POI导入EXCEL

最新推荐文章于 2024-09-25 10:59:43 发布

weixin_34254823

最新推荐文章于 2024-09-25 10:59:43 发布

阅读量71

点赞数

文章标签： java 数据库 python

原文链接：https://my.oschina.net/langgege/blog/880027

版权

2019独角兽企业重金招聘Python工程师标准>>>

package com.gsww.chis.commons.result;

import java.io.IOException;
import java.io.InputStream;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Queue;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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 com.gsww.chis.pojo.ywyk.YwykSfxm;

public class ImportExcelUtils {
   //每次导入到数据库的实体条数
   private static final int ONCE_IMPORT_SIZE = 2000;
   //开始读取的sheet的row下标
   private static final int BEGIN_ROW_INDEX = 1;

   /**
   * 导入excel到数据库的方法
   * 每次现读取一定条数的实体放入queue中
   * @param excelInputStream
   * @param queue
   */
   public static void importExcelToDB(InputStream excelInputStream,Queue<List<YwykSfxm>> queue){
       //创建Excel工作薄
       Workbook workbook = getWorkbook(excelInputStream);
       for(int i=0;i<workbook.getNumberOfSheets();i++){
           Sheet sheet = workbook.getSheetAt(i);
           if(sheet == null)continue;
           int nums = sheet.getLastRowNum();
           //当sheet的row数量小于ONCE_IMPORT_SIZE时,一次性将数据全部放入queue
           if(nums<=ONCE_IMPORT_SIZE){
               queue.offer(dealWithSheet(sheet,BEGIN_ROW_INDEX,sheet.getLastRowNum()));
           }else{//当sheet的row数量大于ONCE_IMPORT_SIZE时,每次将ONCE_IMPORT_SIZE条数据放入queue
               for(int j=0;j<=nums/ONCE_IMPORT_SIZE;j++){
                   boolean b = j<nums/ONCE_IMPORT_SIZE;
                   int begin = 0;
                   int end = 0;
                   if(b && j==0){//最开始的两千条数据
                       begin = BEGIN_ROW_INDEX;
                       end = ONCE_IMPORT_SIZE;
                   }else if(b){//中间部分
                       begin = ONCE_IMPORT_SIZE*j+1;
                       end = ONCE_IMPORT_SIZE*(j+1);
                   }else{//最后的两千条数据
                       begin = ONCE_IMPORT_SIZE*j+1;
                       end = sheet.getLastRowNum();
                   }
                   queue.offer(dealWithSheet(sheet,begin,end));
               }
           }
       }
   }

   /**
   * 处理sheet
   * @param sheet
   * @param rowBeginIndex 读取开始的sheet下标
   * @param rowEndIndex 读取结束的sheet下标
   * @return
   */
   public static List<YwykSfxm> dealWithSheet(Sheet sheet,int rowBeginIndex,int rowEndIndex){
       List<YwykSfxm> medicalList = new ArrayList<YwykSfxm>();
       for(int i=rowBeginIndex;i<=rowEndIndex;i++){
           Row row = sheet.getRow(i);
           if(row == null )continue;
           medicalList.add(dealWithRow(row));
       }
       return medicalList;
   }

   /**
   * 导入excel处理Row,此方法待优化
   * @param row
   */
   public static YwykSfxm dealWithRow(Row row){
       YwykSfxm ywykSfxm = new YwykSfxm();
       Object[] array = new Object[row.getLastCellNum()];
       for(int i=row.getFirstCellNum();i<row.getLastCellNum();i++){
           Cell cell = row.getCell(i);
           Object obj = getCellValue(cell);
           array[i] = obj;
       }

       ywykSfxm.setXmdm(array[0].toString());
       ywykSfxm.setXmmc(array[1].toString());
       ywykSfxm.setGg(array[2].toString());
       ywykSfxm.setDw(array[3].toString());
       ywykSfxm.setXmfl(array[5].toString());
       String str=array[4].toString();
       if(str.contains(",")){
           str = str.replace(",", "");
       }
       ywykSfxm.setDj(Double.parseDouble(str));
       String str1=array[6].toString();
       if(str1.contains(",")){
           str1 = str1.replace(",", "");
       }
       ywykSfxm.setLb(Long.parseLong(str1));
       return ywykSfxm;
   }

   /**
   * 导入excel获取cell的值,此方法待优化
   *
   * @param cell
   * @return
   */
   public static Object getCellValue(Cell cell){
       //DecimalFormat dfNum = new DecimalFormat("#"); //格式化数字
       NumberFormat nf = NumberFormat.getInstance(); //格式化数字
       SimpleDateFormat sdf = new SimpleDateFormat("HH:mm:ss"); //日期格式化
       Object obj = null;
       switch (cell.getCellType()) {
       case Cell.CELL_TYPE_STRING:
           obj = cell.getStringCellValue();
           break;
       case Cell.CELL_TYPE_BLANK:
           obj = "";
           break;
       case Cell.CELL_TYPE_NUMERIC:
           if("General".equals(cell.getCellStyle().getDataFormatString())){
               obj = nf.format(cell.getNumericCellValue());
           }else if("HH:mm:ss".equals(cell.getCellStyle().getDataFormatString())){
               obj = sdf.format(cell.getDateCellValue());
           }else{
               obj = cell.getNumericCellValue();
           }
           break;

       default:
           obj = "未知类型数据!";
           break;
       }
       return obj;
   }


   /**
   * 导入excel获取工作薄
   * @param excelInputStream
   * @return
   */
   private static Workbook getWorkbook(InputStream excelInputStream) {
       Workbook workbook = null;
       try{
//2003版本的excel，用.xls结尾
workbook = new HSSFWorkbook(excelInputStream);//得到工作簿
}catch (Exception ex){
try{
//2007版本的excel，用.xlsx结尾
workbook = new XSSFWorkbook(excelInputStream);//得到工作簿
}catch (IOException e){
e.printStackTrace();
}
}
return workbook;
   }

}

-------------------------------------

/**
   *
   * 方法描述 : 调用存储过程PKG_DATA_BASE.USP_JC_BASE_SFXM_IMP
   * @param yydm
   * @param req
   * @param res
   * @return
   * @throws Exception
   */
   @ResponseBody
   @RequestMapping(value="/callSfxmImp",method=RequestMethod.POST)
   public Boolean callSfxmImp( String yydm,@RequestParam(value = "medicalExcel", required = true)MultipartFile medicalExcel, int sfqk,HttpServletRequest req,HttpServletResponse res){
           Boolean flag = true;
       Queue<List<YwykSfxm>> queue = new LinkedList<List<YwykSfxm>>();
           InputStream is = null;
           try {

is = medicalExcel.getInputStream();

ImportExcelUtils.importExcelToDB(is,queue);

List<YwykSfxm> medicalList = queue.poll();

                     flag=ywykSfxmService.callSfxmImp(yydm, medicalList, sfqk);
} catch (SerialException e) {
flag=false;
e.printStackTrace();
} catch (SQLException e) {
flag=false;
e.printStackTrace();
}
       return flag;
   }

--------------------------------

转载于:https://my.oschina.net/langgege/blog/880027