Hi,

I am trying to create an excel document by reading a datatable in to a memory stream and then creating an excel sheet from that.

I haven't really played with the XML stuff much but have got so far as I can read my table to the memory stream and output it to my console.

The idea here is that I can do some formating within the XML schema (eventually when I understand it a little more) ultimately making outputing a formated excel document really fast.

Currently I have a little vb.net application that imports a CSV file, loads this in to a datatable along with some additional information from a few other datasources, and then creates a new datatable that is used to bind to a datagridview.

This allows you to do any filtering etc you may want to do, then there are 2 output options, one is just a master (i.e. contains everything) and the other creates an individual spreadsheet report that is emailed to managers.

The spreadsheet is created by adding the data to it, then a summary column is made to allow me to colour code the data. unfortunately this is painfully slow as there are usually around 7000 lines per month and it does this on a line by line basis.

I know I can output to excel much faster than this as I use a stringwriter to create csv files, but as these files contain some formating I wondered if there would be a quicker way?

Here is a little sample code, this was my original code that used a listview, I have since changed to a datagridview as it is quicker to bind a source than write to a listview.

Public Function ExportListViewToCSV(ByVal filename As String, ByVal lv As ListView, ma As String, m1 As String, m2 As String, m3 As String) As Boolean

        Try
            ' Reference Excel application and excel objects 
            Dim xlApp As New Excel.Application                          'Creates a new instance of excel
            Dim xlWkb As Excel.Workbook                                 'Workbook object
            Dim xlSh As Excel.Worksheet                                 'Worksheet object
            Dim misValue As Object = System.Reflection.Missing.Value    'No value overide
            ' Reference Excel application and excel objects 

            xlApp.Visible = True
            xlWkb = xlApp.Workbooks.Add(misValue)               'Create a workbook without a name
            xlSh = xlWkb.Worksheets(1)                          'Reference sheet
            xlSh.Name = "BRADFORD REPORT"                       'Rename sheet
            xlWkb.Worksheets(2).Delete()                        'Delete Standard sheets

            ' Write Headers  
            For i As Integer = 1 To lv.Columns.Count - 5        'Loops accross columns
                xlSh.Cells(1, i) = lv.Columns(i).Text           'Adds column heading
            Next

            ' Write Values
            Dim r As Integer = 2                                        'Holds row to write to
            For i As Integer = 0 To lv.Items.Count - 1                  'Loops through Rows
                If lv.Items(i).SubItems(20).Text = ma Then              'Only Writes rows relating to that manager
                    For j As Integer = 1 To lv.Columns.Count - 5
                        Dim c As Excel.Range = xlSh.Cells(r, j)
                        If j = 11 Or j = 12 Or j = 13 Or j = 14 Then
                            If lv.Items(i).SubItems(j).Text <> "" Then
                                c.Value = CDate(lv.Items(i).SubItems(j).Text)
                            End If
                        Else
                            c.Value = lv.Items(i).SubItems(j).Text
                        End If

                        If j = 19 And lv.Items(i).SubItems(j).Text <> "" Then
                            Dim cVal As Integer
                            cVal = c.Value
                            If cVal < 101 Then
                                xlSh.Range(xlSh.Cells(r, 1), c).Interior.Color = Color.SpringGreen
                            ElseIf cVal > 100 And cVal < 200 Then
                                xlSh.Range(xlSh.Cells(r, 1), c).Interior.Color = Color.Orange
                            ElseIf cVal >= 200 Then
                                xlSh.Range(xlSh.Cells(r, 1), c).Interior.Color = Color.Red
                            End If
                        End If
                    Next
                    r = r + 1
                End If
            Next

            Dim uRng As Excel.Range
            uRng = xlSh.UsedRange
            uRng.Font.Name = "Arial Unicode"
            xlSh.Columns.AutoFit()

            xlWkb.SaveAs(Filename:=filename, FileFormat:=39)
            xlWkb.Close()
            xlApp.Quit()

            releaseObject(xlApp)
            releaseObject(xlWkb)
            releaseObject(xlSh)

            EmailMgr(m1, m2, m3, filename)

        Catch ex As Exception
            ' catch any errors  
            Return False
        End Try
        Return True
    End Function

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
And this was my testing for writing a grid to an excel sheet but doesn't do any of the formating:

    Public Function grid2xl(ByVal filename As String, ByVal dgv As DataGridView, ma As String, m1 As String, m2 As String, m3 As String) As Boolean

        ' Reference Excel application and excel objects 
        Dim xlApp As New Excel.Application                          'Creates a new instance of excel
        Dim xlWkb As Excel.Workbook                                 'Workbook object
        Dim xlSh As Excel.Worksheet                                 'Worksheet object
        Dim misValue As Object = System.Reflection.Missing.Value    'No value overide

        ' Reference Excel application and excel objects 
        xlApp.Visible = True
        xlWkb = xlApp.Workbooks.Add(misValue)               'Create a workbook without a name
        xlSh = xlWkb.Worksheets(1)                          'Reference sheet
        xlSh.Name = "BRADFORD REPORT"                       'Rename sheet
        xlWkb.Worksheets(2).Delete()                        'Delete Standard sheets

        Dim iX As Integer
        Dim iY As Integer
        Dim iC As Integer
        For iC = 0 To dgv.Columns.Count - 1
            xlSh.Cells(1, iC + 1).Value = dgv.Columns(iC).HeaderText
            xlSh.Cells(1, iC + 1).font.bold = True
        Next

        xlSh.Rows(2).select()
        For iX = 0 To dgv.Rows.Count - 2
            For iY = 0 To dgv.Columns.Count - 1
                xlSh.Cells(iX + 2, iY + 1).value = dgv(iY, iX).Value.ToString
            Next
        Next

        xlApp.Visible = True
        xlApp.UserControl = True

    End Function
Finally this is the code I have to populate the memorystream

    Private Sub ExportDataTable()

        Dim xmlStream As New MemoryStream
        dgvSrt.WriteXml(xmlStream, XmlWriteMode.WriteSchema) 'Write schema and data to XML in a memory stream.
        xmlStream.Position = 0 'Rewind memory stream

    End Sub
I appreciate any help, thanks in advance,

Andy


PS: I've not been here for a while but the new forum looks fantastic!