java excel 插件开发工具_使用POI插件，提取导出excel的工具类

packagecom.itcast.core.utils;importjava.io.File;importjava.util.ArrayList;importjava.util.Date;importjava.util.List;importjava.util.TimeZone;importnet.sf.ehcache.hibernate.management.impl.BeanUtils;importorg.apache.poi.hssf.usermodel.HSSFCell;importorg.apache.poi.hssf.usermodel.HSSFCellStyle;importorg.apache.poi.hssf.usermodel.HSSFDateUtil;importorg.apache.poi.hssf.usermodel.HSSFFont;importorg.apache.poi.hssf.usermodel.HSSFName;importorg.apache.poi.hssf.usermodel.HSSFRow;importorg.apache.poi.hssf.usermodel.HSSFSheet;importorg.apache.poi.hssf.usermodel.HSSFWorkbook;importorg.apache.poi.hssf.util.HSSFCellUtil;importorg.apache.poi.hssf.util.HSSFColor;importorg.apache.poi.ss.usermodel.Cell;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.ss.util.CellRangeAddress;public classExcelUtil {privateExcelUtil() {

}/*** 导出excel头部标题

*@paramtitle

*@paramcellRangeAddressLength

*@return

*/

public static HSSFWorkbook makeExcelHead(String title, intcellRangeAddressLength){

HSSFWorkbook workbook= newHSSFWorkbook();

HSSFCellStyle styleTitle= createStyle(workbook, (short)16);

HSSFSheet sheet=workbook.createSheet(title);

sheet.setDefaultColumnWidth(25);

CellRangeAddress cellRangeAddress= new CellRangeAddress(0, 0, 0, cellRangeAddressLength);

sheet.addMergedRegion(cellRangeAddress);

HSSFRow rowTitle= sheet.createRow(0);

HSSFCell cellTitle= rowTitle.createCell(0);//为标题设置背景颜色

styleTitle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

styleTitle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);

cellTitle.setCellValue(title);

cellTitle.setCellStyle(styleTitle);returnworkbook;

}/*** 设定二级标题

*@paramworkbook

*@paramsecondTitles

*@return

*/

public staticHSSFWorkbook makeSecondHead(HSSFWorkbook workbook, String[] secondTitles){//创建用户属性栏

HSSFSheet sheet = workbook.getSheetAt(0);

HSSFRow rowField= sheet.createRow(1);

HSSFCellStyle styleField= createStyle(workbook, (short)13);for (int i = 0; i < secondTitles.length; i++) {

HSSFCell cell=rowField.createCell(i);

cell.setCellValue(secondTitles[i]);

cell.setCellStyle(styleField);

}returnworkbook;

}/*** 插入数据

*@paramworkbook

*@paramdataList

*@parambeanPropertys

*@return

*/

public static HSSFWorkbook exportExcelData(HSSFWorkbook workbook, ListdataList, String[] beanPropertys) {

HSSFSheet sheet= workbook.getSheetAt(0);//填充数据

HSSFCellStyle styleData =workbook.createCellStyle();

styleData.setAlignment(HSSFCellStyle.ALIGN_CENTER);

styleData.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);for (int j = 0; j < dataList.size(); j++) {

HSSFRow rowData= sheet.createRow(j + 2);

T t=dataList.get(j);for(int k=0; k

Object value=BeanUtils.getBeanProperty(t, beanPropertys[k]);

HSSFCell cellData=rowData.createCell(k);

cellData.setCellValue(value.toString());

cellData.setCellStyle(styleData);

}

}returnworkbook;

}/*** 使用批量导入方法时，请注意需要导入的Bean的字段和excel的列一一对应

*@paramclazz

*@paramfile

*@parambeanPropertys

*@return

*/

public static List parserExcel(Classclazz, File file, String[] beanPropertys) {//得到workbook

List list = new ArrayList();try{

Workbook workbook=WorkbookFactory.create(file);

Sheet sheet= workbook.getSheetAt(0);//直接从第三行开始获取数据

int rowSize =sheet.getPhysicalNumberOfRows();if(rowSize > 2){for (int i = 2; i < rowSize; i++) {

T t=clazz.newInstance();

Row row=sheet.getRow(i);int cellSize =row.getPhysicalNumberOfCells();for(int j=0; j

Object cellValue=getCellValue(row.getCell(j));

org.apache.commons.beanutils.BeanUtils.copyProperty(t, beanPropertys[j], cellValue);

}

list.add(t);

}

}

}catch(Exception e) {

e.printStackTrace();

}returnlist;

}/*** 通用的读取excel单元格的处理方法

*@paramcell

*@return

*/

private staticObject getCellValue(Cell cell) {

Object result= null;if (cell != null) {switch(cell.getCellType()) {caseCell.CELL_TYPE_STRING:

result=cell.getStringCellValue();break;caseCell.CELL_TYPE_NUMERIC://对日期进行判断和解析

if(HSSFDateUtil.isCellDateFormatted(cell)){double cellValue =cell.getNumericCellValue();

result=HSSFDateUtil.getJavaDate(cellValue);

}break;caseCell.CELL_TYPE_BOOLEAN:

result=cell.getBooleanCellValue();break;caseCell.CELL_TYPE_FORMULA:

result=cell.getCellFormula();break;caseCell.CELL_TYPE_ERROR:

result=cell.getErrorCellValue();break;caseCell.CELL_TYPE_BLANK:break;default:break;

}

}returnresult;

}/*** 提取公共的样式

*@paramworkbook

*@paramfontSize

*@return

*/

private static HSSFCellStyle createStyle(HSSFWorkbook workbook, shortfontSize){

HSSFCellStyle style=workbook.createCellStyle();

style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//创建一个字体样式

HSSFFont font =workbook.createFont();

font.setFontHeightInPoints(fontSize);

font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

style.setFont(font);returnstyle;

}

}

如何调用：