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.
Thanks

Answer 1

User-943250815 posted

Try use Nuget package ClosedXML
Also check ClosedXML Wiki to know how to use https://github.com/closedxml/closedxml/wiki

Answer 2

User426857743 posted

Check the below sample:

Operate excel document in C#--Import/Export Data from DataTable to Excel

Answer 3

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

Answer 4

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

Answer 5

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

This is the exact opposite of what the user is asking about.  This reads data from Excel; it does not write data to Excel.

Answer 6

User1472580907 posted

You can try http://zetexcel.com/

Answer 7

User1472580907 posted

Try http://csharp.net-informations.com/excel/csharp-excel-o