How to export Dataset to multiple excel sheet?

Question

Hi, How to export Dataset to multiple excel sheet. Thanks in Advance

Andra Chahar · Answer

Hi Jebin, Here is a solution with Spire.Xls(https://www.nuget.org/packages/FreeSpire.XLS/). Hope it can help you. public static void CreateExcel(String outputExcel, DataSet dataset) { Workbook workbook = new Workbook(); workbook.Worksheets.Clear(); foreach (DataTable dt in dataset.Tables) { Worksheet worksheet = workbook.Worksheets.Add(dt.TableName); worksheet.InsertDataTable(dt,true,1,1); } workbook.SaveToFile(outputExcel,ExcelVersion.Version2016); }

Jignesh Kumar · Answer

Hi Jebin, Please find below link, https://www.aspsnippets.com/Articles/Export-DataSet-DataTables-to-multiple-Excel-Sheets-Worksheets-in-ASPNet-using-C-and-VBNet.aspx

Kapil Gaur · Answer

Hi Jebin, I used ExcelLibrary for this. Can you please try something like this. public static void CreateWorkbook(String filePath, DataSet dataset) { if (dataset.Tables.Count == 0) throw new ArgumentException( "DataSet needs to have at least one DataTable" , "dataset" ); Workbook workbook = new Workbook(); foreach (DataTable dt in dataset.Tables) { Worksheet worksheet = new Worksheet(dt.TableName); for ( int i = 0; i { // Add column header worksheet.Cells[0, i] = new Cell(dt.Columns[i].ColumnName); // Populate row data for ( int j = 0; j worksheet.Cells[j + 1, i] = new Cell(dt.Rows[j][i]); } workbook.Worksheets.Add(worksheet); } workbook.Save(filePath); } So you need to just tweak this code according to your requirement. for example if you want to divide dataset on basis of dataset row count. Then you just need to do small changes in this like creating sheet on event when it reaches the particular count. Do tell me if it helps you.

How to export Dataset to multiple excel sheet?

Answers (3)