Feroz Khan

Feroz Khan

  • 1.3k
  • 292
  • 86.7k

Multiple Table Data want to Export in Excell Separate Sheets

Nov 15 2023 12:44 PM

Dear, 

I want to export data from multiple tables and want to export in excel each sheet each table data but one Excel file.
below are my code data importing in excel but in one sheet only not creating another sheets.

same code i did for second table and trying to extract in second sheet and only change the name of sheet but its overwriting mean replacing from old to new.

// Create a new Workbook.
excelworkBook = excel.Workbooks.Add(Type.Missing);

// Create a Worksheet.
excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
excelSheet.Name = "SAIB";


excelSheet.Cells[1, 1] = "SAIB";
excelSheet.Cells[1, 2] = "Date : From: " + dateTimePicker1.Text + " and To: " + dateTimePicker2.Text ;
int rowcount = 2;

foreach (DataRow datarow in cs.dt.Rows)
{
    rowcount += 1;
    for (int i = 1; i <= cs.dt.Columns.Count; i++)
    {
        // on the first iteration we add the column headers
        if (rowcount == 3)
        {
            excelSheet.Cells[2, i] = cs.dt.Columns[i - 1].ColumnName;
            excelSheet.Cells.Font.Color = System.Drawing.Color.Black;

        }

        excelSheet.Cells[rowcount, i] = datarow[i - 1].ToString();

        //for alternate rows
        if (rowcount > 3)
        {
            if (i == cs.dt.Columns.Count)
            {
                if (rowcount % 2 == 0)
                {
                    excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, cs.dt.Columns.Count]];
                    FormattingExcelCells(excelCellrange, "#CCCCFF", System.Drawing.Color.Black, false);
                }

            }
        }
    }
}

// now we resize the columns
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowcount, cs.dt.Columns.Count]];
excelCellrange.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;

excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[2, cs.dt.Columns.Count]];
FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true);

 


Answers (3)