Validation of Excel file

FLX TMS FlexCel Studio for .NET
1

Hello,

our company is using FlexCel to give our customers the possibility to create Excel- and PDF based reports. Since our customers can create reports on their own, we need to validate the excel file before the report is created. Our biggest problem is checking the availability of named ranges. We need 1 named range per SQL-Query. The problem is, how to find out if the query is used. There has to be a cell content like "<#query.column>". But to find out if a query is used, thus if to check if the respective named range is given, we need to find out if there is a cell with a content like "<#query.column>". My first idea was to check every cell in the excel document, but I think this isn't a reasonable option. Can you tell me if it is possible to look for a specific cell content without checking every cell? Or maybe do you have any other ideas to solve our problem?

Thanks in advance

2

There is not much I can suggest here. The first thing is, that FlexCel does validation by itself when running the report. Reporting actually has 2 stages: The first is a "compilation" phase where the tags are read and parsed into executable objects, and the second stage is where the "compiled" report is run. Any typo in ids or tags outside the range will be caught in the compilation phase.


So a simple thing to do is just to try to run a dummy report, without actual data,  between "try/catch" statements, and catch the exception and how it to the user. While this won't give you as much flexibility, it will understand aliases, report variables, etc, which would be quite a lot of work to do manually. For example, and user can write "<#if(<#somecondition>;<#query.column>;>" in a cell, and it will be used as long as somecondition is true.

Now, for this particular validation (looking if a named range has <#namdedrange> cells inside, FlexCel won't validate it, because it isn't really an error, it would be more like a warning. You can have a myrange without any<#myrange.field> inside, what you can't have is the opposite (a <#myrange.field>" outside a myrange range)

For this particular case, I think looking at all the cells will be the easiest way. You can actually look only at the cells inside myrange, you don't need to look at all the cells in the sheet. And by definition, templates are small, so I don't expect performance issues here. The generated files might be big, but the templates themselves are normally with just some hundred of cells at the max. We also have a "Search" functionality built in (you can look at xls.Find(...) method), but it will probably not offer much over just looping into the cells.

For the future, we are looking at adding some more validation options to the report, especailly for the new report designer we are doing, maybe this can be a part of some struct, like:
EnableExtraValidations
{
    bool ThereMustBeOneFieldInsideRange;
}

etc. So you would define which of this optional stuff would be tagged as an error when runnnig reports.
3



Yes it is true, but in the worst case the customer has a cell content like "<#query.data>" and has forgotten to provide the named range  "query". In order to prevent an error message from FlexCel in this case, I have to search for such content and check, if it is in the proper named range. But thank you for your advice. It seems that I have to look in every cell in this worst case.