类库:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.IO;
using System.Data;
namespace DAL
{
public class ExportExcel
{
IWorkbook _workbook = null;
public byte[] Export(DataTable dt, Dictionary<string, string> cols = null)
{
string filename = string.Format("{0}.xlsx", DateTime.Now);
if (cols == null)
{
cols = getCols(ref dt);
}
string sheetName = dt.TableName;
_workbook = new XSSFWorkbook();
setSheet(sheetName, ref dt, ref cols);
return getStream();
}
private Dictionary<string,string> getCols(ref DataTable dt)
{
Dictionary<string, string> dic = new Dictionary<string, string>();
foreach(DataColumn col in dt.Columns)
{
dic.Add(col.ColumnName, col.ColumnName);
}
return dic;
}
protected void setSheet(string sheetname,ref DataTable dt,ref Dictionary<string,string> cols)
{
ISheet sheet = null;
sheet = _workbook.CreateSheet(sheetname);
IRow row = sheet.CreateRow(0);
for (int j = 0; j < dt.Columns.Count; ++j)
{
string colname = dt.Columns[j].ColumnName;
try
{
colname = cols[colname];
}
catch { }
row.CreateCell(j).SetCellValue(colname);
}
for (int i = 0; i < dt.Rows.Count; ++i)
{
IRow r = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; ++j)
{
setSheetCellValue(r.CreateCell(j), dt.Rows[i][j]);
}
}
}
private void setSheet(ref DataTable dt, string title, string title2 = null)
{
//大标题
IFont fontTitle = _workbook.CreateFont();
fontTitle.FontHeightInPoints = 12;
fontTitle.FontName = "黑体";
var cellStyle = _workbook.CreateCellStyle();
cellStyle.SetFont(fontTitle);
cellStyle.Alignment = HorizontalAlignment.Center;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cellStyle.WrapText = true;
//边框
ICellStyle style = _workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.BorderBottom = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
ISheet sheet = _workbook.CreateSheet();
IRow trow = sheet.CreateRow(0);
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dt.Columns.Count - 1));
trow.CreateCell(0).SetCellValue(title);
trow.Height *= 2;
trow.Cells[0].CellStyle = cellStyle;
if (title2 != null)
{
//副标题
IFont fontCell = _workbook.CreateFont();
fontCell.FontName = "宋体";
fontCell.Boldweight = 0;
IRow row1 = sheet.CreateRow(1);
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 0, dt.Columns.Count - 1));
row1.CreateCell(0).SetCellValue(title2);
row1.Cells[0].CellStyle.Alignment = HorizontalAlignment.Left;
row1.Cells[0].CellStyle.SetFont(fontCell);
row1.Height *= 2;
}
IRow row;
int start = 1;
if (title2 != null)
{
start = 3;
row = sheet.CreateRow(2);
}
else
{
start = 2;
row = sheet.CreateRow(1);
}
//列头
for (int j = 0; j < dt.Columns.Count; ++j)
{
row.CreateCell(j).SetCellValue(dt.Columns[j].ColumnName);
row.Cells[j].CellStyle = cellStyle;
}
//数据
for (int i = 0; i < dt.Rows.Count; ++i)
{
IRow r = sheet.CreateRow(start + i);
for (int j = 0; j < dt.Columns.Count; ++j)
{
setSheetCellValue(r.CreateCell(j), dt.Rows[i][j]);
}
}
//边框
for (int c = start - 1; c <= sheet.LastRowNum; c++)
{
IRow re = sheet.GetRow(c);
for (int cell = re.FirstCellNum; cell < re.LastCellNum; cell++)
{
re.Cells[cell].CellStyle = style;
}
}
//列宽自适应
for (int i = 0; i <= dt.Columns.Count; i++)
{
sheet.AutoSizeColumn(i);
}
}
protected void setSheetCellValue(ICell cell, Object tableCell)
{
switch (tableCell.GetType().ToString())
{
case "System.Boolean":
cell.SetCellValue(tableCell.ToString() == "True" ? "是" : "否");
break;
default:
cell.SetCellValue(tableCell.ToString());
break;
}
}
public byte[] getStream()
{
MemoryStream stream = new MemoryStream();
try
{
_workbook.Write(stream);
byte[] by = stream.ToArray();
return by;
}
catch
{
return null;
}
finally
{
if (stream != null)
stream.Close();
}
}
}
}
Action:
public FileResult Export()
{
try
{
byte[] bb = new DAL.Organize.OrganizeImpl().Export();
return File(bb, "application/ms-excel", "organize.xlsx");
}
catch (Exception e)
{
byte[] bb = System.Text.Encoding.UTF8.GetBytes(e.Message);
return File(bb, "application/octet-stream", "err.txt");
}
}