Enable validation on Entire columns (Integers,Real and Booleans)

[Jatin Gundabathula]

Hi Team,

I need to enable formats and validations on Entire Columns or based on the Range provided below are my Columns

columnInt, columnReal, columnBoolean

For columnInt --> it should allow only integer values like -3,-2,-1,0,1,2,3 it should validate if user enters other than integers like string or fractional values.
For columnReal -> it should allow all the real number set and should validate if user string or any other values including special characters
For columnBoolean -> it should allow on true or false (case senstive) as dropdonw list. user should not enter any other text only he has to select from the options.

i tried using like below but seems it is not behaving like what i need. My code is in ASP.Net Core 2.2 C#

                              string range = worksheet.Rows.Length > 1 ? column.AddressLocal : column.AddressLocal + ":" + column.AddressLocal[0] + "5000";

                              case CustomFieldDataType.Integer:

                                     IDataValidation datavalidation =  worksheet.Range[range].DataValidation;

                                    datavalidation.AllowType = ExcelDataType.Integer;

                              case CustomFieldDataType.Boolean:
                                        // 

[Shamini Kiruba Sobers]

Hi Jatin, 

We have prepared a working sample with data validations for Integer, Real and  Boolean values, as per your requirement. Please find the sample from the following link. 

https://www.syncfusion.com/downloads/support/directtrac/general/ze/F152563442577774 

Also, kindly refer the following link to know more about data validation using XlsIO. 

https://help.syncfusion.com/file-formats/xlsio/working-with-data-validation 

Kindly let us know if it helps. 

Thanks, 

Shamini.

[Jatin Gundabathula]

Thanks Shamini for all the information and Code samples.

I am able to enable the data validation using below code like 

Here range hardcoded based on  each column for a DataTable address ex A1:A5000, B1:B5000

                    datavalidation = worksheet.Range[range].DataValidation;

                    datavalidation.BeginUpdate();

                    datavalidation.IsEmptyCellAllowed = true;

                    datavalidation.IsListInFormula = true;

                    datavalidation.ListOfValues = list of values

                    datavalidation.ShowPromptBox = true;

                    datavalidation.EndUpdate();

So i see few issues:

1. If we use EntireColumn in place of custom ranges application getting hanged up and not able to export excel. So instead of EntireColumn I am limiting the datavalidation to be applied for certain range like A1:A5000. if i limit. at least i am able to generate excel but still it is taking too much time around 3 mins.

2. Excel is occupying around 1 MB of file size even there are only columns headers for each worksheet. 

Current implemenation of excel export in my application is. I have couples of worksheet in a single workbook to be clear as code wise I have around 20 DataTable Objects and Each DataTable contains around 40 columns and each column has its own DataValidation based on the type (Boolean,Integers,Decimals and List Types). If cell is of ListType it contains around 20 items for selection. So we are iterating through all the datatables and preparing wokrsheet and exporting Workbook. This is how the final excel will look like.

So i was thinking is above issues are any where related to current implementation. As we have many sheets and more number of columns. Can xlsio library support this much data? 

Please do let me know if you have any inputs on this that would be really appreciated. 

Thanks in advance

jatin.g

[Shamini Kiruba Sobers]

Hi Jatin, 

Thanks for the update. Please find the response for your queries from the below table. 

Query	Response
1. If we use EntireColumn in place of custom ranges application getting hanged up and not able to export excel. So instead of EntireColumn I am limiting the datavalidation to be applied for certain range like A1:A5000. if i limit. at least i am able to generate excel	Yes, that’s fine of limiting the data validation to improve the performance.  You can use either as,  datavalidation = worksheet.Range[range].DataValidation;  Or as,  datavalidation = worksheet[range].DataValidation;
but still it is taking too much time around 3 mins.	We understood your implementation and reproduced your scenario. Currently, we are checking on this issue with high priority. We will check the feasibility to reduce the total time for generating the Excel and let you know further details about whether XlsIO library support this much of data with less time, on 26th March 2020.
2. Excel is occupying around 1 MB of file size even there are only columns headers for each worksheet.   So i was thinking is above issues are any where related to current implementation. As we have many sheets and more number of columns. Can xlsio library support this much data?

