how to create excel sheet and read same excel sheet in ASP.NET

Question

0.00/5 (No votes)

See more:

I have data-table which contain no of column also with date with "DATE" datatype. I tried following option

1) 3rd party DLL- ExcelLibrary It works fine if there is no date column in dataset, else it use some dummy value like -65284 instead of date.

C#

ExcelLibrary.DataSetHelper.CreateWorkbook(@"C:\Users\ABC\Documents\Excel\Report123.xls", ds);

2) Use simple export format without using 3rd party DLL as follow

C#

 public void ExportToExcel(System.Data.DataTable dt)
{
    if (dt.Rows.Count > 0)
    {
        string filename = "Report123.xls";
        System.IO.StringWriter tw = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
        DataGrid dgGrid = new DataGrid();
        dgGrid.DataSource = dt;
        dgGrid.DataBind();

        //Get the HTML for the control.
        dgGrid.RenderControl(hw);
        //Write the HTML back to the browser.
        //Response.ContentType = application/vnd.ms-excel;
        Response.ContentType = "application/vnd.ms-excel";
        Response.AppendHeader("Content-Disposition", "attachment; filename=" + filename + "");
        this.EnableViewState = false;
        Response.Write(tw.ToString());
        Response.End();
    }
}

3) Use 3rd party DLL EPPlus

C#

public void ExportToExcel(System.Data.DataTable dt)
{
    //clear the response of any junk. This may not be necessary
    Response.Clear();

    //add a header so it has a nice file name
    Response.AddHeader("content-disposition", "attachment;filename=myexcelfile.xlsx");

    //Set the MIME type correctly
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

    //create a new package, this is the equivalent of an XLSX file.
    var package = new ExcelPackage();

    //Add a new sheet to the workbook
    var sheet = package.Workbook.Worksheets.Add("My Data"); 

    //EPPlus contains helper function to load data from a DataTable, though you could manually fill in rows/column values yourself if you want
    sheet.Cells["A1"].LoadFromDataTable(dt, true); 

    //write the file bytes to the response
    Response.BinaryWrite(package.GetAsByteArray());

    //end the response so we don't send anymore down and corrupt the file
    Response.End();
}

and I use below code to open created

C#

string strConn = hasHeader ? "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + lFilePath + "';Extended Properties=\"Excel 12.0;HDR=Yes\";"
              : "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + lFilePath + "';Extended Properties=\"Excel 12.0;HDR=No\";";

excel sheet

but facing same error when I read created Excel sheet that "External table is not is expected format".

Please help me. I am facing same problem from a long time.

Posted 5-Oct-15 23:35pm

Member 12036327

Updated 6-Oct-15 3:17am

Palash Mondal_

v3

Add a Solution

Comments

Andy Lanng 6-Oct-15 6:22am

65284 is not "dummy" data. it is the number of days since 00/01/1900. It's how excel stored dates. (29/09/2078?)

You need to add the formatting yourself. See if there are any format properties in the third party classes

Member 12036327 6-Oct-15 7:16am

Ok, Then Can you please help me , how to do formatting , If I used only one line of code

Andy Lanng 6-Oct-15 9:33am

First: you need to reply to my comment. Hover over the comment to see the "reply" button. Otherwise I don't get notified and may not reply.
Second: just google: sheet.Column([int?]).Style.Numberformat.Format = "yyyy-mm-dd"
I'll write up the full answer in a few minutes

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Andy Lanng · Accepted Answer · 2015-10-06T03:49:00

The issue with the dates is because you need to format the date column on the sheet.

If you know which column is a date then you can just use the column index, but then your code is not as generic as it once was. The best idea would be to discover the date fields at run-tim:

C#

public void ExportToExcel(System.Data.DataTable dt)
{
    Response.Clear();
    Response.AddHeader("content-disposition", "attachment;filename=myexcelfile.xlsx");
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    var package = new ExcelPackage();
    var sheet = package.Workbook.Worksheets.Add("My Data"); 
 
//region the bit we'll look at:

    //it's easy if you start at column A, but we'll be using index
    sheet.Cells["A1"].LoadFromDataTable(dt, true); 
 
    for(int x = 0; x < dt.Columns.count; x++)
    {
        //in this loop, x is the index. The index of dt columns will match sheet.Columns
         
        //Test the column[x]:
        if(dt.Columns[i].DataType==typeof(DateTime))
        {
            //if so then apply formatting:
            sheet.Column(x).Style.Numberformat.Format = "yyyy-mm-dd"
        }
    }

//endregion

    Response.BinaryWrite(package.GetAsByteArray());
    Response.End();
}

I hope that help ^_^
Andy

how to create excel sheet and read same excel sheet in ASP.NET

1 solution

Solution 1

Add your solution here

Preview 0

how to create excel sheet and read same excel sheet in ASP.NET

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us