Cara menggunakan POI untuk mengimport dan mengeksport jadual Excel dalam Java-javaTutorial-php.cn

1. Java menggunakan POI untuk melaksanakan import dan eksport demo jadual Excel

1 Memperkenalkan kebergantungan

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

Salin selepas log masuk

2 🎜>

2.1 lapisan pengawal

/**
     * Excel导入 
     */
    @PostMapping("/import")
    public Result userImport2(@RequestParam("file") MultipartFile file) throws Exception{
        Result result=userService.userImportExcel(file);
        return result;
    }

Salin selepas log masuk

2.2 lapisan kelas pelaksanaan perkhidmatan

public Result userImportExcel(MultipartFile file){
    try {
        InputStream inputStream = file.getInputStream();
        XSSFWorkbook sheets = new XSSFWorkbook(inputStream);
        //获取表单sheet 第一个
        XSSFSheet sheetAt = sheets.getSheetAt(0);
        //获取第一行
        int firstRowNum = sheetAt.getFirstRowNum();
        //最后一行
        int lastRowNum = sheetAt.getLastRowNum();
        //存入数据集合
        List<User> users=new ArrayList<>();
        //遍历数据
        for(int i=firstRowNum+1;i<lastRowNum+1;i++){
            XSSFRow row = sheetAt.getRow(i);
            if(row!=null){
               /* //获取第一行的第一列
                int firstCellNum = row.getFirstCellNum();
                //获取第一行的最后列
                short lastCellNum = row.getLastCellNum();
                for (int j=firstCellNum;j<lastCellNum+1;j++){
                    //放入集合中需要可以用这种方法
                    String cellValue = getValue(row.getCell(firstCellNum));
                }*/
                //这里我就直接赋值
                User user = new User();
                user.setUname(row.getCell(0).getStringCellValue());
                user.setUpassword(row.getCell(1).getStringCellValue());
                user.setUsex(row.getCell(2).getStringCellValue());
                user.setRole(row.getCell(3).getStringCellValue());
                user.setUlove((int) row.getCell(4).getNumericCellValue());
                user.setUphoto(row.getCell(5).getStringCellValue());
                user.setUaddress(row.getCell(6).getStringCellValue());
                users.add(user);
            }
        }
        //保存数据
        saveBatch(users);
        return Result.success();
    }catch (Exception e){
        e.printStackTrace();
        log.info("error:{}",e);
    }

    return Result.error("300","导入失败");
}

/**
 * 判断值的类型
 */
public String getValue(HSSFCell cell) {

    if(cell==null){
        return "";
    }
    String cellValue= "";
    try {
        DecimalFormat df=new DecimalFormat("0.00");
        if(cell.getCellType()== CellType.NUMERIC){
            //日期时间转换
            if(HSSFDateUtil.isCellDateFormatted(cell)){
                cellValue=DateFormatUtils.format(cell.getDateCellValue(),"yyyy-MM-dd");
            }else{
                NumberFormat instance = NumberFormat.getInstance();
                cellValue=instance.format(cell.getNumericCellValue()).replace(",","");
            }

        }else if(cell.getCellType() == CellType.STRING){
            //字符串
            cellValue=cell.getStringCellValue();
        }else if(cell.getCellType() == CellType.BOOLEAN){
            //Boolean
            cellValue= String.valueOf(cell.getBooleanCellValue());
        }else if(cell.getCellType() == CellType.ERROR){
            //错误
        }else if(cell.getCellType() == CellType.FORMULA){
            //转换公式 保留两位
            cellValue=df.format(cell.getNumericCellValue());
        }else{
            cellValue=null;
        }

    } catch (Exception e) {
        e.printStackTrace();
        cellValue="-1";
    }

    return cellValue;
}

Salin selepas log masuk

3 3.1 lapisan pengawal

3.2 kelas pelaksanaan perkhidmatan

/**
 * 导出 
 * @param response
 * @return
 * @throws Exception
 */
@GetMapping("/export")
public Result userExport2(HttpServletResponse response) throws Exception{
    Result result=userService.userExportExcel(response);
    return result;
}

Salin selepas log masuk

2. Kaedah pengkapsulan kelas alat Hutool eksport dan import Excel