Thanks for your patience until then. 

Regards, 

Shamini. 

[Jatin Gundabathula]

Thanks a lot for your responses.

Mean while you are looking into the issues. I have small requirement can you please.

How can i added Data Validation on column to allow only list of comma separated integers ex:(1,21,34) other than this pattern user should not allowed to enter any string or other garbage values.

Can you please help how can we add this custom validations. please provide working example if any.

Once again thanks a lot for all the support really appreciated.

[Shamini Kiruba Sobers]

Hi Jatin, 

  

Kindly look into the below responses. 

  

Query	Response
I am limiting the datavalidation to be applied for certain range like A1:A5000. if i limit. at least i am able to generate excel but still it is taking too much time around 3 mins.	Currently, we are validating on it with high priority. But due to complexity, we will update further details on 27th March 2020.
Excel is occupying around 1 MB of file size even there are only columns headers for each worksheet.    So i was thinking is above issues are any where related to current implementation. As we have many sheets and more number of columns. Can xlsio library support this much data?	Yes, XlsIO library supports larger data. But can you please confirm us whether your file is having column headers alone? Check the file by pressing [Ctrl + End] and tell us the last cell address. It would be helpful to analyze further if you can share us the 1 MB file you are talking about.
How can i added Data Validation on column to allow only list of comma separated integers ex:(1,21,34) other than this pattern user should not allowed to enter any string or other garbage values.	You can achieve any list validations similar to what we have done for Boolean. We have added data validation code to allow integers only from the list (1, 21, 34), to the previously given sample and it can be downloaded from the following link.  https://www.syncfusion.com/downloads/support/directtrac/general/ze/F152563835511489     But the comma separated list cannot be added in data validation. However, we can check if that can be achieved through formula and get back to you on 27th March 2020.     The code snippet included in the sample can be found below.     public IActionResult CreateDocument()  {      //Create an instance of ExcelEngine      using (ExcelEngine excelEngine = new ExcelEngine())      {          IApplication application = excelEngine.Excel;             application.DefaultVersion = ExcelVersion.Excel2016;             //Create a workbook          IWorkbook workbook = application.Workbooks.Create(1);          DataTable dataTable = GetDataTable();          var worksheet = workbook.Worksheets.Create(dataTable.TableName);          worksheet.ImportDataTable(dataTable, true, 1, 1);             IDataValidation intvalidation = worksheet["A1:A5000"].DataValidation;          intvalidation.BeginUpdate();          intvalidation.AllowType = ExcelDataType.Integer;          intvalidation.EndUpdate();             IDataValidation realvalidation = worksheet["B1:B5000"].DataValidation;          realvalidation.BeginUpdate();          realvalidation.AllowType = ExcelDataType.Formula;          realvalidation.FirstFormula = "=ISNUMBER(B1)";          realvalidation.EndUpdate();             IDataValidation boolvalidation = worksheet["C1:C5000"].DataValidation;          boolvalidation.BeginUpdate();          boolvalidation.AllowType = ExcelDataType.User;          boolvalidation.ListOfValues = new string[] { "True", "False" };          boolvalidation.EndUpdate();             IDataValidation listvalidation = worksheet["D1:D5000"].DataValidation;          listvalidation.BeginUpdate();          listvalidation.AllowType = ExcelDataType.User;          listvalidation.ListOfValues = new string[] { "1", "21", "34" };          listvalidation.EndUpdate();             //Saving the Excel to the MemoryStream           MemoryStream stream = new MemoryStream();          workbook.SaveAs(stream);             //Set the position as '0'.          stream.Position = 0;             //Download the Excel file in the browser          FileStreamResult fileStreamResult = new FileStreamResult(stream, "application/excel");             fileStreamResult.FileDownloadName = "Output.xlsx";             return fileStreamResult;      }  }     private static DataTable GetDataTable()  {      //Create a DataTable with four columns      DataTable table = new DataTable();      table.TableName = "DataTypes";         table.Columns.Add("columnInt", typeof(int));      table.Columns.Add("columnReal", typeof(decimal));      table.Columns.Add("columnBoolean", typeof(bool));      table.Columns.Add("columnIntList", typeof(int));         return table;  }        Kindly let us know if this helps or if your requirement is different from this. Have a look into the below link too. https://help.syncfusion.com/file-formats/xlsio/working-with-data-validation?cs-save-lang=1&cs-lang=asp.net%20core#custom-validation

  

  

  

