This project has moved and is read-only. For the latest updates, please go here.

Conditional Formatting problem

Dec 10, 2012 at 2:14 PM
Edited Dec 10, 2012 at 2:16 PM

Hi, 

I read data from yaml files and use this data to fill sheets. From each yaml file one sheet is generated.

basically it starts from iterating over files and all files which have the same group are put to the one xlsx file. Inside Generate method I format conditionally some particular columns. If xlsx file has one sheet then I have twoscale formatting from white to red, but when I put more than one sheet to the workbook then only one sheet uses red and white colors, other sheets use red and green. I have no idea why it works like this? Is conditional formatting not read properly? Because between populating next sheets I save file and open it.

 

 

  foreach (string yaml_filepath in GetFilesForProcessing())
            {
                string output_filepath = Path.ChangeExtension(yaml_filepath, ".xlsx");

                SessionDropoutStatsModel sdsm = modelLoader.Load(yaml_filepath);
                if (!groupToFilePath.ContainsKey(sdsm.Group))
                {
                    if (sdsm.Group.CompareTo("") != 0)
                    {
                        groupToFilePath.Add(sdsm.Group, output_filepath);
                        output_filepath = groupToFilePath[sdsm.Group];
                    }
                }
                else
                {
                    output_filepath = groupToFilePath[sdsm.Group];
                }

                
                ConcreteExcelGenerator excelGenerator = new ConcreteExcelGenerator(sdsm, new FileInfo(output_filepath));
                excelGenerator.Generate();
                excelGenerator.SaveAs(new System.IO.FileInfo(output_filepath));
                Console.WriteLine("File : " + yaml_filepath + " used to fill excel file: " + output_filepath + " with data");
            }

 

 

     public void Generate()
        {
            AddWorksheet(m_model.Name);
          //  AddWorksheet(m_model.Name + " graph");
            GenerateFirstSheet(GetWorksheet(m_model.Name))
           // GenerateSecondSheet(GetWorksheet(m_model.Name + " graph"));
        }

        private void GenerateFirstSheet(ExcelWorksheet a_worksheet)
        {            
            a_worksheet.View.ZoomScale = 80;
            create1Row1Sheet(a_worksheet);
            create2Row1Sheet(a_worksheet);
            create3Row1Sheet(a_worksheet);
            create4Row1Sheet(a_worksheet);
            create1Column(a_worksheet);
            createStatistics1Sheet(a_worksheet);
            createConditionalFormatting1Sheet(a_worksheet);
            createMovedToOffPoolLinks1Sheet(a_worksheet);
        }


  // Create two color scale conditional formatting for scores
        private void createConditionalFormatting1Sheet(ExcelWorksheet a_worksheet)
        {
            System.Windows.Media.Color mediaColor = (System.Windows.Media.Color)System.Windows.Media.ColorConverter.ConvertFromString("#C00000");
            System.Drawing.Color systemColor = System.Drawing.Color.FromArgb(mediaColor.A, mediaColor.R, mediaColor.G, mediaColor.B);

            int zoneWidth = m_model.Types.Count;
            int fromColumn = 4;
            int fromRow = 5;
            int toRow = fromRow + m_model.Data.Count -1;
            if (0 == m_model.Data.Count())
                return;

            foreach (Title title in m_model.Titles)
            {
                foreach (Zone zone in m_model.Zones)
                {
                    using (ExcelRange scoreRange = a_worksheet.Cells[fromRow, fromColumn, toRow, fromColumn])
                    {
                        ExcelAddress rangeAddress = new ExcelAddress(scoreRange.Address);
                        var rule = a_worksheet.ConditionalFormatting.AddTwoColorScale(rangeAddress);
                        rule.LowValue.Type = eExcelConditionalFormattingValueObjectType.Min;
                        rule.LowValue.Color = System.Drawing.Color.White;
                        rule.HighValue.Type = eExcelConditionalFormattingValueObjectType.Max;
                        rule.HighValue.Color = systemColor;

                    }
                    fromColumn += zoneWidth;
                }
            }


        }