package com.xbkj.web.location.task;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
import com.sanmen.web.common.LogicException;
import com.sanmen.web.task.Task;
import
com.xbkj.web.location.datasource.DataSourceContextHolder;
import com.xbkj.web.location.datasource.DataSourceMap;
import com.xbkj.web.location.domain.History;
import com.xbkj.web.location.service.IBasicService;
import com.xbkj.web.location.utils.MailUtil1;
public class ExportTaskSpeedImpl implements Task{
private IBasicService service;
private List hisList=
new ArrayList();
private Date cDate;
private String fileName="车辆超速报警记录表";
private String filePath="C:\\";
public ExportTaskSpeedImpl(IBasicService service){
this.service = service;
Calendar c = Calendar.getInstance();
c.set(Calendar.MILLISECOND, 0);
c.add(Calendar.HOUR_OF_DAY, -2);
this.cDate = c.getTime();
}
@SuppressWarnings("unchecked")
@Override
public void execute() throws LogicException {
try {
// 查询超速的车辆信息
DataSourceContextHolder.setDataSourceType(DataSourceMap.REMOTE);
hisList = (List)
service.findObjectBySql("from History h where h.his_time
>= ? and h.equ_mobile = ? order by
h.his_time desc",
new Object[] { cDate, "9110705492" });
System.out.println(hisList.size());
export();
//将查询出来的数据以邮件形式发送出去
try{
MailUtil1 cn = new MailUtil1();
cn.setAddress("caijun@jszg.net","403059609@qq.com",fileName+".xls");
cn.setAffix(filePath+fileName+".xls",fileName+".xls");
cn.send("smtp.sina.net","caijun@jszg.net","pjunc690507");
System.out.println("----------------mail
send successfully----------------");
//发送完邮件后删除本地文件
File f = new
File(filePath+fileName+".xls");
if(f.exists()){
System.out.println("---------------deleting
file-------------------");
if(f.delete()){
System.out.println("----------delete file
successfully--------------------");
}
}else{
System.out.println("--------not find the
file-------------------");
}
}catch(Exception e){
System.out.println("----------------mail send
failed--------------------");
}
} catch (Exception e) {
e.printStackTrace();
throw new LogicException(e);
}
}
public void export() {
WritableWorkbook web = null;
File f = new File(filePath+fileName+".xls");
OutputStream out=null;
try {
if(!f.exists()){
try {
f.createNewFile();
} catch (IOException e) {
e.printStackTrace();
}
}
out = new FileOutputStream(filePath+fileName+".xls");
} catch (FileNotFoundException e3) {
e3.printStackTrace();
}
// 创建新的excel表空间
try {
web = Workbook.createWorkbook(out);
WritableSheet ws = web.createSheet(fileName, 0);
WritableFont headCell = new
WritableFont(WritableFont.ARIAL,16,WritableFont.BOLD,true);
WritableCellFormat headCellFormat = new
WritableCellFormat(headCell);
headCellFormat.setAlignment(Alignment.CENTRE);
Label head = new Label(0,0,"车辆超速报警记录表",headCellFormat);
ws.mergeCells(0, 0, 5, 0);
WritableCellFormat w = new WritableCellFormat();
w.setAlignment(Alignment.CENTRE);
head.setCellFormat(w);
//设置列的宽度
ws.setColumnView(0, 30);
ws.setColumnView(1, 30);
ws.setColumnView(2, 30);
ws.setColumnView(3, 30);
ws.setColumnView(4, 30);
WritableFont wf = new
WritableFont(WritableFont.ARIAL,12,WritableFont.NO_BOLD,true);
WritableCellFormat wcf = new WritableCellFormat(wf);
wcf.setAlignment(Alignment.CENTRE);
Label label1 = new Label(0, 1, "车辆自编号",wcf);
Label label2 = new Label(1, 1, "车牌号",wcf);
Label label3 = new Label(2, 1, "速度",wcf);
Label label4 = new Label(3, 1, "时间",wcf);
Label label5 = new Label(4, 1, "位置",wcf);
ws.addCell(head);
ws.addCell(label1);
ws.addCell(label2);
ws.addCell(label3);
ws.addCell(label4);
ws.addCell(label5);
History his = null;
for (int i = 0; i < hisList.size(); i++)
{
his = hisList.get(i);
// 填充excel表数据
Label labela = new Label(0, i + 2,
String.valueOf(his.getNo()));
Label labelb = new Label(1, i + 2,
his.getHis_carid());
Label labelc = new Label(2, i + 2,
his.getHis_speed());
Label labeld = new Label(3, i + 2,
String.valueOf(his.getHis_time()));
Label labele = new Label(4, i + 2,
his.getHis_direction());
labela.setCellFormat(w);
labelb.setCellFormat(w);
labelc.setCellFormat(w);
labeld.setCellFormat(w);
labele.setCellFormat(w);
try {
ws.addCell(labela);
ws.addCell(labelb);
ws.addCell(labelc);
ws.addCell(labeld);
ws.addCell(labele);
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
}
web.write();
out.flush();
} catch (FileNotFoundException e1) {
e1.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}finally{
try {
web.close();
out.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}