NPOI 1.2.4 nightmare

May 18, 2011 at 9:17 AM

Hi Everyone

 

I have upgraded to NPOI 1.2.4 today to sort out a particular bug.  The bug has now disappeared but unfortuatenly everything has changed!   I have had to recode a lot of things as the objects have been moved around into different namespaces.  Unfortunately my program is now rendered totally useless in terms of user requirements.

 

Please can you tell me where I can find the following:

 

1.  Setting borders for regions:

   NPOI.SS.Util.CellRangeAddress region1 = new NPOI.SS.Util.CellRangeAddress(9, 9, 10, (int)WorkSheetColumns.I);
            NPOI.SS.Util.CellRangeAddress region2 = new NPOI.SS.Util.CellRangeAddress(9, 9, 11, (int)WorkSheetColumns.K);
            NPOI.SS.Util.CellRangeAddress region3 = new NPOI.SS.Util.CellRangeAddress(9, 9, 12, (int)WorkSheetColumns.N);


            for (int y = (int)WorkSheetColumns.H; y <= (int)WorkSheetColumns.N; y++)
            {
                WSToFormat.GetRow(6).GetCell(y).CellStyle = hCenterVTopNine;
            }

            WSToFormat.AddMergedRegion(region1);
            WSToFormat.AddMergedRegion(region2);
            WSToFormat.AddMergedRegion(region3);

            short color = HSSFColor.BLACK.index;
            //int borderType = HSSFCellStyle.BORDER_THIN;
            CellBorderType borderType = CellBorderType.THIN;
            NPOI.HSSF.Util.HSSFRegionUtil.SetRightBorderColor(color, region1, WSToFormat, workBook);

           I get errors with this now.  Is it still valid? Must I change WSToFormat from Sheet to HSSFSheet (old one)?

 

2.  Named ranges problem:

 

   if (workBook.GetNameIndex("Priority") != -1) return;
            Name newNamedRange = workBook.CreateName();
            newNamedRange.NameName = "Priority";
   
            newNamedRange.RefersToFormula = string.Format("{0}!{1}:{2}", "Summary", "$X$2", "$X$4");

       //newNamedRange.Reference = string.Format("{0}!{1}:{2}", "Summary", "$X$2", "$X$4");

         Where is Reference now?

       Is RefersToFormula supposed to replace Reference?

 

3.     I also had SummarySheet.AddValidationData(dataValidation);   Where do I add the validation stuff now?

 

 

 

 

 

May 18, 2011 at 9:30 AM

Unfortuatnely there is more:

 

4. Conditional formatting:

   How does it work now?

  What I had:

   HSSFSheetConditionalFormatting hscf = WSToFormat.SheetConditionalFormatting;
           
            // Define a Conditional Formatting rule, which triggers formatting
            // when cell's value is bigger than 55 and smaller than 500
            // applies patternFormatting defined below.
            HSSFConditionalFormattingRule rule1 = hscf.CreateConditionalFormattingRule(
                NPOI.HSSF.Record.ComparisonOperator.BETWEEN,
                "2", // 1st formula
                "2"     // 2nd formula
            );

            HSSFConditionalFormattingRule rule2 = hscf.CreateConditionalFormattingRule(
              NPOI.HSSF.Record.ComparisonOperator.BETWEEN,
              "1", // 1st formula
              "1"     // 2nd formula
           );

            HSSFConditionalFormattingRule[] rules = { rule1, rule2 };

            // Create pattern with red background
            HSSFPatternFormatting patternFmtYellow = rule1.CreatePatternFormatting();
            patternFmtYellow.FillBackgroundColor = HSSFColor.YELLOW.index;


            HSSFPatternFormatting patternFmtRed = rule2.CreatePatternFormatting();
            patternFmtRed.FillBackgroundColor = HSSFColor.RED.index;

            //// Define a region containing first column

            NPOI.SS.Util.CellRangeAddress[] regions = {
                new NPOI.SS.Util.CellRangeAddress((int) ActionPlanRows.ActionItemStart, 65535,(int) WorkSheetColumns.C,(int)WorkSheetColumns.C)
            };
            // Apply Conditional Formatting rule defined above to the regions 
            hscf.AddConditionalFormatting(regions, rules);

  There is not more SheetConditionalFormatting with 1.2.4.   Where is it?

 

 

Coordinator
May 31, 2011 at 12:04 AM
jpretorius wrote:

Hi Everyone

 

I have upgraded to NPOI 1.2.4 today to sort out a particular bug.  The bug has now disappeared but unfortuatenly everything has changed!   I have had to recode a lot of things as the objects have been moved around into different namespaces.  Unfortunately my program is now rendered totally useless in terms of user requirements.

 

