导入excel

最新推荐文章于 2024-07-26 14:47:41 发布

次品丶

最新推荐文章于 2024-07-26 14:47:41 发布

阅读量149

点赞数

本文链接：https://blog.csdn.net/w645444407/article/details/83931558

版权

导入Excel

/**
    * 导入Excel
    * @param importFile
    * @param response
    * @param request
    * @return
    */
@RequestMapping("/doImport")
@ResponseBody
public ResultJsonInfo doImport(@RequestParam(value = "importFile", required = false) MultipartFile importFile, HttpServletResponse response, HttpServletRequest request) throws Exception {
   try {
      String fileName=importFile.getOriginalFilename();
      ReadExcelUtils excelReader = new ReadExcelUtils(fileName,importFile);
           UserToken userToken = ContextHelper.getUserToken();

      //导入后合并条数
      int mergeCount = 0;
      //导入时异常内容
      String exceptionContent = "";
      //导入后提示信息
      String msg = "";

      //获取表格标题行
      String[] excelTitle = excelReader.readExcelTitle();

      Map<Integer, Map<Integer, Object>> productOrderMap = excelReader.readExcelContent();
      for(int i = 3; i <= productOrderMap.size(); i++){
         Map<Integer, Object> map = productOrderMap.get(i);
         ProductOrderVO vo = new ProductOrderVO();

         /*map为第i行的数据 map.get(n).toString()则是获取第i行,n列的数据*/
         vo.setCode(map.get(0).toString());//订单编号
               // 订单状态：0:待审核 1:审核通过 2:审核失败
               String state = map.get(4).toString();
               state = state.substring(0,1);
               vo.setOrderState(state);
               if ("1".equals(state)) {//审核通过必须要有订单金额
                   try {
                       BigDecimal orderAmount = new BigDecimal(map.get(3).toString());
                       vo.setOrderAmount(orderAmount);//订单金额
                   }catch (Exception e){
                       //异常位置
                       exceptionContent += "【" + vo.getCode() + "】";
                       continue;
                   }
               }
               ProductOrderVO byCode = productOrderService.findOrderByCode(map.get(0).toString());
               HashMap<String, Object> mapSave = productOrderService.isSave(vo, byCode);
               String code = mapSave.get("code").toString();
               //导入数据时 需判断表中是否已经有存在记录和是否满足存入条件
         if (byCode.getCode().equals(vo.getCode())) {
            if ("0".equals(code)) {
               byCode = (ProductOrderVO) mapSave.get("productOrderVO");
               //合并条数加1
               mergeCount++;
            }
            if ("999".equals(code)){//状态没变
               continue;
            }
         } else {//订单号不存在的时候就记录异常的订单标号
            //异常位置
            exceptionContent += "【" + vo.getCode() + "】";
            continue;
         }
         productOrderService.update(null, byCode);
      }

      if(StringUtils.isNotBlank(exceptionContent)){
               msg="导入数据成功、合并了【"+mergeCount+"】条数据、订单编号为"+exceptionContent+"的数据有异常";
           }else{
               msg="导入数据成功、合并了【"+mergeCount+"】条数据";
           }
      return new ResultJsonInfo(true,msg);
   } catch (Exception e) {
      e.printStackTrace();
           return new ResultJsonInfo(true, "信息导入失败");
       }
}

使用的utils

package cn.rmt.bif.web.util;
import java.io.FileInputStream;  
import java.io.FileNotFoundException;  
import java.io.IOException;  
import java.io.InputStream;  
import java.util.Date;  
import java.util.HashMap;  
import java.util.Map;  
  

import org.apache.poi.hssf.usermodel.HSSFWorkbook;  
import org.apache.poi.ss.usermodel.Cell;  
import org.apache.poi.ss.usermodel.DateUtil;  
import org.apache.poi.ss.usermodel.Row;  
import org.apache.poi.ss.usermodel.Sheet;  
import org.apache.poi.ss.usermodel.Workbook;  
import org.apache.poi.xssf.usermodel.XSSFWorkbook;  
import org.slf4j.Logger;  
import org.slf4j.LoggerFactory;  
import org.springframework.web.multipart.MultipartFile;
  
/** 
 * 读取Excel 
 */  
public class ReadExcelUtils {  
    private Logger logger = LoggerFactory.getLogger(ReadExcelUtils.class);  
    private Workbook wb;  
    private Sheet sheet;  
    private Row row;  
  
    public ReadExcelUtils(String filepath,MultipartFile importFile) {  
        if(filepath==null){  
            return;  
        }  
        String ext = filepath.substring(filepath.lastIndexOf("."));  
        try {  
            InputStream is = importFile.getInputStream();  
            if(".xls".equals(ext)){  
                wb = new HSSFWorkbook(is);  
            }else if(".xlsx".equals(ext)){  
                wb = new XSSFWorkbook(is);  
            }else{  
                wb=null;  
            }  
        } catch (FileNotFoundException e) {  
            logger.error("FileNotFoundException", e);  
        } catch (IOException e) {  
            logger.error("IOException", e);  
        }  
    }  
      
