jfinal 导出excle
Controller
Map<String,List<PoiUtilHeader>> result = new HashMap<String, List<PoiUtilHeader>>(); List<PoiUtilHeader> row0 = new ArrayList<PoiUtilHeader>(); row0.add(new PoiUtilHeader(0,"id",1,false)); row0.add(new PoiUtilHeader(0,"name",1,false)); row0.add(new PoiUtilHeader(0,"age",1,false)); row0.add(new PoiUtilHeader(0,"statue",1,false)); List<Record> invList =Db.find("select * from user"); String uuid=UuidUtil.get16UUId()+"RZJGDC.xls"; String[] fields=new String[]{"id","name","age","statue"}; render(PoiUtilRender.data(invList).headers(result).fileName(uuid).fields(fields));
PoiUtilHeader
package com.tax.common.util; public class PoiUtilHeader{ //表头等级 private int level=0; //表头名称 private String columnsName=""; //所占列数 private int cellCount=0; //是否有子节点 private boolean isChildren=false; public PoiUtilHeader(int level,String columnsName,int cellCount,boolean isChildren){ this.level=level; this.columnsName=columnsName; this.cellCount=cellCount; this.isChildren=isChildren; } public PoiUtilHeader(int level,String columnsName,int cellCount){ this.level=level; this.columnsName=columnsName; this.cellCount=cellCount; } public PoiUtilHeader(int level,String columnsName,boolean isChildren){ this.level=level; this.columnsName=columnsName; this.isChildren=isChildren; } public PoiUtilHeader(int level,String columnsName){ this.level=level; this.columnsName=columnsName; } public int getLevel() { return level; } public void setLevel(int level) { this.level = level; } public String getColumnsName() { return columnsName; } public void setColumnsName(String columnsName) { this.columnsName = columnsName; } public int getCellCount() { return cellCount; } public void setCellCount(int cellCount) { this.cellCount = cellCount; } public boolean getIsChildren() { return isChildren; } public void setIsChildren(boolean isChildren) { this.isChildren = isChildren; } }
service 数据处理
List listexcel = new ArrayList(); if(!invlist.isEmpty()){ for (int i = 0; i < invlist.size(); i++) { Record mapDc = new Record(); Record record = (Record) invlist.get(i); String statuea=""; if (record.get("statue").equals("0")) { statuea="正常"; }else{ statuea="作废"; } mapDc.set("invtype", invtypea); mapDc.set("period", record.get("id", "")); mapDc.set("create_time", record.get("name", "")); mapDc.set("create_time", record.get("age", "")); listexcel.add(mapDc); } } return listexcel;
PoiUtilRender
package com.tax.common.util; import java.io.IOException; import java.io.OutputStream; import java.util.ArrayList; import java.util.HashMap; import java.util.HashSet; import java.util.List; import java.util.Map; import java.util.Set; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.util.CellRangeAddress; import com.jfinal.plugin.activerecord.Model; import com.jfinal.plugin.activerecord.Record; import com.jfinal.render.Render; import com.jfinal.render.RenderException; /** * 导出2003的excel表格,超过65535行可能会报错 * 多行跨行跨列表头算法 * @author by:zy */ @SuppressWarnings("unchecked") public class PoiUtilRender extends Render { public final static String H_MAP_NAME="tName"; public final static String H_MAP_SUBNAME="tSubName"; private final String CONTENT_TYPE = "application/msexcel;charset=" + getEncoding(); /*private final String VERSION_2003 = "2003"; private final int MAX_ROWS = 65535;*/ private String fileName = "file1.xls"; private String sheetName = "sheet"; private Map<String,List<PoiUtilHeader>> headers = new HashMap<String,List<PoiUtilHeader>>(); private String[] fields =null; private List<?> data =null; public PoiUtilRender(List<?> data){ this.data=data; } @Override public void render() { response.reset(); response.setHeader("Content-disposition", "attachment; filename=" + fileName); response.setContentType(CONTENT_TYPE); OutputStream os = null; try { os = response.getOutputStream(); export().write(os); } catch (Exception e) { throw new RenderException(e); } finally { try { if (os != null) { os.flush(); os.close(); } } catch (IOException e) { //LOG.error(e.getMessage(), e); } } } /** * 核心方法 */ public Workbook export() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(sheetName); sheet.setDefaultColumnWidth(12); Row row; Cell cell; //表头 int headerRow =0; if (this.headers != null && !this.headers.isEmpty()) { headerRow = headers.size(); HSSFCellStyle headerStyle = this.getColumnTopStyle(wb);//获取列头样式 Set<String> occupyCell =new HashSet<String>(); //总行数 int maxCellCount=0; for (PoiUtilHeader p : headers.get("row0")) { maxCellCount+=p.getCellCount(); } for (int i = 0, len = headers.size(); i < len; i++) { List<PoiUtilHeader> list = headers.get("row" + i); row = sheet.createRow(i); row.setHeightInPoints(30);//设置高度 int index = 0; int lastIndex = list.size(); for (int j = 0, jlen = maxCellCount; j < jlen; j++) { //超出索引退出 if(index>=lastIndex){break;} //是否已被占用 if(occupyCell.contains(i+"-"+j)){continue;} PoiUtilHeader h = list.get(index); //计算跨行的起点 int lastRowNum=i; //计算跨行跨列 int lastCellNum = j + h.getCellCount()-1; if(!h.getIsChildren()){lastRowNum=len-1;} cell = row.createCell(j); cell.setCellValue(h.getColumnsName()); cell.setCellStyle(headerStyle); for(int r = i ; r<=lastRowNum ; r++){ for(int c = j ; c<=lastCellNum ; c++){ occupyCell.add(r+"-"+c); } } sheet.addMergedRegion(new CellRangeAddress(i, lastRowNum, j, lastCellNum)); index++; } } } //sheet.setColumnWidth(j, h.getColumnsName().getBytes().length*2*1000); //内容 if(data!=null){ for (int i = 0, len = data.size(); i < len; i++) { row = sheet.createRow(i + headerRow); row.setHeightInPoints(20);//设置高度 Object obj = data.get(i); if (obj == null) { continue; } if (obj instanceof Map) { processAsMap(row, obj); } else if (obj instanceof Model) { processAsModel(row, obj); } else if (obj instanceof Record) { processAsRecord(row, obj); } else if(obj instanceof List){ processAsList(row,obj); }else{ throw new RuntimeException("Not support type[" + obj.getClass() + "]"); } } } return wb; } /* * 列头单元格样式 */ public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) { // 设置字体 HSSFFont font = workbook.createFont(); //设置字体大小 font.setFontHeightInPoints((short)10); //字体加粗 //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //设置字体名字 font.setFontName("Microsoft YaHei"); //设置样式; HSSFCellStyle style = workbook.createCellStyle(); //在样式用应用设置的字体; style.setFont(font); //设置自动换行; style.setWrapText(true); //设置水平对齐的样式为居中对齐; style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //设置垂直对齐的样式为居中对齐; style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //设置背景颜色; style.setFillForegroundColor(new HSSFColor.BLUE_GREY().getIndex()); return style; } private void processAsMap(Row row, Object obj) { Cell cell; Map<String, Object> map = (Map<String, Object>) obj; if(fields!=null && fields.length>0){ for(int columnIndex = 0 , len = fields.length ; columnIndex<len ; columnIndex++){ String key = fields[columnIndex]; cell = row.createCell(columnIndex); cell.setCellValue(map.get(key) == null ? "" : map.get(key) + ""); } }else{ int columnIndex = 0; for(String key : map.keySet()){ cell = row.createCell(columnIndex); cell.setCellValue(map.get(key) == null ? "" : map.get(key) + ""); columnIndex++; } } } private void processAsModel(Row row, Object obj) { Cell cell; Model<?> model = (Model<?>) obj; if(fields!=null && fields.length>0){ for(int columnIndex = 0 , len = fields.length ; columnIndex<len ; columnIndex++){ String key = fields[columnIndex]; cell = row.createCell(columnIndex); cell.setCellValue(model.get(key) == null ? "" : model.get(key) + ""); } }else{ int columnIndex = 0; Object[] vals = model._getAttrValues(); for(Object v : vals){ cell = row.createCell(columnIndex); cell.setCellValue(v == null ? "" : v + ""); columnIndex++; } } } private void processAsList(Row row, Object obj) { Cell cell; System.out.println("scb"); List record = (List) obj; if(fields!=null && fields.length>0){ for(int columnIndex = 0 , len = fields.length ; columnIndex<len ; columnIndex++){ String key = fields[columnIndex]; cell = row.createCell(columnIndex); cell.setCellValue(record.get(0) == null ? "" : record.get(0) + ""); } }else{ int columnIndex = 0; Record red=new Record(); Object[] vals = red.getColumnValues(); for(Object v : vals){ cell = row.createCell(columnIndex); cell.setCellValue(v == null ? "" : v + ""); columnIndex++; } } } private void processAsRecord(Row row, Object obj) { Cell cell; Record record = (Record) obj; if(fields!=null && fields.length>0){ for(int columnIndex = 0 , len = fields.length ; columnIndex<len ; columnIndex++){ String key = fields[columnIndex]; cell = row.createCell(columnIndex); cell.setCellValue(record.get(key) == null ? "" : record.get(key) + ""); } }else{ int columnIndex = 0; Object[] vals = record.getColumnValues(); for(Object v : vals){ cell = row.createCell(columnIndex); cell.setCellValue(v == null ? "" : v + ""); columnIndex++; } } } private int setHeader(int level , List<Map<String, Object>> titles){ if(titles==null || titles.isEmpty()) return 1; int resultNum=0; List<PoiUtilHeader> h = (this.headers.get("row"+level)==null?new ArrayList<PoiUtilHeader>():this.headers.get("row"+level)); for(Map<String,Object> t : titles){ int subNum = 0; boolean isChildren=false; if(t.get(H_MAP_SUBNAME) instanceof List){ isChildren=true; List<Map<String,Object>> subList = (List<Map<String,Object>>)t.get(H_MAP_SUBNAME) ; subNum += setHeader(level+1, subList); } subNum = (subNum>0?subNum:1); resultNum+=subNum; h.add(new PoiUtilHeader(level, t.get(H_MAP_NAME)+"",subNum,isChildren)); } this.headers.put("row"+level,h); if(resultNum>titles.size()){ return resultNum; }else{ return titles.size(); } } public static PoiUtilRender data(List<?> data) { return new PoiUtilRender(data); } /*** * 复杂的复合表头 * tName : 表头名称 * tSubName : 下级表头 List 数据 * * 常量: * public final String H_MAP_NAME="tName"; * public final String H_MAP_SUBNAME="tSubName"; */ public PoiUtilRender headers(List<Map<String, Object>> titles) { this.headers.clear(); if(titles!=null && !titles.isEmpty()){ List<PoiUtilHeader> h = new ArrayList<PoiUtilHeader>(); for(Map<String,Object> t : titles){ int subNum = 0; boolean isChildren=false; if(t.get(H_MAP_SUBNAME) instanceof List){ isChildren=true; List<Map<String,Object>> subList = (List<Map<String,Object>>)t.get(H_MAP_SUBNAME) ; subNum += setHeader(1, subList); } subNum = (subNum>0?subNum:1); h.add(new PoiUtilHeader(0, t.get(H_MAP_NAME)+"",subNum,isChildren)); } this.headers.put("row0",h); } return this; } /** * 设置普通单行表头 */ public PoiUtilRender headers(String[] titles) { this.headers.clear(); List<PoiUtilHeader> h = new ArrayList<PoiUtilHeader>(); for(String t :titles){ h.add(new PoiUtilHeader(0, t, 1, false)); } this.headers.put("row0",h); return this; } /** * 设置复杂表头 */ public PoiUtilRender headers(Map<String,List<PoiUtilHeader>> titles) { this.headers.clear(); this.headers=titles; return this; } /** * 设置文件名称 */ public PoiUtilRender fileName(String fileName) { this.fileName = fileName; return this; } /** * 设置sheet名称 */ public PoiUtilRender sheetName(String sheetName) { this.sheetName = sheetName; return this; } /** * 设置数据字段名称,字段的顺序就是excel列的顺序,不设置就是按照存储集合的顺序排列 */ public PoiUtilRender fields(String[] fields) { this.fields = fields; return this; } }
