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("[email protected]", "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";
// 發送方郵箱位址(如[email protected].)
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("[email protected]"));
message.setRecipients(RecipientType.CC,InternetAddress.parse("[email protected]"));
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("[email protected],[email protected]"));
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);
}
}