控制层
鉴于导入Excel的大小不同等，都可以在Controller层记性设置判定
/**
* @return
* @Author Mr.Wang
* @Description //TODO 导入报表到数据库
* @Date 17:54 2019/7/17
* @Param
**/
@RequestMapping(value = "/import")
public RspMsg fileImport(@RequestParam(value = "fileDate", required = false) MultipartFile fileDate) {
try {
if (!checkFileSize(fileDate, 1048575, "B")) {
logger.error("上传文件过大");
}
String fileName = fileDate.getOriginalFilename();
return excelImportService.getExcelOrder(fileName, fileDate);
} catch (Exception e) {
e.printStackTrace();
return RspMsg.createRspMsg(MsgConst.CODE_EXCEL_EXPORT_FAIL, MsgConst.MSG_CODE_EXCEL_EXPORT_FAIL, new ArrayList<>());
}
}
public boolean checkFileSize(MultipartFile multipartFile, int size, String unit) {
long len = multipartFile.getSize();
double fileSize = 0;
if ("B".equals(unit.toUpperCase())) {
fileSize = (double) len;
} else if ("K".equals(unit.toUpperCase())) {
fileSize = (double) len / 1024;
} else if ("M".equals(unit.toUpperCase())) {
fileSize = (double) len / 1048576;
} else if ("G".equals(unit.toUpperCase())) {
fileSize = (double) len / 1073741824;
}
//如果上传文件大于限定的容量
if (fileSize > size) {
return false;
}
return true;
}
业务层
按Excel导入格式后期进行微调就好了
/**
* @return
* @Author Mr.Wang
* @Description //TODO 订单报表导入
* @Date 17:53 2019/7/17
* @Param
**/
@Override
public RspMsg getExcelOrder(String fileName, MultipartFile file) throws IOException {
boolean notNull = false;
List<DmOrderExcel> dmOrderExcelList = new ArrayList<>();
if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
return RspMsg.createRspMsg(MsgConst.CODE_EXCEL_EXPORT_ERROR, MsgConst.MSG_CODE_EXCEL_EXPORT_ERROR, new ArrayList<>());
}
boolean isExcel2003 = true;
if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
isExcel2003 = false;
}
InputStream is = file.getInputStream();
Workbook wb = null;
if (isExcel2003) {
wb = new HSSFWorkbook(is);
} else {
wb = new XSSFWorkbook(is);
}
Sheet sheet = wb.getSheetAt(0);
if (sheet != null) {
notNull = true;
}
DmOrderExcel dmOrderExcel;
for (int r = 1; r <= sheet.getLastRowNum(); r++) {//r = 2 表示从第三行开始循环 如果你的第三行开始是数据
Row row = sheet.getRow(r);//通过sheet表单对象得到 行对象
if (row == null && row.equals("")) {
continue;
}
//sheet.getLastRowNum() 的值是 10，所以Excel表中的数据至少是10条；不然报错 NullPointerException
dmOrderExcel = new DmOrderExcel();
if (row.getCell(0).getCellType() != 1) {//循环时，得到每一行的单元格进行判断
logger.info("导入失败(第" + (r + 1) + "行,用户名请设为文本格式)");
}
//订单id
row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
String orderId = row.getCell(0).getStringCellValue();//得到每一行第一个单元格的值
if (orderId == null || orderId == "") {//判断是否为空
logger.info("导入失败(第" + (r + 1) + "行,登记日期未填写)");
break;
}
//商品id
row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);//得到每一行的 第二个单元格的值
String itemId = row.getCell(1).getStringCellValue();
if (itemId == null || itemId.isEmpty()) {
logger.info("导入失败(第" + (r + 1) + "行,用户名未填写)");
break;
}
//用户id
row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);//得到每一行的 第三个单元格的值
String UserId = row.getCell(2).getStringCellValue();
if (UserId == null || UserId.isEmpty()) {
logger.info("导入失败(第" + (r + 1) + "行,手机号未填写)");
}
//返利积分
row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);//得到每一行的 第四个单元格的值
String backPoint = row.getCell(3).getStringCellValue();
if (backPoint == null || backPoint.isEmpty()) {
logger.info("导入失败(第" + (r + 1) + "行,城市未填写)");
}
//订单价格
row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);//得到每一行的 第五个单元格的值
String orderPrice = row.getCell(4).getStringCellValue();
if (orderPrice == null || orderPrice.isEmpty()) {
logger.info("导入失败(第" + (r + 1) + "行,状态未填写)");
}
//订单状态
row.getCell(5).setCellType(Cell.CELL_TYPE_STRING);//得到每一行的 第五个单元格的值
String orderType = row.getCell(5).getStringCellValue();
if (orderType == null || orderType.isEmpty()) {
logger.info("导入失败(第" + (r + 1) + "行,状态未填写)");
}
//结束日期
Date expireDate = row.getCell(6).getDateCellValue();
if (expireDate == null) {
logger.info("导入失败(第" + (r + 1) + "行,说明未填写)");
}
//创建日期
Date createDate = row.getCell(7).getDateCellValue();
if (createDate == null) {
logger.info("导入失败(第" + (r + 1) + "行,收款金额未填写)");
}
//付款日期
Date PayDate = row.getCell(8).getDateCellValue();
if (PayDate == null) {
logger.info("导入失败(第" + (r + 1) + "行,收款金额未填写)");
}
//订单类型
row.getCell(9).setCellType(Cell.CELL_TYPE_STRING);//得到每一行的 第五个单元格的值
String orderTriage = row.getCell(9).getStringCellValue();
if (orderTriage == null || orderTriage.isEmpty()) {
logger.info("导入失败(第" + (r + 1) + "行,状态未填写)");
}
//订单名称
row.getCell(10).setCellType(Cell.CELL_TYPE_STRING);//得到每一行的 第五个单元格的值
String title = row.getCell(10).getStringCellValue();
if (title == null || title.isEmpty()) {
logger.info("导入失败(第" + (r + 1) + "行,状态未填写)");
}
//转换格式
// TODO: 根据插入数据的MySQL数据类型进行转型，这个看自己啦
orderId = sbOrder.toString();
orderType = sbType.toString();
orderTriage = sbTriage.toString();
//完整的循环一次 就组成了一个对象
dmOrderExcel.setOrderId(orderId);//订单id
dmOrderExcel.setItemId(itemId);//商品id
dmOrderExcel.setUserId(UserId);//用户id
dmOrderExcel.setBackPoint(backPoint);//返利积分
dmOrderExcel.setOrderPrice(orderPrice);//订单单价
dmOrderExcel.setOrderType(orderType);//订单类型
dmOrderExcel.setExpireDate(expireDate);//结束日期
dmOrderExcel.setCreateDate(createDate);//创建日期
dmOrderExcel.setPayDate(PayDate);//付款日期
dmOrderExcel.setOrderTriage(orderTriage);//订单分类
dmOrderExcel.setTitle(title);//订单标题
if (orderId.equals("")) {
break;
}
dmOrderExcelList.add(dmOrderExcel);
}
for (DmOrderExcel orderExcel : dmOrderExcelList) {
int count = dmExcelUserExtMapper.addOrder(orderExcel);
if (count == 1) {
logger.info("导入数据成功: {}", count);
}
}
return RspMsg.createRspMsg(MsgConst.CODE_EXCEL_EXPORT_SUCCESS, MsgConst.MSG_CODE_EXCEL_EXPORT_SUCCESS, new ArrayList<>());
}
根据表头内容拿去
Controller
@RequestMapping(value = "/readExcel")
public String readExcel(@RequestParam(value = "fileDate", required = false) MultipartFile fileDate) throws Exception {
/**
*动态通过表头拿去数据
*nameKey
*/
String nameKey = "姓名";
System.out.println("/readExcel/");
String filePath = "F:\\student.xls";
String fileName = fileDate.getOriginalFilename();
List<Map<String, String>> mapList = ExcelUtil.readExcel(fileName, fileDate);
log.info("xls mapList:" + mapList);
for (int i = 0; i < mapList.size(); i++) {
Map<String, String> map = mapList.get(i);
for (String key : map.keySet()) {
log.info(key + "==" + map.get(key));
}
}
System.out.println("/readExcel 111 增加/");
if (fileName.equals("student1.xlsx"))
readExcel1(nameKey, fileName, fileDate);
System.out.println("/readExcel 222 缺少/");
if (fileName.equals("student2.xlsx"))
readExcel2(nameKey, fileName, fileDate);
public void readExcel1(String nameKey, String fileName, MultipartFile fileDate) {
String filePath = "F:\\student1.xlsx";
System.out.println(fileName);
List<Map<String, String>> mapList = ExcelUtil.readExcel(fileName, fileDate);
// log.info("xlsx mapList:" + mapList);
for (int i = 0; i < mapList.size(); i++) {
Map<String, String> map = mapList.get(i);
for (String key : map.keySet()) {
log.info(key + "==" + map.get(key));
if (key.equals(nameKey)) {
log.info("我要=" + key + "==" + map.get(key));
}
}
}
}
public void readExcel2(String nameKey, String fileName, MultipartFile fileDate) {
String filePath = "F:\\student2.xlsx";
System.out.println(fileName);
List<Map<String, String>> mapList = ExcelUtil.readExcel(fileName, fileDate);
// log.info("xlsx mapList:" + mapList);
for (int i = 0; i < mapList.size(); i++) {
Map<String, String> map = mapList.get(i);
for (String key : map.keySet()) {
log.info(key + "==" + map.get(key));
if (key.equals(nameKey)) {
log.info("我要=" + key + "==" + map.get(key));
}
}
}
}
Util
/**
* 从excel中读内容
*
* @param
* @param
* @return
*/
public static List<Map<String, String>> readExcel(String fileName, MultipartFile fileDate) {
List<Map<String, String>> dataList = new ArrayList<>();
Sheet sheet = ExcelUtil.createWorkBook(fileName, fileDate);
if (sheet != null) {
int maxRownum = sheet.getPhysicalNumberOfRows();
Row firstRow = sheet.getRow(0);
int maxColnum = firstRow.getPhysicalNumberOfCells();
String columns[] = new String[maxColnum];
for (int i = 0; i < maxRownum; i++) {
Map<String, String> map = null;
if (i > 0) {
map = new LinkedHashMap<>();
firstRow = sheet.getRow(i);
}
if (firstRow != null) {
String cellData = null;
for (int j = 0; j < maxColnum; j++) {
cellData = (String) ExcelUtil.getCellFormatValue(firstRow.getCell(j));
if (i == 0) {
columns[j] = cellData;
} else {
map.put(columns[j], cellData);
}
}
} else {
break;
}
if (i > 0) {
dataList.add(map);
}
}
}
return dataList;
}
private static Sheet createWorkBook(String fileName, MultipartFile filePath) {
boolean notNull = false;
boolean isExcel2003 = true;
XSSFWorkbook wb = null;
HSSFWorkbook wh = null;
Sheet sheet = null;
try {
InputStream is = filePath.getInputStream();
if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
isExcel2003 = false;
} else {
isExcel2003 = true;
}
if (isExcel2003) {
wh = new HSSFWorkbook(is);
sheet = wh.getSheetAt(0);
} else {
wb = new XSSFWorkbook(is);
sheet = wb.getSheetAt(0);
}
if (sheet != null) {
notNull = true;
}
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
return sheet;
}
/**
* 将字段转为相应的格式
* @param cell
* @return
*/
private static Object getCellFormatValue(Cell cell) {
Object cellValue = null;
if (cell != null) {
//判断cell类型
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC: {
cellValue = String.valueOf(cell.getNumericCellValue());
break;
}
case Cell.CELL_TYPE_FORMULA: {
if (DateUtil.isCellDateFormatted(cell)) {
cellValue = cell.getDateCellValue();转换为日期格式YYYY-mm-dd
} else {
cellValue = String.valueOf(cell.getNumericCellValue()); //数字
}
break;
}
case Cell.CELL_TYPE_STRING: {
cellValue = cell.getRichStringCellValue().getString();
break;
}
default:
cellValue = "";
}
} else {
cellValue = "";
}
return cellValue;
}
Entity
@Data
public class Student {
private String studentName;
private String sex;
private Integer age;
private String school;
private String grade;
}
表
1、Excel的自定义时间格式转换
SimpleDateFormat simpleDateFormat = new SimpleDateFormat(
"yyyy-MM-dd HH:mm:ss");
SimpleDateFormat simpleDateFormatH = new SimpleDateFormat(
"HH:mm:ss");
SimpleDateFormat simpleDateFormatD = new SimpleDateFormat(
"yyyy-MM-dd");
//咨询时间 yyyy/mm/dd
row.getCell(9).setCellType(Cell.CELL_TYPE_NUMERIC);
double consultingDateTimey = row.getCell(9).getNumericCellValue();
//咨询时间 hh:mm
row.getCell(10).setCellType(Cell.CELL_TYPE_NUMERIC);
double consultingDateTimeh = row.getCell(10).getNumericCellValue();
//转换 yyyy-mm-dd
Date years = getJavaDate(consultingDateTimey);
String year = simpleDateFormatD.format(years);
//转换 hh:mm
Date hours = getJavaDate(consultingDateTimeh);
String hour = simpleDateFormatH.format(hours);
The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF)
（1）判断文件后缀名是xls，还是xlsx
（2）如果是xls，使用HSSFWorkbook；如果是xlsx，使用XSSFWorkbook
if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
resData.setResInfo(RespCode.KuaiSheng.ERROR_KUAISHANG_ERROR, RespCode.KuaiSheng.ERROR_KUAISHANG_MSG);
return resData;
}
boolean isExcel2003 = true;
if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
isExcel2003 = false;
} else {
isExcel2003 = true;
}
InputStream is = file.getInputStream();
XSSFWorkbook wb = null;
HSSFWorkbook wh = null;
Sheet sheet = null;
if (isExcel2003) {
wh = new HSSFWorkbook(is);
sheet = wh.getSheetAt(0);
} else {
wb = new XSSFWorkbook(is);
sheet = wb.getSheetAt(0);
}
if (sheet != null) {
notNull = true;
}
2378
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
