- Excel导入模板类
package com.golte.dataform.analysis.controller;
import com.alibaba.fastjson.JSON;
import com.golte.common.GlobalResponse;
import com.golte.dataform.analysis.vo.response.project.ProjectbaseInfoResp;
import com.golte.utils.DateUtils;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Objects;
public class ProjectBasicInfoExcel {
private static String getValue(XSSFCell xssfCell) {
if (Objects.isNull(xssfCell)) {
return null;
}
if (xssfCell.getCellType() == CellType.BOOLEAN) {
return String.valueOf(xssfCell.getBooleanCellValue());
}
if (xssfCell.getCellType() == CellType.NUMERIC) {
return String.valueOf(xssfCell.getNumericCellValue());
}
return String.valueOf(xssfCell);
}
@GetMapping("/downTemplate")
@ApiOperation("下载模板")
public void downTemplate(HttpServletResponse response) throws IOException {
String time = DateUtils.dateTime2Str(DateUtils.DATE_PATTERN_DEFAULT, new Date());
String fileUrl = "test.xlsx";
ServletOutputStream out = null;
InputStream iputstream = null;
try {
//读取文件服务器模板
URL url = new URL(fileUrl);
HttpURLConnection uc = (HttpURLConnection) url.openConnection();
uc.setDoInput(true); //设置是否要从 URL 连接读取数据,默认为true
uc.connect();
iputstream = uc.getInputStream();
String fileName = time + "基础信息导入模板.xls";
String[] array = fileName.split("[.]");
String fileType = array[array.length - 1].toLowerCase();
//设置文件ContentType类型
if ("jpg,jepg,gif,png".contains(fileType)) {//图片类型
response.setContentType("image/" + fileType);
} else if ("pdf".contains(fileType)) {//pdf类型
response.setContentType("application/pdf");
} else {//自动判断下载文件类型
response.setContentType("multipart/form-data");
}
response.setContentType("application/octet-stream;charset=utf-8");
//设置文件头：最后一个参数是设置下载文件名
response.setHeader("Content-Disposition",
"attachment;fileName=" + URLEncoder.encode(fileName, "UTF-8"));
out = response.getOutputStream();
// 读取文件流
int len;
byte[] buffer = new byte[1024 * 10];
while ((len = iputstream.read(buffer)) != -1) {
out.write(buffer, 0, len);
}
if (Objects.nonNull(out)) {
out.flush();
}
} catch (IOException e) {
} finally {
try {
if (Objects.nonNull(out)) {
out.close();
}
if (Objects.nonNull(iputstream)) {
iputstream.close();
}
} catch (IOException e) {
}
}
}
@PostMapping("/excelimport")
@ApiOperation("项目基础信息")
public GlobalResponse> excelimport(@RequestParam("file") MultipartFile file) {
//查询数据库中是否存在数据
Integer count = 0; //mapper.selectCount()
String projectId;
List msg = new ArrayList<>();
ProjectbaseInfoResp projectbaseInfo;
XSSFWorkbook xssfWorkbook = null;
XSSFCell cell = null;
try {
xssfWorkbook = new XSSFWorkbook(file.getInputStream());
} catch (IOException e) {
e.printStackTrace();
}
//获取工作簿中的第一个Sheet
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
//获取第一个Sheet,从第二行开始遍历获取每一行数据
for (int rowNum = 2; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
//获取第 rowNum 行对象
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
Class projectbaseInfoRespClass = ProjectbaseInfoResp.class;
try {
//对象字段必须与excel表格字段顺序一致
projectbaseInfo = projectbaseInfoRespClass.newInstance();
Field[] fields = projectbaseInfoRespClass.getDeclaredFields();
for (int i = 0; i < fields.length; i++) {
cell = xssfSheet.getRow(1).getCell(i);
Field field = fields[i];
field.setAccessible(true);
field.set(projectbaseInfo, getValue(xssfRow.getCell(i)));
log.info("字段名称：{}", cell);
}
log.info("解析对象属性值：{}", JSON.toJSONString(projectbaseInfo));
} catch (Exception e) {
msg.add("项目ID: [ " + xssfRow.getCell(0).toString() + " ] 解析失败! 请检查字段 [ " + cell + " ] 数据是否正确");
e.printStackTrace();
continue;
}
//取第一行第一个格的值
projectId = getValue(xssfRow.getCell(0));
//数据库表中没有数据的时候直接插入所有excel不做校验
if (Objects.isNull(count) || count.intValue() == 0) {
log.info("插入：{}",projectId);
continue;
}
//根据 projectId 去数据库查询项目
//mapper.selectById(projectId);
//查询为空插入数据
if (Objects.isNull(projectId)) {
log.info("插入：{}",projectId);
continue;
}
//如果不为空并且字段有变化则更新数据
String oldObject = JSON.toJSONString(projectId);
String newObject = JSON.toJSONString(projectbaseInfo);
if (Objects.nonNull(projectId) && !oldObject.equals(newObject)) {
log.info("更新：{}n更新前: {}n更新后: {}",projectId,oldObject,newObject);
}
}
if (msg.size() == 0) {
msg.add("已完全同步Excel");
}
return GlobalResponse.ok(msg);
}
}
##通用方法体
private static String getValue(XSSFCell xssfCell) {
if (Objects.isNull(xssfCell)) {
return null;
}
if (xssfCell.getCellType() == CellType.BOOLEAN) {
return String.valueOf(xssfCell.getBooleanCellValue());
}
if (xssfCell.getCellType() == CellType.NUMERIC) {
return String.valueOf(xssfCell.getNumericCellValue());
}
return String.valueOf(xssfCell);
}
public static List excelTemplate(Integer rowNum,Integer sheetIndex,Class clz, XSSFWorkbook xssfWorkbook) {
List excelList = new ArrayList();
//获取第一个Sheet
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(sheetIndex);
//第 rowNum 开始遍历获取每一行数据
for (; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
//获取第 rowNum 行对象
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
try {
//对象字段必须与excel表格字段顺序一致
T obj = (T) clz.newInstance();
Field[] fields = clz.getDeclaredFields();
for (int i = 0; i < fields.length; i++) {
Field field = fields[i];
field.setAccessible(true);
field.set(obj, getValue(xssfRow.getCell(i)));
}
excelList.add(obj);
} catch (Exception e) {
e.printStackTrace();
}
}
//返回Excel解析后的对象集合
return excelList;
}
public staticList excelTemplate(Integer rowNum,Integer sheetIndex,Class clz, XSSFWorkbook xssfWorkbook) { List excelList = new ArrayList(); List msg = new ArrayList(); XSSFCell cell = null; //获取第一个Sheet XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(sheetIndex); //第 rowNum 开始遍历获取每一行数据 for (; rowNum <= xssfSheet.getLastRowNum(); rowNum++) { //获取第 rowNum 行对象 XSSFRow xssfRow = xssfSheet.getRow(rowNum); try { //对象字段必须与excel表格字段顺序一致 T obj = (T) clz.newInstance(); Field[] fields = clz.getDeclaredFields(); for (int i = 0; i < fields.length; i++) { cell = xssfSheet.getRow(1).getCell(i); Field field = fields[i]; field.setAccessible(true); field.set(obj, getValue(xssfRow.getCell(i))); log.info("字段名称：{}", cell); } log.info("解析对象属性值：{}", JSON.toJSONString(obj)); excelList.add(obj); } catch (Exception e) { e.printStackTrace(); msg.add("项目ID: [ " + xssfRow.getCell(0).toString() + " ] 解析失败! 请检查字段 [ " + cell + " ] 数据是否正确"); } } log.info("{}", Arrays.toString(msg.toArray())); return excelList; }
