问题描述
我正在使用数据集创建Excel电子表格.这是我的代码.
I am using a dataset to create an Excel spreadsheet. Here is my code.
Public Sub excelcheck()
Try
' Create the Excel Application object
Dim excelApp As New ApplicationClass()
' Create a new Excel Workbook
Dim excelWorkbook As Workbook = excelApp.Workbooks.Add(Type.Missing)
Dim sheetIndex As Integer = 0
Dim col, row As Integer
Dim excelSheet As Worksheet
' Copy each DataTable as a new Sheet
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
' Copy the DataTable to an object array
Dim rawData(dt.Rows.Count, dt.Columns.Count - 1) As Object
' Copy the column names to the first row of the object array
For col = 0 To dt.Columns.Count - 1
rawData(0, col) = dt.Columns(col).ColumnName
Next
' Copy the values to the object array
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
' Calculate the final column letter
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)
' Create a new Sheet
excelSheet = CType( _
excelWorkbook.Sheets.Add(excelWorkbook.Sheets(sheetIndex), _
Type.Missing, 1, XlSheetType.xlWorksheet), Worksheet)
excelSheet.Name = dt.TableName
' Fast data export to Excel
Dim excelRange As String = String.Format("A1:{0}{1}", finalColLetter, dt.Rows.Count + 1)
excelSheet.Range(excelRange, Type.Missing).Value2 = rawData
' Mark the first row as BOLD
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"
' Save and Close the Workbook
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
' Release the Application object
excelApp.Quit()
excelApp = Nothing
insertexcelfile(StrServerFile)
' Collect the unreferenced objects
GC.Collect()
GC.WaitForPendingFinalizers()
MessageBox.Show("Excel Report Genearted Sucessfully")
SetLabelText(False)
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub
异常详细信息如下:
System.Runtime.InteropServices.COMException(0x800A03EC):来自HRESULT的异常:0x800A03EC
在System.RuntimeType.ForwardCallToInvokeMember处(字符串memberName,BindingFlags标志,对象目标,Int32 [] aWrapperTypes,MessageData& msgData)
在Microsoft.Office.Interop.Excel.Range.set_Value2(Object)
在D:\ exceltest \ Exceltest \ Form1.vb中的Exceltest.Form1.excelcheck()处:行118
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
推荐答案
这篇关于HRESULT的例外:0x800A03EC(Excel 2007)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!