ExportExcelUtil.java 21 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589
  1. package com.goafanti.common.utils;
  2. import java.io.BufferedInputStream;
  3. import java.io.BufferedOutputStream;
  4. import java.io.File;
  5. import java.io.FileInputStream;
  6. import java.io.IOException;
  7. import java.io.InputStream;
  8. import java.io.OutputStream;
  9. import java.text.SimpleDateFormat;
  10. import java.util.List;
  11. import java.util.Map;
  12. import javax.servlet.http.HttpServletResponse;
  13. import org.apache.poi.ss.util.CellRangeAddress;
  14. import org.apache.poi.xssf.usermodel.XSSFCell;
  15. import org.apache.poi.xssf.usermodel.XSSFCellStyle;
  16. import org.apache.poi.xssf.usermodel.XSSFFont;
  17. import org.apache.poi.xssf.usermodel.XSSFRow;
  18. import org.apache.poi.xssf.usermodel.XSSFSheet;
  19. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  20. import com.goafanti.common.model.TOrderBillNew;
  21. import com.goafanti.order.bo.OrderReportBo;
  22. import com.goafanti.order.bo.TOrderBonusBo;
  23. import com.goafanti.order.bo.TOrderNewBo;
  24. import com.goafanti.order.bo.TOrderTaskListBo;
  25. import com.goafanti.order.enums.BonusSubject;
  26. import com.goafanti.order.enums.LiquidationNewState;
  27. import com.goafanti.order.enums.OrderNewState;
  28. import com.goafanti.order.enums.ProjectNewStage;
  29. import com.goafanti.techproject.enums.TaskState;
  30. public class ExportExcelUtil {
  31. //设置单元格格式
  32. private static XSSFCellStyle setCellStyle1(XSSFWorkbook workbook){
  33. // 设置字体
  34. XSSFFont font = workbook.createFont();
  35. // font.setColor(HSSFFont.COLOR_RED);
  36. font.setFontName("微软雅黑");
  37. font.setFontHeightInPoints((short) 10);//字体大小
  38. // 设置样式
  39. XSSFCellStyle cellStyle = workbook.createCellStyle();
  40. cellStyle.setFont(font); //字体
  41. //设置自动换行
  42. cellStyle.setWrapText(true);
  43. return cellStyle;
  44. }
  45. //下载课表
  46. public static void download(String path, HttpServletResponse response,String filename) {
  47. try {
  48. // path是指欲下载的文件的路径。
  49. File file = new File(path);
  50. // 取得文件名。
  51. // 以流的形式下载文件。
  52. InputStream fis = new BufferedInputStream(new FileInputStream(path));
  53. byte[] buffer = new byte[fis.available()];
  54. fis.read(buffer);
  55. fis.close();
  56. // 清空response
  57. response.reset();
  58. // 设置response的Header
  59. response.addHeader("Content-Disposition", "attachment;filename="
  60. + new String(filename.getBytes(),"iso-8859-1")); //防止中文乱码或者中文忽略的情况
  61. response.addHeader("Content-Length", "" + file.length());
  62. OutputStream toClient = new BufferedOutputStream(
  63. response.getOutputStream());
  64. response.setContentType("application/octet-stream;charset=utf-8");
  65. toClient.write(buffer);
  66. toClient.flush();
  67. toClient.close();
  68. } catch (IOException ex) {
  69. ex.printStackTrace();
  70. }
  71. }
  72. public static void download(String path, HttpServletResponse response) {
  73. try {
  74. // path是指欲下载的文件的路径。
  75. File file = new File(path);
  76. // 取得文件名。
  77. String filename = file.getName();
  78. // 以流的形式下载文件。
  79. InputStream fis = new BufferedInputStream(new FileInputStream(path));
  80. byte[] buffer = new byte[fis.available()];
  81. fis.read(buffer);
  82. fis.close();
  83. // 清空response
  84. response.reset();
  85. // 设置response的Header
  86. response.addHeader("Content-Disposition", "attachment;filename="
  87. + new String(filename.getBytes(),"iso-8859-1"));
  88. response.addHeader("Content-Length", "" + file.length());
  89. OutputStream toClient = new BufferedOutputStream(
  90. response.getOutputStream());
  91. response.setContentType("application/octet-stream;charset=utf-8");
  92. toClient.write(buffer);
  93. toClient.flush();
  94. toClient.close();
  95. } catch (IOException ex) {
  96. ex.printStackTrace();
  97. }
  98. }
  99. //=========================================================此处分割线===========================================================================//
  100. /**
  101. * 导出部门流水数据
  102. * @param map 数据
  103. * @param comment 表头(列名)
  104. * @param filePath 生成文件路径
  105. */
  106. public static XSSFWorkbook exportDeptBillInfo(Map<String,List<TOrderBillNew>> map,String[] comment,String filePath){
  107. //map 数据 ,comment表头 (列名称)
  108. //1,创建一个workbook,对应的是一个excel文件
  109. XSSFWorkbook wb = new XSSFWorkbook();
  110. int count = 0;
  111. //遍历map
  112. for (Map.Entry<String,List<TOrderBillNew>> entry : map.entrySet()) {
  113. //2.创建一个sheet
  114. XSSFSheet sheet = wb.createSheet(entry.getKey());
  115. //3。创建一个row
  116. XSSFRow row = sheet.createRow(0);
  117. //4.创建一个cell
  118. XSSFCell cell = row.createCell(0);
  119. cell.setCellValue(entry.getKey()+" 流水记录");
  120. count ++;
  121. XSSFCellStyle style1 = setCellStyle1(wb);
  122. cell.setCellStyle(style1);
  123. //合并列
  124. sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, comment.length-1));//合并单元格
  125. //创建表头
  126. XSSFRow row2 = sheet.createRow(1);
  127. for(int i = 0; i < comment.length; i++){
  128. sheet.setColumnWidth(i, 3000);//设置列宽
  129. XSSFCell c = row2.createCell(i);
  130. c.setCellValue(comment[i]);
  131. c.setCellStyle(style1);
  132. }
  133. //填充表格数据
  134. //int rowmnum = sheet.getLastRowNum();//获得当前行数据
  135. List<TOrderBillNew> list = entry.getValue();
  136. if(list == null){
  137. continue;
  138. }
  139. for (TOrderBillNew ob : list) {
  140. //新增一行
  141. XSSFRow r = sheet.createRow(sheet.getLastRowNum()+1);
  142. for (int i = 0; i < comment.length; i++) {
  143. //当前列的只
  144. String o ;
  145. switch (comment[i]) {
  146. case "平台流水编号": o = ob.getBillNo(); break;
  147. case "平台流水时间": o = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(ob.getCreateTime()); break;
  148. case "交易金额": o = ob.getTransactionAmount().toString() + "万元"; break;
  149. case "付款方": o = ob.getPayerName(); break;
  150. case "收款方": o = ob.getPayeeName(); break;
  151. case "流水科目":
  152. if(0 == ob.getTransactionSubject()){
  153. o = "代收";
  154. }else if(1 == ob.getTransactionSubject()){
  155. o = "代付";
  156. }else if(2 == ob.getTransactionSubject()){
  157. o = "退款";
  158. }else{
  159. o = "未定义";
  160. }
  161. break;
  162. case "交易渠道":
  163. if(0 == ob.getTransactionChannel()){
  164. o = "微信";
  165. }else if(1 == ob.getTransactionChannel()){
  166. o = "支付宝";
  167. }else if(2 == ob.getTransactionChannel()){
  168. o = "银联";
  169. }else{
  170. o = "未定义";
  171. }
  172. break;
  173. case "交易时间": o = ob.getFinancialPayTimes(); break;
  174. case "财务流水号": o = ob.getFinancialPayNo();break;
  175. case "订单编号": o = ob.getOrderNo(); break;
  176. case "订单负责人": o = ob.getSaleName(); break;
  177. default: o = ""; break;
  178. }
  179. XSSFCell c = r.createCell(i);
  180. c.setCellValue(o);
  181. }
  182. }
  183. }
  184. return wb;
  185. /*OutputStream os = new ByteArrayOutputStream();
  186. try {
  187. wb.write(os);
  188. } catch (IOException e1) {
  189. // TODO Auto-generated catch block
  190. e1.printStackTrace();
  191. }
  192. //将表格数据放入文件中
  193. try {
  194. FileOutputStream fos = new FileOutputStream(filePath);
  195. wb.write(fos);
  196. fos.close();
  197. System.out.println("已经创建文件");
  198. } catch (Exception e) {
  199. System.out.println("发生了异常");
  200. e.printStackTrace();
  201. }*/
  202. }
  203. /**
  204. * 导出技术员奖金统计
  205. * @param map
  206. * @param comment
  207. * @return
  208. */
  209. public static XSSFWorkbook exportDeptBonusInfoT(Map<String,List<TOrderBonusBo>> map,String[] comment){
  210. //map 数据 ,comment表头 (列名称)
  211. //1,创建一个workbook,对应的是一个excel文件
  212. XSSFWorkbook wb = new XSSFWorkbook();
  213. int count = 0;
  214. //遍历map
  215. for (Map.Entry<String,List<TOrderBonusBo>> entry : map.entrySet()) {
  216. //2.创建一个sheet
  217. XSSFSheet sheet = wb.createSheet(entry.getKey());
  218. //3。创建一个row
  219. XSSFRow row = sheet.createRow(0);
  220. //4.创建一个cell
  221. XSSFCell cell = row.createCell(0);
  222. cell.setCellValue(entry.getKey()+" 奖金记录");
  223. count ++;
  224. XSSFCellStyle style1 = setCellStyle1(wb);
  225. cell.setCellStyle(style1);
  226. //合并列
  227. sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, comment.length-1));//合并单元格
  228. //创建表头
  229. XSSFRow row2 = sheet.createRow(1);
  230. for(int i = 0; i < comment.length; i++){
  231. sheet.setColumnWidth(i, 3000);//设置列宽
  232. XSSFCell c = row2.createCell(i);
  233. c.setCellValue(comment[i]);
  234. c.setCellStyle(style1);
  235. }
  236. //填充表格数据
  237. //int rowmnum = sheet.getLastRowNum();//获得当前行数据
  238. List<TOrderBonusBo> list = entry.getValue();
  239. if(list == null){
  240. continue;
  241. }
  242. for (TOrderBonusBo ob : list) {
  243. //新增一行
  244. XSSFRow r = sheet.createRow(sheet.getLastRowNum()+1);
  245. for (int i = 0; i < comment.length; i++) {
  246. //当前列的只
  247. String o = "";
  248. switch (comment[i]) {
  249. case "任务编号": o = ob.getTid().toString(); break;
  250. case "任务名称": o = ob.getCommodityName(); break;
  251. case "订单编号": o = ob.getOrderNo(); break;
  252. case "任务负责人": o = ob.getSalesName(); break;
  253. case "任务状态": o = TaskState.getValueByCode(ob.getTaskStatus()); break;
  254. case "任务数量": o = ob.getCommodityQuantity().toString(); break;
  255. case "项目状态": o = ProjectNewStage.getValueByCode(ob.getProjectStatus()); break;
  256. case "受理日期": o = ob.getAcceptTime(); break;
  257. case "评审日期": o = ob.getReviewTime(); break;
  258. case "下证日期": o = ob.getLicenceTime();break;
  259. case "订单结算状态": o = LiquidationNewState.getValueByCode(ob.getLiquidationStatus()); break;
  260. case "发放类型": o = BonusSubject.getValueByCode(ob.getBonusSubject()); break;
  261. case "发放状态":
  262. if(0 == ob.getGrantStatus()){
  263. o = "未发放";
  264. }else if(1 == ob.getGrantStatus()){
  265. o = "已发放";
  266. }
  267. break;
  268. default: o = ""; break;
  269. }
  270. XSSFCell c = r.createCell(i);
  271. c.setCellValue(o);
  272. }
  273. }
  274. }
  275. return wb;
  276. /*OutputStream os = new ByteArrayOutputStream();
  277. try {
  278. wb.write(os);
  279. } catch (IOException e1) {
  280. // TODO Auto-generated catch block
  281. e1.printStackTrace();
  282. }
  283. //将表格数据放入文件中
  284. try {
  285. FileOutputStream fos = new FileOutputStream(filePath);
  286. wb.write(fos);
  287. fos.close();
  288. System.out.println("已经创建文件");
  289. } catch (Exception e) {
  290. System.out.println("发生了异常");
  291. e.printStackTrace();
  292. }*/
  293. }
  294. /**
  295. * 导出订单数据
  296. * @param list
  297. * @param date
  298. * @return
  299. */
  300. public static XSSFWorkbook exportOrderInfo(List<OrderReportBo> list,String date){
  301. //list 数据 ,comment表头 (列名称)
  302. String[] comment = {"序号","订单部门","订单类型","签单人员","签单数量","累计签单金额(万元)"};
  303. //1,创建一个workbook,对应的是一个excel文件
  304. XSSFWorkbook wb = new XSSFWorkbook();
  305. //遍历map
  306. //2.创建一个sheet
  307. XSSFSheet sheet = wb.createSheet(date + "订单统计");
  308. //3。创建一个row
  309. XSSFRow row = sheet.createRow(0);
  310. //4.创建一个cell
  311. XSSFCell cell = row.createCell(0);
  312. cell.setCellValue(date + "订单统计");
  313. XSSFCellStyle style1 = setCellStyle1(wb);
  314. cell.setCellStyle(style1);
  315. //合并列
  316. sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, comment.length-1));//合并单元格
  317. //创建表头
  318. XSSFRow row2 = sheet.createRow(1);
  319. for(int i = 0; i < comment.length; i++){
  320. sheet.setColumnWidth(i, 3000);//设置列宽
  321. XSSFCell c = row2.createCell(i);
  322. c.setCellValue(comment[i]);
  323. c.setCellStyle(style1);
  324. }
  325. //填充表格数据
  326. //int rowmnum = sheet.getLastRowNum();//获得当前行数据
  327. for (OrderReportBo ob : list) {
  328. //新增一行
  329. XSSFRow r = sheet.createRow(sheet.getLastRowNum()+1);
  330. for (int i = 0; i < comment.length; i++) {
  331. //当前列的只
  332. String o = "";
  333. switch (comment[i]) {
  334. case "序号": o = ob.getId().toString(); break;
  335. case "订单部门": o = ob.getDeptName(); break;
  336. case "订单类型": o = (ob.getType() == 1 ? "科技项目" : "认证项目"); break;
  337. case "签单人员": o = ob.getSaleName(); break;
  338. case "签单数量": o = ob.getProofCount().toString(); break;
  339. case "累计签单金额(万元)": o =ob.getAmount().toString(); break;
  340. default: o = ""; break;
  341. }
  342. XSSFCell c = r.createCell(i);
  343. c.setCellValue(o);
  344. }
  345. }
  346. return wb;
  347. /*OutputStream os = new ByteArrayOutputStream();
  348. try {
  349. wb.write(os);
  350. } catch (IOException e1) {
  351. // TODO Auto-generated catch block
  352. e1.printStackTrace();
  353. }
  354. //将表格数据放入文件中
  355. try {
  356. FileOutputStream fos = new FileOutputStream(filePath);
  357. wb.write(fos);
  358. fos.close();
  359. System.out.println("已经创建文件");
  360. } catch (Exception e) {
  361. System.out.println("发生了异常");
  362. e.printStackTrace();
  363. }*/
  364. }
  365. public static XSSFWorkbook exportOrderDunData(List<TOrderNewBo> list,String[] comment,String filePath){
  366. //map 数据 comment表头 (列名称)
  367. //1,创建一个workbook,对应的是一个excel文件
  368. XSSFWorkbook wb = new XSSFWorkbook();
  369. //2.创建一个sheet
  370. XSSFSheet sheet = wb.createSheet();
  371. //创建表头
  372. XSSFCellStyle style1 = setCellStyle1(wb);
  373. XSSFRow row = sheet.createRow(0);
  374. for(int i = 0; i < comment.length; i++){
  375. sheet.setColumnWidth(i, 3000);//设置列宽
  376. XSSFCell c = row.createCell(i);
  377. c.setCellValue(comment[i]);
  378. c.setCellStyle(style1);
  379. }
  380. for (TOrderNewBo ob : list) {
  381. //新增一行
  382. XSSFRow r = sheet.createRow(sheet.getLastRowNum()+1);
  383. for (int i = 0; i < comment.length; i++) {
  384. //当前列的只
  385. String o ;
  386. switch (comment[i]) {
  387. //"订单编号","签单时间","客户名称","订单状态","签单金额","项目状态","结算状态","已收款","订单负责人","催收科目","催款启动日期"
  388. case "订单编号": o = ob.getOrderNo(); break;
  389. case "签单时间": o = ob.getSignDate(); break;
  390. case "客户名称": o = ob.getUserName(); break;
  391. case "订单状态": o = OrderNewState.getValueByCode(ob.getOrderStatus());break;
  392. case "签单金额": o = ob.getTotalAmount().toString(); break;
  393. case "项目状态": o = ProjectNewStage.getValueByCode(ob.getProjectStatus()); break;
  394. case "结算状态": o = LiquidationNewState.getValueByCode(ob.getLiquidationStatus()); break;
  395. case "已收款": o = ob.getSettlementAmount().toString(); break;
  396. case "订单负责人": o =ob.getSalesmanName(); break;
  397. case "催收科目": o = getDunSuBubject(ob.getDunSubject()); break;
  398. case "催款启动日期": o = ob.getStartDate(); break;
  399. default: o = ""; break;
  400. }
  401. XSSFCell c = r.createCell(i);
  402. c.setCellValue(o);
  403. }
  404. }
  405. return wb;
  406. }
  407. private static String getDunSuBubject(String dunSubject) {
  408. if ("12".equals(dunSubject)) return "订单审核通过";
  409. if ("20".equals(dunSubject)) return "项目待提交";
  410. if ("21".equals(dunSubject)) return "项目已提交";
  411. if ("22".equals(dunSubject)) return "项目评审";
  412. if ("23".equals(dunSubject)) return "项目立项";
  413. if ("24".equals(dunSubject)) return "项目公示";
  414. if ("25".equals(dunSubject)) return "项目抽查";
  415. if ("26".equals(dunSubject)) return "项目备案";
  416. if ("27".equals(dunSubject)) return "项目下证";
  417. if ("28".equals(dunSubject)) return "项目验收";
  418. if ("29".equals(dunSubject)) return "项目拨款";
  419. return "";
  420. }
  421. public static XSSFWorkbook exportTaskHoursListData(List<TOrderTaskListBo> list, String[] comment, String filePath) {
  422. //map 数据 comment表头 (列名称)
  423. //1,创建一个workbook,对应的是一个excel文件
  424. XSSFWorkbook wb = new XSSFWorkbook();
  425. //2.创建一个sheet
  426. XSSFSheet sheet = wb.createSheet();
  427. //创建表头
  428. XSSFCellStyle style1 = setCellStyle1(wb);
  429. XSSFRow row = sheet.createRow(0);
  430. for(int i = 0; i < comment.length; i++){
  431. sheet.setColumnWidth(i, 3000);//设置列宽
  432. XSSFCell c = row.createCell(i);
  433. c.setCellValue(comment[i]);
  434. c.setCellStyle(style1);
  435. }
  436. for (TOrderTaskListBo ob : list) {
  437. //新增一行
  438. XSSFRow r = sheet.createRow(sheet.getLastRowNum()+1);
  439. for (int i = 0; i < comment.length; i++) {
  440. //当前列的只
  441. String o ;
  442. switch (comment[i]) {
  443. //"任务编号","任务名称","订单编号","业务别类","客户名称","任务负责人","任务状态","工时"
  444. case "任务编号": o = ob.getId(); break;
  445. case "任务名称": o = ob.getTaskName(); break;
  446. case "订单编号": o = ob.getOrderNo(); break;
  447. case "业务别类": o = ob.getCname();break;
  448. case "客户名称": o = ob.getUserName(); break;
  449. case "任务负责人": o = ob.getReceiverName(); break;
  450. case "任务状态": o = TaskState.getValueByCode(ob.getTaskStatus()); break;
  451. case "工时": o = ob.getHours(); break;
  452. default: o = ""; break;
  453. }
  454. XSSFCell c = r.createCell(i);
  455. c.setCellValue(o);
  456. }
  457. }
  458. return wb;
  459. }
  460. /**
  461. * 年度导出数据模板
  462. * @return
  463. */
  464. public static XSSFWorkbook downOrderModelExcel() {
  465. String[] comment = {"姓名","电话","月份","项目类型","签单数量","累计签单金额(万元)"};
  466. String[] d1 = {"张三","13275161283","1","科技项目","2","12"};
  467. String[] d2 = {"张三","13275161283","1","认证项目","0.3","3"};
  468. //map 数据 comment表头 (列名称)
  469. //1,创建一个workbook,对应的是一个excel文件
  470. XSSFWorkbook wb = new XSSFWorkbook();
  471. //2.创建一个sheet
  472. XSSFSheet sheet = wb.createSheet();
  473. //创建表头
  474. XSSFCellStyle style1 = setCellStyle1(wb);
  475. XSSFRow row = sheet.createRow(0);
  476. XSSFRow row1 = sheet.createRow(1);
  477. XSSFRow row2 = sheet.createRow(2);
  478. for(int i = 0; i < comment.length; i++){
  479. sheet.setColumnWidth(i, 3000);//设置列宽
  480. XSSFCell c = row.createCell(i);
  481. c.setCellValue(comment[i]);
  482. c.setCellStyle(style1);
  483. }
  484. for(int i = 0; i < d1.length; i++){
  485. XSSFCell c = row1.createCell(i);
  486. c.setCellValue(d1[i]);
  487. c.setCellStyle(style1);
  488. }
  489. for(int i = 0; i < d2.length; i++){
  490. XSSFCell c = row2.createCell(i);
  491. c.setCellValue(d2[i]);
  492. c.setCellStyle(style1);
  493. }
  494. return wb;
  495. }
  496. /**
  497. * 导出营销部部门奖金的数据
  498. * @param map
  499. * @return
  500. */
  501. public static XSSFWorkbook exportDeptBonusInfoS(Map<String,List<TOrderBonusBo>> map){
  502. String comment[] = {"订单编号", "订单负责人", "签单日期", "客户名称", "签单金额(万元)", "首付款(万元)", "已收(万元)","已退(万元)","订单状态","项目状态","发放类型",
  503. "校对后单量(个)","发放状态","当月认证项目数量","当月科技项目数量","年度认证项目数量","年度科技项目数量"};
  504. //map 数据 ,comment表头 (列名称)
  505. //1,创建一个workbook,对应的是一个excel文件
  506. XSSFWorkbook wb = new XSSFWorkbook();
  507. int count = 0;
  508. //遍历map
  509. for (Map.Entry<String,List<TOrderBonusBo>> entry : map.entrySet()) {
  510. //2.创建一个sheet
  511. XSSFSheet sheet = wb.createSheet(entry.getKey());
  512. //3。创建一个row
  513. XSSFRow row = sheet.createRow(0);
  514. //4.创建一个cell
  515. XSSFCell cell = row.createCell(0);
  516. cell.setCellValue(entry.getKey()+" 奖金记录");
  517. count ++;
  518. XSSFCellStyle style1 = setCellStyle1(wb);
  519. cell.setCellStyle(style1);
  520. //合并列
  521. sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, comment.length-1));//合并单元格
  522. //创建表头
  523. XSSFRow row2 = sheet.createRow(1);
  524. for(int i = 0; i < comment.length; i++){
  525. sheet.setColumnWidth(i, 3000);//设置列宽
  526. XSSFCell c = row2.createCell(i);
  527. c.setCellValue(comment[i]);
  528. c.setCellStyle(style1);
  529. }
  530. //填充表格数据
  531. //int rowmnum = sheet.getLastRowNum();//获得当前行数据
  532. List<TOrderBonusBo> list = entry.getValue();
  533. if(list == null){
  534. continue;
  535. }
  536. for (TOrderBonusBo ob : list) {
  537. //新增一行
  538. // String comment[] = {"订单编号", "订单负责人", "签单日期", "客户名称", "签单金额", "首付款", "已收","已退","订单状态","项目状态","发放类型",
  539. // "校对后单量","发放状态","当月认证项目数量","当月科技项目数量","年度认证项目数量","年度科技项目数量"};
  540. XSSFRow r = sheet.createRow(sheet.getLastRowNum()+1);
  541. r.createCell(0).setCellValue(ob.getOrderNo());
  542. r.createCell(1).setCellValue(ob.getSalesName());
  543. r.createCell(2).setCellValue(ob.getSignTime());
  544. r.createCell(3).setCellValue(ob.getBuyName());
  545. r.createCell(4).setCellValue(ob.getTotalAmount().toString());
  546. r.createCell(5).setCellValue(ob.getFirstAmount().toString());
  547. r.createCell(6).setCellValue(ob.getSettlementAmount().toString());
  548. r.createCell(7).setCellValue(ob.getRefundAmount().toString());
  549. r.createCell(8).setCellValue(OrderNewState.getValueByCode(ob.getOrderStatus()));
  550. r.createCell(9).setCellValue(ProjectNewStage.getValueByCode(ob.getProjectStatus()));
  551. r.createCell(10).setCellValue(BonusSubject.getValueByCode(ob.getBonusSubject()));
  552. r.createCell(11).setCellValue(ob.getProofCount().toString());
  553. r.createCell(12).setCellValue(ob.getGrantStatus() == 0? "未发放":"已发放");
  554. r.createCell(13).setCellValue(ob.getRenzm());
  555. r.createCell(14).setCellValue(ob.getKejim());
  556. r.createCell(15).setCellValue(ob.getRenzy());
  557. r.createCell(16).setCellValue(ob.getRenzy());
  558. }
  559. }
  560. return wb;
  561. }
  562. }