SpringBoot如何用common.io.utils下载文件，解析Excel内容？

本文共计830个文字，预计阅读时间需要4分钟。

javapackage 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;

ProductImportDetailListService.java

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; } }