1.添加springBoot支持
<!-- excel -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.13</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.13</version>
</dependency>
2.自定义实体类所需要的bean
ExcelColumn
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelColumn {
String value() default "";
int col() default 0;
}
3.定义导出的实体类
省略getter setter
package com.schj.entity;
public class DClassRate {
private Integer id;
//渠道编码
@ExcelColumn(value = "渠道编码", col = 0)
private String chId;
//险种代码
@ExcelColumn(value = "险种代码", col = 1)
private String classCode;
//缴费年限
@ExcelColumn(value = "缴费年限", col = 2)
private Integer yearnum;
//首期费率
@ExcelColumn(value = "首期费率", col = 3)
private Double rate1;
//第二年费率
@ExcelColumn(value = "第二年费率", col = 4)
private Double rate2;
//第三年费率
@ExcelColumn(value = "第三年费率", col = 5)
private Double rate3;
//第四年费率
@ExcelColumn(value = "第四年费率", col = 6)
private Double rate4;
//第五年费率
@ExcelColumn(value = "第五年费率", col = 7)
private Double rate5;
//第六年费率
@ExcelColumn(value = "第六年及以后费率", col = 8)
private Double rate6;
@ExcelColumn(value = "费率起期", col = 9)
private String beginDate;
@ExcelColumn(value = "费率终期", col = 10)
private String endDate;
private String creator;
private String creatdate;
private String updateuser;
private String updatetime;
private Integer isDelete;
private String title;
private String chname;
}
4.ExcelUtils类编写
package com.schj.utils;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
import java.util.stream.Stream;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang3.BooleanUtils;
import org.apache.commons.lang3.CharUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
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.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
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.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.http.MediaType;
import org.springframework.web.multipart.MultipartFile;
import com.schj.entity.ExcelColumn;
/**
* @author ABT
* @description
**/
public class ExcelUtils {
private final static Logger log = LoggerFactory.getLogger(ExcelUtils.class);
private final static String EXCEL2003 = "xls";
private final static String EXCEL2007 = "xlsx";
public static <T> List<T> readExcel(String path, Class<T> cls,
MultipartFile file) throws Exception {
String fileName = file.getOriginalFilename();
if (!fileName.matches("^.+\\.(?i)(xls)$")
&& !fileName.matches("^.+\\.(?i)(xlsx)$")) {
log.error("上传文件格式不正确");
}
List<T> dataList = new ArrayList<>();
Workbook workbook = null;
try {
InputStream is = file.getInputStream();
if (fileName.endsWith(EXCEL2007)) {
workbook = new XSSFWorkbook(is);
}
if (fileName.endsWith(EXCEL2003)) {
workbook = new HSSFWorkbook(is);
}
if (workbook != null) {
// 类映射 注解 value-->bean columns
Map<String, List<Field>> classMap = new HashMap<>();
List<Field> fields = Stream.of(cls.getDeclaredFields())
.collect(Collectors.toList());
fields.forEach(field -> {
ExcelColumn annotation = field
.getAnnotation(ExcelColumn.class);
if (annotation != null) {
String value = annotation.value();
if (StringUtils.isBlank(value)) {
return;// return起到的作用和continue是相同的 语法
}
if (!classMap.containsKey(value)) {
classMap.put(value, new ArrayList<>());
}
field.setAccessible(true);
classMap.get(value).add(field);
}
});
// 索引-->columns
Map<Integer, List<Field>> reflectionMap = new HashMap<>(16);
// 默认读取第一个sheet
Sheet sheet = workbook.getSheetAt(0);
boolean firstRow = true;
for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
// 首行 提取注解
if (firstRow) {
for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
Cell cell = row.getCell(j);
String cellValue = getCellValue(cell);
if (classMap.containsKey(cellValue)) {
reflectionMap.put(j, classMap.get(cellValue));
}
}
firstRow = false;
} else {
// 忽略空白行
if (row == null) {
continue;
}
try {
T t = cls.newInstance();
// 判断是否为空白行
boolean allBlank = true;
for (int j = row.getFirstCellNum(); j <= row
.getLastCellNum(); j++) {
if (reflectionMap.containsKey(j)) {
Cell cell = row.getCell(j);
String cellValue = getCellValue(cell);
if (StringUtils.isNotBlank(cellValue)) {
allBlank = false;
}
List<Field> fieldList = reflectionMap.get(j);
fieldList.forEach(x -> {
try {
handleField(t, cellValue, x);
} catch (Exception e) {
log.error(
String.format(
"reflect field:%s value:%s exception!",
x.getName(),
cellValue),
e);
}
});
}
}
if (!allBlank) {
dataList.add(t);
} else {
log.warn(String.format(
"row:%s is blank ignore!", i));
}
} catch (Exception e) {
log.error(
String.format("parse row:%s exception!", i),
e);
}
}
}
}
} catch (Exception e) {
log.error(String.format("parse excel exception!"), e);
} finally {
if (workbook != null) {
try {
workbook.close();
} catch (Exception e) {
log.error(String.format("parse excel exception!"), e);
}
}
}
return dataList;
}
private static <T> void handleField(T t, String value, Field field)
throws Exception {
Class<?> type = field.getType();
if (type == null || type == void.class || StringUtils.isBlank(value)) {
return;
}
if (type == Object.class) {
field.set(t, value);
// 数字类型
} else if (type.getSuperclass() == null
|| type.getSuperclass() == Number.class) {
if (type == int.class || type == Integer.class) {
field.set(t, NumberUtils.toInt(StringUtils.substringBefore(
value, ".0")));
} else if (type == long.class || type == Long.class) {
field.set(t, NumberUtils.toLong(value));
} else if (type == byte.class || type == Byte.class) {
field.set(t, NumberUtils.toByte(value));
} else if (type == short.class || type == Short.class) {
field.set(t, NumberUtils.toShort(value));
} else if (type == double.class || type == Double.class) {
field.set(t, NumberUtils.toDouble(value));
} else if (type == float.class || type == Float.class) {
field.set(t, NumberUtils.toFloat(value));
} else if (type == char.class || type == Character.class) {
field.set(t, CharUtils.toChar(value));
} else if (type == boolean.class) {
field.set(t, BooleanUtils.toBoolean(value));
} else if (type == BigDecimal.class) {
field.set(t, new BigDecimal(value));
}
} else if (type == Boolean.class) {
field.set(t, BooleanUtils.toBoolean(value));
} else if (type == Date.class) {
field.set(t, value);
} else if (type == String.class) {
if (value.endsWith(".0")) {
value = StringUtils.substringBefore(value, ".0");
}
if (value.endsWith(" 00:00:00")) {
value = StringUtils.substringBefore(value, " 00:00:00");
}
field.set(t, value);
} else {
Constructor<?> constructor = type.getConstructor(String.class);
field.set(t, constructor.newInstance(value));
}
}
private static String getCellValue(Cell cell) {
if (cell == null) {
return "";
}
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
if (HSSFDateUtil.isCellDateFormatted(cell)) {
short format = cell.getCellStyle().getDataFormat();
System.out.println("format:" + format + ";;;;;value:" + cell.getNumericCellValue());
SimpleDateFormat sdf = null;
if (format == 14 || format == 31 || format == 57
|| format == 58 || (176 <= format && format <= 178)
|| (182 <= format && format <= 196)
|| (210 <= format && format <= 213) || (208 == format)) { // 日期
sdf = new SimpleDateFormat("yyyy-MM-dd");
} else if (format == 20 || format == 32 || format == 183
|| (200 <= format && format <= 209)) { // 时间
sdf = new SimpleDateFormat("HH:mm");
} else { // 不是日期格式
return String.valueOf(cell.getNumericCellValue());
}
double value = cell.getNumericCellValue();
Date date = org.apache.poi.ss.usermodel.DateUtil
.getJavaDate(value);
if (date == null || "".equals(date)) {
return "";
}
String result = "";
try {
result = sdf.format(date);
} catch (Exception e) {
e.printStackTrace();
return "";
}
return result;
} /*else if (String.valueOf(cell.getNumericCellValue()).indexOf("E") == -1) {
return String.valueOf(cell.getNumericCellValue());
} */else {
/*return new DecimalFormat("#")
.format(cell.getNumericCellValue());*/
return String.valueOf(cell.getNumericCellValue());
}
/*
* // 日期处理(目前不能处理包含中文的日期类型) if
* (HSSFDateUtil.isCellDateFormatted(cell)) { return
* HSSFDateUtil.getJavaDate(cell.getNumericCellValue()) .toString();
* } else { return new
* BigDecimal(cell.getNumericCellValue()).toString(); }
*/
} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
if (cell.getStringCellValue().contains("年") && cell.getStringCellValue().contains("月")&& cell.getStringCellValue().contains("日")) {
return DateUtil.parseDateToString(DateUtil.parseStringToDate(
cell.getStringCellValue(), "yyyy年MM月dd日HH:mm:ss"),
"yyyy-MM-dd");
} else if (StringUtil.count(cell.getStringCellValue(), "-") >= 2) {
return DateUtil.parseDateToString(DateUtil.parseStringToDate(
cell.getStringCellValue(), "yyyy-MM-dd"), "yyyy-MM-dd");
}
return StringUtils.trimToEmpty(cell.getStringCellValue());
} else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
return StringUtils.trimToEmpty(cell.getCellFormula());
} else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
return "";
} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
return String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
return "ERROR";
} else {
return cell.toString().trim();
}
}
/**
* 浏览器下载excel
*
* @param fileName
* @param wb
* @param response
*/
private static void buildExcelDocument(String fileName, Workbook wb,
HttpServletResponse response) {
try {
response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
response.setHeader("Content-Disposition", "attachment;filename="
+ URLEncoder.encode(fileName, "utf-8"));
response.flushBuffer();
wb.write(response.getOutputStream());
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 生成excel文件
*
* @param path
* 生成excel路径
* @param wb
*/
@SuppressWarnings("unused")
private static void buildExcelFile(String path, Workbook wb) {
File file = new File(path);
if (file.exists()) {
file.delete();
}
try {
wb.write(new FileOutputStream(file));
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 渠道结算导入模板
* <p>
* Title: writeUAccountExcel
* </p>
* <p>
* Description:
* </p>
*
* @param response
*/
public static <T> void writeUAccountExcel(HttpServletResponse response) {
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet("Sheet1");
// 写入表头
XSSFRow row = sheet.createRow(0);
// 设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
sheet.setColumnWidth(0, 20 * 256);
sheet.setColumnWidth(1, 20 * 256);
sheet.setColumnWidth(2, 10 * 256);
sheet.setColumnWidth(3, 20 * 256);
sheet.setColumnWidth(4, 10 * 256);
CreationHelper createHelper = wb.getCreationHelper();
XSSFFont xssfFont = wb.createFont();
xssfFont.setColor(org.apache.poi.ss.usermodel.Font.COLOR_RED);
CellStyle redFontStyle = wb.createCellStyle();
redFontStyle.setFont(xssfFont);
CellStyle DateStyle = wb.createCellStyle();
DateStyle.setFont(xssfFont);
DateStyle.setDataFormat(createHelper.createDataFormat().getFormat(
"yyyy-MM-dd"));
XSSFCell cell;
cell = row.createCell(0);
cell.setCellValue("保单号");
cell.setCellStyle(redFontStyle);
cell = row.createCell(1);
cell.setCellValue("险种代码");
cell.setCellStyle(redFontStyle);
cell = row.createCell(2);
cell.setCellValue("缴费期次");
cell.setCellStyle(redFontStyle);
cell = row.createCell(3);
cell.setCellValue("供应商编码");
cell.setCellStyle(redFontStyle);
cell = row.createCell(4);
cell.setCellValue("结算日期");
cell.setCellStyle(redFontStyle);
// 冻结窗格
wb.getSheet("Sheet1").createFreezePane(0, 1, 0, 1);
// 浏览器下载excel
buildExcelDocument("上游结算导入.xlsx", wb, response);
// 生成excel文件
// buildExcelFile("供应商费率导入.xlsx",wb);
}
}
5.Controller层代码编写
/**
* 导出模板
* <p>Title: exportExcel</p>
* <p>Description: </p>
* @param response
* @throws IOException
*/
@GetMapping(value = "/exportExcel")
public void exportExcel(HttpServletResponse response) throws IOException {
long t1 = System.currentTimeMillis();
ExcelUtils.writeRiskConExcel(response);
long t2 = System.currentTimeMillis();
System.out.println(String.format("write over! cost:%sms", (t2 - t1)));
}
/**
* excel导入
*/
@PostMapping(value = "/readExcel")
public Result readExcel(MultipartFile file){
long t1 = System.currentTimeMillis();
List<RiskCon> list;
try {
list = ExcelUtils.readExcel("", RiskCon.class, file);
if(list.size()>0){
Map<String, Object> uRiskListMap = new HashMap<String, Object>();
Map<String, Object> dRiskListMap = new HashMap<String, Object>();
Map<String, Object> uAccountMap = new HashMap<String, Object>();
Map<String, Object> dAccountMap = new HashMap<String, Object>();
if(list.size()>1){
for (int i = 1; i < list.size(); i++) {
if(list.get(i).equals(list.get(0))){
return new Result(417,null,"导入的数据存在重复数据,请检查导入的数据");
}
}
}
for (RiskCon riskCon : list) {
//获取供应商编码&&险种代码
if(riskCon.getComid()!=null&&riskCon.getComid()!=""&&
riskCon.getClasscode()!=null&&riskCon.getClasscode()!=""&&
riskCon.getChid()!=null&&riskCon.getChid()!=""){
uRiskListMap.put("supplierId", riskCon.getComid());
uRiskListMap.put("classCode", riskCon.getClasscode());
dRiskListMap.put("comid", riskCon.getComid());
dRiskListMap.put("chid", riskCon.getChid());
dRiskListMap.put("classCode", riskCon.getClasscode());
uAccountMap.put("comid", riskCon.getComid());
uAccountMap.put("policyno", riskCon.getOrderno());
uAccountMap.put("classCode", riskCon.getClasscode());
uAccountMap.put("years", riskCon.getYears());
dAccountMap.put("chid", riskCon.getChid());
dAccountMap.put("policyno", riskCon.getOrderno());
dAccountMap.put("classCode", riskCon.getClasscode());
dAccountMap.put("years", riskCon.getYears());
}
if(!(riskConService.addCheckNo(riskCon.getOrderno())>0)){
if(riskConService.addCheckEmpno(riskCon.getEmpno())>0){
if(uRiskListService.addCheck(uRiskListMap)>0){
if(dRiskListService.addCheck(dRiskListMap)>0){
if(uAccountService.getUAccountByOrder(uAccountMap)!=null){
if(dAccountService.getDAccountByOrder(dAccountMap)!=null){
riskCon.setCreator(TokenUtils.getUserID());
riskCon.setCreatdate(DateUtil.getNow());
riskCon.setIsdelete(0);
}else{
return new Result(417,null,"该保单已经完成渠道对账,请检查导入数据");
}
}else{
return new Result(417,null,"该保单已经完成供应商对账,请检查导入数据");
}
}else{
return new Result(417,null,"找不到对应的渠道产品,请检查导入的渠道编码和险种代码");
}
}else{
return new Result(417,null,"找不到对应的供应商产品,请检查导入的供应商编码和险种代码");
}
}else{
return new Result(417,null,"导入的业务员工号无效,请检查导入的数据");
}
}else{
return new Result(417,null,"导入的保单号已存在,请检查导入的数据");
}
}
riskConService.saveBath(list);
long t2 = System.currentTimeMillis();
System.out.println(String.format("read over! cost:%sms", (t2 - t1)));
return new Result(200,null,"导入成功");
}else{
return new Result(417,null,"导入的数据为空,请检查导入的数据");
}
} catch (Exception e) {
e.printStackTrace();
return new Result(417,null,"导入失败");
}
}
网友评论