import com.sun.mail.util.MailSSLSocketFactory;

import jxl.Workbook;

import jxl.write.*;

import jxl.write.biff.RowsExceededException;

import org.springframework.util.StringUtils;

import utils.JDBCUtils;

import javax.activation.DataHandler;

import javax.activation.DataSource;

import javax.activation.FileDataSource;

import javax.mail.*;

import javax.mail.internet.*;

import java.io.File;

import java.io.IOException;

import java.io.UnsupportedEncodingException;

import java.security.GeneralSecurityException;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.Properties;

import java.util.Scanner;

/**

* 创建excel表格

*/

public class input {

public static void main(String[] args) throws IOException, RowsExceededException, WriteException {

//1:创建excel文件

File file=new File("d:/xxx.xls");

file.createNewFile();

//2:创建工作簿

WritableWorkbook workbook= Workbook.createWorkbook(file);

//3:创建sheet,设置第二三四..个sheet，依次类推即可

WritableSheet sheet=workbook.createSheet("数值统计", 0);

//主要是改变单元格背景、字体、颜色等等。

WritableCellFormat wc = new WritableCellFormat();

// 设置居中

wc.setAlignment(Alignment.CENTRE);

// 设置边框线

wc.setBorder(Border.ALL, BorderLineStyle.THIN);

// 设置单元格的背景颜色

wc.setBackground(jxl.format.Colour.RED);

// 设置边距

sheet.getSettings().setTopMargin(50) ;

sheet.getSettings().setBottomMargin(300) ;

sheet.getSettings().setLeftMargin(10) ;

sheet.getSettings().setRightMargin(10) ;

//4：设置titles

String[] titles={"字段1","字段2","字段3","字段4","字段5"};

//5:单元格

Label label=null;

//6:给第一行设置列名

for(int i=0;i

//x,y,第一行的列名

label=new Label(i,0,titles[i]);

//7：添加单元格

sheet.addCell(label);

}

//8：模拟数据库导入数据

//获取general数据库的连接

Connection conn = JDBCUtils.getConnection();

PreparedStatement pstmt = null;

ResultSet rs = null;

String sql = "SELECT 值1,值2,值3,值4,值5 from 表名;

//获取操作sql对象

try {

pstmt = conn.prepareStatement(sql);

rs = pstmt.executeQuery();

int i = 1;

while (rs.next()){

//添加统计日期，第二行第一列

label=new Label(0,i,rs.getString(1));

sheet.addCell(label);

//添加

label=new Label(1,i,rs.getString(2));

sheet.addCell(label);

//添加

label=new Label(2,i,rs.getString(3));

sheet.addCell(label);

//添加

label=new Label(3,i,rs.getString(4));

sheet.addCell(label);

//添加

label=new Label(4,i,rs.getString(5));

sheet.addCell(label);

i++;

}

} catch (SQLException e) {

e.printStackTrace();

}

//写入数据，一定记得写入数据，不然你都开始怀疑世界了，excel里面啥都没有

workbook.write();

//最后一步，关闭工作簿

workbook.close();

//发送邮件

//收件人邮箱

String receive="xxxxx@163.com";

String subject ="邮件主题";

String msg ="邮件内容";

String filename ="d:/xxx.xls";

try {

input.sendMail(receive, subject, msg, filename);

} catch (GeneralSecurityException e) {

e.printStackTrace();

}

}

/**

*

* @param receive 收件人

* @param subject 邮件主题

* @param msg 邮件内容

* @param filename 附件地址

* @return

* @throws GeneralSecurityException

*/

public static boolean sendMail(String receive, String subject, String msg, String filename)

throws GeneralSecurityException {

if (StringUtils.isEmpty(receive)) {

return false;

}

// 发件人电子邮箱

final String from = "xxxx@163.com";

// 发件人电子邮箱密码

final String pass = "xxxxx";

// 指定发送邮件的主机为 smtp.qq.com

String host = "smtp.163.com"; // 邮件服务器

// 获取系统属性

Properties properties = System.getProperties();

// 设置邮件服务器

properties.setProperty("mail.smtp.host", host);

properties.put("mail.smtp.auth", "true");

MailSSLSocketFactory sf = new MailSSLSocketFactory();

sf.setTrustAllHosts(true);

properties.put("mail.smtp.ssl.enable", "true");

properties.put("mail.smtp.ssl.socketFactory", sf);

// 获取默认session对象

Session session = Session.getDefaultInstance(properties, new Authenticator() {

public PasswordAuthentication getPasswordAuthentication() { // qq邮箱服务器账户、第三方登录授权码

return new PasswordAuthentication(from, pass); // 发件人邮件用户名、密码

}

});

try {

// 创建默认的 MimeMessage 对象

MimeMessage message = new MimeMessage(session);

// Set From: 头部头字段

message.setFrom(new InternetAddress(from));

// Set To: 头部头字段

message.addRecipient(Message.RecipientType.TO, new InternetAddress(receive));

// Set Subject: 主题文字

message.setSubject(subject);

// 创建消息部分

BodyPart messageBodyPart = new MimeBodyPart();

// 消息

messageBodyPart.setText(msg);

// 创建多重消息

Multipart multipart = new MimeMultipart();

// 设置文本消息部分

multipart.addBodyPart(messageBodyPart);

// 附件部分

messageBodyPart = new MimeBodyPart();

// 设置要发送附件的文件路径

DataSource source = new FileDataSource(filename);

messageBodyPart.setDataHandler(new DataHandler(source));

// messageBodyPart.setFileName(filename);

// 处理附件名称中文(附带文件路径)乱码问题

messageBodyPart.setFileName(MimeUtility.encodeText(filename));

multipart.addBodyPart(messageBodyPart);

// 发送完整消息

message.setContent(multipart);

// 发送消息

Transport.send(message);

// System.out.println("Sent message successfully....");

return true;

} catch (MessagingException e) {

e.printStackTrace();

} catch (UnsupportedEncodingException e) {

e.printStackTrace();

}

return false;

}

}