ExportExcelUtil.java 28 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748
  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.usermodel.CellStyle;
  14. import org.apache.poi.ss.usermodel.HorizontalAlignment;
  15. import org.apache.poi.ss.util.CellRangeAddress;
  16. import org.apache.poi.xssf.usermodel.XSSFCell;
  17. import org.apache.poi.xssf.usermodel.XSSFCellStyle;
  18. import org.apache.poi.xssf.usermodel.XSSFFont;
  19. import org.apache.poi.xssf.usermodel.XSSFRow;
  20. import org.apache.poi.xssf.usermodel.XSSFSheet;
  21. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  22. import com.goafanti.common.model.TOrderBillNew;
  23. import com.goafanti.customer.bo.CustomerListOut;
  24. import com.goafanti.order.bo.OrderReportBo;
  25. import com.goafanti.order.bo.TOrderBonusBo;
  26. import com.goafanti.order.bo.TOrderNewBo;
  27. import com.goafanti.order.bo.TOrderTaskListBo;
  28. import com.goafanti.order.enums.BonusSubject;
  29. import com.goafanti.order.enums.LiquidationNewState;
  30. import com.goafanti.order.enums.OrderNewState;
  31. import com.goafanti.order.enums.ProjectNewStage;
  32. import com.goafanti.techproject.enums.TaskState;
  33. public class ExportExcelUtil {
  34. //设置单元格格式
  35. public static XSSFCellStyle setCellStyle1(XSSFWorkbook workbook){
  36. // 设置字体
  37. XSSFFont font = workbook.createFont();
  38. // font.setColor(HSSFFont.COLOR_RED);
  39. font.setFontName("微软雅黑");
  40. font.setFontHeightInPoints((short) 22);//字体大小
  41. // 设置样式
  42. XSSFCellStyle cellStyle = workbook.createCellStyle();
  43. cellStyle.setFont(font); //字体
  44. // 设置单元格内容水平对其方式
  45. // XSSFCellStyle.ALIGN_CENTER 居中对齐
  46. // XSSFCellStyle.ALIGN_LEFT 左对齐
  47. // XSSFCellStyle.ALIGN_RIGHT 右对齐
  48. cellStyle.setAlignment(HorizontalAlignment.CENTER);
  49. //设置自动换行
  50. cellStyle.setWrapText(true);
  51. return cellStyle;
  52. }
  53. public static XSSFCellStyle setCellStyle2(XSSFWorkbook workbook){
  54. // 设置字体
  55. XSSFFont font = workbook.createFont();
  56. // font.setColor(HSSFFont.COLOR_RED);
  57. font.setFontName("微软雅黑");
  58. font.setFontHeightInPoints((short) 12);//字体大小
  59. // 设置样式
  60. XSSFCellStyle cellStyle = workbook.createCellStyle();
  61. cellStyle.setFont(font); //字体
  62. // 设置单元格内容水平对其方式
  63. // XSSFCellStyle.ALIGN_CENTER 居中对齐
  64. // XSSFCellStyle.ALIGN_LEFT 左对齐
  65. // XSSFCellStyle.ALIGN_RIGHT 右对齐
  66. cellStyle.setAlignment(HorizontalAlignment.CENTER);
  67. //设置自动换行
  68. cellStyle.setWrapText(true);
  69. return cellStyle;
  70. }
  71. //下载课表
  72. public static void download(String path, HttpServletResponse response,String filename) {
  73. try {
  74. // path是指欲下载的文件的路径。
  75. File file = new File(path);
  76. // 取得文件名。
  77. // 以流的形式下载文件。
  78. InputStream fis = new BufferedInputStream(new FileInputStream(path));
  79. byte[] buffer = new byte[fis.available()];
  80. fis.read(buffer);
  81. fis.close();
  82. // 清空response
  83. response.reset();
  84. // 设置response的Header
  85. response.addHeader("Content-Disposition", "attachment;filename="
  86. + new String(filename.getBytes(),"iso-8859-1")); //防止中文乱码或者中文忽略的情况
  87. response.addHeader("Content-Length", "" + file.length());
  88. OutputStream toClient = new BufferedOutputStream(
  89. response.getOutputStream());
  90. response.setContentType("application/octet-stream;charset=utf-8");
  91. toClient.write(buffer);
  92. toClient.flush();
  93. toClient.close();
  94. } catch (IOException ex) {
  95. ex.printStackTrace();
  96. }
  97. }
  98. public static void download(String path, HttpServletResponse response) {
  99. try {
  100. // path是指欲下载的文件的路径。
  101. File file = new File(path);
  102. // 取得文件名。
  103. String filename = file.getName();
  104. // 以流的形式下载文件。
  105. InputStream fis = new BufferedInputStream(new FileInputStream(path));
  106. byte[] buffer = new byte[fis.available()];
  107. fis.read(buffer);
  108. fis.close();
  109. // 清空response
  110. response.reset();
  111. // 设置response的Header
  112. response.addHeader("Content-Disposition", "attachment;filename="
  113. + new String(filename.getBytes(),"iso-8859-1"));
  114. response.addHeader("Content-Length", "" + file.length());
  115. OutputStream toClient = new BufferedOutputStream(
  116. response.getOutputStream());
  117. response.setContentType("application/octet-stream;charset=utf-8");
  118. toClient.write(buffer);
  119. toClient.flush();
  120. toClient.close();
  121. } catch (IOException ex) {
  122. ex.printStackTrace();
  123. }
  124. }
  125. //=========================================================此处分割线===========================================================================//
  126. /**
  127. * 导出部门流水数据
  128. * @param map 数据
  129. * @param comment 表头(列名)
  130. * @param filePath 生成文件路径
  131. */
  132. public static XSSFWorkbook exportDeptBillInfo(Map<String,List<TOrderBillNew>> map,String[] comment,String filePath){
  133. //map 数据 ,comment表头 (列名称)
  134. //1,创建一个workbook,对应的是一个excel文件
  135. XSSFWorkbook wb = new XSSFWorkbook();
  136. int count = 0;
  137. //遍历map
  138. for (Map.Entry<String,List<TOrderBillNew>> entry : map.entrySet()) {
  139. //2.创建一个sheet
  140. XSSFSheet sheet = wb.createSheet(entry.getKey());
  141. //3。创建一个row
  142. XSSFRow row = sheet.createRow(0);
  143. //4.创建一个cell
  144. XSSFCell cell = row.createCell(0);
  145. cell.setCellValue(entry.getKey()+" 流水记录");
  146. count ++;
  147. XSSFCellStyle style1 = setCellStyle1(wb);
  148. cell.setCellStyle(style1);
  149. //合并列
  150. sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, comment.length-1));//合并单元格
  151. //创建表头
  152. XSSFRow row2 = sheet.createRow(1);
  153. for(int i = 0; i < comment.length; i++){
  154. sheet.setColumnWidth(i, 3000);//设置列宽
  155. XSSFCell c = row2.createCell(i);
  156. c.setCellValue(comment[i]);
  157. c.setCellStyle(style1);
  158. }
  159. //填充表格数据
  160. //int rowmnum = sheet.getLastRowNum();//获得当前行数据
  161. List<TOrderBillNew> list = entry.getValue();
  162. if(list == null){
  163. continue;
  164. }
  165. for (TOrderBillNew ob : list) {
  166. //新增一行
  167. XSSFRow r = sheet.createRow(sheet.getLastRowNum()+1);
  168. for (int i = 0; i < comment.length; i++) {
  169. //当前列的只
  170. String o ;
  171. switch (comment[i]) {
  172. case "平台流水编号": o = ob.getBillNo(); break;
  173. case "平台流水时间": o = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(ob.getCreateTime()); break;
  174. case "交易金额(万元)": o = ob.getTransactionAmount().toString() ; break;
  175. case "付款方": o = ob.getPayerName(); break;
  176. case "收款方": o = ob.getPayeeName(); break;
  177. case "流水科目":
  178. if(0 == ob.getTransactionSubject()){
  179. o = "代收";
  180. }else if(1 == ob.getTransactionSubject()){
  181. o = "代付";
  182. }else if(2 == ob.getTransactionSubject()){
  183. o = "退款";
  184. }else{
  185. o = "未定义";
  186. }
  187. break;
  188. case "交易渠道":
  189. if(0 == ob.getTransactionChannel()){
  190. o = "微信";
  191. }else if(1 == ob.getTransactionChannel()){
  192. o = "支付宝";
  193. }else if(2 == ob.getTransactionChannel()){
  194. o = "银联";
  195. }else{
  196. o = "未定义";
  197. }
  198. break;
  199. case "交易时间": o = ob.getFinancialPayTimes(); break;
  200. case "财务流水号": o = ob.getFinancialPayNo();break;
  201. case "订单编号": o = ob.getOrderNo(); break;
  202. case "订单负责人": o = ob.getSaleName(); break;
  203. default: o = ""; break;
  204. }
  205. XSSFCell c = r.createCell(i);
  206. c.setCellValue(o);
  207. }
  208. }
  209. }
  210. return wb;
  211. /*OutputStream os = new ByteArrayOutputStream();
  212. try {
  213. wb.write(os);
  214. } catch (IOException e1) {
  215. // TODO Auto-generated catch block
  216. e1.printStackTrace();
  217. }
  218. //将表格数据放入文件中
  219. try {
  220. FileOutputStream fos = new FileOutputStream(filePath);
  221. wb.write(fos);
  222. fos.close();
  223. System.out.println("已经创建文件");
  224. } catch (Exception e) {
  225. System.out.println("发生了异常");
  226. e.printStackTrace();
  227. }*/
  228. }
  229. /**
  230. * 导出技术员奖金统计
  231. * @param map
  232. * @param comment
  233. * @return
  234. */
  235. public static XSSFWorkbook exportDeptBonusInfoT(Map<String,List<TOrderBonusBo>> map,String[] comment){
  236. //map 数据 ,comment表头 (列名称)
  237. //1,创建一个workbook,对应的是一个excel文件
  238. XSSFWorkbook wb = new XSSFWorkbook();
  239. int count = 0;
  240. //遍历map
  241. for (Map.Entry<String,List<TOrderBonusBo>> entry : map.entrySet()) {
  242. //2.创建一个sheet
  243. XSSFSheet sheet = wb.createSheet(entry.getKey());
  244. //3。创建一个row
  245. XSSFRow row = sheet.createRow(0);
  246. //4.创建一个cell
  247. XSSFCell cell = row.createCell(0);
  248. cell.setCellValue(entry.getKey()+" 奖金记录");
  249. count ++;
  250. XSSFCellStyle style1 = setCellStyle1(wb);
  251. cell.setCellStyle(style1);
  252. //合并列
  253. sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, comment.length-1));//合并单元格
  254. //创建表头
  255. XSSFRow row2 = sheet.createRow(1);
  256. for(int i = 0; i < comment.length; i++){
  257. sheet.setColumnWidth(i, 3000);//设置列宽
  258. XSSFCell c = row2.createCell(i);
  259. c.setCellValue(comment[i]);
  260. c.setCellStyle(style1);
  261. }
  262. //填充表格数据
  263. //int rowmnum = sheet.getLastRowNum();//获得当前行数据
  264. List<TOrderBonusBo> list = entry.getValue();
  265. if(list == null){
  266. continue;
  267. }
  268. for (TOrderBonusBo ob : list) {
  269. //新增一行
  270. XSSFRow r = sheet.createRow(sheet.getLastRowNum()+1);
  271. for (int i = 0; i < comment.length; i++) {
  272. //当前列的只
  273. String o = "";
  274. switch (comment[i]) {
  275. case "任务编号": o = ob.getTid().toString(); break;
  276. case "任务名称": o = ob.getCommodityName(); break;
  277. case "订单编号": o = ob.getOrderNo(); break;
  278. case "任务负责人": o = ob.getTargetName(); break;
  279. case "任务状态": o = TaskState.getValueByCode(ob.getTaskStatus()); break;
  280. case "任务数量": o = ob.getCommodityQuantity().toString(); break;
  281. case "项目状态": o = ProjectNewStage.getValueByCode(ob.getProjectStatus()); break;
  282. case "受理日期": o = ob.getAcceptTime(); break;
  283. case "评审日期": o = ob.getReviewTime(); break;
  284. case "下证日期": o = ob.getLicenceTime();break;
  285. case "订单结算状态": o = LiquidationNewState.getValueByCode(ob.getLiquidationStatus()); break;
  286. case "发放类型": o = BonusSubject.getValueByCode(ob.getBonusSubject()); break;
  287. case "发放状态":
  288. if(0 == ob.getGrantStatus()){
  289. o = "未发放";
  290. }else if(1 == ob.getGrantStatus()){
  291. o = "已发放";
  292. }
  293. break;
  294. case "奖金创建时间": o = ob.getStartTime1(); break;
  295. default: o = ""; break;
  296. }
  297. XSSFCell c = r.createCell(i);
  298. c.setCellValue(o);
  299. }
  300. }
  301. }
  302. return wb;
  303. /*OutputStream os = new ByteArrayOutputStream();
  304. try {
  305. wb.write(os);
  306. } catch (IOException e1) {
  307. // TODO Auto-generated catch block
  308. e1.printStackTrace();
  309. }
  310. //将表格数据放入文件中
  311. try {
  312. FileOutputStream fos = new FileOutputStream(filePath);
  313. wb.write(fos);
  314. fos.close();
  315. System.out.println("已经创建文件");
  316. } catch (Exception e) {
  317. System.out.println("发生了异常");
  318. e.printStackTrace();
  319. }*/
  320. }
  321. /**
  322. * 导出订单数据
  323. * @param list
  324. * @param date
  325. * @return
  326. */
  327. public static XSSFWorkbook exportOrderInfo(List<OrderReportBo> list,String date){
  328. //list 数据 ,comment表头 (列名称)
  329. String[] comment = {"序号","订单部门","订单类型","签单人员","签单数量","累计签单金额(万元)"};
  330. //1,创建一个workbook,对应的是一个excel文件
  331. XSSFWorkbook wb = new XSSFWorkbook();
  332. //遍历map
  333. //2.创建一个sheet
  334. XSSFSheet sheet = wb.createSheet(date + "订单统计");
  335. //3。创建一个row
  336. XSSFRow row = sheet.createRow(0);
  337. //4.创建一个cell
  338. XSSFCell cell = row.createCell(0);
  339. cell.setCellValue(date + "订单统计");
  340. XSSFCellStyle style1 = setCellStyle1(wb);
  341. cell.setCellStyle(style1);
  342. //合并列
  343. sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, comment.length-1));//合并单元格
  344. //创建表头
  345. XSSFRow row2 = sheet.createRow(1);
  346. for(int i = 0; i < comment.length; i++){
  347. sheet.setColumnWidth(i, 3000);//设置列宽
  348. XSSFCell c = row2.createCell(i);
  349. c.setCellValue(comment[i]);
  350. c.setCellStyle(style1);
  351. }
  352. //填充表格数据
  353. //int rowmnum = sheet.getLastRowNum();//获得当前行数据
  354. for (OrderReportBo ob : list) {
  355. //新增一行
  356. XSSFRow r = sheet.createRow(sheet.getLastRowNum()+1);
  357. for (int i = 0; i < comment.length; i++) {
  358. //当前列的只
  359. String o = "";
  360. switch (comment[i]) {
  361. case "序号": o = ob.getId().toString(); break;
  362. case "订单部门": o = ob.getDeptName(); break;
  363. case "订单类型": o = (ob.getType() == 1 ? "科技项目" : "认证项目"); break;
  364. case "签单人员": o = ob.getSaleName(); break;
  365. case "签单数量": o = ob.getProofCount().toString(); break;
  366. case "累计签单金额(万元)": o =ob.getAmount().toString(); break;
  367. default: o = ""; break;
  368. }
  369. XSSFCell c = r.createCell(i);
  370. c.setCellValue(o);
  371. }
  372. }
  373. return wb;
  374. /*OutputStream os = new ByteArrayOutputStream();
  375. try {
  376. wb.write(os);
  377. } catch (IOException e1) {
  378. // TODO Auto-generated catch block
  379. e1.printStackTrace();
  380. }
  381. //将表格数据放入文件中
  382. try {
  383. FileOutputStream fos = new FileOutputStream(filePath);
  384. wb.write(fos);
  385. fos.close();
  386. System.out.println("已经创建文件");
  387. } catch (Exception e) {
  388. System.out.println("发生了异常");
  389. e.printStackTrace();
  390. }*/
  391. }
  392. public static XSSFWorkbook exportOrderDunData(List<TOrderNewBo> list,String[] comment,String filePath){
  393. //map 数据 comment表头 (列名称)
  394. //1,创建一个workbook,对应的是一个excel文件
  395. XSSFWorkbook wb = new XSSFWorkbook();
  396. //2.创建一个sheet
  397. XSSFSheet sheet = wb.createSheet();
  398. //创建表头
  399. XSSFCellStyle style1 = setCellStyle1(wb);
  400. XSSFRow row = sheet.createRow(0);
  401. for(int i = 0; i < comment.length; i++){
  402. sheet.setColumnWidth(i, 3000);//设置列宽
  403. XSSFCell c = row.createCell(i);
  404. c.setCellValue(comment[i]);
  405. c.setCellStyle(style1);
  406. }
  407. for (TOrderNewBo ob : list) {
  408. //新增一行
  409. XSSFRow r = sheet.createRow(sheet.getLastRowNum()+1);
  410. for (int i = 0; i < comment.length; i++) {
  411. //当前列的只
  412. String o ;
  413. switch (comment[i]) {
  414. case "订单编号": o = ob.getOrderNo(); break;
  415. case "签单时间": o = ob.getSignDate(); break;
  416. case "客户名称": o = ob.getUserName(); break;
  417. case "订单状态": o = OrderNewState.getValueByCode(ob.getOrderStatus());break;
  418. case "签单金额(万元)": o = ob.getTotalAmount().toString(); break;
  419. case "项目状态": o = ProjectNewStage.getValueByCode(ob.getProjectStatus()); break;
  420. case "结算状态": o = LiquidationNewState.getValueByCode(ob.getLiquidationStatus()); break;
  421. case "应收款(万元)": o = ob.getAccountsReceivable().toString(); break;
  422. case "已收款(万元)": o = ob.getSettlementAmount().toString(); break;
  423. case "未收款(万元)": o = ob.getUncollectedAmount().toString(); break;
  424. case "订单部门": o =ob.getDepName(); break;
  425. case "订单负责人": o =ob.getSalesmanName(); break;
  426. case "催收科目": o = getDunSuBubject(ob.getDunSubject()); break;
  427. case "催款启动日期": o = ob.getStartDate(); break;
  428. default: o = ""; break;
  429. }
  430. XSSFCell c = r.createCell(i);
  431. c.setCellValue(o);
  432. }
  433. }
  434. return wb;
  435. }
  436. private static String getDunSuBubject(String dunSubject) {
  437. if ("12".equals(dunSubject)) return "订单审核通过";
  438. if ("20".equals(dunSubject)) return "项目待提交";
  439. if ("21".equals(dunSubject)) return "项目已提交";
  440. if ("22".equals(dunSubject)) return "项目评审";
  441. if ("23".equals(dunSubject)) return "项目立项";
  442. if ("24".equals(dunSubject)) return "项目公示";
  443. if ("25".equals(dunSubject)) return "项目抽查";
  444. if ("26".equals(dunSubject)) return "项目备案";
  445. if ("27".equals(dunSubject)) return "项目下证";
  446. if ("28".equals(dunSubject)) return "项目验收";
  447. if ("29".equals(dunSubject)) return "项目拨款";
  448. return "";
  449. }
  450. public static XSSFWorkbook exportTaskHoursListData(List<TOrderTaskListBo> list, String[] comment, String filePath) {
  451. //map 数据 comment表头 (列名称)
  452. //1,创建一个workbook,对应的是一个excel文件
  453. XSSFWorkbook wb = new XSSFWorkbook();
  454. //2.创建一个sheet
  455. XSSFSheet sheet = wb.createSheet();
  456. //创建表头
  457. XSSFCellStyle style1 = setCellStyle1(wb);
  458. XSSFRow row = sheet.createRow(0);
  459. for(int i = 0; i < comment.length; i++){
  460. sheet.setColumnWidth(i, 3000);//设置列宽
  461. XSSFCell c = row.createCell(i);
  462. c.setCellValue(comment[i]);
  463. c.setCellStyle(style1);
  464. }
  465. for (TOrderTaskListBo ob : list) {
  466. //新增一行
  467. XSSFRow r = sheet.createRow(sheet.getLastRowNum()+1);
  468. for (int i = 0; i < comment.length; i++) {
  469. //当前列的只
  470. String o ;
  471. switch (comment[i]) {
  472. //"任务编号","任务名称","订单编号","业务别类","客户名称","任务负责人","任务状态","工时"
  473. case "任务编号": o = ob.getId(); break;
  474. case "任务名称": o = ob.getTaskName(); break;
  475. case "订单编号": o = ob.getOrderNo(); break;
  476. case "业务别类": o = ob.getCname();break;
  477. case "订单部门": o = ob.getDepName();break;
  478. case "客户名称": o = ob.getUserName(); break;
  479. case "任务负责人": o = ob.getReceiverName(); break;
  480. case "任务状态": o = TaskState.getValueByCode(ob.getTaskStatus()); break;
  481. case "工时": o = ob.getHours().toString(); break;
  482. case "合同编号":o =ob.getContractNo();break;
  483. case "签单日期":o =ob.getSignTime();break;
  484. case "派单日期":o =ob.getCreteTime();break;
  485. default: o = ""; break;
  486. }
  487. XSSFCell c = r.createCell(i);
  488. c.setCellValue(o);
  489. }
  490. }
  491. return wb;
  492. }
  493. public static XSSFWorkbook exportSelectMyTaskList(List<TOrderTaskListBo> list, String[] comment, String filePath) {
  494. //map 数据 comment表头 (列名称)
  495. //1,创建一个workbook,对应的是一个excel文件
  496. XSSFWorkbook wb = new XSSFWorkbook();
  497. //2.创建一个sheet
  498. XSSFSheet sheet = wb.createSheet();
  499. //创建表头
  500. XSSFCellStyle style1 = setCellStyle1(wb);
  501. XSSFRow row = sheet.createRow(0);
  502. for(int i = 0; i < comment.length; i++){
  503. sheet.autoSizeColumn(i);
  504. sheet.autoSizeColumn(i, true);
  505. sheet.setColumnWidth(i, 3000);//设置列宽
  506. XSSFCell c = row.createCell(i);
  507. c.setCellValue(comment[i]);
  508. c.setCellStyle(style1);
  509. }
  510. for (TOrderTaskListBo ob : list) {
  511. //新增一行
  512. XSSFRow r = sheet.createRow(sheet.getLastRowNum()+1);
  513. for (int i = 0; i < comment.length; i++) {
  514. //当前列的只
  515. String o="" ;
  516. switch (comment[i]) {
  517. //"任务编号","任务名称","订单编号","业务别类","客户名称","任务状态","项目状态","分配时间","订单部门"
  518. case "任务编号": o = ob.getId(); setWidth(sheet, i, o,1); break;
  519. case "任务名称": o = ob.getTaskName(); setWidth(sheet, i, o,1);break;
  520. case "订单编号": o = ob.getOrderNo();setWidth(sheet, i, o,1); break;
  521. case "业务别类": o = ob.getCname();setWidth(sheet, i, o,1);break;
  522. case "客户名称(软著数/下证数)":
  523. if(null!=ob.getTaskName()&&ob.getTaskName().contains("软件著作权")){
  524. o =ob.getUserName()+"("+ob.getCommodityQuantity()+"/"+ob.getAlreadyNumber()+")";
  525. }else {
  526. o = ob.getUserName();
  527. }
  528. setWidth(sheet, i, o,2);break;
  529. case "任务状态": o = TaskState.getValueByCode(ob.getTaskStatus()); setWidth(sheet, i, o,1);break;
  530. case "项目状态": o = ProjectNewStage.getValueByCode(ob.getProjectStatus()); setWidth(sheet, i, o,1);break;
  531. case "分配时间": o = ob.getTaskDate();setWidth(sheet, i, o,1); break;
  532. case "订单部门": o = ob.getDepName();setWidth(sheet, i, o,1);break;
  533. default: o = ""; break;
  534. }
  535. XSSFCell c = r.createCell(i);
  536. c.setCellValue(o);
  537. }
  538. }
  539. return wb;
  540. }
  541. private static void setWidth(XSSFSheet sheet, int i, String o,int x) {
  542. if (StringUtils.isNotBlank(o)) {
  543. sheet.setColumnWidth(i, o.getBytes().length*x*280);
  544. }
  545. }
  546. /**
  547. * 年度导出数据模板
  548. * @return
  549. */
  550. public static XSSFWorkbook downOrderModelExcel() {
  551. String[] comment = {"姓名","电话","月份","项目类型","签单数量","累计签单金额(万元)"};
  552. String[] d1 = {"张三","13275161283","1","科技项目","2","12"};
  553. String[] d2 = {"张三","13275161283","1","认证项目","0.3","3"};
  554. //map 数据 comment表头 (列名称)
  555. //1,创建一个workbook,对应的是一个excel文件
  556. XSSFWorkbook wb = new XSSFWorkbook();
  557. //2.创建一个sheet
  558. XSSFSheet sheet = wb.createSheet();
  559. //创建表头
  560. XSSFCellStyle style1 = setCellStyle1(wb);
  561. XSSFRow row = sheet.createRow(0);
  562. XSSFRow row1 = sheet.createRow(1);
  563. XSSFRow row2 = sheet.createRow(2);
  564. for(int i = 0; i < comment.length; i++){
  565. sheet.setColumnWidth(i, 3000);//设置列宽
  566. XSSFCell c = row.createCell(i);
  567. c.setCellValue(comment[i]);
  568. c.setCellStyle(style1);
  569. }
  570. for(int i = 0; i < d1.length; i++){
  571. XSSFCell c = row1.createCell(i);
  572. c.setCellValue(d1[i]);
  573. c.setCellStyle(style1);
  574. }
  575. for(int i = 0; i < d2.length; i++){
  576. XSSFCell c = row2.createCell(i);
  577. c.setCellValue(d2[i]);
  578. c.setCellStyle(style1);
  579. }
  580. return wb;
  581. }
  582. /**
  583. * 导出营销部部门奖金的数据
  584. * @param map
  585. * @return
  586. */
  587. public static XSSFWorkbook exportDeptBonusInfoS(Map<String,List<TOrderBonusBo>> map){
  588. String comment[] = {"订单编号", "订单负责人", "签单日期", "客户名称", "签单金额(万元)", "首付款(万元)", "已收(万元)","已退(万元)","订单状态","项目状态","发放类型",
  589. "校对后单量(个)","发放状态","当月认证项目数量","当月科技项目数量","年度认证项目数量","年度科技项目数量", "奖金创建时间"};
  590. //map 数据 ,comment表头 (列名称)
  591. //1,创建一个workbook,对应的是一个excel文件
  592. XSSFWorkbook wb = new XSSFWorkbook();
  593. int count = 0;
  594. //遍历map
  595. for (Map.Entry<String,List<TOrderBonusBo>> entry : map.entrySet()) {
  596. //2.创建一个sheet
  597. XSSFSheet sheet = wb.createSheet(entry.getKey());
  598. //3。创建一个row
  599. XSSFRow row = sheet.createRow(0);
  600. //4.创建一个cell
  601. XSSFCell cell = row.createCell(0);
  602. cell.setCellValue(entry.getKey()+" 奖金记录");
  603. count ++;
  604. XSSFCellStyle style1 = setCellStyle1(wb);
  605. cell.setCellStyle(style1);
  606. //合并列
  607. sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, comment.length-1));//合并单元格
  608. //创建表头
  609. XSSFRow row2 = sheet.createRow(1);
  610. for(int i = 0; i < comment.length; i++){
  611. sheet.setColumnWidth(i, 3000);//设置列宽
  612. XSSFCell c = row2.createCell(i);
  613. c.setCellValue(comment[i]);
  614. c.setCellStyle(style1);
  615. }
  616. //填充表格数据
  617. //int rowmnum = sheet.getLastRowNum();//获得当前行数据
  618. List<TOrderBonusBo> list = entry.getValue();
  619. if(list == null){
  620. continue;
  621. }
  622. for (TOrderBonusBo ob : list) {
  623. //新增一行
  624. // String comment[] = {"订单编号", "订单负责人", "签单日期", "客户名称", "签单金额", "首付款", "已收","已退","订单状态","项目状态","发放类型",
  625. // "校对后单量","发放状态","当月认证项目数量","当月科技项目数量","年度认证项目数量","年度科技项目数量"};
  626. XSSFRow r = sheet.createRow(sheet.getLastRowNum()+1);
  627. r.createCell(0).setCellValue(ob.getOrderNo());
  628. r.createCell(1).setCellValue(ob.getSalesName());
  629. r.createCell(2).setCellValue(ob.getSignTime());
  630. r.createCell(3).setCellValue(ob.getBuyName());
  631. r.createCell(4).setCellValue(ob.getTotalAmount().toString());
  632. r.createCell(5).setCellValue(ob.getFirstAmount().toString());
  633. r.createCell(6).setCellValue(ob.getSettlementAmount().toString());
  634. r.createCell(7).setCellValue(ob.getRefundAmount().toString());
  635. r.createCell(8).setCellValue(OrderNewState.getValueByCode(ob.getOrderStatus()));
  636. r.createCell(9).setCellValue(ProjectNewStage.getValueByCode(ob.getProjectStatus()));
  637. r.createCell(10).setCellValue(BonusSubject.getValueByCode(ob.getBonusSubject()));
  638. r.createCell(11).setCellValue(ob.getProofCount().toString());
  639. r.createCell(12).setCellValue(ob.getGrantStatus() == 0? "未发放":"已发放");
  640. r.createCell(13).setCellValue(ob.getRenzm());
  641. r.createCell(14).setCellValue(ob.getKejim());
  642. r.createCell(15).setCellValue(ob.getRenzy());
  643. r.createCell(16).setCellValue(ob.getRenzy());
  644. r.createCell(17).setCellValue(ob.getStartTime1());
  645. }
  646. }
  647. return wb;
  648. }
  649. public static XSSFWorkbook exportSelectPrivateOrganizationCustomer(List<CustomerListOut> list, String[] comment,
  650. String filePath) {
  651. //map 数据 comment表头 (列名称)
  652. //1,创建一个workbook,对应的是一个excel文件
  653. XSSFWorkbook wb = new XSSFWorkbook();
  654. //2.创建一个sheet
  655. XSSFSheet sheet = wb.createSheet();
  656. //创建表头
  657. XSSFCellStyle style1 = setCellStyle1(wb);
  658. XSSFRow row = sheet.createRow(0);
  659. for(int i = 0; i < comment.length; i++){
  660. sheet.autoSizeColumn(i);
  661. sheet.autoSizeColumn(i, true);
  662. sheet.setColumnWidth(i, 3000);//设置列宽
  663. XSSFCell c = row.createCell(i);
  664. c.setCellValue(comment[i]);
  665. c.setCellStyle(style1);
  666. }
  667. for (CustomerListOut ob : list) {
  668. //新增一行
  669. XSSFRow r = sheet.createRow(sheet.getLastRowNum()+1);
  670. for (int i = 0; i < comment.length; i++) {
  671. //当前列的只
  672. String o="" ;
  673. switch (comment[i]) {
  674. //"客户名称","地区","联系人","联系电话","社会性质","客户初始时间","剩余私有天数","最新跟进时间","客户等级"
  675. case "客户名称": o = ob.getName(); break;
  676. case "地区": o = ob.getProvince()+"-"+ob.getCity()+"-"+ob.getArea(); break;//
  677. case "联系人": o = ob.getContacts(); break;
  678. case "联系电话": o = ob.getContactMobile();break;
  679. case "社会性质": o = getSocietyTagName(ob.getSocietyTag());break;
  680. case "客户初始时间": o = ob.getCreateTime();break;
  681. case "剩余私有天数": o = ob.getSurplusFollowTime();break;
  682. case "最新跟进时间": o = ob.getLastSignTime();break;
  683. case "客户等级": o = getLevelName(ob.getLevel());break;//
  684. default: o = ""; break;
  685. }
  686. XSSFCell c = r.createCell(i);
  687. c.setCellValue(o);
  688. }
  689. }
  690. return wb;
  691. }
  692. private static String getLevelName(Integer s){
  693. if(s==null)return "";
  694. if(s.equals(0))return "一般客户";
  695. if(s.equals(1))return "意向客户";
  696. if(s.equals(2))return "重点客户";
  697. return "";
  698. }
  699. private static String getSocietyTagName(String s){
  700. if(s==null)return "";
  701. if(s.equals("0"))return "社会企业";
  702. if(s.equals("1"))return "科研单位";
  703. if(s.equals("2"))return "高等院校";
  704. if(s.equals("3"))return "科研专家";
  705. if(s.equals("4"))return "科技达人";
  706. if(s.equals("5"))return "服务机构";
  707. if(s.equals("6"))return "政府部门";
  708. if(s.equals("7"))return "商会";
  709. if(s.equals("8"))return "协会";
  710. if(s.equals("9"))return "其他组织";
  711. if(s.equals("10"))return "其他个人";
  712. return "";
  713. }
  714. }