JAVA POI导出EXCEL设置自定义样式（线框加粗，合并指定行，合计求和，冻结行） |
您所在的位置：网站首页 › excel填充边框线 › JAVA POI导出EXCEL设置自定义样式（线框加粗，合并指定行，合计求和，冻结行） |
JAVA POI导出EXCEL设置自定义样式（线框加粗，合并指定行，合计求和，冻结行）
|
前面部分是当时查询的记录： 实现Excel文件单元格合并、冻结和文件导出 Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("sheet1"); Row row = null; Cell cell = null; //创建表头单元格样式 CellStyle cs_header = wb.createCellStyle(); Font boldFont = wb.createFont(); boldFont.setFontName("Consolas"); boldFont.setFontHeightInPoints((short)14); cs_header.setFont(boldFont); cs_header.setBorderBottom((short)1); cs_header.setBorderLeft((short)1); cs_header.setBorderRight((short)1); cs_header.setBorderTop((short)1); //第一行，时间 row = sheet.createRow((short)0); row.setHeightInPoints((short)24); createMultiCell(row,cell,cs_header,0,31,"访问日期："+time1+" to "+time2); //第二行，大标题 row = sheet.createRow((short)1); row.setHeightInPoints((short)24); createMultiCell(row,cell,cs_header,0,6," "); createMultiCell(row,cell,cs_header,6,25,"访问路径"); //第三行，列标题 row = sheet.createRow((short)2); row.setHeightInPoints((short)24); String[] headers = new String[]{"访问时间","地域","来源","关键字","进入页","停留时间","访问页数", "1时间","1停留","1页面","2时间","2停留","2页面","3时间","3停留","3页面","4时间","4停留","4页面", "5时间","5停留","5页面","6时间","6停留","6页面","7时间","7停留","7页面","8时间","8停留","8页面"}; for(int i=0; i row = sheet.createRow((short)(i+3)); Object[] rw = list.get(i); for(int j=0; j //将workbook写到输入流(下载时候，这个输出流可能是ServletOutStream，写入文件是FileOutputStream，等等) wb.write(os); } catch (IOException e) { e.printStackTrace(); }
导出Excel每列求和,POI导出excel执行自动求和 for(int i = 0; i < arrSize; i++) { JSONObject item = dataListArr.getJSONObject(i); HSSFRow row = sheet.createRow(i + 1); HSSFCell cell0 = row.createCell(0); cell0.setCellValue(i + 1); HSSFCell cell1 = row.createCell(1); cell1.setCellValue(item.get("farmerName") != null ? item.get("farmerName").toString() : ""); HSSFCell cell2 = row.createCell(2); //0对应的是double型数值 新版本此方法过期可以用新方法 cell2.setCellType(0); cell2.setCellValue(item.get("crabCultivationSpecification") != null ? Double.valueOf(item.get("crabCultivationSpecification").toString()) : 0.0); ... HSSFCell cell8 = row.createCell(8); cell8.setCellValue(item.get("cultivationTime") != null && !"".equals(item.get("cultivationTime").toString())? BusinessService.formatTime(item.get("cultivationTime").toString().substring(0, 8)) : ""); } //合计功能 sum也可以替换成其他的函数 HSSFRow row = sheet.createRow(arrSize+1); HSSFCell cell = row.createCell(0); cell.setCellValue("合计"); for (int i = 2; i < 8; i++) { //i代表是第几列 cell = row.createCell(i);//设置公式前，一定要先建立表格 String colString = CellReference.convertNumToColString(i); //长度转成ABC列 String sumstring = "SUM(" + colString + "2:" + colString + (arrSize+1) + ")";//求和公式 sheet.getRow(arrSize+1).getCell(i).setCellFormula(sumstring); }需要注意的是: arrSize代表是行数,在求和公式中arrSize需要+1,不然统计的行数不正确,因为合计是占一行的(想不明白就自己新建个excel),同理求和函数也可以换成excel中其他的函数. String colString = CellReference.convertNumToColString(i); //长度转成ABC列这句话就是把行列转成ABCD那种格式,想想求和公式=SUM(A1:A4)就是转成里面的A这种,实在想不明白就别想了,记住要加就行. 期间遇到一个问题就是,数据库导出的数据写到excel中是String格式,后来百度了一下才知道,源数据是什么类型写到excel中就是什么类型,用cell2.setCellType(0);就行. 原文：原文 本人在实际运用时： ExcelWriter writer = ExcelUtil.getBigWriter(); //为大标题小标题先添加行 if(StrUtil.isNotBlank(titleName)) { rows.add(0, Collections.emptyList()); y++; }else { if (StrUtil.isNotBlank(description)) { for (int i = 0; i < y; i++) { rows.add(i, Collections.emptyList()); } } } if(StrUtil.isNotBlank(minTitle)){ rows.add(0, Collections.emptyList()); y++; } //添加字段-标题到首行 titleList.addAll(multiDataTitle.values()); rows.add(y, titleList); //添加需要合计的列 int rowNum = rows.size() + 1; if(!CollectionUtils.isEmpty(sumCellList)){ rowNum = this.handelSumColum(writer, rows, sumCellList, columnList, y); } //合并计算出来的合并对象 writer.write(rows,true); if(StrUtil.isNotBlank(minTitle)){ //设置查询时间 writer.merge(1, 1, 0, Math.max(titleList.size()-1,x), new HSSFRichTextString(minTitle), false); } if(StrUtil.isNotBlank(titleName)){ writeTitle(titleName, writer, Math.max(titleList.size()-1,x), rowNum, minTitle, y); }else { if (StrUtil.isNotBlank(description)) { writeDescription(excelFile.description(), writer, Math.max(titleList.size()-1,x), y); } } private int handelSumColum(ExcelWriter writer, List rows, List sumCellList, List columnList, int y){ Row row = writer.getSheet().createRow(rows.size()); for (Integer i : sumCellList) { Cell cell = row.createCell(i-1); String colString = CellReference.convertNumToColString(i-1);//长度转成ABC列 //y为标题行，y+1为 数据开始行 String sumString = "SUM(" + colString + (y+1) +":" + colString + rows.size() + ")"; cell.setCellFormula(sumString);// 把公式塞入合计列 CellStyle cellStyle = cell.getCellStyle(); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setAlignment(HorizontalAlignment.CENTER); } //获取合计list中第一个值为columnList的下标，即为第一个要合计的值在excel中的实际列 int index = columnList.indexOf(sumCellList.stream().findFirst().get()) - 1; //设置合计列 if(index > 0){ writer.merge(row.getRowNum(), row.getRowNum(), 0, index, new HSSFRichTextString("合计"), false); }else{ writer.writeCellValue(index, row.getRowNum(), "合计"); } return row.getRowNum(); } private void writeTitle(String title, ExcelWriter writer, int x, int rowNum, String minTitle, int y){ //设置标题头 writer.merge(0, 0, 0, x, new HSSFRichTextString(title), true); StyleSet style = writer.getStyleSet(); CellStyle cellStyle = style.getHeadCellStyle(); cellStyle.setAlignment(HorizontalAlignment.CENTER); Font font = writer.createFont(); font.setFontName("MS Sans Serif"); font.setBold(true); font.setFontHeightInPoints((short) 24); //字体大小 cellStyle.setFont(font); cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex()); writer.setStyle(cellStyle, 0, 0); //设置表头样式 CellStyle headStyle = style.getHeadCellStyle(); Font headFont = writer.createFont(); headFont.setBold(true); headFont.setFontName("微软雅黑"); headFont.setFontHeightInPoints((short) 10); headStyle.setFont(headFont); headStyle.setAlignment(HorizontalAlignment.CENTER); for (int i = 0; i columStyle.put(excelRow.column(),excelRow.alignmentStyle()); }再将指定的单元格进行设置样式。 Sheet sheet1 = writer.getSheet(); Workbook workbook = sheet1.getWorkbook(); if(!columStyle.isEmpty()){ for (int i = y+1; i < rows.size(); i++) { for (int j = 0; j HorizontalAlignment horizontalAlignment = columStyle.get(j); generateRowAndCell(sheet1,workbook,i,j-1,horizontalAlignment, VerticalAlignment.BOTTOM); } } } } private void generateRowAndCell(Sheet sheet, Workbook workbook, int rowIndex, int cellIndex, HorizontalAlignment horizontalAlignment, VerticalAlignment verticalAlignment) { // 创建row，获取行 Row row = sheet.getRow(rowIndex); // 创建cell, 设置样式，获取列 Cell cell = row.getCell(cellIndex); CellStyle cellStyle = workbook.createCellStyle(); // 水平对齐 cellStyle.setAlignment(horizontalAlignment); // 垂直对齐 cellStyle.setVerticalAlignment(verticalAlignment); cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setBorderBottom(BorderStyle.THIN); cell.setCellStyle(cellStyle); } |
上文原文链接：原文地址【本文地址】
今日新闻 |
推荐新闻 |