POI相关技术

程序员李某某大约 24 分钟

POI相关技术

概述

Excel

Excel2003 是一个特有的二进制格式，其核心结构是复合文档类型的结构，存储数据量较小；

Excel2007 的核心结构是 XML 类型的结构，采用的是基于 XML 的压缩方式，使其占用的空间更小，操作效率更高

POI说明

POI是apache的项目,可对微软的Word,Excel,PPT进行操作,包括office2003和2007,Excle2003和2007。 poi现在一直有更新。所以现在主流使用POI。

Apache POI是Apache软件基金会的开源项目，由Java编写的免费开源的跨平台的 Java API，Apache POI提供API给Java语言操作Microsoft Office的功能。

API对象介绍工作簿：

WorkBook （HSSFWordBook ： 2003版本，XSSFWorkBook ： 2007级以上）
工作表： Sheet （HSSFSheet ： 2003版本，XSSFSheet ： 2007级以上）
行： Row （HSSFRow ： 2003版本，XSSFRow ： 2007级以上）
单元格： Cell （HSSFCell ： 2003版本，XSSFCell ： 2007级以上）

**在POI包中有如下几个主要对象和excel的几个对象对应：**开发时，只是类名不一样，方法基本一致

对应excel名称	低版本中的类名	高版本中的类名
工作簿	HSSFWorkbook	XSSFWorkbook
工作表	HSSFSheet	XSSFSheet
行	HSSFRow	XSSFRow
单元格	HSSFCell	XSSFCell
单元格样式	HSSFCellStyle	XSSFCellStyle

旧版本示例

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
public class POIDemo01 {
    public static void main(String[] args) throws Exception{
        Workbook workbook = new HSSFWorkbook(); //创建了一个全新（里面什么都没有）的工作薄
        Sheet sheet = workbook.createSheet("demo测试");  //创建了一个全新（里面什么都没有）的工作表
        Row row = sheet.createRow(0);  //创建了第一行（空的）
        Cell cell = row.createCell(0);//创建的是第一行的第一个单元格
        cell.setCellValue("这是我第一次玩POI");
//        把工作薄输出到本地磁盘
        workbook.write(new FileOutputStream("d://test.xls"));
    }
}

高版本示例

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
 
public class POIDemo02 {
    public static void main(String[] args) throws Exception{
        Workbook workbook = new XSSFWorkbook(); //创建了一个全新（里面什么都没有）的工作薄
        Sheet sheet = workbook.createSheet("demo测试");  //创建了一个全新（里面什么都没有）的工作表
        Row row = sheet.createRow(0);  //创建了第一行（空的）
        Cell cell = row.createCell(0);//创建的是第一行的第一个单元格
        cell.setCellValue("这是我第一次玩POI");
//        把工作薄输出到本地磁盘
        workbook.write(new FileOutputStream("d://test.xlsx"));
    }
}

POI 基本操作

POI读取Excel

public class POIDemo03 {
    public static void main(String[] args) throws Exception{
        // 远程传入的情况
        // Workbook workbook = new XSSFWorkbook(file.getInputStream()); //根据上传的MultipartFile输入流创建workbook
        // 本地文件的情况
        Workbook workbook = new XSSFWorkbook(new FileInputStream("d://test.xlsx"));

        Sheet sheet = workbook.getSheetAt(0); //获取工作薄中的第一个工作表
        int lastRowIndex = sheet.getLastRowNum(); //获取这个sheet中最后一行数据，为了循环遍历
        // 以下三个为了节省栈内存，所以提到循环的外面
        User user = null;
        Row row = null;
        Cell cell = null;

        // 开始循环每行，获取每行的单元格中的值，放入到user属性中
        for (int i = 1; i <= lastRowIndex; i++) {
            row = sheet.getRow(i);
            user = new User();
            // 因为第一个列单元格中是字符串，可以直接使用getStringCellValue方法
            String userName = row.getCell(0).getStringCellValue(); //用户名
            user.setUserName(userName);
            String phone = null; //手机号
            try {
                phone = row.getCell(1).getStringCellValue();
            } catch (IllegalStateException e) {
                phone = row.getCell(1).getNumericCellValue()+"";
            }
            user.setPhone(phone);
            String province = row.getCell(2).getStringCellValue(); //省份
            user.setProvince(province);
            String city = row.getCell(3).getStringCellValue(); //城市
            user.setCity(city);
            // 因为在填写excel中的数据时就可以约定这个列只能填写数值，所以可以直接用getNumericCellValue方法
            Integer salary = ((Double)row.getCell(4).getNumericCellValue()).intValue(); //工资
            user.setSalary(salary);
            String hireDateStr = row.getCell(5).getStringCellValue(); //入职日期
            Date hireDate = simpleDateFormat.parse(hireDateStr);
            user.setHireDate(hireDate);
            String birthdayStr = row.getCell(6).getStringCellValue(); //出生日期
            Date birthday = simpleDateFormat.parse(birthdayStr);
            user.setBirthday(birthday);

            String address = row.getCell(7).getStringCellValue(); //现住地址
            user.setAddress(address);
            System.out.println(user);
        }
    }
}

POI写入Excel

