本文的思维导图如下所示：

什么是 Apache POI

很多时候，需要使用一个应用程序生成 Excel、world 等文件，甚至直接读取 Excel、World 的文档的数据。

这里都需要使用 Apache POI 的相关 API 来实现该操作。

Apache POI 包含用于处理 MS Office 的所有文档的类和方法，该 API 具有以下的列表。

POIFS 该组件用于读取不同的文件HSSF 用于读取 Excel 的文件HPSF 用于读取 Office 文件的 property setsHWPF 用于读取 doc 格式的文件XWPF 用于读取 docx 格式文件HSLF 用于读取 PPT 文件HDGF 用于读取 Visio 二进制文件Spring Boot 集成 Apache POI

添加相关依赖：

<dependency>    <groupId>org.apache.poi</groupId>    <artifactId>poi</artifactId>    <version>RELEASE</version></dependency><dependency>    <groupId>org.apache.poi</groupId>    <artifactId>poi-ooxml</artifactId>    <version>RELEASE</version></dependency>

这样就完成了 Apache POI 和 Spring Boot 的集成。

Apache 对 Word 的操作生成

添加依赖：

<dependency>    <groupId>org.apache.poi</groupId>    <artifactId>poi-ooxml</artifactId>    <version>3.15</version></dependency>

读取 txt 文件中的内容：

public String convertTextFileToString(String fileName) {    try (Stream<String> stream       = Files.lines(Paths.get(ClassLoader.getSystemResource(fileName).toURI()))) {        return stream.collect(Collectors.joining(" "));    } catch (IOException | URISyntaxException e) {        return null;    }}

格式化标题和子标题：

XWPFDocument document = new XWPFDocument();XWPFParagraph title = document.createParagraph();title.setAlignment(ParagraphAlignment.CENTER);//段落的内容需要包装在 XWPFRun 对象中,可以配置此对象以设置文本值及其关联的样式：XWPFRun titleRun = title.createRun();titleRun.setText("Build Your REST API with Spring");titleRun.setColor("009933");titleRun.setBold(true);titleRun.setFontFamily("Courier");titleRun.setFontSize(20);//创建一个 XWPFParagraph 实例，该实例包含子标题XWPFParagraph subTitle = document.createParagraph();subTitle.setAlignment(ParagraphAlignment.CENTER);XWPFRun subTitleRun = subTitle.createRun();subTitleRun.setText("from HTTP fundamentals to API Mastery");subTitleRun.setColor("00CC44");subTitleRun.setFontFamily("Courier");subTitleRun.setFontSize(16);subTitleRun.setTextPosition(20);subTitleRun.setUnderline(UnderlinePatterns.DOT_DOT_DASH);

Word 中插入图片

图片需要包装在 XWPFParagraph 实例中，需要将图片水平居中，并且放在字体下方。

XWPFParagraph image = document.createParagraph();image.setAlignment(ParagraphAlignment.CENTER);//设置此图像与其下方文本之间距离的方法XWPFRun imageRun = image.createRun();imageRun.setTextPosition(20);//获取图像，然后将其插入具有指定尺寸的 MS Word 文件中Path imagePath = Paths.get(ClassLoader.getSystemResource(logo).toURI());imageRun.addPicture(Files.newInputStream(imagePath),  XWPFDocument.PICTURE_TYPE_PNG, imagePath.getFileName().toString(),  Units.toEMU(50), Units.toEMU(50));

通过 txt 文件中内容创建段落并设置格式：

//通过 txt 中内容创建第一段WPFParagraph para1 = document.createParagraph();para1.setAlignment(ParagraphAlignment.BOTH);String string1 = convertTextFileToString("./paragraph1.txt");XWPFRun para1Run = para1.createRun();para1Run.setText(string1);//创建另外两个段落XWPFParagraph para2 = document.createParagraph();para2.setAlignment(ParagraphAlignment.RIGHT);String string2 = convertTextFileToString("./paragraph2.txt");XWPFRun para2Run = para2.createRun();para2Run.setText(string2);para2Run.setItalic(true);XWPFParagraph para3 = document.createParagraph();para3.setAlignment(ParagraphAlignment.LEFT);String string3 = convertTextFileToString("./paragraph3.txt");XWPFRun para3Run = para3.createRun();para3Run.setText(string3);

生成 Word：

//XWPFDocument document = new XWPFDocument();FileOutputStream out = new FileOutputStream(output);document.write(out);out.close();document.close();

添加水印：

//添加水印      public void createWaterMark(XWPFDocument doc){        XWPFHeaderFooterPolicy policy=doc.getHeaderFooterPolicy();        policy.createWatermark("中国华西监理信息管理平台");    }

