Answered by:
Write Excel Sheet from Datatable in c#

Question
-
User82362805 posted
I am creating asp.net webform application in this application The Excel file saved on server in specific format already I need to get it from path and put data in the cells from datatable. I am looking for some sample code
Kindly post the sample code if someone has.
ThanksMonday, January 28, 2019 1:41 PM
Answers
-
User-893317190 posted
Hi AfaqRajput,
You could try epplus , it is available in nuget.
Epplus could write data to an excel's sheet in any place and give the data a format.
Below is a small sample.
string filePath = Server.MapPath("/officesample/My.xlsx"); FileInfo file = new FileInfo(filePath); using (ExcelPackage excelPackage = new ExcelPackage(file)) // read the data from an excel { ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets[1]; // get the first sheet of the excel worksheet.Cells[10, 3].Value = " input data in 5,3"; // set the value of the cell 10,3 , 10 represents row , 3 represents column worksheet.Cells[6, 7].Value = "input data in 6,7"; worksheet.Cells[2, 3].Style.Numberformat.Format = "0.00"; //set the format of number in 2,3 to 0.00 excelPackage.Save(); }
For more information, you could refer to its document.
https://riptutorial.com/epplus/topic/8080/formatting-values
Here is how to load data from a datatable.
https://riptutorial.com/epplus/example/26422/fill-with-a-datatable
Best regards,
Ackerly Xu
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Tuesday, January 29, 2019 6:27 AM
All replies
-
User-943250815 posted
Try use Nuget package ClosedXML
Also check ClosedXML Wiki to know how to use https://github.com/closedxml/closedxml/wikiMonday, January 28, 2019 4:25 PM -
User426857743 posted
Check the below sample:
Operate excel document in C#--Import/Export Data from DataTable to Excel
Tuesday, January 29, 2019 2:59 AM -
User-893317190 posted
Hi AfaqRajput,
You could try epplus , it is available in nuget.
Epplus could write data to an excel's sheet in any place and give the data a format.
Below is a small sample.
string filePath = Server.MapPath("/officesample/My.xlsx"); FileInfo file = new FileInfo(filePath); using (ExcelPackage excelPackage = new ExcelPackage(file)) // read the data from an excel { ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets[1]; // get the first sheet of the excel worksheet.Cells[10, 3].Value = " input data in 5,3"; // set the value of the cell 10,3 , 10 represents row , 3 represents column worksheet.Cells[6, 7].Value = "input data in 6,7"; worksheet.Cells[2, 3].Style.Numberformat.Format = "0.00"; //set the format of number in 2,3 to 0.00 excelPackage.Save(); }
For more information, you could refer to its document.
https://riptutorial.com/epplus/topic/8080/formatting-values
Here is how to load data from a datatable.
https://riptutorial.com/epplus/example/26422/fill-with-a-datatable
Best regards,
Ackerly Xu
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Tuesday, January 29, 2019 6:27 AM -
User1341756031 posted
Hope this will help you...
System.Data.OleDb.OleDbConnection MyConnection ; System.Data.DataSet DtSet ; System.Data.OleDb.OleDbDataAdapter MyCommand ; MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\\csharp.net-informations.xls';Extended Properties=Excel 8.0;"); MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection); MyCommand.TableMappings.Add("Table", "TestTable"); DtSet = new System.Data.DataSet(); MyCommand.Fill(DtSet); dataGridView1.DataSource = DtSet.Tables[0]; MyConnection.Close();
Full Source...Read from Excel
Tuesday, January 29, 2019 10:28 AM -
User2053451246 posted
Hope this will help you...
System.Data.OleDb.OleDbConnection MyConnection ; System.Data.DataSet DtSet ; System.Data.OleDb.OleDbDataAdapter MyCommand ; MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\\csharp.net-informations.xls';Extended Properties=Excel 8.0;"); MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection); MyCommand.TableMappings.Add("Table", "TestTable"); DtSet = new System.Data.DataSet(); MyCommand.Fill(DtSet); dataGridView1.DataSource = DtSet.Tables[0]; MyConnection.Close();
Full Source...Read from Excel
Tuesday, January 29, 2019 4:05 PM -
User1472580907 posted
You can try http://zetexcel.com/
Tuesday, February 12, 2019 5:52 PM -
User1472580907 posted
Try http://csharp.net-informations.com/excel/csharp-excel-o
Tuesday, February 12, 2019 5:54 PM