一、设置单元格格式。
设置单元格边框、单元格背景颜色、单元格对齐方式、单元格字体，设置自动换行。
/* * Description: 设置单元格格式. * @author : ys. * @date : 20-3-31 上午11:09 * @param workbook : * @return: org.apache.poi.ss.usermodel.CellStyle **/ public CellStyle setCellStyle(Workbook workbook) { CellStyle cellStyle = workbook.createCellStyle(); //设置边框 cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); //设置背景颜色 cellStyle.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); //设置对齐方式 cellStyle.setAlignment(HorizontalAlignment.CENTER); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //字体设置 Font font = workbook.createFont(); font.setFontName("黑体"); font.setFontHeightInPoints((short) 13); cellStyle.setFont(font); //内容自动换行 cellStyle.setWrapText(true); return cellStyle; }
二、获取单元格中的值。
/* * Description: 读取单元格的值. * @author : ys. * @param cell :单元格对象 * @return: java.lang.String **/ public static String getCellValue(Cell cell) { String cellValue; if (cell == null) { return ""; } switch (cell.getCellType()) { case NUMERIC: cellValue = String.valueOf(cell.getNumericCellValue()); break; case STRING: cellValue = String.valueOf(cell.getStringCellValue()); break; case BOOLEAN: cellValue = String.valueOf(cell.getBooleanCellValue()); break; case BLANK: cellValue = ""; break; default: cellValue = "error"; break; } return cellValue; }
三、合并单元格的方法。
可使用sheet.addMergedRegion(new CellRangeAddress(firstrow, lastrown, firstcolu, lastcolu)); 直接把指定的区域合并为一个合并单元格。
下列方法是一个简单的运用示例，作用是基于列检验指定区域的值，并把值重复的单元格合并。
/* * Description: 指定区域合并单元格.(左闭右开，上闭下开) * @author : ys. * @param sheet : 表格 * @param startcolu : 开始列 * @param stopcolu : 结束列 * @param startrow : 开始行 * @param stoprow : 结束行 * @return: void **/ public void setMergedSomeRegion(Sheet sheet, int startCol, int stopCol, int startRow, int stopRow) { int lastrow = sheet.getPhysicalNumberOfRows(); if (stopRow > lastrow) { stopRow = lastrow; } for (int colIndex = startCol; colIndex < stopCol; colIndex++) { //合并单元格开始行 int begin = startRow; //记录重复个数 int count = 0; for (int rowIndex = startRow; rowIndex < stopRow - 1; rowIndex++) { Cell cell1 = sheet.getRow(rowIndex).getCell(colIndex); Cell cell2 = sheet.getRow(rowIndex + 1).getCell(colIndex); if (cell1.getStringCellValue().equals(cell2.getStringCellValue())) { count++; //遇到重复数据时，更新起点 if (count == 1) { begin = rowIndex; } } else if (count > 0) { //值不再重复时合并单元格 startMerge(sheet, begin, begin + count, colIndex, colIndex); count = 0; } //处理最后一组数据 if (rowIndex == lastrow - 2 && count > 0) { startMerge(sheet, begin, begin + count, colIndex, colIndex); begin = begin + count + 1; count = 0; } } } } private void startMerge(Sheet sheet, int firstrow, int lastrow, int firstcol, int lastcol) { //合并指定单元格 sheet.addMergedRegion(new CellRangeAddress(firstrow, lastrow, firstcol, lastcol)); //设置合并后单元格的边框 CellRangeAddress region = new CellRangeAddress(firstrow, lastrow, firstcol, lastcol); RegionUtil.setBorderTop(BorderStyle.THIN, region, sheet); RegionUtil.setBorderBottom(BorderStyle.THIN, region, sheet); RegionUtil.setBorderLeft(BorderStyle.THIN, region, sheet); RegionUtil.setBorderRight(BorderStyle.THIN, region, sheet); }
四、获取Excel中所有的合并单元格。
合并单元格后只会保留左上角第一个单元格的值，当表格中既有合并单元格，又存在值为空的单元格时，就需要区分这两种单元格。使用sheet的以下方法可以获取到Excel中的所有合并单元格信息，读取到的单元格值为空时，可以通过判断它是否在合并单元格的范围内区分。
public void getExcelMergedRegins(Sheet sheet) { //获取合并单元格的范围 int mergedSheets = sheet.getNumMergedRegions(); for (int i = 0; i < mergedSheets; i++) { CellRangeAddress rangeAddress = sheet.getMergedRegion(i); int rowStart = rangeAddress.getFirstRow(); int rowEnd = rangeAddress.getLastRow(); int columnStart = rangeAddress.getFirstColumn(); int columnEnd = rangeAddress.getLastColumn(); } }
五、导出Excel到指定地址。
此处filename是带有绝对路径的文件名。
关闭流的时候在finally中需要套try catch(关闭两个流的情况需要按顺序写两个try catch)，不然coverity扫描会报错...0o0别问我怎么知道的。
public class ExcelSummarize { private static final Logger LOG = LoggerBuilder.getLogger(ExcelSummarize.class); /* * Description: 导出Excel至指定地址. * @author : ys. * @param workbook : * @param filename : * @return: void **/ public void exportExcelOverwriteLocal(Workbook workbook, String filename) { int count = filename.lastIndexOf("/"); String path = filename.substring(0, count + 1); FileOutputStream fileOutputStream = null; if (filename.endsWith(".xls") || filename.endsWith(".xlsx")) { try { File file = new File(path); if (!file.exists()) { try { file.mkdirs(); } catch (Exception ex) { LOG.info("Create path failed!", ex); } } fileOutputStream = new FileOutputStream(filename); workbook.write(fileOutputStream); LOG.info("Output excel success!"); } catch (IOException ex) { LOG.info("Output excel error!", ex); } finally { try { workbook.close(); if (fileOutputStream != null) { fileOutputStream.close(); } } catch (IOException ex) { LOG.error("Close stream error!", ex); } } } else { LOG.error("Input excel filename format error!"); } } }