excel 写入数据并发送到指定邮箱

今天公司要实现这个功能就搜索并实现了

附上代码，方便以后使用：

excel操作类

static String Filename = "C:/Users/Administrator/Desktop/new.xlsx";

/**
* 生成一个Excel文件
*/
public static void writeExcel(String name) {
WritableWorkbook wwb = null;
try {
// 创建一个可写入的工作薄(Workbook)对象
wwb = Workbook.createWorkbook(new File(Filename));
} catch (IOException e) {// 捕获流异常
e.printStackTrace();
}
if (wwb != null) {
// 创建一个可写入的工作表
// Workbook的createSheet方法有两个参数，第一个是工作表的名称，第二个是工作表在工作薄中的位置
WritableSheet ws = wwb.createSheet("sheet1", 0);
// for (int i = 0; i < 10; i++) {// 循环添加单元格
// for (int j = 0; j < 5; j++) {
// Label labelC = new Label(j, i, "这是第" + (i + 1) + "行，第" + (j + 1)
// + "列");
// try {
// ws.addCell(labelC);// 将生成的单元格添加到工作表中
// } catch (Exception e) {// 捕获异常
// e.printStackTrace();
// }
// }
// }
try {
wwb.write();// 从内存中写入文件中
wwb.close();// 从内存中写入文件中
} catch (Exception e) {// 捕获异常
e.printStackTrace();
}
}
System.out.println("生成一个Excel文件成功!");
}

/**
* 将内容写入
*
* @param fileName
* @throws Exception
*/
public static void writeContentToExcel(String fileName, Map<String, Object> map) throws Exception {
File tempFile = new File(fileName);
WritableWorkbook workbook = Workbook.createWorkbook(tempFile);
WritableSheet sheet = workbook.createSheet("TestCreateExcel", 0);
// 一些临时变量，用于写到excel中
Label l = null;
jxl.write.Number n = null;
jxl.write.DateTime d = null;

// 预定义的一些字体和格式，同一个Excel中最好不要有太多格式 字形 大小 加粗 倾斜 下划线 颜色
WritableFont headerFont = new WritableFont(WritableFont.ARIAL, 12, WritableFont.BOLD, false,
UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLUE);
WritableCellFormat headerFormat = new WritableCellFormat(headerFont);

WritableFont titleFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false,
UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.RED);
WritableCellFormat titleFormat = new WritableCellFormat(titleFont);

WritableFont detFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false,
UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
WritableCellFormat detFormat = new WritableCellFormat(detFont);

NumberFormat nf = new NumberFormat("0.00000"); // 用于Number的格式
WritableCellFormat priceFormat = new WritableCellFormat(detFont, nf);

