首先添加maven 依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.6</version>
</dependency>
编写工具类
package com.baking.baking.util;
import com.baking.baking.entity.Goods;
import com.baking.baking.entity.vo.GoodsExecl;
import com.baking.baking.zander.exceptions.BusinessException;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
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 java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
public class GoodsExcelUtil {
private static final Logger LOGGER = LogManager.getLogger(GoodsExcelUtil.class);
/**
* 导入excel
*
* @param file
* @param projectId
* @return
*/
public static List<GoodsExecl> excelImport(File file, int projectId) {
//获取文件名称
String fileOriginalFilename = file.getName();
String fileType = fileOriginalFilename.substring(fileOriginalFilename.lastIndexOf("."));
String tableName = file.getName();
List<GoodsExecl> resultList = new ArrayList<>();
// 仅支持.xlsx格式
if (!fileOriginalFilename.matches("^.+\\.(?i)(xlsx)$")) {
LOGGER.info("文件全名称,{}", fileOriginalFilename);
throw new BusinessException("文件格式不正確,只支持XLSX格式");
}
try {
FileInputStream in = new FileInputStream(file);
resultList = parseExcel(in, fileOriginalFilename, projectId);
} catch (Exception e) {
e.printStackTrace();
}
return resultList;
}
public static List<GoodsExecl> parseExcel(InputStream in, String fileName, Integer projectId) throws Exception {
List<GoodsExecl> resultList = new ArrayList<>();
Workbook work = getWorkbook(in, fileName);
if (null == work) {
throw new BusinessException("excel為空");
}
Sheet sheet = null;
Row row = null;
Cell cell = null;
for (int i = 0; i < work.getNumberOfSheets(); i++) {
sheet = work.getSheetAt(i);
if (sheet == null) {
continue;
}
// 獲取總行數
int totalRows = sheet.getPhysicalNumberOfRows();
//每一行的单元格数
int totalCells = 0;
for (int n = 0; n < totalRows; n++) {
//遍历单元格
row = sheet.getRow(n);
//获取每一行的单元格数
totalCells = row.getPhysicalNumberOfCells();
//循环设置每个单元格的数据类型为String类型
for (int j = 0; j < totalCells; j++) {
if (row.getCell(j) != null) {
row.getCell(j).setCellType(Cell.CELL_TYPE_STRING);
}
}
row = sheet.getRow(0);
if (!row.getCell(0).getStringCellValue().equals("商品編號")) {
throw new BusinessException("文件內容與模板不一致");
}
for (int j = 1; j < sheet.getLastRowNum() + 1; j++) {
row = sheet.getRow(j);
String key = row.getCell(0).getStringCellValue();
if (key.equals("商品編號") && key.equals("")) {
continue;
}
String goodsCode = row.getCell(0).getStringCellValue();
String goodsName = row.getCell(1).getStringCellValue();
String enGoodsName = row.getCell(2).getStringCellValue();
String classCode = row.getCell(3).getStringCellValue();
String goodsImg1 = "xxxxxxx/baking_service/goods_img/" + row.getCell(0).getStringCellValue() + ".xxxx";
String goodsNote = row.getCell(4).getStringCellValue();
String enGoodsNote = row.getCell(5).getStringCellValue();
String goodsAddress = row.getCell(6).getStringCellValue();
String enGoodsAddress = row.getCell(7).getStringCellValue();
GoodsExecl goods = new GoodsExecl(
goodsCode,goodsName,enGoodsName,
classCode,goodsImg1,
goodsNote,enGoodsNote,goodsAddress,enGoodsAddress,
projectId);
resultList.add(goods);
}
return resultList;
}
}
LOGGER.info("处理excel获取的数据,{}", "");
return null;
}
public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
Workbook wb = null;
String fileType = fileName.substring(fileName.lastIndexOf("."));
if (".xls".equals(fileType)) {
wb = new HSSFWorkbook(inStr); // 2003-
} else if (".xlsx".equals(fileType)) {
wb = new XSSFWorkbook(inStr); // 2007+
} else {
throw new Exception("解析的文件格式有误!");
}
return wb;
}
}
Controller 调用
String originalFilename = file.getOriginalFilename();
String newFileName = UUID.randomUUID().toString().replaceAll("-", "")
+ originalFilename.substring(originalFilename.lastIndexOf("."));
File newFile = new File(projectConfig.getBakingFile() + File.separator + "/excel/" + File.separator + newFileName);
//判断目标文件所在的目录是否存在
if (!newFile.getParentFile().exists()) {
//如果目标文件所在的目录不存在,则创建父目录
newFile.getParentFile().mkdirs();
}
file.transferTo(newFile);
List<GoodsExecl> resultList = GoodsExcelUtil.excelImport(newFile, projectId);
List<Goods> goodsList = new ArrayList<>();
StringBuilder sb = new StringBuilder();
int successFlag = 0;
int failureFlag = 0;
for (GoodsExecl goodsExecl : resultList) {
Goods goods = new Goods();
if (goodsService.check(goodsExecl.getGoodsCode(), projectId)) {
goods.setGoodsCode(goodsExecl.getGoodsCode());
goods.setGoodsName(goodsExecl.getGoodsName());
goods.setEnGoodsName(goodsExecl.getEnGoodsName());
GoodsClass goodsClass = goodsClassService.selectByCode(goodsExecl.getClassCode(), projectId);
System.out.println(goodsClass.getId());
goods.setGoodsClassId(goodsClass.getId());
goods.setGoodsImg1(goodsExecl.getGoodsImg1());
goods.setGoodsNote(goodsExecl.getGoodsNote());
goods.setEnGoodsNote(goodsExecl.getEnGoodsNote());
goods.setGoodsAddress(goodsExecl.getGoodsAddress());
goods.setEnGoodsAddress(goodsExecl.getEnGoodsAddress());
goods.setProjectId(projectId);
successFlag++;
goodsList.add(goods);
} else {
failureFlag++;
sb.append(goodsExecl.getGoodsCode() + "丶");
}
}
if (goodsList.size() > 0) {
goodsService.insertBatch(goodsList);
} else {
return renderError("數據庫已經存在該Excel上所有商品信息,無法再次導入");
}
if (failureFlag == 0) {
return renderSuccess("執行成功,成功導入" + successFlag + "條,失敗" + failureFlag + "條");
} else {
return renderSuccess("執行成功,成功導入" + successFlag + "條," +
"失敗" + failureFlag + "條,導入失敗商品編號如下:" + sb.toString());
}