CODEWITHSUNDEEP
c#excellistinterop
DoIt
DoIt

Reputation: 3448

Inserting data into multiple worksheets on a single excel workbook

I am working on a method which exports data from two different C# lists into two excel worksheets in a single workbook using the following code

Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
                Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);



                Microsoft.Office.Interop.Excel._Worksheet worksheet1 = null;
                worksheet1 = (Microsoft.Office.Interop.Excel._Worksheet)workbook.Sheets["Sheet1"];
                worksheet1 = (Microsoft.Office.Interop.Excel._Worksheet)workbook.ActiveSheet;

                for (int i = 1; i < listExport.Count + 1; i++)
                {

                        worksheet1.Cells[i+1,1] = listExport[i - 1].time.ToString("HH:mm:ss");
                        worksheet1.Cells[i+1, 2] = listExport[i - 1].CC;
                        worksheet1.Cells[i+1, 3] = listExport[i - 1].term;                        

                }

                Microsoft.Office.Interop.Excel._Worksheet worksheet2 = null;
                worksheet2 = (Microsoft.Office.Interop.Excel._Worksheet)workbook.Sheets["Sheet2"];
                worksheet2 = (Microsoft.Office.Interop.Excel._Worksheet)workbook.ActiveSheet;

                for (int i = 1; i < AxiomSubSet.Count + 1; i++)
                {

                    worksheet2.Cells[i + 1, 1] = AxiomSubset[i - 1].time.ToString("HH:mm:ss");
                    worksheet2.Cells[i + 1, 2] = AxiomSubset[i - 1].CC;

                }

                string fileDestination = @"S:\Axiom Derivatives\Parser Project\axiom.xls";
                if (File.Exists(fileDestination))
                {
                    File.Delete(fileDestination);
                }

                workbook.SaveAs(fileDestination, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                workbook.Close(true, Type.Missing, Type.Missing);
                Process.Start(fileDestination);
                app.Quit();

But when I run the above method the first list is successfully processed but when assigning the second sheet at the following line it threw me error Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))

worksheet2 ==Microsoft.Office.Interop.Excel._Worksheet)workbook.Sheets["Sheet2"];

Is it not the way to deal with multiple Excel sheets?

Upvotes: 0

Views: 4720

Answers (1)

Donal
Donal

Reputation: 32763

The second worksheet is probably not called Sheet2. It is best to just use the index instead.

Also, you need to call Activate to make Sheet 2 the active sheet:

before first sheet 

worksheet1 = (Microsoft.Office.Interop.Excel._Worksheet)workbook.Sheets[1];
workbook.Sheets[1].Activate();

before second sheet

worksheet2 = (Microsoft.Office.Interop.Excel._Worksheet)workbook.Sheets[2];
workbook.Sheets[2].Activate();

Upvotes: 2

Related Questions