Saving cell colors with save?

FLX TMS FlexCel Studio for .NET
1


When saving a spreadsheet, I assumed that everything I see on screen on the preview would save to the xlsm file.  

I'm opening a new spreadsheet this way:

 _xls = new XlsFile(4, TExcelFileFormat.v2016, true);

Saving this way:

 _xls.Save(pathAndFilename);

I get all the data but no cell colors.  Is there an extra method/property I need to call first?  The colors show up as expected on the print preview and when printed, print as expected, but the saved file has no color information in it.

Thanks,
Curt


2
Hi,

Indeed when you save everything should be preserved: We aren't aware of any case where it isn't.
Can you send us some example file/code so we can investigate what's going on?  If there is any private data you can send it to adrian@tmssoftware.com
3
I have a complicated setup, actually using a Tuple to pull in disparate items, like cell behavior overrides,  property maps (reflection PropertyInfo references) to the cell contents and PropertyInfo references to color (integer converted to bytes) for cell colors. 

Here's the column headers:

    int colCount = 1;
                    foreach (var key in IndexedIgColumnNames.Keys)
                    {
                        Tuple<string, PropertyInfo, PropertyInfo, CellInfo> t;
                        if (IndexedIgColumnNames.TryGetValue(key, out t))
                        {
                            xls.SetCellValue(celly, cellx, t.Item1);

                           var  fmt = xls.GetCellVisibleFormatDef(celly, cellx);
                            fmt.Borders.Left.Style = TFlxBorderStyle.Thin;
                            fmt.Borders.Left.Color = TExcelColor.Automatic;
                            fmt.Borders.Right.Style = TFlxBorderStyle.Thin;
                            fmt.Borders.Right.Color = TExcelColor.Automatic;
                            fmt.Borders.Top.Style = TFlxBorderStyle.Thin;
                            fmt.Borders.Top.Color = TExcelColor.Automatic;
                            fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin;
                            fmt.Borders.Bottom.Color = TExcelColor.Automatic;
                            fmt.HAlignment = t.Item4.HAlignment;
                            fmt.VAlignment = TVFlxAlignment.center;
                            fmt.Font.Style = TFlxFontStyles.Bold;
                            fmt.WrapText = t.Item4.WordWrap;
                            if (t.Item4.Width > 0)
                                xls.SetColWidth(colCount, t.Item4.Width);
                            xls.SetCellFormat(celly, cellx, xls.AddFormat(fmt));
                            colCount++;
                        }
                        cellx++;

Putting the data in:

  foreach (var key in IndexedIgColumnNames.Keys)
                        {
                            Tuple<string, PropertyInfo, PropertyInfo, CellInfo> t;
                            if (IndexedIgColumnNames.TryGetValue(key, out t))
                            {
                                var propValue = t.Item2;
                                //Setup cell format/color here
                                xls.SetCellValue(celly, cellx, PropertyHelper.GetValueAsString(sched, propValue));
                                var fmt = xls.GetCellVisibleFormatDef(celly, cellx);
                                fmt.Borders.Left.Style = TFlxBorderStyle.Thin;
                                fmt.Borders.Left.Color = TExcelColor.Automatic;
                                fmt.Borders.Right.Style = TFlxBorderStyle.Thin;
                                fmt.Borders.Right.Color = TExcelColor.Automatic;
                                fmt.Borders.Top.Style = TFlxBorderStyle.Thin;
                                fmt.Borders.Top.Color = TExcelColor.Automatic;
                                fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin;
                                fmt.Borders.Bottom.Color = TExcelColor.Automatic;
                                fmt.FillPattern = new TFlxFillPattern();
                                byte[] bytes = BitConverter.GetBytes(PropertyHelper.GetIntValue(sched, t.Item3));
                                fmt.FillPattern.BgColor = TUIColor.FromArgb(bytes[1], bytes[2], bytes[3]); ;
                                fmt.HAlignment = t.Item4.HAlignment;
                                fmt.VAlignment = TVFlxAlignment.center;
                                fmt.Font.Style = TFlxFontStyles.Bold;
                                fmt.WrapText = t.Item4.WordWrap;
                                xls.SetCellFormat(celly, cellx, xls.AddFormat(fmt));
                            }

                            cellx++;
                        }

                        cellx = x;
                        celly++;


The "PropertyHelper.GetValueAsString()" is a static class that reads the property value from the object in question, using the property map I setup.   The properties used is dynamic and that's the reason why I'm using it.

I'm not able to share the data.  It's pulled off of a web service, and prior to that it's pulled out of a database and a lot happens between the database and what's displayed.

As far as setting the cell value and the cell format in the code above, is there anything there that jumps out at you?  Am I assigning things in the wrong order?

It prints correctly, that's what's odd about it.

Thanks,
Curt
4
Hi,
I think the problem is that you are not initializing the fill pattern to "solid" and also when the pattern is solid, you need to set the FgColor, not the BgColor.

Despite their names, FgColor and BgColor both apply to the cell "background", the "foreground" color would be "Font.Color". Background and foreground in the fill style make sense only when you have a pattern like "diagonal lines". So the bacground of the cell is say red (bgcolor), and the lines are blue (fgColor). But when the pattern is solid, Excel uses the FgColor to fill the cell. It is as if the pattern was a block in fgColor, with background BgColor. Since the block covers all the cell, then BgColor is not visible, it is always covered by the foreground pattern.

Going to the code: Replace

fmt.FillPattern = new TFlxFillPattern();
byte[] bytes = BitConverter.GetBytes(PropertyHelper.GetIntValue(sched, t.Item3));
fmt.FillPattern.BgColor = TUIColor.FromArgb(bytes[1], bytes[2], bytes[3]); ;

with:

fmt.FillPattern.Pattern = TFlxPatternStyle.Solid; //There is no need to create a new TFlxFillPattern.
byte[] bytes = BitConverter.GetBytes(PropertyHelper.GetIntValue(sched, t.Item3));
fmt.FillPattern.FgColor = TUIColor.FromArgb(bytes[1], bytes[2], bytes[3]);

5
Using the your example, i.e. assigning the fill pattern, solved the problem.

Thank you for the solution and your extremely quick response!

Regards,
Curt