POI报表入门,excel

POI报表入门,excel,使用事件模型解析百万数据excel报表

1.pom依赖:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>cn.itcast</groupId>
    <artifactId>poi-demo</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
        <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>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.0.1</version>
        </dependency>
    </dependencies>
</project>

2.使用POI创建excel

package cn.itcast.poi.test;

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

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

/**
 * 使用POI创建excel
 */
public class PoiTest01 {


    public static void main(String[] args) throws Exception {
        //1.创建工作簿  HSSFWorkbook -- 2003
        Workbook wb = new XSSFWorkbook(); //2007版本
        //2.创建表单sheet
        Sheet sheet = wb.createSheet("test");
        //3.文件流
        FileOutputStream pis = new FileOutputStream("E:\\excel\\poi\\test.xlsx");
        //4.写入文件
        wb.write(pis);
        pis.close();
    }
}

3.创建单元格写入内容

package cn.itcast.poi.test;

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 PoiTest02 {


    public static void main(String[] args) throws Exception {
        //创建工作簿  HSSFWorkbook -- 2003
        Workbook wb = new XSSFWorkbook(); //2007版本
        //创建表单sheet
        Sheet sheet = wb.createSheet("test");
        //创建行对象  参数：索引（从0开始）
        Row row = sheet.createRow(2);
        //创建单元格对象  参数：索引（从0开始）
        Cell cell = row.createCell(2);
        //向单元格中写入内容
        cell.setCellValue("传智播客");
        //文件流
        FileOutputStream pis = new FileOutputStream("E:\\excel\\poi\\test1.xlsx");
        //写入文件
        wb.write(pis);
        pis.close();
    }
}

4.单元格样式处理

package cn.itcast.poi.test;

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

import java.io.FileOutputStream;

/**
 * 单元格样式处理
 */
public class PoiTest03 {


    public static void main(String[] args) throws Exception {
        //创建工作簿  HSSFWorkbook -- 2003
        Workbook wb = new XSSFWorkbook(); //2007版本
        //创建表单sheet
        Sheet sheet = wb.createSheet("test");
        //创建行对象  参数：索引（从0开始）
        Row row = sheet.createRow(2);
        //创建单元格对象  参数：索引（从0开始）
        Cell cell = row.createCell(2);
        //向单元格中写入内容
        cell.setCellValue("传智播客");

        //样式处理
        //创建样式对象
        CellStyle style = wb.createCellStyle();
        style.setBorderTop(BorderStyle.THIN);//上边框
        style.setBorderBottom(BorderStyle.THIN);//下边框
        style.setBorderLeft(BorderStyle.THIN);//左边框
        style.setBorderRight(BorderStyle.THIN);//右边框
        //创建字体对象
        Font font = wb.createFont();
        font.setFontName("华文行楷"); //字体
        font.setFontHeightInPoints((short)28);//字号
        style.setFont(font);

        //行高和列宽
        row.setHeightInPoints(50);//行高
        //列宽的宽度  字符宽度
        sheet.setColumnWidth(2,31 * 256);//列宽

        //剧中显示
        style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中

        //向单元格设置样式
        cell.setCellStyle(style);

        //文件流
        FileOutputStream pis = new FileOutputStream("E:\\excel\\poi\\test2.xlsx");
        //写入文件
        wb.write(pis);
        pis.close();
    }
}

5.插入图片

package cn.itcast.poi.test;

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

import java.io.FileInputStream;
import java.io.FileOutputStream;

/**
 * 插入图片
 */
public class PoiTest04 {


    public static void main(String[] args) throws Exception {
        //创建工作簿  HSSFWorkbook -- 2003
        Workbook wb = new XSSFWorkbook(); //2007版本
        //创建表单sheet
        Sheet sheet = wb.createSheet("test");

        //读取图片流
        FileInputStream stream = new FileInputStream("E:\\excel\\poi\\logo.jpg");
        //转化二进制数组
        byte[] bytes = IOUtils.toByteArray(stream);
        stream.read(bytes);
        //向POI内存中添加一张图片，返回图片在图片集合中的索引
        int index = wb.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);//参数一：图片的二进制数据，参数二：图片类型
        //绘制图片工具类
        CreationHelper helper = wb.getCreationHelper();
        //创建一个绘图对象
        Drawing<?> patriarch = sheet.createDrawingPatriarch();
        //创建锚点，设置图片坐标
        ClientAnchor anchor = helper.createClientAnchor();
        anchor.setRow1(0);
        anchor.setCol1(0);
        //绘制图片
        Picture picture = patriarch.createPicture(anchor, index);//图片位置，图片的索引
        picture.resize();//自适应渲染图片

        //文件流
        FileOutputStream pis = new FileOutputStream("E:\\excel\\poi\\test3.xlsx");
        //写入文件
        wb.write(pis);
        pis.close();
    }
}

6. 读取excel并解析 sheet.getLastRowNum() : 最后一行的索引 row.getLastCellNum() ：最后一个单元格的号码

package cn.itcast.poi.test;

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

import java.io.IOException;