Thanks, 

Shamini. 

[Jatin Gundabathula]

Thanks for the update unfortunately i cant share the excel file. Yes the file contains only headers. and for list of integer what i was looking for is different we dont have predefined list of values. cell should be text format not as dropdown or option type but we need validation like user can enter single integer value or list of interger values with comma separated.

Valid

Ex1: 1 or 5 or 8 .....

Ex2: 1,4,5 or 112,344,513 or .....

Invalid:

Ex: asd,asda,asd... or asdas or @@asda$1 

Please let me know if you need anything else

[Shamini Kiruba Sobers]

Hi Jatin, 

Thanks for the updates and excuses for the misinterpretation. Kindly make use of the below code snippet to allow only list of comma separated integers and let us know if it fulfils your requirement. 

Code snippet: 

IDataValidation listvalidation = worksheet["D1:D5000"].DataValidation; 

listvalidation.BeginUpdate(); 

listvalidation.AllowType = ExcelDataType.Formula; 

listvalidation.FirstFormula = "=ISNUMBER(--SUBSTITUTE(D1,\",\",\"\"))"; 

listvalidation.EndUpdate(); 

Sample download link: 

https://www.syncfusion.com/downloads/support/directtrac/general/ze/F152563-1593826284 

Besides, we are still dealing with the performance issue reported for larger data (containing data validation implementation) and let you know the details soon when it is validated. 

Regards, 

Shamini 

[Shamini Kiruba Sobers]

Hi Jatin, 

  

We have checked the performance issue in data validation with the below sample, which contains only 3 data validations that were initially given and are repeated to reach the count specified by you. That is, you have been using 20 Data Tables with 5000 rows and around 40 columns each. The below sample is for 1 Data Table with 42 columns (but only 3 simple data validations are repeated). 

https://www.syncfusion.com/downloads/support/directtrac/general/ze/GettingStarted_Core654735469 

  

And, we have increased the Data Table with same set of code and noted down the time details, which is given below for your reference. We have also made a fix in list validation and reduced the time taken to some extent, which can also be compared from the following table. 

  

No. of Data Tables	Time taken before fix	Time taken after fix
2	23 seconds	11 seconds
5	52 seconds	30 seconds
10	1 minute 54 seconds	1 minute 1 second

  

But the fix is specific to these three validations. So, if you can share us the complex data validations that are using in your side, we would be able to check those cases to improve the performance, since performance in validation differs from case by case. 

  

Kindly let us know the requested details. We are eagerly waiting to hear from you. 

  

  

Regards, 

Shamini 

[Jatin Gundabathula]

Hi Team,

Thanks for all you support 

I have 2 use cases where export to excel for few cell data not working as expected.

Case 1: Boolean Types:
I have Datatable table where the table is filled with couple of rows and column as metioned in below threads. so this data table contains few columns of type Boolean where it should only accepts (true or false)
the we have few cells which will have data like long intergers seperated by commas something like below
100005636,100005637,100005638,100005639

When i do export the above 2 cases are auto formated

