/**
         * If this formula was on an imported sheet, check that
         * cell references to another sheet are warned appropriately
         *
         * @return TRUE if this formula was able to be imported, FALSE otherwise
         */
        public bool handleImportedCellReferences(ExternalSheet es,
                                                 WorkbookMethods mt,
                                                 WorkbookSettings ws)
        {
            try
            {
                if (parser == null)
                {
                    byte[] formulaData  = formula.getFormulaData();
                    byte[] formulaBytes = new byte[formulaData.Length - 16];
                    System.Array.Copy(formulaData, 16,
                                      formulaBytes, 0, formulaBytes.Length);
                    parser = new FormulaParser(formulaBytes,
                                               this,
                                               es, mt, ws);
                    parser.parse();
                }

                return(parser.handleImportedCellReferences());
            }
            catch (FormulaException e)
            {
                //logger.warn("cannot import formula:  " + e.Message);
                return(false);
            }
        }
Example #2
0
        /**
         * Constructs this object from a string
         *
         * @param s the string
         * @param w the external sheet
         * @exception FormulaException
         */
        public CellReference3d(string s, ExternalSheet w)
        {
            workbook       = w;
            columnRelative = true;
            rowRelative    = true;

            // Get the cell details
            int    sep        = s.IndexOf('!');
            string cellString = s.Substring(sep + 1);

            column = CellReferenceHelper.getColumn(cellString);
            row    = CellReferenceHelper.getRow(cellString);

            // Get the sheet index
            string sheetName = s.Substring(0, sep);

            // Remove single quotes, if they exist
            if (sheetName[0] == '\'' && sheetName[sheetName.Length - 1] == '\'')
            {
                sheetName = sheetName.Substring(1, sheetName.Length - 1);
            }
            sheet = w.getExternalSheetIndex(sheetName);

            if (sheet < 0)
            {
                throw new FormulaException(FormulaException.SHEET_REF_NOT_FOUND, sheetName);
            }
        }
Example #3
0
 /// <summary> Gets the fully qualified cell reference given the column, row
 /// external sheet reference etc
 ///
 /// </summary>
 /// <param name="">sheet
 /// </param>
 /// <param name="">column
 /// </param>
 /// <param name="">row
 /// </param>
 /// <param name="">workbook
 /// </param>
 /// <param name="">buf
 /// </param>
 public static void  getCellReference(int sheet, int column, int row, ExternalSheet workbook, System.Text.StringBuilder buf)
 {
     buf.Append('\'');
     buf.Append(workbook.getExternalSheetName(sheet));
     buf.Append('\'');
     buf.Append('!');
     getCellReference(column, row, buf);
 }
 /**
  * Constructor
  * @param f
  * @param ws
  */
 public StringFormulaParser(string f, ExternalSheet es, WorkbookMethods nt, WorkbookSettings ws, ParseContext pc)
 {
     formula       = f;
     settings      = ws;
     externalSheet = es;
     nameTable     = nt;
     parseContext  = pc;
 }
Example #5
0
 /**
  * Constructor which creates the parse tree out of the string
  *
  * @param form the formula string
  * @param es the external sheet handle
  * @param nt the name table
  * @param ws the workbook settings
  * @param pc the context of the parse
  */
 public FormulaParser(string form,
                      ExternalSheet es,
                      WorkbookMethods nt,
                      WorkbookSettings ws,
                      ParseContext pc)
 {
     parser = new StringFormulaParser(form, es, nt, ws, pc);
 }
 /**
  * Constructs this object from the raw data
  *
  * @param t the basic number formula record
  * @param fr the formatting records
  * @param es the external sheet
  * @param nt the name table
  * @param nf flag indicating whether the 1904 date system is in use
  * @param si the sheet
  */
 public DateFormulaRecord(NumberFormulaRecord t, FormattingRecords fr,
                          ExternalSheet es, WorkbookMethods nt,
                          bool nf, SheetImpl si)
     : base(t, t.getXFIndex(), fr, nf, si)
 {
     externalSheet = es;
     nameTable     = nt;
     data          = t.getFormulaData();
 }
Example #7
0
        /**
         * Gets the fully qualified cell reference given the column, row
         * external sheet reference etc
         *
         * @param sheet
         * @param column
         * @param row
         * @param workbook
         * @return the cell reference in the form 'Sheet 1'!A1
         */
        public static string getCellReference
            (int sheet, int column, int row,
            ExternalSheet workbook)
        {
            StringBuilder sb = new StringBuilder();

            getCellReference(sheet, column, row, workbook, sb);
            return(sb.ToString());
        }
Example #8
0
        /// <summary> Constructs this object from the raw data
        ///
        /// </summary>
        /// <param name="t">the raw data
        /// </param>
        /// <param name="fr">the formatting records
        /// </param>
        /// <param name="es">the external sheet
        /// </param>
        /// <param name="nt">the name table
        /// </param>
        /// <param name="si">the sheet
        /// </param>
        public ErrorFormulaRecord(Record t, FormattingRecords fr, ExternalSheet es, WorkbookMethods nt, SheetImpl si) : base(t, fr, si)
        {
            externalSheet = es;
            nameTable     = nt;
            data          = getRecord().Data;

            Assert.verify(data[6] == 2);

            errorCode = data[8];
        }
Example #9
0
 /// <summary> Constructor which creates the parse tree out of tokens
 ///
 /// </summary>
 /// <param name="tokens">the list of parsed tokens
 /// </param>
 /// <param name="rt">the cell containing the formula
 /// </param>
 /// <param name="es">a handle to the external sheet
 /// </param>
 /// <param name="nt">a handle to the name table
 /// </param>
 /// <param name="ws">the workbook settings
 /// </param>
 /// <exception cref=""> FormulaException
 /// </exception>
 public FormulaParser(sbyte[] tokens, Cell rt, ExternalSheet es, WorkbookMethods nt, WorkbookSettings ws)
 {
     // A null workbook bof means that it is a writable workbook and therefore
     // must be biff8
     if (es.WorkbookBof != null && !es.WorkbookBof.isBiff8())
     {
         throw new FormulaException(FormulaException.biff8Supported);
     }
     parser = new TokenFormulaParser(tokens, rt, es, nt, ws);
 }
