1.Excel实体类,根据实际情况修改,我的需求中要求导出多个sheet页,含多级表头;
/// <summary>
/// Excel表头实体类
/// </summary>
public class ExcelClass
{
/// <summary>
/// 内容
/// </summary>
public DataTable Table { get; set; }
/// <summary>
/// 标题
/// </summary>
public string Title { get; set; }
/// <summary>
/// 标题自定义合并(上)
/// </summary>
public int TitleTop { get; set; }
/// <summary>
/// 标题自定义合并(下)
/// </summary>
public int TitleDown { get; set; }
/// <summary>
/// 标题自定义合并(左)
/// </summary>
public int TitleLeft { get; set; }
/// <summary>
/// 标题自定义合并(右)
/// </summary>
public int TitleRight { get; set; }
/// <summary>
/// 一级表头
/// </summary>
public string[] HeadOne { get; set; }
/// <summary>
/// 二级表头
/// </summary>
public string[] HeadTwo { get; set; }
/// <summary>
/// Sheet页名称
/// </summary>
public string SheetName { get; set; }
}
2.每个ExcelClass代表一个sheet页
//集合代表整个excel,每个list代表一个sheet页
List<ExcelClass> list = new List<ExcelClass>();
string headone = "";
string headtwo= "";
string sql = "";
// 从上起第几个,到上起第几个,从左起第几个,到左起第几个;
headone = "种类一:1, 2, 0, 0|种类二:1,1,1,3|种类三:1,1,4,9";
headtwo= " |一|二|三|四|五|六|七|八|";
sql = "你的sql"
DataTable dt = new DataTable();
ExcelClass excel = new ExcelClass();
excel.Table = dt;
excel.Title = "Title";
//这四个数字代表Title合并情况,规则与上面提到的相同,不需要合并则都写0
excel.TitleTop = 0;
excel.TitleDown = 0;
excel.TitleLeft = 0;
excel.TitleRight = 9;
excel.HeadOne = maincol.Split('|');
excel.HeadTwo = detailcol.Split('|');
excel.SheetName = "SheetName ";
list.Add(excel);
3.
string fileName = "MyExcel.xls";
MemoryStream ms = DeriveExcel.ExportExcel(list, "xls");
return File(ms, "application/vnd.ms-excel", fileName);
4.方法看情况自行封装
//导出excel
public static MemoryStream ExportExcel(List<ExcelClass> list, string excelType)
{
HSSFWorkbook workbook = null;
if (excelType == "xls")
{
workbook = new HSSFWorkbook();//2003
}
else
{
//workbook = new XSSFWorkbook();//2007
}
HSSFPalette palette = workbook.GetCustomPalette();
Color colorBord = Color.FromArgb(0, 0, 0);
Color color = Color.FromArgb(197, 217, 241);
short FIRST_COLOR_INDEX = (short)0x8;
palette.SetColorAtIndex((short)(FIRST_COLOR_INDEX + 1), colorBord.R, colorBord.G, colorBord.B);//边框颜色
palette.SetColorAtIndex((short)(FIRST_COLOR_INDEX), color.R, color.G, color.B);//标题背景色
foreach (var excelClass in list)
{
#region 创建一个sheet
ISheet sheet = workbook.CreateSheet(excelClass.SheetName);
sheet.AddMergedRegion(new CellRangeAddress(excelClass.TitleTop, excelClass.TitleDown, excelClass.TitleLeft, excelClass.TitleRight)); // (从上起第几个,到上起第几个,从左起第几个,到左起第几个)
IRow rowHead = sheet.CreateRow(0);
rowHead.Height = 600;
ICell cellHead = rowHead.CreateCell(0);
cellHead.SetCellValue(excelClass.Title); //标题
ICellStyle cellstyleHead = workbook.CreateCellStyle();//设置垂直居中格式
cellstyleHead.VerticalAlignment = VerticalAlignment.Center;//垂直对齐(默认应该为Center,如果Center无效则用justify)
cellstyleHead.Alignment = HorizontalAlignment.Center;//水平对齐
cellHead.CellStyle = cellstyleHead;
IFont fontHead = workbook.CreateFont();
fontHead.FontHeightInPoints = 16;
fontHead.Boldweight = short.MaxValue;
cellstyleHead.SetFont(fontHead);
//设置全局列宽和行高
sheet.DefaultColumnWidth = 14; //全局列宽
sheet.DefaultRowHeightInPoints = 15; //全局行高
//设置大标题行
int mainRowCount = 1;
int detailRowCount = 2;
if (excelClass.HeadTwo.Length <= 0) { detailRowCount = 1; };
//设置标题行数据
IRow mainRow = sheet.CreateRow(mainRowCount); //创建报表表头标题列
IRow detailRow = sheet.CreateRow(detailRowCount);
ICellStyle style = workbook.CreateCellStyle();
IFont font = workbook.CreateFont();
//font.Boldweight = short.MaxValue;
style.SetFont(font);
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
style.VerticalAlignment = VerticalAlignment.Center;//垂直对齐(默认应该为Center,如果Center无效则用justify)
style.Alignment = HorizontalAlignment.Center;//水平对齐
var vBord = palette.FindColor(colorBord.R, colorBord.G, colorBord.B);
style.BottomBorderColor = vBord.Indexed;
style.TopBorderColor = vBord.Indexed;
style.LeftBorderColor = vBord.Indexed;
style.RightBorderColor = vBord.Indexed;
for (int k = 0; k < excelClass.HeadTwo.Length; k++)
{ //将传递过来的字符串表头进行拆分到Excel
string columnName = excelClass.HeadTwo[k];
ICell cell = detailRow.CreateCell(k);
ICell maincell = mainRow.CreateCell(k);
cell.SetCellValue(columnName);
cell.CellStyle = style;
maincell.CellStyle = style;
}
int nowColIndex = 0; //当前写到第几列
for (int k = 0; k < excelClass.HeadOne.Length; k++)
{ //将传递过来的字符串表头进行拆分到Excel
string[] Htitle = excelClass.HeadOne[k].Split(':');
if (Htitle.Length > 1) //需要合并单元格
{
string[] arr = Htitle[1].Split(',');
sheet.AddMergedRegion(new CellRangeAddress(Convert.ToInt32(arr[0]), Convert.ToInt32(arr[1]), Convert.ToInt32(arr[2]), Convert.ToInt32(arr[3])));
nowColIndex = Convert.ToInt32(arr[2]);
}
string columnName = Htitle[0];
ICell cell = mainRow.CreateCell(nowColIndex); //写到第几列
nowColIndex++;
cell.SetCellValue(columnName);//写入的内容
var v1 = palette.FindColor(color.R, color.G, color.B);
if (v1 == null)
{
throw new Exception("Color is not in Palette");
}
cell.CellStyle = style;
}
//IDataFormat dataformat = workbook.CreateDataFormat();
style.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.000");
//填写ds数据进excel
for (int i = 0; i < excelClass.Table.Rows.Count; i++) //写行数据
{
IRow contentRow = sheet.CreateRow(i + detailRowCount + 1);
for (int j = 0; j < excelClass.Table.Columns.Count - 1; j++)
{
string dgvValue = string.Empty;
dgvValue = excelClass.Table.Rows[i][j].ToString();
ICell cell = contentRow.CreateCell(j);
double number;
if (double.TryParse(dgvValue,out number))
{
cell.SetCellValue(number);
}
else
{
cell.SetCellValue(dgvValue);
}
cell.CellStyle = style;
}
}
//列宽自适应,只对英文和数字有效
for (int i = 0; i <= excelClass.Table.Rows.Count; i++)
{
sheet.AutoSizeColumn(i);
}
//获取当前列的宽度,然后对比本列的长度,取最大值
for (int columnNum = 0; columnNum <= excelClass.Table.Columns.Count; columnNum++)
{
int columnWidth = sheet.GetColumnWidth(columnNum) / 256;
for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++)
{
IRow currentRow;
//当前行未被使用过
if (sheet.GetRow(rowNum) == null)
{
currentRow = sheet.CreateRow(rowNum);
}
else
{
currentRow = sheet.GetRow(rowNum);
}
if (currentRow.GetCell(columnNum) != null)
{
ICell currentCell = currentRow.GetCell(columnNum);
int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
if (columnWidth < length)
{
columnWidth = length;
}
}
}
sheet.SetColumnWidth(columnNum, (columnWidth+1) * 256);
}
#endregion
}
//创建excel
MemoryStream memoryStream = new MemoryStream();
workbook.Write(memoryStream);
memoryStream.Seek(0, SeekOrigin.Begin);
return memoryStream;
}
前台:
使用window.open(url)
url指向后台方法,将2 3步的代码放到这个方法中