依赖导入
<!-- 基本依赖,仅操作 xls 格式只需引入此依赖 --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.17</version> </dependency> <!-- 使用 xlsx 格式需要额外引入此依赖 --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.17</version> </dependency>
说明
● poi:读取Excel 2003及以下版本的文件,也就是后缀为.xls的文件
Workbook workbook = new HSSFWorkbook();
●poi-ooxml:读取Excel 2007及以上的文件,也就是后缀为.xlsx的文件
Workbook workbook =new XSSFWorkbook();
一、Apache POI常用的类
- HSSF - 提供读写Microsoft Excel XLS格式档案的功能。
- XSSF - 提供读写Microsoft Excel OOXML XLSX格式档案的功能。
- HWPF - 提供读写Microsoft Word DOC97格式档案的功能。
- XWPF - 提供读写Microsoft Word DOC2003格式档案的功能
二、常用的类和方法
HSSFWorkbook
工作簿,代表一个excel的整个文档
- HSSFWorkbook(); // 创建一个新的工作簿
- HSSFWorkbook(InputStream inputStream); // 创建一个关联输入流的工作簿,可以将一个excel文件封装成工作簿
- HSSFSheet createSheet(String sheetname); 创建一个新的Sheet
- HSSFSheet getSheet(String sheetName); 通过名称获取Sheet
- HSSFSheet getSheetAt(int index); // 通过索引获取Sheet,索引从0开始
- HSSFCellStyle createCellStyle(); 创建单元格样式
- int getNumberOfSheets(); 获取sheet的个数
- setActiveSheet(int index); 设置默认选中的工作表
- write();
- write(File newFile);
- write(OutputStream stream);
HSSFSheet:工作表
- HSSFRow createRow(int rownum); 创建新行,需要指定行号,行号从0开始
- HSSFRow getRow(int index); 根据索引获取指定的行
- int addMergedRegion(CellRangeAddress region); 合并单元格
- CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol); 单元格范围, 用于合并单元格,需要指定要合并的首行、最后一行、首列、最后一列。
- autoSizeColumn(int column); 自动调整列的宽度来适应内容
- getLastRowNum(); 获取最后的行的索引,没有行或者只有一行的时候返回0
- setColumnWidth(int columnIndex, int width); 设置某一列的宽度,width=字符个数 * 256,例如20个字符的宽度就是20 * 256
HSSFRow :行
- HSSFCell createCell(int column); 创建新的单元格
- HSSFCell setCell(shot index);
- HSSFCell getCell(shot index);
- setRowStyle(HSSFCellStyle style); 设置行样式
- short getLastCellNum(); 获取最后的单元格号,如果单元格有第一个开始算,lastCellNum就是列的个数
- setHeightInPoints(float height); 设置行的高度
HSSFCell:单元格
- setCellValue(String value); 设置单元格的值
- setCellType(); 设置单元格类型,如 字符串、数字、布尔等
- setCellStyle(); 设置单元格样式
- String getStringCellValue(); 获取单元格中的字符串值
- setCellStyle(HSSFCellStyle style); 设置单元格样式,例如字体、加粗、格式化
- setCellFormula(String formula); 设置计算公式,计算的结果作为单元格的值,也提供了异常常用的函数,如求和"sum(A1,C1)"、日期函数、字符串相关函数、CountIf和SumIf函数、随机数函数等
HSSFCellStyle :单元格样式
- setFont(Font font); 为单元格设置字体样式
- setAlignment(HorizontalAlignment align); // 设置水平对齐方式
- setVerticalAlignment(VerticalAlignment align); // 设置垂直对齐方式
- setFillPattern(FillPatternType fp);
- setFillForegroundColor(short bg); 设置前景色
- setFillBackgroundColor(short bg); 设置背景颜色
HSSFFont:字体
- setColor(short color); // 设置字体颜色
- setBold(boolean bold); // 设置是否粗体
- setItalic(boolean italic); 设置倾斜
- setUnderline(byte underline); 设置下划线
其他
- HSSFName:名称
- HSSFDataFormat :日期格式化
- HSSFHeader : Sheet的头部
- HSSFFooter :Sheet的尾部
- HSSFDateUtil :日期工具
- HSSFPrintSetup :打印设置
- HSSFErrorConstants:错误信息表
Excel中的工作簿、工作表、行、单元格中的关系:
一个Excel文件对应于一个workbook(HSSFWorkbook),
一个workbook可以有多个sheet(HSSFSheet)组成,
一个sheet是由多个row(HSSFRow)组成,
一个row是由多个cell(HSSFCell)组成
二、基本操作excle
从Excel文件读取数据(从一个已经存在的Excel文件中读取数据)
第一个方法:遍历工作表获得行,遍历行获得单元格,最终获取单元格中的值
//创建工作簿 XSSFWorkbook workbook = new XSSFWorkbook("D:\hello.xlsx"); //获取工作表,既可以根据工作表的顺序获取,也可以根据工作表的名称获取 XSSFSheet sheet = workbook.getSheetAt(0); //遍历工作表获得行对象 for (Row row : sheet) { //遍历行对象获取单元格对象 for (Cell cell : row) { //获得单元格中的值 String value = cell.getStringCellValue();//这里的value值取出来自行操作 System.out.println(value); } } workbook.close();
第二个方法:根据单元格索引获取每行的一个单元格对象
//创建工作簿 XSSFWorkbook workbook = new XSSFWorkbook("D:\hello.xlsx"); //获取工作表,既可以根据工作表的顺序获取,也可以根据工作表的名称获取 XSSFSheet sheet = workbook.getSheetAt(0); //获取当前工作表最后一行的行号,行号从0开始 int lastRowNum = sheet.getLastRowNum(); for(int i=0;i<=lastRowNum;i++){ //根据行号获取行对象 XSSFRow row = sheet.getRow(i); short lastCellNum = row.getLastCellNum(); for(short j=0;j<lastCellNum;j++){ String value = row.getCell(j).getStringCellValue(); System.out.println(value); //这里的value值取出来自行操作 } } workbook.close();
向Excel文件写入数据
样式封装
public class BaseExcel { protected HSSFWorkbook workbook; protected HSSFSheet sheet; public BaseExcel(){ this.workbook = new HSSFWorkbook(); this.sheet = this.workbook.createSheet("sheet"); } //合并单元格 protected CellRangeAddress cellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol){ CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol); return region; } /** * 设置单元格字体大小 */ protected void setFontSize(Cell cell, int fontSize, boolean bold , String fontName) { Font font = this.workbook.createFont(); font.setFontName(fontName); font.setFontHeightInPoints((short)fontSize); if(bold){ //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗 font.setBold(true); } //解决单元格样式覆盖的问题 CellStyle cStyle = this.workbook.createCellStyle(); cStyle.cloneStyleFrom(cell.getCellStyle()); cStyle.setWrapText(true); cStyle.setFont(font); cell.setCellStyle(cStyle); } protected void setBorder(CellRangeAddress cellRangeAddress){ RegionUtil.setBorderBottom(1, cellRangeAddress, this.sheet); // 下边框 RegionUtil.setBorderLeft(1, cellRangeAddress, this.sheet); // 左边框 RegionUtil.setBorderRight(1, cellRangeAddress, this.sheet); // 有边框 RegionUtil.setBorderTop(1, cellRangeAddress, this.sheet); // 上边框 } protected void setMargin(){ //设置打印参数 this.sheet.setMargin(HSSFSheet.TopMargin,( double ) 0.2);// 页边距(上) this.sheet.setMargin(HSSFSheet.BottomMargin,( double ) 0.2);// 页边距(下) this.sheet.setMargin(HSSFSheet.LeftMargin,( double ) 0.2 );// 页边距(左) this.sheet.setMargin(HSSFSheet.RightMargin,( double ) 0.2);// 页边距(右 this.sheet.setHorizontallyCenter(true);//设置打印页面为水平居中 this.sheet.setVerticallyCenter(true);//设置打印页面为垂直居中 }
生成excel (合并单元格,单元格边框,计算公式,为单元格生成一个自定义的数据显示格式)
public class TestExcel5 extends BaseExcel { public void TestMain() throws FileNotFoundException { String filePath="D:\\Langyinkeji\\生成的.xls";//文件路径 HSSFCellStyle style = this.workbook.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); //居中 style.setVerticalAlignment(VerticalAlignment.CENTER);//居中 style.setBorderBottom(BorderStyle.THIN); //边框样式 style.setBorderLeft(BorderStyle.THIN); style.setBorderRight(BorderStyle.THIN); style.setBorderTop(BorderStyle.THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); this.sheet.setColumnWidth(0, (short) (9.13*256)); // 每列格子的宽度 this.sheet.setColumnWidth(1, (short) (6.72*256)); // 5 this.sheet.setForceFormulaRecalculation(true); //设置打印样式(页边距和居中) super.setMargin(); //第一行 HSSFRow row2 = this.sheet.createRow(0); row2.setHeight((short) (30*20)); //每行的高度 HSSFCell cell_2_1 = row2.createCell(0); //第一列 cell_2_1.setCellStyle(style); cell_2_1.setCellValue("工程编号"); // 第一行第一列的值 //设置字体大小 setFontSize(cell_2_1,10,true,"黑体"); //合并单元格 (第0行,第0行,第0列,第一列) CellRangeAddress region_cell_2_1 = cellRangeAddress(0, 0, 0, 1); this.sheet.addMergedRegion(region_cell_2_1); //设置边框 setBorder(region_cell_2_1); HSSFCell cell_2_2 = row2.createCell(2); //第二列 cell_2_2.setCellStyle(style); cell_2_2.setCellValue(1); //设置字体大小 setFontSize(cell_2_2,10,false,"黑体"); //合并单元格(第0行,第0行,第2列,第4列) CellRangeAddress region_cell_2_2 = cellRangeAddress(0, 0, 2, 4); this.sheet.addMergedRegion(region_cell_2_2); //设置边框 setBorder(region_cell_2_2); HSSFCell cell_2_3 = row2.createCell(5); cell_2_3.setCellStyle(style); cell_2_3.setCellValue(2); HSSFCell cell_2_4 = row2.createCell(6); cell_2_4.setCellStyle(style); cell_2_4.setCellValue(2); String str="SUM(C1:F1)"; //这里是excel里的算和的公式 excel里啥格式就啥格式 // (C1:F1);这里是计算的位置 可以自行拼接 cell_2_4.setCellFormula(str); //设置计算格式 exlcel里支持的这里都支持。 HSSFCell cell_2_5 = row2.createCell(7); //第7列 char a= '"'; short f = this.workbook.createDataFormat().getFormat( a+"M"+a+"#" ); //为单元格生成一个自定义的数据显示格式 HSSFCellStyle style2 = this.workbook.createCellStyle(); style2.setWrapText(true); style2 .setDataFormat( f ); cell_2_5.setCellStyle(style2); cell_2_5.setCellValue(33); FileOutputStream out = new FileOutputStream(filePath); try { workbook.write(out);//保存Excel文件 System.out.println("OK!"); } catch (Exception e) { e.printStackTrace(); }finally{ if(out != null){ try { out.close();//关闭文件流 } catch (IOException e) { e.printStackTrace(); } } } } public static void main(String[] args) throws FileNotFoundException { TestExcel5 testExcel=new TestExcel5(); testExcel.TestMain(); } }
生成效果:
3是根据计算公式生成的 fx=sum(C1:F1) C1的位置到F2的位置
为单元格生成一个自定义的数据显示格式。这样生成的即时有M的字符依然可以进行计算。
双击进去实际是33 M是设置的一个格式
读取excel 在生成图片
public class ExcelImageTest { public static void main(String[] args) { FileOutputStream fileOut = null; BufferedImage bufferImg = null; InputStream is = null; //先把读进来的图片放到一个ByteArrayOutputStream中,以便产生ByteArray try { ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream(); bufferImg = ImageIO.read(new File("D:\\Langyinkeji\\图片1.png")); ImageIO.write(bufferImg, "JPG", byteArrayOut); FileInputStream fileInputStream = null; // POIFSFileSystem fs=new POIFSFileSystem(new FileInputStream("D:\\Langyinkeji\\FU.xlsx")); XSSFWorkbook wb=new XSSFWorkbook(new FileInputStream("D:\\Langyinkeji\\FU.xlsx")); // HSSFWorkbook wb = new HSSFWorkbook(fs); XSSFSheet sheet = wb.getSheetAt(0); XSSFRow row = sheet.getRow(0); //画图的顶级管理器,一个sheet只能获取一个(一定要注意这点) XSSFDrawing patriarch = sheet.createDrawingPatriarch(); //anchor主要用于设置图片的属性 XSSFClientAnchor anchor = new XSSFClientAnchor(30, 0, 255, 255,(short) 1, 11, (short) 3, 15); //第1列,第11行,第三列 ,第15行 anchor.setAnchorType(ClientAnchor.AnchorType.byId(3)); //插入图片 patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), XSSFWorkbook.PICTURE_TYPE_JPEG)); fileOut = new FileOutputStream("D:\\Langyinkeji\\FU.xlsx"); // 写入excel文件 wb.write(fileOut); System.out.println("----Excle文件已生成------"); } catch (Exception e) { e.printStackTrace(); }finally{ if(fileOut != null){ try { fileOut.close(); } catch (IOException e) { e.printStackTrace(); } } } } }
生成效果:
图片上面是原来excel的内容 ,图片是读取完之后添加上去的
java实现往已有内容word文档中追加图片功能简单实现
public class InsertPic { /**java实现往已有内容word文档中追加图片功能简单实现 * * */ public static void main(String[] args)throws Exception { // 创建一个document对象,相当于新建一个word文档(后缀名为.docx)。. InputStream is = new FileInputStream("D:\\Langyinkeji\\1234.docx"); // XWPFDocument document=new XWPFDocument(); XWPFDocument document=new XWPFDocument(OPCPackage.open(is)); // 创建一个段落对象 XWPFParagraph paragraph=document.createParagraph(); paragraph.setAlignment(ParagraphAlignment.RIGHT); //这里是新开一个段落生成在右下角 // 创建一个run。run具体是什么,我也不知道。但是run是这里面的最小单元了。 XWPFRun run=paragraph.createRun(); run.addPicture(new FileInputStream("D:\\Langyinkeji\\AA.JPG"), XWPFDocument.PICTURE_TYPE_PNG, "IMG_1394(20200630-135033).JPG", Units.toEMU(80), Units.toEMU(80)); // 创建一个输出流 即是该文档的保存位置 OutputStream outputStream=new FileOutputStream("D:\\Langyinkeji\\1234.docx"); document.write(outputStream); outputStream.close(); System.out.println("成功"); } }
生成效果:
图片上面是原来word的内容 ,图片是读取完之后添加上去的
word指定位置生成图片:
public class DealDocx3 { private static final int BLANK_SPACE_NUM = 2; public static void main(String[] args) throws IOException, InvalidFormatException { File inputFile = new File("D:\\Langyinkeji\\ce.docx"); File outputFile = new File("D:\\Langyinkeji\\1235.docx"); InputStream inputStream = new FileInputStream(inputFile); FileOutputStream outputStream = new FileOutputStream(outputFile); dealDocx(inputStream, outputStream); } /** * 处理docx * * @param inputStream * @param outputStream * @throws IOException */ private static void dealDocx(InputStream inputStream, OutputStream outputStream) throws IOException, InvalidFormatException { // 读取docx XWPFDocument xwpfDocument = new XWPFDocument(inputStream); String key="二维码"; // String key=""; // 读取所有段落 存入列表 List<XWPFParagraph> paragraphs = xwpfDocument.getParagraphs(); List<XWPFPictureData> pictures = xwpfDocument.getAllPictures(); PackagePartName partName=null; // 删除图片 // for(int i=0;i<pictures.size();i++) { // partName=pictures.get(i).getPackagePart().getPartName(); // System.out.println(partName); // if(null!=partName) { // String name = partName.getName(); // String substring = name.substring(name.lastIndexOf(".") + 1); // if (substring.equals("jpeg")){ // PackagePart part = xwpfDocument.getPackage().getPart(partName); // if(null!=part) { // //删除图片流 删除引用 // part.setDeleted(true); // String id2 = xwpfDocument.getRelationId(pictures.get(i)); // xwpfDocument.getPackagePart().removeRelationship(id2); // xwpfDocument.getPackage().removePart(partName); // // // 创建一个段落对象。 // XWPFParagraph paragraph = xwpfDocument.getLastParagraph(); // paragraph.setAlignment(ParagraphAlignment.RIGHT); // // 创建一个run。 // XWPFRun run = paragraph.createRun(); // run.addPicture(new FileInputStream("D:\\Langyinkeji\\图片1.png"), // XWPFDocument.PICTURE_TYPE_PNG, // "IMG_1394(20200630-135033).JPG", // Units.toEMU(120), // Units.toEMU(120)); // } // } // } // } // int count=0; // if (paragraphs != null && paragraphs.size() > 0) { // for (XWPFParagraph paragraph : paragraphs) { // List<XWPFRun> runs = paragraph.getRuns(); // for (XWPFRun run : runs) { // String text = run.getText(0); // System.out.println(text); // if (text != null) { // if (text.indexOf(key) >= 0) { // count++; // // } // } // } // } // } if (paragraphs != null && paragraphs.size() > 0) { for (XWPFParagraph paragraph : paragraphs) { List<XWPFRun> runs = paragraph.getRuns(); for (XWPFRun run : runs) { String text = run.getText(0); System.out.println(text); if (text != null) { if (text.indexOf(key) >= 0) { run.setText("", 0);//清空所有文字 //这里是把原来的二维码字样删除 run.addBreak(); run.addPicture(new FileInputStream("D:\\Langyinkeji\\图片1.png"), XWPFDocument.PICTURE_TYPE_PNG, "IMG_1394(20200630-135033).JPG", Units.toEMU(120), //图片大小 Units.toEMU(120)); } } } } } // 写入输出文件 xwpfDocument.write(outputStream); } }
动态合并excel单元格
//拼数据 int max_a1 = -1; int max_a2 = -1; int max_a3 = -1; int max_a4 = -1; int max_a5 = -1; //临时存放obj JSONObject tmpobj = new JSONObject(); JSONObject tmpobj2 = new JSONObject(); for (int j = 0; j < jsonArrayData.size(); j++) { tmpobj = (JSONObject) jsonArrayData.get(j);//获取json数组里的值 if( j <= max_a1){ tmpobj.put("A5",-1); continue; } if(j+1 == jsonArrayData.size()){ if( j <= max_a1){ tmpobj.put("A5",-1); continue; } }else{ System.out.println("222222"); for (int k1 = j+1; k1 < jsonArrayData.size(); k1++) { tmpobj2 = (JSONObject) jsonArrayData.get(k1); int m = k1 - j ; //加多少行 if(tmpobj.get("A1").equals(tmpobj2.get("A1")) ){ tmpobj.put("A5",m); max_a1 = k1; //k1不需要渲染 }else{ break; } System.out.println("max_a1=="+max_a1); } } } for (int j = 0; j < jsonArrayData.size(); j++) { tmpobj = (JSONObject) jsonArrayData.get(j); if( j <= max_a2){ tmpobj.put("A6",-1); continue; } if(j+1 == jsonArrayData.size()){ if( j <= max_a2){ tmpobj.put("A6",-1); continue; } }else{ for (int k1 = j+1; k1 < jsonArrayData.size(); k1++) { tmpobj2 = (JSONObject) jsonArrayData.get(k1); int m = k1 - j ; //加多少行 if(tmpobj.get("A2").equals(tmpobj2.get("A2")) ){ tmpobj.put("A6",m); max_a2 = k1; //k1不需要渲染 }else{ break; } } } } for (int j = 0; j < jsonArrayData.size(); j++) { tmpobj = (JSONObject) jsonArrayData.get(j); if( j <= max_a3){ tmpobj.put("A7",-1); continue; } if(j+1 == jsonArrayData.size()){ if( j <= max_a3){ tmpobj.put("A7",-1); continue; } }else{ for (int k1 = j+1; k1 < jsonArrayData.size(); k1++) { System.out.println("j=="+j+" k1=="+k1); tmpobj2 = (JSONObject) jsonArrayData.get(k1); int m = k1 - j ; //加多少行 if(tmpobj.get("A3").equals(tmpobj2.get("A3")) ){ tmpobj.put("A7",m); max_a3 = k1; //k1不需要渲染 }else{ break; } } } } for (int j = 0; j < jsonArrayData.size(); j++) { tmpobj = (JSONObject) jsonArrayData.get(j); if( j <= max_a5){ tmpobj.put("A10",-1); continue; } if(j+1 == jsonArrayData.size()){ if( j <= max_a5){ tmpobj.put("A10",-1); continue; } }else{ for (int k1 = j+1; k1 < jsonArrayData.size(); k1++) { System.out.println("j=="+j+" k1=="+k1); tmpobj2 = (JSONObject) jsonArrayData.get(k1); int m = k1 - j ; //加多少行 if(tmpobj.get("A9").equals(tmpobj2.get("A9")) ){ tmpobj.put("A10",m); max_a5 = k1; //k1不需要渲染 }else{ break; } } } } //检测结果有多少行 int start_number = 10; int result_count = jsonArrayData.size() ; //这里是读取excle里的值结果的数组长度 for (int i = 1 ; i <= result_count ; i++){ int rownum = start_number + i; HSSFRow row = this.sheet.createRow(rownum); row.setHeight((short) (30*20)); JSONObject jsonObject = (JSONObject) jsonArrayData.get(i-1); if((Integer) jsonObject.get("A5") > 0){ HSSFCell cell1 = row.createCell(0); cell1.setCellStyle(style); cell1.setCellValue((String) jsonObject.get("A1")); //设置字体大小 setFontSize(cell1,10,true,"黑体"); //合并单元格 CellRangeAddress region_cell1 = cellRangeAddress(rownum, (Integer) jsonObject.get("A5") +rownum, 0, 3); this.sheet.addMergedRegion(region_cell1); //设置边框 setBorder(region_cell1); }else if ((Integer) jsonObject.get("A5") == 0){ HSSFCell cell1 = row.createCell(0); cell1.setCellStyle(style); cell1.setCellValue((String) jsonObject.get("A1")); if( ((String) jsonObject.get("A1")).length()>15){ row.setHeight((short) (45*30)); } //设置字体大小 setFontSize(cell1,10,true,"黑体"); //合并单元格 CellRangeAddress region_cell1 = cellRangeAddress(rownum, rownum, 0, 3); this.sheet.addMergedRegion(region_cell1); //设置边框 setBorder(region_cell1); }
生成效果: 相同值的地方会自动合并