Row and column count and dynamic access of data values

[Ajay Shastry]

Hi SyncFusion Team,

In connection to the code in this link under the topic accessing a cell or range using IMigrantRange, 2 variables rowCount and colCount have to be created. They must obviously be integers. But my question is that, is there a method or a property that can be used to read data values from every row and column in an excel file that is being retrieved from the database dyamically?

IWorksheet sheet = workbook.Worksheets[w];
IMigrantRange migrantRange = sheet.MigrantRange;
IRange[] rowCount = sheet.Rows;
IRange[] colCount = sheet.Columns;
foreach (IRange Rows in rowCount)
     {
          foreach(IRange Columns in colCount)
               {
                    //Code to read data in row and column and manipulate them accordingly.
               }
     }

What I need to be able to do is read every row and column grid cell and then set appropriate data type to that grid. Another problem with the above code you might notice is that rowCount and colCount are not integers and hence cannot be used in a for loop.

Setting the data types of individual grid cells is not effective because the data values are being loaded dynamically and need to be parsed at runtime.

Any help and assistance would be greatly appreciated.

Regards,
Ajay Shastry

[Sridhar Sukumar]

Hi Ajay, 

 

Thank you for contacting Syncfusion support. 

 

RowCount and ColumnCount variables denotes the number of rows and columns to be manipulated respectively. Please refer the following code example to access data from a worksheet. 

 

Code snippet: 

IWorksheet sheet = workbook.Worksheets[0]; 

IMigrantRange migrantRange = sheet.MigrantRange; 

int rowCount = sheet.UsedRange.LastRow; 

int colCount = sheet.UsedRange.LastColumn; 

for (int i = 1; i <= rowCount; i++) 

{ 

    for (int j = 1; j <= colCount; j++) 

    { 

        migrantRange.ResetRowColumn(i,j); 

        string value = migrantRange.DisplayText; 

    } 

} 

To know more about the used range, please refer the following link.

UG documentation Link: https://help.syncfusion.com/file-formats/xlsio/worksheet-cells-manipulation#accessing-used-range-of-a-worksheet 

 

As per Microsoft Excel behavior, cell type is set based on the cell value. By using IRange.Value2 property, you can set the cell value dynamically. Please refer the following code example to achieve this. 

 

Code snippet: 

//Set dynamic data to Value2 property 

sheet.Range["A1"].Value2 = value; 

 

To know more about Value2 property, please refer the below link.

UG Documentation Link: https://help.syncfusion.com/cr/file-formats/Syncfusion.XlsIO.IRange.html#Syncfusion_XlsIO_IRange_Value2

Please let us know if you have any concern. 

 

Regards, 

Sridhar S. 

[Ajay Shastry]

Hi Sridhar,

Thank you for your quick response!

I will play around with the methods that you gave me and let you know in the same thread if I run into any issues or if I need further clarification on this issue.

Regards,

Ajay Shastry

[Sridhar Sukumar]

Hi Ajay,  

 

Please get back to us if you need any assistance, we will be happy to assist you. 

 

Regards, 

Sridhar S.

[Ajay Shastry]

Hi Sridhar,

I understand from going through the documentation of Value2 that it assigns format of the cells using the content of the data. But my query is can I use Value2 property without explicitly specifying the grid cell numbers. Could I use rowCount and colCount variables that I've created instead?

For me to do this, I need to be able to use an if condition inside the two for loops and make use of the Value2 property and also possibly the sheet.Range property. So my question is can an if condition be used to access data dynamically without specifying the grid numbers?

I want to be able to do something like this.

IWorksheet sheet = workbook.Worksheets[0];

IMigrantRange migrantRange = sheet.MigrantRange;

int rowCount = sheet.UsedRange.LastRow;

int colCount = sheet.UsedRange.LastColumn;

for (int row = 1; row <= rowCount; row++)

     {
          for (int column = 1; column <= colCount; column++)

               {
                     if(sheet.UsedRange[rowCount:colCount].NumberFormat = "Text") // If the data in the current grid is a string, set it to a string format.

                     sheet.Range[rowCount:colCount] = "Text";

                     else if (sheet.UsedRange[rowCount:colCount].NumberFormat = "0.00") //If the data in the current row is a number, set the grid data type to Number format.

                     sheet.Range[rowCount:colCount] = "Number"

                    

                     // So on for a date.

                }
      }

Hope you got the idea of what I'm trying to do. Please get back to me if you need further clarification.

Kind Regards,

Ajay Shastry

[Sridhar Sukumar]

Hi Ajay,  

  

Thank you for updating us. 

 

Please refer the following code example to achieve your requirement. 

 

Code snippet: 

foreach (IRange cell in worksheet.UsedRange) 

{ 

    if(cell.HasString) 

    { 

        cell.NumberFormat = "@"; 

    } 

    else if(cell.HasNumber) 

    { 

        cell.NumberFormat = "0.00"; 

    } 

    else if(cell.HasDateTime) 

    { 

        ell.NumberFormat = "m/d/yyyy"; 

    } 

} 

 

