---

ExcelSheet.java

Go to the documentation of this file.

package net.threebit.utils.sosc;

/*
        Copyright 2003 Shawn Deleurme
        Copyright 2003 Kevin O'Donnell

        This program is free software; you can redistribute it and/or modify
        it under the terms of the GNU General Public License as published by
        the Free Software Foundation; either version 2 of the License, or
        (at your option) any later version.

        This program is distributed in the hope that it will be useful,
        but WITHOUT ANY WARRANTY; without even the implied warranty of
        MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
        GNU General Public License for more details.

        You should have received a copy of the GNU General Public License
        along with this program; if not, write to the Free Software
        Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
*/

import javax.xml.transform.*;
import javax.xml.transform.stream.*;
import java.sql.*;
import java.io.*;
import java.util.*;
import javax.servlet.http.*;
import javax.servlet.jsp.*;

public class ExcelSheet {

        public static final int COLUMN = 0; 
        public static final int ROW = 1; 

        private int columns = 0 ; 
        private StringWriter sw = new StringWriter();
        private PrintWriter pw = new PrintWriter(sw);
        private String sheetName = ""; 
        private Random random = new Random();
        private List items = new ArrayList();

        // internally, represent as char array. 
        // The COLUMN is cast to a char and the row is converted to a char. 
        private int xy[] = new int [2]; 
        private int start[] = new int [2]; 
        private int end[] = new int [2]; 

        public String width = "99%";
        public String height = "500px";
        public String fontName = "Verdana";
        public String fontSize = "8";

        private boolean rawWrite = false;

        public ExcelSheet (String sheetName, String title) throws Exception  {
                sheetName = sheetName.replaceAll(" ","_");
                this.sheetName = sheetName; 
                setTitle(title);
        }
        public void setXY ( int width, int height ) {
                this.width = width+"%"; 
                this.height = height+"px"; 
        } 

        public void render (OutputStream out) throws Exception {
                // Use a printwriter for writing to the stream
                PrintWriter ow = new PrintWriter(new OutputStreamWriter(out));
                ow.print("<object ");
                ow.print(" id=\""+sheetName+"\" "); 
                ow.print(" classid=\"CLSID:0002E510-0000-0000-C000-000000000046\" ");  
                ow.print(" style=\"width: "+width+"; height: "+height+";\">");
                ow.println("</object>"); 
                ow.println("");
                ow.println(sw.getBuffer().toString());
                ow.println("");
                ow.println("<script language=\"vbs\">");
                for (Iterator i = items.iterator(); i.hasNext(); ) {
                        ow.println("  " + i.next() + "()");
                }
                ow.println("</script>");
                ow.println("");
                ow.flush();
        }

        public void setTitle ( String title ) throws Exception {
                // Write the relevant data to the internal buffer
                title = title.replaceAll(" ","_");
                rawWrite(".TitleBar.caption = \"" + title + "\"");
        }

        public void autofit() throws Exception {
                pw.println(sheetName+".ActiveSheet.Range(\"A1:AZ2000\").AutoFitColumns" ); 
        }

        public void bold()  throws Exception {
                pw.println (sheetName+".ActiveSheet.Cells.Range(\""+xy[ROW]+":"+xy[ROW]+"\").Font.Bold = True"); 
        }

        public void bold(String range)  throws Exception {
                pw.println (sheetName+".ActiveSheet.Cells.Range(\""+range+"\").Font.Bold = True"); 
        }

        public void font(String font, int size, String range)  throws Exception {
                pw.println (sheetName+".ActiveSheet.Cells.Range(\""+range+"\").Font.Name = \""+font+"\"");  
                pw.println (sheetName+".ActiveSheet.Cells.Range(\""+range+"\").Font.Size = "+size+"");  
        }
        
        public void printCell ( Object o ) throws Exception {
                pw.println (sheetName+".ActiveSheet.Cells("+nextColumn()+").Value = \""+o.toString()+"\"" ) ;  
        }


  public int drawTable(String tableName, List rows, String startxy ) throws Exception  {
    return drawTable(tableName, rows, startxy, true);

  }
  public int drawTable(String tableName, List rows, String startxy, boolean first ) throws Exception  {
    if ( first  ) items.add( tableName ) ;
                tableName = tableName.replace(' ','_');         
                start[COLUMN] = xy[COLUMN] = ((int) startxy.charAt( COLUMN )) - 64;  
                start[ROW] = xy[ROW] = Integer.parseInt(startxy.substring(1));  

                pw.println("<script language=vbs> \nSub "+tableName +"() ") ; 
                pw.println("With " +sheetName + ".ActiveSheet");        
                pw.println(".Columns.Font.Name = \"" + fontName + "\"");  
                pw.println(".Columns.Font.Size = "+ fontSize); 
                pw.println("End With" ) ; 

                // ----------------------- start parsing --------------------------//
                // first array is the title. 
                boolean title = true; 
                for ( Iterator i = rows.iterator(); i.hasNext();  ) {
                        ArrayList row = (ArrayList) i.next(); 
                        for ( Iterator j = row.iterator(); j.hasNext();  ) {
                                printCell( j.next() );  
                                nextColumn();
                        }
                        if (title) { bold(); title = false; }                    
                        nextRow(); 
                }
                autofit(); 
                close(); 
                return xy[ROW] ; 
        }

