ExportExcelUtil.java 22 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591
  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.getTargetName(); 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. case "奖金创建时间": o = ob.getStartTime1(); break;
  269. default: o = ""; break;
  270. }
  271. XSSFCell c = r.createCell(i);
  272. c.setCellValue(o);
  273. }
  274. }
  275. }
  276. return wb;
  277. /*OutputStream os = new ByteArrayOutputStream();
  278. try {
  279. wb.write(os);
  280. } catch (IOException e1) {
  281. // TODO Auto-generated catch block
  282. e1.printStackTrace();
  283. }
  284. //将表格数据放入文件中
  285. try {
  286. FileOutputStream fos = new FileOutputStream(filePath);
  287. wb.write(fos);
  288. fos.close();
  289. System.out.println("已经创建文件");
  290. } catch (Exception e) {
  291. System.out.println("发生了异常");
  292. e.printStackTrace();
  293. }*/
  294. }
  295. /**
  296. * 导出订单数据
  297. * @param list
  298. * @param date
  299. * @return
  300. */
  301. public static XSSFWorkbook exportOrderInfo(List<OrderReportBo> list,String date){
  302. //list 数据 ,comment表头 (列名称)
  303. String[] comment = {"序号","订单部门","订单类型","签单人员","签单数量","累计签单金额(万元)"};
  304. //1,创建一个workbook,对应的是一个excel文件
  305. XSSFWorkbook wb = new XSSFWorkbook();
  306. //遍历map
  307. //2.创建一个sheet
  308. XSSFSheet sheet = wb.createSheet(date + "订单统计");
  309. //3。创建一个row
  310. XSSFRow row = sheet.createRow(0);
  311. //4.创建一个cell
  312. XSSFCell cell = row.createCell(0);
  313. cell.setCellValue(date + "订单统计");
  314. XSSFCellStyle style1 = setCellStyle1(wb);
  315. cell.setCellStyle(style1);
  316. //合并列
  317. sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, comment.length-1));//合并单元格
  318. //创建表头
  319. XSSFRow row2 = sheet.createRow(1);
  320. for(int i = 0; i < comment.length; i++){
  321. sheet.setColumnWidth(i, 3000);//设置列宽
  322. XSSFCell c = row2.createCell(i);
  323. c.setCellValue(comment[i]);
  324. c.setCellStyle(style1);
  325. }
  326. //填充表格数据
  327. //int rowmnum = sheet.getLastRowNum();//获得当前行数据
  328. for (OrderReportBo ob : list) {
  329. //新增一行
  330. XSSFRow r = sheet.createRow(sheet.getLastRowNum()+1);
  331. for (int i = 0; i < comment.length; i++) {
  332. //当前列的只
  333. String o = "";
  334. switch (comment[i]) {
  335. case "序号": o = ob.getId().toString(); break;
  336. case "订单部门": o = ob.getDeptName(); break;
  337. case "订单类型": o = (ob.getType() == 1 ? "科技项目" : "认证项目"); break;
  338. case "签单人员": o = ob.getSaleName(); break;
  339. case "签单数量": o = ob.getProofCount().toString(); break;
  340. case "累计签单金额(万元)": o =ob.getAmount().toString(); break;
  341. default: o = ""; break;
  342. }
  343. XSSFCell c = r.createCell(i);
  344. c.setCellValue(o);
  345. }
  346. }
  347. return wb;
  348. /*OutputStream os = new ByteArrayOutputStream();
  349. try {
  350. wb.write(os);
  351. } catch (IOException e1) {
  352. // TODO Auto-generated catch block
  353. e1.printStackTrace();
  354. }
  355. //将表格数据放入文件中
  356. try {
  357. FileOutputStream fos = new FileOutputStream(filePath);
  358. wb.write(fos);
  359. fos.close();
  360. System.out.println("已经创建文件");
  361. } catch (Exception e) {
  362. System.out.println("发生了异常");
  363. e.printStackTrace();
  364. }*/
  365. }
  366. public static XSSFWorkbook exportOrderDunData(List<TOrderNewBo> list,String[] comment,String filePath){
  367. //map 数据 comment表头 (列名称)
  368. //1,创建一个workbook,对应的是一个excel文件
  369. XSSFWorkbook wb = new XSSFWorkbook();
  370. //2.创建一个sheet
  371. XSSFSheet sheet = wb.createSheet();
  372. //创建表头
  373. XSSFCellStyle style1 = setCellStyle1(wb);
  374. XSSFRow row = sheet.createRow(0);
  375. for(int i = 0; i < comment.length; i++){
  376. sheet.setColumnWidth(i, 3000);//设置列宽
  377. XSSFCell c = row.createCell(i);
  378. c.setCellValue(comment[i]);
  379. c.setCellStyle(style1);
  380. }
  381. for (TOrderNewBo ob : list) {
  382. //新增一行
  383. XSSFRow r = sheet.createRow(sheet.getLastRowNum()+1);
  384. for (int i = 0; i < comment.length; i++) {
  385. //当前列的只
  386. String o ;
  387. switch (comment[i]) {
  388. //"订单编号","签单时间","客户名称","订单状态","签单金额","项目状态","结算状态","已收款","订单负责人","催收科目","催款启动日期"
  389. case "订单编号": o = ob.getOrderNo(); break;
  390. case "签单时间": o = ob.getSignDate(); break;
  391. case "客户名称": o = ob.getUserName(); break;
  392. case "订单状态": o = OrderNewState.getValueByCode(ob.getOrderStatus());break;
  393. case "签单金额(万元)": o = ob.getTotalAmount().toString(); break;
  394. case "项目状态": o = ProjectNewStage.getValueByCode(ob.getProjectStatus()); break;
  395. case "结算状态": o = LiquidationNewState.getValueByCode(ob.getLiquidationStatus()); break;
  396. case "已收款(万元)": o = ob.getSettlementAmount().toString(); break;
  397. case "订单负责人": o =ob.getSalesmanName(); break;
  398. case "催收科目": o = getDunSuBubject(ob.getDunSubject()); break;
  399. case "催款启动日期": o = ob.getStartDate(); break;
  400. default: o = ""; break;
  401. }
  402. XSSFCell c = r.createCell(i);
  403. c.setCellValue(o);
  404. }
  405. }
  406. return wb;
  407. }
  408. private static String getDunSuBubject(String dunSubject) {
  409. if ("12".equals(dunSubject)) return "订单审核通过";
  410. if ("20".equals(dunSubject)) return "项目待提交";
  411. if ("21".equals(dunSubject)) return "项目已提交";
  412. if ("22".equals(dunSubject)) return "项目评审";
  413. if ("23".equals(dunSubject)) return "项目立项";
  414. if ("24".equals(dunSubject)) return "项目公示";
  415. if ("25".equals(dunSubject)) return "项目抽查";
  416. if ("26".equals(dunSubject)) return "项目备案";
  417. if ("27".equals(dunSubject)) return "项目下证";
  418. if ("28".equals(dunSubject)) return "项目验收";
  419. if ("29".equals(dunSubject)) return "项目拨款";
  420. return "";
  421. }
  422. public static XSSFWorkbook exportTaskHoursListData(List<TOrderTaskListBo> list, String[] comment, String filePath) {
  423. //map 数据 comment表头 (列名称)
  424. //1,创建一个workbook,对应的是一个excel文件
  425. XSSFWorkbook wb = new XSSFWorkbook();
  426. //2.创建一个sheet
  427. XSSFSheet sheet = wb.createSheet();
  428. //创建表头
  429. XSSFCellStyle style1 = setCellStyle1(wb);
  430. XSSFRow row = sheet.createRow(0);
  431. for(int i = 0; i < comment.length; i++){
  432. sheet.setColumnWidth(i, 3000);//设置列宽
  433. XSSFCell c = row.createCell(i);
  434. c.setCellValue(comment[i]);
  435. c.setCellStyle(style1);
  436. }
  437. for (TOrderTaskListBo ob : list) {
  438. //新增一行
  439. XSSFRow r = sheet.createRow(sheet.getLastRowNum()+1);
  440. for (int i = 0; i < comment.length; i++) {
  441. //当前列的只
  442. String o ;
  443. switch (comment[i]) {
  444. //"任务编号","任务名称","订单编号","业务别类","客户名称","任务负责人","任务状态","工时"
  445. case "任务编号": o = ob.getId(); break;
  446. case "任务名称": o = ob.getTaskName(); break;
  447. case "订单编号": o = ob.getOrderNo(); break;
  448. case "业务别类": o = ob.getCname();break;
  449. case "客户名称": o = ob.getUserName(); break;
  450. case "任务负责人": o = ob.getReceiverName(); break;
  451. case "任务状态": o = TaskState.getValueByCode(ob.getTaskStatus()); break;
  452. case "工时": o = ob.getHours(); break;
  453. default: o = ""; break;
  454. }
  455. XSSFCell c = r.createCell(i);
  456. c.setCellValue(o);
  457. }
  458. }
  459. return wb;
  460. }
  461. /**
  462. * 年度导出数据模板
  463. * @return
  464. */
  465. public static XSSFWorkbook downOrderModelExcel() {
  466. String[] comment = {"姓名","电话","月份","项目类型","签单数量","累计签单金额(万元)"};
  467. String[] d1 = {"张三","13275161283","1","科技项目","2","12"};
  468. String[] d2 = {"张三","13275161283","1","认证项目","0.3","3"};
  469. //map 数据 comment表头 (列名称)
  470. //1,创建一个workbook,对应的是一个excel文件
  471. XSSFWorkbook wb = new XSSFWorkbook();
  472. //2.创建一个sheet
  473. XSSFSheet sheet = wb.createSheet();
  474. //创建表头
  475. XSSFCellStyle style1 = setCellStyle1(wb);
  476. XSSFRow row = sheet.createRow(0);
  477. XSSFRow row1 = sheet.createRow(1);
  478. XSSFRow row2 = sheet.createRow(2);
  479. for(int i = 0; i < comment.length; i++){
  480. sheet.setColumnWidth(i, 3000);//设置列宽
  481. XSSFCell c = row.createCell(i);
  482. c.setCellValue(comment[i]);
  483. c.setCellStyle(style1);
  484. }
  485. for(int i = 0; i < d1.length; i++){
  486. XSSFCell c = row1.createCell(i);
  487. c.setCellValue(d1[i]);
  488. c.setCellStyle(style1);
  489. }
  490. for(int i = 0; i < d2.length; i++){
  491. XSSFCell c = row2.createCell(i);
  492. c.setCellValue(d2[i]);
  493. c.setCellStyle(style1);
  494. }
  495. return wb;
  496. }
  497. /**
  498. * 导出营销部部门奖金的数据
  499. * @param map
  500. * @return
  501. */
  502. public static XSSFWorkbook exportDeptBonusInfoS(Map<String,List<TOrderBonusBo>> map){
  503. String comment[] = {"订单编号", "订单负责人", "签单日期", "客户名称", "签单金额(万元)", "首付款(万元)", "已收(万元)","已退(万元)","订单状态","项目状态","发放类型",
  504. "校对后单量(个)","发放状态","当月认证项目数量","当月科技项目数量","年度认证项目数量","年度科技项目数量", "奖金创建时间"};
  505. //map 数据 ,comment表头 (列名称)
  506. //1,创建一个workbook,对应的是一个excel文件
  507. XSSFWorkbook wb = new XSSFWorkbook();
  508. int count = 0;
  509. //遍历map
  510. for (Map.Entry<String,List<TOrderBonusBo>> entry : map.entrySet()) {
  511. //2.创建一个sheet
  512. XSSFSheet sheet = wb.createSheet(entry.getKey());
  513. //3。创建一个row
  514. XSSFRow row = sheet.createRow(0);
  515. //4.创建一个cell
  516. XSSFCell cell = row.createCell(0);
  517. cell.setCellValue(entry.getKey()+" 奖金记录");
  518. count ++;
  519. XSSFCellStyle style1 = setCellStyle1(wb);
  520. cell.setCellStyle(style1);
  521. //合并列
  522. sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, comment.length-1));//合并单元格
  523. //创建表头
  524. XSSFRow row2 = sheet.createRow(1);
  525. for(int i = 0; i < comment.length; i++){
  526. sheet.setColumnWidth(i, 3000);//设置列宽
  527. XSSFCell c = row2.createCell(i);
  528. c.setCellValue(comment[i]);
  529. c.setCellStyle(style1);
  530. }
  531. //填充表格数据
  532. //int rowmnum = sheet.getLastRowNum();//获得当前行数据
  533. List<TOrderBonusBo> list = entry.getValue();
  534. if(list == null){
  535. continue;
  536. }
  537. for (TOrderBonusBo ob : list) {
  538. //新增一行
  539. // String comment[] = {"订单编号", "订单负责人", "签单日期", "客户名称", "签单金额", "首付款", "已收","已退","订单状态","项目状态","发放类型",
  540. // "校对后单量","发放状态","当月认证项目数量","当月科技项目数量","年度认证项目数量","年度科技项目数量"};
  541. XSSFRow r = sheet.createRow(sheet.getLastRowNum()+1);
  542. r.createCell(0).setCellValue(ob.getOrderNo());
  543. r.createCell(1).setCellValue(ob.getSalesName());
  544. r.createCell(2).setCellValue(ob.getSignTime());
  545. r.createCell(3).setCellValue(ob.getBuyName());
  546. r.createCell(4).setCellValue(ob.getTotalAmount().toString());
  547. r.createCell(5).setCellValue(ob.getFirstAmount().toString());
  548. r.createCell(6).setCellValue(ob.getSettlementAmount().toString());
  549. r.createCell(7).setCellValue(ob.getRefundAmount().toString());
  550. r.createCell(8).setCellValue(OrderNewState.getValueByCode(ob.getOrderStatus()));
  551. r.createCell(9).setCellValue(ProjectNewStage.getValueByCode(ob.getProjectStatus()));
  552. r.createCell(10).setCellValue(BonusSubject.getValueByCode(ob.getBonusSubject()));
  553. r.createCell(11).setCellValue(ob.getProofCount().toString());
  554. r.createCell(12).setCellValue(ob.getGrantStatus() == 0? "未发放":"已发放");
  555. r.createCell(13).setCellValue(ob.getRenzm());
  556. r.createCell(14).setCellValue(ob.getKejim());
  557. r.createCell(15).setCellValue(ob.getRenzy());
  558. r.createCell(16).setCellValue(ob.getRenzy());
  559. r.createCell(17).setCellValue(ob.getStartTime1());
  560. }
  561. }
  562. return wb;
  563. }
  564. }