如何创建Excel工作表并在ASP.NET中读取相同的Excel工作表 [英] how to create excel sheet and read same excel sheet in ASP.NET

问题描述

我有数据表，其中不包含任何列，日期为DATE数据类型。我尝试了以下选项



1）第三方DLL- ExcelLibrary如果数据集中没有日期列，它可以正常工作，否则它使用一些虚拟值，如-65284而不是date。

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.

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

2）使用简单的导出格式而不使用第三方DLL如下：

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

 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）使用第三方DLL EPPlus

3) Use 3rd party DLL EPPlus

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

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表



但是当我阅读创建的Excel表格外部表格不是预期的格式时面临同样的错误。



请帮帮我。我很长时间都面临同样的问题。

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.

推荐答案

日期的问题是因为你需要格式化工作表上的日期列。



如果您知道哪个列是日期，那么您可以使用列索引，但是您的代码不像以前那样通用。最好的想法是在运行时发现日期字段：

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:

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();
}

我希望有帮助^ _ ^

Andy

I hope that help ^_^
Andy

这篇关于如何创建Excel工作表并在ASP.NET中读取相同的Excel工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！