public Result userExportExcel(HttpServletResponse response) {
    try {
        //创建excel
        XSSFWorkbook sheets = new XSSFWorkbook();
        //创建行
        XSSFSheet sheet = sheets.createSheet("用户信息");
        //格式设置
        XSSFCellStyle cellStyle = sheets.createCellStyle();
        //横向居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //创建单元格第一列
        XSSFRow row = sheet.createRow(0);
        //表头
        this.titleExcel(row,cellStyle);
        //查询全部的用户数据  mybatis-plus
        List<User> list = list();
        //遍历设置值
        for(int i=0;i<list.size();i++){
            XSSFRow rows = sheet.createRow(i+1);
            User user=list.get(i);
            //表格里赋值
            this.titleExcelValue(user,rows,cellStyle);
        }
        //设置浏览器响应格式
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        String filName= URLEncoder.encode("用户信息","UTF-8");
        response.setHeader("Content-Disposition","attachment;filename="+filName+".xls");

        ServletOutputStream outputStream=response.getOutputStream();
        sheets.write(outputStream);
        outputStream.close();
        sheets.close();
        return Result.success();

    }catch (Exception e){
        e.printStackTrace();
        log.info("error:{}",e);
    }

    return Result.error("300","导出失败");
}

/**
*表格里赋值
**/
public void titleExcelValue(User user, XSSFRow row,XSSFCellStyle cellStyle) {
    XSSFCell cellId = row.createCell(0);
    cellId.setCellValue(user.getUid());
    cellId.setCellStyle(cellStyle);

    XSSFCell cellUserName = row.createCell(1);
    cellUserName.setCellValue(user.getUname());
    cellUserName.setCellStyle(cellStyle);

    XSSFCell cellPassword = row.createCell(2);
    cellPassword.setCellValue(user.getUpassword());
    cellPassword.setCellStyle(cellStyle);

    XSSFCell cellSex = row.createCell(3);
    cellSex.setCellValue(user.getUsex());
    cellSex.setCellStyle(cellStyle);

    XSSFCell cellRole = row.createCell(4);
    cellRole.setCellValue(user.getRole());
    cellRole.setCellStyle(cellStyle);

    XSSFCell cellLoveValue = row.createCell(5);
    cellLoveValue.setCellValue(user.getRole());
    cellLoveValue.setCellStyle(cellStyle);

    XSSFCell cellPhone = row.createCell(6);
    cellPhone.setCellValue(user.getUphoto());
    cellPhone.setCellStyle(cellStyle);

    XSSFCell cellAddress = row.createCell(7);
    cellAddress.setCellValue(user.getUaddress());
    cellAddress.setCellStyle(cellStyle);


}
/**
    表头
**/
public void titleExcel(XSSFRow row,XSSFCellStyle cellStyle){

    XSSFCell cellId = row.createCell(0);
    cellId.setCellValue("用户ID");
    cellId.setCellStyle(cellStyle);

    XSSFCell cellUserName = row.createCell(1);
    cellUserName.setCellValue("用户名");
    cellUserName.setCellStyle(cellStyle);

    XSSFCell cellPassword = row.createCell(2);
    cellPassword.setCellValue("密码");
    cellPassword.setCellStyle(cellStyle);

    XSSFCell cellSex = row.createCell(3);
    cellSex.setCellValue("性别");
    cellSex.setCellStyle(cellStyle);

    XSSFCell cellRole = row.createCell(4);
    cellRole.setCellValue("角色");
    cellRole.setCellStyle(cellStyle);

    XSSFCell cellLoveValue = row.createCell(5);
    cellLoveValue.setCellValue("爱心值");
    cellLoveValue.setCellStyle(cellStyle);

    XSSFCell cellPhone = row.createCell(6);
    cellPhone.setCellValue("电话号码");
    cellPhone.setCellStyle(cellStyle);

    XSSFCell cellAddress = row.createCell(7);
    cellAddress.setCellValue("地址");
    cellAddress.setCellStyle(cellStyle);

}

Salin selepas log masuk

1 🎜>

Merangkumkan poi ke dalam kaedah kelas alat

2. Demo import

<!-- hutool  -->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.7.20</version>
        </dependency>

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

Salin selepas log masuk

3 🎜>

/**
     * Excel导入 
*/
@PostMapping("/import")
public Result userImport(@RequestParam("file") MultipartFile file) throws Exception{
        System.out.println(file.toString());
        //InputStream inputStream = multipartFile.getInputStream();
        InputStream inputStream = file.getInputStream();
        ExcelReader reader = ExcelUtil.getReader(inputStream);
        //读取表的内容
        List<List<Object>> list = reader.read(1);
        List<User> users = new ArrayList<>();
        for(List<Object> row : list){
            User user = new User();
            user.setUname(row.get(0).toString());
            user.setUpassword(row.get(1).toString());
            user.setUsex(row.get(2).toString());
            user.setRole(row.get(3).toString());
            user.setUlove(Integer.valueOf(row.get(4).toString()));
            user.setUphoto(row.get(5).toString());
            user.setUaddress(row.get(6).toString());
            users.add(user);
        }
        //批量插入用户信息 mybatis-plus
        userService.saveBatch(users);
        return Result.success();
    }

Salin selepas log masuk

Atas ialah kandungan terperinci Cara menggunakan POI untuk mengimport dan mengeksport jadual Excel dalam Java. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!