Please can you tell me where I can find the following:

 

1.  Setting borders for regions:

   NPOI.SS.Util.CellRangeAddress region1 = new NPOI.SS.Util.CellRangeAddress(9, 9, 10, (int)WorkSheetColumns.I);
            NPOI.SS.Util.CellRangeAddress region2 = new NPOI.SS.Util.CellRangeAddress(9, 9, 11, (int)WorkSheetColumns.K);
            NPOI.SS.Util.CellRangeAddress region3 = new NPOI.SS.Util.CellRangeAddress(9, 9, 12, (int)WorkSheetColumns.N);


            for (int y = (int)WorkSheetColumns.H; y <= (int)WorkSheetColumns.N; y++)
            {
                WSToFormat.GetRow(6).GetCell(y).CellStyle = hCenterVTopNine;
            }

            WSToFormat.AddMergedRegion(region1);
            WSToFormat.AddMergedRegion(region2);
            WSToFormat.AddMergedRegion(region3);

            short color = HSSFColor.BLACK.index;
            //int borderType = HSSFCellStyle.BORDER_THIN;
            CellBorderType borderType = CellBorderType.THIN;
            NPOI.HSSF.Util.HSSFRegionUtil.SetRightBorderColor(color, region1, (HSSFSheet)WSToFormat, workBook);

           I get errors with this now.  Is it still valid? Must I change WSToFormat from Sheet to HSSFSheet (old one)?

explicit conversion is necessary here. 

2.  Named ranges problem:

 

   if (workBook.GetNameIndex("Priority") != -1) return;
            Name newNamedRange = workBook.CreateName();
            newNamedRange.NameName = "Priority";
   
            newNamedRange.RefersToFormula = string.Format("{0}!{1}:{2}", "Summary", "$X$2", "$X$4");

       //newNamedRange.Reference = string.Format("{0}!{1}:{2}", "Summary", "$X$2", "$X$4");

         Where is Reference now?

       Is RefersToFormula supposed to replace Reference?

Yes

3.     I also had SummarySheet.AddValidationData(dataValidation);   Where do I add the validation stuff now?

AddValidationData is still in HSSFSheet but not in the interface Sheet. What you want to do is explicitly converting SummarySheet to HSSFSheet like this : ((HSSFSheet)SummarySheet).AddValidationData 

I understand it's a bit complex for beginner to understand the relationship between Sheet and HSSFSheet. The definition of Sheet will be changed to some degree. Sheet interface is used to support Office 2007/2010 interface in the future. Again, Sorry for any inconvenience.

 

Coordinator
May 31, 2011 at 12:06 AM
jpretorius wrote:

Unfortuatnely there is more:

 

4. Conditional formatting:

   How does it work now?

  What I had:

   HSSFSheetConditionalFormatting hscf = WSToFormat.SheetConditionalFormatting;
           
            // Define a Conditional Formatting rule, which triggers formatting
            // when cell's value is bigger than 55 and smaller than 500
            // applies patternFormatting defined below.
            HSSFConditionalFormattingRule rule1 = hscf.CreateConditionalFormattingRule(
                NPOI.HSSF.Record.ComparisonOperator.BETWEEN,
                "2", // 1st formula
                "2"     // 2nd formula
            );

            HSSFConditionalFormattingRule rule2 = hscf.CreateConditionalFormattingRule(
              NPOI.HSSF.Record.ComparisonOperator.BETWEEN,
              "1", // 1st formula
              "1"     // 2nd formula
           );

            HSSFConditionalFormattingRule[] rules = { rule1, rule2 };

            // Create pattern with red background
            HSSFPatternFormatting patternFmtYellow = rule1.CreatePatternFormatting();
            patternFmtYellow.FillBackgroundColor = HSSFColor.YELLOW.index;


            HSSFPatternFormatting patternFmtRed = rule2.CreatePatternFormatting();
            patternFmtRed.FillBackgroundColor = HSSFColor.RED.index;

            //// Define a region containing first column

            NPOI.SS.Util.CellRangeAddress[] regions = {
                new NPOI.SS.Util.CellRangeAddress((int) ActionPlanRows.ActionItemStart, 65535,(int) WorkSheetColumns.C,(int)WorkSheetColumns.C)
            };
            // Apply Conditional Formatting rule defined above to the regions 
            hscf.AddConditionalFormatting(regions, rules);

  There is not more SheetConditionalFormatting with 1.2.4.   Where is it?

This is the same issue. Explicit conversion is necessary here. convert WSToFormat to HSSFSheet.