package com.report; import java.awt.Dimension; import com.report.CustCell; import javax.swing.JDialog; import javax.swing.JScrollPane; import java.io.ByteArrayInputStream; import java.io.ByteArrayOutputStream; import java.io.File; import java.io.OutputStream; import java.awt.*; import java.awt.event.*; import javax.swing.*; import javax.swing.table.*; import javax.swing.event.*; import javax.swing.border.*; import java.util.*; import com.coolservlets.beans.method.genMethod; import jxl.*; import jxl.CellFormat.*; import com.coolservlets.beans.method.*; import jxl.write.biff.*; import jxl.write.*; import jxl.*; import java.io.*; import jxl.format.PageOrientation; import jxl.format.PaperSize; import jxl.write.Label; import jxl.Workbook; import jxl.write.WritableSheet; import jxl.write.WritableWorkbook; import java.io.File; /* * Created on 24/07/2003 * * To change the template for this generated file go to * Window&Preferences&Java&Code Generation&Code and Comments */ /** * @author kenta * * To change the template for this generated type comment go to * Window&Preferences&Java&Code Generation&Code and Comments */ public class ExcelReport extends JDialog { //每页行数 private int aPageLineCount; //需要动态设置的元素：key:替换成数据的标记；value:为Vector; private Properties clusters = new Properties(); //报表表头 private String ReportTitle; //报表种类category :"CARD" 卡片式；"REPORT" 记录格式 private String category; //卡片式报表数据 private String[] carddata; //记录格式报表数据 private String[][] ReportData; //替换成数据的标记 private String covertag; //报表模板文件的二进制流，通过模板文件将模板中的表头在创建一个新的打印输出的文件 private byte [] ExeclModule = null; //外部文件名 //private String printflm; //全局表字符串，用来保存上次报表的字符串 private byte [] excelreport = null; //新创建报表的sheet 名 private String SheetName = null; //创建的报表模板 HashMap HmReportModule = new HashMap(); //创建的报表的数据列数 int DataCloums = 0; //有效单元格的键值 Vector vModuleKey = new Vector(); //设置数据打印内容的字体！缺省的是 8号字 int DataFontSize = 8; //设置数据打印的行高 int DataLineHeight = 225; //设置数据打印的列宽 int DataColumWidth = 3; //报表表头的行数 int TitleRows = 0; //创建的二进制流的报表模板,该模板是将模板整体完整的读入并创建与输出完全一致的模板 private byte [] BytesExcelModule = null; public int getDataColumWidth() { return this.DataColumWidth; } public void setDataColumWidth(int m_DataColumWidth) { this.DataColumWidth = m_DataColumWidth; } public ExcelReport() { super(); } public byte [] createExcelReport() throws Exception{ byte [] reportbytes = null; if(ExeclModule != null){ //创建的报表模板 HmReportModule = createReportModule(); //System.out.println("createReportModule is end"); reportbytes = writeToExcel(); }else{ System.out.println("没有创建的报表模板，请先创建的报表模板！"); } return reportbytes; } private byte[] writeToExcel() throws Exception { byte[] filebyte = null; //int ArrayLength = 11; if((ReportData != null)&&(ReportData.length!=0)) { System.out.println("ReportData[0].length = " + ReportData[0].length + ", DataCloums =" + DataCloums); if (ReportData[0].length == DataCloums) { try { ByteArrayOutputStream baos = new ByteArrayOutputStream(); // 设置WorkbookSettings，获得excel文件中的工作薄 WorkbookSettings wss = new WorkbookSettings(); wss.setEncoding("iso-8859-1"); WritableWorkbook workbook = Workbook.createWorkbook((OutputStream) baos, wss); // 获得工作薄的数量 WritableSheet tablesheet = workbook.createSheet(SheetName,0); for (int i = 0; i < 100; i++) { tablesheet.setColumnView(i, DataColumWidth); } tablesheet.getSettings().setPrintTitlesRow(0, 2); this.geneHeadFooter(tablesheet); //System.out.println("geneHeadFooter is end"); this.geneTableHeader(tablesheet); //System.out.println("geneTableHeader is end"); this.geneContent(tablesheet); //System.out.println("geneContent is end"); workbook.write(); workbook.close(); filebyte = baos.toByteArray(); } catch (Exception e) { e.printStackTrace(); } } } return filebyte; } public void geneHeadFooter(WritableSheet tablesheet) { tablesheet.getSettings().setLeftMargin(0.5d); tablesheet.getSettings().setRightMargin(0.5d); //设置纸张 tablesheet.setPageSetup(PageOrientation.LANDSCAPE.LANDSCAPE,PaperSize.A4, 0.5d, 0.5d); //设置缩放调整宽 tablesheet.getSettings().setFitWidth(1); // 设置页眉页脚 HeaderFooter header = new HeaderFooter(); header.getCentre().setFontSize(16); header.getCentre().append("&B" + ReportTitle); tablesheet.getSettings().setHeader(header); HeaderFooter footer = new HeaderFooter(); footer.getCentre().append("第 "); footer.getCentre().appendPageNumber(); footer.getCentre().append(" 页 共 "); footer.getCentre().appendTotalPages(); footer.getCentre().append(" 页"); tablesheet.getSettings().setFooter(footer); } public void geneTableHeader(WritableSheet tablesheet) throws Exception { int loop = vModuleKey.size(); Vector vLeaf = new Vector(); for(int i=0;i 3500) { DataLineHeight = 3500; } tablesheet.setRowView(rownum,DataLineHeight); }//for(int i=0;i 0){ if(lastkey.length() > 0){ int endColums = j -1; CustCell custCell= (CustCell)hmReport.get(lastkey); //将结束列分析出来 String ts = custCell.getContents(); custCell.setStopCloums(endColums); hmReport.put(lastkey,custCell); //System.out.println("contents="+ts+",endColums="+Integer.toString(endColums)+","+Integer.toString(row)); } String putkey = Integer.toString(row) + "@@" + Integer.toString(j) + "@@"; CustCell custCell= new CustCell(); //设置起始列号 custCell.setStartCloums(j); //设置起始行号 custCell.setStartRows(row); //设置单元格内容 custCell.setContents(contents); //设置单元格字体大小 int fontsize = cellEach.getCellFormat().getFont().getPointSize(); custCell.setFontSize(fontsize); //设置单元格加重程度 int boldWeight = cellEach.getCellFormat().getFont().getBoldWeight(); custCell.setBoldWeight(boldWeight); //设置批注 if(isHasComment(cellEach)) { String comment=cellEach.getCellFeatures().getComment().toString().trim(); custCell.setComment(comment); } //System.out.println("contents 0="+contents+",endColums="+Integer.toString(j)); hmReport.put(putkey,custCell); vModuleKey.addElement(new String(putkey)); lastkey = putkey; } } if(lastkey.length() > 0){ int endColums = MaxColumns -1; CustCell custCell= (CustCell)hmReport.get(lastkey); //将结束列分析出来 String ts = custCell.getContents(); custCell.setStopCloums(endColums); hmReport.put(lastkey,custCell); //System.out.println("contents end="+ts+",endColums="+Integer.toString(endColums)+","+Integer.toString(row)); } } //初步分析统计报表的格式 End //开始分析该单元格是否是最底层的叶子单元格 Begin int loop = vModuleKey.size(); Vector vLeaf = new Vector(); for(int i=0;i= startCloums && fstartCloums < stopCloums){ //说明该单元格不是叶子节点 leafIs = false; //获得当前单元格的终止列列号 int fstopCloums = fcustCell.getStopCloums(); //说明是最后一个单元格，终止列号校正 if(fstopCloums > stopCloums){ fcustCell.setStopCloums(stopCloums); hmReport.put(findkey,fcustCell); String fcontents = custCell.getContents(); String contents = fcustCell.getContents(); //System.out.println("fcontents ="+fcontents+",contents="+contents+","+Integer.toString(stopCloums)); } } }//startRows+1 == fstartRows }// !getkey.equalsIgnoreCase(findkey) }//for end int stopRows = (leafIs)?MaxRows-1:startRows; custCell.setStopRows(stopRows); custCell.setLeafIs(leafIs); hmReport.put(getkey,custCell); if(leafIs){ String putstr = getkey + Integer.toString(startCloums)+"@@"; vLeaf.addElement(new String(putstr)); } } //开始分析该单元格是否是最底层的叶子单元格 End //对叶子单元格按照起始列进行排序 Begin vLeaf = genMethod.sortValueStr(vLeaf,"@@",2,"1",null,0); DataCloums = vLeaf.size(); for(int i=0;i -1){ System.out.println(cstr); str = com.coolservlets.beans.method.genMethod.replaceValueStr(str,"data[0]<","明细说明[0]<"); } FileOutputStream fos = new FileOutputStream(outFile); fos.write(str.getBytes("iso8859-1")); fos.close(); fis.close(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } /** * 由一个已经创建的电子表格模板，创建新的电子表格，并替换其中的单元格内容 * @param args */ public static void main05(String[] args) { //TODO Auto-generated method stub try { //byte[] contentBytes = null; //ByteArrayInputStream input=new ByteArrayInputStream(contentBytes); File inFile = new File("D:\\sqlrun\\app\\report\\excelreport.xls"); ByteArrayOutputStream baos = new ByteArrayOutputStream(); File outFile = new File("D:\\sqlrun\\app\\report\\excelreportnew.xls"); //jxl.Workbook rw = jxl.Workbook.getWorkbook(input); jxl.Workbook rw = jxl.Workbook.getWorkbook(inFile); jxl.write.WritableWorkbook wwb = Workbook.createWorkbook((OutputStream) baos,rw); jxl.write.WritableSheet ws = wwb.getSheet(0); jxl.write.WritableCell wc = ws.getWritableCell(1,0); //判断单元格的类型, 做出相应的转化 if(wc.getType() == CellType.LABEL){ Label l = (Label)wc; l.setString("替换字符内容"); } //写入Excel对象 wwb.write(); //关闭可写入的Excel对象 wwb.close(); //关闭只读的Excel对象 rw.close(); FileOutputStream fos = new FileOutputStream(outFile); fos.write(baos.toByteArray()); fos.close(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } public static void main07(String[] args) { try { //创建报表模板的二进制文件流 java.io.File modulefile=new java.io.File("D:\\sqlrun\\app\\report\\excelreport.xls"); FileInputStream fin=new FileInputStream(modulefile); byte [] excelModuleBytes=new byte[fin.available()]; fin.read(excelModuleBytes); String[] value = new String[1]; value[0] = "2010-3-13"; excelModuleBytes = replaceModuleByValue(excelModuleBytes,value); File outFile = new File("D:\\sqlrun\\app\\report\\excelreportnew.xls"); FileOutputStream fos = new FileOutputStream(outFile); fos.write(excelModuleBytes); fos.close(); }catch (Exception ex) { ex.printStackTrace(); } } public static void main08(String[] args) { try { ExcelReport excelReport = new ExcelReport(); //构建模板文件 excelReport.setBytesExcelModule("ImpTaskMonth.xls"); String aligt[][] = {{"0","CENTER"}}; String[][] data = {{"1","厂长办公室","","厂部重点任务","做好厂部重点工作的督察工作。","2012-06-30","0",""},{"1","厂长办公室","","厂部重点任务","做好厂部重点工作的督察工作。","2012-06-30","0",""}}; byte[] ExcelDataReport = excelReport.getCollectBytes(data,"3,0;",aligt); System.out.println("ExcelDataReport size="+ExcelDataReport.length); }catch (Exception ex) { ex.printStackTrace(); } } /** * 用值替换模板中有固定标志的值（***） * @param contentBytes :二进制文档模板文件 * @param value：替换模板中固定标志的值 * @return */ public static byte[] replaceModuleByValue(byte[] contentBytes,String[] value) { try { ByteArrayInputStream hinput=new ByteArrayInputStream(contentBytes); jxl.Workbook wb = jxl.Workbook.getWorkbook(hinput); //Workbook wb = Workbook.getWorkbook(new File("D:\\ibm\\SMP\\maximo\\reports\\pluck\\module\\ImpTaskMonth.xls")); WorkbookSettings settings = new WorkbookSettings (); settings.setWriteAccess(null); ByteArrayOutputStream baos = new ByteArrayOutputStream(); //WritableWorkbook workbook = Workbook.createWorkbook(new File("D:\\ibm\\SMP\\maximo\\reports\\pluck\\module\\DataImpTaskMonth.xls"),wb,settings); WritableWorkbook wwb = Workbook.createWorkbook((OutputStream)baos,wb,settings); WritableSheet ws = wwb.getSheet(0); ws.getSettings().setSelected(true); //获取Sheet表中所包含的总列数 int MaxColumns = ws.getColumns(); //获取Sheet表中所包含的总行数，示例： int MaxRows = ws.getRows(); int iR = 0; for(int row=0;row -1){ String st = genMethod.replaceValueStr(ls,flag,value[iR]); l.setString(st); iR++; } } } } //写入Excel对象 wwb.write(); //关闭可写入的Excel对象 wwb.close(); //关闭只读的Excel对象 wb.close(); return baos.toByteArray(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); return null; } } /** * 用值替换模板中有固定标志的值（***） * @param contentBytes :二进制文档模板文件 * @param value：替换模板中固定标志的值 * @return */ public static byte[] replaceModuleByValue(byte[] contentBytes,String[] value,String modflag) { try { ByteArrayInputStream hinput=new ByteArrayInputStream(contentBytes); jxl.Workbook wb = jxl.Workbook.getWorkbook(hinput); //Workbook wb = Workbook.getWorkbook(new File("D:\\ibm\\SMP\\maximo\\reports\\pluck\\module\\ImpTaskMonth.xls")); WorkbookSettings settings = new WorkbookSettings (); settings.setWriteAccess(null); ByteArrayOutputStream baos = new ByteArrayOutputStream(); //WritableWorkbook workbook = Workbook.createWorkbook(new File("D:\\ibm\\SMP\\maximo\\reports\\pluck\\module\\DataImpTaskMonth.xls"),wb,settings); WritableWorkbook wwb = Workbook.createWorkbook((OutputStream)baos,wb,settings); WritableSheet ws = wwb.getSheet(0); ws.getSettings().setSelected(true); //开始执行 String[] aRow = genMethod.split(modflag,";"); int loop = aRow.length; for(int i=0;i 0){ if(setAlig.equalsIgnoreCase("LEFT")){ cell.setCellFormat(smallformatleft); }else{ if(setAlig.equalsIgnoreCase("RIGHT")){ cell.setCellFormat(smallformatright); }else{ if(setAlig.equalsIgnoreCase("CENTER")){ cell.setCellFormat(smallformatcenter); }else{ cell.setCellFormat(smallformatleft); } } } }else{ cell.setCellFormat(smallformatleft); } sheet.addCell(cell); } } workbook.write(); workbook.close(); //关闭只读的Excel对象 wb.close(); return baos.toByteArray(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); try { //throw new Exception("SQL: \"" + "\" 执行出错，原因是：" + e.getMessage()); }catch (Exception ioexception) { IllegalArgumentException illegalargumentexception = new IllegalArgumentException(ioexception.toString()); Fake(illegalargumentexception,ioexception); throw illegalargumentexception; } return null; } //return BytesExcelModule; } /** * 用值替换模板中有固定标志的值（***） * @param contentBytes :二进制文档模板文件 * @param value：替换模板中固定标志的值 * @return */ public static String getValuesByBytes(byte[] contentBytes,String locations) { String rs = ""; try { ByteArrayInputStream input=new ByteArrayInputStream(contentBytes); WorkbookSettings workbookSettings=new WorkbookSettings(); workbookSettings.setEncoding("ISO-8859-1"); Workbook workbook=Workbook.getWorkbook(input,workbookSettings); Sheet sheet=workbook.getSheet(0); String[] aRow =genMethod.split(locations,";"); int loop = aRow.length; //System.out.println("loop="+loop); for(int i=0;iConsumes the given property and returns the value. * * @param properties Properties table * @param key Key of the property to retrieve and remove from * the properties table * @return Value of the property, or null if not found */ private String consume(java.util.Properties p, String key) { String s = null; if ((p != null) && (key != null)) { // Get the value of the key s = p.getProperty(key); // If found, remove it from the properties table if (s != null) { p.remove(key); } } return s; } /// /// 将流保存为文件 /// /// private boolean SaveStreamToFile(byte[] excelModuleBytes,String path) { try { FileOutputStream fos = new FileOutputStream(path); fos.write(excelModuleBytes); fos.close(); }catch (Exception ex) { ex.printStackTrace(); } return true; } public static Throwable Fake(Throwable throwable, Throwable throwable1) { try { throwable.getClass().getMethod("initCause", new Class[] { java.lang.Throwable.class }).invoke(throwable, new Object[] { throwable1 }); } catch (Exception ioexception) { IllegalArgumentException illegalargumentexception = new IllegalArgumentException(ioexception.toString()); Fake(illegalargumentexception,ioexception); throw illegalargumentexception; } return throwable; } }