1000字范文 > poi 导出excel实战与word新增行与excel添加背景色与设置单元格边框

poi 导出excel实战与word新增行与excel添加背景色与设置单元格边框

时间：2020-06-20 09:03:24

提示：本文尽可能简洁通俗的讲解【poi 导出excel实战】，如需导入可见文尾

一、导入依赖：

<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.1.2</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.1.2</version></dependency><dependency><groupId>joda-time</groupId><artifactId>joda-time</artifactId><version>2.10.6</version></dependency>

二、代码实现分步解析：

1.初始化excel模板

// 1.初始化excel模板Workbook workbook = new XSSFWorkbook();

2.创建sheet

// 2.创建sheet1Sheet sheet1 = workbook.createSheet("一级计划");

3.创建行

// 3.创建行Row sheet1row0 = sheet1.createRow(0);

4.创建单元格并设置单元格内容

// 4.创建单元格并设置单元格内容Cell sheet1row0cell0 = sheet1row0.createCell(0);sheet1row0cell0.setCellValue("产品开发一级计划");// 创建单元格直接赋值sheet1row1.createCell(0).setCellValue("序号");

5.输出：

输出形式有输出到response浏览器【ServletOutputStream】或者文件【FileOutputStream 】两种：按需选择

5.1. 输出到response浏览器【ServletOutputStream】：此方案会将文件输出到浏览器

//File file = new File("XXX项目.xlsx");// 6.输出文件 todo 【项目编号/项目名称-计划级别-版本】String filePath = projectPlanExportVO.getPlProjectNo()+"-"+projectPlanExportVO.getPlProjectName()+"-"+str+".xlsx";//6.1清除buffer缓存response.reset();response.setContentType("application/vnd.ms-excel;charset=UTF-8");//response.setHeader("Content-Disposition", "inline; filename="+ new String(filePath.getBytes("UTF-8"), "ISO-8859-1"));// 定义文件名String filename = new String(filePath.getBytes("UTF-8"), "ISO-8859-1");response.setHeader("Content-Disposition", "attachment; filename="+ filename);// 定义文件名//response.setHeader("Content-Disposition", "attachment; filename=" + .URLEncoder.encode(filePath, "UTF-8"));response.setHeader("Pragma", "no-cache");response.setHeader("Cache-Control", "no-cache");response.setHeader("Expires", " 0");BufferedOutputStream bufferedOutputStream = null;try {ServletOutputStream outputStream = response.getOutputStream();bufferedOutputStream = new BufferedOutputStream(outputStream);//6.2用来刷新缓冲区，刷新后可以再次写出bufferedOutputStream.flush();workbook.write(bufferedOutputStream);} catch (Exception e) {e.printStackTrace();} finally {//6.3用来关闭流释放资源//6.4如果是带缓冲区的流对象的close()方法,不仅仅会关闭流,还会在关闭流之前刷新缓冲区，关闭之后不能再写入bufferedOutputStream.close();}return R.success("计划导出成功！");

5.2. 输出到浏览器【ServletOutputStream】：此方案会将文件输出到指定文件

FileOutputStream out = new FileOutputStream("E:\2.xls");workbook.write(out);

三、poi-Excel高阶操作：

1.合并首行单元格：

// 3.1合并首行单元格CellRangeAddress region = new CellRangeAddress(0, 0, 0, 4);sheet1.addMergedRegion(region);

2.单元格格式自定义：

/*** @author: songwl* @Date: /5/18 13:38* @description: DIY单元格格式，设置居中*/public CellStyle setDYICellStyle(Workbook workbook){CellStyle cellStyle = workbook.createCellStyle();cellStyle.setAlignment(HorizontalAlignment.CENTER);return cellStyle;}

3.时间单元格格式自定义：输出时间类别单元格

/*** @author: songwl* @Date: /5/18 13:38* @description: DIY Date类型单元格格式*/public CellStyle setDYICellStyleOfDate(Workbook workbook){CellStyle cellStyle = workbook.createCellStyle();CreationHelper createHelper = workbook.getCreationHelper();short dateFormat = createHelper.createDataFormat().getFormat("yyyy-MM-dd");cellStyle.setDataFormat(dateFormat);cellStyle.setAlignment(HorizontalAlignment.CENTER);return cellStyle;}

四、poi-Word高阶操作：

1.word新增行：