Example #10
0
 /// <summary> Constructor</summary>
 public TokenFormulaParser(sbyte[] data, Cell c, ExternalSheet es, WorkbookMethods nt, WorkbookSettings ws)
 {
     tokenData  = data;
     pos        = 0;
     relativeTo = c;
     workbook   = es;
     nameTable  = nt;
     tokenStack = new Stack();
     settings   = ws;
 }
 /**
  * Constructs this number
  *
  * @param t the data
  * @param excelFile the excel biff data
  * @param v the errorCode
  * @param fr the formatting records
  * @param es the external sheet
  * @param nt the name table
  * @param si the sheet
  */
 public SharedErrorFormulaRecord(Record t,
                                 File excelFile,
                                 int ec,
                                 FormattingRecords fr,
                                 ExternalSheet es,
                                 WorkbookMethods nt,
                                 SheetImpl si)
     : base(t, fr, es, nt, si, excelFile.getPos())
 {
     errorCode = ec;
 }
Example #12
0
        /**
         * Gets the fully qualified cell reference given the column, row
         * external sheet reference etc
         *
         * @param sheet
         * @param column
         * @param row
         * @param workbook
         * @param buf
         */
        public static void getCellReference
            (int sheet, int column, int row,
            ExternalSheet workbook, StringBuilder buf)
        {
            // Quotes are added by the WorkbookParser
            string name = workbook.getExternalSheetName(sheet);

            buf.Append(StringHelper.replace(name, "\'", "\'\'"));
            buf.Append(sheetInd);
            getCellReference(column, row, buf);
        }
 /**
  * Constructs this number
  *
  * @param t the data
  * @param excelFile the excel biff data
  * @param v the errorCode
  * @param fr the formatting records
  * @param es the external sheet
  * @param nt the name table
  * @param si the sheet
  */
 public SharedErrorFormulaRecord(Record t,
     File excelFile,
     int ec,
     FormattingRecords fr,
     ExternalSheet es,
     WorkbookMethods nt,
     SheetImpl si)
     : base(t,fr,es,nt,si,excelFile.getPos())
 {
     errorCode = ec;
 }
Example #14
0
 /**
  * Constructor
  */
 public DataValiditySettingsRecord(Record t,
                                   ExternalSheet es,
                                   WorkbookMethods wm,
                                   WorkbookSettings ws)
     : base(t)
 {
     data             = t.getData();
     externalSheet    = es;
     workbook         = wm;
     workbookSettings = ws;
 }
 /**
  * Constructs this number
  *
  * @param t the data
  * @param excelFile the excel biff data
  * @param v the value
  * @param fr the formatting records
  * @param es the external sheet
  * @param nt the name table
  * @param si the sheet
  */
 public SharedBooleanFormulaRecord(Record t,
     File excelFile,
     bool v,
     FormattingRecords fr,
     ExternalSheet es,
     WorkbookMethods nt,
     SheetImpl si)
     : base(t,fr,es,nt,si,excelFile.getPos())
 {
     value = v;
 }
 /**
  * Constructs this string formula
  *
  * @param t the record
  * @param excelFile the excel file
  * @param fr the formatting record
  * @param es the external sheet
  * @param nt the workbook
  * @param si the sheet
  * @param dummy the overload indicator
  */
 public SharedStringFormulaRecord(Record t,
                                  File excelFile,
                                  FormattingRecords fr,
                                  ExternalSheet es,
                                  WorkbookMethods nt,
                                  SheetImpl si,
                                  EmptyString dummy)
     : base(t, fr, es, nt, si, excelFile.getPos())
 {
     value = string.Empty;
 }
 /**
  * Constructs this number
  *
  * @param t the data
  * @param excelFile the excel biff data
  * @param v the value
  * @param fr the formatting records
  * @param es the external sheet
  * @param nt the name table
  * @param si the sheet
  */
 public SharedBooleanFormulaRecord(Record t,
                                   File excelFile,
                                   bool v,
                                   FormattingRecords fr,
                                   ExternalSheet es,
                                   WorkbookMethods nt,
                                   SheetImpl si)
     : base(t, fr, es, nt, si, excelFile.getPos())
 {
     value = v;
 }
 /**
  * Constructor
  */
 public DataValiditySettingsRecord(Record t,
     ExternalSheet es,
     WorkbookMethods wm,
     WorkbookSettings ws)
     : base(t)
 {
     data = t.getData();
     externalSheet = es;
     workbook = wm;
     workbookSettings = ws;
 }
Example #19
0
 /**
  * Constructor used to create writable data validations
  */
 public DataValidation(uint objId,
                       ExternalSheet es,
                       WorkbookMethods wm,
                       WorkbookSettings ws)
 {
     workbook         = wm;
     externalSheet    = es;
     workbookSettings = ws;
     validitySettings = new ArrayList();
     comboBoxObjectId = objId;
     copied           = false;
 }
Example #20
0
 /**
  * Constructs this number
  *
  * @param t the record
  * @param fr the formatting records
  * @param es the external sheet
  * @param nt the name table
  * @param si the sheet
  * @param pos the position of the next record in the file
  */
 public BaseSharedFormulaRecord(Record t,
                                FormattingRecords fr,
                                ExternalSheet es,
                                WorkbookMethods nt,
                                SheetImpl si,
                                int pos)
     : base(t, fr, si)
 {
     externalSheet = es;
     nameTable     = nt;
     filePos       = pos;
 }
        /**
         * Constructs this object from the raw data
         *
         * @param t the raw data
         * @param fr the formatting records
         * @param es the external sheet
         * @param nt the name table
         * @param si the sheet
         */
        public ErrorFormulaRecord(Record t,FormattingRecords fr,ExternalSheet es,
            WorkbookMethods nt,SheetImpl si)
            : base(t,fr,si)
        {
            externalSheet = es;
            nameTable = nt;
            data = getRecord().getData();

            Assert.verify(data[6] == 2);

            errorCode = data[8];
        }
