java excel导入_JAVA Excel导入导出

packagecom.glory.venus.common.utils;importjava.io.IOException;importjava.io.InputStream;importjava.lang.reflect.Field;importjava.lang.reflect.InvocationTargetException;importjava.lang.reflect.Method;importjava.util.ArrayList;importjava.util.List;importorg.apache.poi.EncryptedDocumentException;importorg.apache.poi.hssf.usermodel.HSSFWorkbook;importorg.apache.poi.openxml4j.exceptions.InvalidFormatException;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.ss.usermodel.WorkbookFactory;importorg.apache.poi.xssf.usermodel.XSSFWorkbook;/***@authorme

* @date 2017-06-08 16:29:25*/

public classExcelUtil {/*** 生成2007及以后版本

*@paramlist 数据集合

*@paramtitle 表头 表头名称的顺序要与list里的object属性的顺序对应

*@paramsheetName sheet名称

*@return

*/

public static Workbook getXSSFWorkbook(Listlist,String[] title,String sheetName){

Workbook wb= new XSSFWorkbook();//2007及以后版本 .xslx

return createExcel(wb,list,title,sheetName,0,0);

}/*** 2003及以前的版本

*@paramlist 数据集合

*@paramtitle 表头 表头名称的顺序要与list里的object属性的顺序对应

*@paramsheetName sheet名称

*@return

*/

public static Workbook getHSSFWorkbook(Listlist,String[] title,String sheetName){

Workbook wb= new HSSFWorkbook();//2003及以前的版本 .xsl

return createExcel(wb,list,title,sheetName,0,0);

}/*** 生成Excel文件

*@paramlist 数据集合

*@paramtitle 表头

*@paramsheetName sheet名称

*@paramrowNum 从第几行开始写入

*@paramcolNum 从第几列开始写入

*@return

*/

private static Workbook createExcel(Workbook wb,List list,String[] title,String sheetName,int rowNum,intcolNum) {if(sheetName == null || "".equals(sheetName)){

sheetName= "sheet1";

}

Sheet sheet=wb.createSheet(sheetName);

Row topRow=sheet.createRow(rowNum);for (int i = 0; i < title.length; i++) {

sheet.autoSizeColumn(i,true);

topRow.createCell(i+colNum).setCellValue(title[i]);

}if(list == null || list.size() == 0){returnwb;

}

Object[] objs= null;for (int i = 0; i < list.size(); i++) {

rowNum++;

Row row=sheet.createRow(rowNum);

objs=getFieldValues(list.get(i));for (int j = 0; j < objs.length; j++) {

String value= objs[j] == null ? "": objs[j].toString();

row.createCell(j+colNum).setCellValue(value);

}

}returnwb;

}/*** 获取对象属性的值

*@paramo

*@return

*/

private staticObject[] getFieldValues(Object o) {

Object[] value= null;try{

Field[] fields=o.getClass().getDeclaredFields();

value= newObject[fields.length];for (int i = 0; i < fields.length; i++) {

String firstLetter= fields[i].getName().substring(0, 1).toUpperCase();

String getter= "get" + firstLetter + fields[i].getName().substring(1);

Method method= o.getClass().getMethod(getter, newClass[] {});

value[i]= method.invoke(o, newObject[] {});

}

}catch(SecurityException e) {

e.printStackTrace();

}catch(NoSuchMethodException e) {

e.printStackTrace();

}catch(IllegalAccessException e) {

e.printStackTrace();

}catch(IllegalArgumentException e) {

e.printStackTrace();

}catch(InvocationTargetException e) {

e.printStackTrace();

}returnvalue;

}/*** 获取对象属性名称

*@paramo

*@return

*/

private staticString[] getFieldValue(Object o) {

Field[] fields=o.getClass().getDeclaredFields();

String[] value= newString[fields.length];for (int i = 0; i < fields.length; i++) {

value[i]=fields[i].getName();

}returnvalue;

}/*** 读取Excel

*@paramis Excel的文件流

*@return

*/

public staticExcelCellBean readExcel(InputStream is){

Workbook workbook= null;

Sheet sheet= null;try{

workbook=WorkbookFactory.create(is);

sheet= workbook.getSheetAt(0); //读取第几个工作表sheet

int rowNum = sheet.getLastRowNum();//有多少列

ExcelCellBean eb = newExcelCellBean();

String[] title= new String[0];

String[] value= new String[0];

List values = new ArrayList();int c = 0;//用去区分表头和数值

int colNum = 0;//总共有多少列

int firstCellNum = 0;//第几列开始读取

for(int k = 0;k <= rowNum;k++){

Row row= sheet.getRow(k);//从第几行开始读

if(row == null){//过滤空行

continue;

}if(c == 0){

firstCellNum=row.getFirstCellNum();

colNum= row.getLastCellNum() -row.getFirstCellNum();

title= newString[colNum];

}else{

value= newString[colNum];

}int count = 0;//计算放进数值里面的顺序

for (int i = firstCellNum; i < colNum+firstCellNum; i++) {if(c == 0){

title[count]=getCellFormatValue(row.getCell(i));

}else{

value[count]=getCellFormatValue(row.getCell(i));

}

count++;

}if(c > 0){

values.add(value);

}

c++;

}

eb.setTitle(title);

eb.setValues(values);returneb;

}catch(IOException e) {

e.printStackTrace();

}catch(EncryptedDocumentException e) {

e.printStackTrace();

}catch(InvalidFormatException e) {

e.printStackTrace();

}return null;

}/*** 从cell获取值

*@paramcell

*@return

*/

private staticString getCellFormatValue(Cell cell) {

String cellvalue= "";if(cell != null){switch(cell.getCellTypeEnum()) {caseNUMERIC:if(DateUtil.isCellDateFormatted(cell)){//日期类型

cellvalue = cell.getDateCellValue() + "";

}else{

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

}break;caseBOOLEAN:

cellvalue= cell.getBooleanCellValue() + "";break;caseFORMULA:

cellvalue= "公式";break;caseERROR:

cellvalue= cell.getErrorCellValue() + "";break;//case BLANK://空白//case STRING:

default:

cellvalue=cell.getStringCellValue();break;

}

}returncellvalue;

}

}