欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

poi导入excel通用工具类

程序员文章站 2022-04-30 15:49:47
...

注释:

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