[Java知识库]POI技术实现文件的导入导出

一、POI简介：
Apache POI的定义是Poor Obfuscation Implementation”首字母的缩写，即“简单的模糊实现”。

二、用途：
使用java解析和创建office文档的工具. 常用于处理excel文件等。

三、常用API：
1.获得文档对象（工作簿）：(03版最多65536行数据，07版最多1048576行，16384列数据 )
03版的exile： Workbook workbook= new HSSFWorkbook(fs);
07版的exile： Workbook workbook= new XSSFWorkbook(fs);
大数据量exile对象：Workbook workbook= new SXSSFWorkbook();

2.获取工作表单对象：
Sheet sheet = workbook.createSheet(“工作表名”)

3.设置列宽度：
sheet.setColumnWidth(0, 5 * 256);
参数一：列的索引，从0开始
参数二：列的宽度，这里设置的不是每列的 是所有列的总宽度

4.获取行对象：
Row row = sheet.createRow(索引)；

5.获取有效行：
int rowcount = sheet.getLastRowNum();

6.取得一行的有效单元格个数：
row.getLastCellNum();

7.设置行样式：
row.setRowStyle(CellStyle);

8.设置行高：
row.setHeightInPoints(folat f);
row.setHeight(short s);

9.获取单元格对象：
Cell cell = row.createCell(索引)
Cell cell = row.createCell(索引，表格类型CellType)
CellType是一个枚举取值如下：

NONE(-1),//未知类型
NUMERIC(0),//数值类型（整数，小数，日期）
STRING(1),//文本类型
FORMULA(2),//公式
BLANK(3),//空值
BOOLEAN(4),//布尔值
ERROR(5);//错误单元格

10.操作单元格
给单元格赋值：cell.setCellValue
设置单元格格式：cell.setCellStyle(this.title(workbook));参数为：CellStyle，单元格样式
获取单元格内容：
数值类型：cell.getNumericCellValue();
文本类型：cell.getStringCellValue();
布尔类型：cell.getBooleanCellValue()
日期类型：cell.getDateCellValue()
错误单元格类型：cell.getErrorCellValue()
单元格公式：cell.getCellFormula()

11.保存Excel文件：
需要通过流写到页面，在通过浏览器解析下载
FileOutputStream fileOut = new FileOutputStream(path);
workbook.write(fileOut);

四、代码实现文件导入流程：
1.导入相关依赖：

2.编写yml配置文件：（特别提请：使用mysql8.0需要在url上指定时区）

server:
  port: 8888
  servlet:
    path: /testOne
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/test?serverTimezone=UTC
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: root
    password: 123456
  servlet:
    multipart:
      max-file-size: 10MB
      max-request-size: 100MB

3.创建实体：

4.编写链接数据库访问层：

@Mapper
public interface PeopleMessagesDao{
    //导入数据
    @Insert("insert into infisa_people_messages values" +
            "(default,#{name},#{sex},#{age},#{birhDate},#{address},#{remark})")
    int addAll(PeopleMessages p);

    //查询要导出的数据
    @Select("select id,name,sex,age,birth_date as birhDate,address,remark " +
            "from infisa_people_messages ")
    List<PeopleMessages> findAll();

}

5.编写业务层接口及实现：

@Service
public class IPeopleMessagesImpl implements PeopleMessagesService {
    private Logger logger= LoggerFactory.getLogger(this.getClass());
    @Autowired
    private PeopleMessagesDao peopleMessagesDao;
    
    /**
     * 单元测试方法
     * @param file
     * @return
     */
    @Override
    @Transactional
    public int addAll(File file) {
        int i =0;
        Iterator<Row> rowNum = POIUtils.getRowNum(file);
        List<PeopleMessages> peopleMessages = POIUtils.analysisRow(rowNum);
        for (PeopleMessages p:peopleMessages) {
            if (p!=null){
                i = peopleMessagesDao.addAll(p);
            }
        }
        return i;
    }

	 /**
     * 接口测试方法
     * @param file
     * @return
     */
    @Override
    @Transactional
    public int addAll(MultipartFile file) {
        int i =0;
        Iterator<Row> rowNum = POIUtils.getRowNum(file);
        List<PeopleMessages> peopleMessages = POIUtils.analysisRow(rowNum);
        for (PeopleMessages p:peopleMessages) {
            try {
                if (p!=null){
                    i = peopleMessagesDao.addAll(p);
                }else {
                    logger.info("列转行对象为空，解析excel文件失败！");
                }
            } catch (Exception e) {
                logger.error("报错异常为："+e.getMessage());
            }
        }
        return i;
    }
}

POIUtils工具类的方法：

public class POIUtils {

    private static Logger logger=LoggerFactory.getLogger(POIUtils.class);

