读取Excel文件,并保存进数据库
需要的依赖
<!-- POI 导入 导出 依赖 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.1.2</version>
</dependency>
<!-- 导出到表中执行sql语句的依赖 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>
除了上面的还有。连接数据库,spring boot WEB,lombok等
创建实体类
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student3 implements Serializable {
private static final long serialVersionUID = 1L;
private Integer id;
private String name;
private String birthday;
private String denger;
private String address;
}
读取文件内容。这里使用到了JdbcTemplate类。对sql进行操作。
@Autowired
JdbcTemplate jdbcTemplate;
//读取文件内容
@PostMapping("/saveRowDataToDatabase")
public void readPOI(@RequestParam("file") MultipartFile file) throws IOException {
// 获取文件名
String fileName = file.getOriginalFilename();
// 创建workbook
Workbook workbook;
// 获取文件后缀
String fileType = fileName.substring(fileName.lastIndexOf(".") + 1);
// 使用文件输入流读取文件
InputStream inputStream = file.getInputStream();
// 判断文件的类型
if (fileType.equals("xls")) {
workbook = new HSSFWorkbook(inputStream);
} else if (fileType.equals("xlsx")) {
workbook = new XSSFWorkbook(inputStream);
} else {
System.out.println("文件格式不对");
return;
}
// 开始解析工作薄,调用文件解析方法
parseExcel(workbook);
}
//解析文件内容
private void parseExcel(Workbook workbook) {
for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
Sheet sheet = workbook.getSheetAt(sheetNum); // 获取表格
System.out.println(sheet.getSheetName());
// 校验sheet是否合法(是否为空)
if (sheet == null) {
continue;
}
// 获取第一行,一般是标题
Row firstRow = sheet.getRow(sheet.getFirstRowNum());
if (null == firstRow) {
System.out.println("解析Excel失败,在第一行没有读取到任何数据!");
}
// 解析每一行的数据,构造数据对象
int firstRowNum = 0;
//标题下面的数据,数据起始行
int rowStart = firstRowNum + 1;
//获取有记录的行数,即:最后有数据的行是第n行,前面有m行是空行没数据,则返回n-m;
int rowEnd = sheet.getPhysicalNumberOfRows();
//循环遍历每行的内容
for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
Row row = sheet.getRow(rowNum);
if (null == row) {
continue;
}
//定义一个空数组,用于保存值
List<String> rowData = new ArrayList<>();
//循环遍历处理Cell(每个单元格的内容)
for (int cellNum = 1; cellNum < 146; cellNum++) {
Cell cell = row.getCell(cellNum);
if (cell != null) {
String content = convertCellValueToString(cell);
rowData.add(content);
}
}
System.out.println("总"+rowData);
// 将rowData保存到数据库表中
//确保您已经创建了目标表,表的结构应与您解析出的内容相匹配。
String sql = "INSERT INTO student3 (name, birthday, denger, address) VALUES (?, ?, ?,?)";
jdbcTemplate.update(sql, rowData.toArray());
}
}
}
解析文件时,需要对不同类型的文件进行解析。这里创建一个工具类
package com.buba.utils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
import java.text.DecimalFormat;
/**
* @ClassName ConvertCellValueToString 一个格式化列的工具列
* Description TODO
* @Author zn-pcBOOK
* @Date 2023/5/24 15:55
* @Version 1.0
**/
public class ConvertCellValueToString {
public static String convertCellValueToString(Cell cell) {
if (cell == null) {
return null;
}
String content = null;
switch (cell.getCellType()) {
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
// 处理日期类型
content = cell.getDateCellValue().toString();
} else {
// 处理数字类型
DataFormatter dataFormatter = new DataFormatter();
content = dataFormatter.formatCellValue(cell);
}
break;
case STRING:
content = cell.getStringCellValue();
break;
case BOOLEAN:
content = Boolean.toString(cell.getBooleanCellValue());
break;
case BLANK:
break;
case FORMULA:
content = cell.getCellFormula();
break;
case ERROR:
break;
default:
break;
}
return content;
}
}
从数据库的表里导出Excel
package com.buba.controller;
import com.buba.entity.Student3;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
import java.util.Map;
/**
* @ClassName POIoutDb
* Description TODO
* @Author zn-pcBOOK
* @Date 2023/5/24 16:59
* @Version 1.0
**/
@RestController
public class POIoutDb {
@Autowired
JdbcTemplate jdbcTemplate;
@GetMapping("/POIoutDb")
public String export() throws IOException {
//定义文件路径
String fileName = "D:\\files\\students导出测试.xlsx";
//1.创建工作簿对象 需要选择xls或者xlsx.
Workbook workbook = new XSSFWorkbook();
//2.创建工作表对象 (单元的名字)
Sheet sheet = workbook.createSheet("学生信息1");
//3.创建行对象 (表头行 从0开始计算)
Row headerRow = sheet.createRow(0);
//3.2创建表头样式
//设置表头样式对象
CellStyle headerStyle = workbook.createCellStyle();
//设置水平居中对齐
headerStyle.setAlignment(HorizontalAlignment.CENTER);
//设置垂直居中对齐
headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//创建表头字体对象
Font headerFont = workbook.createFont();
//设置表头字体加粗
headerFont.setBold(true);
//设置表头字体颜色为白的
headerFont.setColor(IndexedColors.WHITE.getIndex());
//设置表头字体大小
//将字体应用到表头样式
headerStyle.setFont(headerFont);
//设置表头单元格填充颜色为蓝色
headerStyle.setFillForegroundColor(IndexedColors.BLUE.getIndex());
//设置表头单元格填充颜色模式为实心填充
headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//3.3创建表头单元格对象
Cell cell = headerRow.createCell(0);
cell.setCellValue("id");
cell.setCellStyle(headerStyle);
cell = headerRow.createCell(1);
cell.setCellValue("姓名");
cell.setCellStyle(headerStyle);
cell = headerRow.createCell(2);
cell.setCellValue("生日");
cell.setCellStyle(headerStyle);
cell = headerRow.createCell(3);
cell.setCellValue("性别");
cell.setCellStyle(headerStyle);
cell = headerRow.createCell(4);
cell.setCellValue("住址");
cell.setCellStyle(headerStyle);
cell = headerRow.createCell(5);
cell.setCellStyle(headerStyle);
// 填充数据
List<Student3> students = jdbcTemplate.query("SELECT * FROM student3", new BeanPropertyRowMapper<>(Student3.class));
int rowNum = 1;
for (Student3 student : students) {
//******一定要对应上表的数据。和数据库里的顺序。
Row dataRow = sheet.createRow(rowNum++);
dataRow.createCell(0).setCellValue(student.getId());
dataRow.createCell(1).setCellValue(student.getName());
dataRow.createCell(2).setCellValue(student.getBirthday());
dataRow.createCell(3).setCellValue(student.getDenger());
dataRow.createCell(4).setCellValue(student.getAddress());
// ...
}
// 将工作簿写入输出流
FileOutputStream outputStream = new FileOutputStream(fileName);
workbook.write(outputStream);
outputStream.close();
return "Excel文件生成成功!";
}
}