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;
}
}
如何调用: