POI读取EXCEL文件

POI读取EXCEL文件
强烈推介IDEA2021.1.3破解激活,IntelliJ IDEA 注册码,2021.1.3IDEA 激活码  

大家好,我是架构君,一个会写代码吟诗的架构师。今天说一说POI读取EXCEL文件,希望能够帮助大家进步!!!

  • pom
    <properties>
        <poi.version>3.17</poi.version>
        <poi-ooxml.version>3.17</poi-ooxml.version>
        <poi-ooxml-schemas.version>3.17</poi-ooxml-schemas.version>
        <xmlbeans.version>2.6.0</xmlbeans.version>
        <poi-scratchpad.version>4.1.2</poi-scratchpad.version>
    </properties>


 <dependencies>
<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>${poi.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>${poi-ooxml.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>${poi-ooxml-schemas.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.xmlbeans</groupId>
            <artifactId>xmlbeans</artifactId>
            <version>${xmlbeans.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-scratchpad</artifactId>
            <version>${poi-scratchpad.version}</version>
        </dependency>
 </dependencies>
  • 读取EXCEL
package com.github.pig.admin.controller.area;


import com.github.pig.admin.service.area.HazardousAreaService;
import com.github.pig.admin.util.ExcelUtil;
import com.github.pig.common.util.R;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.AllArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
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.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;

@RequestMapping("hazardousArea")
@Api(value = "hazardousArea", tags = "危险区域数据")
@AllArgsConstructor
@RestController
@Slf4j
public class HazardousAreaController {

    private final HazardousAreaService hazardousAreaService;

    @PostMapping("/import")
    @ApiOperation(value = "危险区域数据导入", notes = "危险区域数据导入")
    public R Import() {
        File file = new File("D:\\fzy\\temp\\HazardousArea.xlsx"); //实际这个路径由前端传后台
        FileInputStream fis = null;
        try {
            fis = new FileInputStream(file);
        } catch (FileNotFoundException e) {
            log.error("读取文件异常:"+e.getMessage(),e);
            return new R(false);
        }

        Workbook wb = null;
        try {
            if(ExcelUtil.isExcel2003(file.getPath())){
                System.out.println("2003版本Excel: .xls结尾");
                wb = new HSSFWorkbook(fis); //创建2003版本Excel工作簿对象
            }else if (ExcelUtil.isExcel2007(file.getPath())){
                System.out.println("2007版本Excel: .xlsx结尾");
                wb = new XSSFWorkbook(fis); //创建2007版本Excel工作簿对象
            }else {
                System.out.println("未知版本的Excel !!!");
            }
            Sheet sheet= wb.getSheetAt(0);

            for(int i=1;i<=sheet.getLastRowNum();i++){//循环Excel文件的i=1行开始
//                User user = new User();
                Row row = sheet.getRow(i); //获取第i行
                Cell cell1 = row.getCell(0); //获取第1个单元格的数据
//                cell1.setCellType(Cell.CELL_TYPE_STRING); //设置Cell的类型为String类型
//                Cell cell2 = row.getCell(1); //获取第2个单元格的数据
//                cell2.setCellType(Cell.CELL_TYPE_STRING);
//                user.setName(cell1.getStringCellValue());
//                user.setEmail(cell2.getStringCellValue());
                System.out.println("第一单元格: " + cell1.getStringCellValue());
//                System.out.println("第二单元格: " + cell2.getStringCellValue());
//                userRepository.save(user); //保存
            }
        } catch (Exception e) {
            log.error("区域数据持久化异常:"+e.getMessage(),e);
        }finally {
            try {
                fis.close();
            } catch (IOException e) {
                log.error("EXCEL文件流关闭异常:"+e.getMessage(),e);
            }
        }
        return new R(true);
    }
}

 

 

本文来源huayang183,由架构君转载发布,观点不代表Java架构师必看的立场,转载请标明来源出处:https://javajgs.com/archives/18025

发表评论