I am using a dataset to create an Excel spreadsheet. Here is my code.
Public Sub excelcheck()
Try
Dim excelApp As New ApplicationClass()
Dim excelWorkbook As Workbook = excelApp.Workbooks.Add(Type.Missing)
Dim sheetIndex As Integer = 0
Dim col, row As Integer
Dim excelSheet As Worksheet
dsold = Utilities.exceldataset.Copy
If dsold.Tables(0).Rows.Count > 65000 Then
Dim i As Integer = 0
Dim startind As Integer
Dim endind As Integer
startind = 0
endind = 64999
Dim totalcount As Double
totalcount = dsold.Tables(0).Rows.Count
Dim currentcount As Double
currentcount = Math.Ceiling(totalcount / 65000)
While (i < currentcount)
Dim dt As New System.Data.DataTable("Table" + Convert.ToString(i))
dt = splitrecords(dsold.Tables(0), dsold.Tables(0).Rows.Count, startind, endind)
If i = 0 Then
ds.Tables.Add(dt.Copy)
Else
dt.TableName = "Table" + Convert.ToString(i)
ds.Tables.Add(dt.Copy)
End If
startind = startind + 65000
endind = endind + 65000
i = i + 1
End While
Else
ds = dsold.Copy
End If
For Each dt As System.Data.DataTable In ds.Tables
sheetIndex += 1
Dim rawData(dt.Rows.Count, dt.Columns.Count - 1) As Object
For col = 0 To dt.Columns.Count - 1
rawData(0, col) = dt.Columns(col).ColumnName
Next
For col = 0 To dt.Columns.Count - 1
For row = 0 To dt.Rows.Count - 1
rawData(row + 1, col) = dt.Rows(row).ItemArray(col)
Next
Next
Dim finalColLetter As String = String.Empty
Dim colCharset As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
Dim colCharsetLen As Integer = colCharset.Length
If dt.Columns.Count > colCharsetLen Then
finalColLetter = colCharset.Substring( _
(dt.Columns.Count - 1) \ colCharsetLen - 1, 1)
End If
finalColLetter += colCharset.Substring( _
(dt.Columns.Count - 1) Mod colCharsetLen, 1)
excelSheet = CType( _
excelWorkbook.Sheets.Add(excelWorkbook.Sheets(sheetIndex), _
Type.Missing, 1, XlSheetType.xlWorksheet), Worksheet)
excelSheet.Name = dt.TableName
Dim excelRange As String = String.Format("A1:{0}{1}", finalColLetter, dt.Rows.Count + 1)
excelSheet.Range(excelRange, Type.Missing).Value2 = rawData
CType(excelSheet.Rows(1, Type.Missing), Range).Font.Bold = True
excelSheet = Nothing
Next
Dim strDate1 As String = String.Format("{0:dd}", DateTime.Now).ToUpper
Dim strMonth As String = String.Format("{0:MM}", DateTime.Now).ToUpper()
Dim stryear As String = String.Format("{0:yyyy}", DateTime.Now).ToUpper()
StrServerFile = Utilities.Jobid + " " + strDate1 + strMonth + stryear + ".xls"
excelWorkbook.SaveAs("C:\Bcp\" + StrServerFile, XlFileFormat.xlWorkbookNormal, Type.Missing, _
Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, _
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)
excelWorkbook.Close(True, Type.Missing, Type.Missing)
excelWorkbook = Nothing
excelApp.Quit()
excelApp = Nothing
insertexcelfile(StrServerFile)
GC.Collect()
GC.WaitForPendingFinalizers()
MessageBox.Show("Excel Report Genearted Sucessfully")
SetLabelText(False)
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub
The exception details are:
System.Runtime.InteropServices.COMException (0x800A03EC): Exception from HRESULT: 0x800A03EC
at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
at Microsoft.Office.Interop.Excel.Range.set_Value2(Object )
at Exceltest.Form1.excelcheck() in D:\exceltest\Exceltest\Form1.vb:line 118