POI使用详细版

最新推荐文章于 2024-03-11 07:15:00 发布

会跑的滑板鞋

最新推荐文章于 2024-03-11 07:15:00 发布

阅读量620

点赞数

本文链接：https://blog.csdn.net/m0_64887997/article/details/131755916

版权

1.POI依赖

   <!--  POI依赖  -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>

2.POI导出

2.1实体类

package com.douluo.springbootfarmwork.domain;

import lombok.Data;

import java.util.Date;
@Data
public class Student {
    String id;
    String name;
    String age;
    String sex;
    Date birth;
    String phone;
}

2.2导出poi语法

package com.douluo.springbootfarmwork.service.Imp;

import com.douluo.springbootfarmwork.dao.StudentDao;
import com.douluo.springbootfarmwork.domain.Student;
import com.douluo.springbootfarmwork.service.ExportExcelService;
import lombok.extern.slf4j.Slf4j;
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.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Service
@Slf4j
public class ExportExcelImpl implements ExportExcelService{
    @Autowired
    private StudentDao studentDao;
    /**
     * poi导出文件
     * @return
     */
    @Override
    public Map<String, Object> exportExcel() {
        HSSFWorkbook sheets = new HSSFWorkbook();
        //创建 sheetname页名
        HSSFSheet sheet = sheets.createSheet("导出信息");
        sheet.setColumnWidth(3,20*256);//给第3列设置为20个字的宽度
        sheet.setColumnWidth(4,20*256);//给第4列设置为20个字的宽度
        //创建一行,下标从0开始
        HSSFRow row;
        row = sheet.createRow(0);
        //创建这行中的列,下标从0开始 （表头）
        HSSFCell cell = row.createCell(0);
        // 给cell 0下表赋值
        cell.setCellValue("姓名");
        //创建这行中的列,并给该列直接赋值
        row.createCell(1).setCellValue("年龄");
        row.createCell(2).setCellValue("性别");
        row.createCell(3).setCellValue("生日");
        row.createCell(4).setCellValue("手机号");
        List<Student> students = studentDao.queryByList();
        for (int i = 0; i < students.size(); i++) {
            Student student = students.get(i);
            row = sheet.createRow(i + 1);
            row.createCell(0).setCellValue(student.getName());
            row.createCell(1).setCellValue(student.getAge());
            row.createCell(2).setCellValue(student.getSex());
            Date birth = student.getBirth();
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String format = dateFormat.format(birth);
            row.createCell(3).setCellValue(format);
            row.createCell(4).setCellValue(student.getPhone());
        }
        Date date = new Date();
        String path ="D:\\JAVA\\web\\export\\" + date.getTime() + "导出信息.xlsx";
        File file = new File(path);
        try (FileOutputStream fileOutputStream = new FileOutputStream(file)){
            sheets.write(fileOutputStream);
        } catch (Exception e) {
           log.error("导出信息失败:{}",e.getMessage());
        }
       Map<String, Object> map = new HashMap<>();
        map.put("path",path);
        return map;
    }
}

3.POI导入

3.1解决导入id的问题 UUID生成不重复的主键

package com.douluo.springbootfarmwork.Util;
import org.springframework.stereotype.Component;

import java.util.UUID;

public  class  UuidUtil {
    public UuidUtil(String[] chars) {
        UuidUtil.chars = chars;
    }

    public static String[] chars = new String[] { "a", "b", "c", "d", "e", "f",
                "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", "r", "s",
                "t", "u", "v", "w", "x", "y", "z", "0", "1", "2", "3", "4", "5",
                "6", "7", "8", "9", "A", "B", "C", "D", "E", "F", "G", "H", "I",
                "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V",
                "W", "X", "Y", "Z" };
        public static String getShortUuid() {
            StringBuffer shortBuffer = new StringBuffer();
            String uuid = UUID.randomUUID().toString().replace("-", "");
            for (int i = 0; i < 8; i++) {
                String str = uuid.substring(i * 4, i * 4 + 4);
                int x = Integer.parseInt(str, 16);
                shortBuffer.append(chars[x % 0x3E]);
            }
            return shortBuffer.toString();
        }
}

3.2使用文件流参数接收

@PostMapping("/import")
    public ResponseResult importStudents(@RequestParam("file") MultipartFile file) {
        return ResponseResult.okResult(importExcelService.importExcel(file));
    }

如果有其他参数也是使用@RequestParam进行参数的接收

3.2使用poi完成导入

