如何修复HRESULT：从数据加拿大加载数据时0x800A03EC错误？

Question

首先，我知道此错误杂物有一些答案，但是没有任何帮助，无论是删除0索引等

。 DataTable 是正确的，因此这不是问题。

第一部分是我的excel，是我检查是否存在现有文件，并与数据创建新文件。 新闻通讯是我的数据词 - 我知道它不是一个好名字：

string path2 = "C:\\Users\\pandev\\newsletter.xls";

if (File.Exists(path2) == true)
{
    Process[] pp = Process.GetProcessesByName("excel");
    foreach (Process p in pp)
    {
        if (p.MainWindowTitle.Length == 0)
            p.Kill();
    }

    File.Delete(path2);
}

using (FileStream sw = File.Create(path + "newsletter" + ".xls"))
{
    var data = Encoding.Unicode.GetBytes("Artikelnummer" + "\t" + "Hersteller" + "\t" + "Beschreibung" + "\t" + "Nettopreis" + "\t" + "Bruttopreis" + "\t" + "Zustand" + "\t" + "P/N" + "\t" + "Kategorie I" + "\t" + "Kategorie II" + "\t" + "Kategorie III" + "\t" + "Shop-Link" + "\n");
    sw.Write(data, 0, data.Length);

    foreach (DataRow r in newsletter.Rows)
    {
        data = Encoding.Unicode.GetBytes(r["Artikelnummer"].ToString() + "\t" + r["Hersteller"].ToString() + "\t" + r["Bezeichnung"].ToString() + "\t" + r["Nettopreis"].ToString() + "\t" + r["Bruttopreis"].ToString() + "\t" + r["Zustand"].ToString() + "\t" + r["PN"].ToString() + "\t" + r["Kategorie I"].ToString() + "\t" + r["Kategorie II"].ToString() + "\t" + r["Kategorie III"].ToString() + "\t" + r["Link"].ToString() + "\n");
        sw.Write(data, 0, data.Length);
    }
}

然后，我有以下代码在Excel表中进行一些样式更改：

Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();

Microsoft.Office.Interop.Excel.Workbook oWB = oXL.Workbooks.Open(path + "newsletter.xls");

Microsoft.Office.Interop.Excel.Worksheet oWS = oWB.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
Microsoft.Office.Interop.Excel.Range exrngKopf = (Microsoft.Office.Interop.Excel.Range)oWS.Rows[1].Cells[1, oWS.Columns.Count];
           
Microsoft.Office.Interop.Excel.Range allBZ = oWS.UsedRange;

allBZ.EntireColumn.AutoFit();

error += "T";

oXL.DisplayAlerts = false;
error += "T";
oWS.Name = "GEKKO Computer GmbH";
error += "T";
oWS.Cells.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone;

error += "T";
exrngKopf.EntireRow.Font.Bold = true;
error += "T";
oWB.Activate();
error += "T";
oWB.Application.ActiveWindow.SplitRow = 1;
error += "T";
oWB.Application.ActiveWindow.FreezePanes = true;
error += "T";
Microsoft.Office.Interop.Excel.Range Firstrow = (Microsoft.Office.Interop.Excel.Range)oWS.Rows[1];
error += "T";

error += "x";

if (oWS.AutoFilter != null)
    oWS.AutoFilterMode = false;

oWS.ListObjects.AddEx(Microsoft.Office.Interop.Excel.XlListObjectSourceType.xlSrcRange, allBZ, Type.Missing, Microsoft.Office.Interop.Excel.XlYesNoGuess.xlYes, Type.Missing).Name = "WFTableStyle";
oWS.ListObjects.get_Item("WFTableStyle").TableStyle = null;

