赞
踩
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>
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。