DateFormat df = new DateFormat("yyyy-MM-dd");// 用于日期的
WritableCellFormat dateFormat = new WritableCellFormat(detFont, df);
// 标题
// label(列，行，值，格式)
int seq = 0;
l = new Label(seq++, 0, "序号", titleFormat);
sheet.addCell(l);
l = new Label(1, 0, "债券代码", titleFormat);
sheet.addCell(l);
l = new Label(2, 0, "债券简称", titleFormat);
sheet.addCell(l);
l = new Label(3, 0, "发债人名称", titleFormat);
sheet.addCell(l);
l = new Label(4, 0, "评审类型", titleFormat);
sheet.addCell(l);
l = new Label(5, 0, "评级日期", titleFormat);
sheet.addCell(l);
l = new Label(6, 0, "最新评级", titleFormat);
sheet.addCell(l);
l = new Label(7, 0, "首次评级", titleFormat);
sheet.addCell(l);
l = new Label(8, 0, "变动方向", titleFormat);
sheet.addCell(l);
l = new Label(9, 0, "评级结构", titleFormat);
sheet.addCell(l);
l = new Label(10, 0, "评级公告", titleFormat);
sheet.addCell(l);
Set<String> key = map.keySet();
for (Iterator it = key.iterator(); it.hasNext();) {
String s = (String) it.next();
// 一层代码对应的内容，二层评级对应list，三层评级内容
Map<String, Object> dataMap = (Map<String, Object>) map.get(s);
Set<String> key2 = dataMap.keySet();
for (Iterator it2 = key2.iterator(); it2.hasNext();) {
int row = 0;
String s2 = (String) it2.next();
if ("主体评级".equals(s2)) {
List<DBondIssCredChan> bongIss = (List<DBondIssCredChan>) dataMap.get(s2);
for (DBondIssCredChan b : bongIss) {
int col = seq++;
l = new Label(row++, col, String.valueOf(seq));
sheet.addCell(l);
l = new Label(row++, col, s);
sheet.addCell(l);
l = new Label(row++, col, b.getBondShortName());
sheet.addCell(l);
l = new Label(row++, col, b.getReserName());
sheet.addCell(l);
l = new Label(row++, col, s2);
sheet.addCell(l);
l = new Label(row++, col, String.valueOf(b.getRatePublDate()));
sheet.addCell(l);
l = new Label(row++, col, "");
sheet.addCell(l);
l = new Label(row++, col, "");
sheet.addCell(l);
l = new Label(row++, col, "");
sheet.addCell(l);
l = new Label(row++, col, b.getOrgUniName());
sheet.addCell(l);
l = new Label(row++, col, "查看");
sheet.addCell(l);
}
}
if ("债项评级".equals(s2)) {
List<DBondCredChan> bongCred = (List<DBondCredChan>) dataMap.get(s2);
for (DBondCredChan b : bongCred) {
int col = seq++;
l = new Label(row++, col, String.valueOf(seq));
sheet.addCell(l);
l = new Label(row++, col, s);
sheet.addCell(l);
l = new Label(row++, col, b.getBondShortName());
sheet.addCell(l);
l = new Label(row++, col, b.getReserName());
sheet.addCell(l);
l = new Label(row++, col, s2);
sheet.addCell(l);
l = new Label(row++, col, String.valueOf(b.getRatePublDate()));
sheet.addCell(l);
l = new Label(row++, col, "");
sheet.addCell(l);
l = new Label(row++, col, "");
sheet.addCell(l);
l = new Label(row++, col, "");
sheet.addCell(l);
l = new Label(row++, col, b.getOrgUniName());
sheet.addCell(l);
l = new Label(row++, col, "查看");
sheet.addCell(l);
}
}
}
}
seq += 3;
// 公告
int row = seq++;
l = new Label(0, row, "序号", titleFormat);
sheet.addCell(l);
l = new Label(1, row, "标题", titleFormat);
sheet.addCell(l);
l = new Label(2, row, "来源", titleFormat);
sheet.addCell(l);
l = new Label(3, row, "时间", titleFormat);
sheet.addCell(l);
l = new Label(4, row, "关联组合", titleFormat);
sheet.addCell(l);
l = new Label(5, row, "关联证券", titleFormat);
sheet.addCell(l);
l = new Label(6, row, "关联机构", titleFormat);
sheet.addCell(l);
int num = 0;
for (Iterator it = key.iterator(); it.hasNext();) {
String s = (String) it.next();
// 一层代码对应的内容，二层评级对应list，三层评级内容
Map<String, Object> dataMap = (Map<String, Object>) map.get(s);
Set<String> key2 = dataMap.keySet();
for (Iterator it2 = key2.iterator(); it2.hasNext();) {
String s2 = (String) it2.next();

if ("债券公告".equals(s2)) {
List<DAnnMain> dann = (List<DAnnMain>) dataMap.get(s2);
for (DAnnMain b : dann) {
int col = 0;
row = seq++;
// 序号
l = new Label(col++, row, String.valueOf(++num));
sheet.addCell(l);
l = new Label(col++, row, b.getAnnTitle());
sheet.addCell(l);
l = new Label(col++, row, String.valueOf(b.getOriCode()));
sheet.addCell(l);
l = new Label(col++, row, String.valueOf(b.getDeclDate()));
sheet.addCell(l);
l = new Label(col++, row, b.getStkShortName());
sheet.addCell(l);
l = new Label(col++, row, String.valueOf(b.getStkUniCode()));
sheet.addCell(l);
l = new Label(col++, row, "");
sheet.addCell(l);
}

}
if ("股票公告".equals(s2)) {
List<DAnnMain> dann = (List<DAnnMain>) dataMap.get(s2);
for (DAnnMain b : dann) {
int col = 0;
row = seq++;
// 序号
l = new Label(col++, row, String.valueOf(++num));
sheet.addCell(l);
l = new Label(col++, row, b.getAnnTitle());
sheet.addCell(l);
l = new Label(col++, row, String.valueOf(b.getOriCode()));
sheet.addCell(l);
l = new Label(col++, row, String.valueOf(b.getDeclDate()));
sheet.addCell(l);
l = new Label(col++, row, b.getStkShortName());
sheet.addCell(l);
l = new Label(col++, row, String.valueOf(b.getStkUniCode()));
sheet.addCell(l);
l = new Label(col++, row, "");
sheet.addCell(l);
}

}
}
}
// 设置列的宽度
int column = 0;
sheet.setColumnView(column++, 20);
sheet.setColumnView(column++, 80);
sheet.setColumnView(column++, 30);
sheet.setColumnView(column++, 40);
sheet.setColumnView(column++, 30);
sheet.setColumnView(column++, 30);
sheet.setColumnView(column++, 50);
sheet.setColumnView(column++, 20);
sheet.setColumnView(column++, 20);
sheet.setColumnView(column++, 80);
sheet.setColumnView(column++, 20);
workbook.write();
workbook.close();
System.out.println("内容写入成功");
}

