Adding excel sheet to workbook inconsistent

See more:

Windows

WinForms

C#5.0

Hi,

I have written this method to create worksheets to a workbook. But i am facing a problem. By defaul, when an excelworkbook is opened it will have a default sheet Sheet1. When we add the sheet it adds to the right of the sheet1. So with that in mind, i am renaming the first sheet using index (1) to "Test Case".

private string CreateExcelFile(DataSet dsWorkitems)
      {
          string filePath = string.Empty;
          Excel.Application excelApp = new Excel.Application();
          Excel.Workbook excelWorkBook = null;
          Excel.Worksheet excelWorkSheet = null;

          try
          {

              excelApp.Visible = false;
              excelWorkBook = excelApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);

              //Adding New sheet in Excel Workbook
              foreach (DataRow _dr in dsWorkitems.Tables[0].Rows)
              {
                  Excel.Worksheet xlWorkSheet = excelWorkBook.Worksheets.Add();
                  xlWorkSheet.Name = _dr.ItemArray[0].ToString();
              }

              //Naming the first sheet
              //excelWorkSheet = excelWorkBook.Worksheets[1];
              excelWorkSheet = excelWorkBook.Worksheets["Sheet1"];
              excelWorkSheet.Name = "Test Case";

              //Writing column heading in the first sheet
              for (int col = 1; col < dsWorkitems.Tables[0].Columns.Count; col++)
              {
                  excelWorkSheet.Cells[1, col] = dsWorkitems.Tables[0].Columns[col - 1].ColumnName;
              }

              // Initialize Excel Row Start Position
              int excelCellPointer = 2;

              //Opening first sheet and entering test case
              for (int rowCount = 0; rowCount < dsWorkitems.Tables[0].Rows.Count; rowCount++)
              {
                  for (int colCount = 1; colCount < dsWorkitems.Tables[0].Columns.Count; colCount++)
                  {
                      excelWorkSheet.Cells[excelCellPointer, colCount] = dsWorkitems.Tables[0].Rows[rowCount][colCount - 1].ToString();
                  }
                  excelCellPointer++;
              }

              ExportDialogue.InitialDirectory = "C:\\";
              ExportDialogue.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
              ExportDialogue.RestoreDirectory = false;
              ExportDialogue.ShowHelp = true;
              ExportDialogue.DefaultExt = ".xlsx";
              ExportDialogue.Title = "Save Test Case ";

              if (ExportDialogue.ShowDialog() == DialogResult.OK)
              {
                  excelWorkBook.SaveAs(ExportDialogue.FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,
                                      Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                  filePath = ExportDialogue.FileName;
              }

          }
          catch (Exception exHandle)
          {
              throw exHandle;
          }
          finally
          {
              if (excelWorkBook != null)
              {
                  excelWorkBook.Close();
                  excelApp.Quit();
                  Marshal.ReleaseComObject(excelWorkSheet);
                  Marshal.ReleaseComObject(excelWorkBook);
                  Marshal.ReleaseComObject(excelApp);
              }
          }

          return filePath;
      }

But renaming the excel sheet using index is at times making the wrong sheet renamed.

So is there a way i can handle this?

I want the test case sheet to be the first one.

Thanks