/**
 * 读取excel并解析
 *      sheet.getLastRowNum() : 最后一行的索引
 *      row.getLastCellNum() ： 最后一个单元格的号码
 */
public class PoiTest05 {

    public static void main(String[] args) throws Exception {
        //1.根据Excel文件创建工作簿
        Workbook wb = new XSSFWorkbook("C:\\Users\\ThinkPad\\Desktop\\ihrm\\day8\\资源\\百万数据报表\\demo.xlsx");
        //2.获取Sheet
        Sheet sheet = wb.getSheetAt(0);//参数：索引
        //3.获取Sheet中的每一行，和每一个单元格
        for (int rowNum = 0; rowNum<= sheet.getLastRowNum() ;rowNum ++) {
            Row row = sheet.getRow(rowNum);//根据索引获取每一个行
            StringBuilder sb = new StringBuilder();
            for(int cellNum=2;cellNum< row.getLastCellNum(); cellNum ++) {
                //根据索引获取每一个单元格
                Cell cell = row.getCell(cellNum);
                //获取每一个单元格的内容
                Object value = getCellValue(cell);
                sb.append(value).append("-");
            }
            System.out.println(sb.toString());
        }
    }

    public static Object getCellValue(Cell cell) {
        //1.获取到单元格的属性类型
        CellType cellType = cell.getCellType();
        //2.根据单元格数据类型获取数据
        Object value = null;
        switch (cellType) {
            case STRING:
                value = cell.getStringCellValue();
                break;
            case BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case NUMERIC:
                if(DateUtil.isCellDateFormatted(cell)) {
                    //日期格式
                    value = cell.getDateCellValue();
                }else{
                    //数字
                    value = cell.getNumericCellValue();
                }
                break;
            case FORMULA: //公式
                value = cell.getCellFormula();
                break;
            default:
                break;
        }
        return value;
    }
}

7.使用事件模型解析百万数据excel报表

package cn.itcast.poi.test;

import cn.itcast.poi.entity.cn.itcast.poi.handler.SheetHandler;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.xml.sax.InputSource;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.XMLReaderFactory;

import java.io.InputStream;
import java.util.Iterator;

/**
 * 使用事件模型解析百万数据excel报表
 */
public class PoiTest06 {

    public static void main(String[] args) throws Exception {
        String path = "C:\\Users\\ThinkPad\\Desktop\\ihrm\\day8\\资源\\百万数据报表\\demo.xlsx";

        //1.根据excel报表获取OPCPackage
        OPCPackage opcPackage = OPCPackage.open(path, PackageAccess.READ);
        //2.创建XSSFReader
        XSSFReader reader = new XSSFReader(opcPackage);
        //3.获取SharedStringTable对象
        SharedStringsTable table = reader.getSharedStringsTable();
        //4.获取styleTable对象
        StylesTable stylesTable = reader.getStylesTable();
        //5.创建Sax的xmlReader对象
        XMLReader xmlReader = XMLReaderFactory.createXMLReader();
        //6.注册事件处理器
        XSSFSheetXMLHandler xmlHandler = new XSSFSheetXMLHandler(stylesTable,table,new SheetHandler(),false);
        xmlReader.setContentHandler(xmlHandler);
        //7.逐行读取
        XSSFReader.SheetIterator sheetIterator = (XSSFReader.SheetIterator) reader.getSheetsData();
        while (sheetIterator.hasNext()) {
            InputStream stream = sheetIterator.next(); //每一个sheet的流数据
            InputSource is = new InputSource(stream);
            xmlReader.parse(is);
        }
    }
}

7.1工具类:

package cn.itcast.poi.entity.cn.itcast.poi.handler;

import cn.itcast.poi.entity.PoiEntity;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.usermodel.XSSFComment;

/**
 * 自定义的事件处理器
 *  处理每一行数据读取
 *      实现接口
 */
public class SheetHandler implements XSSFSheetXMLHandler.SheetContentsHandler {

    private PoiEntity entity;
    /**
     * 当开始解析某一行的时候触发
     *      i:行索引
     */
    @Override
    public void startRow(int i) {
        //实例化对象
        if(i>0) {
            entity = new PoiEntity();
        }
    }

    /**
     * 当结束解析某一行的时候触发
     *      i:行索引
     */
    @Override
    public void endRow(int i) {
        //使用对象进行业务操作
        System.out.println(entity);
    }

    /**
     * 对行中的每一个表格进行处理
     *      cellReference: 单元格名称
     *      value：数据
     *      xssfComment：批注
     */
    @Override
    public void cell(String cellReference, String value, XSSFComment xssfComment) {
        //对对象属性赋值
        if(entity != null) {
            String pix = cellReference.substring(0,1);
            switch (pix) {
                case "A":
                    entity.setId(value);
                    break;
                case "B":
                    entity.setBreast(value);
                    break;
                case "C":
                    entity.setAdipocytes(value);
                    break;
                case "D":
                    entity.setNegative(value);
                    break;
                case "E":
                    entity.setStaining(value);
                    break;
                case "F":
                    entity.setSupportive(value);
                    break;
                default:
                    break;
            }
        }
    }
}