We have also prepared a sample for this which will be downloaded from the following location. 

 

Sample link:  

http://www.syncfusion.com/downloads/support/directtrac/general/ze/DynamicAccess-437521241.zip 

 

Please let us know if you have any concern. 

 

Regards, 

Sridhar S. 

[Ajay Shastry]

Hi Sridhar,

That piece of code you sent me looks like just what I wanted. I will test it and get back to you if I want help.

Thanks much.

Regards,

Ajay Shastry

[Sridhar Sukumar]

Hi Ajay,  

 

Thank you for updating us. 

 

We are glad that the issue is resolved at your end. Please let us know if you need any further assistance. 

  

Regards, 

 Sridhar S.

[Ajay Shastry]

Hi Sridhar,

Sorry for a late response query to your solution. I tried the code that you provided.

foreach (IRange cell in sheet.UsedRange)
{

      if(cell.HasNumber)
          {
               cell.NumberFormat = "0.00";
          }
 }

In addition to the above code, I also tried the following code.

IMigrantRange migrantRange = sheet.MigrantRange;

int rowCount = sheet.UsedRange.LastRow;

int colCount = sheet.UsedRange.LastColumn;

for(int i = 1 ; i <= rowCount; i++)
{  

       for(int j = 1; j <= colCount; j++)             

            { 

                   if(migrantRange.HasNumber)
                            {
                                migrantRange.NumberFormat = "0.00";
                            }
             }
 }                      

                 

The problem with bo0th code snippets seem to be that the variable rowCount and colCount do not seem to initialize properly using the functions LastRow and LastColumn. So could you advice me on how to proceed with this?

In the first code snippet, the variable cell does not increment and always stays 0. So the for loop is never executed.      

Any help would be appreciated.

Kind Regards,

Ajay Shastry                        
                    
                        
                        
                   

                   

[Sridhar Sukumar]

Hi Ajay, 

 

Thank you for updating us. 

 

When the worksheet is empty, UsedRange.LastRow and UsedRange.LastColumn values are set as -1. So, the for loop will not be executed. If you want to access the cell in empty worksheet, then row counts and column index should be set as manually. Please refer the following code example to achieve your requirement.  

 

Code snippet: 

IMigrantRange migrantRange = worksheet.MigrantRange; 

int rowCount = 10; 

int colIndex = 5; 

for (int i = 1; i <= rowCount; i++) 

{ 

    migrantRange.ResetRowColumn(i, colIndex); 

    migrantRange.NumberFormat = "0.00"; 

} 

 

We have also prepared a sample for this which will be downloaded from the following link.  

  

Sample link:   

http://www.syncfusion.com/downloads/support/directtrac/general/ze/DynamicAccess184192699.zip 

  

Please let us know if you have any concern.  

             

Regards,  

Sridhar S. 

[Ajay Shastry]

Hi Sridhar,

I think you have misunderstood my requirement. The excel worksheet that I'm trying to download is not empty. It has data filled in. It is just that just before the file is downloaded, my code needs to scan the data in each filled cell, determine whether it is a number or a text or date, set the appropriate format and then download. That is the reason I said that the for loop is not being initialized and the value of rowCount and colCount is set to zero and the for loop is exiting without executing further.

While we are on this issue, Could I know if there is any limitation with Syncfusion XLSIO in exporting an excel file as an XLS formatted file?

I greatly appreciate your quick responses.

Kind regards,

Ajay Shastry 

[Sridhar Sukumar]

Hi Ajay,  

  

Thank you for updating us. 

 

When the worksheet is not empty, then UsedRange.LastRow and UsedRange.LastColumn will not be set as 0 in XlsIO, We suspect that the issue is raised from the input file. Kindly share us the issue reproducing sample and input files which will be helpful for us to give you a prompt solution at the earliest. 

 

Regards, 

Sridhar S. 

[Ajay Shastry]

Hi Sridhar,

I'm using the following code snippet to achieve the cell formatting dynamically.

foreach (IRange cell in worksheet.UsedRange) 

{ 

    if(cell.HasString) 

    { 

        cell.NumberFormat = "@"; 

    } 

    else if(cell.HasNumber) 

    { 

        cell.NumberFormat = "0.00"; 

    } 

    else if(cell.HasDateTime) 

    { 

        ell.NumberFormat = "m/d/yyyy"; 

    } 

} 

I am also attaching the input file that I'm trying to format. But please note that this is a very small sample file and the actual files may be larger.

Also, I would appreciate if you could let me know if there any limitations with XLSIO with the exporting of an excel file in XLS format?

Kind regards,

Ajay Shastry

Attachment: Sample_20d3d3b2.zip

[Sridhar Sukumar]

Hi Ajay, 

 

We are unable to reproduce the issue which you reported on 18th April, 2017. Also, the code snippet is working fine with the Excel file shared in your last update. The output Excel document generated in XlsIO is shared for your reference, which can be downloaded from the following link. 

 

