欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页  >  科技

Java导出Excel .xls文件

程序员文章站 2022-03-26 17:13:12
这里写自定义目录标题欢迎使用Markdown编辑器纯手敲导出xls欢迎使用Markdown编辑器你好！ 这是你第一次使用 Markdown编辑器 所展示的欢迎页。如果你想学习如何使用Markdown编辑器, 可以仔细阅读这篇文章，了解一下Markdown的基本语法知识。纯手敲导出xlspackage com.sinitek.sirm.web.stockresearch.job;import com.sinitek.sirm.busin.routine.entity.IMessageReceiver...

这里写自定义目录标题

欢迎使用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("股票分管领域与行业分管领域不匹配数据为空");
 }

    }
    }

本文地址：https://blog.csdn.net/newuer/article/details/110228437

相关标签： xls导出 笔记 java

上一篇： 深度复盘阿里、字节跳动、美团、腾讯、小米等名企高频面试专题，大厂面试真不难

下一篇： java递归实现ztree树结构数据展示

推荐阅读