java 编辑excel文件合并单元格边框显示问题

2024-06-06 19:02| 来源: 网络整理| 查看: 265

现象：

第12行边框显示不完整。

解决方法：

误区以为不用设置跨掉的单元格，第9行是完整单元格，第11行是画了一个格子。

如图：

将单元格数量补全后设置边框样式整个边框就完整了。

实现完整代码：

引入maven：

org.apache.poi poi 4.1.1 org.apache.poi poi-ooxml 4.1.1

代码：

可以直接拷贝运行起来。

package com.weighbridge.test; import java.io.FileOutputStream; import java.text.SimpleDateFormat; import java.util.Date; import java.util.Map; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.BorderStyle; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.FillPatternType; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.VerticalAlignment; import org.apache.poi.ss.util.CellRangeAddress; import org.springframework.util.StringUtils; import com.alibaba.fastjson.JSON; import com.alibaba.fastjson.JSONArray; public class test { public static void main(String[] args) { createdExcel(); } static String format(Object oo){ String object = ""; if (!StringUtils.isEmpty(oo)) { object = oo.toString(); } return object; } // 初始格子 public static void createdExcel() { try { // 创建工作薄对象 HSSFWorkbook workbook = new HSSFWorkbook();// 这里也可以设置sheet的Name // 创建工作表对象 HSSFSheet sheet = workbook.createSheet(); int rowNum = 0; // 跨行参数从0开始行开始行结束列开始列结束 sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0,7)); Row titleRow = sheet.createRow(rowNum++); titleRow.setHeightInPoints(30); Cell titleCell = titleRow.createCell(0); // 设置样式 titleCell.setCellStyle(getStyleTitle(workbook)); titleCell.setCellValue("统计报表"); int height = 20 ; // 负责人 TODO int i=rowNum++; HSSFRow row11=sheet.createRow(i); //第三行 sheet.addMergedRegion(new CellRangeAddress( i, i, 0, 7 )); //第三行的第0列到第7列合并单元格 row11.setHeightInPoints(height); row11.createCell(0).setCellValue("交货单位：一车间 2021年03月09日单位:kg、%vol "); //赋值 row11.createCell(1); //赋值 row11.createCell(2); row11.createCell(3); row11.createCell(4); row11.createCell(5); row11.createCell(6); row11.createCell(7); for (Cell cell : row11) { cell.setCellStyle(getStyleCell(workbook)); } HSSFCellStyle styleCell = getStyleHeadCell(workbook); // 设置列宽 TODO sheet.setColumnWidth(0, 5500); sheet.setColumnWidth(1, 5500); sheet.setColumnWidth(2, 5500); sheet.setColumnWidth(3, 5500); sheet.setColumnWidth(4, 5500); sheet.setColumnWidth(5, 5500); sheet.setColumnWidth(6, 5500); sheet.setColumnWidth(7, 5500); // 创建工作表的行 HSSFRow row = sheet.createRow(rowNum++);// 设置第一行，从零开始 row.setHeightInPoints(height); int is = 0 ; HSSFCell createCell1 = row.createCell(is++); createCell1.setCellStyle(styleCell); createCell1.setCellValue("单位");// 第一行单位 createCell1 = row.createCell(is++); createCell1.setCellStyle(styleCell); createCell1.setCellValue("入库数");// 第一行入库度大 createCell1 = row.createCell(is++); createCell1.setCellStyle(styleCell); createCell1.setCellValue("二入库数");// 第一行入库度二 createCell1 = row.createCell(is++); createCell1.setCellStyle(styleCell); createCell1.setCellValue("合计头");// 第一行合计头 createCell1 = row.createCell(is++); createCell1.setCellStyle(styleCell); createCell1.setCellValue("入库量");// 第一行入库量大 createCell1 = row.createCell(is++); createCell1.setCellStyle(styleCell); createCell1.setCellValue("入库量二");// 第一行入库量二 createCell1 = row.createCell(is++); createCell1.setCellStyle(styleCell); createCell1.setCellValue("合计");// 第一行合计 createCell1 = row.createCell(is++); createCell1.setCellStyle(styleCell); createCell1.setCellValue("扣除损耗合计");// 第一行扣除损耗合计 HSSFRow row1 = sheet.createRow(rowNum++);// 设置第二行，从零开始 row1.setHeightInPoints(height); styleCell = getStyleCell(workbook); String ss = "[ { \"departmentName\": \"一组\", \"hj\": 40.0, \"drclljshh\": 0.0, \"alcoholContent2\": 0.0, \"alcoholContent1\": 65.0, \"jdbdblj\": 0.0, \"optCounter\": 0, \"updatedDate\": 1634199611860, \"delFlag\": 0, \"clljshq\": 0.0, \"jubdbgs\": 0, \"kcshhj\": 39.85, \"createdDate\": 1633934873000, \"clljshh\": 0.0, \"headWeight\": 5.0, \"drjdbdblj\": 0.0, \"jtbdbgs\": 0, \"departmentNo\": \"01010\", \"netWeight1\": 35.0, \"id\": \"0a5d9f2753f34a698f90158f3b2c223a\", \"netWeight2\": 0.0, \"drclljshq\": 0.0 } ]"; JSONArray parseArray = JSON.parseArray(ss); for (Object object : parseArray) { Map map = (Map) object; i = 0 ; HSSFCell createCell2 = row1.createCell(i++); createCell2.setCellStyle(styleCell); createCell2.setCellValue(format(map.get("departmentName")));// 第一行单位 createCell2 = row1.createCell(i++); createCell2.setCellStyle(styleCell); createCell2.setCellValue(format(map.get("alcoholContent1")));//第一行入库度大 createCell2 = row1.createCell(i++); createCell2.setCellStyle(styleCell); createCell2.setCellValue(format(map.get("alcoholContent2")));// 第一行入库度二 createCell2 = row1.createCell(i++); createCell2.setCellStyle(styleCell); createCell2.setCellValue(format(map.get("headWeight")));// 第一行合计头 createCell2 = row1.createCell(i++); createCell2.setCellStyle(styleCell); createCell2.setCellValue(format(map.get("netWeight1")));// 第一行入库量大 createCell2 = row1.createCell(i++); createCell2.setCellStyle(styleCell); createCell2.setCellValue(format(map.get("netWeight2")));// 第一行入库量二 createCell2 = row1.createCell(i++); createCell2.setCellStyle(styleCell); createCell2.setCellValue(format(map.get("hj")));// 第一行合计 createCell2 = row1.createCell(i++); createCell2.setCellStyle(styleCell); createCell2.setCellValue(format(map.get("kcshhj")));// 第一行扣除损耗合计 } // 备注合计 i = rowNum++; HSSFRow rowRemake = sheet.createRow(i); rowRemake.setHeightInPoints(height); // 跨行参数从0开始行开始行结束列开始列结束 sheet.addMergedRegion(new CellRangeAddress(i, i, 0,1)); rowRemake.createCell(0).setCellValue("备注：各组各扣除0.15的损耗量"); //赋值 rowRemake.createCell(1); //赋值 rowRemake.createCell(2).setCellValue("合计"); rowRemake.createCell(3).setCellValue("2"); rowRemake.createCell(4).setCellValue("2"); rowRemake.createCell(5).setCellValue("2"); rowRemake.createCell(6).setCellValue("2"); rowRemake.createCell(7).setCellValue("2"); for (Cell cell : rowRemake) { cell.setCellStyle(getStyleCell(workbook)); } // 负责人 TODO i=rowNum++; row11=sheet.createRow(i); //第三行 row11.setHeightInPoints(height); sheet.addMergedRegion(new CellRangeAddress( i, i, 0, 7 )); //第三行的第0列到第7列合并单元格 row11.createCell(0).setCellValue("负责人：统计员：报出日期： "); //赋值 row11.createCell(1); //赋值 row11.createCell(2); row11.createCell(3); row11.createCell(4); row11.createCell(5); row11.createCell(6); row11.createCell(7); for (Cell cell : row11) { cell.setCellStyle(getStyleCell(workbook)); } height = 20 ; // 负责人 TODO i=9; // 9 行 row11=sheet.createRow(i); //第三行 sheet.addMergedRegion(new CellRangeAddress( i, i, 0, 7 )); //第三行的第0列到第7列合并单元格 row11.setHeightInPoints(height); row11.createCell(0).setCellValue("测试跨行"); //赋值 row11.createCell(1); //赋值 row11.createCell(2); row11.createCell(3); row11.createCell(4); row11.createCell(5); row11.createCell(6); row11.createCell(7); for (Cell cell : row11) { cell.setCellStyle(getStyleCell(workbook)); } height = 20 ; // 负责人 TODO i=11; // 11 行 row11=sheet.createRow(i); //第三行 sheet.addMergedRegion(new CellRangeAddress( i, i, 0, 7 )); //第三行的第0列到第7列合并单元格 row11.setHeightInPoints(height); row11.createCell(0).setCellValue("测试跨行2"); //赋值 // row11.createCell(1); //赋值 // row11.createCell(2); // row11.createCell(3); // row11.createCell(4); // row11.createCell(5); // row11.createCell(6); // row11.createCell(7); for (Cell cell : row11) { cell.setCellStyle(getStyleCell(workbook)); } workbook.setSheetName(0, "sheetName");// 设置sheet的Name // 文档输出d FileOutputStream out = new FileOutputStream( "C:\\Users\\admin\\Desktop\\test\\" + new SimpleDateFormat("yyyyMMddHHmmss").format( new Date()).toString() + ".xls"); workbook.write(out); out.close(); } catch (Exception e) { e.printStackTrace(); } } /* * 标题样式 */ public static HSSFCellStyle getStyleTitle(HSSFWorkbook workbook) { // 设置字体 HSSFFont font = workbook.createFont(); //设置字体大小 font.setFontHeightInPoints((short)15); //字体加粗 font.setBold(true); //设置字体名字 font.setFontName("Arial"); //设置样式; HSSFCellStyle style = workbook.createCellStyle(); //在样式用应用设置的字体; style.setFont(font); //设置自动换行; style.setWrapText(false); //设置水平对齐的样式为居中对齐; style.setAlignment(HorizontalAlignment.CENTER); //设置垂直对齐的样式为居中对齐; style.setVerticalAlignment(VerticalAlignment.CENTER); style.setWrapText(true); return style; } /* * 列数据信息单元格样式 */ public static HSSFCellStyle getStyleCell(HSSFWorkbook workbook) { // 设置字体 HSSFFont font = workbook.createFont(); //设置字体大小 font.setFontHeightInPoints((short)12); //字体加粗 // font.setBold(true); //设置字体名字 font.setFontName("Arial"); //设置样式; HSSFCellStyle style = workbook.createCellStyle(); //在样式用应用设置的字体; style.setFont(font); //设置自动换行; style.setWrapText(false); //设置水平对齐的样式为居中对齐; style.setAlignment(HorizontalAlignment.CENTER); //设置垂直对齐的样式为居中对齐; style.setVerticalAlignment(VerticalAlignment.CENTER); //设置样式对象，这里仅设置了边框属性 style.setBorderBottom(BorderStyle.THIN); //下边框 style.setBorderLeft(BorderStyle.THIN);//左边框 style.setBorderTop(BorderStyle.THIN);//上边框 style.setBorderRight(BorderStyle.THIN);//右边框 return style; } /* * 头列数据信息单元格样式 */ public static HSSFCellStyle getStyleHeadCell(HSSFWorkbook workbook) { //设置样式; HSSFCellStyle style = workbook.createCellStyle(); style = workbook.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); Font headerFont = workbook.createFont(); headerFont.setFontName("Arial"); headerFont.setFontHeightInPoints((short) 10); headerFont.setBold(true); headerFont.setColor(IndexedColors.WHITE.getIndex()); style.setFont(headerFont); //设置垂直对齐的样式为居中对齐; style.setVerticalAlignment(VerticalAlignment.CENTER); return style; } }

【本文地址】

公司简介

联系我们