7.2实体类:

package cn.itcast.poi.entity;


public class PoiEntity {
    private String id;
    private String breast;
    private String adipocytes;
    private String negative;
    private String staining;
    private String supportive;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getBreast() {
        return breast;
    }

    public void setBreast(String breast) {
        this.breast = breast;
    }

    public String getAdipocytes() {
        return adipocytes;
    }

    public void setAdipocytes(String adipocytes) {
        this.adipocytes = adipocytes;
    }

    public String getNegative() {
        return negative;
    }

    public void setNegative(String negative) {
        this.negative = negative;
    }

    public String getStaining() {
        return staining;
    }

    public void setStaining(String staining) {
        this.staining = staining;
    }

    public String getSupportive() {
        return supportive;
    }

    public void setSupportive(String supportive) {
        this.supportive = supportive;
    }

    @Override
    public String toString() {
        return "PoiEntity{" +
                "id='" + id + '\'' +
                ", breast='" + breast + '\'' +
                ", adipocytes='" + adipocytes + '\'' +
                ", negative='" + negative + '\'' +
                ", staining='" + staining + '\'' +
                ", supportive='" + supportive + '\'' +
                '}';
    }
}

7.3导入Excel:

POI报表入门,excel,使用事件模型解析百万数据excel报表

补充:

在web项目中操作

1.导入

数据导入代码示例:

  /**
     * 导入Excel，添加用户
     *  文件上传：springboot
     */
    @RequestMapping(value="/user/import",method = RequestMethod.POST)
    public Result importUser(@RequestParam(name="file") MultipartFile file) throws Exception {
        //1.解析Excel
        //1.1.根据Excel文件创建工作簿
        Workbook wb = new XSSFWorkbook(file.getInputStream());
        //1.2.获取Sheet
        Sheet sheet = wb.getSheetAt(0);//参数：索引
        //1.3.获取Sheet中的每一行，和每一个单元格
        //2.获取用户数据列表
        List<User> list = new ArrayList<>();
        System.out.println(sheet.getLastRowNum());
        for (int rowNum = 1; rowNum<= sheet.getLastRowNum() ;rowNum ++) {
            Row row = sheet.getRow(rowNum);//根据索引获取每一个行
            Object [] values = new Object[row.getLastCellNum()];
            for(int cellNum=1;cellNum< row.getLastCellNum(); cellNum ++) {
                Cell cell = row.getCell(cellNum);
                Object value = getCellValue(cell);
                values[cellNum] = value;
            }
            User user = new User(values);
            list.add(user);
        }
        //3.批量保存用户
        userService.saveAll(list,companyId,companyName);

        return new Result(ResultCode.SUCCESS);
    }

    public static Object getCellValue(Cell cell) {
        //1.获取到单元格的属性类型
        CellType cellType = cell.getCellType();
        //2.根据单元格数据类型获取数据
        Object value = null;
        switch (cellType) {
            case STRING:
                value = cell.getStringCellValue();
                break;
            case BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case NUMERIC:
                if(DateUtil.isCellDateFormatted(cell)) {
                    //日期格式
                    value = cell.getDateCellValue();
                }else{
                    //数字
                    value = cell.getNumericCellValue();
                }
                break;
            case FORMULA: //公式
                value = cell.getCellFormula();
                break;
            default:
                break;
        }
        return value;
    }

构造方法构造数据:

  public User(Object [] values) {
        //用户名	手机号	工号	聘用 形式	入职 时间	部门编码
        this.username = values[1].toString();
        this.mobile = values[2].toString();
        this.workNumber = new DecimalFormat("#").format(values[3]).toString();
        this.formOfEmployment =((Double) values[4]).intValue();
        this.timeOfEntry = (Date) values[5];
        this.departmentId = values[6].toString(); //部门编码 != 部门id
    }

2.导出

数据导出代码示例:

