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
    
      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
     
       nodes = Lists.newArrayList();        Integer[] widthArr = new Integer[clos];        List
      
        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
       
         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
        
          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
         
           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); } }}