none
When I try to put array data into Excel, the system prompted HResult:0x800A03EC. RRS feed

  • 問題

  • When I try to put array data into Excel, the system prompted "Exception from HResult:0x800A03EC". The error occurs should be in red font as following code. I saw the Microsoft msdn web site, it said that the OS Regional setting different to Office installation language. It might prompt this error due to datetime convertion. But I exported the dataset without datetime field.  How to solve the problem and can I know the client computer office installation language? If I reset the regional setting after executed red font, can I solve this problem? 

    The execute code as following:

        Public Sub PrintToExcelEvt(ByVal asDataTable As DataTable, Optional ByVal outputWithFileName As String = Nothing, Optional ByVal outputType As String = "xls", Optional ByVal sheetName As String = "Output", Optional ByVal asReportCode As String = Nothing)

            'Export DataTable data into Excel SpreadSheet

            Dim xl As New Excel.Application
            xl.UserControl = True
            Dim oldCI As System.Globalization.CultureInfo = _
                System.Threading.Thread.CurrentThread.CurrentCulture
            System.Threading.Thread.CurrentThread.CurrentCulture = _
                New System.Globalization.CultureInfo("en-US")

            Dim dc As DataColumn
            Dim iCols As Integer = 0
            Dim iRows As Integer = 0
            Dim oCol As Integer
            Dim frm As New frmProgressStatus

            Try

                frm.maxCount = dt.Rows.Count
                frm.Show()

                xl.Workbooks.Add()
                xl.ActiveSheet.Name = sheetName
                xl.Range("A1").Value = "Loading the DataSet...."

                'Assign the data format in every column
                For oCol = 1 To dt.Columns.Count
                    If dt.Columns(oCol - 1).DataType.ToString = "System.String" Then
                        xl.Columns(oCol).select()
                        xl.Selection.NumberFormatLocal = "@"
                    End If
                Next
            
        xl.Range("A1").Select()

                'Reset to original region
                System.Threading.Thread.CurrentThread.CurrentCulture = oldCI
                'Add the column headings for the Customers
                For Each dc In dt.Columns
                    xl.Range("A1").Offset(0, iCols).Value = dc.ColumnName
                    iCols += 1
                Next
                For iRows = 0 To dt.Rows.Count - 1
                    xl.Range("A2").Offset(iRows).Resize(1, iCols).Value = _
                      dt.Rows(iRows).ItemArray()
                Next

                Xl.Visible = true


            Catch ex As Exception

                MsgBox(Err.Description)
                System.Threading.Thread.CurrentThread.CurrentCulture = oldCI

            End Try

    End Sub

    2006年11月24日 上午 01:55

解答

  • 1. 請明確標記程式碼發生錯誤的行號。把不需要的區段剪掉,以利快速閱讀,你的程式碼沒有適當的註記,無法快速瞭解你要幹麻,也沒辦法快速判斷哪邊可以跳過不看。

    2. 非必要,請不要使用 Select 方法跟 Selection 物件,原因請在本站板上搜尋先前討論。

    3. xl.Range("A1").Value = "Loading the DataSet...." 建議改成 xl.StatusBar = "Loading the DataSet...." ,用狀態列來顯示,而不是用表格。

    4. xl.Range("A2") 已經限定範圍為 A2 ,Offset 只能在 A2 範圍內跑,不管怎樣,一格不能等於陣列。

    5. .Net Object 陣列與 Excel 採用的 Variant 陣列是不同性質,COM 自動封送可能會有問題,請一格格塞資料。

    6. 只是把資料庫資料塞進空白的 Excel 的話,你可以把 Excel 當資料庫輸出,會更方便,也會更快。下面網址是 In 子句的範例,可節省自己寫資料進 Excel 的程式碼與時間:

    http://tlcheng.spaces.live.com/blog/cns!145419920BFD55A7!1290.entry

    2006年11月24日 上午 05:45
    版主