Java导出Excel .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("股票分管领域与行业分管领域不匹配数据为空"); }
}
}
本文地址:https://blog.csdn.net/newuer/article/details/110228437