    /**
     * 获得所有行
     * @param file
     * @return
     */
    public static Iterator<Row> getRowNum(File file){
        InputStream in= null;
        Workbook workbook =null;
        Sheet sheet =null;
        Iterator<Row> rows=null;
        try {
            in = new FileInputStream(file);
            //创建WorkBook对象
            workbook = WorkbookFactory.create(in);
            //获得sheet表单
            sheet = workbook.getSheet("sheet1");
            //获得表单所有行数
            rows = sheet.rowIterator();
        } catch (Exception e) {
            logger.error(e.getMessage());
        }
        return rows;
    }
    /**
     * 获得有效行数
     * @param file
     * @return
     */
    public static Iterator<Row> getRowNum(MultipartFile file){
        InputStream in= null;
        Workbook workbook =null;
        Sheet sheet =null;
        Iterator<Row> rows=null;
        try {
            in = file.getInputStream();
            //创建WorkBook对象
            workbook = WorkbookFactory.create(in);
            //获得sheet表单
            sheet = workbook.getSheet("sheet1");
            //获得表单所有行数
            rows = sheet.rowIterator();
        } catch (Exception e) {
            logger.error(e.getMessage());
        }
        return rows;
    }
    /**
     *获取每行每个单元格的值
     * @param rowIterator
     * @return
     */
    public static List<PeopleMessages> analysisRow(Iterator<Row> rowIterator){
        List<PeopleMessages> list=new ArrayList<>();
        int i=0;
        while (rowIterator.hasNext()){
            Row row = rowIterator.next();
            if (i==0 || i==1){
                i++;
                continue;
            }
            PeopleMessages peopleMessages=new PeopleMessages();
            short cellNum = row.getLastCellNum();
            for (int j=0;j<cellNum;j++){
                Cell cell = row.getCell(j);
                String value = String.valueOf(cell);
                value = match(value, cell);
                peopleMessages = fill(j, value,peopleMessages);
            }
            list.add(peopleMessages);
        }
        return list;
    }

    /**
     * 匹配格式。
     * @param value
     * @param cell
     * @return
     */
    public static String match(String value,Cell cell){
        int cellType = cell.getCellType();
        if (StringUtils.isNotBlank(value) && cellType==0 && value.contains(".")){
            String[] split = StringUtils.split(value,".");
            value=split[0];
        }
        if (cell!=null && cellType==0 && ( value.contains("/") || value.contains("-") ) ){
            //则单元格格式是日期类型
            Date date = cell.getDateCellValue();
            SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
            value = sdf.format(date);
        }
        return value;
    }

    /**
     * 列转行。
     * @param i
     * @param value
     * @return
     */
    public static PeopleMessages fill(int i,String value,PeopleMessages peopleMessages){
        if (value!=null){
            switch (i){
                case 0:
                    peopleMessages.setName(value);
                    break;
                case 1:
                    peopleMessages.setSex(value);
                    break;
                case 2:
                    peopleMessages.setAge(value);
                    break;
                case 3:
                    peopleMessages.setBirhDate(value);
                    break;
                case 4:
                    peopleMessages.setAddress(value);
                    break;
                case 5:
                    peopleMessages.setRemark(value);
                    break;
            }
        }
        return peopleMessages;
    }

 
}

6. 创建测试类，进行单元测试

（或创建控制层对象，引入thymleaf插件的依赖，在templates下创建html进行接口测试，具体在附页）：

@RunWith(SpringRunner.class)
@SpringBootTest(classes = TestOneApplication.class)
public class TestExcelPOI {
    @Autowired
    private  PeopleMessagesService peopleMessagesService;
    
    @Test
    public void testOne(){
        int i = peopleMessagesService.addAll(new File
                ("E:\\PeopleMessages.xlsx"));
        if (i!=0){
            System.out.println("解析成功！");
        }
    }
    
	

（注：测试类要指定启动的测试容器和指定的启动类）
7.创建数据库表格存储解析excel文件的数据：

8.测试结果：

附：使用thymleaf插件，通过页面上传文件，进行接口测试：
导入thymleaf依赖，在resources下创建templates目录，在该目录下面创建html文件，如果用thymleaf，控制器就必会走视图解析器对视图解析，因此控制器类上不能用@RestController注解，

上传文件请求必须是post请求，必须指定enctype=“multipart/form-data”。

<!--模板引擎-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>
        <!--thymeleaf 不重启刷新页面 工具-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <!-- optional=true,依赖不会传递，该项目依赖devtools；
			之后依赖boot项目的项目如果想要使用devtools，需要重新引入 -->
            <!--            <optional>true</optional>-->
        </dependency>

<!DOCTYPE html>
<html lang="en"  xmlns="http://www.w3.org/1999/xhtml" xmlns:th="http://www.thymeleaf.org"
      xmlns:sec="http://www.thymeleaf.org/thymeleaf-extras-springsecurity3">
<head>
    <meta charset="UTF-8">
    <title>上传文件</title>
</head>
<body>
<form method="post" enctype="multipart/form-data" id="file_upload" action="/testOne/upload" >
    　　<p>
    　　　　<input type="file" id="test-image-file" name="f" >
    　　</p>
        <p>
            <input type="submit" value="上传">
        </p>
</form>
</body>
</html>

继续前四个步骤(省略)，5.只修改业务层实现类方法参数

/**
 * 接口测试方法
 * @param file
 * @return
 */
@Override
@Transactional
public int addAll(MultipartFile file) {
    int i =0;
    Iterator<Row> rowNum = POIUtils.getRowNum(file);
    List<PeopleMessages> peopleMessages = POIUtils.analysisRow(rowNum);
    for (PeopleMessages p:peopleMessages) {
        try {
            if (p!=null){
                i = peopleMessagesDao.addAll(p);
            }else {
                logger.info("列转行对象为空，解析excel文件失败！");
            }
        } catch (Exception e) {
            logger.error("报错异常为："+e.getMessage());
        }
    }
    return i;
}

6.编写控制层代码：

如果html文件中指定name属性，@RequestParm注解什么属性没有写，那么控制器参数必须保持一致（或者在注解中添加value属性，例如:@RequestParm（value=“其他参数名”））

@Controller
public class PeopleMessagesController {
    @Autowired
    private PeopleMessagesService peopleMessagesService;