public class POIDemo04 {
    public static void main(String[] args) throws Exception{
        // 创建一个空的工作薄
        Workbook workbook = new XSSFWorkbook();
        // 在工作薄中创建一个工作表
        Sheet sheet = workbook.createSheet("测试");
        // 设置列宽
        sheet.setColumnWidth(0,5*256);
        sheet.setColumnWidth(1,8*256);
        sheet.setColumnWidth(2,15*256);
        sheet.setColumnWidth(3,15*256);
        sheet.setColumnWidth(4,30*256);
        // 处理标题
        String[] titles = new String[]{"编号","姓名","手机号","入职日期","现住址"};

        // 创建标题行
        Row titleRow = sheet.createRow(0);
        Cell cell = null;
        for (int i = 0; i < titles.length; i++) {
            cell = titleRow.createCell(i);
            cell.setCellValue(titles[i]);
        }
        // 处理内容
        List<User> userList = this.findAll();
        int rowIndex = 1;
        Row row = null;
        for (User user : userList) {
            row = sheet.createRow(rowIndex);
            cell = row.createCell(0);
            cell.setCellValue(user.getId());

            cell = row.createCell(1);
            cell.setCellValue(user.getUserName());

            cell = row.createCell(2);
            cell.setCellValue(user.getPhone());

            cell = row.createCell(3);
            cell.setCellValue(simpleDateFormat.format(user.getHireDate()));

            cell = row.createCell(4);
            cell.setCellValue(user.getAddress());

            rowIndex++;
        }
        // 导出的文件名称
        String filename="员工数据.xlsx";
        // 设置文件的打开方式和mime类型
        ServletOutputStream outputStream = response.getOutputStream();
        response.setHeader( "Content-Disposition", "attachment;filename="  + new String(filename.getBytes(),"ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        workbook.write(outputStream);

    }
}

样式

框线

/**
 * 设置框线
 */
HSSFCellStyle contentStyle = book.createCellStyle();
contentStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);//底线
contentStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//顶部线
contentStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左侧线
contentStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右侧线

合并单元格

//合并单元格 起始行, 结束行, 起始列, 结束列  
sheet.addMergedRegion(new CellRangeAddress(0,0,0,4));

行高

/**
设置行高
*/
sheet.getRow(1).setHeight((short)500);

对齐方式和字体

//*设置对齐方式和字体***/
//内容部分的样式
style_content.setAlignment(HSSFCellStyle.ALIGN_CENTER);//设置水平居中
style_content.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//设置垂直居中

HSSFFont font = book.createFont();//创建字体
font.setFontName("宋体");//设置字体名称
font.setFontHeightInPoints((short)11);//设置字体大小
style_content.setFont(font);//对样式设置字体
        
//标题样式
HSSFCellStyle style_title = book.createCellStyle();//创建标题样式
style_title.setAlignment(HSSFCellStyle.ALIGN_CENTER);//设置水平居中
style_title.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//设置垂直居中
HSSFFont titleFont = book.createFont();//设置标题字体
titleFont.setFontName("黑体");
titleFont.setBold(true);//加粗
titleFont.setFontHeightInPoints((short)18);//字体大小    
style_title.setFont(titleFont);//将标题字体设置到标题样式
sheet.getRow(0).getCell(0).setCellStyle(style_title);//单元格设置标题样式

模版导出

Excel列表

public class POIDemo05 {
    public void downLoadUserInfoWithTempalte(Long id, HttpServletRequest request, HttpServletResponse response) throws Exception{
        // 获取模板的路径
        File rootPath = new File(ResourceUtils.getURL("classpath:").getPath()); //SpringBoot项目获取根目录的方式
        File templatePath = new File(rootPath.getAbsolutePath(),"/excel_template/userList.xlsx");
        // 读取模板文件产生workbook对象,这个workbook是一个有内容的工作薄
        Workbook workbook  = new XSSFWorkbook(templatePath);
        // 读取工作薄的第一个工作表，向工作表中放数据
        Sheet sheet = workbook.getSheetAt(0);
        // 获取第二个的sheet中那个单元格中的单元格样式
        CellStyle cellStyle = workbook.getSheetAt(1).getRow(0).getCell(0).getCellStyle();
        // 处理内容
        List<User> userList = this.findAll();
        int rowIndex = 2;
        Row row = null;
        Cell cell = null;
        for (User user : userList) {
            row = sheet.createRow(rowIndex);
            row.setHeightInPoints(15); //设置行高

            cell = row.createCell(0);
            cell.setCellValue(user.getId());
            cell.setCellStyle(cellStyle); //设置单元格样式

            cell = row.createCell(1);
            cell.setCellValue(user.getUserName());
            cell.setCellStyle(cellStyle);

            cell = row.createCell(2);
            cell.setCellValue(user.getPhone());
            cell.setCellStyle(cellStyle);

            cell = row.createCell(3);
            cell.setCellValue(simpleDateFormat.format(user.getHireDate()));
            cell.setCellStyle(cellStyle);

            cell = row.createCell(4);
            cell.setCellValue(user.getAddress());
            cell.setCellStyle(cellStyle);

            rowIndex++;
        }
        // 导出的文件名称
        String filename="用户列表数据.xlsx";
        // 设置文件的打开方式和mime类型
        ServletOutputStream outputStream = response.getOutputStream();
        response.setHeader( "Content-Disposition", "attachment;filename="  + new String(filename.getBytes(),"ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        workbook.write(outputStream);
    }
}

图片

// 先创建一个字节输出流
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
// BufferedImage是一个带缓冲区图像类,主要作用是将一幅图片加载到内存中
BufferedImage bufferImg = ImageIO.read(new File(rootPath + user.getPhoto()));
// 把读取到图像放入到输出流中
ImageIO.write(bufferImg, "jpg", byteArrayOut);
// 创建一个绘图控制类，负责画图
Drawing patriarch = sheet.createDrawingPatriarch();
// 指定把图片放到哪个位置
ClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 2, 1, 4, 5);
// 开始把图片写入到sheet指定的位置
patriarch.createPicture(anchor, workbook.addPicture(
    byteArrayOut.toByteArray(), Workbook.PICTURE_TYPE_JPEG));

关于XSSFClientAnchor的8个参数说明：

dx1 - the x coordinate within the first cell.//定义了图片在第一个cell内的偏移x坐标，既左上角所在cell的偏移x坐标，一般可设0
dy1 - the y coordinate within the first cell.//定义了图片在第一个cell的偏移y坐标，既左上角所在cell的偏移y坐标，一般可设0
dx2 - the x coordinate within the second cell.//定义了图片在第二个cell的偏移x坐标，既右下角所在cell的偏移x坐标，一般可设0
dy2 - the y coordinate within the second cell.//定义了图片在第二个cell的偏移y坐标，既右下角所在cell的偏移y坐标，一般可设0

col1 - the column (0 based) of the first cell.//第一个cell所在列，既图片左上角所在列
row1 - the row (0 based) of the first cell.//图片左上角所在行
col2 - the column (0 based) of the second cell.//图片右下角所在列
row2 - the row (0 based) of the second cell.//图片右下角所在行

公式

关于POI支持公式详见官网： https://poi.apache.org/components/spreadsheet/eval-devguide.htmlopen in new windowps:其实在正常开发时应该在模板中直接设置好公式，这样打开直接导出的excel文档时公式会直接运行出我们想要的结果

自定义模版引擎

public class ExcelExportEngine {