Example #22
0
        /// <summary> Constructs this object from the raw data
        ///
        /// </summary>
        /// <param name="t">the raw data
        /// </param>
        /// <param name="fr">the formatting records
        /// </param>
        /// <param name="si">the sheet
        /// </param>
        /// <param name="es">the sheet
        /// </param>
        /// <param name="nt">the name table
        /// </param>
        public BooleanFormulaRecord(Record t, FormattingRecords fr, ExternalSheet es, WorkbookMethods nt, SheetImpl si) : base(t, fr, si)
        {
            externalSheet = es;
            nameTable     = nt;
            _Value        = false;

            data = getRecord().Data;

            Assert.verify(data[6] != 2);

            _Value = data[8] == 1?true:false;
        }
Example #23
0
        /**
         * Gets the fully qualified cell reference given the column, row
         * external sheet reference etc
         *
         * @param sheet
         * @param column
         * @param colabs TRUE if the column is an absolute reference
         * @param row
         * @param rowabs TRUE if the row is an absolute reference
         * @param workbook
         * @param buf
         */
        public static void getCellReference
            (int sheet, int column, bool colabs,
            int row, bool rowabs,
            ExternalSheet workbook, StringBuilder buf)
        {
            // WorkbookParser now appends quotes and escapes apostrophes
            string name = workbook.getExternalSheetName(sheet);

            buf.Append(name);
            buf.Append(sheetInd);
            getCellReference(column, colabs, row, rowabs, buf);
        }
Example #24
0
        public static String PrependSheetName(IFormulaRenderingWorkbook book, int field_1_index_extern_sheet, String cellRefText)
        {
            ExternalSheet externalSheet = book.GetExternalSheet(field_1_index_extern_sheet);
            StringBuilder sb;

            if (externalSheet != null)
            {
                String wbName    = externalSheet.WorkbookName;
                String sheetName = externalSheet.SheetName;
                if (wbName != null)
                {
                    sb = new StringBuilder(wbName.Length + sheetName.Length + cellRefText.Length + 4);
                    SheetNameFormatter.AppendFormat(sb, wbName, sheetName);
                }
                else
                {
                    sb = new StringBuilder(sheetName.Length + cellRefText.Length + 4);
                    SheetNameFormatter.AppendFormat(sb, sheetName);
                }
                if (externalSheet is ExternalSheetRange)
                {
                    ExternalSheetRange r = (ExternalSheetRange)externalSheet;
                    if (!r.FirstSheetName.Equals(r.LastSheetName))
                    {
                        sb.Append(':');
                        SheetNameFormatter.AppendFormat(sb, r.LastSheetName);
                    }
                }
            }
            else
            {
                String firstSheetName = book.GetSheetFirstNameByExternSheet(field_1_index_extern_sheet);
                String lastSheetName  = book.GetSheetLastNameByExternSheet(field_1_index_extern_sheet);
                sb = new StringBuilder(firstSheetName.Length + cellRefText.Length + 4);
                if (firstSheetName.Length < 1)
                {
                    // What excel does if sheet has been deleted
                    sb.Append("#REF"); // note - '!' Added just once below
                }
                else
                {
                    SheetNameFormatter.AppendFormat(sb, firstSheetName);
                    if (!firstSheetName.Equals(lastSheetName))
                    {
                        sb.Append(':');
                        sb.Append(lastSheetName);
                    }
                }
            }
            sb.Append('!');
            sb.Append(cellRefText);
            return(sb.ToString());
        }
Example #25
0
        /// <summary> Constructs this object from the raw data.  Creates either a
        /// NumberFormulaRecord or a StringFormulaRecord depending on whether
        /// this formula represents a numerical calculation or not
        ///
        /// </summary>
        /// <param name="t">the raw data
        /// </param>
        /// <param name="excelFile">the excel file
        /// </param>
        /// <param name="fr">the formatting records
        /// </param>
        /// <param name="es">the workbook, which contains the external sheet references
        /// </param>
        /// <param name="nt">the name table
        /// </param>
        /// <param name="si">the sheet
        /// </param>
        /// <param name="ws">the workbook settings
        /// </param>
        public FormulaRecord(Record t, File excelFile, FormattingRecords fr, ExternalSheet es, WorkbookMethods nt, SheetImpl si, WorkbookSettings ws) : base(t, fr, si)
        {
            sbyte[] data = getRecord().Data;

            shared = false;

            // Check to see if this forms part of a shared formula
            int grbit = IntegerHelper.getInt(data[14], data[15]);

            if ((grbit & 0x08) != 0)
            {
                shared = true;

                if (data[6] == 0 && data[12] == -1 && data[13] == -1)
                {
                    // It is a shared string formula
                    formula = new SharedStringFormulaRecord(t, excelFile, fr, es, nt, si, ws);
                }
                else
                {
                    // It is a numerical formula
                    double Value = DoubleHelper.getIEEEDouble(data, 6);
                    formula = new SharedNumberFormulaRecord(t, excelFile, Value, fr, es, nt, si);
                }

                return;
            }

            // microsoft and their goddam magic values determine whether this
            // is a string or a number value
            if (data[6] == 0 && data[12] == -1 && data[13] == -1)
            {
                // we have a string
                formula = new StringFormulaRecord(t, excelFile, fr, es, nt, si, ws);
            }
            else if (data[6] == 1 && data[12] == -1 && data[13] == -1)
            {
                // We have a boolean formula
                // multiple values.  Thanks to Frank for spotting this
                formula = new BooleanFormulaRecord(t, fr, es, nt, si);
            }
            else if (data[6] == 2 && data[12] == -1 && data[13] == -1)
            {
                // The cell is in error
                formula = new ErrorFormulaRecord(t, fr, es, nt, si);
            }
            else
            {
                // it is most assuredly a number
                formula = new NumberFormulaRecord(t, fr, es, nt, si);
            }
        }
 /**
  * Constructs this number
  *
  * @param t the data
  * @param excelFile the excel biff data
  * @param v the value
  * @param fr the formatting records
  * @param es the external sheet
  * @param nt the name table
  * @param si the sheet
  */
 public SharedNumberFormulaRecord(Record t,
                                  File excelFile,
                                  double v,
                                  FormattingRecords fr,
                                  ExternalSheet es,
                                  WorkbookMethods nt,
                                  SheetImpl si)
     : base(t, fr, es, nt, si, excelFile.getPos())
 {
     value  = v;
     format = defaultFormat;                // format is set up later from the
     // SharedFormulaRecord
 }
 /**
  * Constructs this number
  *
  * @param t the data
  * @param excelFile the excel biff data
  * @param v the value
  * @param fr the formatting records
  * @param es the external sheet
  * @param nt the name table
  * @param si the sheet
  */
 public SharedNumberFormulaRecord(Record t,
     File excelFile,
     double v,
     FormattingRecords fr,
     ExternalSheet es,
     WorkbookMethods nt,
     SheetImpl si)
     : base(t,fr,es,nt,si,excelFile.getPos())
 {
     value = v;
     format = defaultFormat;    // format is set up later from the
     // SharedFormulaRecord
 }
