java poi的基本使用

 一、pom依赖

poi针对旧版本的文件，poi-ooxml针对新版本的文件

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

二、java实现

package cn.pkulaw.fblx.util.poi;

import cn.pkulaw.fblx.model.domain.ExcelPaichong;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.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 org.springframework.web.multipart.MultipartFile;

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

/**
 * @Author: WXM
 * @Description:
 * @Date: create in 2021/1/28 14:59
 */

public class ExcelUtils {

    //根据表头创建excel文档
    public static HSSFWorkbook getExcelWithTitle(String[] title){
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("Sheet1");
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell;
        for(int i=0;i<title.length;i++){
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
        }
        return workbook;
    }

    //把上传文件转化为excel
    public static Workbook getExcelFromFile(MultipartFile file){
        String fileName = file.getOriginalFilename();
        String EXCEL2003 = "xls";
        String EXCEL2007 = "xlsx";
        Workbook workbook = null;
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            throw new RuntimeException("传入文件格式错误");
        } else {
            try {
                InputStream is = file.getInputStream();
                if (fileName.endsWith(EXCEL2007)) {
                    workbook = new XSSFWorkbook(is);
                }
                if (fileName.endsWith(EXCEL2003)) {
                    workbook = new HSSFWorkbook(is);
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return workbook;
    }

    //读取导入数据
    public static List<ExcelPaichong> excelToObjectList(Workbook workbook ,String sheetName){
        List<ExcelPaichong> excelPaichongs = new ArrayList<>();
        boolean isFirstRow = true;
        if(null != workbook){
            Sheet sheet = workbook.getSheet(sheetName);
            if(null!=sheet){
                Iterator<Row> rowIterator = sheet.rowIterator();
                while (rowIterator.hasNext()){
                    //将第一行标题行跳过
                    if(isFirstRow){
                        isFirstRow = false;
                        continue;
                    }
                    ExcelPaichong ep = new ExcelPaichong();
                    Row row = rowIterator.next();
                    //只有行有编号才是有效行
                    if(null!=row && null!=row.getCell(0)){
                        ep.setId(null==row.getCell(0)?null:(int) row.getCell(0).getNumericCellValue());
                        ep.setZsName(null==row.getCell(2)?null:row.getCell(2).getStringCellValue());
                        ep.setFwzh(null==row.getCell(3)?null:row.getCell(3).getStringCellValue());
                        ep.setLib(null==row.getCell(8)?null:row.getCell(8).getStringCellValue());
                        ep.setPplx(null==row.getCell(9)?null:row.getCell(9).getStringCellValue());
                        excelPaichongs.add(ep);
                    }
                }
            }
        }
        return excelPaichongs;
    }

//指定将集合加载到excel中
    public HSSFWorkbook getExcelFromObject(List<ExcelPaichong> excelPaichongs){
        //获取有标题列的excel对象
        String[] title = {"编号","Gid","标题","发文字号","发布部门","发布时间","数据状态","后台","库别","匹配类型"};
        HSSFWorkbook workBook = ExcelUtils.getExcelWithTitle(title);
        HSSFSheet sheet1 = workBook.getSheet("Sheet1");

        if(null!=excelPaichongs){
            for(int i=0;i<excelPaichongs.size();i++){
                HSSFRow row = sheet1.createRow(i + 1);
                row.createCell(0).setCellValue(excelPaichongs.get(i).getId());
                row.createCell(1).setCellValue(excelPaichongs.get(i).getGid());
                row.createCell(2).setCellValue(excelPaichongs.get(i).getZsName());
                row.createCell(3).setCellValue(excelPaichongs.get(i).getFwzh());
                row.createCell(4).setCellValue(excelPaichongs.get(i).getFbbm());
                row.createCell(5).setCellValue(excelPaichongs.get(i).getFdate());
                row.createCell(6).setCellValue(excelPaichongs.get(i).getZsType());
                row.createCell(7).setCellValue(excelPaichongs.get(i).getDataFrom());
                row.createCell(8).setCellValue(excelPaichongs.get(i).getLib());
                row.createCell(9).setCellValue(excelPaichongs.get(i).getPplx());
            }
        }
        return workBook;
    }
}