    private static SimpleDateFormat  sdf = new SimpleDateFormat("yyyy-MM-dd");

    public  static Workbook writeToExcel(Object object, Workbook workbook,String photoPath) throws Exception{
        //先把bean转成map
        Map<String, Object> map = EntityUtils.entityToMap(object);
        //循环遍历每一对数据,把日期型的转成字符串，方便导出
        for (String key : map.keySet()) {
            Object vlaue = map.get(key);
            if(vlaue instanceof Date){
                System.out.println(sdf.format(vlaue));
                map.put(key,sdf.format(vlaue));
            }
        }
        //获取第一个sheet，整体的思路是循环100个行的100个单元格
        Sheet sheet = workbook.getSheetAt(0);
        Cell cell =null;
        Row row = null;
        for (int i = 0; i < 100; i++) {
            row = sheet.getRow(i); //获取到空行为止
            if(row==null){
                break;
            }else{
                for (int j = 0; j < 100; j++) {
                    cell = row.getCell(j);//获取到空单元格不处理
                    if(cell!=null){
                        writeCell(cell,map); //开始向单元格中写内容
                    }
                }
            }
        }

        if(StringUtils.isNotBlank(photoPath)){
            File rootPath = new File(ResourceUtils.getURL("classpath:").getPath()); //SpringBoot项目获取根目录的方式
            ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
//        BufferedImage是一个带缓冲区图像类,主要作用是将一幅图片加载到内存中
            BufferedImage bufferImg = ImageIO
                    .read(new File(rootPath + photoPath));
            ImageIO.write(bufferImg, "jpg", byteArrayOut);
            Drawing patriarch = sheet.createDrawingPatriarch();

            Sheet sheet2 = workbook.getSheetAt(1);
            row = sheet2.getRow(0);
            int col1 = ((Double) row.getCell(0).getNumericCellValue()).intValue();
            int row1 = ((Double) row.getCell(1).getNumericCellValue()).intValue();
            int col2 = ((Double) row.getCell(2).getNumericCellValue()).intValue();
            int row2 = ((Double) row.getCell(3).getNumericCellValue()).intValue();
//            锚点，固定点
            ClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0,  col1, row1, col2, row2);
            patriarch.createPicture(anchor, workbook.addPicture(byteArrayOut.toByteArray(), Workbook.PICTURE_TYPE_JPEG));
            workbook.removeSheetAt(1);
        }

        return workbook;
    }

