asked on

VB.NET 2005 Excel spreadsheet save error

Greetings experts;

I have an application that allows custom queries to a SQL 2005 database and displays the results in a datagrid. The user then has the option of exporting the resulting datagrid to either a .TXT or .XLS file for further data analysis.

I am having serious difficulty saving the created excel spreadsheet. I can create, open and write the data to the workbook, however it will not allow me to save the file.

At this line --

oXL.ActiveWorkbook.SaveAs(.FileName)

The catch section of the try block executes and I get an error message saying that it can not access the given file. The base .xls file is created in the desired path, however the data is missing, since the file can not save properly, and an error message pops up stating that the file is corrupt.

Any help you can give me on this issue would be greatly appreciated. thank you ahead of time for your support.

Private Sub ExportGridContentsToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles ExportGridContentsToolStripMenuItem.Click
        Dim strCSV As String = ""
        Dim strFilename As String = ""
        Dim swExport As StreamWriter
        Dim intCount As Integer = 0
        Dim intRow As Integer = 0
        Dim intColumn As Integer = 0
 
        SaveFileDialog1.Title = "Save File As "
        SaveFileDialog1.InitialDirectory = defaultExportLocation
 
        Try
            With SaveFileDialog1
                .Filter = "TXT files|*.txt|Excel 97-2003 Workbook|*.xls"
                If .ShowDialog = Windows.Forms.DialogResult.OK Then
                    strFilename = .FileName
                    swExport = New StreamWriter(.OpenFile)
                    intCount = 0
 
                    Application.DoEvents()
                    Me.Cursor = Cursors.WaitCursor
 
                    If .FileName.Contains(".txt") Then
 
                        For Each dgColumn As DataGridViewTextBoxColumn In DataGridView1.Columns
                            If dgColumn.Index = 0 Then
                                strCSV = Chr(34) & dgColumn.HeaderText & Chr(34)
                            Else
                                strCSV = strCSV & ";" & Chr(34) & dgColumn.HeaderText & Chr(34)
                            End If
                        Next
 
                        swExport.WriteLine(strCSV)
 
                        For intRow = 0 To DataGridView1.RowCount - 2
                            strCSV = ""
                            For intColumn = 0 To DataGridView1.ColumnCount - 1
                                If intColumn = 0 Then
                                    ' first entry
                                    strCSV = DataGridView1.Item(intColumn, intRow).Value.ToString
                                Else
                                    strCSV = strCSV & ";" & DataGridView1.Item(intColumn, intRow).Value.ToString
                                End If
                            Next intColumn
 
                            ' print contents of string here
                            swExport.WriteLine(strCSV)
                            intCount = intCount + 1
                        Next intRow
 
                        swExport.Close()
                    ElseIf .FileName.Contains(".xls") Then
                        Dim oXL As New Excel.Application
                        Dim oWB As Excel.Workbook = oXL.Workbooks.Add
                        Dim oSheet As Excel.Worksheet = CType(oWB.Worksheets.Add, Excel.Worksheet)
                        Dim intColumnMax, intRowMax As Integer
                        
                        intRowMax = DataGridView1.Rows.Count - 1
                        intColumnMax = DataGridView1.Columns.Count - 1
 
                        For intRow = 0 To intRowMax
                            For intColumn = 0 To intColumnMax
                                oSheet.Cells(intRow + 1, intColumn + 1) = DataGridView1.Item(intColumn, intRow).Value.ToString
                            Next
                            intCount = intCount + 1
                        Next
 
                        oSheet.Columns.AutoFit()
                        oSheet.Activate()
 
                        If System.IO.File.Exists(.FileName) Then
                            oXL.DisplayAlerts = False
                            oXL.ActiveWorkbook.SaveAs(.FileName)
                            oXL.ActiveWorkbook.Close()                            
                            oXL.DisplayAlerts = True
                        Else
                            oSheet.SaveAs(.FileName)
                        End If
 
                        oXL.Quit()
                        oXL = Nothing
                        oWB = Nothing
                        oSheet = Nothing
                    End If
                End If
            End With
 
            MsgBox(intCount & " records exported to file", MsgBoxStyle.OkOnly)
            Me.Cursor = Cursors.Default
 
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical)
        End Try

Open in new window

ASKER CERTIFIED SOLUTION

Howard Cantrell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Howard Cantrell

Also change these lines back to ....
For intRow = 0 To intRowMax
For intColumn = 0 To intColumnMax

gwosgood

ASKER

planocz,

