// 导入excel中的数据到数据库中
@PostMapping(value = "importHDangerRecord")
@Override
public RestMessage importHDangerRecord(@RequestParam("file") MultipartFile file) {
try {
// 隐患分类 隐患级别 隐患描述 报告时间 隐患报告人
String qyid = UserUtil.getCurrQyId();
// String userid = UserUtil.getCurrUserId();
// SeUser user = userFeign.getUserById(userid).getData();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
StringBuilder errorMsg = new StringBuilder("");
List<Hiddendanger> res = new ArrayList<>();
Map<Integer, String> map = ImportExcelUtil.getDataFromExcel(file);
if(map.size()>1){
for (Map.Entry<Integer, String> entry : map.entrySet()) {
if (entry.getKey() > 1) {
Integer row = entry.getKey()+1;
String[] s = entry.getValue().split("#&");
Hiddendanger hiddendanger = new Hiddendanger();
//企业id
TbQybaseinfo qyInfo = userFeign.getQyByUserId(UserUtil.getCurrUserId()).getData();
// hiddendanger.beforeInsert();
// hiddendanger.setQyid(qyInfo.getId());
// hiddendanger.setQyname(qyInfo.getName());
// hiddendanger.setZygsid(qyInfo.getZygsid());
// hiddendanger.setSybid(qyInfo.getSybid());
//隐患分类
if(StringUtils.isNoneBlank(s[0]) && StringUtils.isNoneBlank(s[1])) {
Thirtytwoelements firstCategory = hDangerService.getFirstCategory(s[0]);
if(firstCategory != null && StringUtils.isNoneBlank(firstCategory.getId())){
Thirtytwoelements secondCategoryCategory = hDangerService.getSecondCategory(s[1],firstCategory.getId());
if(secondCategoryCategory != null){
hiddendanger.setCategory(secondCategoryCategory.getId());
hiddendanger.setCatecode(secondCategoryCategory.getCode());
hiddendanger.setCategoryname(secondCategoryCategory.getName());
}else{
errorMsg.append("第" + row + "行隐患分类有误 \n");
}
}else{
errorMsg.append("第" + row + "行隐患分类有误 \n");
}
}else {
errorMsg.append("第" + row + "行隐患分类为空 \n");
}
//隐患级别
if(StringUtils.isNoneBlank(s[2])) {
RestMessage<TbDicItem> rm = userFeign.getItemByNameFieldCode(s[2], "DANGER_YHDJ");
TbDicItem item = rm.getData();
if(item != null){
hiddendanger.setLevel(item.getId());
hiddendanger.setLevelcode(item.getCode());
hiddendanger.setLevelname(item.getName());
}else{
errorMsg.append("第" + row + "行隐患等级有误 \n");
}
}else {
errorMsg.append("第" + row + "行隐患等级为空 \n");
}
//隐患描述
if(StringUtils.isNoneBlank(s[3])) {
hiddendanger.setDescribe(s[3]);
}else {
errorMsg.append("第" + row + "行隐患描述为空 \n");
}
//隐患报告时间
if(StringUtils.isNoneBlank(s[4])) {
if(isValidDate(s[4])){
hiddendanger.setReporttime(s[4]);
}else {
errorMsg.append("第" + row + "行隐患报告时间格式有误 请按照 \" yyyy-MM-dd\" 格式填写 \n");
}
}else {
errorMsg.append("第" + row + "行隐患报告时间为空 \n");
}
//隐患报告人
// hiddendanger.setReportUsername(user.getRealname());
// hiddendanger.setReportUser(userid);
if(StringUtils.isNoneBlank(s[5])) {
SeUser seUser = userFeign.getUserByEmail(s[5]).getData();
if(seUser != null){
hiddendanger.setReportUser(seUser.getId());
hiddendanger.setReportUsername(seUser.getRealname());
TbQybaseinfo qybaseinfo = userFeign.getQyById(seUser.getQyId()).getData();
if(qybaseinfo.getHierarchy() == ConstantCode.DW){
hiddendanger.setHiddenSource(1);
hiddendanger.setQyid(qybaseinfo.getId());
hiddendanger.setQyname(qybaseinfo.getName());
hiddendanger.setZygsid(qybaseinfo.getZygsid());
hiddendanger.setSybid(qybaseinfo.getSybid());
hiddendanger.setReportlevel(1);
}else if(qybaseinfo.getHierarchy() == ConstantCode.ZYGS){
hiddendanger.setHiddenSource(2);
hiddendanger.setReportlevel(2);
hiddendanger.setQyid(qybaseinfo.getId());
hiddendanger.setQyname(qybaseinfo.getName());
if(StringUtils.isNoneBlank(qybaseinfo.getZygsid())){
hiddendanger.setZygsid(qybaseinfo.getZygsid());
}else {
hiddendanger.setZygsid(qybaseinfo.getId());
}
hiddendanger.setSybid(qybaseinfo.getSybid());
}
}else {
errorMsg.append("第" + row + "行隐患报告人邮箱参数有误 \n");
}
}else {
errorMsg.append("第" + row + "行隐患报告人邮箱为空 \n");
}
res.add(hiddendanger);
}
}
}else{
errorMsg.append("导入模板为空 \n");
}
if (errorMsg.length() <= 0) {
for (Hiddendanger hiddendanger: res) {
hiddendanger.beforeInsert();
hDangerService.addHDanger(hiddendanger);
}
return new RestMessage();
} else {
return new RestMessage(0, errorMsg.toString());
}
}catch (Exception e) {
e.printStackTrace();
return new RestMessage(RespCodeAndMsg.FAIL);
}
}
/**
*
*/
package com.zhjt.utils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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 java.io.Closeable;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* @ClassName: ImportExcel
*
*/
public class ImportExcelUtil {
/**
* 读取出filePath中的所有数据信息
* @param filePath excel文件的绝对路径
*
*/
/**
* @Description //TODO 读取excel数据到map集合,key为行号,value为本行每列拼接内容。
* @Param [filePath]
* @return java.util.Map<java.lang.Integer, java.lang.String>
**/
public static Map<Integer, String> getDataFromExcel(MultipartFile filePath) {
//判断是否为excel类型文件
if (!filePath.getOriginalFilename().endsWith(".xls") && !filePath.getOriginalFilename().endsWith(".xlsx")) {
throw new IllegalArgumentException("文件不是excel类型");
}
InputStream fis = null;
Workbook wookbook = null;
try {
//获取一个绝对地址的流
fis = filePath.getInputStream();
} catch (Exception e) {
e.printStackTrace();
}
try {
//2003版本的excel,用.xls结尾
wookbook = new HSSFWorkbook(fis);//得到工作簿
} catch (Exception ex) {
//ex.printStackTrace();
try {
//这里需要重新获取流对象,因为前面的异常导致了流的关闭—————————————————————————————加了这一行
fis = filePath.getInputStream();
//2007版本及更高的excel,用.xlsx结尾
wookbook = new XSSFWorkbook(fis);//得到工作簿
} catch (IOException e) {
e.printStackTrace();
}
}
//得到一个工作表
Sheet sheet = wookbook.getSheetAt(0);
//获得数据的总行数
int totalRowNum = sheet.getLastRowNum();
if (0 == totalRowNum) {
throw new IllegalArgumentException("Excel内没有数据!");
}
//读取表格数据
StringBuilder str = new StringBuilder();
Map<Integer, String> content = new HashMap<Integer, String>();
//模板文件默认前两行为表名和表头,正式数据从第三行开始
Row row = sheet.getRow(1);
int rowNum = sheet.getLastRowNum();
int colNum = row.getLastCellNum();
for (int i = 1; i <= rowNum; i++) {
str = new StringBuilder();
row = sheet.getRow(i);
if(row!=null){
int j = 0;
while (j < colNum) {
if (getCellFormatValue(row.getCell(j)) != null && !"".equals(getCellFormatValue(row.getCell(j)))) {
//避免时间格式产生混乱,不再使用"/"与"-",使用#&分开
str.append(getCellFormatValue(row.getCell(j)) + "#&");
} else {
str.append(" " + "#&");
}
j++;
}
content.put(i, str.substring(0, str.length() - 2).toString());
}
}
closeQuietly(fis);
return content;
}
public static void closeQuietly(Closeable closeable) {
try {
if (closeable != null) closeable.close();
} catch (IOException ioe) {
// ignore
}
}
public static String getCellFormatValue(Cell cell) {
String cellvalue = "";
if (cell != null) {
// 判断当前Cell的Type
switch (cell.getCellType()) {
// 如果当前Cell的Type为NUMERIC
case HSSFCell.CELL_TYPE_BOOLEAN:
cellvalue = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK:
cellvalue = "";
break;
case HSSFCell.CELL_TYPE_NUMERIC:
case HSSFCell.CELL_TYPE_FORMULA: {
// 判断当前的cell是否为Date
if (HSSFDateUtil.isCellDateFormatted(cell)) {
// 如果是Date类型则,默认yyyy-mm-dd
Date date = cell.getDateCellValue();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
cellvalue = sdf.format(date);
}
// 如果是纯数字
else {
// 取得当前Cell的数值
DecimalFormat df = new DecimalFormat("#.#########");
cellvalue = df.format(cell.getNumericCellValue()).toString();
}
break;
}
// 如果当前Cell的Type为STRIN
case HSSFCell.CELL_TYPE_STRING:
// 取得当前的Cell字符串
cellvalue = cell.getRichStringCellValue().getString();
break;
// 默认的Cell值
default:
cellvalue = " ";
}
} else {
cellvalue = "";
}
return cellvalue;
}
/**
* 一对多导入(例:接触职业病危害人员信息)
* @return
*/
public static Map<String,Map<Integer, String>> getOneToManyDataFromExcel(MultipartFile filePath) {
//判断是否为excel类型文件
if (!filePath.getOriginalFilename().endsWith(".xls") && !filePath.getOriginalFilename().endsWith(".xlsx")) {
throw new IllegalArgumentException("文件不是excel类型");
}
InputStream fis = null;
Workbook wookbook = null;
try {
//获取一个绝对地址的流
fis = filePath.getInputStream();
} catch (Exception e) {
e.printStackTrace();
}
try {
//2003版本的excel,用.xls结尾
wookbook = new HSSFWorkbook(fis);//得到工作簿
} catch (Exception ex) {
//ex.printStackTrace();
try {
//这里需要重新获取流对象,因为前面的异常导致了流的关闭—————————————————————————————加了这一行
fis = filePath.getInputStream();
//2007版本及更高的excel,用.xlsx结尾
wookbook = new XSSFWorkbook(fis);//得到工作簿
} catch (IOException e) {
e.printStackTrace();
}
}
Map<String,Map<Integer, String>> map=new HashMap<>();
for (int n= 0; n< wookbook.getNumberOfSheets(); n++) {//获取Sheet表个数
//得到一个工作表
Sheet sheet = wookbook.getSheetAt(n);
//获得数据的总行数
int totalRowNum = sheet.getLastRowNum();
if (0 == totalRowNum) {
throw new IllegalArgumentException("Excel内没有数据!");
}
//读取表格数据
StringBuilder str = new StringBuilder();
Map<Integer, String> content = new HashMap<Integer, String>();
//模板文件默认前两行为表名和表头,正式数据从第三行开始
Row row = sheet.getRow(1);
int rowNum = sheet.getLastRowNum();
int colNum = row.getLastCellNum();
for (int i = 1; i <= rowNum; i++) {
str = new StringBuilder();
row = sheet.getRow(i);
if(row!=null){
int j = 0;
while (j < colNum) {
if (getCellFormatValue(row.getCell(j)) != null && !"".equals(getCellFormatValue(row.getCell(j)))) {
//避免时间格式产生混乱,不再使用"/"与"-",使用#&分开
str.append(getCellFormatValue(row.getCell(j)) + "#&");
} else {
str.append(" " + "#&");
}
j++;
}
content.put(i, str.substring(0, str.length() - 2).toString());
}
}
if(n==0){
map.put("zhubiao",content);
}else{
map.put("zibiao"+n,content);
}
}
closeQuietly(fis);
return map;
}
}