예제 #1
0
        /**
         * Add a row to the _sheet
         *
         * @param AddLow whether to Add the row to the low level model - false if its already there
         */

        private void AddRow(HSSFRow row, bool addLow)
        {
            rows[row.RowNum] = row;

            if (addLow)
            {
                _sheet.AddRow(row.RowRecord);
            }
            bool firstRow = rows.Count == 1;
            if (row.RowNum > LastRowNum || firstRow)
            {
                lastrow = row.RowNum;
            }
            if (row.RowNum < FirstRowNum || firstRow)
            {
                firstrow = row.RowNum;
            }
        }
예제 #2
0
        /// <summary>
        /// Used internally to Create a high level Row object from a low level row object.
        /// USed when Reading an existing file
        /// </summary>
        /// <param name="row">low level record to represent as a high level Row and Add to _sheet.</param>
        /// <returns>HSSFRow high level representation</returns>
        private HSSFRow CreateRowFromRecord(RowRecord row)
        {
            HSSFRow hrow = new HSSFRow(_workbook, this, row);

            AddRow(hrow, false);
            return hrow;
        }
예제 #3
0
 /// <summary>
 /// Create a new row within the _sheet and return the high level representation
 /// </summary>
 /// <param name="rownum">The row number.</param>
 /// <returns></returns>
 /// @see org.apache.poi.hssf.usermodel.HSSFRow
 /// @see #RemoveRow(HSSFRow)
 public Zephyr.Utils.NPOI.SS.UserModel.IRow CreateRow(int rownum)
 {
     HSSFRow row = new HSSFRow(_workbook, this, rownum);
     AddRow(row, true);
     return row;
 }
예제 #4
0
 private void NotifyRowShifting(HSSFRow row)
 {
     String msg = "Row[rownum=" + row.RowNum + "] contains cell(s) included in a multi-cell array formula. " +
             "You cannot change part of an array.";
     foreach (ICell cell in row.Cells)
     {
         HSSFCell hcell = (HSSFCell)cell;
         if (hcell.IsPartOfArrayFormulaGroup)
         {
             hcell.NotifyArrayFormulaChanging(msg);
         }
     }
 }
예제 #5
0
        /// <summary>
        /// Goes through the Workbook, optimising the fonts by
        /// removing duplicate ones.
        /// For now, only works on fonts used in HSSFCellStyle
        /// and HSSFRichTextString. Any other font uses
        /// (eg charts, pictures) may well end up broken!
        /// This can be a slow operation, especially if you have
        /// lots of cells, cell styles or rich text strings
        /// </summary>
        /// <param name="workbook">The workbook in which to optimise the fonts</param>
        public static void OptimiseFonts(HSSFWorkbook workbook)
        {
            // Where each font has ended up, and if we need to
            //  delete the record for it. Start off with no change
            short[] newPos =
                new short[workbook.Workbook.NumberOfFontRecords + 1];
            bool[] zapRecords = new bool[newPos.Length];
            for (int i = 0; i < newPos.Length; i++)
            {
                newPos[i]     = (short)i;
                zapRecords[i] = false;
            }

            // Get each font record, so we can do deletes
            //  without Getting confused
            FontRecord[] frecs = new FontRecord[newPos.Length];
            for (int i = 0; i < newPos.Length; i++)
            {
                // There is no 4!
                if (i == 4)
                {
                    continue;
                }

                frecs[i] = workbook.Workbook.GetFontRecordAt(i);
            }

            // Loop over each font, seeing if it is the same
            //  as an earlier one. If it is, point users of the
            //  later duplicate copy to the earlier one, and
            //  mark the later one as needing deleting
            // Note - don't change built in fonts (those before 5)
            for (int i = 5; i < newPos.Length; i++)
            {
                // Check this one for being a duplicate
                //  of an earlier one
                int earlierDuplicate = -1;
                for (int j = 0; j < i && earlierDuplicate == -1; j++)
                {
                    if (j == 4)
                    {
                        continue;
                    }

                    FontRecord frCheck = workbook.Workbook.GetFontRecordAt(j);
                    if (frCheck.SameProperties(frecs[i]))
                    {
                        earlierDuplicate = j;
                    }
                }

                // If we got a duplicate, mark it as such
                if (earlierDuplicate != -1)
                {
                    newPos[i]     = (short)earlierDuplicate;
                    zapRecords[i] = true;
                }
            }

            // Update the new positions based on
            //  deletes that have occurred between
            //  the start and them
            // Only need to worry about user fonts
            for (int i = 5; i < newPos.Length; i++)
            {
                // Find the number deleted to that
                //  point, and adjust
                short preDeletePos = newPos[i];
                short newPosition  = preDeletePos;
                for (int j = 0; j < preDeletePos; j++)
                {
                    if (zapRecords[j])
                    {
                        newPosition--;
                    }
                }

                // Update the new position
                newPos[i] = newPosition;
            }

            // Zap the un-needed user font records
            for (int i = 5; i < newPos.Length; i++)
            {
                if (zapRecords[i])
                {
                    workbook.Workbook.RemoveFontRecord(
                        frecs[i]
                        );
                }
            }

            // Tell HSSFWorkbook that it needs to
            //  re-start its HSSFFontCache
            workbook.ResetFontCache();

            // Update the cell styles to point at the
            //  new locations of the fonts
            for (int i = 0; i < workbook.Workbook.NumExFormats; i++)
            {
                ExtendedFormatRecord xfr = workbook.Workbook.GetExFormatAt(i);
                xfr.FontIndex = (
                    newPos[xfr.FontIndex]
                    );
            }

            // Update the rich text strings to point at
            //  the new locations of the fonts
            // Remember that one underlying unicode string
            //  may be shared by multiple RichTextStrings!
            ArrayList doneUnicodeStrings = new ArrayList();

            for (int sheetNum = 0; sheetNum < workbook.NumberOfSheets; sheetNum++)
            {
                Zephyr.Utils.NPOI.SS.UserModel.ISheet s = workbook.GetSheetAt(sheetNum);
                IEnumerator rIt = s.GetRowEnumerator();
                while (rIt.MoveNext())
                {
                    HSSFRow     row = (HSSFRow)rIt.Current;
                    IEnumerator cIt = row.GetEnumerator();
                    while (cIt.MoveNext())
                    {
                        ICell cell = (HSSFCell)cIt.Current;
                        if (cell.CellType == Zephyr.Utils.NPOI.SS.UserModel.CellType.STRING)
                        {
                            HSSFRichTextString rtr = (HSSFRichTextString)cell.RichStringCellValue;
                            UnicodeString      u   = rtr.RawUnicodeString;

                            // Have we done this string already?
                            if (!doneUnicodeStrings.Contains(u))
                            {
                                // Update for each new position
                                for (short i = 5; i < newPos.Length; i++)
                                {
                                    if (i != newPos[i])
                                    {
                                        u.SwapFontUse(i, newPos[i]);
                                    }
                                }

                                // Mark as done
                                doneUnicodeStrings.Add(u);
                            }
                        }
                    }
                }
            }
        }
