分享一下我的偶像大神的人工智能教程!http://blog.csdn.net/jiangjunshow
也欢迎转载我的文章,转载请注明出处 https://blog.csdn.net/aabbyyz
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
- 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
- 204
- 205
- 206
- 207
- 208
- 209
- 210
- 211
- 212
- 213
- 214
- 215
- 216
- 217
- 218
- 219
- 220
- 221
- 222
- 223
- 224
- 225
- 226
- 227
- 228
- 229
- 230
- 231
- 232
- 233
- 234
- 235
- 236
- 237
- 238
- 239
- 240
- 241
- 242
- 243
- 244
- 245
- 246
- 247
- 248
- 249
- 250
- 251
- 252
- 253
- 254
- 255
- 256
- 257
- 258
- 259
- 260
- 261
- 262
- 263
- 264
- 265
- 266
- 267
- 268
- 269
- 270
- 271
- 272
- 273
- 274
- 275
- 276
- 277
- 278
- 279
- 280
- 281
- 282
- 283
- 284
- 285
- 286
- 287
- 288
- 289
- 290
- 291
- 292
- 293
- 294
- 295
- 296
- 297
- 298
- 299
- 300
- 301
- 302
- 303
- 304
- 305
- 306
- 307
- 308
- 309
- 310
- 311
- 312
- 313
- 314
- 315
- 316
- 317
- 318
- 319
- 320
- 321
- 322
- 323
- 324
- 325
- 326
- 327
- 328
- 329
- 330
- 331
- 332
- 333
- 334
- 335
- 336
- 337
- 338
- 339
- 340
- 341
- 342
- 343
- 344
- 345
- 346
- 347
- 348
- 349
- 350
- 351
- 352
- 353
- 354
- 355
- 356
- 357
- 358
- 359
- 360
- 361
- 362
- 363
- 364
- 365
- 366
- 367
- 368
- 369
- 370
- 371
- 372
- 373
- 374
- 375
- 376
- 377
- 378
- 379
- 380
- 381
- 382
- 383
- 384
- 385
- 386
- 387
- 388
- 389
- 390
- 391
- 392
- 393
- 394
- 395
- 396
- 397
- 398
- 399
- 400
- 401
- 402
- 403
- 404
- 405
- 406
- 407
- 408
- 409
- 410
- 411
- 412
- 413
- 414
- 415
- 416
- 417
- 418
- 419
- 420
- 421
- 422
- 423
- 424
- 425
- 426
- 427
- 428
- 429
- 430
- 431
- 432
- 433
- 434
- 435
- 436
- 437
- 438
- 439
- 440
- 441
- 442
- 443
- 444
- 445
- 446
- 447
- 448
- 449
- 450
- 451
- 452
- 453
- 454
- 455
- 456
- 457
- 458
- 459
- 460
- 461
- 462
- 463
- 464
- 465
- 466
- 467
- 468
- 469
- 470
- 471
- 472
- 473
- 474
- 475
- 476
- 477
- 478
- 479
- 480
- 481
- 482
- 483
- 484
- 485
- 486
- 487
- 488
- 489
- 490
- 491
- 492
- 493
- 494
- 495
- 496
- 497
- 498
- 499
- 500
- 501
- 502
- 503
- 504
- 505
- 506
- 507
- 508
- 509
- 510
- 511
- 512
- 513
- 514
- 515
- 516
- 517
- 518
- 519
- 520
- 521
- 522
- 523
- 524
- 525
- 526
- 527
- 528
- 529
- 530
- 531
- 532
- 533
- 534
- 535
- 536
- 537
- 538
- 539
- 540
- 541
- 542
- 543
- 544
- 545
- 546
- 547
- 548
- 549
- 550
- 551
- 552
- 553
- 554
- 555
- 556
- 557
- 558
- 559
- 560
- 561
- 562
- 563
- 564
- 565
- 566
- 567
- 568
- 569
- 570
- 571
- 572
- 573
- 574
- 575
- 576
- 577
- 578
- 579
- 580
- 581
- 582
- 583
- 584
- 585
- 586
- 587
- 588
- 589
- 590
- 591
- 592
- 593
- 594
- 595
- 596
- 597
- 598
- 599
- 600
- 601
- 602
- 603
- 604
- 605
- 606
- 607
- 608
- 609
- 610
- 611
- 612
- 613
- 614
- 615
- 616
- 617
- 618
- 619
- 620
- 621
- 622
- 623
- 624
- 625
- 626
- 627
- 628
- 629
- 630
- 631
- 632
- 633
- 634
- 635
- 636
- 637
- 638
- 639
- 640
- 641
- 642
- 643
- 644
- 645
- 646
- 647
- 648
- 649
- 650
- 651
- 652
- 653
- 654
- 655
- 656
- 657
- 658
- 659
- 660
- 661
- 662
- 663
- 664
- 665
- 666
- 667
- 668
- 669
- 670
- 671
- 672
- 673
- 674
- 675
- 676
- 677
- 678
- 679
- 680
- 681
- 682
- 683
- 684
- 685
- 686
- 687
- 688
- 689
- 690
- 691
- 692
- 693
- 694
- 695
- 696
- 697
- 698
- 699
- 700
- 701
- 702
- 703
- 704
- 705
- 706
- 707
- 708
- 709
- 710
- 711
- 712
- 713
- 714
- 715
- 716
- 717
- 718
- 719
- 720
- 721
- 722
- 723
- 724
- 725
- 726
- 727
- 728
- 729
- 730
- 731
- 732
- 733
- 734
- 735
- 736
- 737
- 738
- 739
- 740
- 741
- 742
- 743
- 744
- 745
- 746
- 747
- 748
- 749
- 750
- 751
- 752
- 753
- 754
- 755
- 756
- 757
- 758
- 759
- 760
- 761
- 762
- 763
- 764
- 765
- 766
- 767
- 768
- 769
- 770
- 771
- 772
- 773
- 774
- 775
- 776
- 777
- 778
- 779
- 780
- 781
- 782
- 783
接下来是测试类。
public class TestExcelUtil {
@Test
public void test() throws Exception {
ExcelHelper excelHelper = new ExcelHelper();
List<Object> rowDatas = new ArrayList<Object>();
Map<String, String> map = new HashMap<String, String>();
map.put("userName", "张三");
map.put("sex", "男");
rowDatas.add(map);
Map<String, String> map1 = new HashMap<String, String>();
map1.put("userName", "李四");
map1.put("sex", "男");
rowDatas.add(map1);
List<ExcelHeadCell> headCells = new ArrayList<ExcelHeadCell>();
headCells.add(new ExcelHeadCell("姓名"));
headCells.add(new ExcelHeadCell("性别"));
List<List<ExcelHeadCell>> headCellsList = new ArrayList<List<ExcelHeadCell>>();
headCellsList.add(headCells);
ExcelExportRule rule = new ExcelExportRule();
rule.setSheetName("测试");
rule.setHeadCols(headCellsList);
List<ExcelColMode> colModes = new ArrayList<ExcelColMode>();
colModes.add(new ExcelColMode("userName"));
colModes.add(new ExcelColMode("sex"));
rule.setColModes(colModes);
InputStream inputStream = excelHelper.writeExcel(rowDatas, rule);
File file = new File("d:/test.xls");
if (file.exists())
file.delete();
file.createNewFile();
FileOutputStream fileOutputStream = new FileOutputStream(file);
byte b[] = new byte[512];
int i = inputStream.read(b);
while (i != -1) {
fileOutputStream.write(b);
i = inputStream.read(b);
}
fileOutputStream.flush();
fileOutputStream.close();
inputStream.close();
}
}
- 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
通过看测试类应该就知道使用方法了吧,最后再给一个实际的从页面到后台的例子:从页面发来的一个请求,包含了表格的标题、内容、sheet名、Excel名,然后实现生成并下载Excel(在页面会弹出选择保存路径框)的过程。
首先是页面:
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta http-equiv="X-UA-Compatible" content="IE=EDGE" />
<title>导出Excel</title>
<script src="../../../ns-face-sys/common/lib/jquery-1.11.2.min.js"></script>
<script src="../../../ns-face-sys/common/js/newsee.js"></script>
<script src="../../../ns-face-sys/common/js/newsee.ui.js"></script>
</head>
<body>
<form id="formid" name= "myform" method = "post" action = "/ns-face-sys/rest/system/export/excel">
<input id="headCells" name="headCells" value="" type="hidden"/>
<input id="rowDatas" name="rowDatas" value="" type="hidden"/>
<input id="sheetName" name="sheetName" value="人员信息" type="hidden"/>
<input id="excelName" name="excelName" value="人员信息表" type="hidden"/>
<table id="table">
<tr>
<td>序号</td>
<td>姓名</td>
<td>备注</td>
</tr>
<tr>
<td>1</td>
<td>yjc</td>
<td>欣lp</td>
</tr>
<tr>
<td>2</td>
<td>lsx</td>
<td>诚lg</td>
</tr>
<tr>
<td>3</td>
<td>测试1</td>
<td>备注1</td>
</tr>
<tr>
<td>4</td>
<td>测试2</td>
<td>备注2</td>
</tr>
</table>
<input id="export" type="button" value="导出Excel" onclick="exportExcel()"/>
</form>
<script type="text/javascript" src="export_excel.js"></script>
</body>
</html>
- 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
然后是js,里面做了把表格数据整合成后台需要的数据(每列数据用”,”隔开,每行数据用”;”隔开,标题和内容分别放在隐藏的input里传给后台解析),放到隐藏域里,然后以提交表单的形式发送到后台。
千万注意:不要使用ajax请求,虽然也能发到后台,但会导致浏览器收不到response,导致弹不出选择保存路径的弹出框了!!!
function exportExcel(){
document.charset='utf-8';
var head = getHead();
var data = getData();
// var path = getPath();
$("#headCells").val(head);
$("#rowDatas").val(data);
$("#formid").submit();
// var postData = [{
// Request: {
// Data: {
// headCells : head,
// rowDatas : data,
// sheetName : "人员信息",
// path : path,
// excelName : "人员信息表"
// }
// }
// }]
// $.ajax({
// url: "/ns-face-sys/rest/system/export/excel",
// type: "post",
// dataType: 'json',
// async: true,
// data: {
// request: newsee.base.JsonArrayToStringCfz(postData)
// }
// });
function getHead(){
var head = "";
var uls = $("#table").find("tr");
var lis = $(uls[0]).children("td");
for(var j=0;j<lis.length;j++){
if(j == lis.length-1){
head += $(lis[j]).text();
}else{
head += $(lis[j]).text() + ",";
}
}
return head;
}
function getData(){
var data = "";
var uls = $("#table").find("tr");
for(var i=1;i<uls.length;i++){
var lis = $(uls[i]).children("td");
for(var j=0;j<lis.length;j++){
if(j == lis.length-1 && i == uls.length-1){
data += $(lis[j]).text();
}else if(j == lis.length-1 && i != uls.length-1){
data += $(lis[j]).text() + ";";
}else{
data += $(lis[j]).text() + ",";
}
}
}
return data;
}
function getPath(){
return "D:";
}
- 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
然后时控制器层代码,里面为了解决页面过来的数据有中文乱码,采用了笨方法,一个一个指定解码方式
import javax.servlet.http.HttpServletResponse;
import javax.ws.rs.FormParam;
import javax.ws.rs.POST;
import javax.ws.rs.Path;
import javax.ws.rs.core.Context;
import org.springframework.stereotype.Controller;
import com.newsee.face.common.ExportExcel;
@Path("system")
@Controller
public class SystemExcelFace {
/**
* 导出excel入口
* @param request
* @param response
* @throws Exception
*/
@POST
@Path("/export/excel")
public void showImg(@FormParam("headCells") String headCells,
@FormParam("rowDatas") String rowDatas,
@FormParam("sheetName") String sheetName,
@FormParam("excelName") String excelName,
@Context HttpServletResponse response) throws Exception {
headCells=new String(headCells.getBytes("iso-8859-1"),"UTF-8");
rowDatas=new String(rowDatas.getBytes("iso-8859-1"),"UTF-8");
sheetName=new String(sheetName.getBytes("iso-8859-1"),"UTF-8");
excelName=new String(excelName.getBytes("iso-8859-1"),"UTF-8");
new ExportExcel().exportExcel(rowDatas, headCells, sheetName, excelName,response);
}
}
- 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
然后就是业务逻辑层了ExportExcel,这里就调用了之前介绍的导出Excel处理类ExcelHelper。
import java.io.BufferedOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
import com.newsee.dto.common.ExcelColMode;
import com.newsee.dto.common.ExcelExportRule;
import com.newsee.dto.common.ExcelHeadCell;
import com.newsee.util.ExcelHelper;
import com.newsee.util.StringUtil;
/**
* 导出excel
*
* @author yaojiacheng
*
*/
public class ExportExcel {
/**
* 导出excel
*
* @param requestContent
* @return
*/
public void exportExcel(String rowData,String headCell,String sheetName,String excelName, HttpServletResponse response) throws Exception {
if (StringUtil.isAnyBlank(rowData, headCell)) {
return;
}
if(response == null){
return;
}
ExcelHelper excelHelper = new ExcelHelper();
List<ExcelHeadCell> headCells = new ArrayList<ExcelHeadCell>();
String[] headCellsArray = headCell.split(",");
for (String hc : headCellsArray) {
headCells.add(new ExcelHeadCell(hc));
}
List<List<ExcelHeadCell>> headCellsList = new ArrayList<List<ExcelHeadCell>>();
headCellsList.add(headCells);
ExcelExportRule rule = new ExcelExportRule();
if (StringUtil.isBlank(sheetName)) {
rule.setSheetName("测试");
} else {
rule.setSheetName(sheetName);
}
rule.setHeadCols(headCellsList);
List<ExcelColMode> colModes = new ArrayList<ExcelColMode>();
for (int i = 0; i < headCellsArray.length; i++) {
colModes.add(new ExcelColMode(i + ""));
}
rule.setColModes(colModes);
List<Object> rowDatas = new ArrayList<Object>();
String[] rowDataArrays = rowData.split(";");
for (String rowDataArray : rowDataArrays) {
Map<String, String> map = new HashMap<String, String>();
String[] rowDataAs = rowDataArray.split(",");
for (int i = 0; i < rowDataAs.length; i++) {
map.put(i + "", rowDataAs[i]);
}
rowDatas.add(map);
}
InputStream fis = excelHelper.writeExcel(rowDatas, rule);
byte b[] = new byte[512];
// 清空response
response.reset();
// 设置response的Header
response.addHeader("Content-Disposition", "attachment;filename=" +
URLEncoder.encode(excelName, "UTF-8")+ ".xls");
response.addHeader("Content-Length", "" + fis.available());
OutputStream toClient = new BufferedOutputStream(
response.getOutputStream());
response.setContentType("application/vnd.ms-excel;charset=gb2312");
int i = fis.read(b);
while (i != -1) {
toClient.write(b);
i = fis.read(b);
}
toClient.flush();
toClient.close();
fis.close();
}
}
- 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
如上,使用response.getOutputStream()得到的输出流来write数据后会先放在缓存中,到执行toClient.flush()就会在页面弹出选择保存路径的选择框,点击保存后就下载成功了。这里没有Excel导出的过程,直接将经过Excel处理类得到的InputStream拿过来读取,然后写进OutputStream下载,通常这是最优的方式。
以上代码没有添加合并单元格的功能,需要合并单元格的同学们,可以自行修改代码,合并调用的方法就是:WritableSheet.mergeCells(int m,int n,int p,int q); 作用是从(m,n)到(p,q)的单元格全部合并,比如:
//合并第一列第二行到第六列第二行的所有单元格
sheet.mergeCells(0,1,5,1); //注意了m和p指的是列,n和q指的是行
可以在workbook.write();workbook.close();之前调用即可!
还需要注意的是,合并的单元格显示的内容是第一个单元格的内容,不是所有单元格的内容追加!
![这里写图片描述](https://i-blog.csdnimg.cn/blog_migrate/2e19ffb3303a490d20ea5d00dd397156.gif)
给我偶像的人工智能教程打call!http://blog.csdn.net/jiangjunshow