Example #28
0
        /**
         * Constructs this object from the raw data.  Used when reading in formula
         * strings which evaluate to null (in the case of some IF statements)
         *
         * @param t the raw data
         * @param fr the formatting records
         * @param es the external sheet records
         * @param nt the workbook
         * @param si the sheet impl
         * @param ws the workbook settings
         */
        public StringFormulaRecord(Record t,
                                   FormattingRecords fr,
                                   ExternalSheet es,
                                   WorkbookMethods nt,
                                   SheetImpl si)
            : base(t, fr, si)
        {
            externalSheet = es;
            nameTable     = nt;

            data  = getRecord().getData();
            value = string.Empty;
        }
        /**
         * Constructs this object from the raw data
         *
         * @param t the raw data
         * @param fr the formatting records
         * @param si the sheet
         * @param es the sheet
         * @param nt the name table
         */
        public BooleanFormulaRecord(Record t,FormattingRecords fr,
            ExternalSheet es,WorkbookMethods nt,
            SheetImpl si)
            : base(t,fr,si)
        {
            externalSheet = es;
            nameTable = nt;
            value = false;

            data = getRecord().getData();

            Assert.verify(data[6] != 2);

            value = data[8] == 1 ? true : false;
        }
Example #30
0
        /**
         * Constructor used when copying sheets
         *
         * @param dvsr the record copied from a writable sheet
         */
        public DataValiditySettingsRecord(DataValiditySettingsRecord dvsr,
                                          ExternalSheet es,
                                          WorkbookMethods w,
                                          WorkbookSettings ws)
            : base(Type.DV)
        {
            workbook         = w;
            externalSheet    = es;
            workbookSettings = ws;

            Assert.verify(w != null);
            Assert.verify(es != null);

            data = new byte[dvsr.data.Length];
            System.Array.Copy(dvsr.data, 0, data, 0, data.Length);
        }
Example #31
0
        /**
         * Constructor invoked when parsing a string formula
         *
         * @param s the string to parse
         * @param es the external sheet
         * @exception FormulaException
         */
        public ColumnRange3d(string s, ExternalSheet es)
            : base(es)
        {
            workbook = es;
            int seppos = s.LastIndexOf(":");

            Assert.verify(seppos != -1);
            string startcell = s.Substring(0, seppos);
            string endcell   = s.Substring(seppos + 1);

            // Get the the start cell details
            int    sep         = s.IndexOf('!');
            string cellString  = s.Substring(sep + 1, seppos);
            int    columnFirst = CellReferenceHelper.getColumn(cellString);
            int    rowFirst    = 0;

            // Get the sheet index
            string sheetName    = s.Substring(0, sep);
            int    sheetNamePos = sheetName.LastIndexOf(']');

            // Remove single quotes, if they exist
            if (sheetName[0] == '\'' &&
                sheetName[sheetName.Length - 1] == '\'')
            {
                sheetName = sheetName.Substring(1, sheetName.Length - 1);
            }

            sheet = es.getExternalSheetIndex(sheetName);

            if (sheet < 0)
            {
                throw new FormulaException(FormulaException.SHEET_REF_NOT_FOUND, sheetName);
            }

            // Get the last cell index
            int columnLast = CellReferenceHelper.getColumn(endcell);
            int rowLast    = 0xffff;

            bool columnFirstRelative = true;
            bool rowFirstRelative    = true;
            bool columnLastRelative  = true;
            bool rowLastRelative     = true;

            setRangeData(sheet, columnFirst, columnLast, rowFirst, rowLast,
                         columnFirstRelative, rowFirstRelative,
                         columnLastRelative, rowLastRelative);
        }
 /**
  * Constructor which creates the parse tree out of tokens
  *
  * @param tokens the list of parsed tokens
  * @param rt the cell containing the formula
  * @param es a handle to the external sheet
  * @param nt a handle to the name table
  * @param ws the workbook settings
  * @param pc the parse context
  * @exception FormulaException
  */
 public FormulaParser(byte[] tokens,
     Cell rt,
     ExternalSheet es,
     WorkbookMethods nt,
     WorkbookSettings ws)
 {
     // A null workbook bof means that it is a writable workbook and therefore
     // must be biff8
     if (es.getWorkbookBof() != null &&
         !es.getWorkbookBof().isBiff8())
         {
         throw new FormulaException(FormulaException.BIFF8_SUPPORTED);
         }
     Assert.verify(nt != null);
     parser = new TokenFormulaParser(tokens,rt,es,nt,ws,
                                     ParseContext.DEFAULT);
 }
