C# HSSFWorkbook.GetCellStyleAt方法代码示例

        /**
         *  This method attempt to find an already existing HSSFCellStyle that matches
         *  what you want the style to be. If it does not find the style, then it
         *  Creates a new one. If it does Create a new one, then it applies the
         *  propertyName and propertyValue to the style. This is necessary because
         *  Excel has an upper limit on the number of Styles that it supports.
         *
         *@param  workbook               The workbook that is being worked with.
         *@param  propertyName           The name of the property that is to be
         *      changed.
         *@param  propertyValue          The value of the property that is to be
         *      changed.
         *@param  cell                   The cell that needs it's style changes
         *@exception  NestableException  Thrown if an error happens.
         */
        public static void SetCellStyleProperty(NPOI.SS.UserModel.ICell cell, HSSFWorkbook workbook, String propertyName, Object propertyValue)
        {
            NPOI.SS.UserModel.ICellStyle originalStyle = cell.CellStyle;
            NPOI.SS.UserModel.ICellStyle newStyle = null;
            Hashtable values = GetFormatProperties(originalStyle);
            values[propertyName] = propertyValue;

            // index seems like what  index the cellstyle is in the list of styles for a workbook.
            // not good to compare on!
            short numberCellStyles = workbook.NumCellStyles;

            for (short i = 0; i < numberCellStyles; i++)
            {
                NPOI.SS.UserModel.ICellStyle wbStyle = workbook.GetCellStyleAt(i);
                Hashtable wbStyleMap = GetFormatProperties(wbStyle);

                if (wbStyleMap.Equals(values))
                {
                    newStyle = wbStyle;
                    break;
                }
            }

            if (newStyle == null)
            {
                newStyle = workbook.CreateCellStyle();
                SetFormatProperties(newStyle, workbook, values);
            }

            cell.CellStyle = (newStyle);
        }

        public void Test49689()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            ISheet s = wb.CreateSheet("Test");
            IRow r = s.CreateRow(0);
            ICell c = r.CreateCell(0);

            HSSFCellStyle cs1 = (HSSFCellStyle)wb.CreateCellStyle();
            HSSFCellStyle cs2 = (HSSFCellStyle)wb.CreateCellStyle();
            HSSFCellStyle cs3 = (HSSFCellStyle)wb.CreateCellStyle();
            
            Assert.AreEqual(21, cs1.Index);
            cs1.UserStyleName = ("Testing");

            Assert.AreEqual(22, cs2.Index);
            cs2.UserStyleName = ("Testing 2");

            Assert.AreEqual(23, cs3.Index);
            cs3.UserStyleName = ("Testing 3");

            // Set one
            c.CellStyle = (cs1);

            // Write out and read back
            wb = WriteOutAndReadBack(wb);

            // Re-check
            Assert.AreEqual("Testing", ((HSSFCellStyle)wb.GetCellStyleAt((short)21)).UserStyleName);
            Assert.AreEqual("Testing 2", ((HSSFCellStyle)wb.GetCellStyleAt((short)22)).UserStyleName);
            Assert.AreEqual("Testing 3", ((HSSFCellStyle)wb.GetCellStyleAt((short)23)).UserStyleName);
        }

//.........这里部分代码省略.........
                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;
                }
            }
            // Loop over all the cells in the file, and identify any user defined
            //  styles aren't actually being used (don't touch built-in ones)
            for (int sheetNum = 0; sheetNum < workbook.NumberOfSheets; sheetNum++)
            {
                HSSFSheet s = (HSSFSheet)workbook.GetSheetAt(sheetNum);
                foreach (IRow row in s)
                {
                    foreach (ICell cellI in row)
                    {
                        HSSFCell cell = (HSSFCell)cellI;
                        short oldXf = cell.CellValueRecord.XFIndex;
                        isUsed[oldXf] = true;
                    }
                }
            }
            // Mark any that aren't used as needing zapping
            for (int i = 21; i < isUsed.Length; i++)
            {
                if (!isUsed[i])
                {
                    // Un-used style, can be removed
                    zapRecords[i] = true;
                    newPos[i] = 0;
                }
            }
            // 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
            // removing by index, because removing by object may delete
            // styles we did not intend to (the ones that _were_ duplicated and not the duplicates)
            int max = newPos.Length;
            int removed = 0; // to adjust index after deletion
            for (int i = 21; i < max; i++)
            {
                if (zapRecords[i + removed])
                {
                    workbook.Workbook.RemoveExFormatRecord(i);
                    i--;
                    max--;
                    removed++;
                }
            }

            // 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())
                foreach(IRow row in s)
                {
                    //HSSFRow row = (HSSFRow)rIt.Current;
                    //IEnumerator cIt = row.GetEnumerator();
                    //while (cIt.MoveNext())
                    foreach (ICell cell in row)
                    {
                        //ICell cell = (HSSFCell)cIt.Current;
                        short oldXf = ((HSSFCell)cell).CellValueRecord.XFIndex;
                        NPOI.SS.UserModel.ICellStyle newStyle = workbook.GetCellStyleAt(
                                newPos[oldXf]
                        );
                        cell.CellStyle = (newStyle);
                    }
                }
            }
        }

        /// <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 = workbook.GetSheetAt(sheetNum);
                IEnumerator rIt = s.GetRowEnumerator();
                while (rIt.MoveNext())
                {
                    HSSFRow row = (HSSFRow)rIt.Current;
                    IEnumerator cIt = row.GetCellEnumerator();
                    while (cIt.MoveNext())
                    {
                        HSSFCell cell = (HSSFCell)cIt.Current;
                        short oldXf = cell.CellValueRecord.XFIndex;
                        HSSFCellStyle newStyle = workbook.GetCellStyleAt(
//.........这里部分代码省略.........

        public void TestSetRemoveStyle()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
            HSSFRow row = sheet.CreateRow(0) as HSSFRow;
            HSSFCell cell = row.CreateCell(0) as HSSFCell;

            HSSFCellStyle defaultStyle = wb.GetCellStyleAt((short)15) as HSSFCellStyle;

            // Starts out with the default style
            Assert.AreEqual(defaultStyle, cell.CellStyle);

            // Create some styles, no change
            HSSFCellStyle style1 = wb.CreateCellStyle() as HSSFCellStyle;
            HSSFCellStyle style2 = wb.CreateCellStyle() as HSSFCellStyle;
            style1.DataFormat = (/*setter*/(short)2);
            style2.DataFormat = (/*setter*/(short)3);

            Assert.AreEqual(defaultStyle, cell.CellStyle);

            // Apply one, Changes
            cell.CellStyle = (/*setter*/style1);
            Assert.AreEqual(style1, cell.CellStyle);

            // Apply the other, Changes
            cell.CellStyle = (/*setter*/style2);
            Assert.AreEqual(style2, cell.CellStyle);

            // Remove, goes back to default
            cell.CellStyle = (/*setter*/null);
            Assert.AreEqual(defaultStyle, cell.CellStyle);

            // Add back, returns
            cell.CellStyle = (/*setter*/style2);
            Assert.AreEqual(style2, cell.CellStyle);
        }