导出xls

欢迎使用Markdown编辑器

你好！ 这是你第一次使用 Markdown编辑器 所展示的欢迎页。如果你想学习如何使用Markdown编辑器, 可以仔细阅读这篇文章，了解一下Markdown的基本语法知识。

纯手敲导出xls

package com.sinitek.sirm.web.stockresearch.job;

import com.sinitek.sirm.busin.routine.entity.IMessageReceiver;
import com.sinitek.sirm.busin.routine.entity.IMessageTemplate;
import com.sinitek.sirm.busin.routine.service.IMessageEngineService;
import com.sinitek.sirm.busin.routine.service.IMessageService;
import com.sinitek.sirm.busin.routine.service.RoutineServiceFactory;
import com.sinitek.sirm.busin.routine.utils.MessageAttachment;
import com.sinitek.sirm.busin.routine.utils.MessageContext;
import com.sinitek.sirm.busin.routine.utils.MessageReceiver;
import com.sinitek.sirm.busin.stockresearch.service.StockResearchServiceFactory;
import com.sinitek.sirm.org.busin.entity.Employee;
import com.sinitek.sirm.org.busin.service.IOrgService;
import com.sinitek.sirm.org.busin.service.OrgServiceFactory;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.write.*;
import org.apache.commons.collections.MapUtils;
import org.apache.log4j.Logger;
import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;
import org.quartz.StatefulJob;

import java.io.;
import java.text.SimpleDateFormat;
import java.util.;