/**
     * 当月人事报表导出
     *  参数：
     *      年月-月（2018-02%）
     */
    @RequestMapping(value = "/export/{month}", method = RequestMethod.GET)
    public void export(@PathVariable String month) throws Exception {
        //1.获取报表数据
        List<EmployeeReportResult> list = userCompanyPersonalService.findByReport(companyId,month);
        //2.构造Excel
        //创建工作簿
        //SXSSFWorkbook : 百万数据报表
        // SXSSFWorkbook wb = new SXSSFWorkbook(100); //阈值，内存中的对象数量最大数量
        Workbook wb = new XSSFWorkbook();
       
        //构造sheet
        Sheet sheet = wb.createSheet();
        //创建行
        //标题
        String [] titles = "编号,姓名,手机,最高学历,国家地区,护照号,籍贯,生日,属相,入职时间,离职类型,离职原因,离职时间".split(",");
        //处理标题

        Row row = sheet.createRow(0);

        int titleIndex=0;
        for (String title : titles) {
            Cell cell = row.createCell(titleIndex++);
            cell.setCellValue(title);
        }

        int rowIndex = 1;
        Cell cell=null;
        for(int i=0;i<10000;i++){
        for (EmployeeReportResult employeeReportResult : list) {
            row = sheet.createRow(rowIndex++);
            // 编号,
            cell = row.createCell(0);
            cell.setCellValue(employeeReportResult.getUserId());
            // 姓名,
            cell = row.createCell(1);
            cell.setCellValue(employeeReportResult.getUsername());
            // 手机,
            cell = row.createCell(2);
            cell.setCellValue(employeeReportResult.getMobile());
            // 最高学历,
            cell = row.createCell(3);
            cell.setCellValue(employeeReportResult.getTheHighestDegreeOfEducation());
            // 国家地区,
            cell = row.createCell(4);
            cell.setCellValue(employeeReportResult.getNationalArea());
            // 护照号,
            cell = row.createCell(5);
            cell.setCellValue(employeeReportResult.getPassportNo());
            // 籍贯,
            cell = row.createCell(6);
            cell.setCellValue(employeeReportResult.getNativePlace());
            // 生日,
            cell = row.createCell(7);
            cell.setCellValue(employeeReportResult.getBirthday());
            // 属相,
            cell = row.createCell(8);
            cell.setCellValue(employeeReportResult.getZodiac());
            // 入职时间,
            cell = row.createCell(9);
            cell.setCellValue(employeeReportResult.getTimeOfEntry());
            // 离职类型,
            cell = row.createCell(10);
            cell.setCellValue(employeeReportResult.getTypeOfTurnover());
            // 离职原因,
            cell = row.createCell(11);
            cell.setCellValue(employeeReportResult.getReasonsForLeaving());
            // 离职时间
            cell = row.createCell(12);
            cell.setCellValue(employeeReportResult.getResignationTime());
        }
        }
        //3.完成下载
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        wb.write(os);
        new DownloadUtils().download(os,response,month+"人事报表.xlsx");
    }

导出工具类:

DownloadUtils

package com.ihrm.common.utils;

import org.apache.poi.ss.usermodel.Workbook;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;

public class DownloadUtils {
    public void download(ByteArrayOutputStream byteArrayOutputStream, HttpServletResponse response, String returnName) throws IOException {
        response.setContentType("application/octet-stream");
        returnName = response.encodeURL(new String(returnName.getBytes(),"iso8859-1"));			//保存的文件名,必须和页面编码一致,否则乱码
        response.addHeader("content-disposition","attachment;filename=" + returnName);
        response.setContentLength(byteArrayOutputStream.size());
        ServletOutputStream outputstream = response.getOutputStream();	//取得输出流
        byteArrayOutputStream.writeTo(outputstream);					//写到输出流
        byteArrayOutputStream.close();									//关闭
        outputstream.flush();											//刷数据
    }
}

3.模板打印

[ 通过预先做好的模板来导出,导出的报表样式与模板一致 ]

3.1 制作模板

POI报表入门,excel,使用事件模型解析百万数据excel报表

下载地址:

链接：https://pan.baidu.com/s/1dOlavfW8I-gYaSn45_yskA
提取码：c56c

3.2 模板路径

ihrm_employee\src\main\resources\excel-template\hr-demo.xlsx

POI报表入门,excel,使用事件模型解析百万数据excel报表

3.3代码实现 :


@RequestMapping(value = "/export/{month}", method = RequestMethod.GET)
public void export(@PathVariable(name = "month") String month) throws Exception {
//1.构造数据List<EmployeeReportResult> list =
userCompanyPersonalService.findByReport(companyId,month+"%");

//2.加载模板流数据
Resource resource = new ClassPathResource("excel-template/hr-demo.xlsx");
 FileInputStream fis = new FileInputStream(resource.getFile());

//3.根据文件流，加载指定的工作簿
XSSFWorkbook wb = new XSSFWorkbook(fis);

//4.读取工作表
Sheet sheet = wb.getSheetAt(0);

//5.抽取公共的样式
Row styleRow = sheet.getRow(2);    
CellStyle [] styles = new CellStyle[styleRow.getLastCellNum()];
 for(int i=0;i<styleRow.getLastCellNum();i++) {
styles[i] = styleRow.getCell(i).getCellStyle();
}

//6.构造每行和单元格数据
AtomicInteger datasAi = new AtomicInteger(2);


Cell cell = null;
for (EmployeeReportResult report : list) {
Row dataRow = sheet.createRow(datasAi.getAndIncrement());
//编号
cell = dataRow.createCell(0); 
cell.setCellValue(report.getUserId());
cell.setCellStyle(styles[0]);
//姓名
cell = dataRow.createCell(1); 
cell.setCellValue(report.getUsername());
cell.setCellStyle(styles[1]);
//手机
cell = dataRow.createCell(2); 
cell.setCellValue(report.getMobile());
cell.setCellStyle(styles[2]);
//最高学历
cell = dataRow.createCell(3); 
cell.setCellValue(report.getTheHighestDegreeOfEducation());
cell.setCellStyle(styles[3]);
//国家地区
cell = dataRow.createCell(4); 
cell.setCellValue(report.getNationalArea());
cell.setCellStyle(styles[4]);
//护照号
cell = dataRow.createCell(5); 
cell.setCellValue(report.getPassportNo());
cell.setCellStyle(styles[5]);
//籍贯
cell = dataRow.createCell(6); 
cell.setCellValue(report.getNativePlace());
cell.setCellStyle(styles[6]);
//生日
cell = dataRow.createCell(7); 
cell.setCellValue(report.getBirthday());
cell.setCellStyle(styles[7]);
//属相
cell = dataRow.createCell(8); 
cell.setCellValue(report.getZodiac());
cell.setCellStyle(styles[8]);
//入职时间
cell = dataRow.createCell(9); 
cell.setCellValue(report.getTimeOfEntry());
cell.setCellStyle(styles[9]);
//离职类型
cell = dataRow.createCell(10);
cell.setCellValue(report.getTypeOfTurnover()); 
cell.setCellStyle(styles[10]);
//离职原因
cell = dataRow.createCell(11); 
cell.setCellValue(report.getReasonsForLeaving());
cell.setCellStyle(styles[11]);
//离职时间
cell = dataRow.createCell(12); 
cell.setCellStyle(styles[12]); 
cell.setCellValue(report.getResignationTime());
}
String fileName = URLEncoder.encode(month+"人员信息.xlsx", "UTF-8"); response.setContentType("application/octet-stream");
response.setHeader("content-disposition", "attachment;filename=" + new String(fileName.getBytes("ISO8859-1")));
 response.setHeader("filename", fileName); 
wb.write(response.getOutputStream());
}

