guangzhouyiselie/GDYSL/websrc/com/kingdee/shr/customer/gtiit/util/ExportAllUtil.java

/**
 *
 */
package com.kingdee.shr.customer.gtiit.util;

import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.sql.Date;
import java.sql.SQLException;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.stream.Collectors;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.cloudera.impala.jdbc4.internal.apache.log4j.Logger;
import com.kingdee.jdbc.rowset.IRowSet;

/**
 * @author ISSUSER
 * 到处全部的工具类
 */
public class ExportAllUtil {

    private static Logger logger1 = Logger.getLogger("com.kingdee.shr.customer.gtiit.util.ExportAllUtils");


    /**
     *
     * @param response
     *            response
     * @param resource
     *            需要导出的数据源
     * @param fileName
     *            导出文件名称
     * @param selectFieldsTypeMap
     *            存放查询字段对应导出类型和导出样式 key selectField 导出字段 value
     *            fieldType_cellStyleMap key 导出查询字段 value 导出类型_导出样式 导出样式 key
     *            DateType 日期格式 value "yyyy-mm-dd" key NumberType 数字 value '0.0_
     *            '->保留一位小数 '0_ '->整数 key StringType 文本 value @
     * @throws SQLException
     */
    public static void exportData(HttpServletResponse response, Object resource, String fileName,
                                  LinkedHashMap<String, String> selectFieldsTypeMap, List<String> list) throws SQLException {
        // 创建新的Excel 工作簿
        // Excel2003版本（包含2003）以前使用HSSFWorkbook类，扩展名为.xls
        // Excel2007版本（包含2007）以后使用XSSFWorkbook类，扩展名为.xlsx
        XSSFWorkbook workBook = new XSSFWorkbook();

        // 在Excel工作簿中创建新的工作表，名为"Sheet1"
        XSSFSheet sheet = workBook.createSheet("Sheet1");

        DataFormat format = workBook.createDataFormat();
        // 设置数字格式为整数，不保留小数 (_ )空格是必须要切勿格式
        if (resource != null) {
            if (resource instanceof IRowSet) {
                IRowSet rs = (IRowSet) resource;
                // 列数
                int numColumns = selectFieldsTypeMap.entrySet().size();
                // 创建多行多列的数据
                int i = 0;
                // 创建标题
                XSSFRow titleRow = sheet.createRow(i);
                // 列名
                List<String> keyList = selectFieldsTypeMap.keySet().stream().collect(Collectors.toList());
                //List<String> chineseList = fieldsTypeMap.keySet().stream().collect(Collectors.toList());

                if (i == 0) {
                    for (int j = 0; j < numColumns; j++) {
                        // 创建列
                        XSSFCell cell = titleRow.createCell(j);
                        //cell.setCellValue(keyList.get(j));
                        //cell.setCellValue(chineseList.get(j));
                        cell.setCellValue(list.get(j));
                    }
                }

                i++;
                // 创建数据
                while (rs.next()) {
                    try {
                        XSSFRow dateRow = sheet.createRow(i);
                        for (int j = 0; j < numColumns; j++) {
                            // 创建列
                            XSSFCell cell = dateRow.createCell(j);
                            // 由于开发与测试环境的数据库类型与不一致 导致无法从数据库表读取相关字段类型判断 且有相关计算字段
                            // 所以采用参数判断
                            String column = keyList.get(j);
                            String fieldTypeAndcellStyleType = selectFieldsTypeMap.get(column);
                            int indexOf = fieldTypeAndcellStyleType.indexOf("_");
                            // 字段属性类型
                            String fieldType = fieldTypeAndcellStyleType.substring(0, indexOf);
                            // 样式类型
                            String cellStyleType = fieldTypeAndcellStyleType.substring(indexOf + 1,
                                    fieldTypeAndcellStyleType.length());
                            // 不设置格式
                            cell.setCellValue(rs.getString(column));
                            if ("Date".equals(fieldType)) {
                                // 日期
                                // cell.setCellValue(rs.getString(column));
                                Date date = rs.getDate(column);
                                cell.setCellValue(date);
                                XSSFCellStyle stylee = workBook.createCellStyle();
                                stylee.setDataFormat(format.getFormat(cellStyleType));
                                cell.setCellStyle(stylee);
                                // cell.setCellValue(rs.getString(column));
                            } else if ("Number".equals(fieldType)) {
                                // 数值 (小数和整数)
                                BigDecimal value = rs.getBigDecimal(column) != null ? rs.getBigDecimal(column).setScale(2, 4) : BigDecimal.ZERO;
//                                cell.setCellValue(value);
                                XSSFCellStyle style = workBook.createCellStyle();
                                if (cellStyleType.contains(".")) {
                                    style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
                                    cell.setCellStyle(style);
                                    // 小数
                                    cell.setCellValue(value.doubleValue());
                                } else {
                                    style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
                                    cell.setCellStyle(style);
                                    cell.setCellValue(value.intValue());
                                }
                            } else if ("String".equals(fieldType)) {
                                // 文本类型
                                if (rs.getString(column) != null && !("").equals(rs.getString(column))) {
                                    // style.setDataFormat(format.getFormat(cellStyleType));
                                    // cell.setCellStyle(style);
                                    cell.setCellValue(rs.getString(column));
                                }
                            } else if ("StringDate".equals(fieldType)) {
                                // 文本类型
                                if (rs.getString(column) != null && !("").equals(rs.getString(column))) {
                                    cell.setCellValue(rs.getString(column).substring(0, 10));
                                }
                            }
                        }
                        i++;
                    } catch (Exception e) {
                        logger1.info("Excel file was created Error!" + e.getMessage());
                    }
                }
                try {
                    setResponseHeader(response, fileName);
                    OutputStream os = response.getOutputStream();
                    workBook.write(os);
                    workBook.close();
                    os.flush();
                    os.close();
                    logger1.info("Excel file was created successfully!");
                } catch (IOException e) {
                    logger1.info("Error while writing to file " + e.getMessage());
                    e.printStackTrace();
                }
            }
        }
    }

    public static void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            try {
                fileName = URLEncoder.encode(fileName, "UTF-8");
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            }
            response.setContentType("application/octet-stream;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
}