Case for boolean data is automatically converting to Upper case
examples true --> TRUE and false --> FALSE

Expected true --> true or false  --> false

Case for long Intergers data is automatically converting to exponentials
examples 100005636,100005637,100005638,100005639 --> 1.00006E+17

Expected 100005636,100005637,100005638,100005639 --> 100005636,100005637,100005638,100005639
   100005636 --> 100005636
   100005636,100005637 --> 100005636,100005637
  
Below is the code for Each Case

Boolean DataType Validations using below code is applied for all the columns which are of type Boolean:

case CustomFieldDataType.Boolean:
                    this.ListTypeDataValidation(worksheet, range, new[] { "true", "false" });

public IDataValidation ListTypeDataValidation(IWorksheet workSheet, string range, string[] items)
        {
            var datavalidation = workSheet.Range[range].DataValidation;
            datavalidation = workSheet.Range[range].DataValidation;
            datavalidation.BeginUpdate();
            workSheet.Range[range].NumberFormat = "@";
            datavalidation.IsEmptyCellAllowed = true;
            datavalidation.ListOfValues = items;
            datavalidation.EndUpdate();

            return datavalidation;
        }
  
Long Intergers:

Using below validation formating cell to Text type.

 this.AllowAnyDataValidation(worksheet, range);

  public IDataValidation AllowAnyDataValidation(IWorksheet workSheet, string range)
        {
            var datavalidation = workSheet.Range[range].DataValidation;
            datavalidation.BeginUpdate();
            workSheet.Range[range].NumberFormat = "@";
            datavalidation.IsEmptyCellAllowed = true;
            datavalidation.EndUpdate();

            return datavalidation;
        }
  

we need to have same format as our data looks for Booleans and Long Intergers (mentioned as Expected examples)

Please take this as high priority and provide us some solution

[Shamini Kiruba Sobers]

Hi Jatin, 

Thanks for the update. 

For Boolean case, adding the following code line will actually resolve the issue because Microsoft Excel doesn’t auto format Boolean values only when the Number Format is Text. 

workSheet.Range[range].NumberFormat = "@"; 

You can download the working sample for Boolean case from the following link, in which Number Format is set to Text for entire column by creating default style to avoid performance issue. 

https://www.syncfusion.com/downloads/support/directtrac/general/ze/F152563-2103698411 

For Long Integers: As per MS Excel behavior, the number value may be shown as exponential based on the column width. To view full value, we recommend to use AutofitColumn. 

Note: Use of AutoFitColumn may cause performance problems. 

Kindly try and let us know if it helps. 

Regards, 

Shamini 

[Jatin Gundabathula]

Hi,

Thanks for the update however I believe my issue is different the issue occurs when we have DataTable filled with data for Booleans. I am attaching the code sample which will replicate the issue.

Below is code snippet as well

        //Create a workbook
                IWorkbook workbook = application.Workbooks.Create(1);
                DataTable dataTable = GetDataTable();
                FillTable(dataTable);

 private void FillTable(DataTable dataTable)
        {
            DataRow datarowObj = dataTable.NewRow();
            datarowObj["columnBoolean"] = "false";

            dataTable.Rows.Add(datarowObj);
        }

Please do need full ASAP.

Thanks

Jatin.G

Attachment: F152563_ca8e6158.zip

[Jatin Gundabathula]

Hi Team,

Attached is a sample where Booleans and Long Integers are not working as motioned earlier 

workSheet.Range[range].NumberFormat = "@"; and AutofitColumn solution not working when we filed that datatable with data you can check this issue from attached sample do let me know if you have any other questions

Please resolve this ASAP

Attachment: F152563_Sample_74ac2226.zip

[Shamini Kiruba Sobers]

Hi Jatin, 

Thanks for the update. 

