locked
Write Excel Sheet from Datatable in c# RRS feed

  • 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

    Monday, 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/wiki

    Monday, January 28, 2019 4:25 PM
  • 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

    This is the exact opposite of what the user is asking about.  This reads data from Excel; it does not write data to 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