实现Excel的导入导出

深藏阁楼爱情的钟 2023-10-16 09:49 63阅读 0赞

--------------------

### theme: channing-cyan ###

![coverForJavaExcel.png][]

## 一、前言 ##

我们在日常开发中，一定遇到过要将数据导出为`Excel`的需求，那么怎么做呢？在做之前，我们需要思考下`Excel`的组成。`Excel`是由四个**元素**组成的分别是：`WorkBook(工作簿)`、`Sheet(工作表)`、`Row(行)`、`Cell(单元格)`，其中包含关系是**从左至右**,，一个`WorkBook`可以包含多个`Sheet`，一个`Sheet`又是由多个`Row`组成，一个`Row`是由多个`Cell`组成。知道这些后那么我们就使用`java`来将数据以`Excel`的方式导出。让我们一起来学习吧:pencil2:！

--------------------

## 二、引入`Apache POI`依赖 ##

使用`Java`实现将数据以`Excel`的方式导出，需要依赖第三方的库。我们需要再`pom.xml`中引入下面的依赖：

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

## 三、用法`&`步骤 ##

### 2.1 创建`Excel`的元素 ###

#### (1)创建`WokrBook` ####

`java Workbook workbook = new XSSFWorkbook();`

#### (2)创建`Sheet` ####

`java Sheet sheet = workbook.createSheet();`

设置`sheet`的名称

`javascript Sheet sheet = workbook.createSheet("sheet名称");`

#### (3)创建行`Row` ####

`java Row row = sheet.createRow(0);`

#### (4)创建单元格`Cell` ####

`java Cell cell = row.createCell(0, CellType.STRING);`

可以指定单元格的类型，支持的类型有下面7种：

`java _NONE(-1), NUMERIC(0), STRING(1), //公式 FORMULA(2), BLANK(3), //布尔 BOOLEAN(4), ERROR(5);`

#### (5) 填充数据 ####

`java cell.setCellValue("苹果");`

### 2.3 样式和字体 ###

如果我们需要导出的`Excel`美观一些，如设置字体的样式**加粗**、**颜色**、**大小**等等，就需要创建样式和字体。 创建样式：

`javascript CellStyle cellStyle = workbook.createCellStyle();`

#### (1)左右垂直居中 ####

`java //左右居中 excelTitleStyle.setAlignment(HorizontalAlignment.CENTER); // 设置垂直居中 excelTitleStyle.setVerticalAlignment(VerticalAlignment.CENTER);`

#### (2)字体加粗、颜色 ####

创建加粗样式并设置到`CellStyle`中：

`java Font font = workbook.createFont(); //字体颜色为红色 font.setColor(IndexedColors.RED.getIndex()); //字体加粗 font.setBold(true); cellStyle.setFont(font);`

指定`Cell`单元格使用该样式：

`javascript cell.setCellStyle(style);`

#### (3)调整列宽和高 ####

`java Sheet sheet = workbook.createSheet(); //自动调整列的宽度来适应内容 sheet.autoSizeColumn(int column); // 设置列的宽度 sheet.setColumnWidth(2, 20 * 256);`

`autoSizeColumn()`传递的参数就是要设置的列索引。`setColumnWidth()`第一个参数是要设置的列索引，第二参数是具体的宽度值，宽度 = 字符个数 \* 256(例如20个字符的宽度就是20 \* 256)

#### (4)倾斜、下划线 ####

`javascript Font font = workbook.createFont(); font.setItalic(boolean italic); 设置倾斜 font.setUnderline(byte underline); 设置下划线`

### 2.4 进阶用法 ###

#### (1)合并单元格 ####

`java Workbook workbook = new XSSFWorkbook(); Sheet sheet = workbook.createSheet(fileName); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5));`

*  `CellRangeAddress()`方法四个参数分别是`fristRow`：起始行、`lastRow`：结束行、`fristCol`：起始列、`lastCol`：结束列。 如果你想合并从第一行到第二行从一列到第十列的单元格(一共合并20格)，那么就是`CellRangeAddress(0,1,0,10)`。

#### (2)字段必填 ####

