Exception from Hresult: 0X800A03Ec Error

Exception from HRESULT: 0x800A03EC Error

Got same error in this line

 Object temp = range.Cells[i][0].Value;

Solved with non-zero based index

 Object temp = range.Cells[i][1].Value;

How is it possible that the guys who created this library thought it was a good idea to use non-zero based indexing?

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

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:

  • DocumentFormat.OpenXml
  • ClosedXml
  • EPPlus
  • NPOI

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:

  • Microsoft.Office.Interop.Excel Namespace
  • Range.EntireColumn Property
  • How do I get an Excel range using row and column numbers in VSTO / C#?
  • C# Excel how to add hyperlink with cell link
  • How To Create A Hyperlink Using Excel Hyperlink() Function In EPPlus .NET Application (C#) Part - Seven
  • How to automate Microsoft Excel from Microsoft Visual C#.NET
  • How to automate Excel by using Visual C# to fill or to obtain data in a range by using arrays

Why do I get the error Exception from HRESULT: 0x800A03EC when I deploy my mvc application to a live server using iis?

Using Office Interop on a server is not supported.

The linked article describes some of the problems you will face. I would recommend using an alternative such as EPPlus, or a commercial product such as Aspose Cells.

Excel error HRESULT: 0x800A03EC while trying to get range with cell's name

The error code 0x800A03EC (or -2146827284) means NAME_NOT_FOUND; in other words, you've asked for something, and Excel can't find it.

This is a generic code, which can apply to lots of things it can't find e.g. using properties which aren't valid at that time like PivotItem.SourceNameStandard throws this when a PivotItem doesn't have a filter applied. Worksheets["BLAHBLAH"] throws this, when the sheet doesn't exist etc. In general, you are asking for something with a specific name and it doesn't exist. As for why, that will taking some digging on your part.

Check your sheet definitely does have the Range you are asking for, or that the .CellName is definitely giving back the name of the range you are asking for.

How to fix 0x800A03EC error when exporting a Datatable to Excel

Launching Excel just to export data to an Excel file is overkill. For web sites it's completely impractical for several reasons:

  • You need a license for every user of the site. That's a lot of money.
  • It's way too easy to leave Excel open, slowly eating up the server's RAM and CPU.
  • It's just too slow

xlsx is a ZIP package containing well-defined XML files, so one can create them directly, use the Open XML SDK or one of the many open source libraries that make this a lot easier, like Epplus, NPOI or ClosedXML.

For example, Epplus allows filling an Excel sheet from a DataTable, IEnumerable or IDbDataReader with a single call:

Dim dt As DataTable = ...

Dim fi New FileInfo(SomePath)
Using p As New ExcelPackage(fi)
    Dim ws = p.Workbook.Worksheets.Add(sheetName)
    ws.Cells("A1").LoadFromDataTable(dt, PrintHeaders:=True)
    p.Save()
End Using

You can also use LoadFromDataReader and avoid loading all the data into memory:

Using cmd As New SqlCommand(sql,connection)
    connection.Open()
    Using reader As SqlDataReader = cmd.ExecuteReader()
        Dim fi New FileInfo(SomePath)
        Using p As New ExcelPackage(fi)
            Dim ws = p.Workbook.Worksheets.Add(sheetName)
            ws.Cells("A1").LoadFromDataReader(reader, PrintHeaders:=True)
            p.Save()
        End Using
    End Using
End Using

Related Topics

How to Combine More Than Two Generic Lists in C# Zip

Why Use 'Virtual' for Class Properties in Entity Framework Model Definitions

Re-Entrant Locks in C#

C# 6.0 Features Not Working with Visual Studio 2015

ASP.NET Core MVC:How to Get Raw JSON Bound to a String Without a Type

Custom Collection Initializers

Why Does Calling a Method in My Derived Class Call the Base Class Method

Dotnet Core System.Text.JSON Unescape Unicode String

How to List All Processes Running in Windows

Unique Random String Generation

Convert a Binary String Representation to a Byte Array

C# List<String> to String with Delimiter

Quick and Simple Hash Code Combinations

Does Anyone Still Use [Goto] in C# and If So Why

How to Show Progress Bar for Upload with Ftpwebrequest

Convert JSON to a C# Array

Is Securestring Ever Practical in a C# Application

How to Get Current User Timezone in C#


Leave a reply



Submit