Example #33
0
 /**
  * Constructor which creates the parse tree out of tokens
  *
  * @param tokens the list of parsed tokens
  * @param rt the cell containing the formula
  * @param es a handle to the external sheet
  * @param nt a handle to the name table
  * @param ws the workbook settings
  * @param pc the parse context
  * @exception FormulaException
  */
 public FormulaParser(byte[] tokens,
                      Cell rt,
                      ExternalSheet es,
                      WorkbookMethods nt,
                      WorkbookSettings ws,
                      ParseContext pc)
 {
     // A null workbook bof means that it is a writable workbook and therefore
     // must be biff8
     if (es.getWorkbookBof() != null &&
         !es.getWorkbookBof().isBiff8())
     {
         throw new FormulaException(FormulaException.BIFF8_SUPPORTED);
     }
     Assert.verify(nt != null);
     parser = new TokenFormulaParser(tokens, rt, es, nt, ws, pc);
 }
        /**
         * Constructor invoked when parsing a string formula
         *
         * @param s the string to parse
         * @param es the external sheet
         * @exception FormulaException
         */
        public ColumnRange3d(string s,ExternalSheet es)
            : base(es)
        {
            workbook = es;
            int seppos = s.LastIndexOf(":");
            Assert.verify(seppos != -1);
            string startcell = s.Substring(0,seppos);
            string endcell = s.Substring(seppos + 1);

            // Get the the start cell details
            int sep = s.IndexOf('!');
            string cellString = s.Substring(sep + 1,seppos);
            int columnFirst = CellReferenceHelper.getColumn(cellString);
            int rowFirst = 0;

            // Get the sheet index
            string sheetName = s.Substring(0,sep);
            int sheetNamePos = sheetName.LastIndexOf(']');

            // Remove single quotes, if they exist
            if (sheetName[0] == '\'' &&
                sheetName[sheetName.Length - 1] == '\'')
                {
                sheetName = sheetName.Substring(1,sheetName.Length - 1);
                }

            sheet = es.getExternalSheetIndex(sheetName);

            if (sheet < 0)
                {
                throw new FormulaException(FormulaException.SHEET_REF_NOT_FOUND,sheetName);
                }

            // Get the last cell index
            int columnLast = CellReferenceHelper.getColumn(endcell);
            int rowLast = 0xffff;

            bool columnFirstRelative = true;
            bool rowFirstRelative = true;
            bool columnLastRelative = true;
            bool rowLastRelative = true;

            setRangeData(sheet,columnFirst,columnLast,rowFirst,rowLast,
                         columnFirstRelative,rowFirstRelative,
                         columnLastRelative,rowLastRelative);
        }
        /**
         * Constructor
         */
        public TokenFormulaParser(byte[] data,
                                  Cell c,
                                  ExternalSheet es,
                                  WorkbookMethods nt,
                                  WorkbookSettings ws,
                                  ParseContext pc)
        {
            tokenData    = data;
            pos          = 0;
            relativeTo   = c;
            workbook     = es;
            nameTable    = nt;
            tokenStack   = new Stack <ParseItem>();
            settings     = ws;
            parseContext = pc;

            Assert.verify(nameTable != null);
        }
        /**
         * Constructs this object from the raw data
         *
         * @param t the raw data
         * @param fr the formatting record
         * @param es the external sheet
         * @param nt the name table
         * @param si the sheet
         */
        public NumberFormulaRecord(Record t, FormattingRecords fr,
                                   ExternalSheet es, WorkbookMethods nt,
                                   SheetImpl si)
            : base(t, fr, si)
        {
            externalSheet = es;
            nameTable     = nt;
            data          = getRecord().getData();

            format = fr.getNumberFormat(getXFIndex());

            if (format == null)
            {
                format = defaultFormat;
            }

            value = DoubleHelper.getIEEEDouble(data, 6);
        }
Example #37
0
        /**
         * Constructs this object from the raw data.  Creates either a
         * NumberFormulaRecord or a StringFormulaRecord depending on whether
         * this formula represents a numerical calculation or not
         *
         * @param t the raw data
         * @param fr the base shared formula
         * @param es the workbook, which contains the external sheet references
         * @param nt the workbook
         * @param si the sheet
         */
        public SharedFormulaRecord(Record t, BaseSharedFormulaRecord fr,
                                   ExternalSheet es, WorkbookMethods nt,
                                   SheetImpl si)
        {
            sheet = si;
            byte[] data = t.getData();

            firstRow = IntegerHelper.getInt(data[0], data[1]);
            lastRow  = IntegerHelper.getInt(data[2], data[3]);
            firstCol = data[4] & 0xff;
            lastCol  = data[5] & 0xff;

            formulas = new ArrayList();

            templateFormula = fr;

            tokens = new byte[data.Length - 10];
            System.Array.Copy(data, 10, tokens, 0, tokens.Length);
        }
Example #38
0
        /// <summary> Constructs this object from the raw data.  Creates either a
        /// NumberFormulaRecord or a StringFormulaRecord depending on whether
        /// this formula represents a numerical calculation or not
        ///
        /// </summary>
        /// <param name="t">the raw data
        /// </param>
        /// <param name="fr">the base shared formula
        /// </param>
        /// <param name="es">the workbook, which contains the external sheet references
        /// </param>
        /// <param name="nt">the workbook
        /// </param>
        /// <param name="si">the sheet
        /// </param>
        public SharedFormulaRecord(Record t, BaseSharedFormulaRecord fr, ExternalSheet es, WorkbookMethods nt, SheetImpl si)
        {
            externalSheet = es;
            nameTable     = nt;
            sheet         = si;
            sbyte[] data = t.Data;

            firstRow = IntegerHelper.getInt(data[0], data[1]);
            lastRow  = IntegerHelper.getInt(data[2], data[3]);
            firstCol = (int)(data[4] & 0xff);
            lastCol  = (int)(data[5] & 0xff);

            formulas = new ArrayList();

            templateFormula = fr;

            tokens = new sbyte[data.Length - 10];
            Array.Copy(data, 10, tokens, 0, tokens.Length);
        }
 /**
  * Constructor
  * @param f
  * @param ws
  */
 public StringFormulaParser(string f,ExternalSheet es,WorkbookMethods nt,WorkbookSettings ws,ParseContext pc)
 {
     formula = f;
     settings = ws;
     externalSheet = es;
     nameTable = nt;
     parseContext = pc;
 }
