这几天在工作当中由于遇到要把excel文件导入到项目,所以在参考了网上前辈们的资料后,自己动手写了一个符合自己要求的方法。项目中所需要的jar文件为:
xmlbeans-2.6.0.jar
poi-ooxml-3.13-20150929.jar
poi-3.13-20150929.jar
poi-ooxml-schemas-3.13-20150929.jar
jxl.jar
package com.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
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.Cell;
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;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
/**
* Excel工具类
* @author NO-1
*
*/
public class ExcelUtil {
/**
* 使用jxl插件读取Excel(2003)文件
* @param fileurl 文件路径
* @return list<HashMap<String,String>>
* map对象:第一行表头为key,从第三行开始单元内容为value
*/
public static List<HashMap<String, String>> readExcelByJxl(String fileurl) {
List<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();
HashMap<String, String> hm =null;
try {
//文件流
InputStream is = new FileInputStream(fileurl);
//获得Excel文档
Workbook rwb = Workbook.getWorkbook(is);
//获得工作区Sheet
jxl.Sheet[] sheets=rwb.getSheets();
//获得表头字段
jxl.Cell[] cell0=sheets[0].getRow(0);
for(int i=0;i<sheets.length;i++){
Sheet sheet =sheets[i];
//获得总行数
int rowsNum=sheet.getRows();
//遍历每行
for(int j=2;j<rowsNum;j++){
//创建HashMap对象,存放每行的数据
hm = new HashMap<String, String>();
jxl.Cell[] cells=sheet.getRow(j);
for(int k=0;k<cells.length;k++){
//遍历每行单元格
jxl.Cell cell=cells[k];
if(cell!=null){
//获得单元格内容
String value=cell.getContents();
//存放到map中,表头为key,内容为value
hm.put(cell0[k].getContents(), value);
}
}
//存放到list对象中(每行为一个map对象)
list.add(hm);
}
}
//关闭流
is.close();
rwb.close();
} catch (BiffException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return list;
}
/**
*
* @param fileName
* @return List<HashMap<String, String>>
* @throws IOException
* @throws FileNotFoundException
*/
public static List<HashMap<String, String>> readExcel2007(String fileName) throws FileNotFoundException, IOException {
List<HashMap<String,String>> list = new LinkedList<HashMap<String,String>>();
// 构造 XSSFWorkbook 对象,strPath 传入文件路径
XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(new File(fileName)));
// 读取第一章表格内容
XSSFSheet sheet = xwb.getSheetAt(0);
XSSFRow row = null;
XSSFCell cell = null;
//获得总行数
int counter = sheet.getPhysicalNumberOfRows();
//获得第一行总列数(表头)
row=sheet.getRow(0);
int cellNum = row.getPhysicalNumberOfCells();
String[] cells=new String[cellNum];
//将表头存放到数组中,做为map中的key使用
for(int i=0;i<cellNum;i++){
cell=row.getCell(i);
if(cell!=null){
cell.setCellType(Cell.CELL_TYPE_STRING);
cells[i]=cell.getStringCellValue();
}
}
HashMap<String ,String> map=null;
//遍历行
for (int i = 0; i < counter; i++) {
row = sheet.getRow(i);
//遍历单元格
map=new HashMap<String,String>();
for (int j = 0; j < cellNum; j++) {
cell = row.getCell(j);
if (cell != null) {
//设置单元格内容为String类型
cell.setCellType(Cell.CELL_TYPE_STRING);
String value = cell.getStringCellValue();
//存放到map对象中,表头为key,单元格内容为value
map.put(cells[j], value);
// System.out.println("第" + (i + 1) + "行第" + (j + 1) + "列="+ value);
}
}
list.add(map);
}
//关闭流
xwb.close();
return list;
}
/**
*
* @param fileName
* @return List<HashMap<String, String>>()
* @throws FileNotFoundException
* @throws IOException
*/
public static List<HashMap<String, String>> readExcel2003(String fileName) throws FileNotFoundException, IOException {
List<HashMap<String,String>> list = new LinkedList<HashMap<String,String>>();
// 构造 XSSFWorkbook 对象,strPath 传入文件路径
HSSFWorkbook xls = new HSSFWorkbook(new FileInputStream(new File(fileName)));
// 读取第一章表格内容
HSSFSheet sheet = xls.getSheetAt(0);
HSSFRow row = null;
HSSFCell cell = null;
//获得总行数
int counter = sheet.getPhysicalNumberOfRows();
//获得第一行总列数(表头)
row=sheet.getRow(0);
int cellNum = row.getPhysicalNumberOfCells();
String[] cells=new String[cellNum];
//将表头存放到数组中,做为map中的key使用
for(int i=0;i<cellNum;i++){
cell=row.getCell(i);
if(cell!=null){
cell.setCellType(Cell.CELL_TYPE_STRING);
cells[i]=cell.getStringCellValue();
}
}
HashMap<String ,String> map=null;
//遍历行
for (int i = 0; i < counter; i++) {
row = sheet.getRow(i);
//遍历单元格
map=new HashMap<String,String>();
for (int j = 0; j < cellNum; j++) {
cell = row.getCell(j);
if (cell != null) {
//设置单元格内容为String类型
cell.setCellType(Cell.CELL_TYPE_STRING);
String value = cell.getStringCellValue();
//存放到map对象中,表头为key,单元格内容为value
map.put(cells[j], value);
// System.out.println("第" + (i + 1) + "行第" + (j + 1) + "列="+ value);
}
}
list.add(map);
}
//关闭流
xls.close();
return list;
}
/**
* 解析Excel文件,2003和2007都可以
* @param fileName
* @return List<HashMap<String, String>>()
* @throws FileNotFoundException
* @throws IOException
*/
public static List<HashMap<String, String>> readExcel(String fileName) throws FileNotFoundException, IOException{
if(fileName==null||fileName.length()==0){
return new ArrayList<HashMap<String, String>>();
}
if(fileName.endsWith(".xls")){
return readExcel2003(fileName);
}else if(fileName.endsWith(".xlsx")){
return readExcel2007(fileName);
}else{
return new ArrayList<HashMap<String, String>>();
}
}
}