读取 word

添加依赖：

<!--  -->    <dependency>      <groupId>org.apache.poi</groupId>      <artifactId>poi</artifactId>      <version>4.1.2</version>    </dependency>    <dependency>      <groupId>cn.hutool</groupId>      <artifactId>hutool-all</artifactId>      <version>5.5.7</version>    </dependency>    <!--  -->    <dependency>      <groupId>org.apache.poi</groupId>      <artifactId>poi-ooxml</artifactId>      <version>4.1.2</version>    </dependency>    <!--  -->    <dependency>      <groupId>org.apache.poi</groupId>      <artifactId>poi-ooxml-schemas</artifactId>      <version>4.1.2</version>    </dependency>    <dependency>      <groupId>org.apache.poi</groupId>      <artifactId>ooxml-schemas</artifactId>      <version>1.1</version>    </dependency>    <!--  -->    <dependency>      <groupId>org.apache.poi</groupId>      <artifactId>poi-scratchpad</artifactId>      <version>4.1.2</version>    </dependency>

加载文档：

 InputStream is = new FileInputStream("C:\\Users\\10386\\Desktop\\word-正确文档 2.docx");  XWPFDocument doc = new XWPFDocument(is);

页眉页脚：

 XWPFHeaderFooterPolicy headerFooterPolicy = doc.getHeaderFooterPolicy();    //获取页眉    String header = headerFooterPolicy.getDefaultHeader().getText();    System.out.println("***页眉 ***"+header);    //获取页脚    String footer = headerFooterPolicy.getDefaultFooter().getText();    System.out.println("***页脚 ***"+header);

页眉边距：

 CTDocument1 ctdoc =  doc.getDocument();    int top = ctdoc.getBody().getSectPr().getPgMar().getTop().intValue();    int bottom = ctdoc.getBody().getSectPr().getPgMar().getBottom().intValue();    int left = ctdoc.getBody().getSectPr().getPgMar().getLeft().intValue();    int right = ctdoc.getBody().getSectPr().getPgMar().getRight().intValue();

获取标题：

List<XWPFParagraph> paras = doc.getParagraphs(); //将得到包含段落列表    //获取标题    List<Map<String, String>> list = getParagraph(paras.get(0));    System.out.println("标题信息==="+list);