Example #40
0
 /**
  * Constructor
  *
  * @param es the external sheet
  */
 public Area3d(ExternalSheet es)
 {
     workbook = es;
 }
 /**
  * Gets the fully qualified cell reference given the column, row
  * external sheet reference etc
  *
  * @param sheet
  * @param column
  * @param row
  * @param workbook
  * @return the cell reference in the form 'Sheet 1'!A1
  */
 public static string getCellReference(int sheet,int column,int row,
     ExternalSheet workbook)
 {
     StringBuilder sb = new StringBuilder();
     getCellReference(sheet,column,row,workbook,sb);
     return sb.ToString();
 }
 /**
  * Gets the fully qualified cell reference given the column, row
  * external sheet reference etc
  *
  * @param sheet
  * @param column
  * @param row
  * @param workbook
  * @param buf
  */
 public static void getCellReference(int sheet,int column,int row,
     ExternalSheet workbook,StringBuilder buf)
 {
     // Quotes are added by the WorkbookParser
     string name = workbook.getExternalSheetName(sheet);
     buf.Append(StringHelper.replace(name, "\'", "\'\'"));
     buf.Append(sheetInd);
     getCellReference(column,row,buf);
 }
        /**
         * Constructor used when copying sheets
         *
         * @param dvsr the record copied from a writable sheet
         */
        public DataValiditySettingsRecord(DataValiditySettingsRecord dvsr,
            ExternalSheet es,
            WorkbookMethods w,
            WorkbookSettings ws)
            : base(Type.DV)
        {
            workbook = w;
            externalSheet = es;
            workbookSettings = ws;

            Assert.verify(w != null);
            Assert.verify(es != null);

            data = new byte[dvsr.data.Length];
            System.Array.Copy(dvsr.data,0,data,0,data.Length);
        }
        /**
         * Copy constructor used to copy from read to write
         */
        public DataValidation(DataValidation dv,
            ExternalSheet es,
            WorkbookMethods wm,
            WorkbookSettings ws)
        {
            workbook = wm;
            externalSheet = es;
            workbookSettings = ws;
            copied = true;
            validityList = new DataValidityListRecord(dv.getDataValidityList());

            validitySettings = new ArrayList();
            DataValiditySettingsRecord[] settings = dv.getDataValiditySettings();

            for (int i = 0; i < settings.Length; i++)
                validitySettings.Add(new DataValiditySettingsRecord(settings[i],externalSheet,workbook,workbookSettings));
        }
Example #45
0
 public void setExternalSheet(ExternalSheet es)
 {
     externalSheet = es;
 }
