Web forum is in read-only mode. Login as active registered customer for write access
  Forum Search   New Posts New Posts

Conditional formats in xlsx files

 Post Reply Post Reply Page  12>
Author
pdv-software GmbH Firma View Drop Down
Member
Member
Avatar

Joined: 02 Apr 2014
Posts: 46
Post Options Post Options   Quote pdv-software GmbH Firma Quote  Post ReplyReply Direct Link To This Post Topic: Conditional formats in xlsx files
    Posted: 13 Nov 2014 at 9:39am
Hi,

we would like to use Excel based conditional formats in xlsx files while generating a report via FlexcelReport. But unfortunately the conditional formats are missing after report creation. We are using version 6.2.1. I have read the release notes from the newer releases, but I could not find any entries that address this issue. Is the conditional format support on your roadmap and/or can you tell me in which version it will be released?

Thanks in advance
Back to Top
Adrian Gallero View Drop Down
TMS Support
TMS Support
Avatar

Joined: 18 May 2010
Posts: 1237
Post Options Post Options   Quote Adrian Gallero Quote  Post ReplyReply Direct Link To This Post Posted: 13 Nov 2014 at 10:28am
Hi,
Support for conditional format in xlsx is scheduled for next release: 6.7. We don't have a timeframe yet on when it will be ready, but it should be in the coming months, I hope by the end of this one. Not sure if we are going to make it for the end of november, but we are trying.

Xlsx cond fmt is our top priority right now, and we are working as fast as we can to have it ready. But it is a complex task, which includes formatting the cells when exporting to pdf/html too, and lots of new stuff introduced in Excel 2007. We've already finished a good part of the support, including drawing all the icon sets (and differently from Excel, we draw them vectorial, not as bitmap, so they look well at any resolution and zoom). 

At the moment, support is only for xls/2003 style conditional formats, and we include support also when exporting to pdf/html/etc. Once 6.7 is out, there should be full support for xlsx too.
Back to Top
pdv-software GmbH Firma View Drop Down
Member
Member
Avatar

Joined: 02 Apr 2014
Posts: 46
Post Options Post Options   Quote pdv-software GmbH Firma Quote  Post ReplyReply Direct Link To This Post Posted: 17 Nov 2014 at 2:57am
OK, thank you for the answer.
Back to Top
pdv-software GmbH Firma View Drop Down
Member
Member
Avatar

Joined: 02 Apr 2014
Posts: 46
Post Options Post Options   Quote pdv-software GmbH Firma Quote  Post ReplyReply Direct Link To This Post Posted: 04 Mar 2015 at 11:03am
Hello,

do you already have a realease date for version 6.7?
Back to Top
Adrian Gallero View Drop Down
TMS Support
TMS Support
Avatar

Joined: 18 May 2010
Posts: 1237
Post Options Post Options   Quote Adrian Gallero Quote  Post ReplyReply Direct Link To This Post Posted: 04 Mar 2015 at 11:54am
We are having some performance problems in the conditional format rendering, we still need to optimize the code more. This is why it is still delayed, we want to release it when it is not just only working, but working correctly and fast.

 We released 6.6.19 yesterday because other improvements have been piling up in the last months, and we don't want the people who care about those to have to wait until we finish the cf support to get them. As soon as cfs are working correctly we'll release 6.7.
Back to Top
jason paul View Drop Down
New Member
New Member
Avatar

Joined: 03 Jun 2015
Posts: 7
Post Options Post Options   Quote jason paul Quote  Post ReplyReply Direct Link To This Post Posted: 03 Jun 2015 at 9:26pm
I am also interested in seeing version 6.7 for Conditional Format Support.
Back to Top
pdv-software GmbH Firma View Drop Down
Member
Member
Avatar

Joined: 02 Apr 2014
Posts: 46
Post Options Post Options   Quote pdv-software GmbH Firma Quote  Post ReplyReply Direct Link To This Post Posted: 15 Sep 2015 at 2:05pm
I saw that you just recently released version 6.7 . But I can find no entry in the release notes concerning conditional formats. Are they now preserved in version 6.7?
Back to Top
Adrian Gallero View Drop Down
TMS Support
TMS Support
Avatar

