Springboot之Excel表格导出
表格导出使用的还是POI,POI的介绍请查看
https://blog.csdn.net/qq_36654629/article/details/90172911
使用前需引入poi相关依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>RELEASE</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>RELEASE</version>
</dependency>
表格导出工具
import org.apache.poi.hssf.usermodel.*;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Date;
import java.util.List;
@Service
@Transactional(rollbackFor = Throwable.class)
public class GoodsExportService {
/**
* @param sheetName sheet标签名称,headers第一行标题名称,list 数据
* @author
* @description excel 导出
* @date 2019-06-27
* @throws IOException
*/
public void exportTemplate(HttpServletResponse response, String sheetName, String[] headers, List list) throws IOException {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet(sheetName);
setTitle(headers,workbook, sheet);
//新增数据行,并且设置单元格数据
for(int i=0;i<list.size();i++){
List clist = (List)list.get(i);
HSSFRow hssfRow = sheet.createRow(i+1);
for(int j=0;j<clist.size();j++){
hssfRow.createCell(j).setCellValue((String)clist.get(j));
}
}
Date date = new Date();
String fileName =String.valueOf(date.getTime());
fileName = fileName + ".xls";
//清空response
response.reset();
//设置response的Header
response.setContentType("application/vnd.ms-exce;charset=GBK");
response.setCharacterEncoding("GBK");
response.setHeader("content-Disposition", "attachment;filename="+ java.net.URLEncoder.encode(new String(fileName.getBytes("GBK"),"ISO-8859-1"), "GBK"));
response.setDateHeader("Expires", 0);
OutputStream os = new BufferedOutputStream(response.getOutputStream());
//将excel写入到输出流中
workbook.write(os);
os.flush();
os.close();
}
// 创建表头
public void setTitle(String[] headers,HSSFWorkbook workbook, HSSFSheet sheet) {
HSSFRow row = sheet.createRow(0);
// 设置为居中加粗
HSSFCellStyle style = workbook.createCellStyle();
HSSFFont font = workbook.createFont();
style.setFont(font);
// 设置表格默认列宽度为15个字节
for (int i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell(i);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
cell.setCellStyle(style);
}
}
}
表格导入
@Service
@Transactional(rollbackFor = Throwable.class)
public class GoodsImportService {
@Value("${image.uploadPath}")
private String imageUploadPath;
@Autowired
private CustomGoodsUnitService customGoodsUnitService;
@Autowired
private CustomMerchantInfoService customMerchantInfoService;
@Autowired
private CustomGoodsCategoryService customGoodsCategoryService;
/**
* 批量导入商品专用
*
* @throws IOException
* @author liyueken
* @description excel 导入
* @date 2019-06-27
*/
public List analysisExcelByGoods(HttpServletRequest request, MultipartFile file, String userId, MerchantInfo info, Boolean updownState) throws IOException, InvalidFormatException {
Workbook wb = readExcel(request, file);//读取
Sheet sheet = null;
Row row = null;
List<Goods> list = null;
if (wb != null) {
//用来存放表中数据
list = new ArrayList<>();
//获取第一个sheet
sheet = wb.getSheetAt(0);
//获取最大行数
int rownum = sheet.getLastRowNum();
for (int i = 0; i < rownum; i++) {
//从第二行开始
row = sheet.getRow(i + 1);
if (row != null) {
//第一个单元格为空,定义为最后一行
if (row.getCell(0) == null) {
return list;
}
Goods goods = new Goods();
goods.setId(UUIDUtils.generator());
goods.setCreateUser(userId);
goods.setLastModifyUser(userId);
goods.setMerchantId(info.getId());
goods.setMerchantName(info.getCompanyName());
goods.setState(true);
goods.setGoodsType(GoodsTypeEnum.NORMAL.getCode());
//设置Excel数据读入实体
goods.setGoodsName(getCellFormatValue(row.getCell(0)));
if (row.getCell(1) != null) {
goods.setGoodsDescription(getCellFormatValue(row.getCell(1)));
}
if (row.getCell(2) == null) {
throw new GenericException(GoodsErrorEnum.GOODS_CATEGORY.getCode(), GoodsErrorEnum.GOODS_CATEGORY.getDesc());
}
//=================================================
//-1表示不限制库存
goods.setGoodsInventory(new BigDecimal("-1"));
goods.setGoodsInventoryType(GoodsInventoryTypeEnum.UNRESTRICTED.getCode());
String categoryName = getCellFormatValue(row.getCell(2));
boolean flag = true;
String categoryFullName = "";
String categoryFullId = "";
String[] split = categoryName.split(",");
GoodsCategory goodsCategory = customGoodsCategoryService.getByName(split[0]);
if (goodsCategory == null) {
//商品分类不存在异常
throw new GenericException(GoodsCategoryErrorEnum.CATEGORY_ISEMPTY.getCode(), GoodsCategoryErrorEnum.CATEGORY_ISEMPTY.getDesc());
}
goods.setCategoryId(goodsCategory.getId());
//todo 待定
categoryFullName = goodsCategory.getCategoryName();
categoryFullId = goodsCategory.getId();
String pid = goodsCategory.getPid();
while (flag) {
GoodsCategory bean = customGoodsCategoryService.getEqualsIdByPid(pid);
if (bean == null) {
flag = false;
} else {
categoryFullName = bean.getCategoryName() + "," + categoryFullName;
categoryFullId = bean.getId() + "," + categoryFullId;
pid = bean.getPid();
}
}
goods.setCategoryFullId(categoryFullId);
goods.setCategoryFullName(categoryFullName);
goods.setGoodsOriginalPrice(new BigDecimal(getCellFormatValue(row.getCell(3))));
if (row.getCell(4) == null) {
throw new GenericException(GoodsErrorEnum.GOODS_UNIT_ISEMPTY.getCode(), GoodsErrorEnum.GOODS_UNIT_ISEMPTY.getDesc());
}
String goodsUnit = getCellFormatValue(row.getCell(4));
//校验单位是否符合平台规范
GoodsUnit goodsUnit1 = customGoodsUnitService.checkName(goodsUnit);
if (goodsUnit1 == null) {
throw new GenericException(GoodsErrorEnum.GOODS_UNIT_ISEMPTY.getCode(), GoodsErrorEnum.GOODS_UNIT_ISEMPTY.getDesc());
}
//设置商品单位编码
goods.setGoodsUnit(goodsUnit);
goods.setGoodsUnitCode(goodsUnit1.getCode());
goods.setGoodsMinUnit(Integer.valueOf((row.getCell(5).getStringCellValue())));
goods.setGoodsSalesPrice(new BigDecimal(getCellFormatValue(row.getCell(6))));
goods.setAuditState(GoodsAuditStateEnum.AUDIT_OK.getCode());
goods.setIfShortage(false);//默认不缺货
goods.setUpdownState(updownState);
//0否1是 称重商品 默认false 0
goods.setIfWeighGoods(false);
if (row.getCell(9).getStringCellValue() == "1") {
goods.setIfWeighGoods(true);
}
goods.setSupplierId(row.getCell(10).getStringCellValue());
goods.setSupplierName(row.getCell(11).getStringCellValue());
try {
goods.setQualityGuaranteePeriod(DateUtils.format(row.getCell(12).getStringCellValue(),"yyyy/MM/dd"));
} catch (ParseException e) {
throw new GenericException(2011001,"日期转换异常");
}
if (row.getCell(13) != null) {
goods.setGoodsFirstPic(row.getCell(13).getStringCellValue());
} else {
throw new GenericException(GoodsErrorEnum.GOODS_FIRST_PIC_ISEMPTY_ISEMPTY.getCode(),
GoodsErrorEnum.GOODS_FIRST_PIC_ISEMPTY_ISEMPTY.getDesc());
}
list.add(goods);
} else {
break;
}
}
}
return list;
}
/**
* @param file 附件
* @return 文件名称
* @date 2019-6-27
* @author zl
* @description 读取附件
*/
public Workbook readExcel(HttpServletRequest request, MultipartFile file) throws IOException, InvalidFormatException {
String excelPath = upload(request, file);//上传excel文件
File excel = new File(excelPath);
InputStream in = new FileInputStream(excel);
String fileName = excel.getName();
Workbook wb = null;
if (excel.isFile() && excel.exists()) { //判断文件是否存在
InputStream is = in;
if (fileName.endsWith("xls")) {
wb = new HSSFWorkbook(is);
} else if (fileName.endsWith("xlsx")) {
wb = WorkbookFactory.create(is);
} else {
throw new GenericException(AttachErrorEnum.ATTACH_TYPE_EXCEL_ERROR.getCode(), AttachErrorEnum.ATTACH_TYPE_EXCEL_ERROR.getDesc());
}
}
return wb;
}
/**
* @param file 附件
* @return 文件名称
* @date 2019-6-27
* @author zl
* @description 附件上传
*/
public String upload(HttpServletRequest request, MultipartFile file) {
List<FileBean> filePath = new ArrayList<>();
Calendar cal = Calendar.getInstance();
String folder = "" + cal.get(Calendar.YEAR) + (cal.get(Calendar.MONTH) + 1) + cal.get(Calendar.DATE);
String basePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort() + request.getRequestURI();
String path = "";
try {
//todo 前端传递业务类型。判断能否上传
String fullFileName = System.currentTimeMillis() + file.getOriginalFilename();
String serveFileName = fullFileName.substring(0, fullFileName.lastIndexOf("."));
String originalName = file.getOriginalFilename().substring(0, file.getOriginalFilename().lastIndexOf("."));
path = imageUploadPath + File.separator + folder + File.separator + fullFileName;
File target = new File(path);
if (!target.getParentFile().exists()) {
target.getParentFile().mkdirs();
}
file.transferTo(target);
String fileResource = basePath + "/" + folder + "/" + fullFileName;
//信息封装
FileBean fileBean = new FileBean(fullFileName, originalName, fileResource, file.getContentType());
//返回文件在服务器的地址
filePath.add(fileBean);
} catch (Exception e) {
e.printStackTrace();
}
return path;
}
public static String getCellFormatValue(Cell cell) {
String 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: {
//判断cell是否为日期格式
if (DateUtil.isCellDateFormatted(cell)) {
//转换为日期格式YYYY-mm-dd
// cellValue = cell.getDateCellValue();
cellValue = String.valueOf(cell.getNumericCellValue());
} else {
//数字
cellValue = String.valueOf(cell.getNumericCellValue());
}
break;
}
case Cell.CELL_TYPE_STRING: {
cellValue = cell.getRichStringCellValue().getString();
break;
}
default:
cellValue = "";
}
} else {
cellValue = "";
}
return cellValue;
}
}
业务层实现导出具体业务
@Service
@Transactional(rollbackFor = Throwable.class)
public class MerchantGoodsService extends GoodsService {
@Autowired
private MerchantGoodsDao merchantGoodsDao;
@Autowired
private MerchantMerchantInfoService merchantMerchantInfoService;
@Autowired
@Qualifier("goodsMapStructImpl")
private GoodsMapStruct goodsMapStruct;
@Autowired
private CustomMerchantInfoService customMerchantInfoService;
@Autowired
private GoodsExportService excelExport;
/**
* @Description: 批量导入商品
* @Param: []
* @return: void
* @Author:
* @Date: 2019/6/29
*/
public void batchImport(HttpServletRequest request, MultipartFile file, String userId) throws IOException, InvalidFormatException {
//卖家
MerchantInfo info = customMerchantInfoService.getByUserId(userId);
//卖家添加商品默认下架状态
List<Goods> list = excelImport.analysisExcelByGoods(request, file, userId, info, false);
//插入
merchantGoodsDao.batchInsert(list);
}
/**
* @Description: 批量导出商品逻辑
* @Param: [vo, response, userId]
* @return: void
* @Author:
* @Date: 2019/7/15
*/
public void batchExport(GoodsQueryVo vo, HttpServletResponse response, String userId) throws IOException {
//表头headers
String[] headers = {"商品名(可修改)", "商品描述(可修改)", "所属分类", "原价", "基础单位",
"最小销售规格", "销售价", "审核状态(可修改,0待审核,1审核通过,2审核失败,3暂存)", "销售状态(可修改,1上架,0下架)", "是否称重(可修改,1称重,0不称)",
"供应商id", "供应商", "保质期", "首图url"};
// 获取所有当前卖家的未删除状态的商品
List<Goods> goodsList = merchantGoodsDao.selectGoodsList(vo, userId);
List<List<String>> target = new ArrayList();
for (Goods goods : goodsList) {
List<String> row = new ArrayList<>();
row.add(goods.getGoodsName());
row.add(goods.getGoodsDescription());
row.add(goods.getCategoryFullName());
row.add(String.valueOf(goods.getGoodsOriginalPrice()));
row.add(goods.getGoodsUnit());
row.add(String.valueOf(goods.getGoodsMinUnit()));
row.add(String.valueOf(goods.getGoodsSalesPrice()));
//审核状态
Byte auditState = null;
if (GoodsAuditStateEnum.AUDITING.getCode() == goods.getAuditState()) {
auditState = GoodsAuditStateEnum.AUDITING.getCode();
} else if (GoodsAuditStateEnum.AUDIT_FAIL.getCode() == goods.getAuditState()) {
auditState = GoodsAuditStateEnum.AUDIT_FAIL.getCode();
} else if (GoodsAuditStateEnum.AUDIT_OK.getCode() == goods.getAuditState()) {
auditState = GoodsAuditStateEnum.AUDIT_OK.getCode();
}else if (GoodsAuditStateEnum.TEMP_SAVE.getCode() == goods.getAuditState()){
auditState = GoodsAuditStateEnum.TEMP_SAVE.getCode();
}
row.add(String.valueOf(auditState));
//销售状态(上下架状态)
Integer updown_state = null;
if (goods.getUpdownState()) {
updown_state = GoodsStateEnum.GOODS_ONLINE.getCode();
} else {
updown_state = GoodsStateEnum.GOODS_DOWNLINE.getCode();
}
row.add(String.valueOf(updown_state));
//是否称重
Byte ifWeight = null;
if (goods.getIfWeighGoods()) {
ifWeight = GoodsWeightEnum.WEIGHT.getCode();
} else {
ifWeight = GoodsWeightEnum.UN_WEIGHT.getCode();
}
row.add(String.valueOf(ifWeight));
//供应商id 供应商 保质期 首图url
row.add(goods.getSupplierId());
row.add(goods.getSupplierName());
if (goods.getQualityGuaranteePeriod() != null) {
row.add(DateUtils.format(goods.getQualityGuaranteePeriod(), "yyyy/MM/dd"));
}
row.add(goods.getGoodsFirstPic());
target.add(row);
}
String sheetName = "在售商品";
excelExport.exportTemplate(response, sheetName, headers, target);
}
}
商品导入控制层
@Controller
@RequestMapping("goods")
public class MerchantGoodsController extends GenericController {
private static final Logger LOGGER = LoggerFactory.getLogger(MerchantGoodsController.class);
@Autowired
private FileService fileService;
@Autowired
private MerchantGoodsService merchantGoodsService;
@Autowired
private SecurityHandler securityHandler;
/**
* @Param [request, file]
* @return com.hrt.framework.web.core.Result
* @Author youjp
* @Description //TODO 批量导入商品
* @throw
**/
@PostMapping(value = "/goodsBatchImport")
@ResponseBody
public Result goodsBatchImport(HttpServletRequest request, MultipartFile file) throws IOException, InvalidFormatException {
merchantGoodsService.batchImport(request, file, securityHandler.getUserId());
return Result.success();
}
/**
* @Param [response, vo]
* @return void
* @Author youjp
* @Description //TODO 批量导出商品
* @throw
**/
@GetMapping(value = "/goodsBatchExport")
public void goodsBatchExport(HttpServletResponse response, GoodsQueryVo vo) throws IOException {
merchantGoodsService.batchExport(vo, response, securityHandler.getUserId());
}
}
前端接口请求:
- Excel导出时,只要请求导出接口即可
var url = window.mconfig.mbaseUrl + path.goodsBatchExport + "?access_token=" + access_token + "&goodsName=" + goodsName + "&categoryFullId=" + categoryFullId;
window.location.href = url;
Excel导入:
var uploadInst2 = upload.render({
elem: '#batchUploadGoods', //绑定元素
accept: 'file', //普通文件
exts: 'xls|xlsx', //只允许上传压缩文件
url: window.mconfig.mbaseUrl + path.goodsBatchImport + "?access_token=" + access_token, //上传接口
done: function (res) {
if (res.code == window.httpStatus.success) {
layer.msg("导入成功", {
icon: 6
});
table.reload('goodsTableId', {
url: window.mconfig.mbaseUrl + path.page,
async: false,
request: {
pageName: 'pageNo', // 页码的参数名称,默认:page
limitName: 'pageSize' // 每页数据量的参数名,默认:limit
},
page: {
curr: 1
},
});
} else {
layer.alert('导入失败:' + res.msg, {
icon: 0,
});
}
},
error: function () {
layer.msg("请联系管理员", {
icon: 5
});
},
headers: { //通过 request 头传递
Authorization: layui.data(setter.tableName)[setter.headers.accessTokenName]
}
});
页面代码:
<div class="layui-input-inline">
<button class="layui-btn layui-btn-normal" lay-filter="exportGoods" lay-submit="">
<i class="layui-icon"></i>导出
</button>
</div>
<div class="layui-btn layui-btn-sm" id="batchUploadGoods">
<i class="layui-icon"></i>批量导入
<span id="batchUploadTag"></span>
</div>
更详细的可以看一下这篇博文:
https://blog.csdn.net/typ1805/article/details/83279532