java 读取Excel （利用poi，jxl插件）

这几天在工作当中由于遇到要把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);//获得工作区Sheetjxl.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，内容为valuehm.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，单元格内容为valuemap.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，单元格内容为valuemap.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>>();}}}