导出Excel-demo

原创
2018/08/29 09:52
阅读数 225
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.fastjson.JSON;
import com.google.common.collect.Lists;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.File;
import java.io.FileOutputStream;
import java.util.Iterator;
import java.util.List;
public static void recordToHSSFWorkbook(HSSFWorkbook wb , int clos , int rows , List<Record> records) {
        HSSFSheet sheet = wb.createSheet("导出报表");
        //创建空EXCEL
        int _i = 0;
        for (int i = 0; i < rows; ++i) {
            HSSFRow row = sheet.createRow(i);
            for (int j = 0; j < clos; j++) {
                if (_i != i) { //换行,重新设置行高
                    row.setHeight((short) 500);
                    _i = i;
                }
                row.createCell(j);
            }
        }
        List<SpanNode> nodes = Lists.newArrayList();
        Integer[] widthArr = new Integer[clos];
        List<MergedNode> mergedNodes = Lists.newArrayList();
        HSSFCellStyle style = wb.createCellStyle();
        style.setVerticalAlignment((short) 1);
        style.setAlignment((short) 2);
        Iterator iterator = records.iterator();
        while (iterator.hasNext()) {
            Record rd = (Record) iterator.next();
            //System.err.printf("d:%s\n" , JSON.toJSONString(rd.toJson()));
            int _x = rd.getInt("model_attach_x");
            int _y = rd.getInt("model_attach_y");
            if (_x != 0 && _y != 0) { //去除第一行第一列
                --_x;
                --_y;
                Integer rowSpan = rd.getInt("model_attach_rowspan");
                Integer colspan = rd.getInt("model_attach_colspan");
                Object value = rd.get("value");
                int new_x = _x;
                int span = SpanNode.getSpan(nodes , _y , _x);
                new_x = new_x+span;
                //处理跨行,跨列
                SpanNode.putNewNodes(nodes , _y , _x , rowSpan , colspan);
                //记录合并单元格信息
                MergedNode.putNewNode(mergedNodes , _y , new_x , rowSpan , colspan , value != null ? value.toString() : "");
                //将值赋予表格
                HSSFRow row = sheet.getRow(_y);
                HSSFCell cell = row.getCell(new_x);
                cell.setCellStyle(style);
                if (value != null) {
                    String _v = value.toString();
                    //System.err.printf("[%d行,%d原列,%d新列,%s]\n" , _y , _x,new_x , _v);
                    if (NumberUtils.isNumber(_v)) {
                        cell.setCellValue(Double.parseDouble(_v));
                        //cell.setCellValue("["+_y+","+new_x+","+_v+"]");
                    } else {
                        cell.setCellValue(_v);
                        //cell.setCellValue("["+_y+","+new_x+","+_v+"]");
                        if (widthArr[new_x] == null) {
                            widthArr[new_x] = (_v.length()+16) * 256;
                        } else {
                            int _w = (_v.length()+16) * 256;
                            if (widthArr[new_x] < _w) {
                                widthArr[new_x] = _w;
                            }
                        }
                    }
                }
            }
        }

        //列宽
        for (int i = 0; i < widthArr.length; ++i) {
            if (widthArr[i] != null) {
                sheet.setColumnWidth(i , widthArr[i]);
            }
        }

        //合并单元格
       // System.err.printf("'meg':'%s'\n 'span':'%s'" , JSON.toJSONString(mergedNodes) , JSON.toJSONString(nodes));
        for (MergedNode node : mergedNodes) {
            CellRangeAddress region = new CellRangeAddress(node.startRow , node.endRow , node.startCol , node.endCol);
            sheet.addMergedRegion(region);
        }
    }
}

//合并单元格
class MergedNode {
    int startRow;
    int endRow;
    int startCol;
    int endCol;
    String v;

    MergedNode() {
    }

    static void putNewNode(List<MergedNode> mergedNodes , int new_y , int new_x , Integer rowSpan , Integer colSpan , String _v) {
        if (rowSpan == null) {
            rowSpan = 0;
        }

        if (colSpan == null) {
            colSpan = 0;
        }

        if (rowSpan != 0 || colSpan != 0) {
            MergedNode node = new MergedNode();
            node.startRow = new_y;
            if (rowSpan > 0) {
                rowSpan--;
            }
            node.endRow = new_y+rowSpan;
            node.startCol = new_x;
            if (colSpan > 0) {
                colSpan--;
            }
            node.endCol = new_x+colSpan;
            node.v = _v;
            mergedNodes.add(node);
        }
    }
}

//格点数据新坐标
class SpanNode {
    int startColIndex = 0;
    int startRowIndex = 0;
    int endRowIndex = 0;
    int span = 0;

    static int getSpan(List<SpanNode> nodes , int _y , int _new_x) {
        int _span = 0;
        for (SpanNode node : nodes) {
            if (node.startColIndex <= _new_x) {
                if (node.startRowIndex <= _y && _y <= node.endRowIndex) {
                    _span = _span+node.span;
                }
            }
        }
        return _span;
    }
    //跨行(or +跨列)
    static void putNewNodes(List<SpanNode> nodes , int _y , int _new_x , Integer _rowSapn , Integer _colSpan) {
        SpanNode newNode1 = null;
        if (null != _rowSapn && _rowSapn > 0) {
            newNode1 = new SpanNode();
            newNode1.startColIndex = _new_x;
            newNode1.startRowIndex = (_y+1);//从下一行开始
            newNode1.endRowIndex = (_y+_rowSapn-1);
            newNode1.span = 1;
            if (_colSpan != null && _colSpan > 1) {
                newNode1.span = (_colSpan-1);
            }
            nodes.add(newNode1);
        }
        if (_colSpan != null && _colSpan > 0) { //跨行又跨列 or 跨列
            SpanNode newNode2 = new SpanNode();
            newNode2.startColIndex = _new_x;
            newNode2.startRowIndex = _y;
            newNode2.endRowIndex = _y;
            newNode2.span = (_colSpan-1);
            nodes.add(newNode2);
        }
    }
}
展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部