Esempio n. 1
0
        /// <summary>
        /// Shift the Hyperlink anchors(not the hyperlink text, even if the hyperlink is of type LINK_DOCUMENT and refers to a cell that was shifted). Hyperlinks  do not track the content they point to.
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="shifter"></param>
        public static void UpdateHyperlinks(ISheet sheet, FormulaShifter shifter)
        {
            XSSFSheet         xsheet        = (XSSFSheet)sheet;
            int               sheetIndex    = xsheet.GetWorkbook().GetSheetIndex(sheet);
            List <IHyperlink> hyperlinkList = sheet.GetHyperlinkList();

            foreach (IHyperlink hyperlink1 in hyperlinkList)
            {
                XSSFHyperlink    hyperlink    = hyperlink1 as XSSFHyperlink;
                String           cellRef      = hyperlink.CellRef;
                CellRangeAddress cra          = CellRangeAddress.ValueOf(cellRef);
                CellRangeAddress shiftedRange = BaseRowColShifter.ShiftRange(shifter, cra, sheetIndex);
                if (shiftedRange != null && shiftedRange != cra)
                {
                    // shiftedRange should not be null. If shiftedRange is null, that means
                    // that a hyperlink wasn't deleted at the beginning of shiftRows when
                    // identifying rows that should be removed because they will be overwritten
                    hyperlink.SetCellReference(shiftedRange.FormatAsString());
                }
            }
        }
Esempio n. 2
0
        public static void UpdateConditionalFormatting(ISheet sheet, FormulaShifter Shifter)
        {
            XSSFSheet    xsheet     = (XSSFSheet)sheet;
            XSSFWorkbook wb         = xsheet.Workbook as XSSFWorkbook;
            int          sheetIndex = wb.GetSheetIndex(sheet);


            XSSFEvaluationWorkbook          fpb         = XSSFEvaluationWorkbook.Create(wb);
            CT_Worksheet                    ctWorksheet = xsheet.GetCTWorksheet();
            List <CT_ConditionalFormatting> conditionalFormattingArray = ctWorksheet.conditionalFormatting;

            // iterate backwards due to possible calls to ctWorksheet.removeConditionalFormatting(j)
            for (int j = conditionalFormattingArray.Count - 1; j >= 0; j--)
            {
                CT_ConditionalFormatting cf = conditionalFormattingArray[j];

                List <CellRangeAddress> cellRanges = new List <CellRangeAddress>();
                String[] regions = cf.sqref.ToString().Split(new char[] { ' ' });
                for (int i = 0; i < regions.Length; i++)
                {
                    cellRanges.Add(CellRangeAddress.ValueOf(regions[i]));
                }

                bool Changed = false;
                List <CellRangeAddress> temp = new List <CellRangeAddress>();
                for (int i = 0; i < cellRanges.Count; i++)
                {
                    CellRangeAddress craOld = cellRanges[i];
                    CellRangeAddress craNew = BaseRowColShifter.ShiftRange(Shifter, craOld, sheetIndex);
                    if (craNew == null)
                    {
                        Changed = true;
                        continue;
                    }
                    temp.Add(craNew);
                    if (craNew != craOld)
                    {
                        Changed = true;
                    }
                }

                if (Changed)
                {
                    int nRanges = temp.Count;
                    if (nRanges == 0)
                    {
                        conditionalFormattingArray.RemoveAt(j);
                        continue;
                    }
                    string refs = string.Empty;
                    foreach (CellRangeAddress a in temp)
                    {
                        if (refs.Length == 0)
                        {
                            refs = a.FormatAsString();
                        }
                        else
                        {
                            refs += " " + a.FormatAsString();
                        }
                    }
                    cf.sqref = refs;
                }

                foreach (CT_CfRule cfRule in cf.cfRule)
                {
                    List <String> formulas = cfRule.formula;
                    for (int i = 0; i < formulas.Count; i++)
                    {
                        String formula = formulas[i];
                        Ptg[]  ptgs    = FormulaParser.Parse(formula, fpb, FormulaType.Cell, sheetIndex, -1);
                        if (Shifter.AdjustFormula(ptgs, sheetIndex))
                        {
                            String ShiftedFmla = FormulaRenderer.ToFormulaString(fpb, ptgs);
                            formulas[i] = ShiftedFmla;
                        }
                    }
                }
            }
        }