You have set number format and validations after importing the values from DataTable into worksheet. We have shuffled your code along with a small workaround to make it work for Boolean and long integers, as required. The imported values when set in worksheet cells, are now formatted to lower case and without exponential values. Workaround added for Boolean case is highlighted below and please note that it must be placed after importing values from DataTable. 

Code snippet: 

public IActionResult CreateDocument() 

{ 

    //Create an instance of ExcelEngine 

    using (ExcelEngine excelEngine = new ExcelEngine()) 

    { 

        IApplication application = excelEngine.Excel; 

        application.DefaultVersion = ExcelVersion.Excel2016; 

 

        //Create a workbook 

        IWorkbook workbook = application.Workbooks.Create(1); 

        DataTable dataTable = GetDataTable(); 

 

        FillTable(dataTable); 

        var worksheet = workbook.Worksheets.Create(dataTable.TableName); 

 

        //Number format is set to Text for column 3 and column 5 

        IStyle style = workbook.Styles.Add("NewStyle"); 

        style.NumberFormat = "@"; 

        worksheet.SetDefaultColumnStyle(3, style); 

        worksheet.SetDefaultColumnStyle(5, style); 

 

        IDataValidation intvalidation = worksheet["A1:A5"].DataValidation; 

        intvalidation.BeginUpdate(); 

        intvalidation.AllowType = ExcelDataType.Integer; 

        intvalidation.EndUpdate(); 

 

        IDataValidation realvalidation = worksheet["B1:B5"].DataValidation; 

        realvalidation.BeginUpdate(); 

        realvalidation.AllowType = ExcelDataType.Formula; 

        realvalidation.FirstFormula = "=ISNUMBER(B1)"; 

        realvalidation.EndUpdate(); 

 

        IDataValidation boolvalidation = worksheet["C1:C5"].DataValidation; 

        boolvalidation.BeginUpdate(); 

        boolvalidation.AllowType = ExcelDataType.User; 

        boolvalidation.ListOfValues = new string[] { "true", "false" }; 

        boolvalidation.EndUpdate(); 

 

        IDataValidation listvalidation = worksheet["D1:D5000"].DataValidation; 

        listvalidation.BeginUpdate(); 

        listvalidation.AllowType = ExcelDataType.Formula; 

        listvalidation.FirstFormula = "=ISNUMBER(--SUBSTITUTE(D1,\",\",\"\"))"; 

        listvalidation.EndUpdate(); 

 

        //IDataValidation longvalidation = worksheet["E1:E5"].DataValidation; 

        //longvalidation.BeginUpdate(); 

        //worksheet.Range["E1:E5"].NumberFormat = "@"; 

        //longvalidation.EndUpdate(); 

 

        worksheet.ImportDataTable(dataTable, true, 1, 1); 

 

        //Workaround to preserve the text along with its number format 

        IRange booleanRange = worksheet["C2:C5"]; 

        foreach(IRange range in booleanRange) 

        { 

            string value = range.Value2.ToString(); 

            if(value != "") 

                range.Formula = "=LOWER(" + value + ")"; 

        } 

 

        var table = worksheet.ListObjects.Create(dataTable.TableName, worksheet.UsedRange); 

        table.BuiltInTableStyle = TableBuiltInStyles.TableStyleMedium6;                 

 

        //Saving the Excel to the MemoryStream  

        MemoryStream stream = new MemoryStream(); 

        workbook.SaveAs(stream); 

 

        //Set the position as '0'. 

        stream.Position = 0; 

 

        //Download the Excel file in the browser 

        FileStreamResult fileStreamResult = new FileStreamResult(stream, "application/excel"); 

 

        fileStreamResult.FileDownloadName = "Output2.xlsx"; 

 

        return fileStreamResult; 

    } 

} 

The working sample with above code can be downloaded from the following link.
https://www.syncfusion.com/downloads/support/directtrac/general/ze/F152563_Resolved-2086369371 

Kindly try this and let us know if this fulfils your requirement. 

Regards, 

Shamini