Joined: 18 May 2010
Posts: 1237
Post Options Post Options   Quote Adrian Gallero Quote  Post ReplyReply Direct Link To This Post Posted: 15 Sep 2015 at 8:41pm
I am really sorry about this, but it didn't made it for this release, and had to be pushed back for 6.8. We had to release a new FlexCel version now, to support both Delphi 10 in FlexCel for VCL and VS2015 in FlexCel for .NET. So we just shipped what was ready and tested at this moment for users to be able to install in the new IDEs. 

Conditional format is working internally but we had to pull the code in order to release, because there were many bugs still to fix on it. We'll keep working on it (this and 2007 tables support are really the only 2 important things missing in our xlsx support) and we will release it as soon as it is ready. The feature will be the first in the what's new, so it will be easy to spot.

I really wish I had better news, but it seems like there is always something getting in the way to finish them. At this time, I think nobody wants to see the full cf support for xlsx more than me.
Back to Top
pdv-software GmbH Firma View Drop Down
Member
Member
Avatar

Joined: 02 Apr 2014
Posts: 46
Post Options Post Options   Quote pdv-software GmbH Firma Quote  Post ReplyReply Direct Link To This Post Posted: 01 Oct 2015 at 9:11am
Back to Top
pdv-software GmbH Firma View Drop Down
Member
Member
Avatar

Joined: 02 Apr 2014
Posts: 46
Post Options Post Options   Quote pdv-software GmbH Firma Quote  Post ReplyReply Direct Link To This Post Posted: 01 Oct 2015 at 9:14am
OK, thank you for your answer. Do you have any estimated release date for the conditional formats? I am asking because we have several requests from our customers and it would be nice if we could get at least an estimated date.
Back to Top
Adrian Gallero View Drop Down
TMS Support
TMS Support
Avatar

Joined: 18 May 2010
Posts: 1237
Post Options Post Options   Quote Adrian Gallero Quote  Post ReplyReply Direct Link To This Post Posted: 01 Oct 2015 at 9:22am
I really think it should be ready by the end of october. The thing is, we are already very delayed (as you know) so I don't really feel comfortable giving you an estimate that I am not sure is right. But most of the work has been done, we just need to connect the parts, do a lot of testing and release. 
Back to Top
pdv-software GmbH Firma View Drop Down
Member
Member
Avatar

Joined: 02 Apr 2014
Posts: 46
Post Options Post Options   Quote pdv-software GmbH Firma Quote  Post ReplyReply Direct Link To This Post Posted: 07 Oct 2015 at 8:08am
Ok, thank you for your information.
Back to Top
pdv-software GmbH Firma View Drop Down
Member
Member
Avatar

Joined: 02 Apr 2014
Posts: 46
Post Options Post Options   Quote pdv-software GmbH Firma Quote  Post ReplyReply Direct Link To This Post Posted: 14 Dec 2015 at 4:51pm
Hello Adrian,

it seems that the conditional format support is still missing. I don't want to annoy you with this topic but we have a customer request concerning conditional formatting and I just would like to know if there will be conditional formatting available by the end of january?
Back to Top
Adrian Gallero View Drop Down
TMS Support
TMS Support
Avatar

Joined: 18 May 2010
Posts: 1237
Post Options Post Options   Quote Adrian Gallero Quote  Post ReplyReply Direct Link To This Post Posted: 14 Dec 2015 at 5:20pm
I really hope so. To be completely honest, I was hoping to have it already, since december is normally a quiet month. But this year support seems to not only have not decreased but increased. I hope the next part of december will be more calm and allow me to write the small pieces still missing.
Back to Top
jason paul View Drop Down
New Member
New Member
Avatar

Joined: 03 Jun 2015
Posts: 7
Post Options Post Options   Quote jason paul Quote  Post ReplyReply Direct Link To This Post Posted: 13 Jan 2016 at 8:49pm
Hello Adrian,

Wondering if you were productive on this issue over December and early January?

Thanks!

