一、前言
基于apache的poi-3.9源码包org.apache.poi.hssf的excel解析定义ExportExcelUtil工具类,通过定义sheet名称/表头head/对象属性/数据进行通用数据export导出excel的数据后缀为xls数据,详情参见代码示例。
二、代码示例
package test;@b@@b@import java.io.FileOutputStream;@b@import java.io.OutputStream;@b@import java.lang.reflect.Method;@b@import java.util.ArrayList;@b@import java.util.Collection;@b@import java.util.Iterator;@b@import java.util.List;@b@import javax.servlet.http.HttpServletResponse;@b@@b@import org.apache.commons.logging.Log;@b@import org.apache.commons.logging.LogFactory;@b@import org.apache.poi.hssf.usermodel.HSSFCell;@b@import org.apache.poi.hssf.usermodel.HSSFCellStyle;@b@import org.apache.poi.hssf.usermodel.HSSFFont;@b@import org.apache.poi.hssf.usermodel.HSSFRichTextString;@b@import org.apache.poi.hssf.usermodel.HSSFRow;@b@import org.apache.poi.hssf.usermodel.HSSFSheet;@b@import org.apache.poi.hssf.usermodel.HSSFWorkbook;@b@import org.apache.poi.hssf.util.HSSFColor;@b@ @b@public class ExportExcelUtil {@b@ @b@ private static Log logger=LogFactory.getLog(ExportExcelUtil.class);@b@@b@ /**@b@ * 导出指定sheet名称配置的excel表格@b@ * @param sheetName @b@ * @param headers@b@ * @param headerFields@b@ * @param rowdata@b@ * @param response@b@ * @throws Exception@b@ */@b@ public static void export(String sheetName,String [] headers,String[] headerFields,Object rowdata,@b@ HttpServletResponse response) throws Exception { @b@ if(null==response||null==response.getOutputStream()){@b@ logger.error("exportExcel error,cause response object is null!");@b@ throw new RuntimeException("exportExcel error,cause response object is null!");@b@ }else{@b@ List<Object> rowdatas=new ArrayList<Object>();@b@ rowdatas.add(rowdata);@b@ export(sheetName,headers,headerFields,rowdatas,response.getOutputStream());@b@ }@b@ }@b@ @b@ @b@ public static void export(String sheetName,String [] headers,String[] headerFields,Collection<?> rowDatas,@b@ HttpServletResponse response) throws Exception { @b@ if(null==response||null==response.getOutputStream()){@b@ logger.error("exportExcel error,cause response object is null!");@b@ throw new RuntimeException("exportExcel error,cause response object is null!");@b@ }else{@b@ export(sheetName,headers,headerFields,rowDatas,response.getOutputStream());@b@ }@b@ }@b@ @b@ public static void export(String sheetName,String [] headers,String[] headerFields,Collection<?> rowDatas,@b@ OutputStream outstream) throws Exception { @b@ if(outstream==null){@b@ logger.error("exportExcel error,outstream is null!");@b@ throw new RuntimeException("exportExcel error,outstream is null!");@b@ }@b@ try {@b@ parseExcelData(sheetName, headers,headerFields, rowDatas, outstream);@b@ } catch (Exception e) {@b@ logger.error("exportExcel error,headers or headerFields set error!",e);@b@ throw new RuntimeException("exportExcel error,headers or headerFields set error!");@b@ }finally{@b@ outstream.flush();@b@ outstream.close();@b@ }@b@ @b@ }@b@ @b@ @SuppressWarnings("deprecation")@b@ private static void parseExcelData(String sheetName, String[] headers,String[] headerFields,@b@ Collection<?> rowDatas, OutputStream outstream) throws Exception {@b@ // 声明一个工作薄@b@ HSSFWorkbook workbook = new HSSFWorkbook();@b@ // 生成一个表格@b@ HSSFSheet sheet = workbook.createSheet(sheetName);@b@ // 设置表格默认列宽度为15个字节@b@ sheet.setDefaultColumnWidth((short) 15);@b@ // 生成一个样式@b@ HSSFCellStyle style = workbook.createCellStyle();@b@ // 设置这些样式@b@ style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);@b@ style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);@b@ style.setBorderBottom(HSSFCellStyle.BORDER_THIN);@b@ style.setBorderLeft(HSSFCellStyle.BORDER_THIN);@b@ style.setBorderRight(HSSFCellStyle.BORDER_THIN);@b@ style.setBorderTop(HSSFCellStyle.BORDER_THIN);@b@ style.setAlignment(HSSFCellStyle.ALIGN_CENTER);@b@ // 生成一个字体@b@ HSSFFont font = workbook.createFont();@b@ font.setColor(HSSFColor.VIOLET.index);@b@ font.setFontHeightInPoints((short) 12);@b@ font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);@b@ // 把字体应用到当前的样式@b@ style.setFont(font);@b@ // 生成并设置另一个样式@b@ HSSFCellStyle style2 = workbook.createCellStyle();@b@ style2.setFillForegroundColor(HSSFColor.WHITE.index);@b@ style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);@b@ style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);@b@ style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);@b@ style2.setBorderRight(HSSFCellStyle.BORDER_THIN);@b@ style2.setBorderTop(HSSFCellStyle.BORDER_THIN);@b@ style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);@b@ style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);@b@ // 生成另一个字体@b@ HSSFFont font2 = workbook.createFont();@b@ font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);@b@ // 把字体应用到当前的样式@b@ style2.setFont(font2);@b@ HSSFFont font3 = workbook.createFont();@b@ font3.setColor(HSSFColor.BLACK.index);@b@@b@ // 产生表格标题行@b@ HSSFRow row = sheet.createRow(0);@b@ // 遍历集合数据,产生数据行@b@ Iterator<?> it = rowDatas.iterator();@b@ row = sheet.createRow(0);@b@ for (int j=0;j<headers.length;j++){@b@ HSSFCell cell = row.createCell(j);@b@ setExcelHeaderText(cell,headers[j],style);@b@ }@b@ int index = 1;@b@ while (it.hasNext()) {@b@ Object valobj = it.next(); @b@ row = sheet.createRow(index);@b@ @b@ int i=0;@b@ for(String hf:headerFields){@b@ HSSFCell cellx = row.createCell(i);@b@ Object valObj=getGetMethod(valobj,hf);@b@ setExcelText(cellx,style2,String.valueOf(getGetMethod(valobj,hf)),font3);@b@ i++;@b@ }@b@ index ++;@b@ }@b@ workbook.write(outstream);@b@ }@b@ @b@ @b@ public static Object getGetMethod(Object ob,String name)throws Exception{@b@ Method[] m = ob.getClass().getMethods();@b@ for(int i = 0;i < m.length;i++){@b@ if(("get"+name).toLowerCase().equals(m[i].getName().toLowerCase())){@b@ return m[i].invoke(ob);@b@ }@b@ }@b@ return null;@b@ }@b@@b@ @b@ private static void setExcelText(HSSFCell cell,HSSFCellStyle style2,String textValue,HSSFFont font3){ @b@ cell.setCellStyle(style2); @b@ HSSFRichTextString richString = new HSSFRichTextString(textValue);@b@ richString.applyFont(font3);@b@ cell.setCellValue(richString);@b@ }@b@ private static void setExcelHeaderText(HSSFCell cell,String header,HSSFCellStyle style){ @b@ cell.setCellStyle(style);@b@ HSSFRichTextString text = new HSSFRichTextString(header);@b@ cell.setCellValue(text); @b@ }@b@ @b@ public static void main(String[] args) throws Exception{@b@ CityDTO dto=new CityDTO();@b@ dto.setCityName("Shanghai");@b@ dto.setCityCode("200000");@b@ System.out.println(ExportExcelUtil.getGetMethod(dto, "startNum"));@b@ @b@ FileOutputStream fos=new FileOutputStream("D:\\NIJUN\\citydata616.xls");@b@ List<CityDTO> datas=new ArrayList<CityDTO>();@b@ datas.add(dto);@b@ @b@ export("sheetname",new String[]{"城市","城市编码"},new String[]{"cityName","cityCode"},datas,fos);@b@ @b@ }@b@ @b@ public static class CityDTO{@b@ private String cityName;@b@ private String cityCode;@b@ @b@ public String getCityName() {@b@ return cityName;@b@ }@b@ public void setCityName(String cityName) {@b@ this.cityName = cityName;@b@ }@b@ public String getCityCode() {@b@ return cityCode;@b@ }@b@ public void setCityCode(String cityCode) {@b@ this.cityCode = cityCode;@b@ }@b@ }@b@ @b@}