Example #46
0
        /**
         * Constructor
         */
        public DVParser(byte[] data,
            ExternalSheet es,
            WorkbookMethods nt,
            WorkbookSettings ws)
        {
            Assert.verify(nt != null);

            wasCopied = false;
            int options = IntegerHelper.getInt(data[0],data[1],data[2],data[3]);

            int typeVal = options & 0xf;
            type = DVType.getType(typeVal);

            int errorStyleVal = (options & 0x70) >> 4;
            errorStyle = ErrorStyle.getErrorStyle(errorStyleVal);

            int conditionVal = (options & 0xf00000) >> 20;
            condition = Condition.getCondition(conditionVal);

            stringListGiven = (options & STRING_LIST_GIVEN_MASK) != 0;
            emptyCellsAllowed = (options & EMPTY_CELLS_ALLOWED_MASK) != 0;
            suppressArrow = (options & SUPPRESS_ARROW_MASK) != 0;
            showPrompt = (options & SHOW_PROMPT_MASK) != 0;
            showError = (options & SHOW_ERROR_MASK) != 0;

            int pos = 4;
            int length = IntegerHelper.getInt(data[pos],data[pos + 1]);
            if (length > 0 && data[pos + 2] == 0)
                {
                promptTitle = StringHelper.getString(data,length,pos + 3,ws);
                pos += length + 3;
                }
            else if (length > 0)
                {
                promptTitle = StringHelper.getUnicodeString(data,length,pos + 3);
                pos += length * 2 + 3;
                }
            else
                {
                pos += 3;
                }

            length = IntegerHelper.getInt(data[pos],data[pos + 1]);
            if (length > 0 && data[pos + 2] == 0)
                {
                errorTitle = StringHelper.getString(data,length,pos + 3,ws);
                pos += length + 3;
                }
            else if (length > 0)
                {
                errorTitle = StringHelper.getUnicodeString(data,length,pos + 3);
                pos += length * 2 + 3;
                }
            else
                {
                pos += 3;
                }

            length = IntegerHelper.getInt(data[pos],data[pos + 1]);
            if (length > 0 && data[pos + 2] == 0)
                {
                promptText = StringHelper.getString(data,length,pos + 3,ws);
                pos += length + 3;
                }
            else if (length > 0)
                {
                promptText = StringHelper.getUnicodeString(data,length,pos + 3);
                pos += length * 2 + 3;
                }
            else
                {
                pos += 3;
                }

            length = IntegerHelper.getInt(data[pos],data[pos + 1]);
            if (length > 0 && data[pos + 2] == 0)
                {
                errorText = StringHelper.getString(data,length,pos + 3,ws);
                pos += length + 3;
                }
            else if (length > 0)
                {
                errorText = StringHelper.getUnicodeString(data,length,pos + 3);
                pos += length * 2 + 3;
                }
            else
                {
                pos += 3;
                }

            int formula1Length = IntegerHelper.getInt(data[pos],data[pos + 1]);
            pos += 4;
            int formula1Pos = pos;
            pos += formula1Length;

            int formula2Length = IntegerHelper.getInt(data[pos],data[pos + 1]);
            pos += 4;
            int formula2Pos = pos;
            pos += formula2Length;

            pos += 2;

            row1 = IntegerHelper.getInt(data[pos],data[pos + 1]);
            pos += 2;

            row2 = IntegerHelper.getInt(data[pos],data[pos + 1]);
            pos += 2;

            column1 = IntegerHelper.getInt(data[pos],data[pos + 1]);
            pos += 2;

            column2 = IntegerHelper.getInt(data[pos],data[pos + 1]);
            pos += 2;

            hasExtendedCellsValidation = (row1 == row2 && column1 == column2) ? false : true;

            // Do the formulas
            try
                {
                // First, create a temporary  blank cell for any formula relative
                // references
                EmptyCell tmprt = new EmptyCell(column1,row1);

                if (formula1Length != 0)
                    {
                    byte[] tokens = new byte[formula1Length];
                    System.Array.Copy(data,formula1Pos,tokens,0,formula1Length);
                    formula1 = new FormulaParser(tokens,tmprt,es,nt,ws,
                                                 ParseContext.DATA_VALIDATION);
                    formula1.parse();
                    }

                if (formula2Length != 0)
                    {
                    byte[] tokens = new byte[formula2Length];
                    System.Array.Copy(data,formula2Pos,tokens,0,formula2Length);
                    formula2 = new FormulaParser(tokens,tmprt,es,nt,ws,
                                                 ParseContext.DATA_VALIDATION);
                    formula2.parse();
                    }
                }
            catch (FormulaException e)
                {
                //logger.warn(e.Message + " for cells " +
                //    CellReferenceHelper.getCellReference(column1,row1) + "-" +
                //    CellReferenceHelper.getCellReference(column2,row2));
                }
        }
        /**
         * Constructs this object from the raw data.  We need to use the excelFile
         * to retrieve the string record which follows this formula record
         *
         * @param t the raw data
         * @param excelFile the excel file
         * @param fr the formatting records
         * @param es the external sheet records
         * @param nt the workbook
         * @param si the sheet impl
         * @param ws the workbook settings
         */
        public StringFormulaRecord(Record t,File excelFile,
            FormattingRecords fr,
            ExternalSheet es,
            WorkbookMethods nt,
            SheetImpl si,
            WorkbookSettings ws)
            : base(t,fr,si)
        {
            externalSheet = es;
            nameTable = nt;

            data = getRecord().getData();

            int pos = excelFile.getPos();

            // Look for the string record in one of the records after the
            // formula.  Put a cap on it to prevent looping

            Record nextRecord = excelFile.next();
            int count = 0;
            while (nextRecord.getType() != Type.STRING && count < 4)
                {
                nextRecord = excelFile.next();
                count++;
                }
            Assert.verify(count < 4," @ " + pos);
            byte[] stringData = nextRecord.getData();

            // Read in any continuation records
            nextRecord = excelFile.peek();
            while (nextRecord.getType() == Type.CONTINUE)
                {
                nextRecord = excelFile.next(); // move the pointer within the data
                byte[] d = new byte[stringData.Length + nextRecord.getLength() - 1];
                System.Array.Copy(stringData,0,d,0,stringData.Length);
                System.Array.Copy(nextRecord.getData(),1,d,
                                                 stringData.Length,nextRecord.getLength() - 1);
                stringData = d;
                nextRecord = excelFile.peek();
                }
            readString(stringData,ws);
        }
        /**
         * Constructs this object from the raw data.  Used when reading in formula
         * strings which evaluate to null (in the case of some IF statements)
         *
         * @param t the raw data
         * @param fr the formatting records
         * @param es the external sheet records
         * @param nt the workbook
         * @param si the sheet impl
         * @param ws the workbook settings
         */
        public StringFormulaRecord(Record t,
            FormattingRecords fr,
            ExternalSheet es,
            WorkbookMethods nt,
            SheetImpl si)
            : base(t,fr,si)
        {
            externalSheet = es;
            nameTable = nt;

            data = getRecord().getData();
            value = string.Empty;
        }
        /**
         * Constructs this object from the raw data.  Creates either a
         * NumberFormulaRecord or a StringFormulaRecord depending on whether
         * this formula represents a numerical calculation or not
         *
         * @param t the raw data
         * @param fr the base shared formula
         * @param es the workbook, which contains the external sheet references
         * @param nt the workbook
         * @param si the sheet
         */
        public SharedFormulaRecord(Record t,BaseSharedFormulaRecord fr,
            ExternalSheet es,WorkbookMethods nt,
            SheetImpl si)
        {
            sheet = si;
            byte[] data = t.getData();

            firstRow = IntegerHelper.getInt(data[0],data[1]);
            lastRow = IntegerHelper.getInt(data[2],data[3]);
            firstCol = data[4] & 0xff;
            lastCol = data[5] & 0xff;

            formulas = new ArrayList();

            templateFormula = fr;

            tokens = new byte[data.Length - 10];
            System.Array.Copy(data,10,tokens,0,tokens.Length);
        }
 /**
  * Constructs this string formula
  *
  * @param t the record
  * @param excelFile the excel file
  * @param fr the formatting record
  * @param es the external sheet
  * @param nt the workbook
  * @param si the sheet
  * @param dummy the overload indicator
  */
 public SharedStringFormulaRecord(Record t,
     File excelFile,
     FormattingRecords fr,
     ExternalSheet es,
     WorkbookMethods nt,
     SheetImpl si,
     EmptyString dummy)
     : base(t,fr,es,nt,si,excelFile.getPos())
 {
     value = string.Empty;
 }
        /**
         * Constructs this string formula
         *
         * @param t the record
         * @param excelFile the excel file
         * @param fr the formatting record
         * @param es the external sheet
         * @param nt the workbook
         * @param si the sheet
         * @param ws the workbook settings
         */
        public SharedStringFormulaRecord(Record t,
            File excelFile,
            FormattingRecords fr,
            ExternalSheet es,
            WorkbookMethods nt,
            SheetImpl si,
            WorkbookSettings ws)
            : base(t,fr,es,nt,si,excelFile.getPos())
        {
            int pos = excelFile.getPos();

            // Save the position in the excel file
            int filepos = excelFile.getPos();

            // Look for the string record in one of the records after the
            // formula.  Put a cap on it to prevent ednas
            Record nextRecord = excelFile.next();
            int count = 0;
            while (nextRecord.getType() != Type.STRING && count < 4)
                {
                nextRecord = excelFile.next();
                count++;
                }
            Assert.verify(count < 4," @ " + pos);

            byte[] stringData = nextRecord.getData();

            // Read in any continuation records
            nextRecord = excelFile.peek();
            while (nextRecord.getType() == Type.CONTINUE)
                {
                nextRecord = excelFile.next(); // move the pointer within the data
                byte[] d = new byte[stringData.Length + nextRecord.getLength() - 1];
                System.Array.Copy(stringData,0,d,0,stringData.Length);
                System.Array.Copy(nextRecord.getData(),1,d,
                                                 stringData.Length,nextRecord.getLength() - 1);
                stringData = d;
                nextRecord = excelFile.peek();
                }

            int chars = IntegerHelper.getInt(stringData[0],stringData[1]);

            bool unicode = false;
            int startpos = 3;
            if (stringData.Length == chars + 2)
                {
                // string might only consist of a one byte length indicator, instead
                // of the more normal 2
                startpos = 2;
                unicode = false;
                }
            else if (stringData[2] == 0x1)
                {
                // unicode string, two byte length indicator
                startpos = 3;
                unicode = true;
                }
            else
                {
                // ascii string, two byte length indicator
                startpos = 3;
                unicode = false;
                }

            if (!unicode)
                {
                value = StringHelper.getString(stringData,chars,startpos,ws);
                }
            else
                {
                value = StringHelper.getUnicodeString(stringData,chars,startpos);
                }

            // Restore the position in the excel file, to enable the SHRFMLA
            // record to be picked up
            excelFile.setPos(filepos);
        }
 /**
  * Constructor used to create writable data validations
  */
 public DataValidation(uint objId,
     ExternalSheet es,
     WorkbookMethods wm,
     WorkbookSettings ws)
 {
     workbook = wm;
     externalSheet = es;
     workbookSettings = ws;
     validitySettings = new ArrayList();
     comboBoxObjectId = objId;
     copied = false;
 }
        /**
         * Constructs this object from a string
         *
         * @param s the string
         * @param w the external sheet
         * @exception FormulaException
         */
        public CellReference3d(string s,ExternalSheet w)
        {
            workbook = w;
            columnRelative = true;
            rowRelative = true;

            // Get the cell details
            int sep = s.IndexOf('!');
            string cellString = s.Substring(sep + 1);
            column = CellReferenceHelper.getColumn(cellString);
            row = CellReferenceHelper.getRow(cellString);

            // Get the sheet index
            string sheetName = s.Substring(0,sep);

            // Remove single quotes, if they exist
            if (sheetName[0] == '\'' && sheetName[sheetName.Length - 1] == '\'')
                sheetName = sheetName.Substring(1,sheetName.Length - 1);
            sheet = w.getExternalSheetIndex(sheetName);

            if (sheet < 0)
                throw new FormulaException(FormulaException.SHEET_REF_NOT_FOUND,sheetName);
        }
        /**
         * Constructs this object from the raw data
         *
         * @param t the raw data
         * @param fr the formatting record
         * @param es the external sheet
         * @param nt the name table
         * @param si the sheet
         */
        public NumberFormulaRecord(Record t,FormattingRecords fr,
            ExternalSheet es,WorkbookMethods nt,
            SheetImpl si)
            : base(t,fr,si)
        {
            externalSheet = es;
            nameTable = nt;
            data = getRecord().getData();

            format = fr.getNumberFormat(getXFIndex());

            if (format == null)
                {
                format = defaultFormat;
                }

            value = DoubleHelper.getIEEEDouble(data,6);
        }
 /**
  * Gets the fully qualified cell reference given the column, row
  * external sheet reference etc
  *
  * @param sheet
  * @param column
  * @param colabs TRUE if the column is an absolute reference
  * @param row
  * @param rowabs TRUE if the row is an absolute reference
  * @param workbook
  * @param buf
  */
 public static void getCellReference(int sheet,int column,bool colabs,
     int row,bool rowabs,
     ExternalSheet workbook,StringBuilder buf)
 {
     // WorkbookParser now appends quotes and escapes apostrophes
     string name = workbook.getExternalSheetName(sheet);
     buf.Append(name);
     buf.Append(sheetInd);
     getCellReference(column,colabs,row,rowabs,buf);
 }