    /**
     * 通过视图解析器，找到index.html页面。
     * @return
     */
    @RequestMapping("/goUpload")
    public String upload() {
        return "index";
    }

    @PostMapping("/upload")
    @ResponseBody
    public String addAll(@RequestParam MultipartFile f){
        int i = peopleMessagesService.addAll(f);
        if (i!=0){
            return "ok";
        }
        return "error";
    }
}

7.测试结果：页面返回ok,数据库导入数据成功。

nice！！
五、代码实现导出流程：
前四个步骤一样，只是在POIUtils工具类新增create()方法:

 public static Workbook create(List<PeopleMessages> list,String path){
        Workbook workbook=new HSSFWorkbook();
        Sheet sheet = workbook.createSheet("表单一");
        createHeader(sheet);
        //设置列值
        Class<? extends PeopleMessages> pClass = PeopleMessages.class;
        Field[] fields = pClass.getDeclaredFields();
        int length = fields.length;
        for (int i=0;i< list.size();i++){
            Row row2 = sheet.createRow(i + 2);
            for (int j=0;j<length;j++){
                Field field = fields[j];
                try {
                    field.setAccessible(true);
                    Cell cell = row2.createCell(j);
                    cell.setCellValue(field.get(list.get(i))+"");
                } catch (IllegalAccessException e) {
                    logger.error(e.getMessage());
                }
            }
        }
        return workbook;
    }

    public static void createHeader(Sheet sheet){
        Row row = sheet.createRow(0);
        row.createCell(0).setCellValue("人口信息表");
        //合并单元格
        CellRangeAddress region = new CellRangeAddress(0, 0, 0, 6);
        sheet.addMergedRegion(region);
        //设置列头
        Row row1 = sheet.createRow(1);
        row1.createCell(0).setCellValue("编号");
        row1.createCell(1).setCellValue("姓名");
        row1.createCell(2).setCellValue("性别");
        row1.createCell(3).setCellValue("年龄");
        row1.createCell(4).setCellValue("出生日期");
        row1.createCell(5).setCellValue("地址");
        row1.createCell(6).setCellValue("备注");
    }

5.业务层调用：

/**
     * 导出方法
     * @param path
     */
    @Override
    public void exportExcel(String path){
        List<PeopleMessages> list = peopleMessagesDao.findAll();
        Workbook workbook = POIUtils.create(list, path);
        File file=new File(path);
        try {
            OutputStream os=new FileOutputStream(file);
            workbook.write(os);
        } catch (Exception e) {
            logger.error(e.getMessage());
        }
    }

6.单元测试：

	@Test
    public void testOne1(){
        peopleMessagesService.exportExcel("E:\\test.xlsx");
    }

7.总结：经测试上述没问题。这次导出方法是直接在E盘生成一个xlsx文件，如果是浏览器下载那种方式，需要配置response的header和contentType属性。代码如下：

/**
     * 创建文件名为当前日期的文件。
     * @param wb
     */
    public void createFile(Workbook wb, HttpServletResponse response){
        Date date=new Date(System.currentTimeMillis());
        SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
        String currTime= sdf.format(date);
        OutputStream os=null;
        try {
            response.reset();
            response.setHeader("Access-Control-Allow-Origin","*");
            response.setHeader("Content-Disposition","attachment; filename=\""+currTime+".xlsx");
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=utf-8");
            os = response.getOutputStream();
            //os=new FileOutputStream(file);
            wb.write(os);
            os.flush();
        } catch (Exception e) {
            logger.info(e.getMessage());
        }finally {
            try {
                os.close();
            } catch (IOException e) {
                logger.info(e.getMessage());
            }
        }
    }

最后呢
POI技术还有好多API没有用到，像设置字体颜色，单元格类型，单元格居中等。待更新。。。