package ais.mytest.web;
/**
* 功能：主要实现数据导出Excel和Word功能
* 日期：2012-3-19 */
import ais.basic.service.BasicUtil;
import ais.commons.file.service.FileService;
import ais.framework.web.BaseAction;
import ais.mytest.model.Employeer;
import ais.mytest.service.IemployeerService;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.text.DecimalFormat;
import jxl.write.NumberFormat;
import java.util.List;
import javax.servlet.ServletOutputStream;
import jxl.Cell;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.BoldStyle;
import jxl.format.Colour;
import jxl.write.Blank;
import jxl.write.DateFormat;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableImage;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
//import com.ibm.icu.text.SimpleDateFormat;
//import com.lowagie.text.Cell;
import com.lowagie.text.Document;
import com.lowagie.text.Font;
import com.lowagie.text.Image;
import com.lowagie.text.PageSize;
import com.lowagie.text.Paragraph;
import com.lowagie.text.Table;
import com.lowagie.text.pdf.BaseFont;
import com.lowagie.text.rtf.RtfWriter2;
import java.awt.Color;
import java.io.InputStream;
import java.io.OutputStream;
import org.apache.struts2.ServletActionContext;
public class ExportAction extends BaseAction{
/**
*
*/
private static final long serialVersionUID = -3951404867065653031L;
FileService fileService;
BasicUtil basicUtil;
IemployeerService iemployeerService;
Employeer employeer;
String filename=System.currentTimeMillis()+".doc";
public ExportAction() {}
/**
* 功能:将数据库中的数据导出word
* 日期:2008-2-29
* @return
*/
// 复制一份新的RTF文件
public String getNewFilePath() throws Exception {
InputStream ins = ServletActionContext.getServletContext().getResourceAsStream("/empty.doc");
byte[] b = new byte[ins.available()];
ins.read(b);
ins.close();
String newFileName = ServletActionContext.getServletContext().getRealPath("")+ "/"+filename;
OutputStream ous = new FileOutputStream(newFileName);
ous.write(b);
ous.close();
return newFileName;
}
// 将二进制流写到客户端
public void writeNewFile(String newFilePath, String downloadFileName)
throws Exception {
File f = new File(newFilePath);
InputStream ins = new FileInputStream(f);
byte[] b = new byte[ins.available()];
ins.read(b);
ins.close();
/* <option value="image/bmp">BMP</option>
<option value="image/gif">GIF</option>
<option value="image/jpeg">JPEG</option>
<option value="image/tiff">TIFF</option>
<option value="image/x-dcx">DCX</option>
<option value="image/x-pcx">PCX</option>
<option value="text/html">HTML</option>
<option value="text/plain">TXT</option>
<option value="text/xml">XML</option>
<option value="application/afp">AFP</option>
<option value="application/pdf">PDF</option>
<option value="application/pdf">zip</option>
<option value="application/rtf">RTF</option>
<option value="application/msword">MSWORD</option>
<option value="application/vnd.ms-excel">MSEXCEL</option>
<option value="application/vnd.ms-powerpoint">MSPOWERPOINT</option>
<option value="application/wordperfect5.1">WORDPERFECT</option>
<option value="application/vnd.lotus-wordpro">WORDPRO</option>
<option value="application/vnd.visio">VISIO</option>
<option value="application/vnd.framemaker">FRAMEMAKER</option>
<option value="application/vnd.lotus-1-2-3">LOTUS123</option>*/
/**
* 出现保存与打开对话框application/octet-stream,加上下面第二句是保存时能正确出现文件类型
*/
//getResponse().setContentType("application/octet-stream");
//getResponse().setHeader("Content-Disposition","attachment;filename=\""+ new String((downloadFileName).getBytes(),"ISO-8859-1") + "\";");
/**
* 直接打开word文档
*/
getResponse().setContentType("application/msword");
ServletActionContext.getResponse().getOutputStream().write(b);
//文件删除
if (f != null) {
f.delete();
}
}
/**
* @param em_name 姓名
* @param em_sex 性别
* @param em_age 年龄
* @param em_duty 职务
* @param em_pay 工资
* @param em_polity 政治面貌
* @param em_address 住址
*/
public void createWord(String eMname,String eMsex,int eMage,String eMduty, Double eMpay, String eMpolity,String eMaddress) throws Exception {
// 创建word文档
Document document = new Document(PageSize.A4);
// 输入word文档
String newFilePath = getNewFilePath();
RtfWriter2.getInstance(document, new FileOutputStream(newFilePath));
document.open();
// 中文字体
BaseFont bfChinese = BaseFont.createFont("STSongStd-Light","UniGB-UCS2-H", BaseFont.NOT_EMBEDDED);
Font fontChinese = new Font(bfChinese, 12, Font.BOLD);
Table table = new Table(4, 4);
Paragraph paragraph = new Paragraph("XX单位员工信息表", fontChinese);
paragraph.setAlignment(Paragraph.ALIGN_CENTER);
document.add(paragraph);
table.setBorderWidth(2);
table.setBorderColor(new Color(0, 0, 0));
table.setPadding(5);
table.setSpacing(0);
table.setAutoFillEmptyCells(true);
com.lowagie.text.Cell cell = new com.lowagie.text.Cell(new Paragraph("姓名", fontChinese));
table.addCell(cell);
cell = new com.lowagie.text.Cell(eMname);
table.addCell(cell);
cell = new com.lowagie.text.Cell(new Paragraph("性别", fontChinese));
table.addCell(cell);
cell = new com.lowagie.text.Cell(eMsex);
table.addCell(cell);
cell = new com.lowagie.text.Cell(new Paragraph("年龄", fontChinese));
table.addCell(cell);
cell = new com.lowagie.text.Cell(Integer.valueOf(eMage).toString());
table.addCell(cell);
cell = new com.lowagie.text.Cell(new Paragraph("政治面貌", fontChinese));
table.addCell(cell);
cell = new com.lowagie.text.Cell(eMpolity);
table.addCell(cell);
cell = new com.lowagie.text.Cell(new Paragraph("职务", fontChinese));
table.addCell(cell);
cell = new com.lowagie.text.Cell(eMduty);
table.addCell(cell);
cell = new com.lowagie.text.Cell(new Paragraph("工资", fontChinese));
table.addCell(cell);
cell = new com.lowagie.text.Cell(Double.valueOf(eMpay).toString());
//cell.setColspan(5);
table.addCell(cell);
cell = new com.lowagie.text.Cell(new Paragraph("住址", fontChinese));
table.addCell(cell);
cell = new com.lowagie.text.Cell(eMaddress);
cell.setColspan(3);
table.addCell(cell);
cell = new com.lowagie.text.Cell(new Paragraph("\n\n\n教育背景:\n\n\n", fontChinese));
cell.setVerticalAlignment(com.lowagie.text.Cell.ALIGN_CENTER);
cell.setHorizontalAlignment(com.lowagie.text.Cell.ALIGN_CENTER);
table.addCell(cell);
cell = new com.lowagie.text.Cell("");
cell.setVerticalAlignment(com.lowagie.text.Cell.ALIGN_CENTER);
cell.setHorizontalAlignment(com.lowagie.text.Cell.ALIGN_CENTER);
cell.setColspan(3);
table.addCell(cell);
cell = new com.lowagie.text.Cell(new Paragraph("\n\n\n工作经历\n\n\n",fontChinese));
cell.setVerticalAlignment(com.lowagie.text.Cell.ALIGN_CENTER);
cell.setHorizontalAlignment(com.lowagie.text.Cell.ALIGN_CENTER);
table.addCell(cell);
cell = new com.lowagie.text.Cell( "");
cell.setVerticalAlignment(com.lowagie.text.Cell.ALIGN_CENTER);
cell.setHorizontalAlignment(com.lowagie.text.Cell.ALIGN_CENTER);
cell.setColspan(3);
table.addCell(cell);
cell = new com.lowagie.text.Cell(new Paragraph("\n\n\n\n\t\t\t\t\t（盖章） 年 月 日\n\n\n",fontChinese));
cell.setVerticalAlignment(com.lowagie.text.Cell.ALIGN_CENTER);
cell.setHorizontalAlignment(com.lowagie.text.Cell.ALIGN_CENTER);
cell.setColspan(4);
table.addCell(cell);
document.add(table);
// 在表格末尾添加图片
Image png=Image.getInstance(ServletActionContext.getServletContext().getRealPath("")+ "/png-0662.png");
document.add(png);
// 分页
document.newPage();
document.close();
}
public String exportword(){
String eMname="李小明";
String eMsex="男";
int eMage=25;
String eMduty="经理";
Double eMpay=2580.78;
String eMpolity="党员";
String eMaddress="天津市清河县大柳营镇";
try {
String newFilePath1 = getNewFilePath();
createWord(eMname,eMsex,eMage,eMduty,eMpay,eMpolity,eMaddress);
writeNewFile(newFilePath1, filename);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
/**
* 功能:将数据库中的数据导出excel
* 日期:2012-3-20 */
public String exportexcel(){
getResponse().setContentType("application/vnd.ms-excel");
File f1 = null;
//得到服务器路径
String path = getRequest().getSession().getServletContext().getRealPath("")+File.separator;
//文件名生成
String filename="temp";
filename=System.currentTimeMillis()+".xls";
File f = new File(path+"Book2.xlt");// 定义一个模板，放在服务器相应路径下．
Workbook workbook = null;
WritableWorkbook writableWorkbook = null;
String workwheet="工作表名";
try {
/**
* 使用文件输入输出流读取Excel模板,重新写入新文件,就是为了读到其EXCEL格式.
* 所以在服务器相应文件,必须有一个空Excel文件,如:Book1.xls
*/
FileInputStream fis = new FileInputStream(f);
byte[] b = new byte[fis.available()];
fis.read(b);
fis.close();
f1 = new File(path+filename);
FileOutputStream fos = new FileOutputStream(f1);
fos.write(b);
fos.close();
workbook = Workbook.getWorkbook(f1);
ServletOutputStream os = getResponse().getOutputStream();
//.createWorkbook(os, workbook);
writableWorkbook = Workbook.createWorkbook(f1);
WritableSheet ws = writableWorkbook.getSheet(0);
//添加第四个工作表
WritableSheet sheet = writableWorkbook.createSheet(workwheet,4);
//定义样式:包括字体样式
WritableFont wf1 = new WritableFont(WritableFont.TIMES,18,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.RED);
WritableCellFormat wcf1 = new WritableCellFormat(wf1);
//背景样式
wcf1.setBackground(Colour.YELLOW2);
//对齐方式
wcf1.setAlignment(Alignment.CENTRE);
wcf1.setVerticalAlignment(VerticalAlignment.CENTRE);
WritableFont wf2 = new WritableFont(WritableFont.TIMES,11,WritableFont.NO_BOLD);;
WritableCellFormat wcf2 = new WritableCellFormat(wf2);
wcf2.setAlignment(Alignment.CENTRE);
//边框线设置
wcf2.setBorder(Border.ALL,BorderLineStyle.THIN);
WritableFont wf3 = new WritableFont(WritableFont.TIMES,12,WritableFont.BOLD);
WritableCellFormat wcf3 = new WritableCellFormat(wf3);
wcf3.setAlignment(Alignment.CENTRE);
wcf3.setBorder(Border.ALL,BorderLineStyle.THIN);
//显示数字形式
NumberFormat wf5=new NumberFormat("#0.00");
WritableCellFormat wcf5 = new WritableCellFormat(wf5);
wcf5.setAlignment(Alignment.CENTRE);
wcf5.setBorder(Border.ALL,BorderLineStyle.THIN);
//添加带有formatting的DateFormat对象
DateFormat df = new DateFormat("yyyy-MM-dd hh:mm:ss");
WritableCellFormat wcfdf = new WritableCellFormat(df);
wcfdf.setAlignment(Alignment.CENTRE);
wcfdf.setBorder(Border.ALL,BorderLineStyle.THIN);
//数字格式显示两位小数
DecimalFormat dfformat = new DecimalFormat("###,##0.00");
//输出表头
Label l = null;
int k = 0;
//ws.mergeCells(0,k,7,k); //合并单元格,表示合并第0行第0列到第0行第7列
/**
* 功能：下面5句实现在模板中任何单元格定义名称EM_name,通过findCellByName查找
* 如果找到，则将获取标记单元格的位置，并将内容填入，找不到，不管．
* 实现了程序充许可以任何定义有限范围的模板定义．
* 日期：2008-2-28
*
*/
Cell cl=writableWorkbook.findCellByName("EM_name");
if(cl!=null){
//System.out.println(cl.getContents()+"找到了"+cl.getColumn()+"---"+cl.getRow());
l = new Label(cl.getColumn(),cl.getRow(), "网通审计人员统计报表", wcf1); //将内容填入
ws.addCell(l);
ws.setRowView(k,700); //设置单元格宽度,k表示行,500表示高度
}
k++;
ws.mergeCells(0,k,1,k);
l = new Label(0,k, "填报单位：", wcf3);
ws.addCell(l);
ws.mergeCells(2,k,5,k);
l = new Label(2,k, "审计部一处", wcf3);
ws.addCell(l);
l = new Label(6,k, "日期：", wcf3);
ws.addCell(l);
//l = new Label(7,k, "2008-03-15", wcfdf);
DateTime lt = new DateTime(7,k,new java.util.Date(),wcfdf);
ws.addCell(lt);
k++;
l = new Label(0,k, "序号", wcf3);
ws.addCell(l);
l = new Label(1,k, "姓名", wcf3);
ws.addCell(l);
l = new Label(2,k, "性别", wcf3);
ws.addCell(l);
l = new Label(3,k, "年龄", wcf3);
ws.addCell(l);
l = new Label(4,k, "职务", wcf3);
ws.addCell(l);
l = new Label(5,k, "工资", wcf3);
ws.addCell(l);
l = new Label(6,k, "政治面貌", wcf3);
ws.addCell(l);
l = new Label(7,k, "地址", wcf3);
ws.addCell(l);
ws.setColumnView(7,30); //设置单元格宽度,7表示列,30表示宽度
//输出内容
List<Employeer> emlist=iemployeerService.getEmployeerAll("from Employeer");
for(int i=0;i<emlist.size();i++)
{
k++;
l = new Label(0,k, Integer.valueOf(emlist.get(i).getId()).toString(), wcf2);
ws.addCell(l);
l = new Label(1,k, emlist.get(i).getEm_name(), wcf2);
ws.addCell(l);
l = new Label(2,k, emlist.get(i).getEm_sex(), wcf2);
ws.addCell(l);
l = new Label(3,k, Integer.valueOf(emlist.get(i).getEm_age()).toString(), wcf2);
ws.addCell(l);
l = new Label(4,k, emlist.get(i).getEm_duty(), wcf2);
ws.addCell(l);
l = new Label(5,k, dfformat.format(emlist.get(i).getEm_pay()).toString(), wcf5);
ws.addCell(l);
l = new Label(6,k, emlist.get(i).getEm_polity(), wcf2);
ws.addCell(l);
l = new Label(7,k, emlist.get(i).getEm_address(), wcf2);
ws.addCell(l);
}
//添加图片,只支持png格式,注意在服务器启动时，第一次将png图片放进去，读取时是不显示的．需得启服务器．
ws.mergeCells(5, 5, 6, 10);
WritableImage wbi=new WritableImage(5, 5, 6, 10, new File(path+"png-0662.png"));
ws.addImage(wbi);
writableWorkbook.write();
//得到工作表行数
int cnt = ws.getRows();
System.out.println("表行数有:"+cnt);
//工作表某一单元格内容
Cell cell = ws.getCell(0,0);
System.out.println("0行0列内容是:" + cell.getContents());
} catch (Exception e) {
e.printStackTrace();
} finally {
if (writableWorkbook != null)
try {
writableWorkbook.close();
} catch (Exception e) {
// Exception
}
if (f1.exists()) {
f1.delete();
System.out.println("文件已经删除！");
} else {
/*try {
f1.createNewFile();
} catch (IOException e) {
e.printStackTrace();
}
System.out.println("文件已经创建！");*/
}
}
return null;
}
public BasicUtil getBasicUtil() {
return basicUtil;
}
public void setBasicUtil(BasicUtil basicUtil) {
this.basicUtil = basicUtil;
}
public FileService getFileService() {
return fileService;
}
public void setFileService(FileService fileService) {
this.fileService = fileService;
}
public IemployeerService getIemployeerService() {
return iemployeerService;
}
public void setIemployeerService(IemployeerService iemployeerService) {
this.iemployeerService = iemployeerService;
}
public Employeer getEmployeer() {
return employeer;
}
public void setEmployeer(Employeer employeer) {
this.employeer = employeer;
}
}
