package com.zuihuibao.chcshop.util;
import java.io.*;
import java.net.URL;
import java.util.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.util.CellRangeAddress;
public class ExcelMergeUtil {
private org.apache.poi.ss.usermodel.Workbook wb;
public ExcelMergeUtil(String filePath){
if(filePath==null || filePath.trim().length()==0){
return;
}
this.getWrokbook(filePath);
}
/**
* 读取excel数据
* @param sheetIndex sheet 索引，从0开始
* @param startReadLine 开始索引
* @param tailLine
* @return
*/
public List<Map<String, String>> readExcelToObjBySheetIndex(Integer sheetIndex,Integer startReadLine,Integer tailLine ) {
return readExcel(this.wb, sheetIndex, startReadLine, tailLine);
}
/**
* 读取excel数据
* @param sheetName sheet 名称
* @param startReadLine 开始索引
* @param tailLine
* @return
*/
public List<Map<String, String>> readExcelToObjBySheetName(String sheetName,Integer startReadLine,Integer tailLine ) {
return readExcel(this.wb, sheetName, startReadLine, tailLine);
}
/**
* 读取列名
* @param sheetIndex
* @param rowIndex
* @return
*/
public Map<String,String> readExcelColumnName(Integer sheetIndex,Integer rowIndex) {
return readExcelColumnName(this.wb, sheetIndex, rowIndex);
}
/**
* 读取列名
* @param sheetName sheet页的名称
* @param rowIndex 列名行的索引，从0开始
* @return
*/
public Map<String,String> readExcelColumnName(String sheetName,Integer rowIndex) {
return readExcelColumnName(this.wb, sheetName, rowIndex);
}
/**
* 读取列名
*
* @param wb
* @param sheetIndex sheet页下标：从0开始
* @param rowIndex 列名行的索引，从0开始
*/
private Map<String,String> readExcelColumnName(Workbook wb, int sheetIndex, int rowIndex) {
Sheet sheet = wb.getSheetAt(sheetIndex);
Row row = null;
Map<String,String> result = new HashMap<>();
row = sheet.getRow(rowIndex);
if (row == null) {
return result;
}
for(Short i=0;i< row.getLastCellNum();i++){
Cell c=row.getCell(i);
String returnStr = "";
boolean isMerge = isMergedRegion(sheet, rowIndex, c.getColumnIndex());
//判断是否具有合并单元格
if (isMerge) {
String rs = getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex());
returnStr = rs;
} else {
//设置单元格类型
c.setCellType(CellType.STRING);
returnStr = c.getRichStringCellValue().getString();
}
returnStr=returnStr.replaceAll("(\r\n|\n)", "");
result.put(i.toString(), returnStr);
}
return result;
}
/**
* 读取列名
*
* @param wb
* @param sheetName sheet页名称
* @param rowIndex 列名行的索引，从0开始
*/
private Map<String,String> readExcelColumnName(Workbook wb, String sheetName, int rowIndex) {
Sheet sheet = wb.getSheet(sheetName);
Row row = null;
Map<String,String> result = new HashMap<>();
row = sheet.getRow(rowIndex);
if (row == null) {
return result;
}
for(Short i=0;i< row.getLastCellNum();i++){
Cell c=row.getCell(i);
String returnStr = "";
boolean isMerge = isMergedRegion(sheet, rowIndex, c.getColumnIndex());
//判断是否具有合并单元格
if (isMerge) {
String rs = getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex());
returnStr = rs;
} else {
//设置单元格类型
c.setCellType(CellType.STRING);
returnStr = c.getRichStringCellValue().getString();
}
returnStr=returnStr.replaceAll("(\r\n|\n)", "");
result.put(i.toString(), returnStr);
}
return result;
}
/**
* 读取excel文件
*
* @param wb
* @param sheetIndex sheet页下标：从0开始
* @param startReadLine 开始读取的行:从0开始
* @param tailLine 去除最后读取的行
*/
private List<Map<String, String>> readExcel(Workbook wb, int sheetIndex, int startReadLine, int tailLine) {
Sheet sheet = wb.getSheetAt(sheetIndex);
Row row=null;
List<Map<String, String>> result = new ArrayList<Map<String, String>>();
for (int i = startReadLine; i < sheet.getLastRowNum() - tailLine + 1; i++) {
row = sheet.getRow(i);
if (row == null) {
continue;
}
Map<String, String> map = new HashMap<String, String>();
for (Cell c : row) {
String returnStr = "";
boolean isMerge = isMergedRegion(sheet, i, c.getColumnIndex());
//判断是否具有合并单元格
if (isMerge) {
String rs = getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex());
returnStr = rs;
} else {
//设置单元格类型
c.setCellType(CellType.STRING);
returnStr = c.getRichStringCellValue().getString();
}
map.put(String.valueOf(c.getColumnIndex()), returnStr.trim());
}
result.add(map);
}
return result;
}
private void getWrokbook(String filePath){
filePath=filePath.toLowerCase();
if(filePath.contains("http://") || filePath.contains("https://")){
InputStream inputStream = null;
try {
inputStream = new URL(filePath).openStream();
} catch (IOException e) {
e.printStackTrace();
}
try {
this.wb = WorkbookFactory.create(inputStream);
} catch (IOException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
}
}else{
try {
this.wb = WorkbookFactory.create(new File(filePath));
} catch (IOException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
}
}
}
/**
* 读取excel文件
*
* @param wb
* @param sheetName sheet页名称
* @param startReadLine 开始读取的行:从0开始
* @param tailLine 去除最后读取的行
*/
private List<Map<String, String>> readExcel(Workbook wb, String sheetName, int startReadLine, int tailLine) {
Sheet sheet = wb.getSheet(sheetName);
Row row=null;
List<Map<String, String>> result = new ArrayList<Map<String, String>>();
for (int i = startReadLine; i < sheet.getLastRowNum() - tailLine + 1; i++) {
row = sheet.getRow(i);
if (row == null) {
continue;
}
Map<String, String> map = new HashMap<String, String>();
for (Cell c : row) {
String returnStr = "";
boolean isMerge = isMergedRegion(sheet, i, c.getColumnIndex());
//判断是否具有合并单元格
if (isMerge) {
String rs = getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex());
returnStr = rs;
} else {
//设置单元格类型
c.setCellType(CellType.STRING);
returnStr = c.getRichStringCellValue().getString();
}
map.put(String.valueOf(c.getColumnIndex()), returnStr.trim());
}
result.add(map);
}
return result;
}
/**
* 获取合并单元格的值
*
* @param sheet
* @param row
* @param column
* @return
*/
public String getMergedRegionValue(Sheet sheet, int row, int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress ca = sheet.getMergedRegion(i);
int firstColumn = ca.getFirstColumn();
int lastColumn = ca.getLastColumn();
int firstRow = ca.getFirstRow();
int lastRow = ca.getLastRow();
if (row >= firstRow && row <= lastRow) {
if (column >= firstColumn && column <= lastColumn) {
Row fRow = sheet.getRow(firstRow);
Cell fCell = fRow.getCell(firstColumn);
return getCellValue(fCell);
}
}
}
return null;
}
/**
* 判断合并了行
*
* @param sheet
* @param row
* @param column
* @return
*/
@SuppressWarnings("unused")
private boolean isMergedRow(Sheet sheet, int row, int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress range = sheet.getMergedRegion(i);
int firstColumn = range.getFirstColumn();
int lastColumn = range.getLastColumn();
int firstRow = range.getFirstRow();
int lastRow = range.getLastRow();
if (row == firstRow && row == lastRow) {
if (column >= firstColumn && column <= lastColumn) {
return true;
}
}
}
return false;
}
/**
* 判断指定的单元格是否是合并单元格
*
* @param sheet
* @param row 行下标
* @param column 列下标
* @return
*/
private boolean isMergedRegion(Sheet sheet, int row, int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress range = sheet.getMergedRegion(i);
int firstColumn = range.getFirstColumn();
int lastColumn = range.getLastColumn();
int firstRow = range.getFirstRow();
int lastRow = range.getLastRow();
if (row >= firstRow && row <= lastRow) {
if (column >= firstColumn && column <= lastColumn) {
return true;
}
}
}
return false;
}
/**
* 判断sheet页中是否含有合并单元格
*
* @param sheet
* @return
*/
@SuppressWarnings("unused")
private boolean hasMerged(Sheet sheet) {
return sheet.getNumMergedRegions() > 0 ? true : false;
}
/**
* 合并单元格
*
* @param sheet
* @param firstRow 开始行
* @param lastRow 结束行
* @param firstCol 开始列
* @param lastCol 结束列
*/
@SuppressWarnings("unused")
private void mergeRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
}
/**
* 获取单元格的值
*
* @param cell
* @return
*/
@SuppressWarnings("deprecation")
private String getCellValue(Cell cell) {
if (cell == null) return "";
if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
return cell.getStringCellValue();
} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
return String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
return cell.getCellFormula();
} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
return String.valueOf(cell.getNumericCellValue());
}
return "";
}
}调用示例：
controller:
@GetMapping("/api/szcp/tools/importAccidentPolicy")
public Rsp<Void> importAccidentPolicy(@RequestParam(value = "fileKey") String fileKey,
@CookieValue(value = RequestConstant.SESSION_KEY) String sessionId){
AccidentUser user = accidentUserService.checkSystemOperatorRole(sessionId);
accidnetPolicyConfigService.importAccidentPolicy(fileKey,user.getUserId());
return Rsp.ok(null);
}service：
@Transactional(rollbackFor = Exception.class)
public void importAccidentPolicy(String fileKey, Long userId){
//String path = "/Users/yangjingqin/Downloads/政策精选新-10121133.xlsx";
//fileKey="doccc3b45f1d3954516a5cdecf67b0fe083";
String path=host+"chc-shop/api/v1/accident/document/download?id="+fileKey;
org.apache.poi.ss.usermodel.Workbook wb = ExcelMergeUtil.create(path);
String[] sheetNameArr={"超赔","驾意","雇主","货运"};
accidnetPolicyConfigRepository.deleteByDate(new Date());
for(Integer i=0;i<sheetNameArr.length;i++){
//列名
Map<String,String> columnName= ExcelMergeUtil.readExcelColumnName(wb,sheetNameArr[i],2);
//数据
List<Map<String, String>> dataResult = ExcelMergeUtil.readExcelToObjBySheetName(wb,sheetNameArr[i],3,0);
//列名跟数据关联
List<Map<String, String>> dataResultWithColumnNameList=ExcelMergeUtil.matchHeaderNameAndData(columnName,dataResult);
//字段名跟数据关联
List<Map<String,String>> dataResultWithFieldNameList= ExcelMergeUtil.matchFieldAndData(dataResultWithColumnNameList,accidnetPolicyConfigRepository.policyHeaderConfig());
accidnetPolicyConfigRepository.addAccidentPolicy(sheetNameArr[i],dataResultWithFieldNameList,fileKey,userId);
}
}表头配置：
public Map<String, String> policyHeaderConfig() {
Map<String, String> myHeader = new HashMap<>();
myHeader.put("productCode", "产品编码"); //数据库模型字段 -> 表头名称
myHeader.put("insuranceCompanyName", "保险公司");
myHeader.put("districtLimit", "区域限制");
myHeader.put("carModel", "车型");
myHeader.put("insurancePlan", "方案");
myHeader.put("premium", "保费");
myHeader.put("businessRequire", "业务要求");
myHeader.put("refund", "佣金");
myHeader.put("netPremium", "净保费");
myHeader.put("vipRefund", "VIP佣金");
myHeader.put("vipNetPremium", "VIP净保费");
myHeader.put("supplierCode", "上游渠道编码");
myHeader.put("contactName", "联系人");
myHeader.put("refundRate", "收入比例");
myHeader.put("expendNetPremium", "支出净保费");
myHeader.put("orderGroupName", "出单群名");
myHeader.put("settlementType", "结算");
return myHeader;
}
public void addAccidentPolicy(String insuranceTypeName,List<Map<String, String>> dataList, String fileKey, Long userId) {
for (Map<String, String> data : dataList) {
if(StringUtils.isEmpty(data.get("productCode"))){
continue;
}
this.checkProductCode(insuranceTypeName,data.get("productCode"));
AccidentPolicyConfig accidentPolicyConfig = this.createAccidentPolicyConfig(insuranceTypeName,data, fileKey, userId);
this.insertAccidentPolicyConfig(accidentPolicyConfig);
}
}private AccidentPolicyConfig createAccidentPolicyConfig(String insuranceTypeName,Map<String, String> data, String fileKey, Long userId) {
AccidentPolicyConfig accidentPolicyConfig = BeanUtil.toJavabean(data, AccidentPolicyConfig.class);
accidentPolicyConfig.setOperatorUserId(userId);
accidentPolicyConfig.setCreateTime(new Date());
accidentPolicyConfig.setEffectDate(new Date());
accidentPolicyConfig.setFileKey(fileKey);
accidentPolicyConfig.setInsuranceTypeName(insuranceTypeName);
return this.handleNum(accidentPolicyConfig);
}package com.zuihuibao.openapi.common.util;
import java.lang.reflect.Field;
import java.util.List;
import java.util.Map;
import org.apache.commons.lang3.reflect.FieldUtils;
import com.google.common.collect.Lists;
import com.zuihuibao.openapi.exception.MessageException;
import lombok.extern.slf4j.Slf4j;
@Slf4j
public class BeanUtil {
public static <T> T createInstance(Class<T> classzz) {
try {
T instance = classzz.newInstance();
return instance;
} catch (Exception e) {
log.error("createInstance", e);
throw new MessageException(e.getMessage());
}
}
public static <T> List<T> toJavabean(List<Map<String, String>> list, Class<T> clz) {
List<T> rslt = Lists.newArrayList();
for( int i=0;i<list.size();i++) {
rslt.add(toJavabean(list.get(i),clz));
}
return rslt;
}
public static <T> T toJavabean(Map<String, String> data, Class<T> clz) {
T inst = BeanUtil.createInstance(clz);
Field[] fields = FieldUtils.getAllFields(clz);
// log.info(JSONUtil.toJsonString(fields));
for (Field field : fields) {
if (data.containsKey(field.getName())) {
field.setAccessible(true);
try {
field.set(inst, data.get(field.getName()));
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
}
return inst;
}
}本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
