package ais.mytest.web;/*** 功能：主要实现数据导出Excel和Word功能* 日期：2008-2-28*/import ais.basic.service.BasicUtil;import mons.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 bel;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);// 在表格末尾添加图片Imagepng=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 blocke.printStackTrace();}return null;}/*** 功能:将数据库中的数据导出excel* 日期:2008-3-28*/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,Co lour.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;} }。