reportcreator.java

信息发布 发布系统 动态的菜单 和 信息统计

package com.xuntian.material.report;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;

public class ReportCreator {
    private ReportProperties properties;

    private int fontSizeTitle;

    private int fontSizeheader;

    private int fontSizeData;

    private int fontBoldWeight;

    private static ReportCreator reportCreator = new ReportCreator();

    public static ReportCreator getInstance() {
        return reportCreator;
    }

    private ReportCreator() {
        properties = ReportProperties.getInstance();
        fontSizeTitle = properties.getFontSizeTitle();
        fontSizeheader = properties.getFontSizeheader();
        fontSizeData = properties.getFontSizeData();
        fontBoldWeight = properties.getFontBoldWeight();
    }

    public HSSFWorkbook creator(String report, ResultSet rs,
            Map<String, String> headValueMap, Map<String, String> footValueMap)
            throws SQLException {

        HSSFWorkbook wb = new HSSFWorkbook();
        String[] titles = properties.getTitle(report);

        if (titles.length == 1) {
            HSSFSheet sheet = wb.createSheet();
            setHeadCells(wb, sheet, report);
            setTitle(wb, sheet, titles[0]);
            wb.setSheetName(0, titles[0], HSSFWorkbook.ENCODING_UTF_16);
            int row = setHeadTitles(wb, sheet, report);
            setHeadValues(wb, sheet, report, headValueMap);
            try {
                row = setData(wb, sheet, row, report, rs);
            } catch (IOException e) {
                throw new SQLException(e.getMessage());
            }
            setFootCells(wb, sheet, row, report);
            setFootTitles(wb, sheet, row, report);
            setFootValues(wb, sheet, row, report, footValueMap);
        } else {
            for (int i = 0; i < titles.length - 2; i++) {
                HSSFSheet sheet = wb.createSheet();
                setHeadCells(wb, sheet, report);
                setTitle(wb, sheet, titles[0]);
                {
                    String[] values = titles[1].split("\\|");
                    HSSFFont font = wb.createFont();
                    font.setFontHeightInPoints((short) fontSizeheader);

                    HSSFCellStyle style = wb.createCellStyle();
                    style.setFont(font);
                    style.setAlignment(Short.parseShort(values[2]));
                    style.setVerticalAlignment(Short.parseShort(values[3]));
                    style.setWrapText(true);

                    int rowIndex = Integer.parseInt(values[0]);
                    short cellIndex = Short.parseShort(values[1]);

                    HSSFRow row = sheet.getRow(rowIndex);
                    if (row == null) {
                        row = sheet.createRow(rowIndex);
                    }
                    HSSFCell cell = row.getCell(cellIndex);
                    if (cell == null) {
                        cell = row.createCell(cellIndex);
                    }
                    cell.setEncoding(HSSFWorkbook.ENCODING_UTF_16);
                    cell.setCellStyle(style);
                    cell.setCellValue(titles[i + 2]);
                    wb.setSheetName(i, titles[i + 2],
                            HSSFWorkbook.ENCODING_UTF_16);
                }
                int row = setHeadTitles(wb, sheet, report);
                setHeadValues(wb, sheet, report, headValueMap);
                rs.beforeFirst();
                try {
                    row = setData(wb, sheet, row, report, rs);
                } catch (IOException e) {
                    throw new SQLException(e.getMessage());
                }
                setFootCells(wb, sheet, row, report);
                setFootTitles(wb, sheet, row, report);
                setFootValues(wb, sheet, row, report, footValueMap);
            }
        }
        if (rs != null) {
            rs.close();
        }
        return wb;
    }

    private void setHeadCells(HSSFWorkbook wb, HSSFSheet sheet, String report) {
        String[][] headCells = properties.getHeadCells(report);
        int rowFrom, rowTo;
        short colFrom, colTo;
        for (String[] headCell : headCells) {
            rowFrom = Integer.parseInt(headCell[0]);
            colFrom = Short.parseShort(headCell[1]);
            rowTo = Integer.parseInt(headCell[2]);
            colTo = Short.parseShort(headCell[3]);
            Region region = new Region(rowFrom, colFrom, rowTo, colTo);
            sheet.addMergedRegion(region);
        }
    }

