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.FileOutputStream; 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 ExcelReportSheets 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; //模板的 sheet 页编号 int SheetNumber = 0; //记录填写数据记录的起始列号 private int[] MergerColum; //记录填写数据记录的起始行号 private int MergerSartRow; //报表模板文件的二进制流 private byte [] ExeclModule = null; //报表模板文件的Excel 文件 WritableWorkbook Sheetwwb = null; //报表模板文件的ByteArrayOutputStream 文件 ByteArrayOutputStream baos = new ByteArrayOutputStream(); //外部文件名 //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 TitleRows = 0; //创建的二进制流的报表模板 private byte [] BytesExcelModule = null; //多个excel 合并为一个 excel 的 sheet WritableSheet MergerSheet = null; //多个excel 合并为一个 excel 的 sheet 上创建 新行的起始行号 int MergerStartRow = 0; //某个 Sheet 上创建 数据行的起始行号 int SheetStartRow = 0; //多个excel 合并为一个 excel 的 sheet 填写完数据的最后行行号 int MergerStopRow = 0; public ExcelReportSheets() { 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; } public WritableSheet createExcelReportMerger() throws Exception{ WritableSheet SingleSheet = null; if(ExeclModule != null){ //创建的报表模板 //HmReportModule = createReportModule(); //System.out.println("createReportModule is end"); SingleSheet = writeToExcelMerger(); }else{ System.out.println("没有创建的报表模板，请先创建的报表模板！"); } return SingleSheet; } public void createExcelReportSheet() throws Exception{ WritableSheet SingleSheet = null; if(ExeclModule != null){ //创建的报表-在对应的sheet 页上添加数据 writeToExcelSheet(); }else{ System.out.println("没有创建的报表模板，请先创建的报表模板！"); } //return SingleSheet; } public byte[] getSheetReportExcel() throws Exception { Sheet[] sheets = Sheetwwb.getSheets(); int mloop = sheets.length; for(int i = 1; i < mloop; i++) { WritableSheet tablesheet = Sheetwwb.getSheet(i); tablesheet.getSettings().setSelected(false); } Sheetwwb.write(); Sheetwwb.close(); byte [] ExcelReport = baos.toByteArray(); return ExcelReport; } 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, 3); } 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; } private WritableSheet writeToExcelMerger() throws Exception { if((ReportData != null)&&(ReportData.length!=0)) { System.out.println("ReportData[0].length = " + ReportData[0].length + ", DataCloums =" + DataCloums); if (ReportData[0].length == DataCloums) { try { this.geneHeadFooterMerger(MergerSheet); //System.out.println("geneHeadFooter is end"); this.geneTableHeaderMerger(MergerSheet); //System.out.println("geneTableHeader is end"); this.geneContentMerger(MergerSheet); } catch (Exception e) { e.printStackTrace(); } } } return MergerSheet; } private void writeToExcelSheet() throws Exception { if((ReportData != null)&&(ReportData.length!=0)) { System.out.println("ReportData[0].length = " + ReportData[0].length + ", DataCloums =" + DataCloums); if (ReportData[0].length == DataCloums) { try { //this.geneHeadFooterMerger(MergerSheet); //System.out.println("geneHeadFooter is end"); //this.geneTableHeaderMerger(MergerSheet); //System.out.println("geneTableHeader is end"); //this.geneContentMerger(MergerSheet); this.geneContentSheet(); } catch (Exception e) { e.printStackTrace(); } } } //return MergerSheet; } 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 geneHeadFooterMerger(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 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(); //System.out.println("loop =" + loop); for(int i=0;i 0){ String first = contents.substring(0, 1); if(first.equalsIgnoreCase(covertag)){ //MergerColum[iIndex] = j; //System.out.println("合并单元格的列号 =" + j); vMergerColum.addElement(new String(Integer.toString(j))); iIndex++; } } } MergerColum = new int[iIndex+1]; for(int f=0;f -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;i -1){ String st = genMethod.replaceValueStr(ls,flag,value[iR]); l.setString(st); iR++; } } } } } //分析报表某个sheet 页模板 public void AnalysisSheetBytes() { if(ExeclModule != null){ //创建的报表模板 try { HmReportModule = createReportSheetModule(); }catch(Exception e) { e.printStackTrace(); } } } //模板的 sheet 页编号 public void setSheetNumber(int p_sheetnum){ this.SheetNumber = p_sheetnum; } //记录填写数据记录的起始行号 public int getMergerSartRow() { return this.MergerSartRow; } //记录填写数据记录的起始行号 public int getSheetSartRow() { return this.SheetStartRow; } //报表模板文件名 public void setBytesExcelModule(String ExcelModule) { try { //规定报表模板必须放在maximo 的resources\defaults String sReportpath = loadConfigValue("com/config/report.cfg","reportpath"); String modflm = sReportpath+ System.getProperties().getProperty("file.separator")+ExcelModule; //创建报表模板的二进制文件流 java.io.File modulefile=new java.io.File(modflm); FileInputStream fin=new FileInputStream(modulefile); byte [] contentBytes=new byte[fin.available()]; fin.read(contentBytes); this.BytesExcelModule = contentBytes; }catch(Exception e) { e.printStackTrace(); } } //新报表的sheet 名 public void setSheetName(String vSheetname) { this.SheetName = vSheetname; } //打印数据行内容字体大小 public void setDataFontSize(int dataFontSize) { this.DataFontSize = dataFontSize; } //打印数据行内容行的行高 public void setDataLineHeight(int dataLineHeight) { this.DataLineHeight = dataLineHeight; } //多个excel 合并为一个 excel 的 sheet //WritableSheet MergerSheet = null; public void setMergerSheet(WritableSheet vMergerSheet) { this.MergerSheet = vMergerSheet; } //多个excel 合并为一个 excel 的 sheet 上创建 新行的起始行号 //int MergerStartRow = 0; public void setMergerStartRow(int vMergerStartRow) { this.MergerStartRow = vMergerStartRow; } //多个excel 合并为一个 excel 的 sheet 填写完数据的最后行行号 //int MergerStopRow = 0; public int getMergerStopRow() { return this.MergerStopRow; } //DataCloums public int getDataCloums() { return this.DataCloums; } //读出配置文件 public String loadConfigValue(String name,String key) throws Exception { String rc = null; // Get our class loader ClassLoader cl = getClass().getClassLoader(); // Attempt to open an input stream to the configuration file. // The configuration file is considered to be a system // resource. java.io.InputStream in; if (cl != null) { in = cl.getResourceAsStream(name); } else { in = ClassLoader.getSystemResourceAsStream(name); } // If the input stream is null, then the configuration file // was not found if (in == null) { throw new Exception("configuration file '" + name + "' not found"); } else { try { java.util.Properties m_ConfigProperties = new java.util.Properties(); // Load the configuration file into the properties table m_ConfigProperties.load(in); // Got the properties. Pull out the properties that we // are interested in rc = consume(m_ConfigProperties,key); } finally { // Always close the input stream if (in != null) { try { in.close(); } catch (Exception ex) { } } } } return rc; } /** *

Consumes 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; } }