Package com.extentech.ExtenXLS

Examples of com.extentech.ExtenXLS.CellRange


     * @param sId  Stream or cachid Id -- links back to SxStream set of records
   */
  byte[] createPivotCacheRecords(String ref, WorkBookHandle wbh, int sId) {
      byte[] newbytes= new byte[0];       
    try {
      CellRange cr= new CellRange(ref, wbh, false, true);
      CellHandle[] ch= cr.getCells()// cells are in row-order
      int[] rows= cr.getRowInts()// first row= header, ensuing rows are cacherecords     
      int[] cols= cr.getColInts();
      int[] types= new int[cols.length]
      byte[][] cachefieldindexes= new byte[cols.length][rows.length-1];
      SxDB sxdb= (SxDB) SxDB.getPrototype();
      sxdb.setNCacheRecords(rows.length-1);
      sxdb.setNCacheFields(cols.length);
View Full Code Here


        WorkBookHandle tbo = new WorkBookHandle();
        try{
      WorkSheetHandle sheet = tbo.getWorkSheet(0);
     
            int[] coords = {12,1,12,5};
            CellRange range = new CellRange(sheet, coords, true);
           
            CellRange range2= new CellRange("Sheet1!B2:Sheet1!C10", tbo, true);
            range2.setBorder(2,FormatConstants.BORDER_THIN, Color.blue);
           
            // set top and bottom
            FormatHandle myfmthandle = new FormatHandle(tbo);
            myfmthandle.addCellRange(range);
          myfmthandle.setTopBorderLineStyle(FormatHandle.BORDER_DOUBLE);
          myfmthandle.setBottomBorderLineStyle(FormatHandle.BORDER_THICK);
         
            // set sides
          int[] coords2 = {5,4,5,8};
          CellRange range3 = new CellRange(sheet, coords2, true);
            FormatHandle myfmthandle2 = new FormatHandle(tbo);
            myfmthandle2.addCellRange(range3);
          myfmthandle2.setBorderLeftColor(Color.red);
            myfmthandle2.setLeftBorderLineStyle(FormatHandle.BORDER_DASH_DOT_DOT);
           
            myfmthandle2.setBorderRightColor(Color.blue);
            myfmthandle2.setRightBorderLineStyle(FormatHandle.BORDER_DOUBLE);
         
            // ok, test not clobbering
            CellRange range4 = new CellRange(sheet, coords2, true);
           
            CellHandle cell0 = range4.getCells()[0];
          
            FormatHandle clobberfmt = cell0.getFormatHandle();
            clobberfmt.setCellBackgroundColor(Color.lightGray);
            clobberfmt.setUnderlined(true);
           
View Full Code Here

        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{
            // defines the cell ranges
            crx1 = new CellRange("Sheet1!A1:E1", book);
            crx2 = new CellRange("Sheet1!A3:E3", book);
           
            // Merge the cells, keep the values in all cells...           
            crx1.mergeCells(CellRange.RETAIN_MERGED_CELLS);
           
            // This time, remove the trailing Cells...
            crx2.mergeCells(CellRange.REMOVE_MERGED_CELLS);
        }catch(Exception e){
            System.err.println("Error setting cell ranges " + e)
        }
       testWrite(book, "TestCellRanges.xls");
    
View Full Code Here

        cfx.resetFormulaRef();
     
      StringBuffer xml= new StringBuffer()
     
      xml.append("<Range>");
      CellRange rn = new CellRange(cfx.getCondfmt().getBoundingRange());//getConditionalFormatRange();
      if(rn!=null)
        xml.append(rn.getR1C1Range());
     
      xml.append("</Range>");
      xml.append("<Condition>");
      if(cfx.cp != 0x0){ // calcer
          xml.append("<Qualifier>");
View Full Code Here

      com.extentech.formats.XLS.Boundsheet sht= parentChart.getSheet();
      java.util.Vector s = this.getAllSeries(-1);
      // Category values *******************************************************************************
      if (s.size() > 0) {
          try {
        CellRange cr = new CellRange(((Series) s.get(0)).getCategoryValueAi().toString(), parentChart.wbh, true);
        CellHandle[] ch = cr.getCells();
        if (ch != null) { // found a template with a chart with series but no categories
            categories = new Object[ch.length];
            for (int j = 0; j < ch.length; j++) {
          try {
              categories[j] = ch[j].getFormattedStringVal(true);
          } catch (IllegalArgumentException e) { // catch format exceptions
              categories[j]= ch[j].getStringVal();
          }
            }
        } else if (s.size() > 0) {
            cr = new CellRange(((Series) s.get(0)).getSeriesValueAi().toString(), parentChart.wbh, true);
            int sz= cr.getCells().length;
            categories= new Object[sz];
            for (int j= 0; j < sz; j++)
      categories[j]= Integer.valueOf(j + 1).toString();       
        }
          } catch (Exception e) {
        Logger.logWarn("ChartSeries.getMinMax: " + e.toString());
          }
      }
      // Series colors, labels and values ***************************************************************
      double yMax = 0.0, yMin = Double.MAX_VALUE;
      int nseries = 0;
      seriescolors = null;
      legends = null;
      int charttype = co.getChartType();
      // obtain/store series colors, store series values and trap maximum and
      // minimun values so can be used below for axis scale
      /*
       * A Scatter chart has two value axes, showing one set of numerical data along the x-axis and another along the y-axis.
       * It combines these values into single data points and displays them in uneven intervals, or clusters   
       */
      if (charttype != PIECHART && charttype != DOUGHNUTCHART) {
          seriescolors = new String[s.size()];
          legends = new String[s.size()];
          for (int i = 0; i < s.size(); i++) {
        Series myseries = ((Series) s.get(i));
        seriescolors[i] = myseries.getSeriesColor();
        legends[i] = com.extentech.formats.XLS.OOXMLAdapter.stripNonAscii(myseries.getLegendText()).toString();
        CellRange cr = new CellRange(myseries.getSeriesValueAi().toString(), parentChart.wbh, true);
        CellHandle[] ch = cr.getCells();
        nseries = Math.max(nseries, ch.length);
        double[] seriesvals;
        String[] sranges;
        //        String[] series_strings;
        if (!myseries.hasBubbleSizes()) {
            seriesvals= new double[nseries];
            sranges= new String[nseries];
        } else {
            seriesvals = new double[nseries * 2];
            sranges= new String[nseries * 2];
        }
        //        series_strings = new String[seriesvals.length];

        for (int j = 0; j < ch.length; j++) {
            try {
          sranges[j]= ch[j].getCellAddressWithSheet();
          seriesvals[j] = ch[j].getDoubleVal();
          if (Double.isNaN(seriesvals[j]))
              seriesvals[j] = 0.0;
          yMax = Math.max(yMax, seriesvals[j]);
          yMin = Math.min(yMin, seriesvals[j]);
            } catch (NumberFormatException n) {
          ;
            }
        }
        if (myseries.hasBubbleSizes()) { // append bubble sizes to series values ... see BubbleChart.getSVG for parsing
            int z = ch.length;
            CellRange crb= new CellRange(myseries.getBubbleValueAi().toString(), parentChart.wbh, true);
            CellHandle[] chb = crb.getCells();
            for (int j = 0; j < ch.length; j++) {
          seriesvals[j + z] = chb[j].getDoubleVal();
          sranges[j + z]= chb[j].getCellAddressWithSheet();             
            }
        }
        seriesvalues.add(seriesvals);   // trap and add series value points
        seriesranges.add(sranges);    // trap series range
          }
      } else if (charttype == DOUGHNUTCHART && s.size() > 1) { // like a PIE chart but can have multiple series
          legends = new String[categories.length];     // for PIE/DONUT charts, legends are actually category labels, not series labels
          for (int i = 0; i < categories.length; i++)
        legends[i] = com.extentech.formats.XLS.OOXMLAdapter.stripNonAscii(categories[i].toString()).toString();
          for (int i = 0; i < s.size(); i++) {
        Series myseries = ((Series) s.get(i));
        // legends[i]=
        // com.extentech.formats.XLS.OOXMLAdapter.stripNonAscii(myseries.getLegend());
        CellRange cr = new CellRange(myseries.getSeriesValueAi().toString(), parentChart.wbh, true);
        CellHandle[] ch = cr.getCells();
        double[] seriesvals= new double[ch.length];
        String[] sranges= new String[ch.length];
        if (seriescolors == null)
            seriescolors = new String[ch.length];
        for (int j = 0; j < ch.length; j++) {
            try {
          seriesvals[j] = ch[j].getDoubleVal();
          if (ch[j].getWorkSheetHandle().getMysheet().equals(sht))           
              sranges[j]= ch[j].getCellAddress();
          yMax = Math.max(yMax, seriesvals[j]);
          yMin = Math.min(yMin, seriesvals[j]);
          if (i == 0) { // only do for 1st series; will be the
              // same for rest
              seriescolors[j] = myseries.getPieSliceColor(j);
              /*if (seriescolors[j] == 0x4D
                  || seriescolors[j] == 0x4E)
                seriescolors[j] = com.extentech.formats.XLS.FormatConstants.COLOR_WHITE;*/
          }

            } catch (NumberFormatException n) {
          ;
            }
        }
        seriesvalues.add(seriesvals); // trap and add series value points
        seriesranges.add(sranges);    // trap series range
          }
      } else { // PIES - only 1 series
          if (s.size() > 0) {
        // PIE: 1 series data
        CellHandle[] cats = new CellRange(((Series)s.get(0)).getCategoryValueAi().toString(), parentChart.wbh, true).getCells();
        if (cats != null) {
            nseries = cats.length;
            legends = new String[cats.length]; // for PIE charts, legends are actually category labels, not series labels
            for (int i = 0; i < cats.length; i++)
          legends[i] = cats[i].getFormattedStringVal(true);
        }
        seriescolors = new String[nseries];
        Series myseries = ((Series) s.get(0));
        try {
            CellRange cr = new CellRange(myseries.getSeriesValueAi().toString(), parentChart.wbh, true);
            CellHandle[] ch = cr.getCells();
            // error trap - shouldn't happen
            if (ch.length != nseries) {
          Logger.logWarn("ChartHandle.getSeriesInfo: unexpected Pie Chart structure");
          nseries = Math.min(nseries, ch.length);
            }
View Full Code Here

              // unclear if at any other time the range referenced is a subset ... [NOTE: in testing, only pivot charts hit]
              // another assumption:  assume that range is only TRUNCATED -- in testing, true
              int npoints= Integer.valueOf(xpp.getAttributeValue(0)).intValue();
              if (!ranges[idx].equals("") && ranges[idx].indexOf(",")==-1) {
                try {
                  CellRange cells= new CellRange(ranges[idx], wbh, false, true);
                  if (cells.getCells().length!=npoints) {  //must adjust
                    int z= 0;
                    CellHandle[] clist= cells.getCells();
                    while (eventType != XmlPullParser.END_DOCUMENT) {
                      if (eventType == XmlPullParser.START_TAG) {
                        tnm= xpp.getName();
                        if (tnm.equals("pt")) {                             
                          // format code idx
                        } else if (tnm.equals("v")) {
/* this case should NOT happen
                          String s= OOXMLAdapter.getNextText(xpp);
                           if (z < clist.length)
                            if (!clist[z].getVal().toString().equals(s))                              
                              Logger.logWarn("ChartSeries.parseOOXML: unexpected pivot value order- skipping");
                          z++;
*/
                        }
                      } else if (eventType == XmlPullParser.END_TAG) {
                        if (xpp.getName().equals(cache)) {
                          cache= null;                     
                          break;
                        }
                      }
                      eventType = xpp.next();
                    }
                    // pivot charts: apparently always truncate/skip last cell in range (which represents the grand total)
                    if (npoints < clist.length) {// truncate!                     
                      int[] rc= cells.getRangeCoords();
                      rc[0]--; rc[2]--;  // make 0-based
                      if (rc[0]==rc[2])
                        rc[3]-=(clist.length-npoints);
                      else
                        rc[2]-=(clist.length-npoints);
// KSC: TESTING: REMOVE WHEN DONE                     
//System.out.println("Truncate list: old range: " + ranges[idx] + " new range: " + cells.getSheet().getQualifiedSheetName() + "!" + ExcelTools.formatLocation(rc));
                      ranges[idx]= cells.getSheet().getQualifiedSheetName() + "!" + ExcelTools.formatLocation(rc);
                    }
                     
                    continue// don't hit xpp.next() below
                  }
                } catch (Exception e) {
View Full Code Here

     * @return
     */
    public CellRange getCellRange() {
      String range= fileName + "!" + ExcelTools.formatLocation(new int[] {rwFirst, colFirst, rwLast, colLast});
      try {
        return new CellRange(range, null)//this.getWorkBook());
      } catch (CellNotFoundException e) {      
      }
      return null;
    }
View Full Code Here

    nm+= com.extentech.ExtenXLS.ExcelTools.getAlphaVal(colFirst);
    nm+=rowFirst+":";
    nm+=com.extentech.ExtenXLS.ExcelTools.getAlphaVal(colLast);
    nm+=rowLast;
    try{
      range = new CellRange(nm,null);
    }catch(Exception e){
      Logger.logWarn("initializing Hlink record failed: " + e);
    }
    if(DEBUGLEVEL > 5)Logger.logInfo("Hlink Cells: " + range.toString());
View Full Code Here

    cellcoords[2] = this.getRowLast();
    cellcoords[1] = this.getColFirst();
    cellcoords[3] = this.getColLast();
     
      try{
        CellRange cr = new CellRange(wbook.getWorkSheet(this.getSheet().getSheetName()), cellcoords);
        cr.setWorkBook(wbook);
        BiffRec[] ch = cr.getCellRecs();
        for(int t=0;t<ch.length;t++){
          ch[t].setHyperlink(this);
        }
      }catch(Exception e){
        Logger.logWarn("initializing Hyperlink Cells failed: " + e);     
View Full Code Here

    ooxml.append("<c:f>" + OOXMLAdapter.stripNonAscii(seriesAi.toString()) + "</c:f>");    ooxml.append("\r\n")// string range
    // Need numCache for chart lines apparently
    ooxml.append("<c:numCache>");  ooxml.append("\r\n");    // specifies the last data shown on the chart for a series
    // formatCode  == format pattern
    ooxml.append("<c:formatCode>" + this.getSeriesFormatPattern() + "</c:formatCode>");
      CellRange cr = new CellRange(seriesAi.toString(), parentChart.wbh, false);
      CellHandle[] ch= cr.getCells();
      // ptCount  == point count
      ooxml.append(getValueRangeOOXML(ch));
      // pt * n  == a Numeric Point each has a <v> child, an idx attribute and an optional formatcode attribute
    ooxml.append("</c:numCache>");  ooxml.append("\r\n");    //
   
View Full Code Here

TOP

Related Classes of com.extentech.ExtenXLS.CellRange

Copyright © 2018 www.massapicom. All rights reserved.
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.