4.封装好的工具类

下载地址:

链接：https://pan.baidu.com/s/1iW7DPaueyzdxUEUtAgHsKg
提取码：qvir

4.1 ExcelAttribute.java 注解序号工具解析类

package com.ihrm.domain.poi;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelAttribute {
    /** 对应的列名称 */
    String name() default "";

    /** 列序号 */
    int sort();

    /** 字段类型对应的格式 */
    String format() default "";

}

注意: 使用工具类时需要在对应实体类字段加上注解序号 @ExcelAttribute(sort = 0)

例:

[

POI报表入门,excel,使用事件模型解析百万数据excel报表

]

4.2 ExcelExportUtil.java 导出工具类

package com.ihrm.common.poi.utils;

import com.ihrm.domain.poi.ExcelAttribute;
import lombok.Getter;
import lombok.Setter;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;

@Getter
@Setter
public class ExcelExportUtil<T> {

    private int rowIndex;
    private int styleIndex;
    private String templatePath;
    private Class clazz;
    private  Field fields[];

    public ExcelExportUtil(Class clazz,int rowIndex,int styleIndex) {
        this.clazz = clazz;
        this.rowIndex = rowIndex;
        this.styleIndex = styleIndex;
        fields = clazz.getDeclaredFields();
    }

    /**
     * 基于注解导出
     */
    public void export(HttpServletResponse response,InputStream is, List<T> objs,String fileName) throws Exception {

        XSSFWorkbook workbook = new XSSFWorkbook(is);
        Sheet sheet = workbook.getSheetAt(0);

        CellStyle[] styles = getTemplateStyles(sheet.getRow(styleIndex));

        AtomicInteger datasAi = new AtomicInteger(rowIndex);
        for (T t : objs) {
            Row row = sheet.createRow(datasAi.getAndIncrement());
            for(int i=0;i<styles.length;i++) {
                Cell cell = row.createCell(i);
                cell.setCellStyle(styles[i]);
                for (Field field : fields) {
                    if(field.isAnnotationPresent(ExcelAttribute.class)){
                        field.setAccessible(true);
                        ExcelAttribute ea = field.getAnnotation(ExcelAttribute.class);
                        if(i == ea.sort()) {
                            cell.setCellValue(field.get(t).toString());
                        }
                    }
                }
            }
        }
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setContentType("application/octet-stream");
        response.setHeader("content-disposition", "attachment;filename=" + new String(fileName.getBytes("ISO8859-1")));
        response.setHeader("filename", fileName);
        workbook.write(response.getOutputStream());
    }

    public CellStyle[] getTemplateStyles(Row row) {
        CellStyle [] styles = new CellStyle[row.getLastCellNum()];
        for(int i=0;i<row.getLastCellNum();i++) {
            styles[i] = row.getCell(i).getCellStyle();
        }
        return styles;
    }
}

4.3 ExcelImportUtil.java 导入工具类

package com.ihrm.common.poi.utils;

import com.ihrm.domain.poi.ExcelAttribute;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.format.CellFormat;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


public class ExcelImportUtil<T> {
 
    private Class clazz;
    private  Field fields[];
 
    public ExcelImportUtil(Class clazz) {
        this.clazz = clazz;
        fields = clazz.getDeclaredFields();
    }
 
