Examples of com.extentech.ExtenXLS.WorkSheetHandle

com.extentech.ExtenXLS.WorkSheetHandle
The WorkSheetHandle provides a handle to a Worksheet within an XLS file
and includes convenience methods for working with the Cell values within the sheet.

for example:

WorkBookHandle book = new WorkBookHandle("testxls.xls");
WorkSheetHandle sheet1 = book.getWorkSheet("Sheet1");
CellHandle cell = sheet1.getCell("B22");

to add a cell:

CellHandle cell = sheet1.add("Hello World","C22");

to add a numeric cell:

CellHandle cell = sheet1.add(Integer.valueOf(120),"C23");

to add a formula cell:

CellHandle cell = sheet1.add("=PI()","C24");

@see WorkSheet @see WorkBookHandle @see CellHandle

        }catch(Exception xe){;}
      }
      
      Iterator it = matchedSheets.iterator();
      while(it.hasNext()){
        WorkSheetHandle s1x = (WorkSheetHandle)it.next();
        WorkSheetHandle s2x = bk2.getWorkSheet(s1x.getSheetName());
        
        // get all the cells, and compare them
        CellHandle[] c1x = s1x.getCells();
        CellHandle[] c2x = s2x.getCells();
        
        System.out.println(getCellText(c1x));
        
        System.out.println(getCellText(c2x));

View Full Code Here

  static String wd = System.getProperty("user.dir")+"/docs/samples/PasswordProtection/";


  public static void main(String[] args) {
    WorkBookHandle book = new WorkBookHandle();
    try{
      WorkSheetHandle sheet = book.getWorkSheet("Sheet1");
      CellHandle a1 = sheet.add("hello world","a1");
      sheet.setProtected(true);
        writeFile(book, "testPasswordProtect.xls");
            book.close();
        book = new WorkBookHandle(wd+"testPasswordProtect.xls");
            sheet = book.getWorkSheet("Sheet1");
            if(!sheet.getProtected())
              Logger.logErr("Set Password Protection Failed!");
            
            // set password/get password
            sheet.setProtectionPassword("g0away");


        }catch(Exception ex){
          Logger.logErr("error opening password protected file " + ex.toString());
        }
  }

View Full Code Here

      Logger.logErr("Spreadsheet_CSV_Input failed.",e);
    }
    
    // Write out the spreadsheet as CSV -- 
        try {
            WorkSheetHandle wsh = null;
        wsh = book.getWorkSheet(0);
        StringBuffer arr = new StringBuffer();
            // return WorkBookCommander.returnXMLErrorResponse("PluginSheet.get() failed: "+ex.toString());
            RowHandle[] rwx = wsh.getRows();
            for (int i=0;i<rwx.length;i++) {
                RowHandle r = rwx[i];
                try{
                  CellHandle[] chx = r.getCells();
                  for(int t=0;t<chx.length;t++){

View Full Code Here

     * creates a cell range to demonstrate various
     * merging methods
     * */
    public void mergeCells(){
        WorkBookHandle book = new WorkBookHandle();
        WorkSheetHandle sheet = null;
        try{
            sheet = book.getWorkSheet(0);
        }catch(WorkSheetNotFoundException e){
            System.err.println("Error getting worksheet " + e);
        }
        
        // add values to the worksheet
        sheet.add("Each","A1");
        sheet.add("cell","B1");
        sheet.add("has","C1");
        sheet.add("been","D1");
        sheet.add("preserved","E1");


        // add other values to worksheet
        sheet.add("Only first cell is preserved","A3");
        sheet.add("first","B3");
        sheet.add("cell","C3");
        sheet.add("is","D3");
        sheet.add("preserved","E3");


        // create cell ranges for each, required in order to merge cells
        CellRange crx1 = null;
        CellRange crx2 = null;
        try{

View Full Code Here

   * test the various printer settings
   * ------------------------------------------------------------
   * 
   */
  public void testPrinterSettings(WorkBookHandle book) {
    WorkSheetHandle sheet = null;
    PrinterSettingsHandle printersetup = null;
    try{
       sheet = book.getWorkSheet(0);
       for(int x=0;x<10;x++){
         for(int t=0;t<10;t++){
           sheet.add("Hello World " + t,t,x);
         }
       }
       printersetup = sheet.getPrinterSettings();
    }catch(Exception e){
      Logger.logErr("testPrinterSettings failed: " + e.toString());
    }
  
    // fit width
    printersetup.setFitWidth(3);
    // fit height
    printersetup.setFitHeight(5);
    // header margin
    printersetup.setHeaderMargin(1.025);
    // footer margin
    printersetup.setFooterMargin(1.025);
    // number of copies
    printersetup.setCopies(10);
    // Paper Size
    printersetup.setPaperSize(PrinterSettingsHandle.PAPER_SIZE_LEDGER_17x11);
    // Scaling
    printersetup.setScale(125);
    //  resolution
    printersetup.setResolution(300);
    
    // GRBIT settings:
    // left to right printing
    printersetup.setLeftToRight(true);
    // print as draft quality
    printersetup.setDraft(true);
    // black and white
    printersetup.setNoColor(true);
    // landscape / portrait
    printersetup.setLandscape(true);
    
    
    // write it out
    testWrite(book, "PrinterSettings_out.xls");
    
    // read it in
    book = new WorkBookHandle(this.outputdir + "PrinterSettings_out.xls");
    try{
       sheet = book.getWorkSheet(0);
       printersetup = sheet.getPrinterSettings();
    }catch(Exception e){
      Logger.logErr("testPrinterSettings failed: " + e.toString());
    }
    
    // header margin

View Full Code Here

    
    // FORMAT ID Sharing
    // the fastest, no fuss way to use formats
    // on multiple cells
    
        WorkSheetHandle sheet1 = null;
    try{
      sheet1 = tbo.getWorkSheet("Sheet1");
    }catch(Exception e){System.out.println(e);}
    sheet1.add("Eurostile Template Cell","A1");
    int SHAREDFORMAT = 0;
    CellHandle b = null;
    CellHandle a= null;
    try{
      b = sheet1.getCell("A1");
      b.setFont("Eurostile",Font.BOLD,14);
      // set format options
      b.setForegroundColor(30);
      b.setBackgroundColor(54);
      b.setBackgroundPattern(3);
      
      SHAREDFORMAT = b.getFormatId();
      for(int t = 1; t<=10;t++){
        sheet1.add(new Float(t*67.5),"E" + t);
        a = sheet1.getCell("E" + t);
        a.setFormatId(SHAREDFORMAT);
      }


      a.setFont("Tango",Font.BOLD,26);
      a.setFontColor(10);
      a.setFormatPattern("[h]:mm:ss");
      a.setURL("http://www.extentech.com/");
      a.setScript(2);
      sheet1.moveCell(a,"A10");
      a.getCol().setWidth(8000); 
      tbo.copyWorkSheet(sheetname, sheetname + " Copy");
    }catch(Exception e){System.out.println(e);}
        
    // optimize String table by sharing dupe entries
    tbo.setDupeStringMode(WorkBookHandle.SHAREDUPES);
    tbo.setStringEncodingMode(WorkBookHandle.STRING_ENCODING_COMPRESSED);


    //iterate backgrounds
    for(int x = 0;x<32;x++){
        sheet1.add("Pattern# " + x, "F"+(x+1));
        sheet1.add("Text", "G"+(x+1));
      try{
        CellHandle c = sheet1.getCell("G"+(x+1));
        c.setBackgroundPattern(x);
        c.setFont("Tango",Font.BOLD,26);
        c.setFontColor(10);
      }catch(Exception e){System.out.println(e);}
    }


    //iterate colors
    for(int x = 0;x<64;x++){
      try{
        sheet1.add("Color# " + x, "C"+(x+1));
        sheet1.add(" ", "D"+(x+1));
        CellHandle c = sheet1.getCell("D"+(x+1));
        c.setBackgroundPattern(x);
        c.setBackgroundColor(x);
        c.setForegroundColor(x);
      }catch(Exception e){System.out.println(e);}
    }
        
    // remove a col and a row
    try{
      sheet1.removeCols( 4, 1 );
      sheet1.removeRow(2);
    }catch(Exception e){System.out.println(e);}
    testWrite(tbo, "testFormats2_out.xls");
  }

View Full Code Here

         writeSSTOOXML(bk);        
         // writeStylesOOXML AFTER sheet OOXML in order to capture any dxf's  (differential xf's used in conditional formatting and others)
         // but ensure that workbook.xml.rels knows about the file styles.xml ***
         wbContentList.add(new String[] {"/xl/styles.xml", "styles"});           
         writeWorkBookOOXML(bk);             
         WorkSheetHandle wsh[]= bk.getWorkSheets();
         bk.getWorkBook().setDxfs(null); // rebuild
         for(int i= 0; i < wsh.length; i++)
         {
           writeSheetOOXML(bk, wsh[i], i);
         }

View Full Code Here

            writer.write( "\r\n" );
            
            // IDENTIFY SHEETS
            writer.write( "<sheets>" );                                           
            writer.write( "\r\n" );
            WorkSheetHandle wsh[] = bk.getWorkSheets();
            for (int i= 0; i < wsh.length; i++) {
                String s= "sheet" + (i+1);        //Write SheetXML to SheetX.xml, 1-based
                writer.write( ("<sheet name=\""+ stripNonAscii(wsh[i].getSheetName()) + "\" sheetId=\""+ (i+1) + "\" r:id=\"rId" + (i+1) + "\"") );
                if (wsh[i].getVeryHidden()) 
                    writer.write( " state=\"veryHidden\"" );

View Full Code Here

     * @param bk
     * @param formulas
     */
    void addFormulas(WorkBookHandle bk, ArrayList formulas) {
        // after sheets, now can input formulas
        WorkSheetHandle sheet= null;
        HashMap sharedFormulas= new HashMap();
        for (int j= 0; j < formulas.size(); j++) {
              String[] s= (String[]) formulas.get(j);    
              //formulas:  0=sheetname, 1= cell address, 2=formula including =, 3=shared formula index, 4=array refs, 5=formula type, 6=calculate always flag, 7=format id, 8=cached value
              if ((s[0].equals("") || s[1].equals("")) || s.length<8) 
                 continue; // no address or formula - should ever happen?
              try {                               
                  // for clarity, assign values to most common ops
                  String addr= s[1];
                  int[] rc = ExcelTools.getRowColFromString(addr);
                  String fStr= s[2];
                  String type= s[5];
                  String fType= "";
                  if (s[5].indexOf('/')>0) {
                      type= s[5].split("/")[0];
                      fType= s[5].split("/")[1];
                  }
                  int fmtid = 0;
                  try{
                     fmtid = Integer.valueOf(s[7]).intValue();
                  }catch(Exception e){;}                  
                  Object cachedValue= s[8];
                  if (type.equals("n"))
                      try {
                          cachedValue= Integer.valueOf((String)cachedValue);
                      } catch (NumberFormatException e) {
                          cachedValue= new Double((String)cachedValue);
                      }
                  else if (type.equals("b")) 
                      cachedValue= Boolean.valueOf((String)cachedValue);
                  // type e -- input calculation exception?
                  CellHandle ch= null;  // normal case but may be created * as a blank * if part of a merged cell range or dv ... 
                  try{
                     sheet = bk.getWorkSheet(s[0]);   
                     ch= sheet.getCell(addr);   // if exists, grab it;                               
                  }catch(Exception ex){;}
                  if (fStr.equals("null")) { // when would this ever occur?
                     Logger.logWarn("OOXMLAdapter.parse: invalid formula encountered at " + addr);
                  }
                  
                  if (fType.equals("array")) {
                      /*
                       * For a multi-cell formula, the r attribute of the top-left cell 
                       * of the range 1 of cells to which that formula applies
                         shall designate the range of cells to which that formula applies
                       */
                      int[] arrayref= null;
                      if (s[4]!=null) {   // if has the ref attribute means its the PARENT array formula
                          sheet.getMysheet().addParentArrayRef(s[1], s[4]);
                          arrayref= ExcelTools.getRangeRowCol(s[4]);
                      } else
                          arrayref= rc;
                      /* must enter array formulas for each cell in range denoted by array ref*/
                      for (int r= arrayref[0]; r <= arrayref[2]; r++) {
                          for (int c= arrayref[1]; c<=arrayref[3]; c++) {
                              try{
                                 ch= sheet.getCell(r, c);   // if exists, grab it;                               
                              }catch(Exception ex){;}
                               if(ch==null) 
                                 ch= sheetAdd(sheet,"{" + fStr + "}",cachedValue, r,c,fmtid);
                               else {
                                 ch.setFormatId(fmtid); // if exists may be part of a merged cell range and therefore it's correct format id may NOT have been set; see mergedranges in parseSheetXML below

View Full Code Here

              break;
            }                        
            }
          
          target = getEntry(zip,p + c[1]);*/
        WorkSheetHandle sheet= pivotTables.get(key);
          PivotTableDefinition.parseOOXML(bk, /*cacheid, */sheet.getMysheet(), wrapInputStream(zip.getInputStream(target)));
        
        
      }
    }

View Full Code Here

0 1

TOP

Related Classes of com.extentech.ExtenXLS.WorkSheetHandle

com.extentech.formats.XLS.Boundsheet

com.extentech.formats.XLS.charts.Chart

com.extentech.formats.XLS.OOXMLReader

com.extentech.formats.XLS.OOXMLWriter

com.extentech.toolkit.CompatibleVector

docs.samples.CellRanges.TestCellRanges

docs.samples.Compare2Spreadsheets.Compare2Spreadsheets

docs.samples.Formats.TestFormats

docs.samples.Input_Output_CSV.Spreadsheet_CSV_Input

docs.samples.PasswordProtection.testPasswordProtect

All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.