导出到excel c＃问题

问题描述

DataSet ds = new DataSet();
System.Data.DataTable dtcdts = (System.Data.DataTable)ViewState["gvcdts"];
dtcdts.TableName="FACTORY";
System.Data.DataTable dtnonfac = (System.Data.DataTable)ViewState["dtnonfac"];
dtnonfac.TableName = "NON-FACTORY";
System.Data.DataTable dtrans = (System.Data.DataTable)ViewState["dttrans"];
dtrans.TableName = "TRANSISTION";
ds.Tables.Add(dtcdts);
ds.Tables.Add(dtnonfac);
ds.Tables.Add(dtrans);
Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
Workbook xlWorkbook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);

// Loop over DataTables in DataSet.
DataTableCollection collection = ds.Tables;
for (int i = collection.Count; i > 0; i--)
{
    Sheets xlSheets = null;
    Worksheet xlWorksheet = null;
    //Create Excel Sheets
    xlSheets = ExcelApp.Sheets;
    xlWorksheet = (Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing);
    System.Data.DataTable table = collection[i - 1];
    xlWorksheet.Name = table.TableName;
    for (int j = 1; j < table.Columns.Count + 1; j++)
    {
        ExcelApp.Cells[1, j] = table.Columns[j - 1].ColumnName;
    }
    // Storing Each row and column value to excel sheet
    for (int k = 0; k < table.Rows.Count; k++)
    {
        for (int l = 0; l < table.Columns.Count; l++)
        {
            ExcelApp.Cells[k + 2, l + 1] =
            table.Rows[k].ItemArray[l].ToString();
        }
    }
    ExcelApp.Columns.AutoFit();
}

这就是我将数据导出到excel的原因，因为我必须在excel表的三个选项卡中显示数据。

但是excel逐个单元格导出服用tOO很长一段时间在服务器，如10分钟，请指导我在哪里，我可以在这里提高性能

altogather我有8000行项目，我必须导出到Excel

this is how i am exporting data to excel because i have to show data in three tabs of excel sheet.
But excel cell by cell exporting taking tOO long time in server like 10 mins please guide me where i can increase performance here
altogather i have 8000 line items i have to export to excel

推荐答案

Quote：

for (int j = 1; j < table.Columns.Count + 1; j++)
{
ExcelApp.Cells[1, j] = table.Columns[j - 1].ColumnName;
}
// Storing Each row and column value to excel sheet
for (int k = 0; k < table.Rows.Count; k++)
{
for (int l = 0; l < table.Columns.Count; l++)
{
ExcelApp.Cells[k + 2, l + 1] =
table.Rows[k].ItemArray[l].ToString();
}
}
ExcelApp.Columns.AutoFit();

}

}

更简单的方法是使用计数器添加单元格，并使用每个新行增加行。基本上设置数据集table = excel'table'/ sheet



所以请使用你必须从dt获取数据的循环，然后将它设置为单元格。 EPPlus的优点是null将在单元格中填入（空白）值



基本上。

easier way to do this will be to rather just add cell for cell by using a counter and increasing the row with each new line. Basically setting the dataset table = to excel 'table'/sheet

so rather use the loop you have to get the data from dt but then set it = to the cell. The nice feature of EPPlus is null will fill in a ""(Blank) value into cell

basically.

foreach(DataRow row in table)
{
    int row = 1 // Starting row in excel
    for (int l = 0; l < table.Columns.Count; l++)
    {
        ExcelApp.Cells[row, l + 1] =
row.ItemArray[l].ToString();
    }
    row++;
}

希望这会让你开始寻求一个有助于使代码更好/更快的解决方案

Hope this will get you started on a solution to help make the code better/faster

problem in the past.  My solution was to copy an empty template Excel file as a starting point.  Using the Microsoft.ACE.OLEDB provider you can then Add Tables to the OLEDB "database" which is your Excel file.  Each table becomes a WorkSheet.  Records are added to the Table (e.g. WorkSheet) with normal SQL Insert commands.

The performance improvement achieved with this approach was anywhere from 10 to 15 times the cell-by-cell approach.

Here is the connection string used to create a connection to your copy of the template file

connExcel = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\";Excel 12.0;HDR=YES\"";

这篇关于导出到excel c＃问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！