123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372 |
- /**
- *
- */
- package com.kingdee.shr.customer.gtiit.util;
- import java.io.IOException;
- import java.io.OutputStream;
- import java.io.UnsupportedEncodingException;
- import java.net.URLEncoder;
- import java.sql.Date;
- import java.sql.SQLException;
- import java.util.LinkedHashMap;
- import java.util.List;
- import java.util.Map;
- import java.util.Set;
- import java.util.stream.Collectors;
- import javax.servlet.http.HttpServletResponse;
- import org.apache.poi.ss.usermodel.CellStyle;
- import org.apache.poi.ss.usermodel.DataFormat;
- import org.apache.poi.ss.usermodel.HorizontalAlignment;
- import org.apache.poi.ss.usermodel.IndexedColors;
- import org.apache.poi.ss.util.CellRangeAddress;
- 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.cloudera.impala.jdbc4.internal.apache.log4j.Logger;
- import com.kingdee.jdbc.rowset.IRowSet;
- /**
- * @author ISSUSER
- * 到处全部的工具类
- */
- public class ExportAllUtils {
- 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());
- // 不设置格式
- String t=rs.getString(column);
- 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)) {
- // 数值 (小数和整数)
- String value = rs.getString(column);
- cell.setCellValue(value);
- if (value != null && !("").equals(value)) {
- XSSFCellStyle style = workBook.createCellStyle();
- style.setDataFormat(format.getFormat(String.format(cellStyleType, " ")));
- cell.setCellStyle(style);
- if (cellStyleType.contains(".")) {
- // 小数
- cell.setCellValue(Double.parseDouble(value));
- } else {
- cell.setCellValue(Integer.parseInt(value));
- }
- }
- } 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));
- }
- }
- }
- 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();
- }
- }
- // 教员缺勤的导出模板样式
- /**
- *
- * @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 @
- * @param formatstartDate
- * 开始时间
- * @param formatendDate
- * 结束时间
- * @param numberSet
- * 存储工号的set
- * @param numberMap key是工号 value工号对应的是考勤类型
- * @throws SQLException
- */
- public static void morisExportData(HttpServletResponse response, Object resource, String fileName,
- LinkedHashMap<String, String> selectFieldsTypeMap, List<String> list,
- Map<String, Map<String, String>> numberMap, Set<String> numberSet,
- String formatstartDate,String formatendDate ) 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();
- // 创建多行多列的数据
- // 先空3行
- XSSFRow firstRow = sheet.createRow(0);
- firstRow.createCell(0).setCellValue("Faculty Absence Report ");
- sheet.createRow(1);
- XSSFRow threeRow = sheet.createRow(2);
- // 创建字体样式
- XSSFFont font = workBook.createFont();
- font.setFontName("Arial"); // 设置字体为 Arial
- font.setFontHeightInPoints((short) 12); // 设置字体大小为 12 点
- font.setColor(IndexedColors.BLACK.getIndex()); // 设置字体颜色为黑色
- // 创建一个新的单元格样式,并将字体样式应用于该样式
- CellStyle cellStyle = workBook.createCellStyle();
- cellStyle.setFont(font);
- cellStyle.setAlignment(HorizontalAlignment.CENTER); // 设置水平居中
- // 将单元格样式应用于第3列的单元格
- firstRow.getCell(0).setCellStyle(cellStyle);
- // 定义要合并的单元格范围
- CellRangeAddress mergedRegion = new CellRangeAddress(0, 0 + 1, 0, 0 + 6);
- // 合并单元格
- sheet.addMergedRegion(mergedRegion);
- // List<String>nameList=new ArrayList<>();
- // nameList.add("A:Annual Leave");
- // nameList.add("B:Business Travel");
- // nameList.add("O:Other Leave");
- // 创建列
- threeRow.createCell(0).setCellValue("A:Annual Leave");
- // threeRow.createCell(1);
- // threeRow.createCell(2);
- threeRow.createCell(2).setCellValue("B:Business Travel");
- // threeRow.createCell(3);
- // threeRow.createCell(4);
- threeRow.createCell(5).setCellValue("O:Other Leave");
- sheet.createRow(3);
- // 拼接日期
- String allDate="Duration:"+formatstartDate+"-"+formatendDate;
- XSSFRow fourRow=sheet.createRow(4);
- fourRow.createCell(0).setCellValue(allDate);
- // 合并单元格
- CellRangeAddress megred = new CellRangeAddress(4, 4, 0, 3);
- sheet.addMergedRegion(megred);
- // 从第四行开始创建数据
- int i = 5;
- // 创建标题行
- XSSFRow titleRow = sheet.createRow(i);
- // 列名
- List<String> keyList = selectFieldsTypeMap.keySet().stream().collect(Collectors.toList());
- if (i == 5) {
- 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++;
- // 创建数据
- rs.beforeFirst();
- while (rs.next()) {
- int tosum = 0;
- String number = null;
- String staffid = rs.getString("StaffID");
- // 如果不存在工号说明 工号以及使用过了 需要跳出循环
- if (!numberSet.contains(staffid)) {
- continue;
- }
- // String todate=null;
- try {
- // 创建行
- XSSFRow dateRow = sheet.createRow(i);
- for (int j = 0; j < numColumns; j++) {
- String column = keyList.get(j);
- tosum++;
- if (column.equals("StaffID")) {
- number = rs.getString(column);
- }
- // 创建列
- XSSFCell cell = dateRow.createCell(j);
- // 由于开发与测试环境的数据库类型与不一致 导致无法从数据库表读取相关字段类型判断 且有相关计算字段
- // 所以采用参数判断
- // 获取工号
- // 如果tosum>5的时候说明开始存储日期对应的字段
- if (tosum > 5) {
- if (numberSet.contains(number)) {
- Map<String, String> toNumberMap = numberMap.get(number);
- for (Map.Entry<String, String> entry : toNumberMap.entrySet()) {
- // 截取工号和日期
- String key = entry.getKey();
- // 日期
- String subKey = key.substring(0, 5);
- // 日期对应的值
- String value = entry.getValue();
- if (subKey.equals(column)) {
- cell.setCellValue(value);
- }
- }
- }
- } else {
- // 不设置格式
- String tname = rs.getString(column);
- cell.setCellValue(rs.getString(column));
- }
- }
- // 为了防止重复 循环一次 把这个工号元素去除
- numberSet.remove(number);
- 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();
- }
- }
- }
- }
- }
|