Exporting dataset to xlsx with Microsoft.Office.Interop.Excel

Question

I was using the following Code in order to export a dataset to xlsx. Everything works fine when i am on .net 4.0 but there is a server that requires the framework to be in 3.5 unfortunately and i can't do anything about it. So i changed the framework from 4.0 to 3.5 and when i execute the code i get an error

Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))

the error happens when the bellow line executes

Excel.Workbook workBook = excel.Workbooks.Add();

this is my Code

static void Main(string[] args)
        {

             String filepath = "C:/test.csv";            
             DataSet ds = Convert(filepath.ToString(), "tblCustomers", "\t");            
             Excel.Application excel = new Excel.Application();           
             Excel.Workbook workBook = excel.Workbooks.Add();   
             Excel.Worksheet sheet = (Excel.Worksheet)workBook.ActiveSheet;
            int i = 0;            
                for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
                {
                    sheet.Cells[i + 1, j + 1] = ds.Tables[0].Columns[j].ToString();            
                }

                i = 1;                
            foreach (DataRow row in ds.Tables[0].Rows)
            {                              
                for (int j = 0; j < row.ItemArray.Length; j++)
                {
                    sheet.Cells[i + 1, j + 1] = row[j].ToString().Trim();
                    //cleangth = row[j].ToString().Trim().Length *10;

                }

                i++;
            }


            workBook.SaveAs(@System.IO.Directory.GetCurrentDirectory() + "\\test.xlsx");
            workBook.Close();           

        }

Any suggestions ?

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

1

This apparently is a bug which was fixed in .NET 4.0 (source): if the regional setting of Excel is different from the regional setting of the PC, you will get this error.

From Microsoft:

You receive this error calling an Excel method when the following conditions are true:

-The method requires an LCID (locale identifier).

-You run an English version of Excel. However, the regional settings for the computer are configured for a non-English language.

If the client computer runs the English version of Excel and the locale for the current user is configured for a language other than English, Excel will try to locate the language pack for the configured language. If the language pack is not found, the error is reported.

To work around this problem, you can use one of the following methods:

-Install the Multilingual User Interface Pack for your version of Office.

-Execute the Excel method or property by using InvokeMember so that you can specify the CultureInfo for the call. For example, the following code illustrates how you can invoke the Workbooks object Add method with "en-US" as the CultureInfo

-Or, set the CultureInfo prior to calling the Excel method.

see this link for code examples of the suggested workarounds: http://support.microsoft.com/kb/320369

edited Jun 20 '20 at 09:12

Community

1
1

answered Jan 31 '12 at 17:07

Brian Snow

1,133
1
12
23

ouf, well, it passes from there, but after it breaks at sheet.Cells[i + 1, j + 1]=ds.Tables[0].Columns[j].ToString(); saying Exception from HRESULT: 0x800A03EC. – themhz Jan 31 '12 at 17:48
even if i set sheet.Cells[i + 1, j + 1] = "ddd"; it still breaks. Also the sheet variable needed to be casted like this in order to work Excel.Worksheet sheet = (Excel.Worksheet)workBook.ActiveSheet; – themhz Jan 31 '12 at 17:49
Maybe this has something to do with it: http://stackoverflow.com/a/493253/1108263 ? – Brian Snow Jan 31 '12 at 17:57
Also, I noticed you are starting your loops at 0 (`i =0` & `j = 0`), try starting them at 1, because I think 1 is the beginning in Excel, not 0. In other words, I don't think the cell `J0` (column: j row: 0) exists in Excel. – Brian Snow Jan 31 '12 at 18:02
Actually after thinking about my comment above, it wouldn't make sense for that to be the problem since your code worked with .NET 4.0.... – Brian Snow Jan 31 '12 at 18:12
well the loops work when i am in .net 4 and when i put sheet.Columns[2,2] = "ddd"; still doesnt work. – themhz Jan 31 '12 at 18:14
anyway i will accept your answer since i passed the first error. I will try to find any other solutions in order to export the excel, or try to percuade my manager to put .net 4 on the server now since everyone stoped working for today. Thanx again. – themhz Jan 31 '12 at 18:18
Thanks, and maybe one more thing to try: http://stackoverflow.com/a/6386651/1108263 ... That is basically a shot in the dark though, I'm not sure why it would work instead of the way you have it now. – Brian Snow Jan 31 '12 at 18:21

Exporting dataset to xlsx with Microsoft.Office.Interop.Excel

1 Answers1