Example #56
0
        /**
         * Called by the cell value when the cell features are added to the sheet
         */
        public void setCell(int col,
            int row,
            ExternalSheet es,
            WorkbookMethods nt,
            WorkbookSettings ws)
        {
            // If this is part of an extended cells validation, then do nothing
            // as this will already have been called and parsed when the top left
            // cell was added
            if (hasExtendedCellsValidation)
                {
                return;
                }

            row1 = row;
            row2 = row;
            column1 = col;
            column2 = col;

            formula1 = new FormulaParser(formula1String,
                                         es,nt,ws,
                                         ParseContext.DATA_VALIDATION);
            formula1.parse();

            if (formula2String != null)
                {
                formula2 = new FormulaParser(formula2String,
                                             es,nt,ws,
                                             ParseContext.DATA_VALIDATION);
                formula2.parse();
                }
        }
        /**
         * If this formula was on an imported sheet, check that
         * cell references to another sheet are warned appropriately
         *
         * @return TRUE if this formula was able to be imported, FALSE otherwise
         */
        public bool handleImportedCellReferences(ExternalSheet es,
            WorkbookMethods mt,
            WorkbookSettings ws)
        {
            try
                {
                if (parser == null)
                    {
                    byte[] formulaData = formula.getFormulaData();
                    byte[] formulaBytes = new byte[formulaData.Length - 16];
                    System.Array.Copy(formulaData, 16,
                                     formulaBytes, 0, formulaBytes.Length);
                    parser = new FormulaParser(formulaBytes,
                                               this,
                                               es, mt, ws);
                    parser.parse();
                    }

                return parser.handleImportedCellReferences();
                }
            catch (FormulaException e)
                {
                //logger.warn("cannot import formula:  " + e.Message);
                return false;
                }
        }
        /**
         * Constructor
         */
        public TokenFormulaParser(byte[] data,
            Cell c,
            ExternalSheet es,
            WorkbookMethods nt,
            WorkbookSettings ws,
            ParseContext pc)
        {
            tokenData = data;
            pos = 0;
            relativeTo = c;
            workbook = es;
            nameTable = nt;
            tokenStack = new Stack<ParseItem>();
            settings = ws;
            parseContext = pc;

            Assert.verify(nameTable != null);
        }
 /**
  * Constructor
  *
  * @param rt the cell containing the formula
  * @param w the list of external sheets
  */
 public CellReference3d(Cell rt,ExternalSheet w)
 {
     relativeTo = rt;
     workbook = w;
 }
 /**
  * Constructor which creates the parse tree out of the string
  *
  * @param form the formula string
  * @param es the external sheet handle
  * @param nt the name table
  * @param ws the workbook settings
  * @param pc the context of the parse
  */
 public FormulaParser(string form,
     ExternalSheet es,
     WorkbookMethods nt,
     WorkbookSettings ws,
     ParseContext pc)
 {
     parser = new StringFormulaParser(form,es,nt,ws,pc);
 }