ExportAllUtils.java 13 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372
  1. /**
  2. *
  3. */
  4. package com.kingdee.shr.customer.gtiit.util;
  5. import java.io.IOException;
  6. import java.io.OutputStream;
  7. import java.io.UnsupportedEncodingException;
  8. import java.net.URLEncoder;
  9. import java.sql.Date;
  10. import java.sql.SQLException;
  11. import java.util.LinkedHashMap;
  12. import java.util.List;
  13. import java.util.Map;
  14. import java.util.Set;
  15. import java.util.stream.Collectors;
  16. import javax.servlet.http.HttpServletResponse;
  17. import org.apache.poi.ss.usermodel.CellStyle;
  18. import org.apache.poi.ss.usermodel.DataFormat;
  19. import org.apache.poi.ss.usermodel.HorizontalAlignment;
  20. import org.apache.poi.ss.usermodel.IndexedColors;
  21. import org.apache.poi.ss.util.CellRangeAddress;
  22. import org.apache.poi.xssf.usermodel.XSSFCell;
  23. import org.apache.poi.xssf.usermodel.XSSFCellStyle;
  24. import org.apache.poi.xssf.usermodel.XSSFFont;
  25. import org.apache.poi.xssf.usermodel.XSSFRow;
  26. import org.apache.poi.xssf.usermodel.XSSFSheet;
  27. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  28. import com.cloudera.impala.jdbc4.internal.apache.log4j.Logger;
  29. import com.kingdee.jdbc.rowset.IRowSet;
  30. /**
  31. * @author ISSUSER
  32. * 到处全部的工具类
  33. */
  34. public class ExportAllUtils {
  35. private static Logger logger1 = Logger.getLogger("com.kingdee.shr.customer.gtiit.util.ExportAllUtils");
  36. /**
  37. *
  38. * @param response
  39. * response
  40. * @param resource
  41. * 需要导出的数据源
  42. * @param fileName
  43. * 导出文件名称
  44. * @param selectFieldsTypeMap
  45. * 存放查询字段对应导出类型和导出样式 key selectField 导出字段 value
  46. * fieldType_cellStyleMap key 导出查询字段 value 导出类型_导出样式 导出样式 key
  47. * DateType 日期格式 value "yyyy-mm-dd" key NumberType 数字 value '0.0_
  48. * '->保留一位小数 '0_ '->整数 key StringType 文本 value @
  49. * @throws SQLException
  50. */
  51. public static void exportData(HttpServletResponse response, Object resource, String fileName,
  52. LinkedHashMap<String, String> selectFieldsTypeMap,List<String>list) throws SQLException {
  53. // 创建新的Excel 工作簿
  54. // Excel2003版本(包含2003)以前使用HSSFWorkbook类,扩展名为.xls
  55. // Excel2007版本(包含2007)以后使用XSSFWorkbook类,扩展名为.xlsx
  56. XSSFWorkbook workBook = new XSSFWorkbook();
  57. // 在Excel工作簿中创建新的工作表,名为"Sheet1"
  58. XSSFSheet sheet = workBook.createSheet("Sheet1");
  59. DataFormat format = workBook.createDataFormat();
  60. // 设置数字格式为整数,不保留小数 (_ )空格是必须要切勿格式
  61. if (resource != null) {
  62. if (resource instanceof IRowSet) {
  63. IRowSet rs = (IRowSet) resource;
  64. // 列数
  65. int numColumns = selectFieldsTypeMap.entrySet().size();
  66. // 创建多行多列的数据
  67. int i = 0;
  68. // 创建标题
  69. XSSFRow titleRow = sheet.createRow(i);
  70. // 列名
  71. List<String> keyList = selectFieldsTypeMap.keySet().stream().collect(Collectors.toList());
  72. //List<String> chineseList = fieldsTypeMap.keySet().stream().collect(Collectors.toList());
  73. if (i == 0) {
  74. for (int j = 0; j < numColumns; j++) {
  75. // 创建列
  76. XSSFCell cell = titleRow.createCell(j);
  77. //cell.setCellValue(keyList.get(j));
  78. //cell.setCellValue(chineseList.get(j));
  79. cell.setCellValue(list.get(j));
  80. }
  81. }
  82. i++;
  83. // 创建数据
  84. while (rs.next()) {
  85. try {
  86. XSSFRow dateRow = sheet.createRow(i);
  87. for (int j = 0; j < numColumns; j++) {
  88. // 创建列
  89. XSSFCell cell = dateRow.createCell(j);
  90. // 由于开发与测试环境的数据库类型与不一致 导致无法从数据库表读取相关字段类型判断 且有相关计算字段
  91. // 所以采用参数判断
  92. String column = keyList.get(j);
  93. String fieldTypeAndcellStyleType = selectFieldsTypeMap.get(column);
  94. int indexOf = fieldTypeAndcellStyleType.indexOf("_");
  95. // 字段属性类型
  96. String fieldType = fieldTypeAndcellStyleType.substring(0, indexOf);
  97. //样式类型
  98. String cellStyleType = fieldTypeAndcellStyleType.substring(indexOf + 1,
  99. fieldTypeAndcellStyleType.length());
  100. // 不设置格式
  101. String t=rs.getString(column);
  102. cell.setCellValue(rs.getString(column));
  103. if ("Date".equals(fieldType)) {
  104. // 日期
  105. // cell.setCellValue(rs.getString(column));
  106. Date date = rs.getDate(column);
  107. cell.setCellValue(date);
  108. XSSFCellStyle stylee = workBook.createCellStyle();
  109. stylee.setDataFormat(format.getFormat(cellStyleType));
  110. cell.setCellStyle(stylee);
  111. // cell.setCellValue(rs.getString(column));
  112. } else if ("Number".equals(fieldType)) {
  113. // 数值 (小数和整数)
  114. String value = rs.getString(column);
  115. cell.setCellValue(value);
  116. if (value != null && !("").equals(value)) {
  117. XSSFCellStyle style = workBook.createCellStyle();
  118. style.setDataFormat(format.getFormat(String.format(cellStyleType, " ")));
  119. cell.setCellStyle(style);
  120. if (cellStyleType.contains(".")) {
  121. // 小数
  122. cell.setCellValue(Double.parseDouble(value));
  123. } else {
  124. cell.setCellValue(Integer.parseInt(value));
  125. }
  126. }
  127. } else if ("String".equals(fieldType)) {
  128. // 文本类型
  129. if (rs.getString(column) != null && !("").equals(rs.getString(column))) {
  130. // style.setDataFormat(format.getFormat(cellStyleType));
  131. // cell.setCellStyle(style);
  132. cell.setCellValue(rs.getString(column));
  133. }
  134. }
  135. }
  136. i++;
  137. } catch (Exception e) {
  138. logger1.info("Excel file was created Error!" + e.getMessage());
  139. }
  140. }
  141. try {
  142. setResponseHeader(response, fileName);
  143. OutputStream os = response.getOutputStream();
  144. workBook.write(os);
  145. workBook.close();
  146. os.flush();
  147. os.close();
  148. logger1.info("Excel file was created successfully!");
  149. } catch (IOException e) {
  150. logger1.info("Error while writing to file " + e.getMessage());
  151. e.printStackTrace();
  152. }
  153. }
  154. }
  155. }
  156. public static void setResponseHeader(HttpServletResponse response, String fileName) {
  157. try {
  158. try {
  159. fileName = URLEncoder.encode(fileName, "UTF-8");
  160. } catch (UnsupportedEncodingException e) {
  161. e.printStackTrace();
  162. }
  163. response.setContentType("application/octet-stream;charset=UTF-8");
  164. response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
  165. response.addHeader("Pargam", "no-cache");
  166. response.addHeader("Cache-Control", "no-cache");
  167. } catch (Exception ex) {
  168. ex.printStackTrace();
  169. }
  170. }
  171. // 教员缺勤的导出模板样式
  172. /**
  173. *
  174. * @param response
  175. * response
  176. * @param resource
  177. * 需要导出的数据源
  178. * @param fileName
  179. * 导出文件名称
  180. * @param selectFieldsTypeMap
  181. * 存放查询字段对应导出类型和导出样式 key selectField 导出字段 value
  182. * fieldType_cellStyleMap key 导出查询字段 value 导出类型_导出样式 导出样式 key
  183. * DateType 日期格式 value "yyyy-mm-dd" key NumberType 数字 value '0.0_
  184. * '->保留一位小数 '0_ '->整数 key StringType 文本 value @
  185. * @param formatstartDate
  186. * 开始时间
  187. * @param formatendDate
  188. * 结束时间
  189. * @param numberSet
  190. * 存储工号的set
  191. * @param numberMap key是工号 value工号对应的是考勤类型
  192. * @throws SQLException
  193. */
  194. public static void morisExportData(HttpServletResponse response, Object resource, String fileName,
  195. LinkedHashMap<String, String> selectFieldsTypeMap, List<String> list,
  196. Map<String, Map<String, String>> numberMap, Set<String> numberSet,
  197. String formatstartDate,String formatendDate ) throws SQLException {
  198. // 创建新的Excel 工作簿
  199. // Excel2003版本(包含2003)以前使用HSSFWorkbook类,扩展名为.xls
  200. // Excel2007版本(包含2007)以后使用XSSFWorkbook类,扩展名为.xlsx
  201. XSSFWorkbook workBook = new XSSFWorkbook();
  202. // 在Excel工作簿中创建新的工作表,名为"Sheet1"
  203. XSSFSheet sheet = workBook.createSheet("Sheet1");
  204. DataFormat format = workBook.createDataFormat();
  205. // 设置数字格式为整数,不保留小数 (_ )空格是必须要切勿格式
  206. if (resource != null) {
  207. if (resource instanceof IRowSet) {
  208. IRowSet rs = (IRowSet) resource;
  209. // 列数
  210. int numColumns = selectFieldsTypeMap.entrySet().size();
  211. // 创建多行多列的数据
  212. // 先空3行
  213. XSSFRow firstRow = sheet.createRow(0);
  214. firstRow.createCell(0).setCellValue("Faculty Absence Report ");
  215. sheet.createRow(1);
  216. XSSFRow threeRow = sheet.createRow(2);
  217. // 创建字体样式
  218. XSSFFont font = workBook.createFont();
  219. font.setFontName("Arial"); // 设置字体为 Arial
  220. font.setFontHeightInPoints((short) 12); // 设置字体大小为 12 点
  221. font.setColor(IndexedColors.BLACK.getIndex()); // 设置字体颜色为黑色
  222. // 创建一个新的单元格样式,并将字体样式应用于该样式
  223. CellStyle cellStyle = workBook.createCellStyle();
  224. cellStyle.setFont(font);
  225. cellStyle.setAlignment(HorizontalAlignment.CENTER); // 设置水平居中
  226. // 将单元格样式应用于第3列的单元格
  227. firstRow.getCell(0).setCellStyle(cellStyle);
  228. // 定义要合并的单元格范围
  229. CellRangeAddress mergedRegion = new CellRangeAddress(0, 0 + 1, 0, 0 + 6);
  230. // 合并单元格
  231. sheet.addMergedRegion(mergedRegion);
  232. // List<String>nameList=new ArrayList<>();
  233. // nameList.add("A:Annual Leave");
  234. // nameList.add("B:Business Travel");
  235. // nameList.add("O:Other Leave");
  236. // 创建列
  237. threeRow.createCell(0).setCellValue("A:Annual Leave");
  238. // threeRow.createCell(1);
  239. // threeRow.createCell(2);
  240. threeRow.createCell(2).setCellValue("B:Business Travel");
  241. // threeRow.createCell(3);
  242. // threeRow.createCell(4);
  243. threeRow.createCell(5).setCellValue("O:Other Leave");
  244. sheet.createRow(3);
  245. // 拼接日期
  246. String allDate="Duration:"+formatstartDate+"-"+formatendDate;
  247. XSSFRow fourRow=sheet.createRow(4);
  248. fourRow.createCell(0).setCellValue(allDate);
  249. // 合并单元格
  250. CellRangeAddress megred = new CellRangeAddress(4, 4, 0, 3);
  251. sheet.addMergedRegion(megred);
  252. // 从第四行开始创建数据
  253. int i = 5;
  254. // 创建标题行
  255. XSSFRow titleRow = sheet.createRow(i);
  256. // 列名
  257. List<String> keyList = selectFieldsTypeMap.keySet().stream().collect(Collectors.toList());
  258. if (i == 5) {
  259. for (int j = 0; j < numColumns; j++) {
  260. // 创建列
  261. XSSFCell cell = titleRow.createCell(j);
  262. // cell.setCellValue(keyList.get(j));
  263. // cell.setCellValue(chineseList.get(j));
  264. cell.setCellValue(list.get(j));
  265. }
  266. }
  267. i++;
  268. // 创建数据
  269. rs.beforeFirst();
  270. while (rs.next()) {
  271. int tosum = 0;
  272. String number = null;
  273. String staffid = rs.getString("StaffID");
  274. // 如果不存在工号说明 工号以及使用过了 需要跳出循环
  275. if (!numberSet.contains(staffid)) {
  276. continue;
  277. }
  278. // String todate=null;
  279. try {
  280. // 创建行
  281. XSSFRow dateRow = sheet.createRow(i);
  282. for (int j = 0; j < numColumns; j++) {
  283. String column = keyList.get(j);
  284. tosum++;
  285. if (column.equals("StaffID")) {
  286. number = rs.getString(column);
  287. }
  288. // 创建列
  289. XSSFCell cell = dateRow.createCell(j);
  290. // 由于开发与测试环境的数据库类型与不一致 导致无法从数据库表读取相关字段类型判断 且有相关计算字段
  291. // 所以采用参数判断
  292. // 获取工号
  293. // 如果tosum>5的时候说明开始存储日期对应的字段
  294. if (tosum > 5) {
  295. if (numberSet.contains(number)) {
  296. Map<String, String> toNumberMap = numberMap.get(number);
  297. for (Map.Entry<String, String> entry : toNumberMap.entrySet()) {
  298. // 截取工号和日期
  299. String key = entry.getKey();
  300. // 日期
  301. String subKey = key.substring(0, 5);
  302. // 日期对应的值
  303. String value = entry.getValue();
  304. if (subKey.equals(column)) {
  305. cell.setCellValue(value);
  306. }
  307. }
  308. }
  309. } else {
  310. // 不设置格式
  311. String tname = rs.getString(column);
  312. cell.setCellValue(rs.getString(column));
  313. }
  314. }
  315. // 为了防止重复 循环一次 把这个工号元素去除
  316. numberSet.remove(number);
  317. i++;
  318. } catch (Exception e) {
  319. logger1.info("Excel file was created Error!" + e.getMessage());
  320. }
  321. }
  322. try {
  323. setResponseHeader(response, fileName);
  324. OutputStream os = response.getOutputStream();
  325. workBook.write(os);
  326. workBook.close();
  327. os.flush();
  328. os.close();
  329. logger1.info("Excel file was created successfully!");
  330. } catch (IOException e) {
  331. logger1.info("Error while writing to file " + e.getMessage());
  332. e.printStackTrace();
  333. }
  334. }
  335. }
  336. }
  337. }