asp.net 中 Interop.Excel.dll 无模版创建 excel 并下载到本地

最新推荐文章于 2025-04-14 09:44:58 发布

原创最新推荐文章于 2025-04-14 09:44:58 发布 · 334 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#asp.net #excel

C# - Excel 专栏收录该内容

2 篇文章

订阅专栏

本文介绍如何在ASP.NET中利用Interop.Excel.dll库无模板创建Excel文件，并实现将其下载到用户的本地计算机。详细步骤包括设置引用、创建Excel工作簿、添加工作表、写入数据以及触发文件下载。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

// 调用此方法 需要添加引用 ->  COM ->  Microsoft Excel 12.0 Object Library


 public void ExportExcel()
    {

        // excel 文件的数据源
        System.Data.DataTable table = DBHelper.GetDataTable("SELECT * FROM StockInOrderItem");


        try
        {
            Application excel = new Excel.Application();
            Workbook workbook = excel.Application.Workbooks.Add(true);
            Worksheet worksheet = (Worksheet)workbook.Worksheets.Add(workbook.Sheets[1], Type.Missing, Type.Missing, Type.Missing);
            ((Worksheet)workbook.Sheets["Sheet1"]).Delete();
            worksheet.Name = "入库单打印";
            // 合并单元格
            Excel.Range range = excel.get_Range(worksheet.Cells[1, 2], worksheet.Cells[1, 3]);
            range.Value2 = "合并A1-A2";
            range.Font.Bold = "true";
            range.Font.Size = "16";
            range.Merge(true);

            // 当前excel行
            int excelCurrRow = 2;
            worksheet.Cells[excelCurrRow, 1] = "Name";
            worksheet.Cells[excelCurrRow, 2] = "Category";
            worksheet.Cells[excelCurrRow, 3] = "Price";
            worksheet.Cells[excelCurrRow, 4] = "Description";

            

            Range firstHeaderRow = worksheet.get_Range("A1:D1", System.Type.Missing);
            firstHeaderRow.Font.Bold = true;
            //firstHeaderRow.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.ColorTranslator.FromHtml("#808080"));
            firstHeaderRow.Font.Color = 2;
            firstHeaderRow.Font.Name = "Calibri";
            firstHeaderRow.EntireRow.RowHeight = 95;
            firstHeaderRow.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;

            Range statusSheetFirstColumn = worksheet.get_Range("A:A", System.Type.Missing);
            statusSheetFirstColumn.EntireColumn.ColumnWidth = 15;

            Range statusSheetSecondColumn = worksheet.get_Range("B:B", System.Type.Missing);
            statusSheetSecondColumn.EntireColumn.ColumnWidth = 15.71;

            Range statusSheetThirdColumn = worksheet.get_Range("C:C", System.Type.Missing);
            statusSheetThirdColumn.EntireColumn.ColumnWidth = 15.43;

            Range statusSheetFourthColumn = worksheet.get_Range("D:D", System.Type.Missing);
            statusSheetFourthColumn.EntireColumn.ColumnWidth = 17.86;


            excelCurrRow++;

            for (int i = 0; i < table.Rows.Count; i++)
            {
                for (int j = 1; j <= 4; j++)
                {
                    worksheet.Cells[excelCurrRow, j] = table.Rows[i][j - 1].ToString();
                }
                excelCurrRow++;
            }

            // 指定生成的文件名和服务器端临时文件的路径，用户下载后删除
            string fileName = Server.MapPath("~/PrintTemp/") + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
            
            if (File.Exists(fileName))
            {
                File.Delete(fileName);
            }


            object missing = System.Reflection.Missing.Value;

            // 将 excel 文件保存到一个服务器端临时文件夹
            workbook.SaveAs(fileName, XlFileFormat.xlOpenXMLWorkbook, missing, missing, false, false,XlSaveAsAccessMode.xlNoChange,
   XlSaveConflictResolution.xlUserResolution, true, missing, missing, missing);

            workbook.Close(false, missing, missing);
            workbook = null;
            excel.Quit();
            
            excel = null;
            GC.Collect();

            // 提示用户下载到本地
            FileInfo fileInfo = new FileInfo(fileName);
            if (fileInfo.Exists)
            {
                Response.Clear();
                Response.ClearHeaders();
                Response.Buffer = false;
                Response.ContentType = "application/octet-stream";
                Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileInfo.Name, System.Text.Encoding.UTF8));
                Response.AppendHeader("Content-Length", fileInfo.Length.ToString());
                Response.WriteFile(fileInfo.FullName);
                Response.Flush();

                //下载完成后，删除服务器端的临时文件，避免文件过多给服务器造成负担
                if (File.Exists(fileName))
                {
                    File.Delete(fileName);
                }
               
            }
            else
            {
                Response.Write("打印excel失败，文件不存在！");
            }



        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }
    }