fj-dc/.svn/pristine/1b/1bd04a385e7f220e63591e2f9e507af692ee7521.svn-base

package cn.com.goldenwater.dcproj.utils.expExcel;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.tools.zip.ZipEntry;
import org.apache.tools.zip.ZipOutputStream;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.zip.Deflater;

/**
 * Created by lhc on 2019-3-18.
 */
public class ExcelExport extends ExportAbstract {
    public OutputStream out;

    private HSSFWorkbook wb = null;

    private  HSSFCellStyle style = null;

    HSSFSheet downloadSheet;
    HSSFCellStyle headStyle;
    HSSFCellStyle dataStyle;
    HSSFCellStyle rowStyle;

    HSSFDataFormat dataFormat;

    HSSFFont headFont;
    HSSFFont dataFont;
    HSSFFont rowFont;
    HSSFRow row = null;
    HSSFCell cell = null;

    int startsheet = -1;
    int maxline = 50000;  //一个Sheet最大数据行数 0000

    public int rowcount = 0;

    private ZipOutputStream zipOut = null;

    private String headType = "meta";


    @Override
    public void Export(HttpServletResponse response) throws Exception {
        response.reset();
        String _fileName = fileName + (export_bzip ? ".zip" : ".xls");
        response.setContentType("application/octet-stream;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment; filename="
                + URLEncoder.encode(_fileName, "UTF-8"));

    }

    @Override
    public HttpServletResponse Export_new(HttpServletResponse response) throws Exception {
        response.reset();
        String _fileName = fileName + (export_bzip ? ".zip" : ".xls");
        response.setContentType("application/octet-stream;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename="
                + URLEncoder.encode(_fileName, "UTF-8"));
        return response;
    }


    @Override
    public void ExportHeadForCustom(HttpServletResponse response) throws Exception {
        headType = "meta";

        out = response.getOutputStream();

        if (this.export_bzip) {
            zipOut = new ZipOutputStream(out);
            zipOut.setEncoding("GBK");
            zipOut.setMethod(ZipOutputStream.DEFLATED);
            zipOut.setLevel(Deflater.BEST_COMPRESSION);
            ZipEntry entry = new ZipEntry(this.fileName + "_" + (startsheet + 2) + ".xls");
            zipOut.putNextEntry(entry);
        }

        cE();
        toHeadRowForCustom();
    }

    @Override
    public void EndExport() throws Exception {


        if (this.export_bzip) {
            wb.write(zipOut);
            zipOut.flush();
            zipOut.finish();
            zipOut = null;
        } else {
            wb.write(out);
        }
        out.flush();
    }

    @Override
    public HSSFWorkbook getWb() {
        return wb;
    }


    @Override
    public void ContinueExport(List list) throws Exception {
        java.util.Iterator it = list.iterator();
        while (it.hasNext()) {
            if (rowcount % maxline == 0) {
                if (startsheet != -1) {
                    if (this.export_bzip) {
                        wb.write(zipOut);
                        zipOut.flush();
                        out.flush();
                        ZipEntry entry = new ZipEntry(this.fileName + "_" + (startsheet + 2) + ".xls");
                        zipOut.putNextEntry(entry);
                        cE();
                        wb.setSheetName(0, this.fileName + "_" + (startsheet + 2));
                    } else {
                        downloadSheet = wb.createSheet();
                        wb.setSheetName(startsheet + 1, this.fileName + "_" + (startsheet + 2));
                    }

                    toHeadRow(list);
                }
                startsheet++;
            }
            row = downloadSheet.createRow((rowcount % maxline + this.level + 1));
            toTextRow((Map) it.next());
            rowcount++;
        }
    }

