导出excel工具类 及导出 导入
package com.shinho.dc3.master.util; import com.github.pagehelper.util.StringUtil; import com.shinho.dc3.master.exception.BusinessException; import com.shinho.dc3.master.req.ExcelSet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.*; import org.springframework.util.StringUtils; import org.springframework.web.multipart.MultipartFile; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.io.UnsupportedEncodingException; import java.util.List; /** * Copyright (C), 2018-2019, 烟台欣和企业食品有限公司 * fileName ExcelUtil * * @author yuhailin * @date 2019/3/4 16:47 * description Excel解析 * History: * <author> <time> <version> <desc> * 作者名称 修改时间 版本号 描述 */ public class ExcelUtil { public static String getPostfix (String path) { String pathContains = "."; if (StringUtils.isEmpty(path) || !path.contains(pathContains)) { return null; } return path.substring(path.lastIndexOf(pathContains) + 1, path.length()).trim(); } /** * 解析Excel文件 * @return */ public static Workbook analysisExcelFile(MultipartFile file) throws BusinessException { if(file==null || file.getSize() == 0){ throw new BusinessException("不能上传空文件"); } //得到文件名称 String fileName = file.getOriginalFilename(); //获取文件的拓展名 String postfix = ExcelUtil.getPostfix(fileName); Workbook workbook; try { InputStream is = file.getInputStream(); String fileType = "xlsx"; if(fileType.equals(postfix)){ workbook = new XSSFWorkbook(is); }else{ workbook = new HSSFWorkbook(is); } }catch (IOException e){ throw new BusinessException("文件解析失败"); } if(workbook==null || workbook.getSheetAt(0)==null){ throw new BusinessException("不能上传空文件"); } return workbook; } /** * 将Cell定义为文本类型,取值 * @param cell Cell * @return 字符串格式的值 */ public static String getCellStringValue(Cell cell){ if(cell==null){ return ""; } cell.setCellType(CellType.STRING); return cell.getStringCellValue(); } /** * 判断上传文件表头是否正确 * @param titleRow 标题行 * @param titleValues 模板标题 * @return true/false */ public static boolean validExcelTitle(Row titleRow,String [] titleValues){ if(titleRow == null){ // 模板不正确 return false; } //判断上传文件的标题行是否符合 for(int i=0;i<titleValues.length;i++){ String titleValue = titleValues[i]; Cell cell = titleRow.getCell(i); if(cell==null){ return false; } String cellValue = cell.getStringCellValue(); if(!titleValue.equals(cellValue)){ return false; } } return true; } /** * 验证关键数据不为空 * * @param rowItem 行号 * @param valueList 数据 * @param item 验证字段序号 */ public static String validKeyValue(int rowItem, List<String> valueList,String [] titleNames, int[] item) { boolean isEmpty = true; Integer firstEmptyItem = null; for (int i : item) { if (StringUtil.isEmpty(valueList.get(i))) { if (firstEmptyItem == null) { firstEmptyItem = i; } if (!isEmpty) { break; } } else { isEmpty = false; } } if (isEmpty) { return "第" + rowItem + "行为空数据"; } else if (firstEmptyItem != null) { return "第" + rowItem + "行的" + titleNames[firstEmptyItem] + "为空"; } return null; } /** * 生成Excel2007 * @param title 报表名称 * @param headers 标题行名称 * @return */ private static XSSFWorkbook exportExcel2007(String sheetName, String title, String[] headers) { // 声明一个工作薄 XSSFWorkbook workbook = new XSSFWorkbook(); // 生成一个表格 XSSFSheet sheet; if(StringUtils.isEmpty(sheetName)){ sheet = workbook.createSheet(); }else{ sheet = workbook.createSheet(sheetName); } // 创建标题样式1 XSSFCellStyle headerStyle = workbook .createCellStyle(); XSSFFont headerFont = workbook.createFont(); headerFont.setBold(true); headerFont.setFontHeightInPoints((short) 20); headerStyle.setFont(headerFont); headerStyle.setAlignment(HorizontalAlignment.CENTER); // 创建标题样式1 XSSFCellStyle titleStyle = workbook .createCellStyle(); XSSFFont titleFont = workbook.createFont(); titleFont.setBold(true); titleFont.setFontHeightInPoints((short) 10); titleStyle.setFont(titleFont); titleStyle.setAlignment(HorizontalAlignment.CENTER); // 产生表格标题行 XSSFRow row = sheet.createRow(0); //报表名称 sheet.addMergedRegion(new CellRangeAddress(0,0,0,headers.length-1)); XSSFCell cellTitle = row.createCell(0); cellTitle.setCellValue(title); cellTitle.setCellStyle(headerStyle); XSSFRow rowHeader = sheet.createRow(1); XSSFCell cellHeader; for (int i = 0; i < headers.length; i++) { cellHeader = rowHeader.createCell(i); cellHeader.setCellValue(new XSSFRichTextString(headers[i])); cellHeader.setCellStyle(titleStyle); } return workbook; } /** * 生成Excel2007 * @param set Excel生成参数 * @param response * @return */ public static void exportExcel2007(ExcelSet set, HttpServletResponse response) { // 声明一个工作薄 XSSFWorkbook workbook = exportExcel2007(set.getSheetName(),set.getReportName(),set.getTitleName()); List<String []> values = set.getValues(); if(values!=null && !values.isEmpty()){ // 生成一个表格 XSSFSheet sheet = workbook.getSheetAt(0); for (int i=0;i<values.size();i++){ XSSFRow valueRow = sheet.createRow(i+2); String [] objs = values.get(i); for(int j=0;j<objs.length;j++){ Cell cell = valueRow.createCell(j); cell.setCellValue(objs[j]); } } } setResponseHeader(response,set.getFileName()); try { OutputStream os = response.getOutputStream(); workbook.write(os); os.flush(); os.close(); } catch (IOException e) { e.printStackTrace(); } } /** * 发送响应流方法 * @param response * @param fileName 下载文件的文件名 */ private static void setResponseHeader(HttpServletResponse response, String fileName) { try { fileName = new String(fileName.getBytes(),"ISO8859-1"); response.setContentType("application/octet-stream;charset=ISO8859-1"); response.setHeader("Content-Disposition", "attachment;filename="+ fileName); response.addHeader("Pargam", "no-cache"); response.addHeader("Cache-Control", "no-cache"); } catch (Exception ex) { ex.printStackTrace(); } } }
导出 service层:
public void exportMatnr(String pDid, String pKondm, String pStatus, String pKeyWord,HttpServletResponse response){
ExcelSet set = new ExcelSet();
String nowStr = DateUtil.formartDate(new Date(),"yyyyMMddHHmmss");
String fileName = pDid+"_"+nowStr+".xlsx";
set.setFileName(fileName);
set.setReportName(ExcelTitle.EXCEL_OUT_MATNR_NAME);
String [] title = ExcelTitle.EXCEL_OUT_MATNR_TITLE;
set.setTitleName(title);
List<ExportMatnr> baseMatnrList =getMatnrDetailed(pDid,pKondm,pStatus,pKeyWord);
int size = title.length;
List<String[]> list = new ArrayList<>();
for(ExportMatnr baseMatnr : baseMatnrList){
String [] values = new String[size];
values[0] = baseMatnr.getMatnr();
values[1] = baseMatnr.getMaktx();
values[2] = baseMatnr.getTypeName();
values[3] = baseMatnr.getKondmName();
values[4] = baseMatnr.getFactoryName();
values[5] = baseMatnr.getUnitB();
values[6] = baseMatnr.getUnitC();
values[7] = baseMatnr.getUnitF();
values[8] = judgeNullValue(baseMatnr.getShelfDate());
values[9] = judgeNullValue(baseMatnr.getFreshnessDate());
values[10] = judgeNullValue(baseMatnr.getSalePriceB());
values[11] = judgeNullValue(baseMatnr.getPurchasePriceB());
values[12] = judgeNullValue(baseMatnr.getSalePriceC());
values[13] = judgeNullValue(baseMatnr.getPurchasePriceC());
values[14] = judgeNullValue(baseMatnr.getSalePriceF());
values[15] = judgeNullValue(baseMatnr.getPurchasePriceF());
values[16] = baseMatnr.getStatus();
if(!StringUtils.isEmpty(baseMatnr.getStatus())){
values[17] = BaseStatusEnums.getEnum(Integer.parseInt(baseMatnr.getStatus())).desc();
}else{
values[17] = "";
}
values[18] = baseMatnr.getSpecB();
values[19] = judgeNullValue(baseMatnr.getUmrezF());
values[20] = baseMatnr.getBoxCode();
values[21] = pDid;
values[22] = baseMatnr.getBarcodeB();
list.add(values);
}
set.setValues(list);
//工作表名称,标题列
ExcelUtil.exportExcel2007(set,response);
}
private String judgeNullValue(BigDecimal value){
if(value==null){
return "";
}
return value.toString();
}
private String judgeNullValue(Long value){
if(value==null){
return "";
}
return value.toString();
}
导入 service层:
/**
* 读取上传的Excel文件,插入到商品表
*
* @param pDid
* @param pUserId
* @param pExcelFile
* @return
* @throws BusinessException
*/
@Transactional(rollbackFor = Exception.class)
public List<String> uploadMatnrExcelFile(String pDid, String pUserId, MultipartFile pExcelFile) throws BusinessException {
// 解析文件
Workbook workbook = ExcelUtil.analysisExcelFile(pExcelFile);
// 获取文件的第一个sheet页
Sheet sheet = workbook.getSheetAt(0);
//验证模板是否正确
Row firstRow = sheet.getRow(0);
if (!ExcelUtil.validExcelTitle(firstRow, ExcelTitle.EXCEL_MATNR_TITLE)) {
throw new BusinessException("上传模板错误");
}
//错误信息保存集合
List<String> templateErrorList = new ArrayList<>();
//保存<客户名称,客户编号>,用于检测,是否存在相同客户
Map<String, String> nameMap = new HashMap<>();
//要保存的客户数据
List<BaseMatnr> matnrList = new ArrayList<>();
List<BaseSupplierMatnr> supplierMatnrList = new ArrayList<>();
//得到文件中最大行号
int lasrRowNum = sheet.getLastRowNum();
//最大读取列数
int maxColumn = ExcelTitle.EXCEL_MATNR_TITLE.length;
// 开始数据处理
for (int rowNum = 1; rowNum <= lasrRowNum; rowNum++) {
Row row = sheet.getRow(rowNum);
int nowRowNum = rowNum + 1;
//取出行数据放入到list集合中
List<String> valueList = new ArrayList<>(maxColumn);
for (int i = 0; i < maxColumn; i++) {
String value = ExcelUtil.getCellStringValue(row.getCell(i));
valueList.add(value);
}
//验证生成商品信息
BaseMatnr matnr = createBaseMatnr(nowRowNum, pUserId, pDid, valueList, nameMap, templateErrorList);
if (matnr != null) {
//判断是否存在供应商信息,写入供应商商品表
if (!StringUtil.isEmpty(valueList.get(10))) {
String supplierName = valueList.get(10);
//根据供应商名称查询供应商信息
BaseSupplier baseSupplier = baseSupplierService.getSupplierByName(pDid, supplierName);
if (baseSupplier == null) {
templateErrorList.add("第" + nowRowNum + "行的供应商无效");
continue;
}
BaseSupplierMatnr baseSupplierMatnr = new BaseSupplierMatnr();
baseSupplierMatnr.setDid(pDid);
baseSupplierMatnr.setSupplierId(baseSupplier.getRowId());
baseSupplierMatnr.setMatnr(matnr.getMatnr());
baseSupplierMatnr.setUserid(pUserId);
supplierMatnrList.add(baseSupplierMatnr);
}
//将客户数据加入到客户列表中
matnrList.add(matnr);
}
}
if (matnrList.isEmpty()) {
templateErrorList.add("文件中无有效数据");
}
//保存导入数据
if (templateErrorList.isEmpty()) {
this.saveMatnrBatch(matnrList, supplierMatnrList);
}
return templateErrorList;
}