导出Excel表格样式优化
开发工具与关键技术:VS
作者:卢媛媛;
页面样式
JS代码
设置表头以及单元格
样式关键代码
最后输出
最终版
//1.创建工作薄
HSSFWorkbook excelBook = new HSSFWorkbook();
//XSSFWorkbook excelBook = new XSSFWorkbook();
//2.创建工作表
ISheet sheet1 = excelBook.CreateSheet("宿舍费报表");
//index代表多少行
var rowIndex = 0;
IRow row1 = sheet1.CreateRow(rowIndex);
//给标题的每一个单元格赋值
row1.CreateCell(0).SetCellValue("宿舍费报表");
ICell cell = sheet1.CreateRow(0).CreateCell(0);
ICellStyle style = excelBook.CreateCellStyle();//创建样式对象
//合并表头
//CellRangeAddress四个参数为:起始行,结束行,起始列,结束列
//CellRangeAddress region = new CellRangeAddress(0, 0, 0, 10);
//sheet1.AddMergedRegion(region);
//设计表头字段
row1.CreateCell(0).SetCellValue("班级");
row1.CreateCell(1).SetCellValue("姓名");
row1.CreateCell(2).SetCellValue("性别");
row1.CreateCell(3).SetCellValue("宿舍号");
row1.CreateCell(4).SetCellValue("房型类别");
row1.CreateCell(5).SetCellValue("充值金额");
row1.CreateCell(6).SetCellValue("充值时间");
row1.CreateCell(7).SetCellValue("月租价格");
row1.CreateCell(8).SetCellValue("床位起住时间");
row1.CreateCell(9).SetCellValue("费用截止日期");
row1.CreateCell(10).SetCellValue("床位租金余额");
//循环每个单元格
for (int i = 0; i < listZhuSu1.Count; i++)
{
IRow row = sheet1.CreateRow(i + 1);
cell = row.CreateCell(0);
cell.SetCellValue(listZhuSu1[i].机构班级MC);
cell.CellStyle = style;
cell = row.CreateCell(1);
cell.SetCellValue(listZhuSu1[i].姓名);
cell.CellStyle = style;
cell = row.CreateCell(2);
cell.SetCellValue(listZhuSu1[i].性别);
cell.CellStyle = style;
cell = row.CreateCell(3);
cell.SetCellValue(listZhuSu1[i].学生宿舍号);
cell.CellStyle = style;
cell = row.CreateCell(4);
cell.SetCellValue(listZhuSu1[i].房型床位类别MC);
cell.CellStyle = style;
cell = row.CreateCell(5);
cell.SetCellValue(listZhuSu1[i].住宿费充值金额);
cell.CellStyle = style;
cell = row.CreateCell(6);
cell.SetCellValue(listZhuSu1[i].住宿费充值时间time);
cell.CellStyle = style;
cell = row.CreateCell(7);
cell.SetCellValue(listZhuSu1[i].月租价格.ToString());
cell.CellStyle = style;
cell = row.CreateCell(8);
cell.SetCellValue(listZhuSu1[i].本床位本次起住时间time);
cell.CellStyle = style;
cell = row.CreateCell(9);
cell.SetCellValue(listZhuSu1[i].费用截止日期time);
cell.CellStyle = style;
cell = row.CreateCell(10);
cell.SetCellValue(listZhuSu1[i].床位租金余额);
cell.CellStyle = style;
}
for (int k = 0; k < row1.Cells.Count; k++)
{
row1.Cells[k].CellStyle = style;
//列宽17 //设置列宽
sheet1.SetColumnWidth(k, 17 * 256);
sheet1.SetColumnWidth(1, 13 * 256);
sheet1.SetColumnWidth(2, 13 * 256);
sheet1.SetColumnWidth(3, 13 * 256);
sheet1.SetColumnWidth(4, 15 * 256);
sheet1.SetColumnWidth(5, 13 * 256);
sheet1.SetColumnWidth(6, 22 * 256);//6,代表列的位置;22,代表列宽;256 代表sheet页的总宽度
sheet1.SetColumnWidth(7, 13 * 256);
style.VerticalAlignment = VerticalAlignment.Center;
style.Alignment = HorizontalAlignment.Center;
//设置单元格的样式:水平对齐填充
IFont font = excelBook.CreateFont(); //创建一个字体样式对象
font.FontHeightInPoints = 12;//字体大小
font.FontName = "宋体"; //和excel里面的字体对应
style.SetFont(font); //使用SetFont方法将字体样式添加到单元格样式中
cell.CellStyle = style; //将新的样式赋给单元格
}
//5.将Excel文件转化为文件流输出
MemoryStream exStream = new MemoryStream(); //实例化
excelBook.Write(exStream);
//6.输出之前调用Seek(偏移量,游标位置)方法:确定流开始的位置
exStream.Seek(0, SeekOrigin.Begin);
// 7.为下载的Excel文件命名
string exFileName = "宿舍管理-宿舍费报表" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
//返回
return File(exStream, "application/vnd.ms-excel", exFileName);