导出Excel表格样式优化

导出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);