    private void setTitle(HSSFWorkbook wb, HSSFSheet sheet, String title) {
        HSSFFont font = wb.createFont();
        font.setBoldweight((short) fontBoldWeight);
        font.setFontHeightInPoints((short) fontSizeTitle);

        HSSFCellStyle style = wb.createCellStyle();
        style.setFont(font);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        HSSFCell cell = sheet.createRow(0).createCell((short) 0);
        cell.setEncoding(HSSFWorkbook.ENCODING_UTF_16);
        cell.setCellStyle(style);
        cell.setCellValue(title);
    }

    private int setHeadTitles(HSSFWorkbook wb, HSSFSheet sheet, String report) {
        String[] titlesTable = properties.getTitlesTable(report);
        int rowTableFrom = Integer.parseInt(titlesTable[0]);
        int rowTableTo = Integer.parseInt(titlesTable[2]);
        short colTableFrom = Short.parseShort(titlesTable[1]);
        short colTableTo = Short.parseShort(titlesTable[3]);

        int begin = 0;

        HSSFFont font = wb.createFont();
        font.setBoldweight((short) fontBoldWeight);
        font.setFontHeightInPoints((short) fontSizeheader);

        String[][] headTitles = properties.getHeadTitles(report);
        HSSFCellStyle style;
        int rowIndex;
        short cellIndex;
        HSSFRow row;
        HSSFCell cell;
        for (String[] headTitle : headTitles) {
            style = wb.createCellStyle();
            style.setFont(font);
            style.setAlignment(Short.parseShort(headTitle[2]));
            style.setVerticalAlignment(Short.parseShort(headTitle[3]));
            style.setWrapText(true);

            rowIndex = Integer.parseInt(headTitle[0]);
            if (rowIndex > begin) {
                begin = rowIndex;
            }
            cellIndex = Short.parseShort(headTitle[1]);

            row = sheet.getRow(rowIndex);
            if (row == null) {
                row = sheet.createRow(rowIndex);
            }
            cell = row.getCell(cellIndex);
            if (cell == null) {
                cell = row.createCell(cellIndex);
            }
            cell.setEncoding(HSSFWorkbook.ENCODING_UTF_16);
            cell.setCellStyle(style);
            cell.setCellValue(headTitle[4]);
        }
        for (int i = rowTableFrom; i <= rowTableTo; i++) {
            row = sheet.getRow(i);
            if (row == null) {
                row = sheet.createRow(i);
            }
            for (short j = colTableFrom; j <= colTableTo; j++) {
                cell = row.getCell(j);
                if (cell == null) {
                    style = wb.createCellStyle();
                    cell = row.createCell(j);
                    cell.setCellStyle(style);
                } else {
                    style = cell.getCellStyle();
                }
                setStyleBorder(style);
            }
        }

        return begin;
    }

    private void setHeadValues(HSSFWorkbook wb, HSSFSheet sheet, String report,
            Map<String, String> headValueMap) {
        HSSFFont font = wb.createFont();
        font.setFontHeightInPoints((short) fontSizeData);

        String[][] headValues = properties.getHeadValues(report);
        HSSFCellStyle style;
        int rowIndex;
        short cellIndex;
        HSSFRow row;
        HSSFCell cell;
        for (String[] headValue : headValues) {
            rowIndex = Integer.parseInt(headValue[0]);
            cellIndex = Short.parseShort(headValue[1]);

            row = sheet.getRow(rowIndex);
            if (row == null) {
                row = sheet.createRow(rowIndex);
            }
            cell = row.getCell(cellIndex);
            if (cell == null) {
                cell = row.createCell(cellIndex);
            }
            style = wb.createCellStyle();
            style.setFont(font);
            style.setAlignment(Short.parseShort(headValue[2]));
            style.setVerticalAlignment(Short.parseShort(headValue[3]));
            style.setWrapText(true);

            cell.setEncoding(HSSFWorkbook.ENCODING_UTF_16);
            cell.setCellStyle(style);
            cell.setCellValue(headValueMap.get(headValue[4]));
        }
    }