    private static void writeCell(Cell cell, Map<String, Object> map) {
        CellType cellType = cell.getCellType();
        switch (cellType){
            case FORMULA:{  //如果是公式就直接放行了
                break;
            }default:{
                String cellValue = cell.getStringCellValue();
                //就是判断一下获取到单元格中的值是否和map中的key保持一致
                if(StringUtils.isNotBlank(cellValue)){
                    for (String key : map.keySet()) {
                        if(key.equals(cellValue)){
                            cell.setCellValue(map.get(key).toString());
                        }
                    }
                }
            }
        }
    }
}

POI导出Word

poi对低版本的doc本身支持的就不好所以我们直接说高版本的docx版本的api。

1、poi操作word正文

XWPFDocument代表一个docx文档，其可以用来读docx文档，也可以用来写docx文档

一个文档包含多个段落，一个段落包含多个Runs文本，一个Runs包含多个Run，Run是文档的最小单元

获取所有段落：List<XWPFParagraph> paragraphs = word.getParagraphs();

获取一个段落中的所有片段Runs：List<XWPFRun> xwpfRuns = xwpfParagraph.getRuns();

获取一个Runs中的一个Run：XWPFRun run = xwpfRuns.get(index);

2、poi操作word中的表格

一个文档包含多个表格，一个表格包含多行，一行包含多列单元格

获取所有表格：List<XWPFTable> xwpfTables = doc.getTables();

获取一个表格中的所有行：List<XWPFTableRow> xwpfTableRows = xwpfTable.getRows();

获取一行中的所有列：List<XWPFTableCell> xwpfTableCells = xwpfTableRow.getTableCells();

获取一格里的内容：List<XWPFParagraph> paragraphs = xwpfTableCell.getParagraphs();

之后和正文段落一样

按模版导出

/**
 * 下载用户合同数据
 * @param id
 */
public void downloadContract(Long id,HttpServletResponse response) throws Exception {
    // 1、读取到模板
    File rootFile = new File(ResourceUtils.getURL("classpath:").getPath()); //获取项目的根目录
    File templateFile = new File(rootFile, "/word_template/contract_template.docx");
    XWPFDocument word = new XWPFDocument(new FileInputStream(templateFile));
    // 2、查询当前用户User--->map
    User user = this.findById(id);
    Map<String,String> params = new HashMap<>();
    params.put("userName",user.getUserName());
    params.put("hireDate",simpleDateFormat.format(user.getHireDate()));
    params.put("address",user.getAddress());
    // 3、替换数据
    // 处理正文开始
    List<XWPFParagraph> paragraphs = word.getParagraphs();
    for (XWPFParagraph paragraph : paragraphs) {
        List<XWPFRun> runs = paragraph.getRuns();
        for (XWPFRun run : runs) {
            String text = run.getText(0);
            for (String key : params.keySet()) {
                if(text.contains(key)){
                    run.setText(text.replaceAll(key,params.get(key)),0);
                }
            }
        }
    }
    // 处理正文结束

    // 处理表格开始     名称 价值 是否需要归还 照片
    List<Resource> resourceList = user.getResourceList(); //表格中需要的数据
    XWPFTable xwpfTable = word.getTables().get(0);

    XWPFTableRow row = xwpfTable.getRow(0);
    int rowIndex = 1;
    for (Resource resource : resourceList) {
        // 添加行
        // xwpfTable.addRow(row);
        copyRow(xwpfTable,row,rowIndex);
        XWPFTableRow row1 = xwpfTable.getRow(rowIndex);
        row1.getCell(0).setText(resource.getName());
        row1.getCell(1).setText(resource.getPrice().toString());
        row1.getCell(2).setText(resource.getNeedReturn()?"需求":"不需要");

        File imageFile = new File(rootFile,"/static"+resource.getPhoto());
        setCellImage(row1.getCell(3),imageFile);
        rowIndex++;
    }
    // 处理表格开始结束
    // 4、导出word
    String filename = "员工(" + user.getUserName() + ")合同.docx";
    response.setHeader("content-disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    word.write(response.getOutputStream());
}


// 向单元格中写入图片
private void setCellImage(XWPFTableCell cell, File imageFile) {

    XWPFRun run = cell.getParagraphs().get(0).createRun();
    try(FileInputStream inputStream = new FileInputStream(imageFile)) {
        // InputStream pictureData, int pictureType, String filename, int width, int height
        run.addPicture(inputStream,XWPFDocument.PICTURE_TYPE_JPEG,imageFile.getName(), Units.toEMU(100),Units.toEMU(50));
    } catch (Exception e) {
        e.printStackTrace();
    }
}

// 用于深克隆行
private void copyRow(XWPFTable xwpfTable, XWPFTableRow sourceRow, int rowIndex) {
    XWPFTableRow targetRow = xwpfTable.insertNewTableRow(rowIndex);
    targetRow.getCtRow().setTrPr(sourceRow.getCtRow().getTrPr());
    // 获取源行的单元格
    List<XWPFTableCell> cells = sourceRow.getTableCells();
    if(CollectionUtils.isEmpty(cells)){
        return;
    }
    XWPFTableCell targetCell = null;
    for (XWPFTableCell cell : cells) {
        targetCell = targetRow.addNewTableCell();
        // 附上单元格的样式
        // 单元格的属性
        targetCell.getCTTc().setTcPr(cell.getCTTc().getTcPr());
 targetCell.getParagraphs().get(0).getCTP().setPPr(cell.getParagraphs().get(0).getCTP().getPPr());
    }
}

EasyPOI

依赖

<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-base</artifactId>
    <version>4.1.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-web</artifactId>
    <version>4.1.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-annotation</artifactId>
    <version>4.1.0</version>
</dependency>

或SpringBoot

<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-spring-boot-starter</artifactId>
    <version>4.1.0</version>
</dependency>

注解导出Excel

@Data
public class User {
    @Id
    @Excel(name = "编号", orderNum = "0", width = 5)
    private Long id;         //主键
    @Excel(name = "员工名", orderNum = "1", width = 15)
    private String userName; //员工名
    @Excel(name = "手机号", orderNum = "2", width = 15)
    private String phone;    //手机号
    @Excel(name = "省份名", orderNum = "3", width = 15)
    private String province; //省份名
    @Excel(name = "城市名", orderNum = "4", width = 15)
    private String city;     //城市名
    @Excel(name = "工资", orderNum = "5", width = 10)
    private Integer salary;   // 工资
    @JsonFormat(pattern="yyyy-MM-dd")
    @Excel(name = "入职日期",  format = "yyyy-MM-dd",orderNum = "6", width = 15)
    private Date hireDate; // 入职日期
    private String deptId;   //部门id
    @Excel(name = "出生日期",  format = "yyyy-MM-dd",orderNum = "7", width = 15)
    private Date birthday; //出生日期
    @Excel(name = "照片", orderNum = "10",width = 15,type = 2)
    private String photo;    //一寸照片
    @Excel(name = "现在居住地址", orderNum = "9", width = 30)
    private String address;  //现在居住地址

    private List<Resource> resourceList; //办公用品

}

public void downLoadXlsxWithEayPoi(HttpServletRequest request, HttpServletResponse response) throws Exception {
    //        查询用户数据
    List<User> userList = userMapper.selectAll();
    //指定导出的格式是高版本的格式
    ExportParams exportParams = new ExportParams("员工信息", "数据",ExcelType.XSSF);
    //        直接使用EasyPOI提供的方法
    Workbook workbook = ExcelExportUtil.exportExcel(exportParams, User.class, userList);
    String filename="员工信息.xlsx";
    //            设置文件的打开方式和mime类型
    ServletOutputStream outputStream = response.getOutputStream();
    response.setHeader( "Content-Disposition", "attachment;filename="  + new String(filename.getBytes(),"ISO8859-1"));
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    workbook.write(outputStream);
}

注解导入Excel

导入需要对注解设置更多详细的属性

还需要对参数类ImportParams常用设置说明

读取指定的sheet 比如要读取上传得第二个sheet 那么需要把startSheetIndex = 1 就可以了
读取几个sheet 比如读取前2个sheet,那么 sheetNum=2 就可以了
读取第二个到第五个sheet 设置 startSheetIndex = 1 然后sheetNum = 4
读取全部的sheet sheetNum 设置大点就可以了
保存Excel 设置 needVerfiy = true,默认保存的路径为upload/excelUpload/Test/yyyyMMddHHmss 保存名称上传时间五位随机数如果自定义路径修改下saveUrl 就可以了,同时saveUrl也是图片上传时候的保存的路径
判断一个Excel是不是合法的Excel importFields 设置下值,就是表示表头必须至少包含的字段,如果缺一个就是不合法的excel,不导入
图片的导入

有图片的导出就有图片的导入,导入的配置和导出是一样的,但是需要设置保存路径 1.设置保存路径saveUrl 默认为"upload/excelUpload" 可以手动修改 ImportParams 修改下就可以了

@Data
public class User {
    @Id
    @Excel(name = "编号", orderNum = "0", width = 5)
    private Long id;         //主键
    @Excel(name = "员工名", orderNum = "1", width = 15,isImportField="true")
    private String userName; //员工名
    @Excel(name = "手机号", orderNum = "2", width = 15,isImportField="true")
    private String phone;    //手机号
    @Excel(name = "省份名", orderNum = "3", width = 15,isImportField="true")
    private String province; //省份名
    @Excel(name = "城市名", orderNum = "4", width = 15,isImportField="true")
    private String city;     //城市名
    @Excel(name = "工资", orderNum = "5", width = 10, type=10, isImportField="true") //type=10表示会导出数字
    private Integer salary;   // 工资
    @JsonFormat(pattern="yyyy-MM-dd")
    @Excel(name = "入职日期",  format = "yyyy-MM-dd",orderNum = "6", width = 15,isImportField="true")
    private Date hireDate; // 入职日期
    private String deptId;   //部门id
    @Excel(name = "出生日期",  format = "yyyy-MM-dd",orderNum = "7", width = 15,isImportField="true")
    private Date birthday; //出生日期
    @Excel(name = "照片", orderNum = "10",width = 15,type = 2,isImportField="true",savePath = "D:\\java_report\\workspace\\user_management\\src\\main\\resources\\static\\user_photos\\")
    private String photo;    //一寸照片
    @Excel(name = "现在居住地址", orderNum = "9", width = 30,isImportField="true")
    private String address;  //现在居住地址

    private List<Resource> resourceList; //办公用品

}

public void uploadExcleWithEasyPOI(MultipartFile file) throws Exception {

    ImportParams importParams = new ImportParams();
    importParams.setTitleRows(1); //有多少行的标题
    importParams.setHeadRows(1);//有多少行的头
    List<User> userList = ExcelImportUtil.importExcel(file.getInputStream(),User.class,importParams);

    System.out.println(userList);
    for (User user : userList) {
        user.setId(null);
        userMapper.insertSelective(user);
    }
}

按模板导出

采用的写法是{{}}代表表达式，然后根据表达式里面的数据取值

public void downLoadUserInfoWithEastPOI(Long id, HttpServletRequest request, HttpServletResponse response) throws Exception  {
    // 获取模板的路径
    File rootPath = new File(ResourceUtils.getURL("classpath:").getPath()); //SpringBoot项目获取根目录的方式
    File templatePath = new File(rootPath.getAbsolutePath(),"/excel_template/userInfo3.xlsx");
    // 读取模板文件
    TemplateExportParams params = new TemplateExportParams(templatePath.getPath(),true);

    // 查询用户，转成map
    User user = userMapper.selectByPrimaryKey(id);
    Map<String, Object> map = EntityUtils.entityToMap(user);
    ImageEntity image = new ImageEntity();
    // image.setHeight(640); //测试发现 这里设置了长度和宽度在合并后的单元格中没有作用
    // image.setWidth(380);
    image.setRowspan(4);//向下合并三行
    image.setColspan(2);//向右合并两列
    image.setUrl(user.getPhoto());
    map.put("photo", image);
    Workbook workbook = ExcelExportUtil.exportExcel(params, map);

    // 导出的文件名称
    String filename="用户详细信息数据.xlsx";
    // 设置文件的打开方式和mime类型
    ServletOutputStream outputStream = response.getOutputStream();
    response.setHeader( "Content-Disposition", "attachment;filename="  + new String(filename.getBytes(),"ISO8859-1"));
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    workbook.write(outputStream);
}

导出CSV

csv的导出基本上和excel的导出一致,大体参数也是一致的

CsvExportParams 的参数描述如下

属性	类型	默认值	功能
encoding	String	UTF8	文件编码
spiltMark	String	,	分隔符
textMark	String	“	字符串识别,可以去掉,需要前后一致
titleRows	int	0	表格头,忽略
headRows	int	1	标题
exclusions	String[]	0	忽略的字段

public void downLoadCSVWithEasyPOI(HttpServletResponse response) throws Exception {
        ServletOutputStream outputStream = response.getOutputStream();
//            文件名
        String filename="百万数据.csv";
//            设置两个头 一个是文件的打开方式 一个是mime类型
        response.setHeader( "Content-Disposition", "attachment;filename="  + new String(filename.getBytes(),"ISO8859-1"));
        response.setContentType("application/csv");
//            创建一个用来写入到csv文件中的writer
        CsvExportParams params = new CsvExportParams();
//        设置忽略的列
        params.setExclusions(new String[]{"照片"}); //这里写表头 中文
        List<User> list = userMapper.selectAll();
        CsvExportUtil.exportCsv(params, User.class, list, outputStream);
    }

说明：从上述的代码中你会发现，如果需要导出几百万数据时不可能全部加载到一个List中的，所以easyPOI的方式导出csv是支持不了太大的数据量的，如果导出几百万条数据还是得选择OpenCSV方式导出。

注解说明

属性	类型	类型	说明
name	String	null	列名
needMerge	boolean	fasle	纵向合并单元格
orderNum	String	"0"	列的排序,支持name_id
replace	String[]	{}	值得替换导出是{a_id,b_id} 导入反过来
savePath	String	"upload"	导入文件保存路径
type	int	1	导出类型 1 是文本 2 是图片,3 是函数,10 是数字默认是文本
width	double	10	列宽
height	double	10	列高,后期打算统一使用@ExcelTarget的height,这个会被废弃,注意
isStatistics	boolean	fasle	自动统计数据,在追加一行统计,把所有数据都和输出这个处理会吞没异常,请注意这一点
isHyperlink	boolean	false	超链接,如果是需要实现接口返回对象
isImportField	boolean	true	校验字段,看看这个字段是不是导入的Excel中有,如果没有说明是错误的Excel,读取失败,支持name_id
exportFormat	String	""	导出的时间格式,以这个是否为空来判断是否需要格式化日期
importFormat	String	""	导入的时间格式,以这个是否为空来判断是否需要格式化日期
format	String	""	时间格式,相当于同时设置了exportFormat 和 importFormat
databaseFormat	String	"yyyyMMddHHmmss"	导出时间设置,如果字段是Date类型则不需要设置数据库如果是string类型,这个需要设置这个数据库格式,用以转换时间格式输出
numFormat	String	""	数字格式化,参数是Pattern,使用的对象是DecimalFormat
imageType	int	1	导出类型 1 从file读取 2 是从数据库中读取默认是文件同样导入也是一样的
suffix	String	""	文字后缀,如% 90 变成90%
isWrap	boolean	true	是否换行即支持\n
mergeRely	int[]	{}	合并单元格依赖关系,比如第二列合并是基于第一列则{1}就可以了
mergeVertical	boolean	fasle	纵向合并内容相同的单元格

导出Word

Word模板和Excel模板用法基本一致，支持的标签也是一致的，仅仅支持07版本的word也是只能生成后缀是docx的文档，poi对doc支持不好所以easyPOI中就没有支持doc，我们就拿docx做导出

下面列举下EasyPoi支持的指令以及作用，最主要的就是各种fe的用法

三元运算 {{test ? obj:obj2}}
n: 表示 这个cell是数值类型 {{n:}}
le: 代表长度{{le:()}} 在if/else 运用{{le:() > 8 ? obj1 : obj2}}
fd: 格式化时间 {{fd:(obj;yyyy-MM-dd)}}
fn: 格式化数字 {{fn:(obj;###.00)}}
fe: 遍历数据,创建row
!fe: 遍历数据不创建row
$fe: 下移插入,把当前行,下面的行全部下移.size()行,然后插入
#fe: 横向遍历
v_fe: 横向遍历值
!if: 删除当前列 {{!if:(test)}}
单引号表示常量值 ‘’ 比如’1’ 那么输出的就是 1
&NULL& 空格
&INDEX& 表示循环中的序号,自动添加
]] 换行符 多行遍历导出
sum： 统计数据

制作模板

正式员工劳动合同

甲方：博途教育

乙方： {{userName}}

根据xxxxx

办公用品如下：

名称	价值	是否需要归还	照片
`{{$fe: mapList t.id`	`t.price`	`t.needReturn?需要:不需要`	`t.image}}`

法定代表人签字：

乙方签字：

public void downloadWithEasyPOI(Long id,HttpServletResponse response) throws Exception {

    File rootPath = new File(ResourceUtils.getURL("classpath:").getPath()); //SpringBoot项目获取根目录的方式
    File templatePath = new File(rootPath.getAbsolutePath(),"/word_template/contract_template2.docx");

    // 先获取导出word需要的数据
    User user = this.findById(id);
    // 把需要的数据放到map中，方便替换
    Map<String,Object> params = new HashMap<String,Object>();
  // ------ 添加填充数据到map ------
    // ------------ 图片 下面是伪代码 -----------
    byte[] imageData = Files.readAllBytes(Paths.get(rootPath.getAbsolutePath()+"/test.png"));
    ImageEntity item = new ImageEntity();
    item.setWidth(300);
    item.setHeight(300);
    item.setData(imageData);
    item.setType(ImageEntity.Data);
    params.put("image",item);
    // -------------------------------
    // 根据模板+数据 导出文档
    XWPFDocument xwpfDocument = WordExportUtil.exportWord07(templatePath.getPath(), params);
    String filename=user.getUserName()+"_合同.docx";
    // 设置文件的打开方式和mime类型
    ServletOutputStream outputStream = response.getOutputStream();
    response.setHeader( "Content-Disposition", "attachment;filename="  + new String(filename.getBytes(),"ISO8859-1"));
    response.setContentType("application/vnd.openxmlformats-officedocument.wordprocessingml.document");
    xwpfDocument.write(outputStream);
}

无法处理单元格的循环嵌套（合并单元格），单独处理

public static void main(String[] args) throws Exception {
    // 加载Word文档

    File rootPath = new File(ResourceUtils.getURL("classpath:").getPath()); //SpringBoot项目获取根目录的方式
    File templatePath = new File(rootPath.getAbsolutePath(), "/word/User.docx");
    FileInputStream fis = new FileInputStream(templatePath);

    XWPFDocument document = new XWPFDocument(fis);

    // 获取文档中的所有表格
    List<XWPFTable> tables = document.getTables();

    // 遍历所有表格
    for (XWPFTable table : tables) {
        mergeCellsByColumn(table);
    }

    // 保存修改后的文档
    FileOutputStream fos = new FileOutputStream(new File(rootPath.getAbsolutePath(), "/downloads/" + "111.docx"));
    document.write(fos);
    fos.close();
}

/**
 * 按列内容相同合并单元格
 *
 * @param table 要处理的表格
 */
public static void mergeCellsByColumn(XWPFTable table) {
    // 获取表格的行数
    int numRows = table.getRows().size();
    int numCols = table.getRow(0).getTableCells().size(); // 假设每行列数相同

    for (int colIndex = 0; colIndex < numCols; colIndex++) {
        String previousContent = null;
        int startMergeRowIndex = -1;

        for (int rowIndex = 0; rowIndex < numRows; rowIndex++) {
            List<XWPFTableCell> cells = table.getRow(rowIndex).getTableCells();
            XWPFTableCell cell = cells.get(colIndex);
            String cellText = cell.getText().trim();

            // 判断当前单元格内容是否与前一个单元格相同
            if (StringUtils.isNotBlank(previousContent) && previousContent.equals(cellText)) {
                // 如果相同，则合并单元格
                table.getRow(startMergeRowIndex).getCell(colIndex).getCTTc().addNewTcPr().addNewVMerge().setVal(org.openxmlformats.schemas.wordprocessingml.x2006.main.STMerge.RESTART);
                cell.getCTTc().addNewTcPr().addNewVMerge().setVal(org.openxmlformats.schemas.wordprocessingml.x2006.main.STMerge.CONTINUE);
            } else {
                // 不同则记录当前单元格
                previousContent = cellText;
                startMergeRowIndex = rowIndex;
            }
        }
    }
}

百万数据

Excel 2003：在POI中使用HSSF对象时，excel 2003最多只允许存储65536条数据，一般用来处理较少的数据量。这时对于百万级别数据，Excel肯定容纳不了。 Excel 2007：当POI升级到XSSF对象时，它可以直接支持excel2007以上版本，因为它采用ooxml格式。这时excel可以支持1048576条数据，单个sheet表就支持近百万条数据。但实际运行时还可能存在问题，原因是执行POI报表所产生的行对象，单元格对象，字体对象，他们都不会销毁，这就导致OOM的风险。

java代码解析xml

dom4j:一次性加载xml文件再解析

SAX:逐行加载，逐行解析

**用户模式：**用户模式有许多封装好的方法操作简单，但创建太多的对象，非常耗内存（之前使用的方法）

**事件模式：**基于SAX方式解析XML，SAX全称Simple API for XML，它是一个接口，也是一个软件包。它是一种XML解析的替代方法，不同于DOM解析XML文档时把所有内容一次性加载到内存中的方式，它逐行扫描文档，一边扫描，一边解析。

SXSSF对象：是用来生成海量excel数据文件，主要原理是借助临时存储空间生成excel

在实例化SXSSFWorkBook这个对象时，可以指定在内存中所产生的POI导出相关对象的数量（默认100），一旦内存中的对象的个数达到这个指定值时，就将内存中的这些对象的内容写入到磁盘中（XML的文件格式），就可以将这些对象从内存中销毁，以后只要达到这个值，就会以类似的处理方式处理，直至Excel导出完成。

造数据

CREATE TABLE `tb_user2` (
  `id` bigint(20) NOT NULL  COMMENT '用户ID',
  `user_name` varchar(100) DEFAULT NULL COMMENT '姓名',
  `phone` varchar(15) DEFAULT NULL COMMENT '手机号',
  `province` varchar(50) DEFAULT NULL COMMENT '省份',
  `city` varchar(50) DEFAULT NULL COMMENT '城市',
  `salary` int(10) DEFAULT NULL,
  `hire_date` datetime DEFAULT NULL COMMENT '入职日期',
  `dept_id` bigint(20) DEFAULT NULL COMMENT '部门编号',
  `birthday` datetime DEFAULT NULL COMMENT '出生日期',
  `photo` varchar(200) DEFAULT NULL COMMENT '照片路径',
  `address` varchar(300) DEFAULT NULL COMMENT '现在住址' 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 存储过程
DELIMITER $$    -- 重新定义“;”分号
DROP PROCEDURE IF EXISTS test_insert $$   -- 如果有test_insert这个存储过程就删除
CREATE PROCEDURE test_insert()     -- 创建存储过程

BEGIN
 DECLARE n int DEFAULT 1;        -- 定义变量n=1
 SET AUTOCOMMIT=0;          -- 取消自动提交
 
  while n <= 5000000 do     
   INSERT INTO `tb_user2` VALUES ( n, CONCAT('测试', n), '13800000001', '北京市', '北京市', '11000', '2001-03-01 21:18:29', '1', '1981-03-02 00:00:00', '\\static\\user_photos\\1.jpg', '北京市西城区宣武大街1号院');
   SET n=n+1;
  END while;
  COMMIT;
END $$

-- 执行
CALL test_insert();
DROP PROCEDURE test_insert;

导出百万数据时有两个弊端：

1、不能使用模板

2、不能使用太多的样式

POI 导出

public class POIExcelUtil {
    public void downLoadMillion(HttpServletRequest request, HttpServletResponse response) throws Exception {
        // 创建一个空的工作薄
        Workbook workbook = new SXSSFWorkbook();
        int page = 1;
        int pageSize=200000;
        int rowIndex = 1; //每一个工作页的行数
        int num = 0; //总数据量
        Row row = null;
        Cell cell = null;
        Sheet sheet = null;
        while (true){  //不停地查询
            List<User> userList = this.findPage(page,pageSize);
            if(CollectionUtils.isEmpty(userList)){  //如果查询不到就不再查询了
                break;
            }
            if(num%1000000==0){  //每100W个就重新创建新的sheet和标题
                rowIndex = 1;
                // 在工作薄中创建一个工作表
                sheet = workbook.createSheet("第"+((num/1000000)+1)+"个工作表");
                // 设置列宽
                sheet.setColumnWidth(0,8*256);
                sheet.setColumnWidth(1,12*256);
                sheet.setColumnWidth(2,15*256);
                sheet.setColumnWidth(3,15*256);
                sheet.setColumnWidth(4,30*256);
                // 处理标题
                String[] titles = new String[]{"编号","姓名","手机号","入职日期","现住址"};
                // 创建标题行
                Row titleRow = sheet.createRow(0);

                for (int i = 0; i < titles.length; i++) {
                    cell = titleRow.createCell(i);
                    cell.setCellValue(titles[i]);
                }
             }

        // 处理内容
        for (User user : userList) {
            row = sheet.createRow(rowIndex);
            cell = row.createCell(0);
            cell.setCellValue(user.getId());

            cell = row.createCell(1);
            cell.setCellValue(user.getUserName());

            cell = row.createCell(2);
            cell.setCellValue(user.getPhone());

            cell = row.createCell(3);
            cell.setCellValue(simpleDateFormat.format(user.getHireDate()));

            cell = row.createCell(4);
            cell.setCellValue(user.getAddress());
            rowIndex++;
            num++;
        }
          page++;// 继续查询下一页
        }
        // 导出的文件名称
        String filename="百万数据.xlsx";
        // 设置文件的打开方式和mime类型
        ServletOutputStream outputStream = response.getOutputStream();
        response.setHeader( "Content-Disposition", "attachment;filename="  + new String(filename.getBytes(),"ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        workbook.write(outputStream);
    }
}

POI 导入

**事件模式：**它逐行扫描文档，一边扫描一边解析。由于应用程序只是在读取数据时检查数据，因此不需要将数据存储在内存中，这对于大型文档的解析是个巨大优势。步骤：（1）设置POI的事件模式根据Excel获取文件流根据文件流创建OPCPackage 用来组合读取到的xml 组合出来的数据占用的空间更小创建XSSFReader对象（2）Sax解析 自定义Sheet处理器 创建Sax的XmlReader对象设置Sheet的事件处理器逐行读取

自定义处理器

public class SheetHandler implements XSSFSheetXMLHandler.SheetContentsHandler {

    // 编号 用户名  手机号  入职日期 现住址
    private User user=null;
    @Override
    public void startRow(int rowIndex) { //每一行的开始   rowIndex代表的是每一个sheet的行索引
        if(rowIndex==0){
            user = null;
        }else{
            user = new User();
        }
    }
    @Override  //处理每一行的所有单元格
    public void cell(String cellName, String cellValue, XSSFComment comment) {

        if(user!=null){
            String letter = cellName.substring(0, 1);  //每个单元名称的首字母 A  B  C
            switch (letter){
                case "A":{
                    user.setId(Long.parseLong(cellValue));
                    break;
                }
                case "B":{
                    user.setUserName(cellValue);
                    break;
                }
            }
        }
    }
    @Override
    public void endRow(int rowIndex) { //每一行的结束
        if(rowIndex!=0){
            System.out.println(user);
        }

    }
}

自定义解析器

public class ExcelParser {

    public void parse (String path) throws Exception {
        //1.根据Excel获取OPCPackage对象
        OPCPackage pkg = OPCPackage.open(path, PackageAccess.READ);
        try {
            //2.创建XSSFReader对象
            XSSFReader reader = new XSSFReader(pkg);
            //3.获取SharedStringsTable对象
            SharedStringsTable sst = reader.getSharedStringsTable();
            //4.获取StylesTable对象
            StylesTable styles = reader.getStylesTable();
            XMLReader parser = XMLReaderFactory.createXMLReader();
            // 处理公共属性：Sheet名，Sheet合并单元格 ----- 用到了自定义处理器
            parser.setContentHandler(new XSSFSheetXMLHandler(styles,sst, new SheetHandler(), false));
            XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) reader.getSheetsData();
            while (sheets.hasNext()) {
                InputStream sheetstream = sheets.next();
                InputSource sheetSource = new InputSource(sheetstream);
                try {
                    parser.parse(sheetSource);
                } finally {
                    sheetstream.close();
                }
            }
        } finally {
            pkg.close();
        }
    }
}

POI相关技术

# POI相关技术

# 概述

# Excel

# POI说明

# POI 基本操作

# POI读取Excel

# POI写入Excel

# 样式

# 框线

# 合并单元格

# 行高

# 对齐方式和字体

# 模版导出

# Excel列表

# 图片

# 公式

# 自定义模版引擎

# POI导出Word

# EasyPOI

# 注解导出Excel

# 注解导入Excel

# 按模板导出

# 导出CSV

# 注解说明

# 导出Word

# 百万数据

# POI 导出

# POI 导入

# 自定义处理器

# 自定义解析器

POI相关技术

概述

Excel

POI说明

POI 基本操作

POI读取Excel

POI写入Excel

样式

框线

合并单元格

行高

对齐方式和字体

模版导出

Excel列表

图片

公式

自定义模版引擎

POI导出Word

EasyPOI

注解导出Excel

注解导入Excel

按模板导出

导出CSV

注解说明

导出Word

百万数据

POI 导出

POI 导入

自定义处理器

自定义解析器