How do I fix the HRESULT: 0x800A03EC error when loading data from a DataTable?

First of all I know there are some answers to this error messsage, but nothing has helped, whether it is the removal of 0 indexing etc.

The part where I export the data from my SQL query to the DataTable is working and the data in my DataTable is correct, so that's not the problem.

The first part is my Excel and is where I check if there is an existing file or not and create a new file with the data. newsletter is my DataTable - I know its not a good name:

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);
    }
}

Then I have this following code to make some Style changes in the Excel sheet:

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();

So normally thats working, but now I have the special case where there is a Hyperlink in the data:

How I want the Hyperlink to look in the Excel cell is:

And like this in the edit field of that cell:

To fix the hyperlink I've placed the following code within the previous code snippet - before the SaveAs Close and Quit 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();
    }
}

It's working. Every time I execute that code it saves the data correct in the Excel sheet, but the following exception occurs HRESULT: 0x800A03EC.

Does anyone have an idea on how to fix that?

I also tried something like this but still receive the same error:

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;
}

Solution

Given the following definition for DataTable (name: 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);

The exception: Exception from HRESULT: 0x800A03EC can be replicated by doing the following:

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}\")"; 
}

It looks like you are creating a .xls (older Excel) file instead of a .xlsx (newer Excel) file. If you create a .xlsx file you could use one of the following NuGet packages instead of Excel Interop:

Is the tab-delimited file something you just created for testing?

A tab-delimited file isn't really an Excel file. If you open Excel and select File => Save As, you'll see that a tab-delimited file is saved as a .txt file. When I opened the tab-delimited file in Excel it generated a warning about the file format not matching the file extension. If the tab-delimited file is saved with a .txt extension, Excel seems to open a wizard when the file is opened. This can be eliminated by naming the file with a .csv extension instead - although it's not really a .csv file either, but it doesn't seem to generate any warnings.

Since you're retrieving data from a database and stated that the data is already in a DataTable, it seems prudent to use the DataTable to create the Excel workbook.

Try the following (Excel Interop):

Create a new Windows Forms App (.NET Framework) project.

Then either download / install NuGet package: Microsoft.Office.Interop.Excel or add a reference to Microsoft Excel xx.x Object Library (Project => Add Reference...=> COM => Microsoft Excel xx.x Object Library (ex: Microsoft Excel 16.0 Object Library))

Add the following using directives:

using Excel = Microsoft.Office.Interop.Excel;
using System.IO;
using System.Data;
using 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);
        }
    }
}

Usage:

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();

Resources: