guangzhouyiselie/GDYSL/websrc/com/kingdee/shr/customer/gtiit/util/NewExportUtils.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.math.RoundingMode;
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.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.kingdee.jdbc.rowset.IRowSet;

/**
 * 
 * @author lang
 * 导出工具类附加列名 
 */
public class NewExportUtils {

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

	/**
	 * 
	 * @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());
				// 创建字体样式
				XSSFFont font = workBook.createFont();
				font.setFontName("宋体"); // 设置字体为 Arial
				font.setFontHeightInPoints((short) 11); // 设置字体大小为 12 点
				font.setColor(IndexedColors.BLACK.getIndex()); // 设置字体颜色为黑色
				font.setBold(true);
				// 创建一个新的单元格样式，并将字体样式应用于该样式
				CellStyle cellStyle = workBook.createCellStyle();
				cellStyle.setFont(font);
				if (i == 0) {
					for (int j = 0; j < numColumns; j++) {
						// 创建列
						XSSFCell cell = titleRow.createCell(j);
						// 创建列标题
						//cell.setCellValue(keyList.get(j));
						cell.setCellValue(list.get(j));
						// 调整列宽
						sheet.setColumnWidth(j, 6000);
						// 应用字体样式
						titleRow.getCell(j).setCellStyle(cellStyle);
					}
				}
				i++;
				// 创建数据
				while (rs.next()) {
					try {
						XSSFRow dateRow = sheet.createRow(i);
						for (int j = 0; j < numColumns; j++) {
							
							XSSFCellStyle style = workBook.createCellStyle();

							// 创建列
							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());
							if ("Date".equals(fieldType)) {
								// 日期
								Date date = rs.getDate(column);
								
								style.setDataFormat(format.getFormat(cellStyleType));
								cell.setCellStyle(style);
								cell.setCellValue(date);
							} else if ("Number".equals(fieldType)) {
								// 数值 (小数和整数)
								style.setDataFormat(format.getFormat(String.format(cellStyleType, " ")));
								String value = rs.getString(column);
								cell.setCellStyle(style);
								// 如果是年份要处理四舍五入保留两位
								if(column.equals("Yearsofservice")||column.equals("YearsOfService")) {
									if(StringUtils.isNotBlank(value)) {
										BigDecimal bgyears=new BigDecimal(value);
								        BigDecimal toYearsOfService = bgyears.setScale(2, RoundingMode.HALF_UP);  
								        value=toYearsOfService.toString();
								        System.out.println("四舍五入后的vlue"+value);
									}
								}
									
								cell.setCellValue(value);
//								if (cellStyleType.contains(".")) {
//									// 小数
//									cell.setCellValue(Double.parseDouble(value));
//								} else { 
//									cell.setCellValue(Integer.parseInt(value));
//								}
							} else if ("String".equals(fieldType)) {
								// 文本类型
//								style.setDataFormat(format.getFormat(cellStyleType));
//								cell.setCellStyle(style);
								cell.setCellValue(rs.getString(column));
							}
						}
						i++;
					} catch (Exception e) {
						System.out.println();
						logger.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();
					logger.info("Excel file was created successfully!");
				} catch (IOException e) {
					logger.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();
		}
	}
}