c# - Asp net Project //导出Excel函数失败

Question

您好，我的学徒期即将结束，我必须自己创建一个项目。我正在开发某种网站，我想添加一个函数来下载 excel 文件中的 sql 信息。在我的本地 Visual Studio 版本上，它可以完美运行，但是一旦我尝试使用 IIS 在我的网络服务器上运行，它就会崩溃并出现一个错误，这对我来说至少没有帮助，因为我的知识有限。希望这里的人可以帮助解决问题。

Export Code:
using System;
using System.Net;
using System.IO;
using System.Web.UI;
using System.Data.SqlClient;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using System.Collections.Generic;

if (download_decision == "Partial")
            {
                //New Excel Application
                Excel.Application XL = new Microsoft.Office.Interop.Excel.Application();

                //Hiding Alerts and the File
                XL.Visible = false;
                XL.DisplayAlerts = false;

                //Workbook opened
                Excel.Workbook WB = XL.Workbooks.Add();

                //All Sheets are selected
                Excel.Sheets sheets = WB.Worksheets;

                //First Worksheets selected and renamed
                Excel.Worksheet WS = sheets.get_Item(1);
                WS.Name = public_language;

                //Name columns
                (WS.Cells[1, 1] as Excel.Range).Value = public_language;
                (WS.Cells[1, 2] as Excel.Range).Value = "English";

                (WS.Cells[1, 1] as Excel.Range).Font.Bold = true;
                (WS.Cells[1, 2] as Excel.Range).Font.Bold = true;

                (WS.Cells[1, 1] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                (WS.Cells[1, 2] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

                //SQL Reader
                using (SqlConnection conn = new SqlConnection())
                {
                    //SQL Server
                    conn.ConnectionString = sqlserver;

                    //Conncection establish
                    conn.Open();

                    //Get SQL Information
                    SqlCommand cmd = new SqlCommand("select * from" + " " + selected_table, conn);
                    SqlDataReader rdr = cmd.ExecuteReader();

                    int counter = 3;

                    while (rdr.Read())
                    {
                        string Source = (string)rdr["source"];
                        string Target = (string)rdr["target"];

                        //Values are written in file
                        (WS.Cells[counter, 1] as Excel.Range).Value = Source;
                        (WS.Cells[counter, 2] as Excel.Range).Value = Target;

                        counter++;
                    }
                }
                //Autofit Cells
                Excel.Range workSheet_range = WS.get_Range("A:B");
                workSheet_range.EntireColumn.AutoFit();

                //Save and Close
                WB.SaveAs(path);
                WB.Close();

            }

该错误向我展示了这一点：

Server Error in '/' Application.
Exception from HRESULT: 0x800AC472
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Runtime.InteropServices.COMException: Exception from HRESULT: 0x800AC472

Source Error: 

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace: 


[COMException (0x800ac472): Exception from HRESULT: 0x800AC472]
   System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData) +1398
   Microsoft.Office.Interop.Excel.Font.set_Bold(Object ) +0
   Glossary.Dashboard.Download_Click(Object sender, EventArgs e) in C:\Users\myuser\source\repos\project1\project1\Dashboard.aspx.cs:107
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) [phone]
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +150
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5062

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.7.2106.0

非常感谢你！

标签： c#sqlasp.netexcel

Answer 1

@Magnetron 非常感谢！你的想法很完美。我现在正在使用 EPPlus。我修改了我的代码，作为奖励，它起作用了，速度也大大提高了。

我的新代码，以及有效的解决方案：

using OfficeOpenXml;
using OfficeOpenXml.Style;
using System.Collections.Generic;

if (download_decision == "Partial")
            {
                //New Excel Application
                ExcelPackage excel = new ExcelPackage();
                var ws = excel.Workbook.Worksheets.Add(public_language);

                //Name columns
                using (ExcelRange Rng = ws.Cells[1, 1])
                {
                    Rng.Value = public_language;
                    Rng.Style.Font.Bold = true;
                    Rng.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                }

                //Name columns
                using (ExcelRange Rng = ws.Cells[1, 2])
                {
                    Rng.Value = "English";
                    Rng.Style.Font.Bold = true;
                    Rng.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                }

                //SQL Reader
                using (SqlConnection conn = new SqlConnection())
                {
                    //SQL Server
                    conn.ConnectionString = sqlserver;

                    //Conncection establish
                    conn.Open();

                    //Get SQL Information
                    SqlCommand cmd = new SqlCommand("select * from" + " " + selected_table, conn);
                    SqlDataReader rdr = cmd.ExecuteReader();

                    int counter = 3;

                    while (rdr.Read())
                    {
                        string Source = (string)rdr["source"];
                        string Target = (string)rdr["target"];

                        //Values are written in file
                        ws.Cells[counter, 1].Value = Source;
                        ws.Cells[counter, 2].Value = Target;

                        counter++;
                    }
                }

                //Autofit
                ws.Column(1).AutoFit();
                ws.Column(2).AutoFit();

                //Save and Close
                Stream stream = File.Create(path);
                excel.SaveAs(stream);
                stream.Close();
            }

//transfer to client
            System.Web.HttpResponse response = System.Web.HttpContext.Current.Response;

            FileInfo file = new FileInfo(path);
            if (file.Exists)
            {
                response.Clear();
                response.ClearHeaders();
                response.ClearContent();
                response.AddHeader("content-disposition", "attachment; filename=" + filename);
                response.AddHeader("content-type", "application/excel");
                response.ContentType = "application/vnd.xls";
                response.AddHeader("content-length", file.Length.ToString());
                response.WriteFile(file.FullName);
                response.Flush();
                response.Close();
            }

            //file deleting
            File.Delete(path);