Thank you for the code, the changes you made cleared up the corruption error. Can you explain to me why? I see that you rearranged the command order, but I dont understand why that fixed my issue. Also, this method of writing the data to the spreadsheet one cell at a time is excruciatingly slow, my datagrid typically has 1000+ rows. Is there a more efficient way of exporting this? One final question; how can I get the column headers into the spreadsheet as well? The code only copies over the data, not the headings.

Thanks for your help!

Howard Cantrell

I found this on sample on the internet that will do a faster job of reading datagrid to excel..

'FORM 1
Public Class Form1
    Private Declare Function ShellEx Lib "shell32.dll" Alias "ShellExecuteA" ( _
        ByVal hWnd As Integer, ByVal lpOperation As String, _
        ByVal lpFile As String, ByVal lpParameters As String, _
        ByVal lpDirectory As String, ByVal nShowCmd As Integer) As Integer
 
    Private Sub exportExcel(ByVal grdView As DataGridView, ByVal fileName As String, _
        ByVal fileExtension As String, ByVal filePath As String)
 
        ' Choose the path, name, and extension for the Excel file
        Dim myFile As String = filePath & "\" & fileName & fileExtension
 
        ' Open the file and write the headers
        Dim fs As New IO.StreamWriter(myFile, False)
        fs.WriteLine("<?xml version=""1.0""?>")
        fs.WriteLine("<?mso-application progid=""Excel.Sheet""?>")
        fs.WriteLine("<ss:Workbook xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet"">")
 
        ' Create the styles for the worksheet
        fs.WriteLine("  <ss:Styles>")
        ' Style for the column headers
        fs.WriteLine("    <ss:Style ss:ID=""1"">")
        fs.WriteLine("      <ss:Font ss:Bold=""1""/>")
        fs.WriteLine("      <ss:Alignment ss:Horizontal=""Center"" ss:Vertical=""Center"" " & _
            "ss:WrapText=""1""/>")
        fs.WriteLine("      <ss:Interior ss:Color=""#C0C0C0"" ss:Pattern=""Solid""/>")
        fs.WriteLine("    </ss:Style>")
        ' Style for the column information
        fs.WriteLine("    <ss:Style ss:ID=""2"">")
        fs.WriteLine("     <ss:Alignment ss:Vertical=""Center"" ss:WrapText=""1""/>")
        fs.WriteLine("    </ss:Style>")
        fs.WriteLine("  </ss:Styles>")
 
        ' Write the worksheet contents
        fs.WriteLine("<ss:Worksheet ss:Name=""Sheet1"">")
        fs.WriteLine("  <ss:Table>")
        For i As Integer = 0 To grdView.Columns.Count - 1
            fs.WriteLine(String.Format("    <ss:Column ss:Width=""{0}""/>", _
            grdView.Columns.Item(i).Width))
        Next
        fs.WriteLine("    <ss:Row>")
        For i As Integer = 0 To grdView.Columns.Count - 1
            fs.WriteLine(String.Format("      <ss:Cell ss:StyleID=""1"">" & _
                "<ss:Data ss:Type=""String"">{0}</ss:Data></ss:Cell>", _
                grdView.Columns.Item(i).HeaderText))
        Next
        fs.WriteLine("    </ss:Row>")
 
        ' Check for an empty row at the end due to Adding allowed on the DataGridView
        Dim subtractBy As Integer, cellText As String
        If grdView.AllowUserToAddRows = True Then subtractBy = 2 Else subtractBy = 1
        ' Write contents for each cell
        For i As Integer = 0 To grdView.RowCount - subtractBy
            fs.WriteLine(String.Format("    <ss:Row ss:Height=""{0}"">", _
                grdView.Rows(i).Height))
            For intCol As Integer = 0 To grdView.Columns.Count - 1
                cellText = grdView.Item(intCol, i).Value
                ' Check for null cell and change it to empty to avoid error
                If cellText = vbNullString Then cellText = ""
                fs.WriteLine(String.Format("      <ss:Cell ss:StyleID=""2"">" & _
                    "<ss:Data ss:Type=""String"">{0}</ss:Data></ss:Cell>", _
                    cellText.ToString))
            Next
            fs.WriteLine("    </ss:Row>")
        Next
 
        ' Close up the document
        fs.WriteLine("  </ss:Table>")
        fs.WriteLine("</ss:Worksheet>")
        fs.WriteLine("</ss:Workbook>")
        fs.Close()
 
        ' Open the file in Microsoft Excel
        ' 10 = SW_SHOWDEFAULT
        ShellEx(Me.Handle, "Open", myFile, "", "", 10)
    End Sub
 
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ' Create a couple of rows
        Dim row1() As String = New String() {"Joe", "Benjamin", vbNullString, "23"}
        Dim row2() As String = New String() {"Michael", "", "Brown", "32"}
        Dim row3() As String = New String() {"Phil", "Todd", "Lee", "42"}
 
        ' Add the rows to the DataGridView
        DataGridView1.Rows.Add(row1)
        DataGridView1.Rows.Add(row2)
        DataGridView1.Rows.Add(row3)
    End Sub
 
    Private Sub Button1_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles Button1.Click
        ' Call the export sub
        exportExcel(DataGridView1, "exportedData", ".xlsx", _
            My.Computer.FileSystem.SpecialDirectories.Desktop)
    End Sub