获取表格：

        int row_count =0;        XWPFTable table = (XWPFTable) element;        List<XWPFTableRow> xwpfTableRows = table.getRows();        row_count = xwpfTableRows.size();        ArrayList cell_count=new ArrayList();        int row_index = 1;        for (XWPFTableRow xwpfTableRow : xwpfTableRows) {          List<XWPFTableCell> xwpfTableCells = xwpfTableRow.getTableCells();          cell_count.add(xwpfTableCells.size());          System.out.println("第"+row_index+"行");          int cell_index =1;          for (XWPFTableCell xwpfTableCell : xwpfTableCells) {            //单元格是否被合并，合并了几个            CTDecimalNumber  cellspan = xwpfTableCell.getCTTc().getTcPr().getGridSpan();            boolean gridspan = cellspan != null;            String gridspan_num = cellspan != null?cellspan.getVal().toString():"0";            List<XWPFParagraph> xwpfParagraphs = xwpfTableCell.getParagraphs();            XWPFParagraph paragraph = xwpfParagraphs.get(0);            System.out.println("第" +cell_index+"个单元格，合并标志："+gridspan+",合并个数:"+gridspan_num            +"文字："+getParagraph(paragraph));            cell_index++;          }          row_index++;        }        System.out.println("表格为：row_count==="+row_count+"行"+Collections.max(cell_count)+"列");

获取图片：

        int row_count =0;        XWPFTable table = (XWPFTable) element;        List<XWPFTableRow> xwpfTableRows = table.getRows();        row_count = xwpfTableRows.size();        ArrayList cell_count=new ArrayList();        int row_index = 1;        for (XWPFTableRow xwpfTableRow : xwpfTableRows) {          List<XWPFTableCell> xwpfTableCells = xwpfTableRow.getTableCells();          cell_count.add(xwpfTableCells.size());          System.out.println("第"+row_index+"行");          int cell_index =1;          for (XWPFTableCell xwpfTableCell : xwpfTableCells) {            //单元格是否被合并，合并了几个            CTDecimalNumber  cellspan = xwpfTableCell.getCTTc().getTcPr().getGridSpan();            boolean gridspan = cellspan != null;            String gridspan_num = cellspan != null?cellspan.getVal().toString():"0";            List<XWPFParagraph> xwpfParagraphs = xwpfTableCell.getParagraphs();            XWPFParagraph paragraph = xwpfParagraphs.get(0);            System.out.println("第" +cell_index+"个单元格，合并标志："+gridspan+",合并个数:"+gridspan_num            +"文字："+getParagraph(paragraph));            cell_index++;          }          row_index++;        }        System.out.println("表格为：row_count==="+row_count+"行"+Collections.max(cell_count)+"列");

缩进方式计算：

//先判断缩进方式再进行数值计算        double ind = -1, ind_left = -1, ind_right = -1, ind_hang = -1;        String ind_type = "";        if (para.getIndentationHanging() != -1) {//悬挂缩进            ind_type = "hang";            if (para.getIndentationHanging() % 567 == 0) {//悬挂单位为厘米                ind = para.getIndentationHanging() / 567.0;                ind_left = (para.getIndentationLeft() - 567.0 * ind) / 210;            } else {//悬挂单位为字符                ind = para.getIndentationHanging() / 240;                ind_left = (para.getIndentationLeft() - para.getIndentationHanging()) / 210;            }            ind_right = para.getIndentationRight() / 210.0;        } else {//首行缩进或者无            ind_type = "first";            if (para.getFirstLineIndent() == -1) {                ind_type = "none";                ind = 0;            } else {                ind = para.getFirstLineIndent() % 567.0 == 0 ? para.getFirstLineIndent() / 567.0 : para.getFirstLineIndent() / 240.0;            }            ind_left = para.getIndentationLeft() / 210;            ind_right = para.getIndentationRight() / 210.0;        }

段落格式：

List<XWPFParagraph> paras = doc.getParagraphs(); //将得到包含段落列表 XWPFParagraph  para =  paras.get(1); List<XWPFRun> runsLists = para.getRuns();        List<Map<String, String>> list = new ArrayList<>();        Map<String, String> titile = new HashMap<>();        titile.put("Text", para.getText());//本段全部内容        titile.put("Alignment", para.getAlignment().toString());        titile.put("SpacingBetween", para.getSpacingBetween() + "");//行距        titile.put("SpacingBeforeLines", para.getSpacingBeforeLines() + "");//段前        titile.put("SpacingAfterLines", para.getSpacingAfterLines() + "");//段后        titile.put("NumLevelText", para.getNumLevelText() + "");//自动编号格式

文字属性：

List<XWPFRun> runsLists = para.getRuns()        for (XWPFRun run : runsLists        ) {            List<XWPFPicture> pictures = run.getEmbeddedPictures();            if (pictures.size() > 0) {                XWPFPicture picture = pictures.get(0);                XWPFPictureData pictureData = picture.getPictureData();                System.out.println(Base64.encode(pictureData.getData()));            }            Map<String, String> titile_map = new HashMap<>();            titile_map.put("content", run.getText(0));            String Bold = Boolean.toString(run.isBold());//加粗            titile_map.put("Bold", Bold);            String color = run.getColor();//字体颜色            titile_map.put("Color", color);            String FontFamily = run.getFontFamily(XWPFRun.FontCharRange.hAnsi);//字体            titile_map.put("FontFamily", FontFamily);            String FontName = run.getFontName();//字体            titile_map.put("FontName", FontName);            String FontSize = run.getFontSize() + "";//字体大小            titile_map.put("FontSize", FontSize);            String Underline = run.getUnderline().name();//字下加线            titile_map.put("Underline", Underline);            String UnderlineColor = run.getUnderlineColor();//字下加线颜色            titile_map.put("UnderlineColor", UnderlineColor);            String Italic = Boolean.toString(run.isItalic());//字体倾斜            titile_map.put("Italic", Italic);            list.add(titile_map);

Apache POI 读取 Excel 内容引入依赖

<!--利用 poi 读取 excel--><dependency>    <groupId>org.apache.poi</groupId>    <artifactId>poi</artifactId>    <version>3.16</version></dependency><dependency>    <groupId>org.apache.poi</groupId>    <artifactId>poi-ooxml</artifactId>    <version>3.16</version></dependency>

读取的文件读取 Excel

private Workbook getReadWorkBookType(String filePath) throws BusinessException {        //xls-2003, xlsx-2007        FileInputStream is = null;        try {            is = new FileInputStream(filePath);            if (filePath.toLowerCase().endsWith("xlsx")) {                return new XSSFWorkbook(is);            } else if (filePath.toLowerCase().endsWith("xls")) {                return new HSSFWorkbook(is);            } else {              //  抛出自定义的业务异常                throw OnlinePayErrorCode.EXCEL_ANALYZE_ERROR.convertToException("excel 格式文件错误");            }        } catch (IOException e) {          //  抛出自定义的业务异常            throw OnlinePayErrorCode.EXCEL_ANALYZE_ERROR.convertToException(e.getMessage());        } finally {            IOUtils.closeQuietly(is);        }  }

解析 Excel

public List<String> readExcel(String sourceFilePath) throws BusinessException {        Workbook workbook = null;        try {            workbook = getReadWorkBookType(sourceFilePath);            List<String> contents = Lists.newArrayList();            //获取第一个 sheet            Sheet sheet = workbook.getSheetAt(0);            //第 0 行是表名，忽略，从第二行开始读取            for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {                Row row = sheet.getRow(rowNum);                Cell cell = row.getCell(0);                contents.add(getCellStringVal(cell).trim());            }            return contents;        } finally {            IOUtils.closeQuietly(workbook);        }    }

写入 Excel 的格式初始化 Workbook

private Workbook getWriteWorkBoolType(String filePath) throws BusinessException{        if (filePath.toLowerCase().endsWith("xlsx")) {            return new XSSFWorkbook();        } else if (filePath.toLowerCase().endsWith("xls")) {            return new HSSFWorkbook();        } else {            //抛出自定的业务异常            throw OnlinePayErrorCode.EXCEL_ANALYZE_ERROR.convertToException("excel 格式文件错误");        }    }

数据写入

public void writeExcel(String targetFilePath, List<? extends GetPoiAccountSettingsFromExcelVO> contents) throws BusinessException {        Workbook workbook = null;        FileOutputStream fos = null;        workbook = getWriteWorkBoolType(targetFilePath);        //创建 sheet        Sheet sheet = workbook.createSheet("门店入件状态");        //在 sheet 第一行写出表单的各个字段名        Row titleRow = sheet.createRow(0);        titleRow.createCell(0).setCellValue("租户 id");        titleRow.createCell(1).setCellValue("门店 id");        titleRow.createCell(2).setCellValue("入件状态");        //每行的单元格一次写入        Integer rowIndex = contents.size();        for (int i = 0; i < rowIndex; i++) {            //第 1 行作为表格列名，所以从第 2 行开始读取            Row row = sheet.createRow(i + 1);            Cell cellTenantId = row.createCell(0);            cellTenantId.setCellValue(contents.get(i).getTenantId());            Cell cellPoiId = row.createCell(1);            cellPoiId.setCellValue(contents.get(i).getMerchantId());            Cell cellStatus = row.createCell(2);            cellStatus.setCellValue(contents.get(i).getMerchantStatus());        }       //写入到文件流中        try {            fos = new FileOutputStream(targetFilePath);            workbook.write(fos);        } catch (IOException e) {            throw OnlinePayErrorCode.EXCEL_ANALYZE_ERROR.convertToException(e.getMessage());        } finally {            IOUtils.closeQuietly(workbook);        }    }

Apache POI 生成 Excel添加依赖

<dependency>    <groupId>org.apache.poi</groupId>    <artifactId>poi</artifactId>    <version>4.0.1</version></dependency><dependency>    <groupId>org.apache.poi</groupId>    <artifactId>poi-ooxml</artifactId>    <version>4.0.1</version></dependency>

示例代码

package com.bytrees;import java.io.File;import java.io.FileOutputStream;import java.lang.management.ManagementFactory;import java.lang.management.MemoryUsage;import java.util.Date;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.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;/** * Hello world! * */public class App  {    public static void main( String[] args ) {        String[] data = {"73982", "1", "NH", "1", "2018122510", "2", "0", "0", "0", "12233", "0", "楼层组件优化", "0"};        Date startTime = new Date();        MemoryUsage startMemory = ManagementFactory.getMemoryMXBean().getHeapMemoryUsage();        // 创建工作薄        XSSFWorkbook workbook = new XSSFWorkbook();        // 创建工作表        XSSFSheet sheet = workbook.createSheet("sheet1");        //设置数据        for (int row = 0; row < 50000; row++) {            XSSFRow sheetRow = sheet.createRow(row);            for (int column = 0; column < data.length; column++) {                sheetRow.createCell(column).setCellValue(data[column]);            }        }        //写入文件        try {            workbook.write(new FileOutputStream(new File("excel.xlsx")));            workbook.close();        } catch (Exception ex) {            System.out.println(ex.getMessage());        }        Date endTime = new Date();        MemoryUsage endMemory = ManagementFactory.getMemoryMXBean().getHeapMemoryUsage();        System.out.println("Cost time(ms): " + (endTime.getTime() - startTime.getTime()));        System.out.println("Cost memory(): " + (endMemory.getUsed() - startMemory.getUsed()));        System.out.println( "Hello World!" );    }}