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