    /**
     * 基于注解读取excel
     */
    public List<T> readExcel(InputStream is, int rowIndex,int cellIndex) {
        List<T> list = new ArrayList<T>();
        T entity = null;
        try {
            XSSFWorkbook workbook = new XSSFWorkbook(is);
            Sheet sheet = workbook.getSheetAt(0);
            // 不准确
            int rowLength = sheet.getLastRowNum();

            System.out.println(sheet.getLastRowNum());
            for (int rowNum = rowIndex; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row row = sheet.getRow(rowNum);
                entity = (T) clazz.newInstance();
                System.out.println(row.getLastCellNum());
                for (int j = cellIndex; j < row.getLastCellNum(); j++) {
                    Cell cell = row.getCell(j);
                    for (Field field : fields) {
                        if(field.isAnnotationPresent(ExcelAttribute.class)){
                            field.setAccessible(true);
                            ExcelAttribute ea = field.getAnnotation(ExcelAttribute.class);
                            if(j == ea.sort()) {
                                field.set(entity, covertAttrType(field, cell));
                            }
                        }
                    }
                }
                list.add(entity);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
 

    /**
     * 类型转换 将cell 单元格格式转为 字段类型
     */
    private Object covertAttrType(Field field, Cell cell) throws Exception {
        String fieldType = field.getType().getSimpleName();
        if ("String".equals(fieldType)) {
            return getValue(cell);
        }else if ("Date".equals(fieldType)) {
            return new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").parse(getValue(cell)) ;
        }else if ("int".equals(fieldType) || "Integer".equals(fieldType)) {
            return Integer.parseInt(getValue(cell));
        }else if ("double".equals(fieldType) || "Double".equals(fieldType)) {
            return Double.parseDouble(getValue(cell));
        }else {
            return null;
        }
    }
 
 
    /**
     * 格式转为String
     * @param cell
     * @return
     */
    public String getValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case STRING:
                return cell.getRichStringCellValue().getString().trim();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    Date dt = DateUtil.getJavaDate(cell.getNumericCellValue());
                    return new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(dt);
                } else {
                    // 防止数值变成科学计数法
                    String strCell = "";
                    Double num = cell.getNumericCellValue();
                    BigDecimal bd = new BigDecimal(num.toString());
                    if (bd != null) {
                        strCell = bd.toPlainString();
                    }
                    // 去除 浮点型 自动加的 .0
                    if (strCell.endsWith(".0")) {
                        strCell = strCell.substring(0, strCell.indexOf("."));
                    }
                    return strCell;
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            default:
                return "";
        }
    }
}

4.4 DownloadUtils下载工具类

package com.ihrm.common.utils;

import org.apache.poi.ss.usermodel.Workbook;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;

public class DownloadUtils {
    public void download(ByteArrayOutputStream byteArrayOutputStream, HttpServletResponse response, String returnName) throws IOException {
        response.setContentType("application/octet-stream");
        returnName = response.encodeURL(new String(returnName.getBytes(),"iso8859-1"));			//保存的文件名,必须和页面编码一致,否则乱码
        response.addHeader("content-disposition","attachment;filename=" + returnName);
        response.setContentLength(byteArrayOutputStream.size());
        ServletOutputStream outputstream = response.getOutputStream();	//取得输出流
        byteArrayOutputStream.writeTo(outputstream);					//写到输出流
        byteArrayOutputStream.close();									//关闭
        outputstream.flush();											//刷数据
    }
}

4.5 使用工具类做导出

