JAVA导出EXCEL实现的多种方式
java导出Excel的方法有多种，最为常用的方式就是使用第三方jar包，目前POI和JXL是最常用的二方包了，也推荐使用这两种。
POI实现
POI这里不详细解释，可参考徐老师发的博客：http://blog.csdn.net/evangel_z/article/details/7332535，他利用开源组件POI3.0.2动态导出EXCEL文档的通用处理类ExportExcel，详细使用方法下载最新代码看看就可以里，徐老师写的很明了！总之思路就是用Servlet接受post、get请求，获取文件导出路径，然后将测试数据封装好调用通用处理类导出Excel，然后再下载刚导出的Excel，会自动在浏览器弹出选择保存路径的弹出框，这样就达到里大家常见的文件导出下载的功能！当然，真正的项目里不可能把文件导出到本地，肯定是先吧文件导出到服务器上，再去服务器下载，对于用户来说就感觉好像直接就导出了！
这种实现逻辑也可以修改，就是把通用处理类ExportExcel从void改为返回read好数据的InputStream，而不要直接就去write，然后调用下载的方法downLoad使用HttpServletResponse.getOutputStream()所得到的输出流来write数据，然后调用flush()时就会在页面弹出选择路径的弹出框，选择好后数据就真正从缓存输出到了Excel中，这样就省去里中间先要导出一次的步骤了。
JXL实现
我这里讲一下JXL，其实和POI差不多，就是调用的组件不同，引入的jar包不同了，整个Excel导出下载的逻辑还是一样的。好了，直接上代码，都是通用代码，以后都能用的上。
先是几个mode类封装了在处理过程中会用到的模型。
ExcelColMode 主要封装的是Map中的key或者dto中实现get方法的字段名，其实就是表格的标题的属性名。
public class ExcelColMode { /** * Map中的key或者dto中实现get方法的字段名 */ private String name; /** 列宽 */ private Integer width; /** * 字体格式,可以设置字体大小，字体颜色，字体加粗 */ private ExcelFontFormat fontFormat; /** * 内容格式化 */ private ExcelColModelFormatterInter contentFormatter; public ExcelColMode(String name) { this.name = name; } public ExcelColMode(String name, Integer width) { this.name = name; this.width = width; } public String getName() { return name; } public void setName(String name) { this.name = name; } public ExcelFontFormat getFontFormat() { return fontFormat; } public void setFontFormat(ExcelFontFormat fontFormat) { this.fontFormat = fontFormat; } public ExcelColModelFormatterInter getContentFormatter() { return contentFormatter; } public void setContentFormatter(ExcelColModelFormatterInter contentFormatter) { this.contentFormatter = contentFormatter } public Integer getWidth() { return width; } public void setWidth(Integer width) { this.width = width; }}
- 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
ExcelHeadCell 主要封装的是标题名
public class ExcelHeadCell implements Comparable<ExcelHeadCell> { /** * 列合并 */ private int colSpan; /** * 展现字符内容 */ private String content; /** * 父列的序列号 */ private int fatherIndex; /** * 字体格式等 */ private ExcelFontFormat fontFormat; private Integer height; /** * 最基础的单元格,没有行合并和列合并 * * @param content */ public ExcelHeadCell(String content) { this.colSpan = 1; this.content = content; } public ExcelHeadCell(String content, Integer height) { this.colSpan = 1; this.content = content; this.height = height; } public ExcelHeadCell(String content, int fatherIndex, ExcelFontFormat fontFormat) { this.colSpan = 1; this.content = content; this.fatherIndex = fatherIndex; this.fontFormat = fontFormat; } public int getColSpan() { return colSpan; } public void setColSpan(int colSpan) { this.colSpan = colSpan; } public String getContent() { return content; } public void setContent(String content) { this.content = content; } public ExcelFontFormat getFontFormat() { return fontFormat; } public void setFontFormat(ExcelFontFormat fontFormat) { this.fontFormat = fontFormat; } public int getFatherIndex() { return fatherIndex; } public void setFatherIndex(int fatherIndex) { this.fatherIndex = fatherIndex; } public Integer getHeight() { return height; } public void setHeight(Integer height) { this.height = height; } public int compareTo(ExcelHeadCell o) { int i = -1; if (o == null) { i = 1; } else { i = o.fatherIndex > this.fatherIndex ? -1 : 1; if (o.fatherIndex == this.fatherIndex) { i = 0; } } return i; }}
- 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
ExcelExportRule 主要封装的是之前的ExcelColMode和ExcelHeadCell以及sheet页名称sheetName
public class ExcelExportRule { /** * 封装如何从数据集取数据，数据显示格式，日期格式和数字格式在这里设置 */ private List<ExcelColMode> colModes; /** * 封装EXCEL头部内容及内容显示格式 */ private List<List<ExcelHeadCell>> headCols; /** * 数据背景颜色区分,0:不区分,1:按行奇偶区分,奇数行白色，偶数行灰色，2:按列奇偶区分 奇数列白色，偶数列灰色， <br/> * <b>注意：此参数为0时，单元格设置的背景色才起作用</b> */ private int distinguishable = 0; /** * EXCEL的sheet页名称 */ private String sheetName; /** * 是否树形结构，1：是，0：否 */ private String hierarchical = "0"; /** * id字段名，当hierarchical="1"时候才起作用 */ private String idName; /** * 父id字段名，当hierarchical="1"时候才起作用 */ private String pidName; public List<ExcelColMode> getColModes() { return colModes; } public void setColModes(List<ExcelColMode> colModes) { this.colModes = colModes; } public List<List<ExcelHeadCell>> getHeadCols() { return headCols; } public void setHeadCols(List<List<ExcelHeadCell>> headCols) { this.headCols = headCols; } public int getDistinguishable() { return distinguishable; } public void setDistinguishable(int distinguishable) { this.distinguishable = distinguishable; } public String getSheetName() { return sheetName; } public void setSheetName(String sheetName) { this.sheetName = sheetName; } public String getHierarchical() { return hierarchical; } public void setHierarchical(String hierarchical) { this.hierarchical = hierarchical; } public String getIdName() { return idName; } public void setIdName(String idName) { this.idName = idName; } public String getPidName() { return pidName; } public void setPidName(String pidName) { this.pidName = pidName; } public void addExcelColMode(ExcelColMode excelColMode) { if (colModes == null) colModes = new ArrayList<ExcelColMode>(); colModes.add(excelColMode); } public void addExcelHeadCellList(List<ExcelHeadCell> list) { if (headCols == null) headCols = new ArrayList<List<ExcelHeadCell>>(); headCols.add(list); }}
- 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
ExcelFontFormat 封装的是表格的一些样式，如果对此没什么要求可以忽略
public class ExcelFontFormat { private int font = 0; // 字体 0:宋体,1:楷体,2:黑体，3：仿宋体，4:隶书 private Colour color = Colour.BLACK; // 字体颜色 private boolean bold = false; // 是否加粗 private int flow = 0; // 文字浮动方向,0:靠左(默认),1:居中,2:靠右, private int fontSize = 0; // 文字大小,0:正常,-2,-1,0,1,2,3,4依次加大,最大到4 private Colour backgroundColor = Colour.WHITE; // 单元格填充色 private boolean italic;// 是否斜体 private int verticalAlign = 1; // 文字上下对齐 0:上 1：中 2：下 public int getFont() { return font; } public void setFont(int font) { this.font = font; } public Colour getColor() { return color; } public void setColor(Colour color) { this.color = color; } public Colour getBackgroundColor() { return backgroundColor; } public void setBackgroundColor(Colour backgroundColor) { this.backgroundColor = backgroundColor; } public boolean isBold() { return bold; } public void setBold(boolean bold) { this.bold = bold; } public int getFontSize() { return fontSize; } public void setFontSize(int fontSize) { this.fontSize = fontSize; } public int getFlow() { return flow; } public void setFlow(int flow) { this.flow = flow; } public Alignment convertFlow() { return convertFlow(flow); } public static Alignment convertFlow(int flow) { Alignment al = null; switch (flow) { case 0: al = Alignment.LEFT; break; case 1: al = Alignment.CENTRE; break; case 2: al = Alignment.RIGHT; break; default: al = Alignment.LEFT; } return al; } public FontName convertFontName() { return convertFontName(font); } public static FontName convertFontName(int font) { FontName fn = null; switch (font) { case 0: fn = WritableFont.createFont("SimSun"); break; case 1: fn = WritableFont.createFont("KaiTi"); break; case 2: fn = WritableFont.createFont("SimHei"); break; case 3: fn = WritableFont.createFont("FangSong"); break; case 4: fn = WritableFont.createFont("LiSu"); break; default: fn = WritableFont.createFont("STSong"); } return fn; } public int convertFontSize() { return convertFontSize(fontSize); } public static int convertFontSize(int fontSize) { return 12 + fontSize * 2; } @Override public boolean equals(Object obj) { boolean eq = false; if (this == obj) { eq = true; } else if (obj != null && obj instanceof ExcelFontFormat) { ExcelFontFormat e = (ExcelFontFormat) obj; if (e.bold == this.bold && e.backgroundColor == this.backgroundColor && e.color == this.color && e.flow == this.flow && e.font == this.font && e.fontSize == this.fontSize && e.italic == this.italic) { eq = true; } } return eq; } public boolean isItalic() { return italic; } public void setItalic(boolean italic) { this.italic = italic; } public int getVerticalAlign() { return verticalAlign; } public void setVerticalAlign(int verticalAlign) { this.verticalAlign = verticalAlign; }}
- 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
4个mode类以及有了，我介绍的很简单，每个封装类其实还封装了一些其他的，但因为我的例子就只用到了这些就不多讲了。下面是Excel处理类ExcelHelper，代码比较多，其实大家不用管太多，粘贴过来用就行了，只要知道怎么用他(包括输入给些什么，输出的ByteArrayInputStream怎么用)就行。
import java.io.ByteArrayInputStream;import java.io.ByteArrayOutputStream;import java.io.IOException;import java.io.InputStream;import java.lang.reflect.InvocationTargetException;import java.lang.reflect.Method;import java.math.BigDecimal;import java.util.ArrayList;import java.util.Collections;import java.util.HashMap;import java.util.HashSet;import java.util.List;import java.util.Map;import java.util.Set;import jxl.Workbook;import jxl.format.Colour;import jxl.format.UnderlineStyle;import jxl.format.VerticalAlignment;import jxl.write.Label;import jxl.write.WritableCellFormat;import jxl.write.WritableFont;import jxl.write.WritableFont.FontName;import jxl.write.WritableSheet;import jxl.write.WritableWorkbook;import jxl.write.WriteException;import jxl.write.biff.JxlWriteException;import jxl.write.biff.RowsExceededException;import org.apache.commons.logging.Log;import org.apache.commons.logging.LogFactory;import com.newsee.dto.common.ExcelColMode;import com.newsee.dto.common.ExcelExportRule;import com.newsee.dto.common.ExcelFontFormat;import com.newsee.dto.common.ExcelHeadCell;public class ExcelHelper { private static Log log = LogFactory.getLog(ExcelHelper.class); /** * 实际需要展现的数据,支持DTO和Map */ private List<Object> rowDatas; private Set<Object> writed; /** * 取数据及数据展现相关 */ private List<ExcelColMode> colModes; /** * 行头(横向排列),如果有父行头则按父行头的顺序，没有父行头的按List顺序排列 */ private List<List<ExcelHeadCell>> headCols; /** * 数据背景颜色区分,0:不区分,1:按行奇偶区分,2:按列奇偶区分 */ private int distinguishable; /** * 缓存展现内容的sheet页 */ private WritableSheet sheet; /** * 缓存单元格格式 */ private Map<ExcelFontFormat, WritableCellFormat> mappedFormat; /** * id字段名称,用于树形结构 */ private String idName; /** * 父id字段名称，用于树形结构 */ private String pidName; private static String ONE_BLANK = " "; private int curDataRowIndex; private int curExcelRowIndex; public InputStream writeExcel(List<Object> rowDatas, ExcelExportRule rule) throws IOException, WriteException, SecurityException, IllegalArgumentException, NoSuchMethodException, IllegalAccessException, InvocationTargetException { if (rule != null) { this.rowDatas = rowDatas; this.colModes = rule.getColModes(); this.headCols = rule.getHeadCols(); this.distinguishable = rule.getDistinguishable(); if (validate()) { ByteArrayOutputStream outputStream = new ByteArrayOutputStream(); WritableWorkbook workbook = Workbook.createWorkbook(outputStream); String sheetName = rule.getSheetName(); if (StringUtil.isBlank(sheetName)) { sheetName = "sheet0"; } sheet = workbook.createSheet(sheetName, 0); // 设置列宽 for (int i = 0; i < colModes.size(); i++) { ExcelColMode colMode = colModes.get(i); if (colMode.getWidth() != null) sheet.setColumnView(i, colMode.getWidth()); } writeHeads(); // 树形结构 if ("1".equals(rule.getHierarchical())) { this.idName = rule.getIdName(); this.pidName = rule.getPidName(); writeTreeBody(); } // 非树形结构 else { writeBody(); } workbook.write(); workbook.close(); return new ByteArrayInputStream(outputStream.toByteArray()); } } else { log.error("ExcelExportRule为空，无法导出excel"); } return null; } private boolean validate() { if (colModes == null || colModes.size() == 0) { log.error("读取数据的规则ExcelExportRule.colModes为空"); return false; } return true; } private void writeHeads() throws JxlWriteException, WriteException { curExcelRowIndex = 0; if (headCols != null && !headCols.isEmpty()) { int s = headCols.size(); if (s > 1) { caculaterHeadColSpans(); } for (int i = 0; i < s; i++) { int tempColIndex = 0; List<ExcelHeadCell> headRowCols = headCols.get(i); for (int j = 0; j < headRowCols.size(); j++) { ExcelHeadCell headCol = headRowCols.get(j); writeHeadCell(headCol, tempColIndex); tempColIndex += headCol.getColSpan(); } curExcelRowIndex++; } } } // 计算标题需要列数 private void caculaterHeadColSpans() { int s = headCols.size(); for (int i = s - 1; i > 0; i--) { List<ExcelHeadCell> subCols = headCols.get(i); Collections.sort(subCols); List<ExcelHeadCell> supCols = headCols.get(i - 1); int[] fatherColSpans = new int[supCols.size()]; for (ExcelHeadCell subCol : subCols) { int fi = subCol.getFatherIndex(); fatherColSpans[fi] += subCol.getColSpan(); } for (int j = 0; j < supCols.size(); j++) { ExcelHeadCell supCol = supCols.get(j); if (fatherColSpans[j] > 0) { supCol.setColSpan(fatherColSpans[j]); } } } } private void writeHeadCell(ExcelHeadCell headCol, int colIndex) throws JxlWriteException, WriteException { ExcelFontFormat eff = headCol.getFontFormat(); String content = headCol.getContent(); int colspan = headCol.getColSpan(); if (headCol.getHeight() != null) sheet.setRowView(curExcelRowIndex, headCol.getHeight(), false); writeCell(content, eff, colIndex, colspan); } private void writeCell(String content, ExcelFontFormat eff, int colIndex, int colspan) throws JxlWriteException, WriteException { if (eff != null) { WritableCellFormat wcf = getCellFormat(eff); sheet.addCell(new Label(colIndex, curExcelRowIndex, content, wcf)); } else { sheet.addCell(new Label(colIndex, curExcelRowIndex, content)); } if (colspan > 1) { sheet.mergeCells(colIndex, curExcelRowIndex, colIndex + colspan - 1, curExcelRowIndex); } } /** * 从缓存中取格式化的字体,没有则新建并缓存,生成EXCELL完成后需要清除缓存的字体 * * @param eff * @return * @throws WriteException */ private WritableCellFormat getCellFormat(ExcelFontFormat eff) throws WriteException { WritableCellFormat wcf = null; if (mappedFormat == null) { mappedFormat = new HashMap<ExcelFontFormat, WritableCellFormat>(); } else { wcf = mappedFormat.get(eff); } if (wcf == null) { FontName fn = eff.convertFontName(); WritableFont wf = new WritableFont(fn, eff.convertFontSize(), eff.isBold() ? WritableFont.BOLD : WritableFont.NO_BOLD, eff.isItalic(), UnderlineStyle.NO_UNDERLINE, eff.getColor()); wcf = new WritableCellFormat(wf); wcf.setBackground(eff.getBackgroundColor()); wcf.setAlignment(eff.convertFlow()); wcf.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK); if (eff.getVerticalAlign() == 0) wcf.setVerticalAlignment(VerticalAlignment.TOP); else if (eff.getVerticalAlign() == 1) wcf.setVerticalAlignment(VerticalAlignment.CENTRE); else if (eff.getVerticalAlign() == 2) wcf.setVerticalAlignment(VerticalAlignment.BOTTOM); mappedFormat.put(eff, wcf); } return wcf; } private void writeTreeBody() throws RowsExceededException, WriteException, SecurityException, IllegalArgumentException, NoSuchMethodException, IllegalAccessException, InvocationTargetException { if (rowDatas != null && rowDatas.size() > 0) { curDataRowIndex = 1; Object fo = rowDatas.get(0); boolean isMap = fo instanceof Map; if (isMap) { writeTreeDatas4Map(); } else { writeTreeDatas4Dto(); } } } private void writeTreeDatas4Map() throws JxlWriteException, WriteException { if (writed == null) { writed = new HashSet<Object>(); } for (Object data : rowDatas) { if (!writed.contains(data)) { Map m = (Map) data; Object pid = m.get(pidName); if (pid == null) { writeRow4Map(data); curDataRowIndex++; curExcelRowIndex++; writed.add(data); writeSubDatas4Map(m, 1); } else { if (pid instanceof String) { String ps = (String) pid; if (StringUtil.isBlank(ps) || (Integer.valueOf(ps) <= 0)) { writeRow4Map(data); curDataRowIndex++; curExcelRowIndex++; writed.add(data); writeSubDatas4Map(m, 1); } } else if (pid instanceof Integer) { Integer pi = (Integer) pid; if (pi <= 0) { writeRow4Map(data); curDataRowIndex++; curExcelRowIndex++; writed.add(data); writeSubDatas4Map(m, 1); } } else if (pid instanceof Long) { Long pl = (Long) pid; if (pl.compareTo(0L) <= 0) { writeRow4Map(data); curDataRowIndex++; curExcelRowIndex++; writed.add(data); writeSubDatas4Map(m, 1); } } else if (pid instanceof BigDecimal) { if (((BigDecimal) pid).compareTo(BigDecimal.ZERO) <= 0) { writeRow4Map(data); curDataRowIndex++; curExcelRowIndex++; writed.add(data); writeSubDatas4Map(m, 1); } } } } } } private void writeSubDatas4Map(Map father, int deep) throws RowsExceededException, WriteException { for (Object data : rowDatas) { if (!writed.contains(data)) { Map m = (Map) data; Object pid = m.get(pidName); Object fid = father.get(idName); if (pid != null) { if (pid instanceof Long) { Long pl = (Long) pid; Long fl = null; try { fl = (Long) fid; } catch (Exception e) { if (fid instanceof BigDecimal) { fl = ((BigDecimal) fid).longValue(); } } if (pl.equals(fl)) { writeSubRow4Map(m, deep); curDataRowIndex++; curExcelRowIndex++; int subDeep = deep + 1; writed.add(data); writeSubDatas4Map(m, subDeep); } } else if (pid instanceof String) { String ps = (String) pid; String fs = null; try { fs = (String) fid; } catch (Exception e) { if (fid instanceof BigDecimal) { fs = ((BigDecimal) fid).toString(); } } if (ps.equals(fs)) { writeSubRow4Map(m, deep); curDataRowIndex++; curExcelRowIndex++; int subDeep = deep + 1; writed.add(data); writeSubDatas4Map(m, subDeep); } } else if (pid instanceof Integer) { Integer pi = (Integer) pid; Integer fi = null; try { fi = (Integer) fid; } catch (Exception e) { if (fid instanceof BigDecimal) { fi = ((BigDecimal) fid).intValue(); } } if (pi.equals(fi)) { writeSubRow4Map(m, deep); curDataRowIndex++; curExcelRowIndex++; int subDeep = deep + 1; writed.add(data); writeSubDatas4Map(m, subDeep); } } } } } } private void writeSubRow4Map(Map subMap, int deep) throws RowsExceededException, WriteException { int tempColIndex = 0; for (ExcelColMode mode : colModes) { Object o = subMap.get(mode.getName()); String content = null; if (o == null) content = ""; else { if (mode.getContentFormatter() != null) content = mode.getContentFormatter().format(o); else content = o.toString(); } if (tempColIndex == 0) { int blankCount = 6 * deep; for (int i = 0; i < blankCount; i++) { content = ONE_BLANK + content; } } writeContent(content, mode, tempColIndex); tempColIndex++; } } private void writeTreeDatas4Dto() throws SecurityException, IllegalArgumentException, NoSuchMethodException, IllegalAccessException, InvocationTargetException, JxlWriteException, WriteException { if (writed == null) { writed = new HashSet<Object>(); } for (Object data : rowDatas) { if (!writed.contains(data)) { Object pid = getValue(data, pidName); if (pid == null) { writeRow4Dto(data); curDataRowIndex++; curExcelRowIndex++; writed.add(data); writeSubDatas4Dto(data, 1); } else { if (pid instanceof String) { String ps = (String) pid; if (StringUtil.isBlank(ps) || (Integer.valueOf(ps)) <= 0) { writeRow4Dto(data); curDataRowIndex++; curExcelRowIndex++; writed.add(data); writeSubDatas4Dto(data, 1); } } else if (pid instanceof Integer) { Integer pi = (Integer) pid; if (pi <= 0) { writeRow4Dto(data); curDataRowIndex++; curExcelRowIndex++; writed.add(data); writeSubDatas4Dto(data, 1); } } else if (pid instanceof Long) { Long pl = (Long) pid; if (pl.compareTo(0L) <= 0) { writeRow4Dto(data); curDataRowIndex++; curExcelRowIndex++; writed.add(data); writeSubDatas4Dto(data, 1); } } } } } } private void writeSubDatas4Dto(Object father, int deep) throws SecurityException, IllegalArgumentException, NoSuchMethodException, IllegalAccessException, InvocationTargetException, RowsExceededException, WriteException { for (Object data : rowDatas) { if (!writed.contains(data)) { Object pid = getValue(data, pidName); Object fid = getValue(father, idName); if (pid != null) { if (pid instanceof Long) { Long pl = (Long) pid; Long fl = (Long) fid; if (pl.equals(fl)) { writeSubRow4Dto(data, deep); curDataRowIndex++; curExcelRowIndex++; int subDeep = deep + 1; writed.add(data); writeSubDatas4Dto(data, subDeep); } } else if (pid instanceof String) { String ps = (String) pid; String fs = (String) fid; if (ps.equals(fs)) { writeSubRow4Dto(data, deep); curDataRowIndex++; curExcelRowIndex++; int subDeep = deep + 1; writed.add(data); writeSubDatas4Dto(data, subDeep); } } else if (pid instanceof Integer) { Integer pi = (Integer) pid; Integer fi = (Integer) fid; if (pi.equals(fi)) { writeSubRow4Dto(data, deep); curDataRowIndex++; curExcelRowIndex++; int subDeep = deep + 1; writed.add(data); writeSubDatas4Dto(data, subDeep); } } } } } } private void writeSubRow4Dto(Object subData, int deep) throws SecurityException, IllegalArgumentException, NoSuchMethodException, IllegalAccessException, InvocationTargetException, RowsExceededException,WriteException { int tempColIndex = 0; for (ExcelColMode mode : colModes) { String field = mode.getName(); Object o = getValue(subData, field); String content = null; if (o == null) content = ""; else { if (mode.getContentFormatter() != null) content = mode.getContentFormatter().format(o); else content = o.toString(); } if (tempColIndex == 0) { int blankCount = 6 * deep; for (int i = 0; i < blankCount; i++) { content = ONE_BLANK + content; } } writeContent(content, mode, tempColIndex); } } private void writeBody() throws RowsExceededException, WriteException, SecurityException, IllegalArgumentException, NoSuchMethodException, IllegalAccessException, InvocationTargetException { if (rowDatas != null && rowDatas.size() > 0) { curDataRowIndex = 1; Object fo = rowDatas.get(0); if (fo instanceof Map) { writeDatas4Map(); } else { writeDatas4Dto(); } } } private void writeDatas4Map() throws JxlWriteException, WriteException { for (Object data : rowDatas) { writeRow4Map(data); curDataRowIndex++; curExcelRowIndex++; } } private void writeDatas4Dto() throws JxlWriteException, WriteException, SecurityException, IllegalArgumentException, NoSuchMethodException, IllegalAccessException, InvocationTargetException { for (Object data : rowDatas) { writeRow4Dto(data); curDataRowIndex++; curExcelRowIndex++; } } private void writeRow4Map(Object data) throws JxlWriteException, WriteException { Map m = (Map) data; int tempColIndex = 0; for (ExcelColMode mode : colModes) { Object o = m.get(mode.getName()); String content = null; if (o == null) content = ""; else { if (mode.getContentFormatter() != null) content = mode.getContentFormatter().format(o); else content = o.toString(); } writeContent(content, mode, tempColIndex); tempColIndex++; } } private void writeContent(String content, ExcelColMode mode, int colIndex) throws RowsExceededException, WriteException { ExcelFontFormat eff = mode.getFontFormat(); if (distinguishable == 1) { if (eff == null) { eff = new ExcelFontFormat(); } if (curDataRowIndex % 2 == 1) { eff.setBackgroundColor(Colour.WHITE); } else { eff.setBackgroundColor(Colour.GRAY_25); } } else if (distinguishable == 2) { if (eff == null) { eff = new ExcelFontFormat(); } if (colIndex % 2 == 1) { eff.setBackgroundColor(Colour.WHITE); } else { eff.setBackgroundColor(Colour.GRAY_25); } } writeCell(content, eff, colIndex, 1); } private void writeRow4Dto(Object data) throws JxlWriteException, WriteException, SecurityException, IllegalArgumentException, NoSuchMethodException, IllegalAccessException, InvocationTargetException { int tempColIndex = 0; for (ExcelColMode mode : colModes) { String field = mode.getName(); Object o = getValue(data, field); String content = null; if (o == null) content = ""; else { if (mode.getContentFormatter() != null) content = mode.getContentFormatter().format(o); else content = o.toString(); } writeContent(content, mode, tempColIndex); tempColIndex++; } } private Object getValue(Object data, String feild) throws SecurityException, NoSuchMethodException, IllegalArgumentException, IllegalAccessException, InvocationTargetException { if (feild.contains(".")) { String fileds[] = feild.split("\\."); Object value = null; String methodName = "get" + fileds[0].substring(0, 1).toUpperCase() + fileds[0].substring(1); try { Method getMethod = data.getClass().getMethod(methodName); value = getMethod.invoke(data); } catch (NoSuchMethodException e) { // e.printStackTrace(); try { methodName = "get" + feild.substring(0, 1).toLowerCase() + feild.substring(1); Method getMethod = data.getClass().getMethod(methodName); value = getMethod.invoke(data); } catch (Exception e2) { e.printStackTrace(); } } Object value2 = null; String methodName2 = "get" + fileds[1].substring(0, 1).toUpperCase() + fileds[1].substring(1); try { Method getMethod = value.getClass().getMethod(methodName2); value2 = getMethod.invoke(value); } catch (NoSuchMethodException e) { // e.printStackTrace(); try { methodName = "get" + fileds[1].substring(0, 1).toLowerCase() + fileds[1].substring(1); Method getMethod = data.getClass().getMethod(methodName); value = getMethod.invoke(value); } catch (Exception e2) { e.printStackTrace(); } } return value2; } else { Object value = null; String methodName = "get" + feild.substring(0, 1).toUpperCase() + feild.substring(1); try { Method getMethod = data.getClass().getMethod(methodName); value = getMethod.invoke(data); } catch (NoSuchMethodException e) { // e.printStackTrace(); try { methodName = "get" + feild.substring(0, 1).toLowerCase() + feild.substring(1); Method getMethod = data.getClass().getMethod(methodName); value = getMethod.invoke(data); } catch (Exception e2) { e.printStackTrace(); } } return value; } } /** * 释放资源 */ public void clear() { if (writed != null) { writed = null; } if (rowDatas != null) { rowDatas = null; } if (colModes != null) { colModes = null; } if (headCols != null) { headCols = null; } if (sheet != null) { sheet = null; } if (mappedFormat != null) { mappedFormat = null; } if (idName != null) { idName = null; } if (pidName != null) { pidName = null; } } private static boolean isContainStyle(String style, String s1, String s2) { String styleArr[] = style.split(";"); for (String s : styleArr) { if (s.contains(s1) && s.contains(s2)) return true; } return false; } private static void writeSheetData(Integer startRow[], int level, Map<String, String> map, List<String> ridAry, List<Map<String, String>> allData, WritableSheet sheet) throws RowsExceededException, WriteException { String tab = ""; for (int i = 0; i < level - 1; i++) { tab += " "; } sheet.addCell(new Label(0, startRow[0], tab + map.get("index"))); for (int i = 0; i < ridAry.size(); i++) { sheet.addCell(new Label(i + 1, startRow[0], map.get("R" + ridAry.get(i)))); } startRow[0]++; List<Map<String, String>> children = getChildren(allData, map.get("id")); if (children != null && children.size() > 0) { for (Map<String, String> child : children) { writeSheetData(startRow, level + 1, child, ridAry, allData, sheet); } } } private static List<Map<String, String>> getFirstLeve(List<Map<String, String>> list) { List<Map<String, String>> firstLevel = new ArrayList<Map<String, String>>(); if (list != null && list.size() > 0) { for (Map<String, String> map : list) { if (map.get("parentId") == null || map.get("parentId").length() == 0) firstLevel.add(map); } } return firstLevel; } private static List<Map<String, String>> getChildren(List<Map<String, String>> list, String id) { List<Map<String, String>> children = new ArrayList<Map<String, String>>(); if (list != null && list.size() > 0) { for (Map<String, String> map : list) { if (map.get("parentId") != null && map.get("parentId").toString().equals(id)) children.add(map); } } return children; } private static String getIndexData(String code, String rid, Map<String, List<HashMap<String, Object>>> tabDataMap) { if (code == null || code.length() == 0) return ""; if (tabDataMap == null || tabDataMap.size() == 0) return "0"; String codeArr[] = code.split("\\."); if (codeArr.length != 2) return ""; List<HashMap<String, Object>> listMap = tabDataMap.get(codeArr[0]); if (listMap == null || listMap.size() == 0) return "0"; else { for (int i = 0; i < listMap.size(); i++) { HashMap<String, Object> map = listMap.get(i); if (map.get("P_R_ID").toString().equals(rid)) { Object obj = map.get(codeArr[1]); if (obj != null) return obj.toString(); else return "0"; } } return ""; } } private static String getUnit(String code, Map<String, String> tabUnitMap) { String result = ""; if (code != null) { String codeArr[] = code.split("\\."); if (codeArr.length == 2) { if (tabUnitMap.get(codeArr[0]) != null) result = tabUnitMap.get(codeArr[0]); } } return result; }}
- 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
955
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
