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);
foreach (DataRow _dr in dsWorkitems.Tables[0].Rows)
{
Excel.Worksheet xlWorkSheet = excelWorkBook.Worksheets.Add();
xlWorkSheet.Name = _dr.ItemArray[0].ToString();
}
excelWorkSheet = excelWorkBook.Worksheets["Sheet1"];
excelWorkSheet.Name = "Test Case";
for (int col = 1; col < dsWorkitems.Tables[0].Columns.Count; col++)
{
excelWorkSheet.Cells[1, col] = dsWorkitems.Tables[0].Columns[col - 1].ColumnName;
}
int excelCellPointer = 2;
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