【JAVA】excel导入

最新推荐文章于 2024-04-26 11:49:04 发布

ielgnod

最新推荐文章于 2024-04-26 11:49:04 发布

阅读量191

点赞数

分类专栏： JAVA

本文链接：https://blog.csdn.net/qq_39938236/article/details/103317436

版权

JAVA 专栏收录该内容

14 篇文章 1 订阅

订阅专栏

import com.comtop.eic.apache.poi.openxml4j.exceptions.InvalidFormatException;
import com.comtop.eic.apache.poi.ss.usermodel.*;
import com.comtop.eic.apache.poi.ss.util.CellRangeAddress;

1、引入依赖，然后读取文件

public void importExcel(MultipartFile file) {
        Workbook wb = null;
        File toFile = null;
        if (file.toString().equals("") || file.getSize() <= 0) {
            throw new BusinessException("文件为空");
        } else {
            InputStream ins = null;
            try {
                ins = file.getInputStream();
                toFile = new File(file.getOriginalFilename());
                OutputStream os = new FileOutputStream(toFile);
                byte[] buffer = new byte[8192];
                int bytesRead = 0;
                while ((bytesRead = ins.read(buffer, 0, 8192)) != -1) {
                    os.write(buffer, 0, bytesRead);
                }
                os.close();
                ins.close();
                wb = WorkbookFactory.create(toFile);
                // 读取excel转化成dto
                log.info("导入，读取excel开始，" + new Date().toString());
                Dto result = readExcel(wb, 0, 3, 0);
                log.info("导入，读取excel结束，" + new Date().toString());
                // 删除excel
                deleteExcel(toFile);
                // 格式化dto数据
               Dto dto = formatDto(result);
                // 保存到数据库
                log.info("导入，保存开始，" + new Date().toString());
                standardSave(formatDto);
                log.info("导入，保存结束，" + new Date().toString());

            } catch (IOException | InvalidFormatException e) {
                throw new BusinessException("读取文件出错");
            }
        }
    }

2、获取sheet页

Sheet sheet = wb.getSheetAt(sheetIndex);

3、获取一行，行数从0开始

Row row = null;
row = sheet.getRow(0);

4、获取一行中第几个单元格，从0开始

String standardName = row.getCell(2).getStringCellValue();

每个单元格有不同的属性，获取单个单元格中的内容，转为string

   举例： String demo = getCellStringValue(row, 2);
    
    private String getCellStringValue(Row row, int column) {
        String value = "";
        Cell cell = row.getCell(column);
        if (cell != null) {
            //CellType: NUMERIC 0,STRING 1,FORMULA 2,BLANK 3,BOOLEAN 4,ERROR 5
            if (cell.getCellType() == 0) {
                double numericCellValue = cell.getNumericCellValue();
                value = Double.toString(numericCellValue);
                if (value.endsWith(".0")) {
                    value = value.substring(0, value.length() - 2);
                }
            } else if (cell.getCellType() == 1) {
                value = cell.getStringCellValue();
            } else if (cell.getCellType() == 3) {
                value = "";
            }
        }
        return value;
    }

获取合并单元格中的内容（待更新：bug，如果为单个单元格，即使有内容也返回空）

private String getMergedRowValue(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        String value = "";
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    try {
                        value = sheet.getRow(firstRow).getCell(firstColumn).getRichStringCellValue().getString();
                    } catch (Exception e) {

                    }
                    try {
                        value = String.valueOf(sheet.getRow(firstRow).getCell(firstColumn).getNumericCellValue());
                    } catch (Exception e) {

                    }
                }
            }
        }
        return value;
    }