SpringBoot common.io.utils 下载文件，解析Excel内容

package com.jxdd.product.service;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.net.URL;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.jxdd.commons.entities.entity.FileUpload;
import com.jxdd.commons.entities.entity.FileUploadCriteria;
import com.jxdd.commons.entities.mapper.FileUploadMapper;
import com.jxdd.commons.exception.BusinessException;
import com.jxdd.commons.utils.constants.UploadConstants;
import com.jxdd.commons.utils.message.Messages;
import com.jxdd.product.model.market.ImportDetailListVO;

/**
 *
 * @author : ouyanglei:
 * @date ：2017年8月31日
 * @version 1.0
 * @parameter
 * @return
 */
@Service
@Transactional
public class ProductImportDetailListService {
	private Logger logger = LoggerFactory.getLogger(ProductImportDetailListService.class);
	@Autowired
	private FileUploadMapper fileUploadMapper;

	public List
 
   importDetailList(int uploadId,HttpServletRequest request) throws BusinessException, IOException {
		// 返回结果集
		List
  
    resultList = new ArrayList
   
    (); FileInputStream fis = null; // 通过uplodeid 查询文件地址 路径 FileUploadCriteria fileUploadCriteria = new FileUploadCriteria(); FileUploadCriteria.Criteria criteria = fileUploadCriteria.createCriteria(); criteria.andUploadIdEqualTo(uploadId); List
    
      list = fileUploadMapper.selectByCriteria(fileUploadCriteria); if (list.size() > 0 && null != list) { String fileName = list.get(0).getFileName(); HSSFRow row; //下载到本地的地址 String localPath = "C:\\"+UploadConstants.addSlash(UploadConstants.GMS_PATH) + fileName; try { FileUtils.copyURLToFile(new URL(UploadConstants.addSlash(UploadConstants.GMS_URL) + fileName), new File(localPath)); } catch (Exception e) { throw new BusinessException(Messages.CODE_170013,"文件不存在"); } //读取本地文件 logger.info("本地地址:{}",localPath); File files =new File (localPath); if(!files.exists()){ throw new BusinessException(Messages.CODE_170013,"文件不存在"); } fis = new FileInputStream(files); HSSFWorkbook wookbook = new HSSFWorkbook(fis); // 创建对Excel工作簿文件的引用 HSSFSheet sheet = wookbook.getSheetAt(0); // 在Excel文档中，第一张工作表的缺省索引是0 int rowNum = sheet.getPhysicalNumberOfRows(); // 获取到Excel文件中的所有行数­ // 正文内容应该从第二行开始,第一行为表头的标题 for (int i = 1; i < rowNum; i++) { row = sheet.getRow(i); if(null != row){ String cheac_NO = ""; String gc_id3 = ""; String goods_id = ""; String goods_name = ""; String brand_id = ""; String minimum_packing_unit = ""; String goods_barcode = ""; String non_standard_barcode = ""; String licence_no = ""; String producer_name = ""; String producer_address = ""; String abbreviation_code = ""; String goods_image = ""; String goodsDetail_image =""; String packing_spec =""; int idx = 0; //* 序号 cheac_NO =getCellFormatValue(row.getCell(idx)); idx++; //* 三级分类ID gc_id3 = getCellFormatValue(row.getCell(idx)); idx++; //* 商品名称（20字） goods_name = getCellFormatValue(row.getCell(idx)); idx++; //品牌ID brand_id = getCellFormatValue(row.getCell(idx)); idx++; //* 最小包装单位（20字） minimum_packing_unit = getCellFormatValue(row.getCell(idx)); idx++; //商品条形码（13位数字） goods_barcode = getCellFormatValue(row.getCell(idx)); idx++; //非标商品码（30字） non_standard_barcode = getCellFormatValue(row.getCell(idx)); idx++; //许可证编号（30字） licence_no = getCellFormatValue(row.getCell(idx)); idx++; //* 生产商（50字） producer_name = getCellFormatValue(row.getCell(idx)); idx++; //* 地址/产地（50字） producer_address = getCellFormatValue(row.getCell(idx)); idx++; //缩写代码（10字） abbreviation_code = getCellFormatValue(row.getCell(idx)); idx++; //* 商品图片（必填1张，共5张） goods_image = getCellFormatValue(row.getCell(idx)); idx++; //* 商品详情（文字加图片地址） goodsDetail_image = getCellFormatValue(row.getCell(idx)); //包装规格（格式：箱(24)） idx++; packing_spec = getCellFormatValue(row.getCell(idx)); ImportDetailListVO importDetailListVO = new ImportDetailListVO(); importDetailListVO.setCheac_NO(cheac_NO); importDetailListVO.setGoods_id(goods_id); importDetailListVO.setGc_id3(gc_id3); importDetailListVO.setGoods_name(goods_name); importDetailListVO.setBrand_id(brand_id); importDetailListVO.setMinimum_packing_unit(minimum_packing_unit); importDetailListVO.setGoods_barcode(goods_barcode); importDetailListVO.setNon_standard_barcode(non_standard_barcode); importDetailListVO.setLicence_no(licence_no); importDetailListVO.setProducer_address(producer_address); importDetailListVO.setAbbreviation_code(abbreviation_code); importDetailListVO.setGoods_image(goods_image); importDetailListVO.setGoodsDetail_image(goodsDetail_image); importDetailListVO.setProducer_name(producer_name); importDetailListVO.setSpec_value(packing_spec); resultList.add(importDetailListVO); } } } return resultList; } private static String getCellFormatValue(HSSFCell cell) { DecimalFormat df = new DecimalFormat("#"); String cellValue = null; if (cell == null) return null; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); cellValue = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())); break; } cellValue = df.format(cell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_STRING: cellValue = String.valueOf(cell.getStringCellValue()); break; case HSSFCell.CELL_TYPE_FORMULA: cellValue = String.valueOf(cell.getCellFormula()); break; case HSSFCell.CELL_TYPE_BLANK: cellValue = null; break; case HSSFCell.CELL_TYPE_BOOLEAN: cellValue = String.valueOf(cell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_ERROR: cellValue = String.valueOf(cell.getErrorCellValue()); break; } if (cellValue != null && cellValue.trim().length() <= 0) { cellValue = null; } return cellValue; } }