請教一下各位,我利用NPOI產生一個.xlsx的檔案匯出,用Excel開啟都會提示有部分內容有問題,要嘗試修復,是否信任這個活頁部的來源。
按下是之後,雖然有正常打開,但是會有下列訊息 "Excel 已完成檔案層級的驗證和修復。此活頁簿的某些部分可能已經修復或遺失。"
excel 的版本是2019標準版
以下是我的部分程式碼
public void WriteExcelWithNPOI(string extension, string filename, DataTable dt)
{
IWorkbook workbook;
if (extension == "xlsx")
{
workbook = new XSSFWorkbook();
}
else if (extension == "xls")
{
workbook = new HSSFWorkbook();
}
else
{
throw new Exception("This format is not supported");
}
ISheet sheet1 = workbook.CreateSheet(filename);
IRow row1 = sheet1.CreateRow(0);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
String columnName = dt.Columns[j].ToString();
cell.SetCellValue(columnName);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row = sheet1.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row.CreateCell(j);
String columnName = dt.Columns[j].ToString();
if (j == 0)
{
cell.SetCellValue(DateTime.Parse(dt.Rows[i][columnName].ToString()).ToString("yyyy/MM/dd").Trim());
}
else
{
cell.SetCellValue(dt.Rows[i][columnName].ToString().Trim());
}
}
}
var context = HttpContext.Current;
context.Response.Clear();
context.Response.ContentType = "application/vnd.ms-excel";
context.Response.AppendHeader("Content-Disposition", "attachment; filename=\"" + filename + "\"");
using (var ms = new MemoryStream())
{
workbook.Write(ms);
context.Response.AddHeader("Content-Length", (ms.ToArray().Length).ToString());
context.Response.BinaryWrite(ms.GetBuffer());
context.ApplicationInstance.CompleteRequest();
context.Response.Output.Flush();
}
}
MemoryStream 的 GetBuffer 會傳回整個記憶組,包含未使用的。所以 Excel 檔後有一堆 null 位元,造成開啟時判斷為壞檔。試試直接寫入 OutputStream
Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", saveAsFileName));
workbook.Write(Response.OutputStream);
Response.Flush();
Response.Close();
其實你只是少了一個context.Response.Close();
如下:
context.Response.Output.Flush();
context.Response.Close(); //<--這行