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