ExportExcelUtil.java 24 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655
  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. 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.getAccountsReceivable().toString(); break;
  396. case "已收款(万元)": o = ob.getSettlementAmount().toString(); break;
  397. case "未收款(万元)": o = ob.getUncollectedAmount().toString(); break;
  398. case "订单部门": o =ob.getDepName(); break;
  399. case "订单负责人": o =ob.getSalesmanName(); break;
  400. case "催收科目": o = getDunSuBubject(ob.getDunSubject()); break;
  401. case "催款启动日期": o = ob.getStartDate(); break;
  402. default: o = ""; break;
  403. }
  404. XSSFCell c = r.createCell(i);
  405. c.setCellValue(o);
  406. }
  407. }
  408. return wb;
  409. }
  410. private static String getDunSuBubject(String dunSubject) {
  411. if ("12".equals(dunSubject)) return "订单审核通过";
  412. if ("20".equals(dunSubject)) return "项目待提交";
  413. if ("21".equals(dunSubject)) return "项目已提交";
  414. if ("22".equals(dunSubject)) return "项目评审";
  415. if ("23".equals(dunSubject)) return "项目立项";
  416. if ("24".equals(dunSubject)) return "项目公示";
  417. if ("25".equals(dunSubject)) return "项目抽查";
  418. if ("26".equals(dunSubject)) return "项目备案";
  419. if ("27".equals(dunSubject)) return "项目下证";
  420. if ("28".equals(dunSubject)) return "项目验收";
  421. if ("29".equals(dunSubject)) return "项目拨款";
  422. return "";
  423. }
  424. public static XSSFWorkbook exportTaskHoursListData(List<TOrderTaskListBo> list, String[] comment, String filePath) {
  425. //map 数据 comment表头 (列名称)
  426. //1,创建一个workbook,对应的是一个excel文件
  427. XSSFWorkbook wb = new XSSFWorkbook();
  428. //2.创建一个sheet
  429. XSSFSheet sheet = wb.createSheet();
  430. //创建表头
  431. XSSFCellStyle style1 = setCellStyle1(wb);
  432. XSSFRow row = sheet.createRow(0);
  433. for(int i = 0; i < comment.length; i++){
  434. sheet.setColumnWidth(i, 3000);//设置列宽
  435. XSSFCell c = row.createCell(i);
  436. c.setCellValue(comment[i]);
  437. c.setCellStyle(style1);
  438. }
  439. for (TOrderTaskListBo ob : list) {
  440. //新增一行
  441. XSSFRow r = sheet.createRow(sheet.getLastRowNum()+1);
  442. for (int i = 0; i < comment.length; i++) {
  443. //当前列的只
  444. String o ;
  445. switch (comment[i]) {
  446. //"任务编号","任务名称","订单编号","业务别类","客户名称","任务负责人","任务状态","工时"
  447. case "任务编号": o = ob.getId(); break;
  448. case "任务名称": o = ob.getTaskName(); break;
  449. case "订单编号": o = ob.getOrderNo(); break;
  450. case "业务别类": o = ob.getCname();break;
  451. case "订单部门": o = ob.getDepName();break;
  452. case "客户名称": o = ob.getUserName(); break;
  453. case "任务负责人": o = ob.getReceiverName(); break;
  454. case "任务状态": o = TaskState.getValueByCode(ob.getTaskStatus()); break;
  455. case "工时": o = ob.getHours(); break;
  456. case "合同编号":o =ob.getContractNo();break;
  457. case "签单日期":o =ob.getSignTime();break;
  458. case "派单日期":o =ob.getCreteTime();break;
  459. default: o = ""; break;
  460. }
  461. XSSFCell c = r.createCell(i);
  462. c.setCellValue(o);
  463. }
  464. }
  465. return wb;
  466. }
  467. public static XSSFWorkbook exportSelectMyTaskList(List<TOrderTaskListBo> list, String[] comment, String filePath) {
  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. for(int i = 0; i < comment.length; i++){
  477. sheet.autoSizeColumn(i);
  478. sheet.autoSizeColumn(i, true);
  479. sheet.setColumnWidth(i, 3000);//设置列宽
  480. XSSFCell c = row.createCell(i);
  481. c.setCellValue(comment[i]);
  482. c.setCellStyle(style1);
  483. }
  484. for (TOrderTaskListBo ob : list) {
  485. //新增一行
  486. XSSFRow r = sheet.createRow(sheet.getLastRowNum()+1);
  487. for (int i = 0; i < comment.length; i++) {
  488. //当前列的只
  489. String o="" ;
  490. switch (comment[i]) {
  491. //"任务编号","任务名称","订单编号","业务别类","客户名称","任务状态","项目状态","分配时间","订单部门"
  492. case "任务编号": o = ob.getId(); setWidth(sheet, i, o,1); break;
  493. case "任务名称": o = ob.getTaskName(); setWidth(sheet, i, o,2);break;
  494. case "订单编号": o = ob.getOrderNo();setWidth(sheet, i, o,1); break;
  495. case "业务别类": o = ob.getCname();setWidth(sheet, i, o,2);break;
  496. case "客户名称(软著数/下证数)":
  497. if(null!=ob.getTaskName()&&ob.getTaskName().contains("软著")){
  498. o =ob.getUserName()+"("+ob.getCommodityQuantity()+"/"+ob.getAlreadyNumber()+")";
  499. }else {
  500. o = ob.getUserName();
  501. }
  502. setWidth(sheet, i, o,2);break;
  503. case "任务状态": o = TaskState.getValueByCode(ob.getTaskStatus()); setWidth(sheet, i, o,2);break;
  504. case "项目状态": o = ProjectNewStage.getValueByCode(ob.getProjectStatus()); setWidth(sheet, i, o,1);break;
  505. case "分配时间": o = ob.getTaskDate();setWidth(sheet, i, o,1); break;
  506. case "订单部门": o = ob.getDepName();setWidth(sheet, i, o,2);break;
  507. default: o = ""; break;
  508. }
  509. XSSFCell c = r.createCell(i);
  510. c.setCellValue(o);
  511. }
  512. }
  513. return wb;
  514. }
  515. private static void setWidth(XSSFSheet sheet, int i, String o,int x) {
  516. if (StringUtils.isNotBlank(o)) {
  517. sheet.setColumnWidth(i, o.getBytes().length*x*280);
  518. }
  519. }
  520. /**
  521. * 年度导出数据模板
  522. * @return
  523. */
  524. public static XSSFWorkbook downOrderModelExcel() {
  525. String[] comment = {"姓名","电话","月份","项目类型","签单数量","累计签单金额(万元)"};
  526. String[] d1 = {"张三","13275161283","1","科技项目","2","12"};
  527. String[] d2 = {"张三","13275161283","1","认证项目","0.3","3"};
  528. //map 数据 comment表头 (列名称)
  529. //1,创建一个workbook,对应的是一个excel文件
  530. XSSFWorkbook wb = new XSSFWorkbook();
  531. //2.创建一个sheet
  532. XSSFSheet sheet = wb.createSheet();
  533. //创建表头
  534. XSSFCellStyle style1 = setCellStyle1(wb);
  535. XSSFRow row = sheet.createRow(0);
  536. XSSFRow row1 = sheet.createRow(1);
  537. XSSFRow row2 = sheet.createRow(2);
  538. for(int i = 0; i < comment.length; i++){
  539. sheet.setColumnWidth(i, 3000);//设置列宽
  540. XSSFCell c = row.createCell(i);
  541. c.setCellValue(comment[i]);
  542. c.setCellStyle(style1);
  543. }
  544. for(int i = 0; i < d1.length; i++){
  545. XSSFCell c = row1.createCell(i);
  546. c.setCellValue(d1[i]);
  547. c.setCellStyle(style1);
  548. }
  549. for(int i = 0; i < d2.length; i++){
  550. XSSFCell c = row2.createCell(i);
  551. c.setCellValue(d2[i]);
  552. c.setCellStyle(style1);
  553. }
  554. return wb;
  555. }
  556. /**
  557. * 导出营销部部门奖金的数据
  558. * @param map
  559. * @return
  560. */
  561. public static XSSFWorkbook exportDeptBonusInfoS(Map<String,List<TOrderBonusBo>> map){
  562. String comment[] = {"订单编号", "订单负责人", "签单日期", "客户名称", "签单金额(万元)", "首付款(万元)", "已收(万元)","已退(万元)","订单状态","项目状态","发放类型",
  563. "校对后单量(个)","发放状态","当月认证项目数量","当月科技项目数量","年度认证项目数量","年度科技项目数量", "奖金创建时间"};
  564. //map 数据 ,comment表头 (列名称)
  565. //1,创建一个workbook,对应的是一个excel文件
  566. XSSFWorkbook wb = new XSSFWorkbook();
  567. int count = 0;
  568. //遍历map
  569. for (Map.Entry<String,List<TOrderBonusBo>> entry : map.entrySet()) {
  570. //2.创建一个sheet
  571. XSSFSheet sheet = wb.createSheet(entry.getKey());
  572. //3。创建一个row
  573. XSSFRow row = sheet.createRow(0);
  574. //4.创建一个cell
  575. XSSFCell cell = row.createCell(0);
  576. cell.setCellValue(entry.getKey()+" 奖金记录");
  577. count ++;
  578. XSSFCellStyle style1 = setCellStyle1(wb);
  579. cell.setCellStyle(style1);
  580. //合并列
  581. sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, comment.length-1));//合并单元格
  582. //创建表头
  583. XSSFRow row2 = sheet.createRow(1);
  584. for(int i = 0; i < comment.length; i++){
  585. sheet.setColumnWidth(i, 3000);//设置列宽
  586. XSSFCell c = row2.createCell(i);
  587. c.setCellValue(comment[i]);
  588. c.setCellStyle(style1);
  589. }
  590. //填充表格数据
  591. //int rowmnum = sheet.getLastRowNum();//获得当前行数据
  592. List<TOrderBonusBo> list = entry.getValue();
  593. if(list == null){
  594. continue;
  595. }
  596. for (TOrderBonusBo ob : list) {
  597. //新增一行
  598. // String comment[] = {"订单编号", "订单负责人", "签单日期", "客户名称", "签单金额", "首付款", "已收","已退","订单状态","项目状态","发放类型",
  599. // "校对后单量","发放状态","当月认证项目数量","当月科技项目数量","年度认证项目数量","年度科技项目数量"};
  600. XSSFRow r = sheet.createRow(sheet.getLastRowNum()+1);
  601. r.createCell(0).setCellValue(ob.getOrderNo());
  602. r.createCell(1).setCellValue(ob.getSalesName());
  603. r.createCell(2).setCellValue(ob.getSignTime());
  604. r.createCell(3).setCellValue(ob.getBuyName());
  605. r.createCell(4).setCellValue(ob.getTotalAmount().toString());
  606. r.createCell(5).setCellValue(ob.getFirstAmount().toString());
  607. r.createCell(6).setCellValue(ob.getSettlementAmount().toString());
  608. r.createCell(7).setCellValue(ob.getRefundAmount().toString());
  609. r.createCell(8).setCellValue(OrderNewState.getValueByCode(ob.getOrderStatus()));
  610. r.createCell(9).setCellValue(ProjectNewStage.getValueByCode(ob.getProjectStatus()));
  611. r.createCell(10).setCellValue(BonusSubject.getValueByCode(ob.getBonusSubject()));
  612. r.createCell(11).setCellValue(ob.getProofCount().toString());
  613. r.createCell(12).setCellValue(ob.getGrantStatus() == 0? "未发放":"已发放");
  614. r.createCell(13).setCellValue(ob.getRenzm());
  615. r.createCell(14).setCellValue(ob.getKejim());
  616. r.createCell(15).setCellValue(ob.getRenzy());
  617. r.createCell(16).setCellValue(ob.getRenzy());
  618. r.createCell(17).setCellValue(ob.getStartTime1());
  619. }
  620. }
  621. return wb;
  622. }
  623. }