| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748 |
- package com.goafanti.common.utils;
- import java.io.BufferedInputStream;
- import java.io.BufferedOutputStream;
- import java.io.File;
- import java.io.FileInputStream;
- import java.io.IOException;
- import java.io.InputStream;
- import java.io.OutputStream;
- import java.text.SimpleDateFormat;
- import java.util.List;
- import java.util.Map;
- import javax.servlet.http.HttpServletResponse;
- import org.apache.poi.ss.usermodel.CellStyle;
- import org.apache.poi.ss.usermodel.HorizontalAlignment;
- import org.apache.poi.ss.util.CellRangeAddress;
- import org.apache.poi.xssf.usermodel.XSSFCell;
- import org.apache.poi.xssf.usermodel.XSSFCellStyle;
- import org.apache.poi.xssf.usermodel.XSSFFont;
- import org.apache.poi.xssf.usermodel.XSSFRow;
- import org.apache.poi.xssf.usermodel.XSSFSheet;
- import org.apache.poi.xssf.usermodel.XSSFWorkbook;
- import com.goafanti.common.model.TOrderBillNew;
- import com.goafanti.customer.bo.CustomerListOut;
- import com.goafanti.order.bo.OrderReportBo;
- import com.goafanti.order.bo.TOrderBonusBo;
- import com.goafanti.order.bo.TOrderNewBo;
- import com.goafanti.order.bo.TOrderTaskListBo;
- import com.goafanti.order.enums.BonusSubject;
- import com.goafanti.order.enums.LiquidationNewState;
- import com.goafanti.order.enums.OrderNewState;
- import com.goafanti.order.enums.ProjectNewStage;
- import com.goafanti.techproject.enums.TaskState;
- public class ExportExcelUtil {
-
- //设置单元格格式
- public static XSSFCellStyle setCellStyle1(XSSFWorkbook workbook){
- // 设置字体
- XSSFFont font = workbook.createFont();
- // font.setColor(HSSFFont.COLOR_RED);
- font.setFontName("微软雅黑");
- font.setFontHeightInPoints((short) 22);//字体大小
-
- // 设置样式
- XSSFCellStyle cellStyle = workbook.createCellStyle();
- cellStyle.setFont(font); //字体
-
- // 设置单元格内容水平对其方式
- // XSSFCellStyle.ALIGN_CENTER 居中对齐
- // XSSFCellStyle.ALIGN_LEFT 左对齐
- // XSSFCellStyle.ALIGN_RIGHT 右对齐
- cellStyle.setAlignment(HorizontalAlignment.CENTER);
- //设置自动换行
- cellStyle.setWrapText(true);
- return cellStyle;
- }
- public static XSSFCellStyle setCellStyle2(XSSFWorkbook workbook){
- // 设置字体
- XSSFFont font = workbook.createFont();
- // font.setColor(HSSFFont.COLOR_RED);
- font.setFontName("微软雅黑");
- font.setFontHeightInPoints((short) 12);//字体大小
-
- // 设置样式
- XSSFCellStyle cellStyle = workbook.createCellStyle();
- cellStyle.setFont(font); //字体
-
- // 设置单元格内容水平对其方式
- // XSSFCellStyle.ALIGN_CENTER 居中对齐
- // XSSFCellStyle.ALIGN_LEFT 左对齐
- // XSSFCellStyle.ALIGN_RIGHT 右对齐
- cellStyle.setAlignment(HorizontalAlignment.CENTER);
- //设置自动换行
- cellStyle.setWrapText(true);
- return cellStyle;
- }
-
- //下载课表
- public static void download(String path, HttpServletResponse response,String filename) {
- try {
- // path是指欲下载的文件的路径。
- File file = new File(path);
- // 取得文件名。
- // 以流的形式下载文件。
- InputStream fis = new BufferedInputStream(new FileInputStream(path));
- byte[] buffer = new byte[fis.available()];
- fis.read(buffer);
- fis.close();
- // 清空response
- response.reset();
- // 设置response的Header
- response.addHeader("Content-Disposition", "attachment;filename="
- + new String(filename.getBytes(),"iso-8859-1")); //防止中文乱码或者中文忽略的情况
- response.addHeader("Content-Length", "" + file.length());
- OutputStream toClient = new BufferedOutputStream(
- response.getOutputStream());
- response.setContentType("application/octet-stream;charset=utf-8");
- toClient.write(buffer);
- toClient.flush();
- toClient.close();
-
-
- } catch (IOException ex) {
- ex.printStackTrace();
- }
-
- }
- public static void download(String path, HttpServletResponse response) {
- try {
- // path是指欲下载的文件的路径。
- File file = new File(path);
- // 取得文件名。
- String filename = file.getName();
- // 以流的形式下载文件。
- InputStream fis = new BufferedInputStream(new FileInputStream(path));
- byte[] buffer = new byte[fis.available()];
- fis.read(buffer);
- fis.close();
- // 清空response
- response.reset();
- // 设置response的Header
- response.addHeader("Content-Disposition", "attachment;filename="
- + new String(filename.getBytes(),"iso-8859-1"));
- response.addHeader("Content-Length", "" + file.length());
- OutputStream toClient = new BufferedOutputStream(
- response.getOutputStream());
- response.setContentType("application/octet-stream;charset=utf-8");
- toClient.write(buffer);
- toClient.flush();
- toClient.close();
- } catch (IOException ex) {
- ex.printStackTrace();
- }
- }
-
- //=========================================================此处分割线===========================================================================//
- /**
- * 导出部门流水数据
- * @param map 数据
- * @param comment 表头(列名)
- * @param filePath 生成文件路径
- */
- public static XSSFWorkbook exportDeptBillInfo(Map<String,List<TOrderBillNew>> map,String[] comment,String filePath){
- //map 数据 ,comment表头 (列名称)
- //1,创建一个workbook,对应的是一个excel文件
- XSSFWorkbook wb = new XSSFWorkbook();
- int count = 0;
- //遍历map
- for (Map.Entry<String,List<TOrderBillNew>> entry : map.entrySet()) {
- //2.创建一个sheet
- XSSFSheet sheet = wb.createSheet(entry.getKey());
- //3。创建一个row
- XSSFRow row = sheet.createRow(0);
- //4.创建一个cell
- XSSFCell cell = row.createCell(0);
- cell.setCellValue(entry.getKey()+" 流水记录");
- count ++;
- XSSFCellStyle style1 = setCellStyle1(wb);
- cell.setCellStyle(style1);
- //合并列
- sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, comment.length-1));//合并单元格
- //创建表头
- XSSFRow row2 = sheet.createRow(1);
- for(int i = 0; i < comment.length; i++){
- sheet.setColumnWidth(i, 3000);//设置列宽
- XSSFCell c = row2.createCell(i);
- c.setCellValue(comment[i]);
- c.setCellStyle(style1);
- }
- //填充表格数据
- //int rowmnum = sheet.getLastRowNum();//获得当前行数据
- List<TOrderBillNew> list = entry.getValue();
- if(list == null){
- continue;
- }
- for (TOrderBillNew ob : list) {
- //新增一行
- XSSFRow r = sheet.createRow(sheet.getLastRowNum()+1);
- for (int i = 0; i < comment.length; i++) {
- //当前列的只
- String o ;
- switch (comment[i]) {
- case "平台流水编号": o = ob.getBillNo(); break;
- case "平台流水时间": o = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(ob.getCreateTime()); break;
- case "交易金额(万元)": o = ob.getTransactionAmount().toString() ; break;
- case "付款方": o = ob.getPayerName(); break;
- case "收款方": o = ob.getPayeeName(); break;
- case "流水科目":
- if(0 == ob.getTransactionSubject()){
- o = "代收";
- }else if(1 == ob.getTransactionSubject()){
- o = "代付";
- }else if(2 == ob.getTransactionSubject()){
- o = "退款";
- }else{
- o = "未定义";
- }
- break;
- case "交易渠道":
- if(0 == ob.getTransactionChannel()){
- o = "微信";
- }else if(1 == ob.getTransactionChannel()){
- o = "支付宝";
- }else if(2 == ob.getTransactionChannel()){
- o = "银联";
- }else{
- o = "未定义";
- }
- break;
- case "交易时间": o = ob.getFinancialPayTimes(); break;
- case "财务流水号": o = ob.getFinancialPayNo();break;
- case "订单编号": o = ob.getOrderNo(); break;
- case "订单负责人": o = ob.getSaleName(); break;
- default: o = ""; break;
- }
- XSSFCell c = r.createCell(i);
- c.setCellValue(o);
- }
- }
- }
- return wb;
- /*OutputStream os = new ByteArrayOutputStream();
- try {
- wb.write(os);
- } catch (IOException e1) {
- // TODO Auto-generated catch block
- e1.printStackTrace();
- }
- //将表格数据放入文件中
- try {
- FileOutputStream fos = new FileOutputStream(filePath);
- wb.write(fos);
- fos.close();
- System.out.println("已经创建文件");
- } catch (Exception e) {
- System.out.println("发生了异常");
- e.printStackTrace();
- }*/
- }
- /**
- * 导出技术员奖金统计
- * @param map
- * @param comment
- * @return
- */
- public static XSSFWorkbook exportDeptBonusInfoT(Map<String,List<TOrderBonusBo>> map,String[] comment){
- //map 数据 ,comment表头 (列名称)
- //1,创建一个workbook,对应的是一个excel文件
- XSSFWorkbook wb = new XSSFWorkbook();
- int count = 0;
- //遍历map
- for (Map.Entry<String,List<TOrderBonusBo>> entry : map.entrySet()) {
- //2.创建一个sheet
- XSSFSheet sheet = wb.createSheet(entry.getKey());
- //3。创建一个row
- XSSFRow row = sheet.createRow(0);
- //4.创建一个cell
- XSSFCell cell = row.createCell(0);
- cell.setCellValue(entry.getKey()+" 奖金记录");
- count ++;
- XSSFCellStyle style1 = setCellStyle1(wb);
- cell.setCellStyle(style1);
- //合并列
- sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, comment.length-1));//合并单元格
- //创建表头
- XSSFRow row2 = sheet.createRow(1);
- for(int i = 0; i < comment.length; i++){
- sheet.setColumnWidth(i, 3000);//设置列宽
- XSSFCell c = row2.createCell(i);
- c.setCellValue(comment[i]);
- c.setCellStyle(style1);
- }
- //填充表格数据
- //int rowmnum = sheet.getLastRowNum();//获得当前行数据
- List<TOrderBonusBo> list = entry.getValue();
- if(list == null){
- continue;
- }
- for (TOrderBonusBo ob : list) {
- //新增一行
- XSSFRow r = sheet.createRow(sheet.getLastRowNum()+1);
- for (int i = 0; i < comment.length; i++) {
- //当前列的只
- String o = "";
- switch (comment[i]) {
- case "任务编号": o = ob.getTid().toString(); break;
- case "任务名称": o = ob.getCommodityName(); break;
- case "订单编号": o = ob.getOrderNo(); break;
- case "任务负责人": o = ob.getTargetName(); break;
- case "任务状态": o = TaskState.getValueByCode(ob.getTaskStatus()); break;
- case "任务数量": o = ob.getCommodityQuantity().toString(); break;
- case "项目状态": o = ProjectNewStage.getValueByCode(ob.getProjectStatus()); break;
- case "受理日期": o = ob.getAcceptTime(); break;
- case "评审日期": o = ob.getReviewTime(); break;
- case "下证日期": o = ob.getLicenceTime();break;
- case "订单结算状态": o = LiquidationNewState.getValueByCode(ob.getLiquidationStatus()); break;
- case "发放类型": o = BonusSubject.getValueByCode(ob.getBonusSubject()); break;
- case "发放状态":
- if(0 == ob.getGrantStatus()){
- o = "未发放";
- }else if(1 == ob.getGrantStatus()){
- o = "已发放";
- }
- break;
- case "奖金创建时间": o = ob.getStartTime1(); break;
- default: o = ""; break;
- }
- XSSFCell c = r.createCell(i);
- c.setCellValue(o);
- }
- }
- }
- return wb;
- /*OutputStream os = new ByteArrayOutputStream();
- try {
- wb.write(os);
- } catch (IOException e1) {
- // TODO Auto-generated catch block
- e1.printStackTrace();
- }
- //将表格数据放入文件中
- try {
- FileOutputStream fos = new FileOutputStream(filePath);
- wb.write(fos);
- fos.close();
- System.out.println("已经创建文件");
- } catch (Exception e) {
- System.out.println("发生了异常");
- e.printStackTrace();
- }*/
- }
- /**
- * 导出订单数据
- * @param list
- * @param date
- * @return
- */
- public static XSSFWorkbook exportOrderInfo(List<OrderReportBo> list,String date){
- //list 数据 ,comment表头 (列名称)
- String[] comment = {"序号","订单部门","订单类型","签单人员","签单数量","累计签单金额(万元)"};
- //1,创建一个workbook,对应的是一个excel文件
- XSSFWorkbook wb = new XSSFWorkbook();
- //遍历map
- //2.创建一个sheet
- XSSFSheet sheet = wb.createSheet(date + "订单统计");
- //3。创建一个row
- XSSFRow row = sheet.createRow(0);
- //4.创建一个cell
- XSSFCell cell = row.createCell(0);
- cell.setCellValue(date + "订单统计");
- XSSFCellStyle style1 = setCellStyle1(wb);
- cell.setCellStyle(style1);
- //合并列
- sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, comment.length-1));//合并单元格
- //创建表头
- XSSFRow row2 = sheet.createRow(1);
- for(int i = 0; i < comment.length; i++){
- sheet.setColumnWidth(i, 3000);//设置列宽
- XSSFCell c = row2.createCell(i);
- c.setCellValue(comment[i]);
- c.setCellStyle(style1);
- }
- //填充表格数据
- //int rowmnum = sheet.getLastRowNum();//获得当前行数据
- for (OrderReportBo ob : list) {
- //新增一行
- XSSFRow r = sheet.createRow(sheet.getLastRowNum()+1);
- for (int i = 0; i < comment.length; i++) {
- //当前列的只
- String o = "";
- switch (comment[i]) {
- case "序号": o = ob.getId().toString(); break;
- case "订单部门": o = ob.getDeptName(); break;
- case "订单类型": o = (ob.getType() == 1 ? "科技项目" : "认证项目"); break;
- case "签单人员": o = ob.getSaleName(); break;
- case "签单数量": o = ob.getProofCount().toString(); break;
- case "累计签单金额(万元)": o =ob.getAmount().toString(); break;
-
- default: o = ""; break;
- }
- XSSFCell c = r.createCell(i);
- c.setCellValue(o);
- }
- }
- return wb;
- /*OutputStream os = new ByteArrayOutputStream();
- try {
- wb.write(os);
- } catch (IOException e1) {
- // TODO Auto-generated catch block
- e1.printStackTrace();
- }
- //将表格数据放入文件中
- try {
- FileOutputStream fos = new FileOutputStream(filePath);
- wb.write(fos);
- fos.close();
- System.out.println("已经创建文件");
- } catch (Exception e) {
- System.out.println("发生了异常");
- e.printStackTrace();
- }*/
- }
-
- public static XSSFWorkbook exportOrderDunData(List<TOrderNewBo> list,String[] comment,String filePath){
- //map 数据 comment表头 (列名称)
- //1,创建一个workbook,对应的是一个excel文件
- XSSFWorkbook wb = new XSSFWorkbook();
- //2.创建一个sheet
- XSSFSheet sheet = wb.createSheet();
- //创建表头
- XSSFCellStyle style1 = setCellStyle1(wb);
- XSSFRow row = sheet.createRow(0);
- for(int i = 0; i < comment.length; i++){
- sheet.setColumnWidth(i, 3000);//设置列宽
- XSSFCell c = row.createCell(i);
- c.setCellValue(comment[i]);
- c.setCellStyle(style1);
- }
- for (TOrderNewBo ob : list) {
- //新增一行
- XSSFRow r = sheet.createRow(sheet.getLastRowNum()+1);
- for (int i = 0; i < comment.length; i++) {
- //当前列的只
- String o ;
- switch (comment[i]) {
- case "订单编号": o = ob.getOrderNo(); break;
- case "签单时间": o = ob.getSignDate(); break;
- case "客户名称": o = ob.getUserName(); break;
- case "订单状态": o = OrderNewState.getValueByCode(ob.getOrderStatus());break;
- case "签单金额(万元)": o = ob.getTotalAmount().toString(); break;
- case "项目状态": o = ProjectNewStage.getValueByCode(ob.getProjectStatus()); break;
- case "结算状态": o = LiquidationNewState.getValueByCode(ob.getLiquidationStatus()); break;
- case "应收款(万元)": o = ob.getAccountsReceivable().toString(); break;
- case "已收款(万元)": o = ob.getSettlementAmount().toString(); break;
- case "未收款(万元)": o = ob.getUncollectedAmount().toString(); break;
- case "订单部门": o =ob.getDepName(); break;
- case "订单负责人": o =ob.getSalesmanName(); break;
- case "催收科目": o = getDunSuBubject(ob.getDunSubject()); break;
- case "催款启动日期": o = ob.getStartDate(); break;
- default: o = ""; break;
- }
- XSSFCell c = r.createCell(i);
- c.setCellValue(o);
- }
- }
- return wb;
-
- }
- private static String getDunSuBubject(String dunSubject) {
- if ("12".equals(dunSubject)) return "订单审核通过";
- if ("20".equals(dunSubject)) return "项目待提交";
- if ("21".equals(dunSubject)) return "项目已提交";
- if ("22".equals(dunSubject)) return "项目评审";
- if ("23".equals(dunSubject)) return "项目立项";
- if ("24".equals(dunSubject)) return "项目公示";
- if ("25".equals(dunSubject)) return "项目抽查";
- if ("26".equals(dunSubject)) return "项目备案";
- if ("27".equals(dunSubject)) return "项目下证";
- if ("28".equals(dunSubject)) return "项目验收";
- if ("29".equals(dunSubject)) return "项目拨款";
- return "";
- }
- public static XSSFWorkbook exportTaskHoursListData(List<TOrderTaskListBo> list, String[] comment, String filePath) {
- //map 数据 comment表头 (列名称)
- //1,创建一个workbook,对应的是一个excel文件
- XSSFWorkbook wb = new XSSFWorkbook();
- //2.创建一个sheet
- XSSFSheet sheet = wb.createSheet();
- //创建表头
- XSSFCellStyle style1 = setCellStyle1(wb);
- XSSFRow row = sheet.createRow(0);
- for(int i = 0; i < comment.length; i++){
- sheet.setColumnWidth(i, 3000);//设置列宽
- XSSFCell c = row.createCell(i);
- c.setCellValue(comment[i]);
- c.setCellStyle(style1);
- }
- for (TOrderTaskListBo ob : list) {
- //新增一行
- XSSFRow r = sheet.createRow(sheet.getLastRowNum()+1);
- for (int i = 0; i < comment.length; i++) {
- //当前列的只
- String o ;
- switch (comment[i]) {
- //"任务编号","任务名称","订单编号","业务别类","客户名称","任务负责人","任务状态","工时"
- case "任务编号": o = ob.getId(); break;
- case "任务名称": o = ob.getTaskName(); break;
- case "订单编号": o = ob.getOrderNo(); break;
- case "业务别类": o = ob.getCname();break;
- case "订单部门": o = ob.getDepName();break;
- case "客户名称": o = ob.getUserName(); break;
- case "任务负责人": o = ob.getReceiverName(); break;
- case "任务状态": o = TaskState.getValueByCode(ob.getTaskStatus()); break;
- case "工时": o = ob.getHours().toString(); break;
- case "合同编号":o =ob.getContractNo();break;
- case "签单日期":o =ob.getSignTime();break;
- case "派单日期":o =ob.getCreteTime();break;
- default: o = ""; break;
- }
- XSSFCell c = r.createCell(i);
- c.setCellValue(o);
- }
- }
- return wb;
- }
-
- public static XSSFWorkbook exportSelectMyTaskList(List<TOrderTaskListBo> list, String[] comment, String filePath) {
- //map 数据 comment表头 (列名称)
- //1,创建一个workbook,对应的是一个excel文件
- XSSFWorkbook wb = new XSSFWorkbook();
- //2.创建一个sheet
- XSSFSheet sheet = wb.createSheet();
- //创建表头
- XSSFCellStyle style1 = setCellStyle1(wb);
- XSSFRow row = sheet.createRow(0);
- for(int i = 0; i < comment.length; i++){
- sheet.autoSizeColumn(i);
- sheet.autoSizeColumn(i, true);
- sheet.setColumnWidth(i, 3000);//设置列宽
- XSSFCell c = row.createCell(i);
- c.setCellValue(comment[i]);
- c.setCellStyle(style1);
- }
- for (TOrderTaskListBo ob : list) {
- //新增一行
- XSSFRow r = sheet.createRow(sheet.getLastRowNum()+1);
- for (int i = 0; i < comment.length; i++) {
- //当前列的只
- String o="" ;
- switch (comment[i]) {
- //"任务编号","任务名称","订单编号","业务别类","客户名称","任务状态","项目状态","分配时间","订单部门"
- case "任务编号": o = ob.getId(); setWidth(sheet, i, o,1); break;
- case "任务名称": o = ob.getTaskName(); setWidth(sheet, i, o,1);break;
- case "订单编号": o = ob.getOrderNo();setWidth(sheet, i, o,1); break;
- case "业务别类": o = ob.getCname();setWidth(sheet, i, o,1);break;
- case "客户名称(软著数/下证数)":
- if(null!=ob.getTaskName()&&ob.getTaskName().contains("软件著作权")){
- o =ob.getUserName()+"("+ob.getCommodityQuantity()+"/"+ob.getAlreadyNumber()+")";
- }else {
- o = ob.getUserName();
- }
-
- setWidth(sheet, i, o,2);break;
- case "任务状态": o = TaskState.getValueByCode(ob.getTaskStatus()); setWidth(sheet, i, o,1);break;
- case "项目状态": o = ProjectNewStage.getValueByCode(ob.getProjectStatus()); setWidth(sheet, i, o,1);break;
- case "分配时间": o = ob.getTaskDate();setWidth(sheet, i, o,1); break;
- case "订单部门": o = ob.getDepName();setWidth(sheet, i, o,1);break;
- default: o = ""; break;
- }
- XSSFCell c = r.createCell(i);
- c.setCellValue(o);
- }
- }
- return wb;
- }
- private static void setWidth(XSSFSheet sheet, int i, String o,int x) {
- if (StringUtils.isNotBlank(o)) {
- sheet.setColumnWidth(i, o.getBytes().length*x*280);
-
- }
-
- }
- /**
- * 年度导出数据模板
- * @return
- */
- public static XSSFWorkbook downOrderModelExcel() {
- String[] comment = {"姓名","电话","月份","项目类型","签单数量","累计签单金额(万元)"};
- String[] d1 = {"张三","13275161283","1","科技项目","2","12"};
- String[] d2 = {"张三","13275161283","1","认证项目","0.3","3"};
-
- //map 数据 comment表头 (列名称)
- //1,创建一个workbook,对应的是一个excel文件
- XSSFWorkbook wb = new XSSFWorkbook();
- //2.创建一个sheet
- XSSFSheet sheet = wb.createSheet();
- //创建表头
- XSSFCellStyle style1 = setCellStyle1(wb);
- XSSFRow row = sheet.createRow(0);
- XSSFRow row1 = sheet.createRow(1);
- XSSFRow row2 = sheet.createRow(2);
- for(int i = 0; i < comment.length; i++){
- sheet.setColumnWidth(i, 3000);//设置列宽
- XSSFCell c = row.createCell(i);
- c.setCellValue(comment[i]);
- c.setCellStyle(style1);
- }
- for(int i = 0; i < d1.length; i++){
- XSSFCell c = row1.createCell(i);
- c.setCellValue(d1[i]);
- c.setCellStyle(style1);
- }
- for(int i = 0; i < d2.length; i++){
- XSSFCell c = row2.createCell(i);
- c.setCellValue(d2[i]);
- c.setCellStyle(style1);
- }
- return wb;
- }
-
- /**
- * 导出营销部部门奖金的数据
- * @param map
- * @return
- */
- public static XSSFWorkbook exportDeptBonusInfoS(Map<String,List<TOrderBonusBo>> map){
- String comment[] = {"订单编号", "订单负责人", "签单日期", "客户名称", "签单金额(万元)", "首付款(万元)", "已收(万元)","已退(万元)","订单状态","项目状态","发放类型",
- "校对后单量(个)","发放状态","当月认证项目数量","当月科技项目数量","年度认证项目数量","年度科技项目数量", "奖金创建时间"};
- //map 数据 ,comment表头 (列名称)
- //1,创建一个workbook,对应的是一个excel文件
- XSSFWorkbook wb = new XSSFWorkbook();
- int count = 0;
- //遍历map
- for (Map.Entry<String,List<TOrderBonusBo>> entry : map.entrySet()) {
- //2.创建一个sheet
- XSSFSheet sheet = wb.createSheet(entry.getKey());
- //3。创建一个row
- XSSFRow row = sheet.createRow(0);
- //4.创建一个cell
- XSSFCell cell = row.createCell(0);
- cell.setCellValue(entry.getKey()+" 奖金记录");
- count ++;
- XSSFCellStyle style1 = setCellStyle1(wb);
- cell.setCellStyle(style1);
- //合并列
- sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, comment.length-1));//合并单元格
- //创建表头
- XSSFRow row2 = sheet.createRow(1);
- for(int i = 0; i < comment.length; i++){
- sheet.setColumnWidth(i, 3000);//设置列宽
- XSSFCell c = row2.createCell(i);
- c.setCellValue(comment[i]);
- c.setCellStyle(style1);
- }
- //填充表格数据
- //int rowmnum = sheet.getLastRowNum();//获得当前行数据
- List<TOrderBonusBo> list = entry.getValue();
- if(list == null){
- continue;
- }
- for (TOrderBonusBo ob : list) {
- //新增一行
- // String comment[] = {"订单编号", "订单负责人", "签单日期", "客户名称", "签单金额", "首付款", "已收","已退","订单状态","项目状态","发放类型",
- // "校对后单量","发放状态","当月认证项目数量","当月科技项目数量","年度认证项目数量","年度科技项目数量"};
- XSSFRow r = sheet.createRow(sheet.getLastRowNum()+1);
- r.createCell(0).setCellValue(ob.getOrderNo());
- r.createCell(1).setCellValue(ob.getSalesName());
- r.createCell(2).setCellValue(ob.getSignTime());
- r.createCell(3).setCellValue(ob.getBuyName());
- r.createCell(4).setCellValue(ob.getTotalAmount().toString());
- r.createCell(5).setCellValue(ob.getFirstAmount().toString());
- r.createCell(6).setCellValue(ob.getSettlementAmount().toString());
- r.createCell(7).setCellValue(ob.getRefundAmount().toString());
-
- r.createCell(8).setCellValue(OrderNewState.getValueByCode(ob.getOrderStatus()));
- r.createCell(9).setCellValue(ProjectNewStage.getValueByCode(ob.getProjectStatus()));
-
- r.createCell(10).setCellValue(BonusSubject.getValueByCode(ob.getBonusSubject()));
- r.createCell(11).setCellValue(ob.getProofCount().toString());
- r.createCell(12).setCellValue(ob.getGrantStatus() == 0? "未发放":"已发放");
-
- r.createCell(13).setCellValue(ob.getRenzm());
- r.createCell(14).setCellValue(ob.getKejim());
- r.createCell(15).setCellValue(ob.getRenzy());
- r.createCell(16).setCellValue(ob.getRenzy());
- r.createCell(17).setCellValue(ob.getStartTime1());
-
- }
- }
- return wb;
- }
- public static XSSFWorkbook exportSelectPrivateOrganizationCustomer(List<CustomerListOut> list, String[] comment,
- String filePath) {
- //map 数据 comment表头 (列名称)
- //1,创建一个workbook,对应的是一个excel文件
- XSSFWorkbook wb = new XSSFWorkbook();
- //2.创建一个sheet
- XSSFSheet sheet = wb.createSheet();
- //创建表头
- XSSFCellStyle style1 = setCellStyle1(wb);
- XSSFRow row = sheet.createRow(0);
- for(int i = 0; i < comment.length; i++){
- sheet.autoSizeColumn(i);
- sheet.autoSizeColumn(i, true);
- sheet.setColumnWidth(i, 3000);//设置列宽
- XSSFCell c = row.createCell(i);
- c.setCellValue(comment[i]);
- c.setCellStyle(style1);
- }
- for (CustomerListOut ob : list) {
- //新增一行
- XSSFRow r = sheet.createRow(sheet.getLastRowNum()+1);
- for (int i = 0; i < comment.length; i++) {
- //当前列的只
- String o="" ;
- switch (comment[i]) {
- //"客户名称","地区","联系人","联系电话","社会性质","客户初始时间","剩余私有天数","最新跟进时间","客户等级"
- case "客户名称": o = ob.getName(); break;
- case "地区": o = ob.getProvince()+"-"+ob.getCity()+"-"+ob.getArea(); break;//
- case "联系人": o = ob.getContacts(); break;
- case "联系电话": o = ob.getContactMobile();break;
- case "社会性质": o = getSocietyTagName(ob.getSocietyTag());break;
- case "客户初始时间": o = ob.getCreateTime();break;
- case "剩余私有天数": o = ob.getSurplusFollowTime();break;
- case "最新跟进时间": o = ob.getLastSignTime();break;
- case "客户等级": o = getLevelName(ob.getLevel());break;//
- default: o = ""; break;
- }
- XSSFCell c = r.createCell(i);
- c.setCellValue(o);
- }
- }
- return wb;
- }
- private static String getLevelName(Integer s){
- if(s==null)return "";
- if(s.equals(0))return "一般客户";
- if(s.equals(1))return "意向客户";
- if(s.equals(2))return "重点客户";
- return "";
- }
- private static String getSocietyTagName(String s){
- if(s==null)return "";
- if(s.equals("0"))return "社会企业";
- if(s.equals("1"))return "科研单位";
- if(s.equals("2"))return "高等院校";
- if(s.equals("3"))return "科研专家";
- if(s.equals("4"))return "科技达人";
- if(s.equals("5"))return "服务机构";
- if(s.equals("6"))return "政府部门";
- if(s.equals("7"))return "商会";
- if(s.equals("8"))return "协会";
- if(s.equals("9"))return "其他组织";
- if(s.equals("10"))return "其他个人";
- return "";
- }
- }
|