一、前言
基于apache的poi-3.9源码包org.apache.poi.hssf的excel解析定义ExportExcelUtil工具类，通过定义sheet名称/表头head/对象属性/数据进行通用数据export导出excel的数据后缀为xls数据，详情参见代码示例。
二、代码示例
 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 | package test;import java.io.FileOutputStream;import java.io.OutputStream;import java.lang.reflect.Method;import java.util.ArrayList;import java.util.Collection;import java.util.Iterator;import java.util.List;import javax.servlet.http.HttpServletResponse;import org.apache.commons.logging.Log;import org.apache.commons.logging.LogFactory;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFFont;import org.apache.poi.hssf.usermodel.HSSFRichTextString;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.util.HSSFColor; public class ExportExcelUtil { private static Log logger=LogFactory.getLog(ExportExcelUtil.class); /** * 导出指定sheet名称配置的excel表格 * @param sheetName * @param headers * @param headerFields * @param rowdata * @param response * @throws Exception */ public static void export(String sheetName,String [] headers,String[] headerFields,Object rowdata, HttpServletResponse response) throws Exception { if(null==response||null==response.getOutputStream()){ logger.error("exportExcel error,cause response object is null!"); throw new RuntimeException("exportExcel error,cause response object is null!"); }else{ List<Object> rowdatas=new ArrayList<Object>(); rowdatas.add(rowdata); export(sheetName,headers,headerFields,rowdatas,response.getOutputStream()); } } public static void export(String sheetName,String [] headers,String[] headerFields,Collection<?> rowDatas, HttpServletResponse response) throws Exception { if(null==response||null==response.getOutputStream()){ logger.error("exportExcel error,cause response object is null!"); throw new RuntimeException("exportExcel error,cause response object is null!"); }else{ export(sheetName,headers,headerFields,rowDatas,response.getOutputStream()); } } public static void export(String sheetName,String [] headers,String[] headerFields,Collection<?> rowDatas, OutputStream outstream) throws Exception { if(outstream==null){ logger.error("exportExcel error,outstream is null!"); throw new RuntimeException("exportExcel error,outstream is null!"); } try { parseExcelData(sheetName, headers,headerFields, rowDatas, outstream); } catch (Exception e) { logger.error("exportExcel error,headers or headerFields set error!",e); throw new RuntimeException("exportExcel error,headers or headerFields set error!"); }finally{ outstream.flush(); outstream.close(); } } @SuppressWarnings("deprecation") private static void parseExcelData(String sheetName, String[] headers,String[] headerFields, Collection<?> rowDatas, OutputStream outstream) throws Exception { // 声明一个工作薄 HSSFWorkbook workbook = new HSSFWorkbook(); // 生成一个表格 HSSFSheet sheet = workbook.createSheet(sheetName); // 设置表格默认列宽度为15个字节 sheet.setDefaultColumnWidth((short) 15); // 生成一个样式 HSSFCellStyle style = workbook.createCellStyle(); // 设置这些样式 style.setFillForegroundColor(HSSFColor.SKY_BLUE.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 生成一个字体 HSSFFont font = workbook.createFont(); font.setColor(HSSFColor.VIOLET.index); font.setFontHeightInPoints((short) 12); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 把字体应用到当前的样式 style.setFont(font); // 生成并设置另一个样式 HSSFCellStyle style2 = workbook.createCellStyle(); style2.setFillForegroundColor(HSSFColor.WHITE.index); style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_CENTER); style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 生成另一个字体 HSSFFont font2 = workbook.createFont(); font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); // 把字体应用到当前的样式 style2.setFont(font2); HSSFFont font3 = workbook.createFont(); font3.setColor(HSSFColor.BLACK.index); // 产生表格标题行 HSSFRow row = sheet.createRow(0); // 遍历集合数据，产生数据行 Iterator<?> it = rowDatas.iterator(); row = sheet.createRow(0); for (int j=0;j<headers.length;j++){ HSSFCell cell = row.createCell(j); setExcelHeaderText(cell,headers[j],style); } int index = 1; while (it.hasNext()) { Object valobj = it.next(); row = sheet.createRow(index); int i=0; for(String hf:headerFields){ HSSFCell cellx = row.createCell(i); Object valObj=getGetMethod(valobj,hf); setExcelText(cellx,style2,String.valueOf(getGetMethod(valobj,hf)),font3); i++; } index ++; } workbook.write(outstream); } public static Object getGetMethod(Object ob,String name)throws Exception{ Method[] m = ob.getClass().getMethods(); for(int i = 0;i < m.length;i++){ if(("get"+name).toLowerCase().equals(m[i].getName().toLowerCase())){ return m[i].invoke(ob); } } return null; } private static void setExcelText(HSSFCell cell,HSSFCellStyle style2,String textValue,HSSFFont font3){ cell.setCellStyle(style2); HSSFRichTextString richString = new HSSFRichTextString(textValue); richString.applyFont(font3); cell.setCellValue(richString); } private static void setExcelHeaderText(HSSFCell cell,String header,HSSFCellStyle style){ cell.setCellStyle(style); HSSFRichTextString text = new HSSFRichTextString(header); cell.setCellValue(text); } public static void main(String[] args) throws Exception{ CityDTO dto=new CityDTO(); dto.setCityName("Shanghai"); dto.setCityCode("200000"); System.out.println(ExportExcelUtil.getGetMethod(dto, "startNum")); FileOutputStream fos=new FileOutputStream("D:\\NIJUN\\citydata616.xls"); List<CityDTO> datas=new ArrayList<CityDTO>(); datas.add(dto); export("sheetname",new String[]{"城市","城市编码"},new String[]{"cityName","cityCode"},datas,fos); } public static class CityDTO{ private String cityName; private String cityCode; public String getCityName() { return cityName; } public void setCityName(String cityName) { this.cityName = cityName; } public String getCityCode() { return cityCode; } public void setCityCode(String cityCode) { this.cityCode = cityCode; } } } |