예제 #6
0
        /// <summary>
        /// Goes through the Wokrbook, optimising the cell styles
        /// by removing duplicate ones.
        /// For best results, optimise the fonts via a call to
        /// OptimiseFonts(HSSFWorkbook) first
        /// </summary>
        /// <param name="workbook">The workbook in which to optimise the cell styles</param>
        public static void OptimiseCellStyles(HSSFWorkbook workbook)
        {
            // Where each style has ended up, and if we need to
            //  delete the record for it. Start off with no change
            short[] newPos =
                new short[workbook.Workbook.NumExFormats];
            bool[] zapRecords = new bool[newPos.Length];
            for (int i = 0; i < newPos.Length; i++)
            {
                newPos[i]     = (short)i;
                zapRecords[i] = false;
            }

            // Get each style record, so we can do deletes
            //  without Getting confused
            ExtendedFormatRecord[] xfrs = new ExtendedFormatRecord[newPos.Length];
            for (int i = 0; i < newPos.Length; i++)
            {
                xfrs[i] = workbook.Workbook.GetExFormatAt(i);
            }

            // Loop over each style, seeing if it is the same
            //  as an earlier one. If it is, point users of the
            //  later duplicate copy to the earlier one, and
            //  mark the later one as needing deleting
            // Only work on user added ones, which come after 20
            for (int i = 21; i < newPos.Length; i++)
            {
                // Check this one for being a duplicate
                //  of an earlier one
                int earlierDuplicate = -1;
                for (int j = 0; j < i && earlierDuplicate == -1; j++)
                {
                    ExtendedFormatRecord xfCheck = workbook.Workbook.GetExFormatAt(j);
                    if (xfCheck.Equals(xfrs[i]))
                    {
                        earlierDuplicate = j;
                    }
                }

                // If we got a duplicate, mark it as such
                if (earlierDuplicate != -1)
                {
                    newPos[i]     = (short)earlierDuplicate;
                    zapRecords[i] = true;
                }
            }

            // Update the new positions based on
            //  deletes that have occurred between
            //  the start and them
            // Only work on user added ones, which come after 20
            for (int i = 21; i < newPos.Length; i++)
            {
                // Find the number deleted to that
                //  point, and adjust
                short preDeletePos = newPos[i];
                short newPosition  = preDeletePos;
                for (int j = 0; j < preDeletePos; j++)
                {
                    if (zapRecords[j])
                    {
                        newPosition--;
                    }
                }

                // Update the new position
                newPos[i] = newPosition;
            }

            // Zap the un-needed user style records
            for (int i = 21; i < newPos.Length; i++)
            {
                if (zapRecords[i])
                {
                    workbook.Workbook.RemoveExFormatRecord(
                        xfrs[i]
                        );
                }
            }

            // Finally, update the cells to point at
            //  their new extended format records
            for (int sheetNum = 0; sheetNum < workbook.NumberOfSheets; sheetNum++)
            {
                HSSFSheet   s   = (HSSFSheet)workbook.GetSheetAt(sheetNum);
                IEnumerator rIt = s.GetRowEnumerator();
                while (rIt.MoveNext())
                {
                    HSSFRow     row = (HSSFRow)rIt.Current;
                    IEnumerator cIt = row.GetEnumerator();
                    while (cIt.MoveNext())
                    {
                        ICell cell  = (HSSFCell)cIt.Current;
                        short oldXf = ((HSSFCell)cell).CellValueRecord.XFIndex;
                        Zephyr.Utils.NPOI.SS.UserModel.ICellStyle newStyle = workbook.GetCellStyleAt(
                            newPos[oldXf]
                            );
                        cell.CellStyle = (newStyle);
                    }
                }
            }
        }