        public int drawTable (String tableName, ResultSet r, String startxy ) throws SQLException,Exception  {
                return drawTable(tableName, r, startxy, false, 1); 
        } 
        public int drawTable(String tableName, ResultSet r, String startxy, boolean sum, int firstNumericRow)  throws SQLException,Exception  { 

                // Tablename cant have any spaces in it. 
                tableName = tableName.replaceAll(" ","_");              
                items.add( tableName ) ;

                // Set xy coordinates.  convert the A1 notation to 1,1 notation. 
                //start[COLUMN] = xy[COLUMN] = (int) startxy.charAt( COLUMN ); 
                //start[ROW] = xy[ROW] = Integer.parseInt(startxy.substring(1));  

                // TODO catch uppercase lowercase
                start[COLUMN] = xy[COLUMN] = ((int) startxy.charAt( COLUMN )) - 64;  // (int) 'A'; 
                start[ROW] = xy[ROW] = Integer.parseInt(startxy.substring(1));  

                // We need to set the rows and columns for "+sheetName+" table.  
                columns = r.getMetaData().getColumnCount() ; 
                pw.println ("<script language=vbs> \nSub "+tableName +"() ") ; 
                //pw.println (sheetName+".ActiveSheet.Cells.Clear");  // breaks stuff. 
                pw.println("With " +sheetName + ".ActiveSheet");        
                pw.println(".Columns.Font.Name = \"" + fontName + "\"");  
                pw.println(".Columns.Font.Size = " + fontSize + " "); 
                pw.println("End With " ) ; 

                // pw.println(sheetName + ".TitleBar.caption = \"" + tableName + "\""  ); 

                // First, do column headers. "+sheetName+" could be much nicer. 
                for ( int j = 1; j<=columns; j++) {
                        pw.println (sheetName+".ActiveSheet.Cells("+nextColumn()+").Value = \""+r.getMetaData().getColumnName(j)+"\"" ) ;  
                }
                // Now, bold them, to be nice.  We need to kwow where we are though ... 
                pw.println (sheetName+".ActiveSheet.Cells.Range(\""+xy[ROW]+":"+xy[ROW]+"\").Font.Bold = True"); 
                nextRow(); 
                while (r.next())  { // for each row in the result set
                        // iterate over the columns, remembering to quote strings, now we should be able to introspect
                        // into the columns to get the datatype and the column header. 
                        for (int i = 1 ; i<=columns; i++){
                                pw.println (" "+sheetName+".ActiveSheet.Cells("+nextColumn()+").Value = \""+r.getString(i)+"\"" ) ;  
                        }
                        nextRow(); 
                }

//              // Final row. 
//              if ( sum ) {    
//                      //nextColumn(); 
//                      int firstrow = start[ROW]; 
//                      for (int i = 1 ; i<columns; i++){
//                              if ( i < 2 ){// = firstNumericRow ) {
//                                      
//                              }
//                              else { 
//                                      pw.println ( sheetName + ".ActiveSheet.Cells("+toRange(xy[ROW],)+").Value = \"=SUM("+
//                                      toRange(firstrow+1, xy[COLUMN]  ) 
//                                      + ':' + 
//                                      toRange(xy[ROW]-1, xy[COLUMN] ) 
//                                      + ")\""); 
//                              }
//                      }
//              }
//              }
//              
//              // Autofit!  this only works for the cells present at the time its called. 
                autofit();
                pw.println (" End Sub\n</script>" ) ; 
                return (int)xy[ROW] ; 
        }

        // TODO:  CHANGE "+sheetName+" to reflect "+sheetName+" notation : 
        //
        //
        public String nextRow() {
                xy[COLUMN] = start[COLUMN] ; 
                return  ++xy[ROW] + "," + xy[COLUMN];  
        }

        // Todo! columns go from Z to AA, must take care of this.   
        public String nextColumn() {
                return xy[ROW] + "," + xy[COLUMN]++; 
        }

        public void close () throws Exception {
                pw.println("End Sub");
                pw.println("</script>"); 
        }

        public void openManual( String name ) throws Exception {
                openManual();
        }

        public void openManual() throws Exception {
                rawWrite = true;
                String ran = ""+random.nextLong();
                if (ran.charAt(0) == '-') { ran = ran.substring(1,ran.length()); }
                String name = "manual" + ran;
                items.add( name ) ;
                pw.println("<script language=\"vbs\">");
                pw.println("Sub "+name +"()");  
                pw.println("");
        }

        public void closeManual( ) throws Exception {
                pw.println("End Sub");
                pw.println("</script>");  
                rawWrite = false;
        }

        // somebody write me. 

        public void select ( String range ) throws Exception {
                rawWrite(".Range(\""+range+"\").Select");
        }
        
        public void format( String range, String format ) throws Exception {
                rawWrite(".Range(\""+range+"\").Select");
                rawWrite(".Selection.NumberFormat = \""+format+"\"");
        }

        public void writeCell( String cell, String data ) throws Exception {
                boolean inRaw = rawWrite;
                if (! inRaw) { openManual(); }
                pw.println ( sheetName+".ActiveSheet.Cells("+cell+").Value = \"" + data + "\"" ) ;  
                if (! inRaw) { closeManual(); }
        }
        
//      public void formatSelection ( String selection, int rows, String format ) throws Exception {
//              // break the selection apart.                                                                   
//              char column = selection.chartAt(0); 
//      }
//
        public void writeRange( String cell, String data ) throws Exception {
                pw.println ( sheetName+".ActiveSheet.Range(\""+cell+"\").Value = \"" + data + "\"" ) ;  
        }
        public String toRange( int row, int column ) {
                return ((char) (column+'A'-1)) + (row +"");
        }

        public void rawWrite (String command ) throws Exception { 
                // open manual mode if need be; if it's already open then don't worry about it.
                boolean inRaw = rawWrite;
                if (! inRaw) { openManual(); }
                pw.println(sheetName + command);
                if (! inRaw) { closeManual(); }
        }

        public int[] getStart() { return start; }

        public int[] getEnd() { return end; }

}

---