    @RequestMapping(value = "/export/{month}", method = RequestMethod.GET)
    public void export(@PathVariable String month) throws Exception {
        //1.获取报表数据
        List<EmployeeReportResult> list = 
        userCompanyPersonalService.findByReport(companyId,month);

        //2.加载模板
        Resource resource = new ClassPathResource("excel-template/hr-demo.xlsx");
        FileInputStream fis = new FileInputStream(resource.getFile());

        //3.通过工具类完成下载
        new ExcelExportUtil(EmployeeReportResult.class,2,2).
                export(response,fis,list,month+"人事报表.xlsx");

注意:

导入导出时使用工具类时需要在对应实体类字段都要加上注解序号 @ExcelAttribute(sort = 0)

4.6 使用工具类做导入

 @RequestMapping(value="/user/import",method = RequestMethod.POST)
    public Result importUser(@RequestParam(name="file") MultipartFile file) throws Exception {

  List<User> list = new ExcelImportUtil(User.class).readExcel(file.getInputStream(), 1, 1);
        //3.批量保存用户
        userService.saveAll(list,companyId,companyName);

        return new Result(ResultCode.SUCCESS);

}

注意:

导入导出时使用工具类时需要在对应实体类字段都要加上注解序号 @ExcelAttribute(sort = 0)

5.百万数据的导入导出

百万数据报表概述

1. 概述

我们都知道Excel可以分为早期的Excel2003版本（使用POI的HSSF对象操作）和Excel2007版本（使用POI的XSSF 操作），两者对百万数据的支持如下：

Excel 2003：在POI中使用HSSF对象时，excel 2003最多只允许存储65536条数据，一般用来处理较少的数据量。这时对于百万级别数据，Excel肯定容纳不了。

Excel 2007：当POI升级到XSSF对象时，它可以直接支持excel2007以上版本，因为它采用ooxml格式。这时excel可以支持1048576条数据，单个sheet表就支持近百万条数据。但实际运行时还可能存在问题，原因是执行POI报表所产生的行对象，单元格对象，字体对象，他们都不会销毁，这就导致OOM的风险。

1. JDK性能监控工具介绍

没有性能监控工具一切推论都只能停留在理论阶段，我们可以使用Java的性能监控工具来监视程序的运行情况，包括CUP,垃圾回收，内存的分配和使用情况，这让程序的运行阶段变得更加可控，也可以用来证明我们的推测。这里我们使用JDK提供的性能工具Jvisualvm来监控程序运行。

1. 1. Jvisualvm概述

VisualVM 是Netbeans的proﬁle子项目，已在JDK6.0 update 7 中自带，能够监控线程，内存情况，查看方法的

CPU时间和内存中的对象，已被GC的对象，反向查看分配的堆栈

1. 1. Jvisualvm的位置

Jvisualvm位于JAVA_HOME/bin目录下，直接双击就可以打开该程序。如果只是监控本地的java进程，是不需要配置参数的，直接打开就能够进行监控。首先我们需要在本地打开一个Java程序，例如我打开员工微服务进程，这时在jvisualvm界面就可以看到与IDEA相关的Java进程了：

POI报表入门,excel,使用事件模型解析百万数据excel报表

1. 1. Jvisualvm的使用

Jvisualvm使用起来比较简单，双击点击当前运行的进程即可进入到程序的监控界面

POI报表入门,excel,使用事件模型解析百万数据excel报表

概述：可以看到进程的启动参数。

监视：左上：cpu利用率，gc状态的监控，右上：堆利用率，永久内存区的利用率，左下：类的监控，右下：线程的监控

线程：能够显示线程的名称和运行的状态，在调试多线程时必不可少，而且可以点进一个线程查看这个线程的详细运行情况

1. 解决方案分析

对于百万数据量的Excel导入导出，只讨论基于Excel2007的解决方法。在ApachePoi 官方提供了对操作大数据量的导入导出的工具和解决办法，操作Excel2007使用XSSF对象，可以分为三种模式：

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

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

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

POI报表入门,excel,使用事件模型解析百万数据excel报表

6.百万数据报表导出

1. 需求分析

使用Apache POI完成百万数据量的Excel报表导出

1. 解决方案

1. 1. 思路分析

基于XSSFWork导出Excel报表，是通过将所有单元格对象保存到内存中，当所有的Excel单元格全部创建完成之后一次性写入到Excel并导出。当百万数据级别的Excel导出时，随着表格的不断创建，内存中对象越来越多，直至内存溢出。Apache Poi提供了SXSSFWork对象，专门用于处理大数据量Excel报表导出。

1. 1. 原理分析

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

1. 代码实现

在原有代码的基础上替换之前的XSSFWorkbook，使用SXSSFWorkbook完成创建过程即可

注意:百万数据导出不支持使用模板的;

//1.构造数据
List<EmployeeReportResult> list =
userCompanyPersonalService.findByReport(companyId,month+"%");
//2.创建工作簿
SXSSFWorkbook workbook = new SXSSFWorkbook();
//3.构造sheet
String[] titles = {"编号", "姓名", "手机","最高学历", "国家地区", "护照号", "籍贯",
"生日", "属相","入职时间","离职类型","离职原因","离职时间"};

Sheet sheet = workbook.createSheet();

Row row = sheet.createRow(0);

AtomicInteger headersAi = new AtomicInteger();

for (String title : titles) {
Cell cell = row.createCell(headersAi.getAndIncrement()); cell.setCellValue(title);
}

AtomicInteger datasAi = new AtomicInteger(1);
Cell cell = null;
for(int i=0;i<10000;i++) {
for (EmployeeReportResult report : list) {
Row dataRow = sheet.createRow(datasAi.getAndIncrement());
//编号
cell = dataRow.createCell(0);
cell.setCellValue(report.getUserId());
//姓名
cell = dataRow.createCell(1);
cell.setCellValue(report.getUsername());
//手机
cell = dataRow.createCell(2);
cell.setCellValue(report.getMobile());
//最高学历
cell = dataRow.createCell(3);
cell.setCellValue(report.getTheHighestDegreeOfEducation());
//国家地区
cell = dataRow.createCell(4);
cell.setCellValue(report.getNationalArea());
//护照号
cell = dataRow.createCell(5);
cell.setCellValue(report.getPassportNo());
//籍贯
cell = dataRow.createCell(6);
cell.setCellValue(report.getNativePlace());
//生日
cell = dataRow.createCell(7);
cell.setCellValue(report.getBirthday());
//属相
cell = dataRow.createCell(8);
cell.setCellValue(report.getZodiac());
//入职时间
cell = dataRow.createCell(9);
cell.setCellValue(report.getTimeOfEntry());
//离职类型
cell = dataRow.createCell(10);
cell.setCellValue(report.getTypeOfTurnover());
//离职原因
cell = dataRow.createCell(11);
cell.setCellValue(report.getReasonsForLeaving());
//离职时间
cell = dataRow.createCell(12); cell.setCellValue(report.getResignationTime());
}
}
String fileName = URLEncoder.encode(month+"人员信息.xlsx", "UTF-8"); response.setContentType("application/octet-stream"); response.setHeader("content-disposition", "attachment;filename=" + new
String(fileName.getBytes("ISO8859-1"))); response.setHeader("filename", fileName); workbook.write(response.getOutputStream());

1. 对比测试
2. XSSFWorkbook生成百万数据报表
3. 使用XSSFWorkbook生成Excel报表，时间较长，随着时间推移，内存占用原来越多，直至内存溢出

SXSSFWorkbook生成百万数据报表

使用SXSSFWorkbook生成Excel报表，内存占用比较平缓\

POI报表入门,excel,使用事件模型解析百万数据excel报表

7.百万数据报表读取

1. 需求分析

使用POI基于事件模式解析案例提供的Excel文件

1. 解决方案

1. 1. 思路分析

用户模式：加载并读取Excel时，是通过一次性的将所有数据加载到内存中再去解析每个单元格内容。当Excel 数据量较大时，由于不同的运行环境可能会造成内存不足甚至OOM异常。

事件模式：它逐行扫描文档，一边扫描一边解析。由于应用程序只是在读取数据时检查数据，因此不需要将数据存储在内存中，这对于大型文档的解析是个巨大优势。

1. 1. 步骤分析

（1）设置POI的事件模式根据Excel获取文件流

根据文件流创建OPCPackage

创建XSSFReader对象

（2）Sax解析

自定义Sheet处理器

创建Sax的XmlReader对象设置Sheet的事件处理器逐行读取

1. 1. 原理分析

我们都知道对于Excel2007的实质是一种特殊的XML存储数据，那就可以使用基于SAX的方式解析XML完成Excel的读取。SAX提供了一种从XML文档中读取数据的机制。它逐行扫描文档，一边扫描一边解析。由于应用程序只是在读取数据时检查数据，因此不需要将数据存储在内存中，这对于大型文档的解析是个巨大优势

POI报表入门,excel,使用事件模型解析百万数据excel报表

1. 代码实现

自定义处理器

//自定义Sheet基于Sax的解析处理器
public class SheetHandler implements XSSFSheetXMLHandler.SheetContentsHandler {

//封装实体对象
private PoiEntity entity;

/**
* 解析行开始
*/
@Override
public void startRow(int rowNum) { if (rowNum >0 ) {
entity = new PoiEntity();
}
}

/**
* 解析每一个单元格
*/
@Override
public void cell(String cellReference, String formattedValue, XSSFComment comment)
{
if(entity != null) {
switch (cellReference.substring(0, 1)) { case "A":
entity.setId(formattedValue); break;
case "B":
entity.setBreast(formattedValue); break;
case "C":
entity.setAdipocytes(formattedValue); break;
case "D":
entity.setNegative(formattedValue); break;
case "E":
entity.setStaining(formattedValue); break;
case "F":
entity.setSupportive(formattedValue); break;
default:
break;
}
}
}

/**
* 解析行结束
*/
public void endRow(int rowNum) { System.out.println(entity);
}

//处理头尾
public void headerFooter(String text, boolean isHeader, String tagName) {
}
}

自定义解析

/**
* 自定义Excel解析器
*/
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();
//5.创建Sax的XmlReader对象
XMLReader parser = XMLReaderFactory.createXMLReader();
//6.设置处理器
parser.setContentHandler(new XSSFSheetXMLHandler(styles,sst, new SheetHandler(), false));
XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator)
reader.getSheetsData();
//7.逐行读取
while (sheets.hasNext()) {
InputStream sheetstream = sheets.next();
InputSource sheetSource = new InputSource(sheetstream); try {
parser.parse(sheetSource);
} finally {
sheetstream.close();
}
}
} finally {
pkg.close();
}
}
}

1. 1. 对比测试
  2. 用户模式下读取测试Excel文件直接内存溢出，测试Excel文件映射到内存中还是占用了不少内存；事件模式下可以流畅的运行。
    
    （1）使用用户模型解析
  3. （2）使用事件模型解析

1. 总结

通过简单的分析以及运行两种模式进行比较，可以看到用户模式下使用更简单的代码实现了Excel读取，但是在读取大文件时CPU和内存都不理想；而事件模式虽然代码写起来比较繁琐，但是在读取大文件时CPU和内存更加占优。

POI报表入门,excel,使用事件模型解析百万数据excel报表

POI报表入门,excel

补充:

1.导入

2.导出

3.模板打印

[ 通过预先做好的模板来导出,导出的报表样式与模板一致 ]

4.封装好的工具类

5.百万数据的导入导出

6.百万数据报表导出

7.百万数据报表读取

相关推荐