Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- using Excel = Microsoft.Office.Interop.Excel;
- private MemoryStream ExportDataSetToExcel()
- {
- System.Data.DataTable employeeTable = new System.Data.DataTable("Employee");
- employeeTable.Columns.Add("Employee ID");
- employeeTable.Columns.Add("Employee Name");
- employeeTable.Rows.Add("1", "ABC");
- employeeTable.Rows.Add("2", "DEF");
- employeeTable.Rows.Add("3", "PQR");
- employeeTable.Rows.Add("4", "XYZ");
- ////Create a Department Table
- System.Data.DataTable departmentTable = new System.Data.DataTable("Department");
- departmentTable.Columns.Add("Department ID");
- departmentTable.Columns.Add("Department Name");
- departmentTable.Rows.Add("1", "IT");
- departmentTable.Rows.Add("2", "HR");
- departmentTable.Rows.Add("3", "Finance");
- ////Create a DataSet with the existing DataTables
- DataSet ds = new DataSet("Organization");
- ds.Tables.Add(employeeTable);
- ds.Tables.Add(departmentTable);
- ////Creae an Excel application instance
- Excel.Application excelApp = new Excel.Application();
- excelApp.Visible = true;
- Excel.Workbook excelWorkBook = excelApp.Workbooks.Add();
- foreach (System.Data.DataTable table in ds.Tables)
- {
- ////Add a new worksheet to workbook with the Datatable name
- Microsoft.Office.Interop.Excel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add();
- excelWorkSheet.Name = table.TableName;
- for (int i = 1; i < table.Columns.Count + 1; i++)
- {
- excelWorkSheet.Cells[1, i] = table.Columns[i - 1].ColumnName;
- excelWorkSheet.Cells[1, i].Font.Bold = true;
- }
- for (int j = 0; j < table.Rows.Count; j++)
- {
- for (int k = 0; k < table.Columns.Count; k++)
- {
- excelWorkSheet.Cells[j + 2, k + 1] = table.Rows[j].ItemArray[k].ToString();
- excelWorkSheet.Cells[j + 2, k + 1].Interior.Color = Excel.XlRgbColor.rgbRed;
- }
- }
- }
- MemoryStream ms = new MemoryStream();
- ////excelWorkBook.SaveAs(ms, Type.Missing, Type.Missing, Type.Missing, true, false, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing);
- excelWorkBook.Close();
- excelApp.Quit();
- return ms;
- }
Add Comment
Please, Sign In to add comment