Jason
Back to Top
Adrian Gallero View Drop Down
TMS Support
TMS Support
Avatar

Joined: 18 May 2010
Posts: 1237
Post Options Post Options   Quote Adrian Gallero Quote  Post ReplyReply Direct Link To This Post Posted: 14 Jan 2016 at 1:57pm
For what is worth, December didn't slow down. I was answering support emails in dec 24 and dec 31. We released 6.7.3 in january 2016 with all the things we had to add due to all the requests. The only positive thing is that from about 3 days ago support did slow down and we progressed a little. It seems that today I am back answering posts/emails, hoping it is only today and after finishing with this batch of replies I can go back to coding.
Back to Top
Putzich Andreas View Drop Down
New Member
New Member
Avatar

Joined: 01 Jun 2010
Posts: 6
Post Options Post Options   Quote Putzich Andreas Quote  Post ReplyReply Direct Link To This Post Posted: 05 Feb 2016 at 7:33am
Hi Adrian,

nothing has been mentioned about conditional formats in xlsx files for version 6.7.8.0. Any update on this?

Andreas
Back to Top
Adrian Gallero View Drop Down
TMS Support
TMS Support
Avatar

Joined: 18 May 2010
Posts: 1237
Post Options Post Options   Quote Adrian Gallero Quote  Post ReplyReply Direct Link To This Post Posted: 05 Feb 2016 at 9:19am
Hi,

The version which will include conditional formats will be 6.8. All the 6.7.x versions are bugfixes releases with all the accumulated stuff that has been going in parallel with the cond fmt development the latest months. There is no sense in not keeping those bugfixes  unreleased until 6.8 is ready.
Back to Top
Putzich Andreas View Drop Down
New Member
New Member
Avatar

Joined: 01 Jun 2010
Posts: 6
Post Options Post Options   Quote Putzich Andreas Quote  Post ReplyReply Direct Link To This Post Posted: 14 Feb 2016 at 1:00pm
Hi Adrian,

ok, understood, that the conditional formats will be available with version 6.8 was new to me.

I'll wait for version 6.8 then.

Andreas
Back to Top
Macchi Paolo View Drop Down
New Member
New Member
Avatar

Joined: 28 Apr 2015
Posts: 1
Post Options Post Options   Quote Macchi Paolo Quote  Post ReplyReply Direct Link To This Post Posted: 11 May 2016 at 3:16pm
Hi Adrian, I downloaded v6.8, but conditional formatting doesn,t work in xlsx files if i used a template file.

Paolo

Back to Top
Adrian Gallero View Drop Down
TMS Support
TMS Support
Avatar

Joined: 18 May 2010
Posts: 1237
Post Options Post Options   Quote Adrian Gallero Quote  Post ReplyReply Direct Link To This Post Posted: 12 May 2016 at 2:53pm
Hi,
Sorry about that.

Xlsx Conditional format is in final testing right now, it should be out in a couple of weeks now. What just happened is that we had to release a 6.8 version to support Delphi "berlin" in the Delphi Side, and UWP10 in the .net side. We couldn't wait until cf support was stable to release it, as much as I would have liked to, as we were having many requests, both for Berlin and for UWP10. So we released 6.8 with that an a lot of other stuff, and rescheduled for 6.9.

Again, I am really sorry to say this, and I hope we can finish this soon. I've been through a perfect storm of lots of unrelated feature/bug fixes requests, personal issues and issues finding a way to have conditional formats perform well enough in xls and xlsx. (because in our first implementation, we added xlsx support but made the xls support much slower, and we couldn't do that so we had to go back to the start). Well, I am going back to working on the conditional formats now.
Back to Top
Adrian Gallero View Drop Down
TMS Support
TMS Support
Avatar

Joined: 18 May 2010
Posts: 1237
Post Options Post Options   Quote Adrian Gallero Quote  Post ReplyReply Direct Link To This Post Posted: 06 Aug 2016 at 5:17am
Hi,
We've just released 6.9 which fully supports conditional formats in xlsx files. You can use APIMate to know how to add formats with the API, and they will be preserved and exported to pdf.
Back to Top
pdv-software GmbH Firma View Drop Down
Member
Member
Avatar