public static void readExcelInfo(String fileName) throws Exception {// 获得Excel文件多少行多少列
Workbook book = Workbook.getWorkbook(new File(fileName));// 构造Workbook（工作薄）对象
Sheet sheet = book.getSheet(0);
// 得到第一列第一行的单元格// 获得第一个工作表对象
int columnum = sheet.getColumns(); // 得到列数
int rownum = sheet.getRows(); // 得到行数
System.out.println(columnum);
System.out.println(rownum);
for (int i = 0; i < rownum; i++) // 循环进行读写
{
for (int j = 0; j < columnum; j++) {
Cell cell1 = sheet.getCell(j, i);
String result = cell1.getContents();
System.out.print(result);
System.out.print(" \t ");
}
System.out.println();
}
book.close();// 关闭（工作薄）对象

}

 

文件发送到指定邮箱：以163邮箱为例：

// 发送邮件的邮箱号（若有授权码则密码输入授权码）
public static String myEmailAccount = "******@163.com";
public static String myEmailPassword = "****";
// 发件人邮箱的 SMTP 服务器地址, 必须准确, 不同邮件服务器地址不同, 一般(只是一般, 绝非绝对)格式为: smtp.xxx.com
// 网易163邮箱的 SMTP 服务器地址为: smtp.163.com
public static String myEmailSMTPHost = "smtp.163.com";
// 收件人邮箱
public static String receiveMailAccount = "***@qq.com";

public static void main(String[] args) throws Exception {
sendMessage();
}