End Class
 
'FORM 1 DESIGNNER
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Partial Class Form1
    Inherits System.Windows.Forms.Form
 
    'Form overrides dispose to clean up the component list.
    <System.Diagnostics.DebuggerNonUserCode()> _
    Protected Overrides Sub Dispose(ByVal disposing As Boolean)
        Try
            If disposing AndAlso components IsNot Nothing Then
                components.Dispose()
            End If
        Finally
            MyBase.Dispose(disposing)
        End Try
    End Sub
 
    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer
 
    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    <System.Diagnostics.DebuggerStepThrough()> _
    Private Sub InitializeComponent()
        Me.DataGridView1 = New System.Windows.Forms.DataGridView
        Me.colFirstName = New System.Windows.Forms.DataGridViewTextBoxColumn
        Me.colMiddleName = New System.Windows.Forms.DataGridViewTextBoxColumn
        Me.colLastName = New System.Windows.Forms.DataGridViewTextBoxColumn
        Me.colAge = New System.Windows.Forms.DataGridViewTextBoxColumn
        Me.Button1 = New System.Windows.Forms.Button
        CType(Me.DataGridView1, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'DataGridView1
        '
        Me.DataGridView1.AutoSizeColumnsMode = System.Windows.Forms.DataGridViewAutoSizeColumnsMode.AllCells
        Me.DataGridView1.AutoSizeRowsMode = System.Windows.Forms.DataGridViewAutoSizeRowsMode.AllCells
        Me.DataGridView1.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize
        Me.DataGridView1.Columns.AddRange(New System.Windows.Forms.DataGridViewColumn() {Me.colFirstName, Me.colMiddleName, Me.colLastName, Me.colAge})
        Me.DataGridView1.Location = New System.Drawing.Point(12, 41)
        Me.DataGridView1.Name = "DataGridView1"
        Me.DataGridView1.Size = New System.Drawing.Size(608, 240)
        Me.DataGridView1.TabIndex = 0
        '
        'colFirstName
        '
        Me.colFirstName.HeaderText = "First Name"
        Me.colFirstName.Name = "colFirstName"
        Me.colFirstName.Width = 82
        '
        'colMiddleName
        '
        Me.colMiddleName.HeaderText = "Middle Name"
        Me.colMiddleName.Name = "colMiddleName"
        Me.colMiddleName.Width = 94
        '
        'colLastName
        '
        Me.colLastName.HeaderText = "Last Name"
        Me.colLastName.Name = "colLastName"
        Me.colLastName.Width = 83
        '
        'colAge
        '
        Me.colAge.HeaderText = "Age"
        Me.colAge.Name = "colAge"
        Me.colAge.Width = 51
        '
        'Button1
        '
        Me.Button1.AutoSize = True
        Me.Button1.Location = New System.Drawing.Point(12, 12)
        Me.Button1.Name = "Button1"
        Me.Button1.Size = New System.Drawing.Size(88, 23)
        Me.Button1.TabIndex = 1
        Me.Button1.Text = "Export to Excel"
        Me.Button1.UseVisualStyleBackColor = True
        '
        'Form1
        '
        Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)
        Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
        Me.ClientSize = New System.Drawing.Size(632, 293)
        Me.Controls.Add(Me.Button1)
        Me.Controls.Add(Me.DataGridView1)
        Me.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedSingle
        Me.MaximizeBox = False
        Me.Name = "Form1"
        Me.Text = "Form1"
        CType(Me.DataGridView1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)
        Me.PerformLayout()
 
    End Sub
    Friend WithEvents DataGridView1 As System.Windows.Forms.DataGridView
    Friend WithEvents colFirstName As System.Windows.Forms.DataGridViewTextBoxColumn
    Friend WithEvents colMiddleName As System.Windows.Forms.DataGridViewTextBoxColumn
    Friend WithEvents colLastName As System.Windows.Forms.DataGridViewTextBoxColumn
    Friend WithEvents colAge As System.Windows.Forms.DataGridViewTextBoxColumn
    Friend WithEvents Button1 As System.Windows.Forms.Button
 
End Class

Open in new window