Java Excel 相关操作

发表于 更新于 分类于

Excel 文件导入导出的相关操作

引入 poi 相关依赖

1
2
3
4
5
6
7
8
9
10
11
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.16</version>
</dependency>

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

解析 Excel

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
import org.apache.poi.xssf.usermodel.*;

@CrossOrigin
@RequestMapping(value = {"/parseExcel"}, method = RequestMethod.POST, produces = {"application/json;charset=UTF-8"})
@ResponseBody
public Result<Boolean> parseExcel(@RequestParam("file") MultipartFile file) {
        // @RequestParam("file")MultipartFile file 用来接受前端传过来的 Excel 文件
        // 创建 Workbook 对象，读取整个文档
        InputStream inputStream = file.getInputStream();
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
        // 读取页脚 sheet
        XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
        // 循环取每行的数据
        // rowIndex 为 1 是为了跳过标题行
        for (int rowIndex = 1; rowIndex < xssfSheet.getPhysicalNumberOfRows(); rowIndex++) {
            XSSFRow xssfRow = xssfSheet.getRow(rowIndex);
            if (xssfRow == null) {
                continue;
            }
            // 获取单元格数据
            Long id = ExcelUtils.getLong(xssfRow.getCell(0));
            String str = ExcelUtils.getString(xssfRow.getCell(1)));
            Integer num = ExcelUtils.getInteger(xssfRow.getCell(2));
            Boolean isFalse = ExcelUtils.getBoolean(xssfRow.getCell(3));
        }
}

导出 Excel

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
import org.apache.poi.xssf.usermodel.*;

@ApiOperation(value = "exportExcel")
@RequestMapping(value = {"/exportExcel"}, method = RequestMethod.GET)
@ResponseBody
public Result<Boolean> exportExcel(HttpServletResponse response) {
    // 设置要导出的文件的名字
    String fileName = "data"  + new Date() + ".xlsx";
    try {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("信息表");
        // headers表示excel表中第一行的表头 在excel表中添加表头
        String[] headers = { "id", "名称", "类型"};
        XSSFRow head = sheet.createRow(0);
        for(int i = 0; i < headers.length; i++){
            XSSFCell cell = head.createCell(i);
            XSSFRichTextString text = new XSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
        // 新增数据行，并且设置单元格数据
        XSSFRow row =sheet.createRow(1);
        row.createCell(0).setCellValue(id);
        row.createCell(1).setCellValue(name);
        row.createCell(2).setCellValue(type);
        response.setContentType("application/octet-stream");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);
        response.flushBuffer();
        workbook.write(response.getOutputStream());
}

单元格数据类型转换

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
import org.apache.poi.xssf.usermodel.*;

// 把单元格值转为 String
public static String getString(XSSFCell xssfCell) {
    if (xssfCell == null) {
        return "";
    }
    if (xssfCell.getCellTypeEnum() == CellType.NUMERIC) {
        return String.valueOf(xssfCell.getNumericCellValue());
    } else if (xssfCell.getCellTypeEnum() == CellType.BOOLEAN) {
        return String.valueOf(xssfCell.getBooleanCellValue());
    } else {
        return xssfCell.getStringCellValue();
    }
}

// 切割是为了去除小数点
public static Long getLong(XSSFCell xssfCell) {
    String s = getString(xssfCell);
    return Long.valueOf(s.substring(0, s.length() - 2));
}

public static Integer getInteger(XSSFCell xssfCell) {
    String s = getString(xssfCell);
    return Integer.valueOf(s.substring(0, s.length() - 2));
}

public static Boolean getBoolean(XSSFCell xssfCell) {
    String s = getString(xssfCell);
    return Boolean.valueOf(s.substring(0, s.length() - 2));
}