// 调用此方法 需要添加引用 -> 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);
}
}
asp.net 中 Interop.Excel.dll 无模版创建 excel 并下载到本地
最新推荐文章于 2025-04-14 09:44:58 发布