Joined: 02 Apr 2014
Posts: 46
Post Options Post Options   Quote pdv-software GmbH Firma Quote  Post ReplyReply Direct Link To This Post Posted: 09 Aug 2016 at 3:44pm
Thank you for your work. I tried it and it works!
But now I have another question concerning conditional formats. Is it possible to update the cell reference while the conditional format is being copied? I use 3 columns to show some data (columns A,B,C). Now i want to use a conditional format for column A which depends on the according data in column C. So I created a conditional format at column A that looks like "value <> C1". But unfortunately the reference to the cells in column C is not updated. So I have 100 data rows  with 100 conditional formats which are all referencing C1. But what I would like to have are data rows with conditional formats that referencing their according cell in column C (so C1 to C99).

Best regards
Back to Top
Adrian Gallero View Drop Down
TMS Support
TMS Support
Avatar

Joined: 18 May 2010
Posts: 1237
Post Options Post Options   Quote Adrian Gallero Quote  Post ReplyReply Direct Link To This Post Posted: 09 Aug 2016 at 4:11pm
Hi,
All references should be updated, same as in Excel.
Are you sure you are setting "value <> C1" and not "value <> $C$1"  ?  If you are setting the second, indeed the reference won't move when you copy it, because that is the meaning of "$".  But if you don't use "$", it should move.

Another thing, just in case, is that you should enter the formula without quotes: "values <> c1" is a string and it won't move.

Here is a simple example code which sets a value and copies it down. As you can see in the generated file, the references move too:


program Project15;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils,
  VCL.FlexCel.Core,
  FlexCel.XlsAdapter;

var
  xls: TXlsFile;
  Ranges: TArray<TXlsCellRange>;
  Rules: TArray<IConditionalFormatRule>;
  ExpressionRule: IConditionalExpressionRule;
  CFDef: TConditionalFormatDefStandard;
begin
  xls := TXlsFile.Create(true);
  try
    xls.NewFile(1, TExcelFileFormat.v2016);
    //Set the cell values
    xls.SetCellValue(1, 1, TFormula.Create('=RANDBETWEEN(1, 2)'));
    xls.SetCellValue(1, 3, 1);

    //Conditional Formats
    Ranges := TArray<TXlsCellRange>.Create(
      TXlsCellRange.Create(1, 1, 1, 1)
    );

    Rules := nil;  //SetLength will resize the array in place. We set it to nil first to create a new array.
    SetLength(Rules, 1);
    ExpressionRule := TConditionalExpressionRule_Create(1, false, '=A1=C1');
    CFDef := ExpressionRule.FormatDef;
    CFDef.ApplyFill.BgColor := true;
    CFDef.Fill.BgColor := TExcelColor.FromTheme(TThemeColor.Accent6, 0.399945066682943);
    Rules[0] := ExpressionRule;

    xls.AddConditionalFormat(TConditionalFormat.Create(Ranges, Rules, false));

    xls.InsertAndCopyRange(TXlsCellRange.Create(1, 1, 1, 1), 2, 1, 10, TFlxInsertMode.ShiftRowDown);
    xls.Save('..\..\result.xlsx');
  finally
    xls.Free;
  end;

end.


Back to Top
pdv-software GmbH Firma View Drop Down
Member
Member
Avatar

Joined: 02 Apr 2014
Posts: 46
Post Options Post Options   Quote pdv-software GmbH Firma Quote  Post ReplyReply Direct Link To This Post Posted: 09 Aug 2016 at 4:37pm
Hi,
my post wasn't really clear. I created a conditional format via Excel, I am not creating it programmatically.
In the following picture you see my settings for the conditional format for cell A1 (it is in German but I think you can still read the settings). As you can see, i wrote C1 ("Zellwert" = "cell value" and "ungleich"="not equal" ) :





And after report creation every data cell (A1 to A99) referencing C1. I am using Excel 2016.
Back to Top
 Post Reply Post Reply Page  12>

Forum Jump Forum Permissions View Drop Down