JAVA导出EXCEL实现

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

这里写图片描述

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

  • 0
    点赞
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值