java项目导入excel

java项目导入excel

采取流文件处理方案，如果需要可以采用EasyExcel工具包

依赖件

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

处理对象

MultipartFile sourceFile

处理方案

1.转储文件流，关闭文件(可以直接使用用file，参考XSSFWorkbook类注释)

file = File.createTempFile("temp", null);
sourceFile.transferTo(file);   //sourceFile为传入的MultipartFile
inputStream = new FileInputStream(file);
file.deleteOnExit();
//处理数据{...}
inputStream.close();

2.提取参数

XSSFWorkbook wb = new XSSFWorkbook(inputStream);
XSSFSheet sheet = wb.getSheetAt(page); //page为页码
int rowNum = sheet.getLastRowNum(); //当前页最大行数
short cellNum = sheet.getRow("此处填页码：int").getLastCellNum(); //当前页最大列
XSSFRow row = sheet.getRow("此处填行数：int"); //该行的所有列
XSSFCell cell = row.getCell("此处填列数：int"); //所在行和列交点的值

3.其他

1.XSSFCell.getCellType() == CellType.BLANK可以判断是否为空
2.StringBuffer可以直接读取XSSFCell
3.使用String接收，需要定义类型cell.setCellType(CellType.STRING);

4.示例：全表遍历输出

public List excelImport(Integer page, MultipartFile sourceFile) throws IOException {
        FileInputStream inputStream = null;
        File file = null;
        file = File.createTempFile("temp", null);
        sourceFile.transferTo(file);   //sourceFile为传入的MultipartFile
        inputStream = new FileInputStream(file);
        file.deleteOnExit();
        XSSFWorkbook wb = new XSSFWorkbook(inputStream);
        XSSFSheet sheet = wb.getSheetAt(page);
        int rowNum = sheet.getLastRowNum();
        short cellNum = sheet.getRow(1).getLastCellNum();
        List list = new ArrayList();
        for (int i = 0; i < rowNum; i++) {
            XSSFRow row = sheet.getRow(i);//行
            StringBuffer sb = new StringBuffer();
            for (int j = 0; j < cellNum; j++) {
                XSSFCell cell = row.getCell(j);//列
                sb.append("'"+cell+"',");
            }
            if (sb.length() > 0) {
                sb.deleteCharAt(sb.length()-1);
            }
            list.add(sb.toString());
        }
        inputStream.close();
        return list;
    }