public static void sendMessage() {
// 创建参数配置, 用于连接邮件服务器的参数配置
Properties props = new Properties();
// 使用的协议（JavaMail规范要求）
props.setProperty("mail.transport.protocol", "smtp");
// 发件人的邮箱的 SMTP 服务器地址
props.setProperty("mail.smtp.host", myEmailSMTPHost);
// 需要请求认证
props.setProperty("mail.smtp.auth", "true");
// PS: 某些邮箱服务器要求 SMTP 连接需要使用 SSL 安全认证 (为了提高安全性, 邮箱支持SSL连接, 也可以自己开启),
// 如果无法连接邮件服务器, 仔细查看控制台打印的 log, 如果有有类似 “连接失败, 要求 SSL 安全连接” 等错误,
/*
* // SMTP 服务器的端口 (非 SSL 连接的端口一般默认为 25, 可以不添加, 如果开启了 SSL 连接, //
* 需要改为对应邮箱的 SMTP 服务器的端口, 具体可查看对应邮箱服务的帮助, // QQ邮箱的SMTP(SLL)端口为465或587,
* 其他邮箱自行去查看) final String smtpPort = "465";
* props.setProperty("mail.smtp.port", smtpPort);
* props.setProperty("mail.smtp.socketFactory.class",
* "javax.net.ssl.SSLSocketFactory");
* props.setProperty("mail.smtp.socketFactory.fallback", "false");
* props.setProperty("mail.smtp.socketFactory.port", smtpPort);
*/
// 根据配置创建会话对象, 用于和邮件服务器交互
Session session = Session.getDefaultInstance(props);
session.setDebug(true); // 设置为debug模式, 可以查看详细的发送 log
// 创建邮件
MimeMessage message;
try {
message = createMimeMessage(session, myEmailAccount, receiveMailAccount);
// 根据 Session 获取邮件传输对象
Transport transport = session.getTransport();
/*
* 使用 邮箱账号 和 密码 连接邮件服务器, 这里认证的邮箱必须与 message 中的发件人邮箱一致, 否则报错 PS_02:
* 连接失败的原因通常为以下几点, 仔细检查代码: (1) 邮箱没有开启 SMTP 服务; (2) 邮箱密码错误,
* 例如某些邮箱开启了独立密码; (3) 邮箱服务器要求必须要使用 SSL 安全连接; (4) 请求过于频繁或其他原因,
* 被邮件服务器拒绝服务; (5) 如果以上几点都确定无误, 到邮件服务器网站查找帮助。
*/
transport.connect(myEmailAccount, myEmailPassword);
//发送邮件
transport.sendMessage(message, message.getAllRecipients());
//关闭连接
transport.close();
} catch (Exception e) {
e.printStackTrace();
}
}

/**
* 创建一封只包含文本的简单邮件
* @param session
* 和服务器交互的会话
* @param sendMail
* 发件人邮箱
* @param receiveMail
* 收件人邮箱
* @return
* @throws Exception
*/
public static MimeMessage createMimeMessage(Session session, String sendMail, String receiveMail) throws Exception {
// 创建邮件
MimeMessage message = new MimeMessage(session);
// From: 发件人
message.setFrom(new InternetAddress(sendMail, "标题内容", "UTF-8"));
// To: 收件人
message.setRecipient(MimeMessage.RecipientType.TO, new InternetAddress(receiveMail, "某用户", "UTF-8"));
// Subject: 邮件主题
message.setSubject("主題是", "UTF-8");
/*
* Content: 邮件正文（可以使用html标签） message.setContent(
* "这里是正文内容", "text/html;charset=UTF-8");
*/
// 创建消息部分
BodyPart messageBodyPart = new MimeBodyPart();
// 正文消息消息
messageBodyPart.setText("正文");
// 创建多重消息
Multipart multipart = new MimeMultipart();
// 设置文本消息部分
multipart.addBodyPart(messageBodyPart);
// 附件部分
messageBodyPart = new MimeBodyPart();
// 文件本地地址
String filename = "C:/Users/Administrator/Desktop/gp.xlsx";
DataSource source = new FileDataSource(filename);
messageBodyPart.setDataHandler(new DataHandler(source));
messageBodyPart.setFileName(filename);
multipart.addBodyPart(messageBodyPart);
// 发送完整消息
message.setContent(multipart);
//设置发件时间
message.setSentDate(new Date());
//保存设置
message.saveChanges();
return message;
}