`java //创建数据验证 DataValidationHelper dvHelper = sheet.getDataValidationHelper(); //创建要添加校验的单元格对象 CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 10); //创建必填校验规则 DataValidationConstraint constraint = validationHelper.createCustomConstraint("NOT(ISBLANK(A1))"); //设置校验 DataValidation validation = dvHelper.createValidation(constraint, addressList); //校验不通过 提示 validation.setShowErrorBox(true); sheet.addValidationData(validation);`

*  `CellRangeAddressList()`方法传递四个参数，分别是：`fristRow`：起始行、`lastRow`：结束行、`fristCol`：起始列、`lastCol`：结束列。`CellRangeAddressList(0, 0, 0, 10)`表示的就是给第一行从第一列开始到第十列一共十个单元格添加数据校验。

#### (3)添加公式 ####

*  SUM：求和函数

`java //创建SUM公式 FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); Cell sumCell = row.createCell(0); sumCell.setCellFormula("SUM(A1:A10)"); //计算SUM公式结果 Cell sumResultCell = row.createCell(1); sumResultCell.setCellValue(evaluator.evaluate(sumCell).getNumberValue());`

*  AVERAGE：平均数函数

\`\`\`java //创建AVERAGE公式 FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); Cell averageCell = row.createCell(0); averageCell.setCellFormula("AVERAGE(A1:A10)");

//计算AVERAGE公式结果 Cell averageResultCell = row.createCell(1); averageResultCell.setCellValue(evaluator.evaluate(averageCell).getNumberValue()); \`\`\`

*  COUNT：计数函数

\`\`\`java //创建COUNT公式 FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); Cell countCell = row.createCell(0); countCell.setCellFormula("COUNT(A1:A10)");

//计算COUNT公式结果 Cell countResultCell = row.createCell(1); countResultCell.setCellValue(evaluator.evaluate(countCell).getNumberValue());

\`\`\`

*  IF：条件函数

\`\`\`java //创建IF公式 FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); Cell ifCell = row.createCell(0); ifCell.setCellFormula("IF(A1>B1,\\"Yes\\",\\"No\\")");

//计算IF公式结果 Cell ifResultCell = row.createCell(1); ifResultCell.setCellValue(evaluator.evaluate(ifCell).getStringValue());

\`\`\`

*  CONCATENATE：连接函数

\`\`\`java //创建CONCATENATE公式 FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); Cell concatenateCell = row.createCell(0); concatenateCell.setCellFormula("CONCATENATE(A1,\\" \\",B1)");

//计算CONCATENATE公式结果 Cell concatenateResultCell = row.createCell(1); concatenateResultCell.setCellValue(evaluator.evaluate(concatenateCell).getStringValue());

\`\`\`

#### (4)下拉选择 ####

`java //下拉值 private List<String> grade = Arrays.asList("高", "中", "低"); (此处省略n行代码) Sheet sheet = workbook.createSheet("sheet"); DataValidation dataValidation = this.addPullDownConstraint(i, sheet, grade ); sheet.addValidationData(dataValidation);`

#### (5)设置单元格的数据类型 ####

*  数字格式

`javascript // 设置单元格样式 - 数字格式 CellStyle numberCellStyle = workbook.createCellStyle(); numberCellStyle.setDataFormat(workbook.createDataFormat().getFormat("#,##0.00")); //指定单元格 Row row = sheet.createRow(0); Cell cell = row.createCell(0); cell.setCellStyle(numberCellStyle);`

*  日期格式

`javascript // 设置单元格样式 - 日期格式 CellStyle dateCellStyle = workbook.createCellStyle(); dateCellStyle.setDataFormat(workbook.createDataFormat().getFormat("yyyy-MM-dd")); //指定单元格 Row row = sheet.createRow(0); Cell cell = row.createCell(0); cell.setCellStyle(dateCellStyle);`

## 四、导出完整示例 ##

下面的示例使用`SpringBoot`项目来演示：

`pom.xml`依赖：

`xml <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> <version>2.7.5</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> <version>2.7.5</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.1.2</version> </dependency>  <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.1.2</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.16.16</version> </dependency>  <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>2.0.21</version> </dependency> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-lang3</artifactId> <version>3.12.0</version> </dependency>`

