java excel解析类_java 解析excel工具类

importjava.io.FileInputStream;importjava.io.FileNotFoundException;importjava.io.IOException;importjava.io.InputStream;importjava.util.ArrayList;importjava.util.Date;importjava.util.HashMap;importjava.util.List;importjava.util.Map;importorg.apache.log4j.Logger;importorg.apache.poi.hssf.usermodel.HSSFWorkbook;importorg.apache.poi.ss.usermodel.Cell;importorg.apache.poi.ss.usermodel.DateUtil;importorg.apache.poi.ss.usermodel.Row;importorg.apache.poi.ss.usermodel.Sheet;importorg.apache.poi.ss.usermodel.Workbook;importorg.apache.poi.xssf.usermodel.XSSFWorkbook;/*** excel解析工具类

*@authorMarydon

* @createTime 2018年2月1日下午12:54:09

* @updateTime

* @Email:Marydon20170307@163.com

*@version:1.0.0*/

public classReadExcelUtils {private Logger logger = Logger.getLogger(this.getClass());privateWorkbook wb;privateSheet sheet;privateRow row;publicReadExcelUtils(String filepath) {if (filepath == null) {return;

}

String fileType= filepath.substring(filepath.lastIndexOf("."));try{

InputStream is= newFileInputStream(filepath);if (".xls".equals(fileType)) {

wb= newHSSFWorkbook(is);

}else if (".xlsx".equals(fileType)) {

wb= newXSSFWorkbook(is);

}else{

wb= null;

}

}catch(FileNotFoundException e) {

logger.error("FileNotFoundException", e);

}catch(IOException e) {

logger.error("IOException", e);

}

}/*** 读取Excel表格表头的内容

*

*@paramInputStream

*@returnString 表头内容的数组

*@authorzengwendong*/

public String[] readExcelTitle() throwsException {if (wb == null) {throw new Exception("Workbook对象为空！");

}

sheet= wb.getSheetAt(0);

row= sheet.getRow(0);//标题总列数

int colNum =row.getPhysicalNumberOfCells();

System.out.println("colNum:" +colNum);

String[] title= newString[colNum];for (int i = 0; i < colNum; i++) {

title[i]=row.getCell(i).getCellFormula();

}returntitle;

}/*** 读取Excel数据内容

* @description

*@return包含单元格数据内容的List对象

*@throwsException*/

public List readExcelContent(List columnsList) throwsException {if (wb == null) {throw new Exception("Workbook对象为空！");

}// List content = new ArrayList();

sheet= wb.getSheetAt(0);//得到总行数

int rowNum =sheet.getLastRowNum();

row= sheet.getRow(0);int colNum =row.getPhysicalNumberOfCells();// Map cellValue = null;//

if (null == columnsList || columnsList.size() != colNum ||columnsList.isEmpty()) {//正文内容应该从第二行开始,第一行为表头的标题

for (int i = 1; i <= rowNum; i++) {//获取当前行

row =sheet.getRow(i);int j = 0;

cellValue= new HashMap();while (j

Object obj=getCellFormatValue(row.getCell(j));

cellValue.put(String.valueOf(j), obj);

j++;

}

content.add(cellValue);

}

}else{//正文内容应该从第二行开始,第一行为表头的标题

for (int i = 1; i <= rowNum; i++) {//获取当前行

row =sheet.getRow(i);int j = 0;

cellValue= new HashMap();while (j

Object obj=getCellFormatValue(row.getCell(j));

cellValue.put(columnsList.get(j), obj);

j++;

}

content.add(cellValue);

}

}returncontent;

}/***

* 根据Cell类型设置数据

*

*@paramcell

*@return*@authorzengwendong*/

privateObject getCellFormatValue(Cell cell) {

Object cellvalue= "";//非空

if (null != cell && (!"".equals(cell.toString()))) {//判断当前Cell的Type

switch(cell.getCellType()) {case Cell.CELL_TYPE_NUMERIC://如果当前Cell的Type为NUMERIC

caseCell.CELL_TYPE_FORMULA: {//判断当前的cell是否为Date

if(DateUtil.isCellDateFormatted(cell)) {//如果是Date类型则，转化为Data格式//data格式是带时分秒的：2013-7-10 0:00:00//cellvalue = cell.getDateCellValue().toLocaleString();//data格式是不带带时分秒的：2013-7-10

Date date =cell.getDateCellValue();

cellvalue=date;

}else {//如果是纯数字//取得当前Cell的数值

cellvalue =String.valueOf(cell.getNumericCellValue());

}break;

}case Cell.CELL_TYPE_STRING://如果当前Cell的Type为STRING//取得当前的Cell字符串

cellvalue =cell.getRichStringCellValue().getString();break;default://默认的Cell值

cellvalue = "";

}

}else{

cellvalue= "";

}returncellvalue;

}

}