    /** 
     * 读取Excel表格表头的内容 
     *  
     * @param InputStream 
     * @return String 表头内容的数组 
     */  
    public String[] readExcelTitle() throws Exception{  
        if(wb==null){  
            throw new Exception("Workbook对象为空！");  
        }  
        sheet = wb.getSheetAt(0);  
        row = sheet.getRow(0);  
        // 标题总列数  
        int colNum = row.getPhysicalNumberOfCells();  
        System.out.println("colNum:" + colNum);  
        String[] title = new String[colNum];  
        for (int i = 0; i < colNum; i++) {  
            // title[i] = getStringCellValue(row.getCell((short) i));  
            title[i] = row.getCell(i).getStringCellValue();
        }  
        return title;  
    }  
  
    /** 
     * 读取Excel数据内容 
     *  
     * @param InputStream 
     * @return Map 包含单元格数据内容的Map对象 
     */  
    public Map<Integer, Map<Integer,Object>> readExcelContent() throws Exception{  
        if(wb==null){  
            throw new Exception("Workbook对象为空！");  
        }  
        Map<Integer, Map<Integer,Object>> content = new HashMap<Integer, Map<Integer,Object>>();  
          
        sheet = wb.getSheetAt(0);  
        // 得到总行数  
        int rowNum = sheet.getLastRowNum();
        // 读列数（你的excel表格规定从第几行开始正式为表格就改为第几行）
        row = sheet.getRow(2);
        int colNum = row.getPhysicalNumberOfCells();  
        // 正文内容应该从第二行开始,第一行为表头的标题  
        for (int i = 1; i <= rowNum; i++) {  
            row = sheet.getRow(i);  
            int j = 0;  
            Map<Integer,Object> cellValue = new HashMap<Integer, Object>();  
            while (j < colNum) {  
                Object obj = getCellFormatValue(row.getCell(j));  
                cellValue.put(j, obj);  
                j++;  
            }  
            content.put(i, cellValue);  
        }  
        return content;  
    }  
  
    /** 
     *  
     * 根据Cell（单元格）类型设置数据 
     *  
     * @param cell 
     * @return 
     */  
    private Object getCellFormatValue(Cell cell) {  
        Object cellvalue = "";  
        if (cell != null) {  
            // 判断当前Cell的Type  
            switch (cell.getCellType()) {  
            case Cell.CELL_TYPE_NUMERIC:// 如果当前Cell的Type为NUMERIC  
            case Cell.CELL_TYPE_FORMULA: {  
                // 判断当前的cell是否为Date  
                if (DateUtil.isCellDateFormatted(cell)) {  
                    // 如果是Date类型则，转化为Data格式  
                    // data格式是带时分秒的：2013-7-10 0:00:00  
                    // cellvalue = cell.getDateCellValue().toLocaleString();  
                    // data格式是不带带时分秒的：2013-7-10  
                    Date date = cell.getDateCellValue();  
                    cellvalue = date;  
                } else {// 如果是纯数字  
  
                    // 取得当前Cell的数值  
                    cellvalue = String.valueOf(cell.getNumericCellValue());  
                }  
                break;  
            }  
            case Cell.CELL_TYPE_STRING:// 如果当前Cell的Type为STRING  
                // 取得当前的Cell字符串  
                cellvalue = cell.getRichStringCellValue().getString();  
                break;  
            default:// 默认的Cell值  
                cellvalue = "";  
            }  
        } else {  
            cellvalue = "";  
        }  
        return cellvalue;  
    }  
  
    public static void main(String[] args) {  
        try {  
//            String filepath = "E://rq.xlsx";  
//            ReadExcelUtils excelReader = new ReadExcelUtils(filepath);  
//            // 对读取Excel表格标题测试  
          String[] title = excelReader.readExcelTitle();  
          System.out.println("获得Excel表格的标题:");  
          for (String s : title) {  
              System.out.print(s + " ");  
          }  
//              
//            // 对读取Excel表格内容测试  
//            Map<Integer, Map<Integer,Object>> map = excelReader.readExcelContent();  
//            System.out.println("获得Excel表格的内容:");  
//            for (int i = 1; i <= map.size(); i++) {  
//                System.out.println(map.get(i));  
//            }  
//        } catch (FileNotFoundException e) {  
//            System.out.println("未找到指定路径的文件!");  
//            e.printStackTrace();  
//        }catch (Exception e) {  
//            e.printStackTrace();  
//        }  
    }  
}

定义的表格如下