/**

• 股票分管领域与行业分管领域不匹配通知Job

• @author wei.pan

• @date 2020/11/18
  */
  public class StockAssignResearchMismatchNoticeJob implements StatefulJob {

  private static Logger logger = Logger.getLogger(StockAssignResearchMismatchNoticeJob.class);

  @Override
  public void execute(JobExecutionContext jobExecutionContext) throws JobExecutionException {
  logger.info(“股票分管领域与行业分管领域不匹配数据通知任务启动.”);
  List result = null;
  try {
  result = StockResearchServiceFactory.getStockAssignSearchService().searchStockAssignResearchMismatchStocksInfo();
  } catch (Exception e) {
  logger.error(“股票分管领域与行业分管领域不匹配数据获取失败”, e);
  return;
  }

   if (result != null && result.size() > 0) {
  
       List<Map> dataMapList = new ArrayList<>();
       Map dataMap;
  
       for (Object o : result) {
           Map map = null;
           try {
               map = (Map) o;
           } catch (Exception e) {
               logger.error("类型转换失败", e);
               return;
           }
           if (map != null) {
               dataMap = new LinkedHashMap();
               dataMap.put("股票代码", MapUtils.getString(map, "scode", ""));
               dataMap.put("股票名称", MapUtils.getString(map, "sname", ""));
               dataMap.put("股票实际归属领域", MapUtils.getString(map, "researchnames", ""));
               dataMap.put("行业名称", MapUtils.getString(map, "industryname", ""));
               dataMap.put("行业归属领域", MapUtils.getString(map, "inresearchnames", ""));
               dataMapList.add(dataMap);
           }
       }
  
       WritableWorkbook workbook = null;
       String filePath = null;
  
       // 加锁防止共用临时文件
       synchronized (StockAssignResearchMismatchNoticeJob.class) {
           OutputStream os;
           try {
               filePath = "tempfiles\\StockAssignResearchMismatchWorkTemplate.xls";
               os = new FileOutputStream(filePath);
               WorkbookSettings workbookSettings = new WorkbookSettings();
               //关键代码，解决中文乱码
               workbookSettings.setEncoding("GBK");
               workbook = Workbook.createWorkbook(os, workbookSettings);
           } catch (IOException e) {
               logger.info("workbook create xls failed: " + e);
               return;
           }
  
           WritableSheet sheet = workbook.createSheet("数据", 0);
           Label label;
           //Label(列号,行号 ,内容 )
           List<String> cellStr = new ArrayList<>();
           Set<String> cellSet= null;
           try {
               cellSet = dataMapList.get(0).keySet();
               cellSet.forEach(obj -> {
                   cellStr.add(obj);
               });
           } catch (IndexOutOfBoundsException e) {
               logger.info("search reportMonthResult noResult:" + e);
           }
  
           // 动态添加excel头部字段
           for (int i = 0; i< cellStr.size(); i++) {
               byte[] titleBytes = cellStr.get(i).getBytes();
               sheet.setColumnView(i, titleBytes.length + 2);
               WritableCellFormat format = new WritableCellFormat();
               try {
                   format.setAlignment(Alignment.CENTRE);
                   format.setBackground(Colour.YELLOW);
                   format.setBorder(Border.ALL, BorderLineStyle.MEDIUM, Colour.BLACK);
                   label = new Label(i, 0, cellStr.get(i), format);
                   sheet.addCell(label);
               } catch (WriteException e) {
                   logger.error("表头添加失败", e);
               }
           }
  
           // 动态添加excel内容
           WritableCellFormat format;
           for (int k = 0; k < dataMapList.size(); k++) {
               for (int m = 0; m < cellSet.size(); m++) {
                   try {
                       format = new WritableCellFormat();
                       format.setAlignment(Alignment.CENTRE);
                       label = new Label(m, k + 1, MapUtils.getString(dataMapList.get(k), cellStr.get(m), ""), format);
                       sheet.addCell(label);
                   } catch (WriteException e) {
                       logger.error("数据插入失败", e);
                       try {
                           workbook.write();
                           workbook.close();
                       } catch (IOException e1) {
                           logger.error(e1);
                       } catch (WriteException e1) {
                           logger.error(e1);
                       }
                       return;
                   }
               }
           }
  
           try {
               workbook.write();
               workbook.close();
           } catch (IOException e) {
               logger.error(e);
           } catch (WriteException e) {
               logger.error(e);
           }
  
           MessageContext messageContext = new MessageContext();
  
           messageContext.setCode("SRE_IRE_MISMATCH");
  
           try {
               // 获取模板配置的内部接收人
               IMessageService messageService = RoutineServiceFactory.getMessageService();
               IMessageTemplate messageTemplate = messageService.getMessageTemplateByCode(messageContext.getCode());
               List<IMessageReceiver> messageReceivers = messageService.findMesssageReceiverByTemplateId(messageTemplate.getId() + "");
               if (messageReceivers != null && messageReceivers.size() > 0) {
                   IOrgService orgService = OrgServiceFactory.getOrgService();
                   List<Employee> employees = new ArrayList<>();
                   for(IMessageReceiver messageReceiver : messageReceivers) {
                       employees.addAll(orgService.findEmployeesByOrgTypeOrgId(messageReceiver.getOrgType(), messageReceiver.getOrgId()));
                   }
  
                   List<MessageReceiver> messageReceiverList = new ArrayList<>();
                   MessageReceiver user = null;
                   for (Employee employee : employees) {
                       user = new MessageReceiver();
                       user.setEmail(employee.getEmail());
                       user.setEmpId(employee.getId());
                       messageReceiverList.add(user);
                   }
                   messageContext.setReceivers(messageReceiverList);
               } else {
                   logger.info("消息接收人为空！");
               }
           } catch (Exception e) {
               logger.error("收件人获取异常", e);
           }
  
           SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm");
           String nowStr = simpleDateFormat.format(new Date());
           String fileNameNowStr = "股票分管领域与行业分管领域不匹配表_" + nowStr + "." + filePath.split("\\.")[1];
  
           Map<String, String> params = new HashMap<>();
           params.put("nowStr", nowStr);
           messageContext.setParams(params);
  
           MessageAttachment messageAttachment = new MessageAttachment(filePath, fileNameNowStr);
           List<MessageAttachment> messageAttachments = new ArrayList<>();
           messageAttachments.add(messageAttachment);
  
           messageContext.setAttachments(messageAttachments);
  
           IMessageEngineService messageEngineService = RoutineServiceFactory.getMessageEngineService();
  
           if (messageEngineService != null) {
               messageEngineService.sendMessage(messageContext);
               logger.info("股票分管领域与行业分管领域不匹配数据通知任务执行完毕");
           } else {
               logger.error("IMessageEngineService 实例获取失败！");
           }
           try {
               os.close();
           } catch (IOException e) {
               logger.error(e);
           }
       }
   } else {
       logger.info("股票分管领域与行业分管领域不匹配数据为空");
   }
  

  }
  }