Export and Format XLS-File

[Marlon]

Hello from Germany,
with following code I export a datatable to Excel.

    Private Sub TEST_XLS(ByVal ds As DataSet)
        Dim dt As DataTable

        'To manipulate an Excel workbook, the code needs to instantiate an Excel Application object, 
        'and then use the object model to add a new workbook, change the name (to Northwind Customers in this example), and do other UI chores.

        'Create an instance of Excel 2003, add a workbook, 
        'and let the user know what's happening
        Dim xl As New Microsoft.Office.Interop.Excel.Application
        xl.Workbooks.Add()
        xl.ActiveSheet.Name = "KPI-Report"
        xl.Visible = True
        xl.Range("A1").Value = "Loading the DataSet...."

        'Once Excel is loaded and running, the code needs a reference to the table in the DataSet ds. 
        'Then it loops through the Columns collection of that DataTable object and places the field names 
        'in the first row of the worksheet. This example uses a Customers table and a Customers column heading.

        Try
            xl.ScreenUpdating = False

            'Start with the Customers table
            dt = ds.Tables("VWDWD_EXP_KPI")

            'Add the column headings for the Customers
            Dim dc As DataColumn
            Dim iCols As Int32 = 0
            For Each dc In dt.Columns
                xl.Range("A1").Offset(0, iCols).Value = dc.ColumnName
                iCols += 1
            Next

            'All that's left is to loop through the rows in the table and use the ItemArray property of the DataRow 
            'object to write the contents of each data row to a row of the worksheet. This is about as close as Excel 
            'comes to supporting a DataSet object directly.

            'Add the data
            Dim iRows As Int32
            For iRows = 0 To dt.Rows.Count - 1
                xl.Range("A2").Offset(iRows).Resize(1, iCols).Value = dt.Rows(iRows).ItemArray()
            Next
        Catch ex As Exception

            'The remaining code in the procedure updates the UI and applies built-in formatting to the worksheet.

        Finally
            xl.ScreenUpdating = True
        End Try
        'Make the sheet pretty
        With xl.ActiveSheet.Range("A1")
            .AutoFilter()
            .AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatSimple)
        End With

        xl = Nothing
    End Sub

This works excellent! But there is a problem:
Column M (Datatype String in VS2005) now contains data in example like '7001400'. The original content was '07001400'.
I understand why: because Excel doesn't format the column to 'Text'.

How do I format the column so that my original data table-data is exported correctly?
Before writing data into excel or afterwards?

Thank You for Your help

Marlon

 

[cjard]

Either, format the column to Text, in code.. before you put the data into it or place a ' apostrophe before the data as you export

 

[Marlon]

OK,
but how do I Format the Column to text in code?

 

[cjard]

http://www.thescripts.com/forum/thread385790.html

 

[Marlon]

Thanks cjard,
I tried to modify my code like following, but it didn't work:

Public Sub XLS_KPI(ByVal vDataset As DataSet, ByVal vds_dt As String, ByVal vSheet_Name As String)
            Dim dt As DataTable

            'Create an instance of Excel 2003, add a workbook, 
            'and let the user know what's happening
            Dim xl As New Microsoft.Office.Interop.Excel.Application
            xl.Workbooks.Add()

            Dim wb As Microsoft.Office.Interop.Excel.Workbook
            Dim style As Microsoft.Office.Interop.Excel.Style

            style = wb.Styles.Add("Style1")
            style.Font.Name = "Arial"
            'style.Font.Bold = True
            style.Font.Size = 10
            style.Interior.Pattern = Microsoft.Office.Interop.Excel.XlPattern.xlPatternSolid
            style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft
            style.NumberFormat = "@"


            xl.ActiveSheet.Name = vSheet_Name
            xl.Visible = True
            xl.Range("A1").Value = "Exportiere Daten aus Datenbank...."

            'Once Excel is loaded and running, the code needs a reference to the table in the DataSet ds. 
            'Then it loops through the Columns collection of that DataTable object and places the field names 
            'in the first row of the worksheet. This example uses a Customers table and a Customers column heading.

            Try
                xl.ScreenUpdating = False

                'Start with the Customers table
                dt = vDataset.Tables(vds_dt) '"VWDWD_EXP_KPI"

                'Add the column headings for the Customers
                Dim dc As DataColumn
                Dim iCols As Int32 = 0
                For Each dc In dt.Columns
                    xl.Range("A1").Offset(0, iCols).Value = dc.ColumnName
                    iCols += 1
                Next

                'All that's left is to loop through the rows in the table and use the ItemArray property of the DataRow 
                'object to write the contents of each data row to a row of the worksheet. This is about as close as Excel 
                'comes to supporting a DataSet object directly.

                'Add the data
                Dim iRows As Int32
                For iRows = 0 To dt.Rows.Count - 1
                    xl.Range("A2").Offset(iRows).Resize(1, iCols).Value = dt.Rows(iRows).ItemArray()
                Next
            Catch ex As Exception

                'The remaining code in the procedure updates the UI and applies built-in formatting to the worksheet.

            Finally
                xl.ScreenUpdating = True
            End Try
            'Make the sheet pretty
            With xl.ActiveSheet.Range("A1")
                '.AutoFilter()
                '.AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatSimple) 'xlRangeAutoFormatSimple
            End With

            xl = Nothing
        End Sub

I get the missing Nullreference-fault in line
=
style = wb.Styles.Add("Style1")
=

Why?

 

[Marlon]

Got it:
Its of Course style = xl.ActiveWorkbook.Styles.Add("Style1")

But Still my Column-Format is STANDARD
Why this now?

 

[cjard]

Err.. exactly how did you tell XL that the range of cells X:Y was to be formatted as Style1?

I.e. you defined a style.. but did you apply it?