poi学习

HSSF是针对2007以前的版本即xls， XSSF是针对2007版本以后的即xlsx

读取excel的demo

demo1：在resource下面创建了excel2.xslx

package com.xiaofeifei.poi.read;

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

import java.io.IOException;
import java.io.InputStream;

public class Demo1 {

    public static void main(String[] args) {
        Demo1 demo = new Demo1();
        InputStream inputStream = null;
        XSSFWorkbook workbook = null;

        try {
            inputStream = demo.getClass().getResourceAsStream("/excel2.xlsx");
            // 获取工作薄
            workbook = new XSSFWorkbook(inputStream);
            // 获取工作表
            XSSFSheet sheet = workbook.getSheetAt(0);

            // 获取行
            for (Row row : sheet) {
                for(Cell cell : row) {
                    //获取单元格中的内容：
                    String value = cell.getStringCellValue();
                    System.out.println(value);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }finally {

            try {
                inputStream.close();
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }

        }
    }
}

demo2 将数据写到excel中

package com.xiaofeifei.poi.write;

import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.net.URL;

public class Demo2 {

    public static void main(String[] args) {

        // 1.创建工作薄
        XSSFWorkbook workbook = new XSSFWorkbook();

        // 2.创建工作表
        XSSFSheet sheet = workbook.createSheet("创建工作表");

        // 3.创建行
        XSSFRow row = sheet.createRow(0);
        // 创建单元格
        row.createCell(0).setCellValue("王二");
        row.createCell(1).setCellValue("王二");

        // 创建一个输出流
        URL resource = Demo2.class.getResource("/");
        FileOutputStream outputStream = null;
        try {
            outputStream = new FileOutputStream(new File(resource.getPath()+"/excel3.xlsx"));
            workbook.write(outputStream);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                outputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }
}

将excel中的数据导入到数据库：

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

        <!-- mysql 驱动 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.26</version>
            <scope>compile</scope>
        </dependency>
        <!-- druid 连接池-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.9</version>
        </dependency>
    </dependencies>

做一个excel,有边框线，合并单元格，行高列宽，对齐方式，字体

package com.xiaofeifei.poi.write;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;

//导出一个excel，合并单元格，行高列宽，对齐方式，字体
public class POIDemo4 {

    public static void main(String[] args) throws Exception {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("由样式的sheet");
        // 创建首行即大标题的样式
        // 设置是否有边框
        CellStyle bigTitleRowStyle = workbook.createCellStyle();
        // poi分别提供了四个方向的边框，没有所有方向边框的方法
        bigTitleRowStyle.setBorderLeft(CellStyle.BORDER_THIN);
        bigTitleRowStyle.setBorderTop(CellStyle.BORDER_THIN);
        bigTitleRowStyle.setBorderBottom(CellStyle.BORDER_THIN);
        bigTitleRowStyle.setBorderRight(CellStyle.BORDER_THIN);
        // 设置对齐方式：水平对齐，垂直对齐
        bigTitleRowStyle.setAlignment(CellStyle.ALIGN_CENTER);
        bigTitleRowStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        // 创建字体
        Font font = workbook.createFont();
        font.setFontName("黑体");
        font.setFontHeightInPoints((short)18);
        // 把字体放入到样式中
        bigTitleRowStyle.setFont(font);

        Row bigTitleRow = sheet.createRow(0);
        bigTitleRow.setHeightInPoints(42);
        for(int i = 0;i < 5; i++) {
            Cell cell = bigTitleRow.createCell(i);
            cell.setCellStyle(bigTitleRowStyle);
        }
        // 设置合并单元格
        sheet.addMergedRegion(new CellRangeAddress(0,0,0,4)); // int firstRow起始行，lastRow结束行，firstCol 开始列 lastCol结束列
        // 向单元格中放一句话：用户信息数据
        sheet.getRow(0).getCell(0).setCellValue("用户信息数据");

        // 创建小标题样式
        CellStyle littleTitleRowStyle = workbook.createCellStyle();
        // 将上面的样式克隆下来
        littleTitleRowStyle.cloneStyleFrom(bigTitleRowStyle);
        // 标题的字体和首行不一样，单独再创建字体
        Font littleFont = workbook.createFont();
        littleFont.setFontName("黑体");
        littleFont.setFontHeightInPoints((short)18);
        littleFont.setBold(true); //字体设置加粗
        // 把字体放入到样式中
        littleTitleRowStyle.setFont(font);
        String[] littleTitles = new String[] {"编号","姓名","手机号","入职时间","先住地址"};
        Row littleTitleRow = sheet.createRow(1);
        littleTitleRow.setHeightInPoints(31.5F);
        for(int i = 0; i < littleTitles.length; i++) {
            Cell cell = littleTitleRow.createCell(i);
            cell.setCellValue(littleTitles[i]);
            cell.setCellStyle(littleTitleRowStyle);
        }



        // 内容样式
        CellStyle contentRowStyle = workbook.createCellStyle();
        contentRowStyle.cloneStyleFrom(littleTitleRowStyle);
        contentRowStyle.setAlignment(CellStyle.ALIGN_LEFT);
        // 标题的字体和首行不一样，单独再创建字体
        Font contentFont = workbook.createFont();
        contentFont.setFontName("宋体");
        contentFont.setFontHeightInPoints((short)12);
        contentFont.setBold(false); //字体设置不加粗
        String[] datas = new String[] {"1","张三","111","2021-01-30","天堂"};
        Row dataRow1 = sheet.createRow(2);
        for(int i = 0; i < datas.length; i++) {
            Cell cell = dataRow1.createCell(i);
            cell.setCellValue(datas[i]);
            cell.setCellStyle(contentRowStyle);
        }
        workbook.write(new FileOutputStream("style.xlsx"));

    }
}

由于样式浪费了大量精力，我们可以首先创建好模板，我们只向模板中放数据就可以了

我们直接将上述生成的excel中从第三行删除作为模板，我们写入数据

package com.xiaofeifei.controller;

import com.xiaofeifei.model.Employee;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Controller;
import org.springframework.util.ResourceUtils;
import org.springframework.web.bind.annotation.GetMapping;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Controller
public class ExportExcelController {

    @GetMapping("/download-excel")
    public void downLoadXlxsByPoiWithTemplate(HttpServletResponse response) throws Exception{
        File rootFile = new File(ResourceUtils.getURL("classpath:").getPath());
        File template = new File(rootFile, "style_template.xlsx"); //在resources 文件夹下放了模板
        XSSFWorkbook workbook = new XSSFWorkbook(template);
        XSSFSheet sheet = workbook.getSheetAt(0);
        int rowIndex = 2;
        Cell cell = null;
        List<Employee> employees = getEmployees();
        Employee employee = null;
        int size = employees.size();
        // 对于单元格的样式，我们可以获取在模板中第二个sheet页中写好一个单元格的样式，我们直接复制
        Sheet sheet1 = workbook.getSheetAt(1);
        Row row1 = sheet1.getRow(0);
        Cell templateCell = row1.getCell(0);
        CellStyle templateCellCellStyle = templateCell.getCellStyle();

        for(int i = 0; i < size; i++) {
            Row row = sheet.createRow(i + rowIndex);
            employee = employees.get(i);
            cell = row.createCell(0);
            cell.setCellValue(employee.getId());
            cell.setCellStyle(templateCellCellStyle);

            cell = row.createCell(1);
            cell.setCellValue(employee.getUserName());
            cell.setCellStyle(templateCellCellStyle);

             cell = row.createCell(2);
             cell.setCellValue(employee.getUserPhone());
            cell.setCellStyle(templateCellCellStyle);

             cell = row.createCell(3);
             cell.setCellValue(employee.getDate());
            cell.setCellStyle(templateCellCellStyle);

            cell = row.createCell(4);
            cell.setCellValue(employee.getAddress());
            cell.setCellStyle(templateCellCellStyle);
        }
        // 把第二个sheet删除掉
        workbook.removeSheetAt(1);
        String filename ="员工数据.xlsx";
        response.setHeader("content-disposition","attachement;filename=" + new String(filename.getBytes(),"ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        workbook.write(response.getOutputStream());
    }

    private List<Employee> getEmployees() {
        List<Employee> employees = new ArrayList<>();
        Employee employee = null;
        for(int i = 1; i <= 2; i++) {
            employee = new Employee();
            employee.setId(i);
            employee.setDate(new Date());
            employee.setAddress("nihao");
            employee.setUserName("zhangshan");
            employee.setUserPhone("1111");
            employees.add(employee);
        }
        return employees;
    }


}

在poi中根据模板做用户信息明细

 上述图片比较特殊：1、位置比较特殊，设计合并单元格，从第二行开始到第五行从第三列开始到第四列 2、数据比较特殊，是图片

 现在处理照片，拿到图片

用patriarch 控制图片的写入和clientanchor指定图片的位置

 注意XSSFClientAnchor 有8各参数，各个参数的含义是，后四个参数的含义是：起始列起始行到结束列结束行，第一个参数图片左上角表示偏离x轴多少，第二个参数表示图片左上角偏离y轴多少，第三个参数表示图片右下角偏x轴多少，第四个参数表示图片右下角偏离y轴多少，偏离的单位是英式公式单位，1厘米等于360000

 虽然我们结束行5结束列为4，但是

这样写完会造成无法填充满 

 所以我们要添加一行

 poi写计算的公式：根据入职时间 计算出工作时间

 然后直接将复制上述函数公式

 poi支持哪些函数公式，可以去官网查，有的函数官网没有，自己可以试试

 当然用代码设置对应的函数完全没有必要，可以函数放到对应的模板中，即直接设置单元格的函数

自定义数据引擎：当某个单元格位置发生变动，我们的代码就要变动，例如手机号本来要填3行3列的单元格，现在要改为4行三列，我们用数据引擎就可以解决

 

 

 

 

 

 百万数据导出：

 操作高版本的excel本质就是操作xml

new woorkbook 这种方式其实用的是dom4j的方式

 

 

 创建一个存储过程，用来造数据，自定义分号原因，执行分号就自动提交了

 百万数据导出：不能使用模板和太多的样式，每个sheet数据不超过100万行

 

 

 

 百万级数据导入excel

 基于事件驱动的

 

 

 

 

poi操作word

 对于一个word文档，另存为html,就自动生成了相关的html文档

然后编辑html文件，将对应的内容去掉

 

这样就有了与word一样样式的网页，poi操作word和excel类似，用模板