ExportExcelUtil.java 28 KB

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