Output Excel link:  

http://www.syncfusion.com/downloads/support/directtrac/general/ze/XlsIO_Output-55305429.zip 

 

To investigate further on this, we need to have the actual Excel file that is causing the issue as we suspect that the issue raises only in the particular document. You can also create an incident from Direct-Trac to share your Excel document for further investigation, which will be disclosed and used only for testing purpose. 

 

Regarding XlsIO limitation in XLS format: 

MS excel supports maximum 65536 rows and 256 columns in Excel 2003 (XLS) format and XlsIO behaves similar to that. You can find further information regarding this from the following link. 

 

https://help.syncfusion.com/file-formats/xlsio/supported-and-non-supported-features 

 

Regards, 

Sridhar S. 

[Ajay Shastry]

Hi Sridhar,

I appreciate you putting so much effort to resolve my issue. I get this exception in the under sheet.UsedRange in my code when I debug it. 

'((Syncfusion.XlsIO.Implementation.RangeImpl)sheet.UsedRange).CellName' threw an exception of type 'System.ArgumentOutOfRangeException'. 

What does this exception mean? Also, the message field under sheet.UsedRange reads "Specified argument was out of the range of valid values.\r\nParameter name: column or row index is wrong. It cannot be less then 1". What could be causing this error? 

Logically, the code you provided is exactly what I'm looking for. But for some reason, I can't get it to work. Any help would be greatly appreciated. 

Please get back to me if you need any more information. 

Kind regards,

Ajay Shastry

[Sridhar Sukumar]

Hi Ajay, 

 

The exception which is mentioned in your last update is raised when the worksheet is empty. We suspect that the issue is raised from the particular Excel file. So, we request you to share us the issue reproducing Excel file as mentioned in our last update which will be helpful for us to give you a prompt solution at earliest. 

 

Regards, 

Sridhar S. 

[Ajay Shastry]

Hi Sridhar,

I am pleased to say that the code snippet you provided is working perfectly with a bit of modification from my side. It was throwing those exceptions because I was trying to scan the worksheets before they were loaded from the database. I fixed that and it worked.

I appreciate your quick responses in trying to help me resolve it.

Impressed with the service.

Kind regards,

Ajay Shastry

[Sridhar Sukumar]

Hi Ajay,   

  

Thank you for updating us.  

  

We are glad that the issue is resolved at your end. Please let us know if you need any further assistance.  

   

Regards,  

Sridhar S. 

[Ajay Shastry]

Hi Syncfusion Team,

This issue was resolved until I was hardcoding the number format explicitly in code. I wanted to know if I could set the number formatting dynamically based on the current culture settings of the web page being displayed. For example, if the current culture info settings are Albanian, could I set the number format to Albanian where the ',' and '.' are interchanged?

I ask this because I tried implementing it. But whatever I do, the excel files are being downloaded using US culture settings by default. So I wanted to know if this is not possible in Syncfusion.

Kind regards,

Ajay Shastry

[Sridhar Sukumar]

Hi Ajay, 

 

Thank you for updating us. 

 

If you set a built-in US number format to a cell (such as 0.00 , mm/dd/yyyy, etc. ), MS Excel displays the value in current system culture. A sample illustrating this behavior is shared in the following link. 

 

Sample link: http://www.syncfusion.com/downloads/support/directtrac/general/ze/Sample1230532803.zip 

 

If you want to set number format in Albanian culture, MS Excel considers as a custom format. This format will be displayed in all other cultures.  

 

Please let us know if you have any clarification. 

  

Regards, 

Sridhar. 

  

  

[Ajay Shastry]

Hi Syncfusion Team,

I have a query. I read that Excel. owing to performance issues truncates leading and trailing zeroes if a particular cell is formatted as a number. So I wanted to ask you guys if Syncfusion offers any method to override this default Excel behavior of truncating trailing and leading zeroes. 

I will illustrate this with an example. 

I have a value like 914.000000. I am using logic to convert this value to a number format with the logic that has been discussed in this ticket. But the value being entered in the downloaded excel sheet is 914.00 or just 914. I understand that this is the default excel behavior of truncating trailing zeroes. So my question is, is there any way that I can retain the value 914.000000 and still have it formatted as a number? 

Thanks in advance for your help.

Kind regards,

Ajay Shastry

[Sridhar Sukumar]

Hi Ajay, 

 

You can achieve your requirement by a workaround in XlsIO. We have prepared a sample for this which can be downloaded from the following link 

 

Sample link: 

http://www.syncfusion.com/downloads/support/directtrac/general/ze/Sample-114913784.zip 

 

Regards, 

Sridhar. 

[Ajay Shastry]

Hi Sridhar, 

Thank you for your help all along the way. I appreciate your quick and sensible responses. 

Regards,

Ajay

[Sridhar Sukumar]

Hi Ajay, 

 

We are glad that the issue is resolved at your end. Please let us know if you need any further assistance. 

 

Regards, 

Sridhar.