public static void ExportExcel(HttpResponse response,MemoryStream stream,string fileName)
{
string fileExtension = Path.GetExtension(fileName).ToLower();
response.Clear();
response.Buffer = true;
response.Charset = Encoding.UTF8.BodyName;
response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
if (fileExtension == ".xlsx") //xlsx file format
{
response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
}
else if (fileExtension == ".xls") //xls file format
{
response.ContentType = "application/vnd.ms-excel";
}
else
{
throw new Exception("文件格式错误");
}
response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", fileName));
response.BinaryWrite(stream.ToArray());
response.End();
try
{
stream.Close();
}
catch
{
}
}
public static void ExportExcel(HttpResponse response,DataTable data,string fileExtension="xlsx", string fileName = null)
{
if (string.IsNullOrWhiteSpace(fileName))
{
fileName = Guid.NewGuid().ToString();
}
IWorkbook exportData = TableToWorkbook(data, fileExtension);
using (MemoryStream stream = new MemoryStream())
{
exportData.Write(stream);
response.Clear();
response.Buffer = true;
response.Charset = Encoding.UTF8.BodyName;
response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
if (fileExtension == "xlsx") //xlsx file format
{
response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
}
else if (fileExtension == "xls") //xls file format
{
response.ContentType = "application/vnd.ms-excel";
}
else
{
throw new Exception("文件格式错误");
}
response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", fileName + "." + fileExtension));
response.BinaryWrite(stream.ToArray());
response.End();
}
}
public static IWorkbook TableToWorkbook(DataTable dt,string extension="xlsx")
{
if (dt == null)
{
dt = new DataTable();
}
IWorkbook workbook;
if (extension == "xlsx")
{
workbook = new XSSFWorkbook();
}
else
{
workbook = new HSSFWorkbook();
}
ISheet sheet1 = workbook.CreateSheet("Sheet 1");
//make a header row
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);
}
//loops through data
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();
cell.SetCellValue(dt.Rows[i][columnName].ToString());
}
}
return workbook;
}