JAVA导出EXCEL实现_excelcolmodelformatterinter 类

作者：小小林熬夜学编程 | 2024-06-16 09:37:06

踩

excelcolmodelformatterinter 类

分享一下我的偶像大神的人工智能教程！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
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
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
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
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
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
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
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
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
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
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();之前调用即可！
还需要注意的是，合并的单元格显示的内容是第一个单元格的内容，不是所有单元格的内容追加！

给我偶像的人工智能教程打call！http://blog.csdn.net/jiangjunshow

声明：本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：【wpsshop博客】