for(int i=0;i<myNeedYow;i++){//copy基准行XWPFTableRow row1 = xwpfTable.getRow(4);CTRow ctrow = CTRow.Factory.parse(row1.getCtRow().newInputStream());//重点行XWPFTableRow row = new XWPFTableRow(ctrow,row1.getTable());row.getTableCells().get(0).getParagraphs().get(0).createRun().setText(String.valueOf(i+1));row.getTableCells().get(1).getParagraphs().get(0).createRun().setText(memberList.get(i).getSysUserName());row.getTableCells().get(2).getParagraphs().get(0).createRun().setText(memberList.get(i).getSysDeptName());//添加xwpfTable.addRow(row,5+i);}//删除基准行xwpfTable.removeRow(4);

2.word合并单元格：

/*** @Description: 跨行合并* table要合并单元格的表格* col要合并哪一列的单元格* fromRow从哪一行开始合并单元格* toRow合并到哪一个行*/public void mergeCellsVertically(XWPFTable table, int col, int fromRow, int toRow) {for (int rowIndex = fromRow; rowIndex <= toRow; rowIndex++) {XWPFTableCell cell = table.getRow(rowIndex).getCell(col);if ( rowIndex == fromRow ) {// The first merged cell is set with RESTART merge valuecell.getCTTc().addNewTcPr().addNewVMerge().setVal(STMerge.RESTART);} else {// Cells which join (merge) the first one, are set with CONTINUEcell.getCTTc().addNewTcPr().addNewVMerge().setVal(STMerge.CONTINUE);}}}

/*** @Description: 跨列合并* table要合并单元格的表格* row要合并哪一行的单元格* fromCell开始合并的单元格* toCell合并到哪一个单元格*/public void mergeCellsHorizontal(XWPFTable table, int row, int fromCell, int toCell) {for (int cellIndex = fromCell; cellIndex <= toCell; cellIndex++) {XWPFTableCell cell = table.getRow(row).getCell(cellIndex);if ( cellIndex == fromCell ) {// The first merged cell is set with RESTART merge value cell.getCTTc().addNewTcPr().addNewHMerge().setVal(STMerge.RESTART);} else {// Cells which join (merge) the first one, are set with CONTINUE cell.getCTTc().addNewTcPr().addNewHMerge().setVal(STMerge.CONTINUE);}}}

3.word新增行无法合并单元格：

方案：在合并后调用下面方法：

/*** @Description: 处理新增行无法合并单元格的问题* 当复制底层CTRow 并使用XWPFTable.addRow* 将其插入CTTbl.TrArray 时，它必须完全完成。* 后面的改动没有写在XML。* 因此，在完成所有更改后，在* 写出之前，请致电commitTableRows*/public void commitTableRows(XWPFTable table) {int rowNr = 0;for (XWPFTableRow tableRow : table.getRows()) {table.getCTTbl().setTrArray(rowNr++, tableRow.getCtRow());}}

五、Excel添加背景色：

/*** @author: songwl* @Date: /5/18 13:38* @description: DIY Date类型单元格格式，带背景色*/public CellStyle setDYICellStyleOfDateAndColor(Workbook workbook) {CellStyle cellStyle = workbook.createCellStyle();//1.设置日期格式CreationHelper createHelper = workbook.getCreationHelper();short dateFormat = createHelper.createDataFormat().getFormat("yyyy-MM-dd");cellStyle.setDataFormat(dateFormat);cellStyle.setAlignment(HorizontalAlignment.CENTER);//2.加背景色cellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());//注意下面这个方法无效//cellStyle.setFillBackgroundColor(IndexedColors.RED.getIndex());cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);return cellStyle;}

//调用

CellStyle cellStyleDYIOfDateAndColor = this.setDYICellStyleOfDateAndColor(workbook);row.createCell(0).setCellStyle(cellStyleDYIOfDateAndColor);

六、设置单元格边框（包含合并的单元格）：

1.为合并的单元格添加边框：

/*** @author: songwl* @Date: /5/18 13:38* @description: DIY单元格格式*/public CellStyle setDYICellStyle(Workbook workbook) {//居中CellStyle cellStyle = workbook.createCellStyle();cellStyle.setAlignment(HorizontalAlignment.CENTER);//边框cellStyle.setBorderBottom(BorderStyle.THIN);cellStyle.setBorderLeft(BorderStyle.THIN);cellStyle.setBorderTop(BorderStyle.THIN);cellStyle.setBorderRight(BorderStyle.THIN);return cellStyle;}

//调用上面方法后，单元格set即可CellStyle cellStyleDYI = this.setDYICellStyle(workbook);sheet1row1.createCell(0).setCellValue("序号");sheet1row1.getCell(0).setCellStyle(cellStyleDYI);

2.合并的单元格添加边框：

/*** @author: songwl* @Date: /5/18 13:38* @description: DIY合并单元格格式*/public CellStyle setDYICellStyleRegion(Workbook workbook,CellRangeAddress region,Sheet sheet) {//居中CellStyle cellStyle = workbook.createCellStyle();cellStyle.setAlignment(HorizontalAlignment.CENTER);// 合并单元格的边框RegionUtil.setBorderBottom(BorderStyle.THIN, region, sheet);RegionUtil.setBorderLeft(BorderStyle.THIN, region, sheet);RegionUtil.setBorderTop(BorderStyle.THIN, region, sheet);RegionUtil.setBorderRight(BorderStyle.THIN, region, sheet);return cellStyle;}

//合并单元格后，调用上面方法即可：CellRangeAddress region1 = new CellRangeAddress(0, 0, 0, totalColumn - 1);sheet.addMergedRegion(region1);CellStyle cellStyleRegion1 = this.setDYICellStyleRegion(workbook,region1,sheet);sheetrow0cell0.setCellStyle(cellStyleRegion1);

如需导入实战请点击：导入实战传送门

本内容不代表本网观点和政治立场，如有侵犯你的权益请联系我们处理。

网友评论

网友评论仅供其表达个人看法，并不表明网站立场。