`Controller`层代码：

\`\`\`java package shijiangdiya.controller; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.PostMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import shijiangdiya.utils.ExportUtils; import javax.servlet.http.HttpServletResponse;

@RestController @RequestMapping("/sync") public class ExportController \{ \} \`\`\`

### (1)代码 ###

\`\`\`java @Autowired private HttpServletResponse response;

@PostMapping("/export")
    public void export() {
        //模拟json数据
        String data = "[{\n" +
                "    \"studentId\": \"20210101\",\n" +
                "    \"name\": \"Alice\",\n" +
                "    \"age\": 20,\n" +
                "    \"credit\": 80\n" +
                "  },\n" +
                "  {\n" +
                "    \"studentId\": \"20210102\",\n" +
                "    \"name\": \"Bob\",\n" +
                "    \"age\": 21,\n" +
                "    \"credit\": 85\n" +
                "  },\n" +
                "  {\n" +
                "    \"studentId\": \"20210103\",\n" +
                "    \"name\": \"Charlie\",\n" +
                "    \"age\": 22,\n" +
                "    \"credit\": 90\n" +
                "  },\n" +
                "  {\n" +
                "    \"studentId\": \"20210104\",\n" +
                "    \"name\": \"David\",\n" +
                "    \"age\": 20,\n" +
                "    \"credit\": 75\n" +
                "  },\n" +
                "  {\n" +
                "    \"studentId\": \"20210105\",\n" +
                "    \"name\": \"Emily\",\n" +
                "    \"age\": 21,\n" +
                "    \"credit\": 82\n" +
                "  },\n" +
                "  {\n" +
                "    \"studentId\": \"20210106\",\n" +
                "    \"name\": \"Frank\",\n" +
                "    \"age\": 22,\n" +
                "    \"credit\": 88\n" +
                "  },\n" +
                "  {\n" +
                "    \"studentId\": \"20210107\",\n" +
                "    \"name\": \"Grace\",\n" +
                "    \"age\": 20,\n" +
                "    \"credit\": 81\n" +
                "  },\n" +
                "  {\n" +
                "    \"studentId\": \"20210108\",\n" +
                "    \"name\": \"Henry\",\n" +
                "    \"age\": 21,\n" +
                "    \"credit\": 89\n" +
                "  },\n" +
                "  {\n" +
                "    \"studentId\": \"20210109\",\n" +
                "    \"name\": \"Isaac\",\n" +
                "    \"age\": 22,\n" +
                "    \"credit\": 92\n" +
                "  },\n" +
                "  {\n" +
                "    \"studentId\": \"20210110\",\n" +
                "    \"name\": \"John\",\n" +
                "    \"age\": 20,\n" +
                "    \"credit\": 78\n" +
                "  },\n" +
                "  {\n" +
                "    \"studentId\": \"20210111\",\n" +
                "    \"name\": \"Kelly\",\n" +
                "    \"age\": 21,\n" +
                "    \"credit\": 84\n" +
                "  },\n" +
                "  {\n" +
                "    \"studentId\": \"20210112\",\n" +
                "    \"name\": \"Linda\",\n" +
                "    \"age\": 22,\n" +
                "    \"credit\": 87\n" +
                "  },\n" +
                "  {\n" +
                "    \"studentId\": \"20210113\",\n" +
                "    \"name\": \"Mike\",\n" +
                "    \"age\": 20,\n" +
                "    \"credit\": 77\n" +
                "  },\n" +
                "  {\n" +
                "    \"studentId\": \"20210114\",\n" +
                "    \"name\": \"Nancy\",\n" +
                "    \"age\": 21,\n" +
                "    \"credit\": 83\n" +
                "  },\n" +
                "  {\n" +
                "    \"studentId\": \"20210115\",\n" +
                "    \"name\": \"Oscar\",\n" +
                "    \"age\": 22,\n" +
                "    \"credit\": 91\n" +
                "  },\n" +
                "  {\n" +
                "    \"studentId\": \"20210116\",\n" +
                "    \"name\": \"Paul\",\n" +
                "    \"age\": 20,\n" +
                "    \"credit\": 76\n" +
                "  },\n" +
                "  {\n" +
                "    \"studentId\": \"20210117\",\n" +
                "    \"name\": \"Queen\",\n" +
                "    \"age\": 21,\n" +
                "    \"credit\": 86\n" +
                "  },\n" +
                "  {\n" +
                "    \"studentId\": \"20210118\",\n" +
                "    \"name\": \"Rachel\",\n" +
                "    \"age\": 22,\n" +
                "    \"credit\": 94\n" +
                "  },\n" +
                "  {\n" +
                "    \"studentId\": \"20210119\",\n" +
                "    \"name\": \"Sarah\",\n" +
                "    \"age\": 20,\n" +
                "    \"credit\": 79\n" +
                "  },\n" +
                "  {\n" +
                "    \"studentId\": \"20210120\",\n" +
                "    \"name\": \"Tom\",\n" +
                "    \"age\": 21,\n" +
                "    \"credit\": 80\n" +
                "  }\n" +
                "]\n";
        ExportUtils.exportExcel("学生信息", data, Student.class, response);
    }

\`\`\`

### (2)工具类 ###

`java /** * 数据导出 * @param fileName 导出excel名称 * @param data 导出的数据 * @param c 导出数据的实体class * @param response 响应 * @throws Exception */ public static void exportExcel(String fileName, String data, Class<?> c, HttpServletResponse response) throws Exception { try { // 创建表头 // 创建工作薄 Workbook workbook = new XSSFWorkbook(); Sheet sheet = workbook.createSheet(); // 创建表头行 Row rowHeader = sheet.createRow(0); if (c == null) { throw new RuntimeException("Class对象不能为空!"); } Field[] declaredFields = c.getDeclaredFields(); List<String> headerList = new ArrayList<>(); if (declaredFields.length == 0) { return; } for (int i = 0; i < declaredFields.length; i++) { Cell cell = rowHeader.createCell(i, CellType.STRING); String headerName = String.valueOf(declaredFields[i].getName()); cell.setCellValue(headerName); headerList.add(i, headerName); } // 填充数据 List<?> objects = JSONObject.parseArray(data, c); Object obj = c.newInstance(); if (!CollectionUtils.isEmpty(objects)) { for (int o = 0; o < objects.size(); o++) { Row rowData = sheet.createRow(o + 1); for (int i = 0; i < headerList.size(); i++) { Cell cell = rowData.createCell(i); Field nameField = c.getDeclaredField(headerList.get(i)); nameField.setAccessible(true); String value = String.valueOf(nameField.get(objects.get(o))); cell.setCellValue(value); } } } response.setContentType("application/vnd.ms-excel"); String resultFileName = URLEncoder.encode(fileName, "UTF-8"); response.setHeader("Content-disposition", "attachment;filename=" + resultFileName + ";" + "filename*=utf-8''" + resultFileName); workbook.write(response.getOutputStream()); workbook.close(); response.flushBuffer(); } catch (Exception e) { throw new RuntimeException(e); } }`

### (2)结果 ###

![在这里插入图片描述][watermark_image_bG9nby9jc2RuXzEucG5nP3gtb3NzLXByb2Nlc3M9aW1hZ2UvcmVzaXplLGhfMTIz_g_se_x_0_y_0_t_100]

## 五、导入完整示例 ##

### (1)代码 ###

`java @PostMapping("/import") public void importExcel(@RequestParam("excel") MultipartFile excel){ Workbook workbook = null; try { workbook = WorkbookFactory.create(excel.getInputStream()); Sheet sheet = workbook.getSheetAt(0); List<Student> students = new ArrayList<>(); int i = 0; for (Row row : sheet) { Row row1 = sheet.getRow(i + 1); if(row1 != null){ Student data = new Student(); data.setStudentId(Integer.parseInt(row1.getCell(0).getStringCellValue())); data.setName(row1.getCell(1).getStringCellValue()); data.setAge(Integer.parseInt(row1.getCell(2).getStringCellValue())); data.setCredit(Integer.parseInt(row1.getCell(3).getStringCellValue())); students.add(data); } } System.out.println(students); workbook.close(); } catch (IOException e) { throw new RuntimeException(e); }`

### (2)工具类 ###

`java /** * 导入 * @param workbook 工作簿 * @param c 实体类 * @return 实体类集合 */ public static <T> List<T> importExcel(Workbook workbook,Class<?> c){ List<T> dataList = new ArrayList<>(); try { Sheet sheet = workbook.getSheetAt(0); int i = 0; T o = null; for (Row row : sheet) { Row row1 = sheet.getRow(i + 1); if(row1 != null){ o = (T) c.newInstance(); Field[] declaredFields = c.getDeclaredFields(); for (int i1 = 0; i1 < declaredFields.length; i1++) { String name = declaredFields[i1].getName(); Field declaredField1 = o.getClass().getDeclaredField(name); declaredField1.setAccessible(true); Cell cell = row1.getCell(i1); String type = declaredFields[i1].getType().getName(); String value = String.valueOf(cell); if(StringUtils.equals(type,"int") || StringUtils.equals(type,"Integer")){ declaredField1.set(o,Integer.parseInt(value)); } else if(StringUtils.equals(type,"java.lang.String") || StringUtils.equals(type,"char") || StringUtils.equals(type,"Character") || StringUtils.equals(type,"byte") || StringUtils.equals(type,"Byte")){ declaredField1.set(o,value); } else if(StringUtils.equals(type,"boolean") || StringUtils.equals(type,"Boolean")){ declaredField1.set(o,Boolean.valueOf(value)); } else if(StringUtils.equals(type,"double") || StringUtils.equals(type,"Double")){ declaredField1.set(o,Double.valueOf(value)); } else if (StringUtils.equals(type,"long") || StringUtils.equals(type,"Long")) { declaredField1.set(o,Long.valueOf(value)); } else if(StringUtils.equals(type,"short") || StringUtils.equals(type,"Short")){ declaredField1.set(o,Short.valueOf(value)); } else if(StringUtils.equals(type,"float") || StringUtils.equals(type,"Float")){ declaredField1.set(o,Float.valueOf(value)); } } } dataList.add(o); } workbook.close(); return dataList; }catch (Exception e){ e.printStackTrace(); } return dataList; }`

注意：导入工具类仅限`Java的八大基础数据类型`和`String`类型。如果还有其他类型需要自己扩展。

### (3)结果 ###

学生信息集合： ![在这里插入图片描述][watermark_image_bG9nby9jc2RuXzEucG5nP3gtb3NzLXByb2Nlc3M9aW1hZ2UvcmVzaXplLGhfOTQ_g_se_x_0_y_0_t_100]

[coverForJavaExcel.png]: https://img-blog.csdnimg.cn/img_convert/0cd854594b73047187ff6bf1806cd6e3.png?x-oss-process=image/watermark,image_bG9nby9jc2RuXzEucG5nP3gtb3NzLXByb2Nlc3M9aW1hZ2UvcmVzaXplLGhfMTQw,g_se,x_0,y_0,t_100
[watermark_image_bG9nby9jc2RuXzEucG5nP3gtb3NzLXByb2Nlc3M9aW1hZ2UvcmVzaXplLGhfMTIz_g_se_x_0_y_0_t_100]: https://img-blog.csdnimg.cn/img_convert/ccdd0b9a8999822462e0b7374ddacaff.png?x-oss-process=image/watermark,image_bG9nby9jc2RuXzEucG5nP3gtb3NzLXByb2Nlc3M9aW1hZ2UvcmVzaXplLGhfMTIz,g_se,x_0,y_0,t_100
[watermark_image_bG9nby9jc2RuXzEucG5nP3gtb3NzLXByb2Nlc3M9aW1hZ2UvcmVzaXplLGhfOTQ_g_se_x_0_y_0_t_100]: https://img-blog.csdnimg.cn/img_convert/47f71f5341415a0c17e26b204538bd26.png?x-oss-process=image/watermark,image_bG9nby9jc2RuXzEucG5nP3gtb3NzLXByb2Nlc3M9aW1hZ2UvcmVzaXplLGhfOTQ=,g_se,x_0,y_0,t_100