oWB.SaveAs(path + "newsletter.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
oWB.Close(true, Missing.Value, Missing.Value);
oXL.Quit();

因此，通常情况下是有效的，但是现在我有一个特殊情况是数据中的超链接：

我希望如何超链接在excel单元格中查看is：

在该单元格的编辑字段中喜欢这种情况：

要修复超链接，我将以下代码放置在上一个代码段中 - 在<之前代码> saveas 关闭和退出 part：

for (int i = 0; i < newsletter.Rows.Count; i++)
{
    for (int j = 0; j < newsletter.Columns.Count; j++)
    {
        oWS.Cells[i + 1, j + 1] = newsletter.Rows[i][j].ToString();
    }
}

它正在工作。每次我执行该代码时，它都会在Excel表中保存数据，但是以下例外发生 hresult：0x800A03EC 。

有人知道如何解决这个问题吗？

我也尝试了这样的事情，但仍然会收到相同的错误：

for (int Idx = 1; Idx < newsletter.Columns.Count; Idx++)
{
    oWS.Range["A1"].Offset[0, Idx].Value = newsletter.Columns[Idx].ColumnName;
}

for (int Idx = 1; Idx < newsletter.Rows.Count; Idx++)
{  // <small>hey! I did not invent this line of code, 
   // I found it somewhere on CodeProject.</small> 
   // <small>It works to add the whole row at once, pretty cool huh?</small>
    oWS.Range["A2"].Offset[Idx].Resize[1, newsletter.Columns.Count].Value =
    newsletter.Rows[Idx].ItemArray;
}

Answer 1

给定数据词的以下定义（名称： datatablenewsletter ）：

DataTable dataTableNewsletter = new DataTable();

//add columns
//dataTableNewsletter.Columns.Add(new DataColumn() { Caption = "Artikelnummer", ColumnName = "Artikelnummer", DataType = System.Type.GetType("System.String") });
dataTableNewsletter.Columns.Add(new DataColumn() { ColumnName = "Artikelnummer", DataType = System.Type.GetType("System.String") });
dataTableNewsletter.Columns.Add(new DataColumn() { ColumnName = "Hersteller", DataType = System.Type.GetType("System.String") });
dataTableNewsletter.Columns.Add(new DataColumn() { ColumnName = "Beschreibung", DataType = System.Type.GetType("System.String") });
dataTableNewsletter.Columns.Add(new DataColumn() { ColumnName = "Nettopreis", DataType = System.Type.GetType("System.Decimal") });
dataTableNewsletter.Columns.Add(new DataColumn() { ColumnName = "Bruttopreis", DataType = System.Type.GetType("System.Decimal") });
dataTableNewsletter.Columns.Add(new DataColumn() { ColumnName = "Zustand", DataType = System.Type.GetType("System.String") });
dataTableNewsletter.Columns.Add(new DataColumn() { ColumnName = "P/N", DataType = System.Type.GetType("System.String") });
dataTableNewsletter.Columns.Add(new DataColumn() { ColumnName = "Kategorie I", DataType = System.Type.GetType("System.String") });
dataTableNewsletter.Columns.Add(new DataColumn() { ColumnName = "Kategorie II", DataType = System.Type.GetType("System.String") });
dataTableNewsletter.Columns.Add(new DataColumn() { ColumnName = "Kategorie III", DataType = System.Type.GetType("System.String") });
dataTableNewsletter.Columns.Add(new DataColumn() { ColumnName = "Shop-Link", DataType = System.Type.GetType("System.String") });

//add data
DataRow row = dataTableNewsletter.NewRow();
row["Artikelnummer"] = "50018113"; //item number
row["Hersteller"] = "HP"; //manufacturer
row["Beschreibung"] = "HP DL38X Gen10 2 Drive NVMe Slim SAS Cable Kit - 871827-B21 NEU"; //description
row["Nettopreis"] = 195; //net price
row["Bruttopreis"] = 195; //gross price
row["Zustand"] = "New"; //condition
row["P/N"] = "869812-001"; //part number
row["Kategorie I"] = "Komponenten"; //category 1
row["Kategorie II"] = "Kabel-Adapter"; //category 2
row["Kategorie III"] = "NVMe-Kabel"; //category 3
row["Shop-Link"] = "https://www.gekko-computer.de/Komponenten/Kabel-Adapter/NVMe-Kabel/HP-DL38X-Gen10-2-Drive-NVMe-Slim-SAS-Cable-Kit-871827-B21-NEU.html"; //URL

//add
dataTableNewsletter.Rows.Add(row);

//add new row
row = dataTableNewsletter.NewRow();
row["Artikelnummer"] = "50015171"; //item number
row["Hersteller"] = ""; //manufacturer
row["Beschreibung"] = "NetApp Ethernet Kabel CAT 6 2m - 112-00195 X6561-R6"; //description
row["Nettopreis"] = 38; //net price
row["Bruttopreis"] = 38; //gross price
row["Zustand"] = "Used"; //condition
row["P/N"] = "112-00195"; //part number
row["Kategorie I"] = "sonstiges"; //category 1
row["Kategorie II"] = "Kabel-Adapter"; //category 2
row["Kategorie III"] = "Ethernet-Kabel"; //category 3
row["Shop-Link"] = "https://www.gekko-computer.de/sonstiges/Kabel-Adapter/Ethernet-Kabel/NetApp-Ethernet-Kabel-CAT-6-2m-112-00195-X6561-R6.html"; //URL

//add
dataTableNewsletter.Rows.Add(row);

异常： HRESULT的异常：0x800A03EC 可以通过执行以下内容来复制：

for (int i = 0; i < dataTableNewsletter.Rows.Count; i++)
{
    //first row contains headers
    int xlRowNum = i + 2;

    string description = dataTableNewsletter.Rows[i]["Beschreibung"].ToString();
    string url = dataTableNewsletter.Rows[i]["Shop-Link"].ToString();
    
    //create hyperlink
    Debug.WriteLine($"location: {i}, 11");

    //The next line results in 'Exception from HRESULT: 0x800A03EC'
    //because 0 is an invalid index in Excel 
    ((Excel.Range)xlWSheet.Cells[i, 11]).Formula = $"=HYPERLINK(\"{url}\", \"{description}\")"; 
}

---

看起来您正在创建<<代码> .xls （较旧的Excel）文件，而不是 .xlsx （newer excel）文件。如果您创建 .xlsx 文件，则可以使用以下Nuget软件包之一，而不是Excel Interop：

• documentformat.openxml
• oflectxml
• epplus
• npoi

---

是您刚创建用于测试的tab-delimited文件吗？

删除的文件并不是真正的Excel文件。如果您打开Excel并选择文件=＆gt;另外，您会看到一个选项卡上的文件保存为 .txt 文件。当我在Excel中打开选项卡删除的文件时，它会生成有关文件格式不匹配文件扩展名的警告。如果将选项卡删除的文件保存使用 .txt 扩展名，则在打开文件时，Excel似乎打开了向导。可以通过用 .csv 扩展名来命名文件来消除这一点 - 尽管它也不是 .csv 文件，但似乎也没有生成任何警告。

由于您正在从数据库中检索数据，并指出数据已经在数据库中，因此使用DataTable创建Excel Workbook似乎是谨慎的。

尝试以下（Excel Interop）：

创建一个新的 Windows表单应用程序（.NET Framework） project。

然后要么下载/安装nuget软件包： Microsoft.office.interop.excel 或将参考添加到 Microsoft Excel xx.x object库库（project =＆gt; add refference。 。

​指令：

• 使用excel = microsoft.office.interop.excel;
• 使用system.io;
• 使用system.data;
• 使用system.diagnostics;

createexcelworkbook ：

public static void CreateExcelWorkbook(DataTable dataTableNewsletter, string excelFilename)
{
    Excel.Application xlApp = null;
    Excel.Workbook xlWBook = null;
    Excel.Worksheet xlWSheet = null;
    Excel.Range allBZ = null;
    Excel.Range exrngKopf = null;

    try
    {
        if (dataTableNewsletter == null)
            throw new Exception("Error - Data table is null.");
        else if (dataTableNewsletter.Rows.Count <= 0)
            throw new Exception($"Error - Data table doesn't contain any data.");

        //create new instance
        xlApp = new Excel.Application();

        //whether or not to make Excel visible
        xlApp.Visible = true;

        //prevent prompting to overwrite existing file
        xlApp.DisplayAlerts = false;

        //disable user control while modifying the Excel Workbook
        //to prevent user interference
        //only necessary if Excel application Visibility property = true
        //need to re-enable before exiting this method
        //xlApp.UserControl = false;

        //if writing/updating a large amount of data
        //disable screen updating by setting value to false
        //for better performance.
        //re-enable when done writing/updating data, if desired
        //excelApp.ScreenUpdating = false;

        //add Workbook
        xlWBook = xlApp.Workbooks.Add();

        //activate
        xlWBook.Activate();

        if (xlWBook.Worksheets.Count > 0)
            xlWSheet = (Excel.Worksheet)xlWBook.ActiveSheet;
        else
            xlWSheet = (Excel.Worksheet)xlWBook.Sheets.Add();

        xlWSheet.Name = "GEKKO Computer GmbH";

        //write column headers
        //Excel indices start with 1; A1 = 1,1
        for (int j = 0; j < dataTableNewsletter.Columns.Count; j++)
        {
            int xlColNum = j + 1;

            //set value - column header
            xlWSheet.Cells[1, xlColNum] = dataTableNewsletter.Columns[j].ColumnName;

            //get range for column
            //Excel.Range colRng = ((Excel.Range)xlWSheet.Cells[1, xlColNum]).EntireColumn;

            //use DataTable data types to set data type for Excel column
            //ToDo: change as desired
            if (dataTableNewsletter.Columns[j].DataType.ToString() == "System.DateTime")
                ((Excel.Range)xlWSheet.Cells[1, xlColNum]).EntireColumn.NumberFormat = @"yyyy\-mm\-dd;@";
            else if (dataTableNewsletter.Columns[j].DataType.ToString() == "System.Int32")
                ((Excel.Range)xlWSheet.Cells[1, xlColNum]).EntireColumn.NumberFormat = 0;
            else if (dataTableNewsletter.Columns[j].DataType.ToString() == "System.Decimal")
                ((Excel.Range)xlWSheet.Cells[1, xlColNum]).EntireColumn.NumberFormat = "0.00";
        }

        //set values in Excel using data from DataTable
        //ToDo: add desired code

        for (int i = 0; i < dataTableNewsletter.Rows.Count; i++)
        {
            //Excel row numbers start with 1
            //headers are in row 1, so data starts in row 2
            int xlRowNum = i + 2;

            for (int j = 0; j < dataTableNewsletter.Columns.Count; j++)
            {
                //Excel column numbers start with 1
                int xlColNum = j + 1;

                if (dataTableNewsletter.Rows[i][dataTableNewsletter.Columns[j].ColumnName] != null && dataTableNewsletter.Rows[i][dataTableNewsletter.Columns[j].ColumnName] != DBNull.Value)
                {
                    //set cell value
                    xlWSheet.Cells[xlRowNum, xlColNum] = dataTableNewsletter.Rows[i][dataTableNewsletter.Columns[j].ColumnName].ToString();
                }
            }
        }

        //set value
        allBZ = (Excel.Range)xlWSheet.UsedRange;
        //Debug.WriteLine($"allBZ.Rows.Count: {allBZ.Rows.Count}; allBZ.Columns.Count: {allBZ.Columns.Count}");

        //auto fit
        allBZ.EntireColumn.AutoFit();

        //set value
        //exrngKopf = (Excel.Range)xlWSheet.Rows[1]; //row 1; header row
        exrngKopf = (Excel.Range)xlWSheet.Cells[1, allBZ.Columns.Count]; //row 1; header row
        exrngKopf.EntireRow.Font.Bold = true;

        //set Border line style
        xlWSheet.Cells.Borders.LineStyle = Excel.XlLineStyle.xlLineStyleNone;

        xlWBook.Application.ActiveWindow.SplitRow = 1;
        xlWBook.Application.ActiveWindow.FreezePanes = true;


        if (xlWSheet.AutoFilter != null)
            xlWSheet.AutoFilterMode = false;

        xlWSheet.ListObjects.AddEx(Excel.XlListObjectSourceType.xlSrcRange, allBZ, Type.Missing, Excel.XlYesNoGuess.xlYes, Type.Missing).Name = "WFTableStyle";
        xlWSheet.ListObjects.get_Item("WFTableStyle").TableStyle = null;

        //fix hyperlinks
        for (int i = 0; i < dataTableNewsletter.Rows.Count; i++)
        {
            //first row contains headers
            int xlRowNum = i + 2;

            //string description = dataTableNewsletter.Rows[i]["Beschreibung"].ToString();
            string description = dataTableNewsletter.Rows[i]["Beschreibung"].ToString() + " - " + DateTime.Now.ToString("HH:mm:ss.fff");
            string url = dataTableNewsletter.Rows[i]["Shop-Link"].ToString();
            Debug.WriteLine($"Description: {description}; URL[{xlRowNum}, 11]: '{url}'");

            //create hyperlink - option 1
            //xlWSheet.Hyperlinks.Add(xlWSheet.Cells[xlRowNum, 11], url, System.Reflection.Missing.Value, description, description);

            //create hyperlink - option 2
            ((Excel.Range)xlWSheet.Cells[xlRowNum, 11]).Formula = $"=HYPERLINK(\"{url}\", \"{description}\")"; //works

            //Debug.WriteLine($"location: {i}, 11");
            //((Excel.Range)xlWSheet.Cells[i, 11]).Formula = $"=HYPERLINK(\"{url}\", \"{description}\")"; //Exception from HRESULT: 0x800A03EC 
        }

        //save Workbook - if file exists, overwrite it
        //xlWBook.SaveAs(filename, Excel.XlFileFormat.xlWorkbookDefault, System.Reflection.Missing.Value, System.Reflection.Missing.Value, true, false, Excel.XlSaveAsAccessMode.xlNoChange, Excel.XlSaveConflictResolution.xlLocalSessionChanges, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
        xlWBook.SaveAs(excelFilename, Excel.XlFileFormat.xlWorkbookNormal, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
    }
    finally
    {
        if (xlWBook != null)
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(allBZ);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(exrngKopf);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWSheet);

            xlWSheet = null;
            allBZ = null;
            exrngKopf = null;

            //close Workbook
            xlWBook.Close(false);

            //release all resources
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWBook);

            xlWBook = null;
        }

        System.Threading.Thread.Sleep(150);

        if (xlApp != null)
        {
            //quit
            xlApp.Quit();

            //release all resources
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlApp);

            xlApp = null;

            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.WaitForPendingFinalizers();

            System.Threading.Thread.Sleep(175);
        }
    }
}

用法：

string excelFilename = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "newsletter.xls");
HelperExcel.CreateExcelWorkbook(dataTableNewsletter, excelFilename);

//the following is necessary otherwise the Excel process seems to persist in Task Manager
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();

资源：

• 我如何获得使用VSTO / C＃中的行和列号的Excel范围？
• c＃excel如何使用单元格链接添加超链接
• <一个href =“ https://www.c-sharpcorner.com/blogs/how-to-to-create-a-hyperlink-usis-usis-usis-rinccel-excel-hyperlink-function-in-epplus-net-net-net-net-net-net-net-net-charp-part-part-part-part-part-part-part-seven “ rel =“ nofollow noreferrer”>如何在epplus .NET应用程序中使用Excel超链接（）函数创建超链接（c＃）部分 - 七
• 如何从Microsoft Visual C＃.net自动化Microsoft Excel
• 如何使用Visual C＃通过使用数组来填充或获取数据来自动化excel