Excel的工具类,BuiPatientInfo是自己定义的接收数据的实体类
ExcelUtils源自通过java将Excel表格导入数据到数据库_java导入excel数据到数据库-CSDN博客
package com.example.demo.utils;
import com.example.demo.utils.BuiPatientInfo;
import com.spire.ms.System.DateTime;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Locale;
/**
* @author xjt
* @version 1.0
*/
public class ExcelUtils {
//总行数
private static int totalRows = 0;
//总条数
private static int totalCells = 0;
//错误信息接收器
private static String errorMsg;
/**
* 读EXCEL文件,获取信息集合
* @return
*/
public static List<BuiPatientInfo> getExcelInfo(MultipartFile mFile) {
String fileName = mFile.getOriginalFilename();//获取文件名
try {
if (!validateExcel(fileName)) {// 验证文件名是否合格
return null;
}
boolean isExcel2003 = true;// 根据文件名判断文件是2003版本还是2007版本
if (isExcel2007(fileName)) {
isExcel2003 = false;
}
List<BuiPatientInfo> userList = createExcel(mFile.getInputStream(), isExcel2003);
return userList;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
* 根据excel里面的内容读取客户信息
* @param is 输入流
* @param isExcel2003 excel是2003还是2007版本
* @return
* @throws IOException
*/
public static List<BuiPatientInfo> createExcel(InputStream is, boolean isExcel2003) {
try{
Workbook wb = null;
if (isExcel2003) {// 当excel是2003时,创建excel2003
wb = new HSSFWorkbook(is);
} else {// 当excel是2007时,创建excel2007
wb = new XSSFWorkbook(is);
}
List<BuiPatientInfo> userList = readExcelValue(wb);// 读取Excel里面客户的信息
return userList;
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
/**
* 读取Excel里面客户的信息
* @param wb
* @return
*/
private static List<BuiPatientInfo> readExcelValue(Workbook wb) {
//默认会跳过第一行标题
// 得到第一个shell
Sheet sheet = wb.getSheetAt(0);
// 得到Excel的行数
totalRows = sheet.getPhysicalNumberOfRows();
// 得到Excel的列数(前提是有行数)
if (totalRows > 1 && sheet.getRow(0) != null) {
totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
}
List<BuiPatientInfo> userList = new ArrayList<BuiPatientInfo>();
// 循环Excel行数
for (int r = 1; r < totalRows; r++) {
Row row = sheet.getRow(r);
if (row == null){
continue;
}
BuiPatientInfo user = new BuiPatientInfo();
// 循环Excel的列
for (int c = 0; c < totalCells; c++) {
Cell cell = row.getCell(c);
if (null != cell) {
if (c == 0) { //第一列
//如果是纯数字,将单元格类型转为String
if(cell.getCellTypeEnum() == CellType.NUMERIC){
cell.setCellType(CellType.STRING);
}
user.setIdentificationCode(cell.getStringCellValue());//将单元格数据赋值给user
}
else if (c == 1){
if(cell.getCellTypeEnum() == CellType.NUMERIC){
cell.setCellType(CellType.STRING);
}
Date javaDate = DateUtil.getJavaDate(Double.parseDouble(cell.getStringCellValue()));
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String formattedDate = formatter.format(javaDate);
// DateTimeFormatter inputFormatter = DateTimeFormatter.ofPattern("EEE MMM dd HH:mm:ss zzz yyyy", Locale.ENGLISH);
// LocalDateTime dateTime = LocalDateTime.parse(input, inputFormatter);
//
// DateTimeFormatter outputFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
// String formattedDateTime = dateTime.format(outputFormatter);
user.setMoitorTime(formattedDate);
}
else if (c == 2){
if(cell.getCellTypeEnum() == CellType.NUMERIC){
cell.setCellType(CellType.STRING);
}
String stringCellValue = cell.getStringCellValue();
user.setMoitorParam5(stringCellValue);
}
else if (c == 3){
if(cell.getCellTypeEnum() == CellType.NUMERIC){
cell.setCellType(CellType.STRING);
}
user.setMoitorParam1(String.valueOf(cell.getStringCellValue()));
}
else if (c == 4){
if(cell.getCellTypeEnum() == CellType.NUMERIC){
cell.setCellType(CellType.STRING);
}
user.setMoitorParam2(String.valueOf(cell.getStringCellValue()));
}
// else if (c == 5){
// if(cell.getCellTypeEnum() == CellType.NUMERIC){
// cell.setCellType(CellType.STRING);
// }
// user.setMoitorParam4(String.valueOf(cell.getStringCellValue()));
// }
// else if (c == 6){
// if(cell.getCellTypeEnum() == CellType.NUMERIC){
// cell.setCellType(CellType.STRING);
// }
// user.setMoitorParam5(String.valueOf(cell.getStringCellValue()));
// }
}
}
// 添加到list
userList.add(user);
}
return userList;
}
/**
* 验证EXCEL文件
*
* @param filePath
* @return
*/
public static boolean validateExcel(String filePath) {
if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {
errorMsg = "文件名不是excel格式";
return false;
}
return true;
}
// @描述:是否是2003的excel,返回true是2003
public static boolean isExcel2003(String filePath) {
return filePath.matches("^.+\\.(?i)(xls)$");
}
//@描述:是否是2007的excel,返回true是2007
public static boolean isExcel2007(String filePath) {
return filePath.matches("^.+\\.(?i)(xlsx)$");
}
}
写接口方法,由于mybatis传参一次只能传入65535个参数,所以数据多的时候分批导入,我这每次导入3000条。
@Autowired
private DemoMapper demoMapper;
@PostMapping("/demo")
public R demo(@RequestParam("file") MultipartFile file) {
List<BuiPatientInfo> excelInfo = ExcelUtils.getExcelInfo(file);
excelInfo.forEach(item -> {
DateTimeFormatter inputFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
LocalDateTime dateTime = LocalDateTime.parse(item.getMoitorTime(), inputFormatter);
item.setMoitorTimee(dateTime);
});
int size = excelInfo.size();
int i = size / 3000;
int temp = size % 3000;
int a = 1;
if (temp == 0) {
i--;
}
for (int k = 0; k <=i; k++) {
if (k == i) {
if (temp == 0) {
temp = 3000;
}
List<BuiPatientInfo> subList = excelInfo.subList(k * 3000, (temp + k * 3000));
System.out.println(k*3000);
System.out.println(temp+k*3000);
demoMapper.insertAll2(subList);
} else {
System.out.println(3000 * k);
System.out.println(3000 * (k + 1));
List<BuiPatientInfo> subList = excelInfo.subList(3000 * k, 3000 * (k + 1));
demoMapper.insertAll2(subList);
}
}
return R.success("成功");
}
Mapper代码
<insert id="insertAll2">
<!-- <foreach collection="excelInfo" close="" separator="" open="" item="item" index="">-->
<!-- insert into 表(id, device_id, device_code, device_type, monitor_param, monitor_data,-->
<!-- monitor_unit, monitor_time)-->
<!-- values ((select max(id) + 1 from 表),-->
<!-- (select id from 表where identification_code = #{item.identificationCode}),-->
<!-- #{item.identificationCode}, '3','水位',#{item.moitorParam2},'m',#{item.moitorTimee});-->
<!-- insert into 表(id, device_id, device_code, device_type, monitor_param, monitor_data,-->
<!-- monitor_unit, monitor_time)-->
<!-- values ((select max(id) + 1 from drg_iot_equipment_data),-->
<!-- (select id from 表where identification_code = #{item.identificationCode}),-->
<!-- #{item.identificationCode}, '3','电压',#{item.moitorParam1},'V',#{item.moitorTimee});-->
<!-- </foreach>-->
</insert>