    private int setData(HSSFWorkbook wb, HSSFSheet sheet, int begin,
            String report, ResultSet rs) throws SQLException, IOException {
        String[] dataAlign = properties.getDataAlign(report);
        HSSFRow row = sheet.getRow(begin);
        HSSFCell cell;

        HSSFFont font = wb.createFont();
        font.setFontHeightInPoints((short) fontSizeData);

        HSSFCellStyle style;
        begin++;
        int record = begin;
        if (rs != null) {
            ResultSetMetaData meta = rs.getMetaData();
            short columnCount = (short) meta.getColumnCount();
            while (rs.next()) {
                row = sheet.getRow(record);
                if (row == null) {
                    row = sheet.createRow(record++);
                }
                for (short i = 0; i < columnCount; i++) {
                    cell = row.getCell(i);
                    if (cell == null) {
                        cell = row.createCell(i);
                    }
                    style = wb.createCellStyle();
                    cell.setEncoding(HSSFWorkbook.ENCODING_UTF_16);
                    cell.setCellStyle(style);
                    style.setFont(font);
                    style.setAlignment(Short.parseShort(dataAlign[i]));
                    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
                    setStyleBorder(style);

                    InputStream tempCellStream = rs.getBinaryStream(i + 1);
                    BufferedReader br = null;

                    String tempCell = null;
                    if (tempCellStream != null) {
                        InputStreamReader ir = new InputStreamReader(
                                tempCellStream);
                        br = new BufferedReader(ir);
                        tempCell = new String(br.readLine().getBytes(), "GBK");
                        tempCellStream.close();
                        ir.close();
                        br.close();
                    }
                    cell.setCellValue(tempCell);

                }
            }
        }
        return record;
    }

    private void setFootCells(HSSFWorkbook wb, HSSFSheet sheet, int begin,
            String report) {
        String[][] footCells = properties.getFootCells(report);
        int rowFrom, rowTo;
        short colFrom, colTo;
        for (String[] footCell : footCells) {
            rowFrom = Integer.parseInt(footCell[0]) + begin;
            colFrom = Short.parseShort(footCell[1]);
            rowTo = Integer.parseInt(footCell[2]) + begin;
            colTo = Short.parseShort(footCell[3]);
            Region region = new Region(rowFrom, colFrom, rowTo, colTo);
            sheet.addMergedRegion(region);
        }
    }

    private void setFootTitles(HSSFWorkbook wb, HSSFSheet sheet, int begin,
            String report) {
        String[][] footTitles = properties.getFootTitles(report);
        HSSFFont font = wb.createFont();
        font.setBoldweight((short) fontBoldWeight);
        font.setFontHeightInPoints((short) fontSizeheader);

        HSSFCellStyle style;
        HSSFCell cell;
        int rowIndex;
        short cellIndex;
        for (String[] footTitle : footTitles) {
            rowIndex = Integer.parseInt(footTitle[0]) + begin;
            cellIndex = Short.parseShort(footTitle[1]);

            style = wb.createCellStyle();
            style.setFont(font);
            style.setAlignment(Short.parseShort(footTitle[2]));
            style.setVerticalAlignment(Short.parseShort(footTitle[3]));
            if (rowIndex < begin) {
                setStyleBorder(style);
            }

            cell = sheet.createRow(rowIndex).createCell(cellIndex);
            cell.setEncoding(HSSFWorkbook.ENCODING_UTF_16);
            cell.setCellStyle(style);
            cell.setCellValue(footTitle[4]);
        }
    }

    private void setFootValues(HSSFWorkbook wb, HSSFSheet sheet, int begin,
            String report, Map<String, String> footValueMap) {
        HSSFFont font = wb.createFont();
        font.setFontHeightInPoints((short) fontSizeData);

        String[][] footValues = properties.getFootValues(report);
        HSSFCellStyle style;
        int rowIndex;
        short cellIndex;
        HSSFRow row;
        HSSFCell cell;
        for (String[] footValue : footValues) {
            rowIndex = Integer.parseInt(footValue[0]) + begin;
            cellIndex = Short.parseShort(footValue[1]);

            row = sheet.getRow(rowIndex);
            if (row == null) {
                row = sheet.createRow(rowIndex);
            }
            cell = row.getCell(cellIndex);
            if (cell == null) {
                cell = row.createCell(cellIndex);
            }
            style = wb.createCellStyle();
            style.setFont(font);
            style.setAlignment(Short.parseShort(footValue[2]));
            style.setVerticalAlignment(Short.parseShort(footValue[3]));
            style.setWrapText(true);
            if (rowIndex < begin) {
                setStyleBorder(style);
            }

            cell.setEncoding(HSSFWorkbook.ENCODING_UTF_16);
            cell.setCellStyle(style);
            cell.setCellValue(footValueMap.get(footValue[4]));
        }
    }

    private void setStyleBorder(HSSFCellStyle style) {
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
    }
}