导出excel工具类及导出导入

导出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 ermodel.HSSFWorkbook;
import ermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import ermodel.*;
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(stIndexOf(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;
}。