2016年4月25日星期一

C#.net+Interop.Excel: Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))

Below is a coding to create a workbook with four worksheets and run in MS Excel 2003 correctly, but it cannot work in MS Excel 2007 with non-English version.
using Excel = Microsoft.Office.Interop.Excel;
:
:
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWB = xlApp.Workbooks.Add();
Excel.Sheets xlWS = xlWB.Worksheets;

Excel.Worksheet wsHdr = null;
Excel.Worksheet wsDtl = null;
Excel.Worksheet wsLog1 = null;
Excel.Worksheet wsLog2 = null;

Excel.Worksheet wsHdr = xlWB.Sheets["Sheet1"];
wsHdr.Name = "Header";                // change worksheet name
Excel.Worksheet wsDtl = xlWB.Sheets["Sheet2"];
wsDtl.Name = "Details";
Excel.Worksheet wsLog1 = xlWB.Sheets["Sheet3"];
wsLog1.Name = "Log 1";
wsLog2 = xlWS.Add(Type.Missing, wsLog1);
wsLog2.Name = "Log 2";



It occurs the error on the line 13 or 15 in the MS Excel 2007, because the system cannot recognize the name of worksheet or cannot find the name of worksheet in the workbook.

In non-English MS Excel, the worksheet name, "Sheet1, Sheet2...etc", is possibly not be shown in English, therefore the system will not recognize it, thus the coding above should use "get_item(1)" syntax to get the worksheet instead of "Sheets["Sheet1"]".

Nevertheless, there is a setting name "Include this many sheets: 3" in MS Excel option. The meaning is that the system will include 3 worksheets inside the workbook when creating a new, and it can also be changed by user. For example, user changed to "Include this many sheets: 1" in the option, so if you want to use "get_item(2)" to get the second of the worksheet, then it will occur a system error too, because there is only one worksheet in the workbook. Consequently you should use "Worksheets.Count()" to check how many worksheets first to prevent the error.

Below is an enhanced coding to solve the two above issues in a simple way. Certainly you can use For-Loop syntax to delete all worksheets first and then add the new worksheet.
using Excel = Microsoft.Office.Interop.Excel;
:
:
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWB = xlApp.Workbooks.Add();
Excel.Sheets xlWS = xlWB.Worksheets;

Excel.Worksheet wsHdr = null;
Excel.Worksheet wsDtl = null;
Excel.Worksheet wsLog1 = null;
Excel.Worksheet wsLog2 = null;

wsHdr = xlWB.Worksheets.get_Item(1);
if (xlWB.Worksheets.Count >= 2) wsDtl = xlWB.Worksheets.get_Item(2); else wsDtl = xlWS.Add(Type.Missing, wsHdr);
if (xlWB.Worksheets.Count >= 3) wsLog1 = xlWB.Worksheets.get_Item(3); else wsLog1 = xlWS.Add(Type.Missing, wsDtl);
if (xlWB.Worksheets.Count >= 4) wsLog2 = xlWB.Worksheets.get_Item(4); else wsLog2 = xlWS.Add(Type.Missing, wsLog1);

wsHdr.Name = "Header";
wsDtl.Name = "Details";
wsLog1.Name = "Log 1";
wsLog2.Name = "Log 2";

沒有留言:

發佈留言