poi导入excel通用工具类

注释:

可在action直接调用该工具类,可传入file或文件路径,支持指定路径文件,和指定物理文件的excel。

工具类会返回一个List对象,拿到该对象后则是业务处理部分,比如:转换为业务对象的pojo,数据校验,迭代插入db等。业务部分代码就不做阐述了。通用部分请看一下代码。

调用工具类:

public class uploadDemo {
    /**
     * @param args
     */
    public static void main(String[] args) {
        List list = ExcelUpLoadUtil.excelUpLoad(null, "d:/test.xls");
        System.out.println(list.size());
        System.exit(0);
    }
}

工具类:

package com.jala.ccs.common;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUpLoadUtil {
    private static final Log log = LogFactory.getLog(ExcelUpLoadUtil.class);
    /**
     * excel文件导入
     * 该方法在传统方法加以修改,支持不同版本导入,最终返回一个list集合,集合中封装的是object数组,然后根据业务需要封装自己想要的pojo对象。
     * 该方法支持指定服务端文件路径上传和客户端上传,如果是服务端指定路径上传则file赋值null,反之,则filePaht赋值null
     * 前端页面代码以下4行
     * <form action="upload" enctype="multipart/form-data">
     * <input type="file" name="myFile" />
     * <input type="submit" value="Upload! " />
     * </form>
     * springMvc action 中使用以下代码,以下两行
     * MultipartHttpServletRequest mulRequest = (MultipartHttpServletRequest) request;
     * MultipartFile file = mulRequest.getFile("excel");
     * @param file
     * @param filePath "d:/test.xls";
     * @return
     */
    public static List excelUpLoad(File f,String filePath){
        List list=null;//返回对象
        if(null==f && (null==filePath || "".equals(filePath))){
            log.error("导入excel,发现无效的参数,无法执行!");
            return null;
        }
        File file = f;
        if(null!=filePath && !"".equals(filePath)){//已指定文件路径
            file = new File(filePath);
        }
       
        String filename = file.getName(); 
        if (filename == null || "".equals(filename)){ 
            log.error("导入excel,文件不存在!");
        } 
        try {
            InputStream input = new FileInputStream(file);
            Workbook workBook = null;
            //以下做法是为了区分不同版本,然后使用XSSF或HSSF
            String flag="";//XSSF或HSSF的标记
            try {
                workBook = new XSSFWorkbook(input);
                flag="XSSF";
            } catch (Exception ex) {
                workBook = new HSSFWorkbook(new FileInputStream(file));
                flag="HSSF";
            }
           
            //根据标记使用不同的方法来解析excel
            if("XSSF".equals(flag)){
                 XSSFSheet sheet = (XSSFSheet) workBook.getSheetAt(0); 
                 if (sheet != null) 
                 {      //list
                      list = new ArrayList();   
                     for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) 
                     {   //object
                          XSSFRow row = sheet.getRow(i);
                           Object[] obj = new Object[row.getPhysicalNumberOfCells()];
                         for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) 
                         {   //column
                              XSSFCell cell = row.getCell(j); 
                             String cellStr = cell.toString(); 
                             log.info("【"+cellStr+"】 ");
                             obj[j] = cellStr;
                         } 
                         System.out.println();
                         list.add(obj);
                     }
                     log.info("当前excel总条数"+list.size());
                 } 
            }else if ("HSSF".equals(flag)){
                 HSSFSheet sheet = (HSSFSheet) workBook.getSheetAt(0); 
                 if (sheet != null) 
                 {      //list
                      list = new ArrayList();   
                     for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) 
                     {   //object
                          HSSFRow row = sheet.getRow(i);
                          Object[] obj = new Object[row.getPhysicalNumberOfCells()];
                         for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) 
                         {   //column
                             HSSFCell cell = row.getCell(j); 
                             String cellStr = cell.toString(); 
                             log.info("【"+cellStr+"】 ");
                             obj[j] = cellStr;
                         } 
                         System.out.println();
                         list.add(obj);
                     }
                     log.info("当前excel总条数"+list.size());
                 } 
            }else{
                log.error("导入excel,发生未知错误!");
            }
        } 
        catch (Exception e) {
            e.printStackTrace(); 
        }
        return list;
    }
}

相关推荐