    public void toTextRow(Map hm) {
        cell = row.createCell(0);
        cell.setCellValue(rowcount + 1);
        int _count = 0;

        for (int i = 0; i < this.cols.size(); i++) {
            /**
             * 这里应该有问题
             */
            String key = "";
            String v = null;

            key = this.cols.get(i).toString();
            key = key.substring(0, key.indexOf("["));
            if (hm.get(key) != null) {
                v = hm.get(key).toString();
            } else {
                v = "";
            }
            cell = row.createCell((i + 1));

            Boolean isNum = false;//data是否为数值型
            Boolean isInteger = false;//data是否为整数
            Boolean isPercent = false;//data是否为百分数
            if (v != null || "".equals(v)) {
                //判断data是否为数值型
                isNum = v.toString().matches("^(-?\\d+)(\\.\\d+)?$");
                //判断data是否为整数（小数部分是否为0）
                isInteger = v.toString().matches("^[-\\+]?[\\d]*$");
                //判断data是否为百分数（是否包含“%”）
                isPercent = v.toString().contains("%");
            }//如果单元格内容是数值类型，涉及到金钱（金额、本、利），则设置cell的类型为数值型，设置data的类型为数值类型
            if (isNum && !isPercent) {
                HSSFDataFormat df = wb.createDataFormat(); // 此处设置数据格式
                if (isInteger) {
                    //style.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,#0"));//数据格式只显示整数(这个可能不好使)
                    style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));//数据格式只显示整数
                    cell.setCellType(CellType.STRING);
                } else {
                    style.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));//保留两位小数点
                }
                // 设置单元格格式
                cell.setCellStyle(style);
                // 设置单元格内容为double类型
                cell.setCellValue(Double.parseDouble(v.toString()));
            } else {
                cell.setCellStyle(style);
                // 设置单元格内容为字符型
                cell.setCellValue(v.toString());
            }
            /**
             * style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
             * cell.setCellType(cell.CELL_TYPE_STRING);
             */
        }
    }

    public void toHeadRow(List data) {
        row = downloadSheet.createRow(0);
        row.setHeightInPoints((short) 30);
        //合并单元格式 合并区域是分别指定起始和终了的列和行，都是从0开始
        int colssize = this.cols.size() + 1;
        cell = row.createCell((0));
        //// 合并单元格(startRow，endRow，startColumn，endColumn)
        downloadSheet.addMergedRegion(new CellRangeAddress(0, 0, 0, colssize - 1));
        HSSFCellStyle style = wb.createCellStyle();
        HSSFFont titleFont = wb.createFont();
        titleFont.setFontHeightInPoints((short) 24);
        // titleFont.setBoldweight((short) 500);
        titleFont.setBold(true);
        style.setFont(titleFont);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setFillForegroundColor((short) 11);
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        cell.setCellValue(this.title);
        cell.setCellType(CellType.STRING);
        cell.setCellStyle(style);


        /**
         * 新行
         */
        row = downloadSheet.createRow(this.level);

        HSSFRow _row = downloadSheet.getRow(1);
        cell = _row.createCell(0);
        //合并单元格(startRow，endRow，startColumn，endColumn)
        downloadSheet.addMergedRegion(new CellRangeAddress(1, this.level, 0, 0));
        cell.setCellValue("序号");
        cell.setCellType(CellType.STRING);
        cell.setCellStyle(headStyle);

        downloadSheet.setColumnWidth(0, 2400);

        for (int i = 0; i < this.cols.size(); i++) {
            String m = cols.get(i) == null ? "" : cols.get(i).toString();
            if (rowspos[i] != 0) {
                HSSFRow _prerow = downloadSheet.getRow(rowspos[i]);
                cell = _prerow.getCell((i + 1));
                if (cell == null) {
                    cell = _prerow.createCell((i + 1));
                }
            } else {
                cell = row.createCell((i + 1));
            }
            cell.setCellValue(m);
            cell.setCellType(CellType.STRING);

            //set width for columns
            //The maximum column width for an individual cell is 255 characters
//			//Throws: 判断设置的宽度是否超过最大值 65280
//			//java.lang.IllegalArgumentException - if width > 65280 (the maximum column width in Excel)
            int columnWidth = (int) ((35.7 + 3) * (MetaUtil.getBestwidth(0, m)));
            downloadSheet.setColumnWidth((i + 1), columnWidth >= 65280 ? 65280 : columnWidth);
            cell.setCellStyle(headStyle);
        }


        //冻结第一行 第一列
        /**
         * 冻结参数......
         */
        downloadSheet.createFreezePane(this.locksize + 1, this.level + 1);
    }


    public void toHeadRowForCustom() {

        row = downloadSheet.createRow(0);
        row.setHeightInPoints((short) 30);
        //合并单元格式 合并区域是分别指定起始和终了的列和行，都是从0开始
        int colssize = this.cols.size() + 1;
        cell = row.createCell((0));
        //// 合并单元格(startRow，endRow，startColumn，endColumn)
        downloadSheet.addMergedRegion(new CellRangeAddress(0, 0, 0, colssize - 1));
        HSSFCellStyle style = wb.createCellStyle();
        HSSFFont titleFont = wb.createFont();
        titleFont.setFontHeightInPoints((short) 24);
        // titleFont.setBoldweight((short) 500);
        titleFont.setBold(true);
        style.setFont(titleFont);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setFillForegroundColor((short) 11);
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cell.setCellValue(this.title);
        cell.setCellType(CellType.STRING);
        cell.setCellStyle(style);

        /**
         * 新行
         */
        row = downloadSheet.createRow(this.level);

        HSSFRow _row = downloadSheet.getRow(1);
        cell = _row.createCell(0);
        //合并单元格(startRow，endRow，startColumn，endColumn)
        cell.setCellValue("序号");
        cell.setCellType(CellType.STRING);
        cell.setCellStyle(headStyle);

        downloadSheet.setColumnWidth(0, 2400);

        String key = "";
        int _count = 0;//用于设置excel的展示列，字段属性为隐藏的列不展示
        if(this.cols.size()>0){
            for (int i = 0; i < this.cols.size(); i++) {
                if (this.cols.get(i) instanceof CellModel) {
                    key = ((CellModel) this.cols.get(i)).getName();
                } else {
                    key = this.cols.get(i).toString();
                    if (key!=""||key!=null){
                        key = key.substring(key.indexOf("[") + 1, key.indexOf("]"));
                    }
                }
                _count += 1;
                if (rowspos[i] != 0) {
                    HSSFRow _prerow = downloadSheet.getRow(rowspos[i]);
                    cell = _prerow.getCell((_count));
                    if (cell == null) {
                        cell = _prerow.createCell((_count));
                    }
                } else {
                    cell = row.createCell((_count));
                }
                cell.setCellValue(key);
                cell.setCellType(CellType.STRING);

                downloadSheet.setColumnWidth(_count, 4000);
                cell.setCellStyle(headStyle);

            }
        }

        //冻结第一行 第一列
        /**
         * 冻结参数......
         */
        downloadSheet.createFreezePane(this.locksize + 1, this.level + 1);
    }

    private void cE() {
        wb = null;
        wb = new HSSFWorkbook();
        HSSFPalette palette = wb.getCustomPalette();  //wb HSSFWorkbook对象
        palette.setColorAtIndex((short) 11, (byte) (228), (byte) (228), (byte) (228));//#E4E4E4
        style = wb.createCellStyle();
        downloadSheet = wb.createSheet();
        headStyle = wb.createCellStyle();
        dataStyle = wb.createCellStyle();
        rowStyle = wb.createCellStyle();
        dataFormat = wb.createDataFormat();
        headFont = wb.createFont();
        dataFont = wb.createFont();
        rowFont = wb.createFont();

//        headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // headFont.setBoldweight((short) 700);
        headFont.setBold(true);
        dataFont.setFontHeightInPoints((short) 10);
        dataFont.setColor(IndexedColors.LIGHT_BLUE.index);
        // dataFont.setBoldweight((short) 500);
        dataFont.setBold(true);
        rowFont.setColor(IndexedColors.DARK_RED.index);
        rowFont.setFontHeightInPoints((short) 12);

        headStyle.setFont(headFont);
        headStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
        headStyle.setAlignment(HorizontalAlignment.CENTER);
        headStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        headStyle.setFillForegroundColor((short) 11);
        headStyle.setLocked(true);

        dataStyle.setFont(dataFont);
        dataStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
        dataStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        dataStyle.setFillForegroundColor((short) 11);
        dataStyle.setBorderBottom(BorderStyle.THIN);
        dataStyle.setBorderTop(BorderStyle.THIN);
        dataStyle.setBorderLeft(BorderStyle.THIN);
        dataStyle.setBorderRight(BorderStyle.THIN);
        dataStyle.setWrapText(true);
        dataStyle.setLocked(true);

        rowStyle.setFont(rowFont);
        rowStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        rowStyle.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.index);
        rowStyle.setBorderLeft(BorderStyle.THICK);
        rowStyle.setBorderBottom(BorderStyle.THIN);
    }
}