问导出到Excel函数，在本地主机上工作，但在已发布的网站中不导出
EN

Stack Overflow用户

提问于 2015-10-19 00:12:12

回答 3查看 2.3K关注 0票数 0

我有这段代码要将我的GridViews导出到Excel，它在本地主机上工作，但是在部署之后就不能工作了。单击导出按钮时收到的错误是运行时错误。

protected void EXPORT_BUTTON_Click(object sender, EventArgs e)
        {
            Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();

            // creating new WorkBook within Excel application
            Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
            String DATA1 = "DATA1";
            String DATA2 = "DATA2";
            ExportToExcel(app, workbook, DATA_1, DATA1);
            workbook.Worksheets["Sheet1"].Delete();
            workbook.Worksheets["Sheet2"].Delete();
            workbook.Worksheets["Sheet3"].Delete();
            ExportToExcel(app, workbook, DATA_2, DATA2);
            string FolderPath = ServerName + DirectoryLocation + DirectoryFolder + ExportsFolder;
            var filename = @"EXCEL_" + datetime.ToString("dd-MM-yyyy_hh-mm-ss") + ".xlsx";
            workbook.SaveAs(FolderPath + filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            workbook.Close();
            app.Quit();
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("content-disposition", "attachment; filename=" + filename + ";");
            Response.TransmitFile(FolderPath + filename);
            Response.Flush();
            Response.End();
        }

        public void ExportToExcel(Microsoft.Office.Interop.Excel._Application app, Microsoft.Office.Interop.Excel._Workbook workbook, GridView gridview, string SheetName)
        {
            // see the excel sheet behind the program
            app.Visible = false;

            Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets.Add();

            // changing the name of active sheet
            worksheet.Name = SheetName;

            // storing header part in Excel
            for (int i = 1; i < gridview.Columns.Count + 1; i++)
            {
                worksheet.Cells[1, i] = gridview.Columns[i - 1].HeaderText;
            }


            // storing Each row and column value to excel sheet
            for (int i = 0; i < gridview.Rows.Count - 1; i++)
            {
                for (int j = 0; j < gridview.Columns.Count; j++)
                {
                    worksheet.Cells[i + 2, j + 1] = gridview.Rows[i].Cells[j].Text.ToString();
                }
            }

        }

asp.net

excel

c#-4.0

gridview

回答 3

Stack Overflow用户

回答已采纳

发布于 2015-10-20 04:34:57

我使用EPPLUS解决了我的问题，EPPlus是一个使用Office格式(xlsx)读写Excel2007/2010文件的.net库。它不要求在服务器计算机上安装excel。谢谢。

以下代码可供参考：

protected void ExportToExcel_Click(object sender, EventArgs e)
    {
        var products = GetProducts();
        GridView1.DataSource = products;
        GridView1.DataBind();
        ExcelPackage excel = new ExcelPackage();
        var workSheet = excel.Workbook.Worksheets.Add("Products");
        var totalCols = GridView1.Rows[0].Cells.Count;
        var totalRows = GridView1.Rows.Count;
        var headerRow = GridView1.HeaderRow;
        for (var i = 1; i <= totalCols; i++ )
        {
            workSheet.Cells[1, i].Value = headerRow.Cells[i - 1].Text;
        }
        for (var j = 1; j <= totalRows; j++ )
        {
            for (var i = 1; i <= totalCols; i++)
            {
                var product = products.ElementAt(j-1);
                workSheet.Cells[j + 1, i].Value = product.GetType().GetProperty(headerRow.Cells[i - 1].Text).GetValue(product, null);
            }
        }
        using (var memoryStream = new MemoryStream())
        {
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("content-disposition", "attachment;  filename=products.xlsx");
            excel.SaveAs(memoryStream);
            memoryStream.WriteTo(Response.OutputStream);
            Response.Flush();
            Response.End();
        }
    }

票数 1

Stack Overflow用户

发布于 2015-10-19 01:17:47

'Microsoft.Office.Interop.Excel‘意味着必须安装Microsoft才能使此代码工作。

避免使用Microsoft.Office.Interop.Excel导出数据，因为它需要在服务器上安装应用程序，可伸缩性很差，并且违反MS许可条件。

我建议其他方法，如文本-csv导出，将满足更好的性能在服务器上。

protected void EXPORT_BUTTON_Click(object sender, EventArgs e)
{
 ...
 StringBuilder sb = new StringBuilder();

 // storing header part in Excel
 for (int i = 1; i < gridview.Columns.Count + 1; i++)
 {
     sb.Append (gridview.Columns[i - 1].HeaderText);
     stringBuilder.Append(",");
 }

 sb.Append(Environment.NewLine);

 // storing Each row and column value to excel sheet
 for (int i = 0; i < gridview.Rows.Count - 1; i++)
 {
     for (int j = 0; j < gridview.Columns.Count; j++)
     {
       sb.Append (gridview.Rows[i].Cells[j].Text.ToString());
       stringBuilder.Append(",");
     }
     sb.Append(Environment.NewLine);
  }

  WriteToCSV(sb.ToString(); 
}

public static void WriteToCSV(string text, string fileName)
  {
      string attachment = "attachment; filename="+fileName+DateTime.Now.ToString("yy-MM-dd")+".csv";
      HttpContext.Current.Response.Clear();
      HttpContext.Current.Response.ClearHeaders();
      HttpContext.Current.Response.ClearContent();
      HttpContext.Current.Response.AddHeader("content-disposition", attachment);
      HttpContext.Current.Response.ContentType = "text/csv;charset=utf8";
      HttpContext.Current.Response.AddHeader("Pragma", "public");

      HttpContext.Current.Response.Write('\uFEFF'); //this is BOM
      HttpContext.Current.Response.Write(text);
      HttpContext.Current.Response.End();
  }