Exporting DataGridView data into multiple sheets of an Excel file

[Pratibha Bhadauria]

I am trying to create multiple worksheets in an excel file which contains data from datagrid (there is only one datatgrid). But I am getting only the data populated in the dataset in a worksheet. Heres the code I am using, can anyone tell e how to implement the same?

private void btnCreateExcel_Click(object sender, EventArgs e)
{
if (dataGridViewCreateExcel.Rows.Count >= 1)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
//xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
DateTime v_todaydate = (DateTime.Today);
string v_todaydt = v_todaydate.Date.ToString("dd/MM/yyyy");
try
{
foreach (DataTable dt in DS.Tables)
{
int i = 0;
int j = 0;
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
for (i = 0; i <= dataGridViewCreateExcel.RowCount - 1; i++)
{
for (j = 0; j <= dataGridViewCreateExcel.ColumnCount - 1; j++)
{

DataGridViewCell cell = dataGridViewCreateExcel[j, i];
xlWorkSheet.Cells[i + 2, j + 1] = cell.Value;


xlWorkSheet.Cells[1, j + 1] = dataGridViewCreateExcel.Columns[j].HeaderText;
}
}
}
FilePath = ConfigurationManager.AppSettings["FilePath"].ToString();
xlWorkBook.SaveAs(FilePath + v_todaydt + ".xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
//releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
MessageBox.Show("Excel file created , you can find the file" + FilePath + v_todaydt + ".xls");
}
catch (Exception ex4)
{
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
MessageBox.Show("File can not be created...", "Error !!",MessageBoxButtons.OK, MessageBoxIcon.Error);
//releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
}
else
{
MessageBox.Show("Empty excel sheet");
}
}

Continue reading...