参考文章 https://blog.csdn.net/u013585096/article/details/83503519
-
我的结果表格样式
-
//Excel HSSFWorkbook hssfWorkbook = new HSSFWorkbook(); Sheet sheet = hssfWorkbook.createSheet("sheet1"); //表头字体 Font headerFont = hssfWorkbook.createFont(); headerFont.setFontName("微软雅黑"); headerFont.setFontHeightInPoints((short) 18); headerFont.setBoldweight(Font.BOLDWEIGHT_NORMAL); headerFont.setColor(HSSFColor.BLACK.index); //正文字体 Font contextFont = hssfWorkbook.createFont(); contextFont.setFontName("微软雅黑"); contextFont.setFontHeightInPoints((short) 12); contextFont.setBoldweight(Font.BOLDWEIGHT_NORMAL); contextFont.setColor(HSSFColor.BLACK.index); //表头样式，左右上下居中 CellStyle headerStyle = hssfWorkbook.createCellStyle(); headerStyle.setFont(headerFont); headerStyle.setAlignment(CellStyle.ALIGN_CENTER);// 左右居中 headerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中 headerStyle.setLocked(true); headerStyle.setWrapText(false);// 自动换行 headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框 headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 //单元格样式，左右上下居中 边框 CellStyle commonStyle = hssfWorkbook.createCellStyle(); commonStyle.setFont(contextFont); commonStyle.setAlignment(CellStyle.ALIGN_CENTER);// 左右居中 commonStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中 commonStyle.setLocked(true); commonStyle.setWrapText(false);// 自动换行 commonStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 commonStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框 commonStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 commonStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 //单元格样式，左右上下居中 边框 CellStyle commonWrapStyle = hssfWorkbook.createCellStyle(); commonWrapStyle.setFont(contextFont); commonWrapStyle.setAlignment(CellStyle.ALIGN_CENTER);// 左右居中 commonWrapStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中 commonWrapStyle.setLocked(true); commonWrapStyle.setWrapText(true);// 自动换行 commonWrapStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 commonWrapStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框 commonWrapStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 commonWrapStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 //单元格样式，竖向 边框 CellStyle verticalStyle = hssfWorkbook.createCellStyle(); verticalStyle.setFont(contextFont); verticalStyle.setAlignment(CellStyle.ALIGN_CENTER);// 左右居中 verticalStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中 verticalStyle.setRotation((short) 255);//竖向 verticalStyle.setLocked(true); verticalStyle.setWrapText(false);// 自动换行 verticalStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 verticalStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框 verticalStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 verticalStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 //单元格样式，左右上下居中 无边框 CellStyle commonStyleNoBorder = hssfWorkbook.createCellStyle(); commonStyleNoBorder.setFont(contextFont); commonStyleNoBorder.setAlignment(CellStyle.ALIGN_CENTER);// 左右居中 commonStyleNoBorder.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中 commonStyleNoBorder.setLocked(true); commonStyleNoBorder.setWrapText(false);// 自动换行 //单元格样式，左对齐 边框 CellStyle alignLeftStyle = hssfWorkbook.createCellStyle(); alignLeftStyle.setFont(contextFont); alignLeftStyle.setAlignment(CellStyle.ALIGN_LEFT);// 左对齐 alignLeftStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中 alignLeftStyle.setLocked(true); alignLeftStyle.setWrapText(false);// 自动换行 alignLeftStyle.setAlignment(CellStyle.ALIGN_LEFT);// 左对齐 alignLeftStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 alignLeftStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框 alignLeftStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 alignLeftStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 //单元格样式，左对齐 无边框 CellStyle alignLeftNoBorderStyle = hssfWorkbook.createCellStyle(); alignLeftNoBorderStyle.setFont(contextFont); alignLeftNoBorderStyle.setAlignment(CellStyle.ALIGN_LEFT);// 左对齐 alignLeftNoBorderStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中 alignLeftNoBorderStyle.setLocked(true); alignLeftNoBorderStyle.setWrapText(false);// 自动换行 alignLeftNoBorderStyle.setAlignment(CellStyle.ALIGN_LEFT);// 左对齐 //单元格样式，右对齐 CellStyle alignRightStyle = hssfWorkbook.createCellStyle(); alignRightStyle.setFont(contextFont); alignRightStyle.setAlignment(CellStyle.ALIGN_LEFT);// 左对齐 alignRightStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中 alignRightStyle.setLocked(true); alignRightStyle.setWrapText(false);// 自动换行 alignRightStyle.setAlignment(CellStyle.ALIGN_RIGHT);// 左对齐 int rowNum = 0; //设置列宽 for (int i = 0; i < 24; i++) { sheet.setColumnWidth(i, 1800); } //第一行 Row r0 = sheet.createRow(rowNum++); r0.setHeight((short) 800); Cell c00 = r0.createCell(0); c00.setCellValue("数据"); c00.setCellStyle(headerStyle); //合并单元格 sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 23)); //第二行 Row r1 = sheet.createRow(rowNum++); r1.setHeight((short) 500); String[] row_first = {"统计项目","","","活动日期","",ac_date,"","","活动名称","",ac_name,"","","活动地点","",ac_city,"","","小计","","","","",""}; for (int i = 0; i < row_first.length; i++) { Cell tempCell = r1.createCell(i); tempCell.setCellValue(row_first[i]); tempCell.setCellStyle(commonStyle); } //合并单元格 sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 2)); sheet.addMergedRegion(new CellRangeAddress(1, 1, 3, 4)); sheet.addMergedRegion(new CellRangeAddress(1, 1, 5, 7)); sheet.addMergedRegion(new CellRangeAddress(1, 1, 8, 9)); sheet.addMergedRegion(new CellRangeAddress(1, 1, 10, 12)); sheet.addMergedRegion(new CellRangeAddress(1, 1, 13, 14)); sheet.addMergedRegion(new CellRangeAddress(1, 1, 15, 17)); sheet.addMergedRegion(new CellRangeAddress(1, 1, 18, 23)); //第三行 Row r2 = sheet.createRow(rowNum++); r2.setHeight((short) 700); String[] row_second = {"断面编号","","","1","","", "2","","", "3","","", "4","","", "5","","","-","","","","",""}; for (int i = 0; i < row_second.length; i++) { Cell tempCell = r2.createCell(i); tempCell.setCellValue(row_second[i]); tempCell.setCellStyle(commonStyle); } //合并单元格 sheet.addMergedRegion(new CellRangeAddress(2,2,0,2)); sheet.addMergedRegion(new CellRangeAddress(2,2,3,5)); sheet.addMergedRegion(new CellRangeAddress(2,2,6,8)); sheet.addMergedRegion(new CellRangeAddress(2,2,9,11)); sheet.addMergedRegion(new CellRangeAddress(2,2,12,14)); sheet.addMergedRegion(new CellRangeAddress(2,2,15,17)); sheet.addMergedRegion(new CellRangeAddress(2,2,18,23)); //第四行 Row r3 = sheet.createRow(rowNum++); r3.setHeight((short) 700); String[] row_third = {"小组人数","","", group[0],"","", group[1],"","", group[2],"","", group[3],"","", group[4],"","",totalP,"","","","",""}; for (int i = 0; i < row_third.length; i++) { Cell tempCell = r3.createCell(i); tempCell.setCellValue(row_third[i]); tempCell.setCellStyle(commonStyle); } //合并单元格 sheet.addMergedRegion(new CellRangeAddress(3,3,0,2)); sheet.addMergedRegion(new CellRangeAddress(3,3,3,5)); sheet.addMergedRegion(new CellRangeAddress(3,3,6,8)); sheet.addMergedRegion(new CellRangeAddress(3,3,9,11)); sheet.addMergedRegion(new CellRangeAddress(3,3,12,14)); sheet.addMergedRegion(new CellRangeAddress(3,3,15,17)); sheet.addMergedRegion(new CellRangeAddress(3,3,18,23)); //第五行 Row r4 = sheet.createRow(rowNum++); r4.setHeight((short) 700); String[] row_fourth = {"清理长度","","", section[0],"","", section[1],"","", section[2],"","",section[3],"","", section[4],"","",totalL,"","","","",""}; for (int i = 0; i < row_fourth.length; i++) { Cell tempCell = r4.createCell(i); tempCell.setCellValue(row_fourth[i]); tempCell.setCellStyle(commonStyle); } //合并单元格 sheet.addMergedRegion(new CellRangeAddress(4,4,0,2)); sheet.addMergedRegion(new CellRangeAddress(4,4,3,5)); sheet.addMergedRegion(new CellRangeAddress(4,4,6,8)); sheet.addMergedRegion(new CellRangeAddress(4,4,9,11)); sheet.addMergedRegion(new CellRangeAddress(4,4,12,14)); sheet.addMergedRegion(new CellRangeAddress(4,4,15,17)); sheet.addMergedRegion(new CellRangeAddress(4,4,18,23)); //第六行 Row r5 = sheet.createRow(rowNum++); r5.setHeight((short) 700); String[] row_five = {"分类项目", "","","数量", "重量(g)", "","数量", "重量(g)", "","数量", "重量(g)","","数量", "重量(g)","","数量", "重量(g)","","数量小计","","", "重量小计(g)","",""}; for (int i = 0; i < row_five.length; i++) { Cell tempCell = r5.createCell(i); tempCell.setCellValue(row_five[i]); tempCell.setCellStyle(commonStyle); } //合并单元格 sheet.addMergedRegion(new CellRangeAddress(5,5,0,2)); sheet.addMergedRegion(new CellRangeAddress(5,5,3,3)); sheet.addMergedRegion(new CellRangeAddress(5,5,4,5)); sheet.addMergedRegion(new CellRangeAddress(5,5,6,6)); sheet.addMergedRegion(new CellRangeAddress(5,5,7,8)); sheet.addMergedRegion(new CellRangeAddress(5,5,9,9)); sheet.addMergedRegion(new CellRangeAddress(5,5,10,11)); sheet.addMergedRegion(new CellRangeAddress(5,5,12,12)); sheet.addMergedRegion(new CellRangeAddress(5,5,13,14)); sheet.addMergedRegion(new CellRangeAddress(5,5,15,15)); sheet.addMergedRegion(new CellRangeAddress(5,5,16,17)); sheet.addMergedRegion(new CellRangeAddress(5,5,18,20)); sheet.addMergedRegion(new CellRangeAddress(5,5,21,23)); //第N行 for (int i = 0; i < partTwoListSize; i++) { String m1 = partTwoList.get(i).get("m1").toString(); String m2 = partTwoList.get(i).get("m2").toString(); String m3 = partTwoList.get(i).get("m3").toString(); String m4 = partTwoList.get(i).get("m4").toString(); String m5 = partTwoList.get(i).get("m5").toString(); String mj = partTwoList.get(i).get("mj").toString(); String w1 = partTwoList.get(i).get("w1").toString(); String w2 = partTwoList.get(i).get("w2").toString(); String w3 = partTwoList.get(i).get("w3").toString(); String w4 = partTwoList.get(i).get("w4").toString(); String w5 = partTwoList.get(i).get("w5").toString(); String wj = partTwoList.get(i).get("wj").toString(); //合并单元格 sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,0,2)); sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,3,3)); sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,4,5)); sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,6,6)); sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,7,8)); sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,9,9)); sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,10,11)); sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,12,12)); sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,13,14)); sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,15,15)); sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,16,17)); sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,18,20)); sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,21,23)); //数据 Row rn = sheet.createRow(rowNum++); rn.setHeight((short) 700); String[] row_rns = {partTwoList.get(i).get("cate").toString(), "","",m1, w1, "",m2, w2, "",m3, w3,"",m4, w4,"",m5, w5,"",mj,"","", wj,"",""}; for (int j = 0; j < row_rns.length; j++) { Cell tempCell = rn.createCell(j); tempCell.setCellValue(row_rns[j]); tempCell.setCellStyle(commonStyle); } } //总计行 String zm1 = zongJi.get("zm1").toString(); String zm2 = zongJi.get("zm2").toString(); String zm3 = zongJi.get("zm3").toString(); String zm4 = zongJi.get("zm4").toString(); String zm5 = zongJi.get("zm5").toString(); String zmj = zongJi.get("zmj").toString(); String zw1 = zongJi.get("zw1").toString(); String zw2 = zongJi.get("zw2").toString(); String zw3 = zongJi.get("zw3").toString(); String zw4 = zongJi.get("zw4").toString(); String zw5 = zongJi.get("zw5").toString(); String zwj = zongJi.get("zwj").toString(); //合并单元格 sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,0,2)); sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,3,3)); sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,4,5)); sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,6,6)); sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,7,8)); sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,9,9)); sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,10,11)); sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,12,12)); sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,13,14)); sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,15,15)); sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,16,17)); sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,18,20)); sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,21,23)); //数据 Row rzong = sheet.createRow(rowNum++); rzong.setHeight((short) 700); String[] row_zongji = {"总计", "","",zm1, zw1, "",zm2, zw2, "",zm3, zw3,"",zm4, zw4,"",zm5, zw5,"",zmj,"","", zwj,"",""}; for (int i = 0; i < row_zongji.length; i++) { Cell tempCell = rzong.createCell(i); tempCell.setCellValue(row_zongji[i]); tempCell.setCellStyle(commonStyle); } //输出 OutputStream stream = null; try { // 文件名 String fileName = "报表名称.xls"; // 处理文件名包含特殊字符出现的乱码问题 String userAgent = request.getHeader("User-Agent"); if (oConvertUtils.isNotEmpty(userAgent)) { userAgent = userAgent.toLowerCase(); if (userAgent.contains("msie") || userAgent.contains("trident") || userAgent.contains("edge")) { if (fileName.length() > 150) {// 解决IE 6.0问题 fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1"); } else { fileName = URLEncoder.encode(fileName, "UTF-8"); } } else { fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1"); } } response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-disposition", "attachment;filename=\"" + fileName + "\""); stream = response.getOutputStream(); if (null != hssfWorkbook && null != stream) { hssfWorkbook.write(stream); stream.flush(); stream.close(); } } catch (IOException e) { e.printStackTrace(); }渲染数据的时候，由于我得到的数据结构不是很合理，所以很多地方都是直接指明的^_^
652
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
