说明
根据xx经理的需求,把30个不同的表格导入的数据库中
pom依赖
<!-- excel工具 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
工具类
package com.economics.society.visual.utils;
import com.economics.society.common.config.RuoYiConfig;
import com.economics.society.common.core.domain.AjaxResult;
import com.economics.society.common.utils.file.FileUploadUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import org.apache.poi.ss.usermodel.CellType;
import java.io.*;
import java.util.HashMap;
import java.util.Map;
/**
* 表格导入工具类
*/
public class ExcelUtil {
/**
* 表格导入
* @param file
* @return 这里返回map返回数据,返回的这些参数后面会用到
* @throws IOException
*/
public static Map<String, Object> getDataFromExcel(MultipartFile file) throws IOException {
//判断file为空,fileName是否为excel类型文件
if (file == null){
throw new OutOfMemoryError("文件不能为空");
}
String fileName = file.getOriginalFilename();
if (!fileName.contains(".xlsx") ){
throw new OutOfMemoryError("文件格式暂不支持导入!");
}
// 定义导入流和Workbook
InputStream inputStream = null;
Workbook wookbook = null;
// 定义返回对象
Map<String, Object> returnMap = new HashMap<>();
try {
inputStream = file.getInputStream();
try {
//得到工作簿
wookbook = new XSSFWorkbook(inputStream);
returnMap.put("wookbook",wookbook);
} catch (Exception ex) {
throw new OutOfMemoryError("数据流异常");
}
//得到一个工作表
Sheet sheet = wookbook.getSheetAt(0);
//获得表头
Row rowHead = sheet.getRow(0);
//获得数据的总行数
int totalRowNum = sheet.getLastRowNum();
// 返回数据
returnMap.put("sheet",sheet);
returnMap.put("totalRowNum",totalRowNum);
try {
// 上传文件路径
String filePath = "/data/file";
// String filePath = "D:/ruoyi/uploadPath";
// 上传并返回新文件名称(这里调用自己的上传接口,把数据存到服务器中以便后续排查)
String fileNames = FileUploadUtils.upload(filePath, file);
String realFileName = file.getOriginalFilename();
String suffix = realFileName.substring(realFileName.lastIndexOf(".") + 1);
// 拿到一些数据,等下会存入日志表(不用的话可以不返回这些数据)
// 文件大小
Long len = file.getSize();
// 文件名称
returnMap.put("realFileName",realFileName);
// 文件后缀
returnMap.put("suffix",suffix);
// 文件大小
returnMap.put("len",len);
// 上传路径
returnMap.put("fileNames",fileNames);
}
catch (Exception e) {
return AjaxResult.error(e.getMessage());
}
} catch(Exception e) {
e.printStackTrace();
}finally {
// 关闭流
inputStream.close();
}
return returnMap;
}
/**
* 判断空行(存在样式的空行。会被统计进来。所以主要的问题是要判断是否是空行。)
* @param row
* @return
*/
public static boolean isRowEmpty(Row row) {
if (row != null) {
for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
Cell cell = row.getCell(0);
if (cell.getCellType() == CellType.NUMERIC) {
return true;
}
if (cell.getStringCellValue() != null && cell.getCellType() == CellType.STRING) {
return true;
}
}
}
return false;
}
}
使用
单个sheet
/**
* 新增导入
* @param file 文件
* @return
*/
@Override
public int installExcelFile(MultipartFile file) throws IOException {
Map<String, Object> dataFromExcel = ExcelUtil.getDataFromExcel(file);
result = tbEverydayPriceService.insertTbEverydayPrice(dataFromExcel);
return result;
};
/**
* 新增每日物价
*
* @param map 每日物价
* @return 结果
*/
@Override
public int insertTbEverydayPrice(Map<String,Object> map) {
// 拿到行列和数据
Sheet sheet = (Sheet) map.get("sheet");
Integer totalRowNum = Integer.valueOf(map.get("totalRowNum").toString());
// 定义存入的对象
TbEverydayPrice tbEverydayPrice = new TbEverydayPrice();
int result = 0;
//获得所有数据(i=2代表从第三行开始读取)
for(int i = 2 ; i <= totalRowNum ; i++) {
//获得第i行对象
Row row = sheet.getRow(i);
//名称
tbEverydayPrice.setTarget(row.getCell((short)0).getStringCellValue());
// 月度
Row rows = sheet.getRow(0);
Cell cell = rows.getCell((short) 0);
tbEverydayPrice.setMonth(cell.getStringCellValue());
//单位
tbEverydayPrice.setUnit(row.getCell((short)1).getStringCellValue());
//数值
tbEverydayPrice.setAbsolute(String.valueOf(row.getCell((short) 2).getNumericCellValue()));
// 存入数据库
tbEverydayPrice.setCreateTime(DateUtils.getNowDate());
result = tbEverydayPriceMapper.insertTbEverydayPrice(tbEverydayPrice);
}
return result;
}
多个sheet
/**
* 新增导入
* @param file 文件
* @return
*/
@Override
public int installExcelFile(MultipartFile file) throws IOException {
Map<String, Object> dataFromExcel = ExcelUtil.getDataFromExcel(file);
result = tbEconomicsSecondService.insertTbEconomicsSecond(dataFromExcel);
return result;
};
/**
* 新增经济体征监测二级页面
*
* @param map 经济体征监测二级页面
* @return 结果
*/
@Override
public int insertTbEconomicsSecond(Map<String, Object> map) {
// 多个sheet得到workbook
Workbook workbook = (Workbook) map.get("wookbook");
// 定义对象
TbEconomicsSecond tbEconomicsSecond = new TbEconomicsSecond();
int result = 0;
// 获取每个sheet表(循环每个sheet)
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
// 获取每个sheet和数据列
Sheet sheet = workbook.getSheetAt(i);
int totalRowNum = sheet.getLastRowNum();
//获得所有数据(从第5行开始读取)
for(int j = 4 ; j <= totalRowNum ; j++) {
// 获得第i行对象
Row row = sheet.getRow(j);
// 体征名称
tbEconomicsSecond.setEconomicsName(sheet.getSheetName());
// 指标名称
tbEconomicsSecond.setTargetName(row.getCell((short) 0).getStringCellValue());
// 月度
Row rows = sheet.getRow(2);
Cell cell = rows.getCell((short) 1);
//todo 规模以上工业规模
cell = rows.getCell((short) 2);
tbEconomicsSecond.setAbsolute("0.00");
// 增速
tbEconomicsSecond.setSpeed(String.valueOf(row.getCell((short) 2).getNumericCellValue()));
// 全省增速/N月增速
tbEconomicsSecond.setSpeedStatus(String.valueOf(row.getCell((short) 1).getNumericCellValue()));
// 年度同期增长
tbEconomicsSecond.setYearAdd(String.valueOf(row.getCell((short) 3).getNumericCellValue()));
// 两年同期增长
tbEconomicsSecond.setTwoYears(String.valueOf(row.getCell((short) 4).getNumericCellValue()));
// 存入数据库
tbEconomicsSecond.setCreateTime(DateUtils.getNowDate());
result = tbEconomicsSecondMapper.insertTbEconomicsSecond(tbEconomicsSecond);
}
}
}
return result;
}