How to import all the Excel sheets to DataSet in any asp.net application without Microsoft Office installed.
XsExcel SDK provides methods to import data from other data source, like DataSet. Each datatable in the dataset will be converted to a worksheet with the table name, so the whole datasset will be converted to a new MS Excel document in C# programming. All the data type will keey the original data type in dataset.
This guide page provides C# users with how to import data from DataSet to XLSX and XLS document with XsExcel SDK in C#.NET.
public static void ImportDataSetToSheets() { string filename = "DataTableSample.xlsx"; // Get a dataset with specified data DataSet ds = createDataSet(); // Create a new excel workbook Workbook workbook = new Workbook(); // Import each datatable in dataset foreach (DataTable dt in ds.Tables) { workbook.ConvertDataTableToSheet(dt); } // Save and show the spreedsheet if (File.Exists(filename)) File.Delete(filename); workbook.Save(filename); Process.Start(filename); } private static DataSet createDataSet() { DataTable table = createDataTable(); DataTable table2 = createDataTable2(); DataSet dataset = new DataSet(); dataset.Tables.Add(table); dataset.Tables.Add(table2); return dataset; } private static DataTable createDataTable() { DataTable dt = new DataTable("SampleTable1"); dt.Columns.Add("Name", typeof(string)); dt.Columns.Add("Number", typeof(int)); for (int i = 0; i < 100; i++) { DataRow dr = dt.NewRow(); dr[0] = "name-" + i.ToString(); dr[1] = i; dt.Rows.Add(dr); } return dt; } private static DataTable createDataTable2() { DataTable dt = new DataTable("SampleTable2"); dt.Columns.Add("Id", typeof(int)); dt.Columns.Add("Version", typeof(string)); for (int i = 0; i < 100; i++) { DataRow dr = dt.NewRow(); dr[0] = i; dr[1] = "version-" + i.ToString(); dt.Rows.Add(dr); } return dt; }
Notice - If you used the trial version of XsExcel SDK, can only edit, insert or get the first 300 row data!