package day1.demo7;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Properties;
import java.util.Timer;
import java.util.TimerTask;
import javax.activation.DataHandler;
import javax.activation.DataSource;
import javax.activation.FileDataSource;
import javax.mail.BodyPart;
import javax.mail.Message;
import javax.mail.Multipart;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeBodyPart;
import javax.mail.internet.MimeMessage;
import javax.mail.internet.MimeMultipart;
import javax.mail.internet.MimeMessage.RecipientType;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
public class SendMailExcle {
public static void readExcel(String excelFileName) throws BiffException, IOException{
// 创建一个list 用来存储读取的内容
//String excelFileName="D:/资料/Report_Day.xls";
Workbook rwb = null;
// 创建输入流
InputStream stream = new FileInputStream(excelFileName);
// 获取Excel文件对象
rwb = Workbook.getWorkbook(stream);
// 获取文件的指定工作表 默认的第一个
Sheet sheet = rwb.getSheet(0);
// 行数(表头的目录不需要,从1开始)
StringBuffer buffer = new StringBuffer();
//创建HTML
buffer.append("<table width='900' cellpadding='1' cellspacing='1' style='font-weight: bold;font-size:12px;font:新宋体;border:1px solid #000000;background-color:#000000;width:900px;'> " );
buffer.append("<thead style='height:35px;background:#ffffff;'><tr style='height:35px;background:#ffffff;'><th align='center' colspan='6' style='font-weight:bold;height:55px;font-size:20px;font:新宋体;background-color:#ffffff;'>"+sheet.getCell(0, 0).getContents()+"</th></tr> ");
buffer.append(" <tr style='height:35px;background:#ffffff;'><th align='left' colspan='6'>"+sheet.getCell(0, 1).getContents()+"</th></tr> ");
buffer.append(" <tr style='font-weight:bold;height:35px;font-size:14px;font:新宋体;background-color:#ffffff;'><th align='center' colspan='2' >"+sheet.getCell(0, 2).getContents()+"</th><th align='center'>"+sheet.getCell(2, 2).getContents()+"</th><th align='center'>"+sheet.getCell(3, 2).getContents()+"</th><th>"+sheet.getCell(4, 2).getContents()+"</th><th align='center'>"+sheet.getCell(5, 2).getContents()+"</th></tr> </thead>");
buffer.append("<tbody>");
int count=0;
int termCount=0;
//重第3行开始循环
for (int i = 3; i < sheet.getRows(); i++) {
//取得,每行的每一列的值
String termString0=sheet.getCell(0, i).getContents();
String termString1=sheet.getCell(1, i).getContents();
String termString2=sheet.getCell(2, i).getContents();
String termString3=sheet.getCell(3, i).getContents();
String termString4=sheet.getCell(4, i).getContents();
String termString5=sheet.getCell(5, i).getContents();
try{
//把每行每列的值进行数据类型转换
Double intTermString2=Double.parseDouble(termString2);
Double intTermString3=Double.parseDouble(termString3);
Double intTermString4=Double.parseDouble(termString4);
Double intTermString5=Double.parseDouble(termString5);
//转换后进行if判断小于intTermString2要把字体变成红色
if(intTermString2!=null){
termString2="<td align='center' style=color:#000;height:23px;background:#ffffff;'>"+intTermString2+"</td>";
}
if(intTermString3<intTermString2){
termString3="<td style='color:red;height:23px;background:#ffffff;' align='center'>"+intTermString3+"</td>";
// buffer.append(termString3).append("</td>");
}if(intTermString4<intTermString2){
termString4="<td style='color:red;height:23px;background:#ffffff;' align='center'>"+intTermString4+"</td>";
}if(intTermString5<intTermString2){
termString5="<td style='color:red;height:23px;background:#ffffff;' align='center'>"+intTermString5+"</td>";
}
else
{
termString2="<td align='center' style=color:#000;height:23px;background:#ffffff;'>"+intTermString2+"</td>";
termString3="<td align='center' style=color:#000;height:23px;background:#ffffff;'>"+intTermString3+"</td>";
termString4="<td align='center' style=color:#000;height:23px;background:#ffffff;'>"+intTermString4+"</td>";
termString5="<td align='center' style=color:#000;height:23px;background:#ffffff;'>"+intTermString5+"</td>";
}
}catch(Exception exception){
termString2="<td align='center'>"+termString2+"</td>";
termString3="<td align='center'>"+termString3+"</td>";
termString4="<td align='center'>"+termString4+"</td>";
termString5="<td align='center'>"+termString5+"</td>";
}
//去掉EXCLE文件中每一行第一列中的空的单元格
if(termString1.length()>0)
{
count=0;
}else
{
count++;
continue;
}
if(count>1)
{
break;
}
buffer.append("<tr style='height:35px;background:#ffffff;'>");
//合并单元格
if(termString0.length()>0)
{
if(termCount>0)
{
String t=buffer.toString();
//把做了标记的进行合并跨行
t=t.replace("@@##"," rowspan='"+(termCount)+"'");
buffer=new StringBuffer();
buffer.append(t);
termCount=0;
}
//给termString0做个@@##标记,根据标记机型单元格合并
termString0="<td style= 'font-weight:bold' align='center' @@##> ";
buffer.append(termString0).append(sheet.getCell(0, i).getContents()).append("</td>");
}
termCount++;
buffer.append("<td style= 'font-weight:bold' align='center'>"+termString1+"</td>"+termString2+""+""+termString3+""+""+termString4+""+""+termString5+"</tr>");
}
String t = buffer.toString();
t=t.replace("@@##"," rowspan='"+(termCount)+"'");
buffer=new StringBuffer();
buffer.append(t);
buffer.append("</tbody>");
buffer.append("</table>");
buffer.append("<div>相关图表请参阅附件</div>");
System.out.println(buffer.toString());
Date dt = new Date();
SimpleDateFormat sdf = new SimpleDateFormat( "yyyy-MM-dd ");
dt.setDate(-1);
String yesterday = sdf.format(dt);
SendMailExcle.send("lib@china.kjlink.com", "lib", "RTYuio123", "请阅"+yesterday+"寿险电话中心日报报表", "D:/资料/Report_Day.xls", yesterday+"寿险电话中心日报报表.xls",buffer.toString());
// 返回值集合
}
public static void send(String cfrom, String username,
String password, String subject,
String attachmentPath, String attachmentName,String buffer) {
// 这里面使用新浪作为发送邮件的邮件服务器,其他的smtp服务器可以到相关网站上查到。
String host = "china.kjlink.com";
// 发送方邮箱地址(如BlogJava2006@blog.com.cn.)
String from = cfrom;
// 收件人邮箱地址
//String to[] = serverMailBoxAddress;
// 发送者的邮箱用户名
String user = username;
// 发送者的邮箱密码
String ps = password;
Properties props = new Properties();
// 设置发送邮件的邮件服务器的属性(这里使用新浪的smtp服务器)
props.put("mail.smtp.host", host);
// 需要经过授权,也就是有户名和密码的校验,这样才能通过验证(一定要有//这一条)
props.put("mail.smtp.auth", "true");
// 用刚刚设置好的props对象构建一个session
Session session = Session.getDefaultInstance(props);
// 有了这句便可以在发送邮件的过程中在console处显示过程信息,供调试使
// 用(有的时候网络连通性不够好,发送邮件可能会有延迟,在这里面会有所//提示,所以最好是加上这句,避免盲目的等待)
session.setDebug(true);
// 定义消息对象
Message message=new MimeMessage(session);
try {
message.setFrom(new InternetAddress(from));
message.setRecipients(RecipientType.TO,InternetAddress.parse("lib@china.kjlink.com"));
message.setRecipients(RecipientType.CC,InternetAddress.parse("c_wangmingzhi@oa.cpic.com.cn"));
message.setSubject(subject);
// 向multipart对象中添加邮件的各个部分内容,包括文本内容和附件
Multipart multipart = new MimeMultipart();
// 设置邮件的文本内容
BodyPart contentPart = new MimeBodyPart();
contentPart.setContent(buffer, "text/html;charset=utf-8");
multipart.addBodyPart(contentPart);
// 添加附件
// 注意:下面定义的enc对象用来处理中文附件名,否则名称是中文的附//件在邮箱里面显示的会是乱码,
sun.misc.BASE64Encoder enc = new sun.misc.BASE64Encoder();
MimeBodyPart messageBodyPart = new MimeBodyPart();
messageBodyPart.setFileName("=?utf-8?B?"+ enc.encode(attachmentName.getBytes()) + "?=");
DataSource source = new FileDataSource(attachmentPath);
messageBodyPart.setDataHandler(new DataHandler(source));
messageBodyPart.setDisposition(MimeBodyPart.ATTACHMENT);
multipart.addBodyPart(messageBodyPart);
// 将multipart对象放到message中
message.setContent(multipart);
// 发送邮件
message.saveChanges();
Transport transport = session.getTransport("smtp");
transport.connect(host, user, ps);
transport.sendMessage(message, message.getAllRecipients());
//transport.send(message, InternetAddress.parse("lib@china.kjlink.com,cenzy@china.kjlink.com"));
transport.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
final String path="D:/资料/Report_Day.xls";
Timer timer = new Timer();
TimerTask tk=new TimerTask() {
@Override
public void run() {
try {
SendMailExcle.readExcel(path);
} catch (BiffException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
};
timer.schedule(tk, 1000);
}
}