3.2.1检查传入的文件流

 public static void checkFile(MultipartFile file) throws IOException {
        //判断文件是否存在
        if (null == file) {
            log.error("文件不存在！");
            throw new FileNotFoundException("文件不存在！");
        }
        //获得文件名
        String fileName = file.getOriginalFilename();
        if (StringUtils.isEmpty(fileName)) {
            log.error("文件名为空");
            throw new NullPointerException("文件名为空!");
        }
        //判断文件是否是excel文件
        if (!fileName.endsWith("xls") && !fileName.endsWith("xlsx")) {
            log.error(fileName + "不是excel文件");
            throw new IOException(fileName + "不是excel文件");
        }

        //文件格式校验
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xLsx)")) {
            throw new IOException(fileName + "不是excel文件");
        }
    }
3.2.2将传入的文件流解析成wookbook
public static Workbook getWorkBook(MultipartFile file) {
        //获得文件名
        String fileName = file.getOriginalFilename();
        if (StringUtils.isEmpty(fileName)) {
            log.error("文件名为空");
            throw new NullPointerException("文件名为空!");
        }
        //创建Workbook工作薄对象，表示整个excel
        Workbook workbook = null;
        try {
            //获取excel文件的io流
            InputStream is = file.getInputStream();
            //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
            if (fileName.endsWith("xls")) {
                //2003
                workbook = new HSSFWorkbook(is);
            } else if (fileName.endsWith("xlsx")) {
                //2007
                workbook = new HSSFWorkbook(is);
            }
        } catch (IOException e) {
            log.info(e.getMessage());
        }
        return workbook;
    }

3.2.3转换cell中的值

public static String getCellValue(Cell cell) {
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
        //把数字当成String来读，避免出现1读成1.0的情况
        if (cell.getCellTypeEnum() == NUMERIC) {
            cell.setCellType(CellType.STRING);
        }
        //判断数据的类型
        switch (cell.getCellTypeEnum()) {
            case NUMERIC: //数字
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            case STRING: //字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case BOOLEAN: //Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA: //公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case BLANK: //空值
                cellValue = "";
                break;
            case ERROR: //故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }

3.2.4业务代码

 /**
     * 导入excel文件
     *
     * @return
     */
    @Override
    public Map<String, Object> importExcel(MultipartFile file) {
        try {
            checkFile(file);
        } catch (IOException e) {
            log.error("文件导入报错:{}", e.getMessage());
        }
        Workbook workBook = getWorkBook(file);
        //定义容器

        List<Student> students = new ArrayList<>();
        if (workBook != null) {
            for (int i = 0; i < workBook.getNumberOfSheets(); i++) {
                //获得当前页对象
                Sheet sheetAt = workBook.getSheetAt(i);

                //如果当前页是空页直接进行熊一页
                if (sheetAt == null) {
                    continue;
                }
                //获得当前页首行
                int firstRowNum = sheetAt.getFirstRowNum();
                //获得当前页所有行数
                int lastRowNum = sheetAt.getLastRowNum();
                //循环除首行的别的行
                for (int rowNum = firstRowNum; rowNum < lastRowNum; rowNum++) {
                    //获取表中的行
                    Row row = sheetAt.getRow(rowNum);
                    //如果当前行为空继续
                    if (row == null) {
                        continue;
                    }
                    //获取当前行的开始列跟结束页
                    int firstCellNum = row.getFirstCellNum();
                    int lastCellNum = row.getPhysicalNumberOfCells();
                    Student student = new Student();
                    for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
                        Cell cell = row.getCell(cellNum);
                        String cellValue = getCellValue(cell);
                        switch (cellNum) {
                            case 0:
                                student.setName(cellValue);
                                break;
                            case 1:
                                student.setAge(cellValue);
                                break;
                            case 2:
                                student.setSex(cellValue);
                                break;
                            case 3:
                                try {
                                    student.setBirth(dateFormat.parse(cellValue));
                                } catch (ParseException e) {
                                    log.error("时间转换异常!");
                                }
                                break;
                            case 4:
                                student.setPhone(cellValue);
                                break;
                            default:
                        }

                    }

                    student.setId(UuidUtil.getShortUuid());
                    students.add(student);
                }

            }
        }
        Integer rows = null;
        try {
            rows = studentDao.importStudents(students);
        } catch (Exception e) {
            log.error(e.getMessage());
        }
        String msg = "导入数据成功,数据更新行数:" + rows;
        Map<String, Object> resultMap = new HashMap<>();
        resultMap.put("msg", msg);
        resultMap.put("rows", rows);
        return resultMap;
    }