1. Home
  2. Forum
  3. WinForms
  4. Last row data question?

Last row data question?

Hi,
Please help me to find index of last row for each sheets? 
Thanks!

Attachment: Khối_1__p1_9fdf5365.rar

34 Replies 1 reply marked as answer
SK Shamini Kiruba Sobers Syncfusion Team August 26, 2020 12:05 PM UTC

Hi Khanh Dang, 

Greetings from Syncfusion. 

You can find the last row index of each worksheet with the help of LastRow property. You can use any of the below code lines to get the index of last row in a worksheet. 

Code snippets: 

IWorksheet worksheet = workbook.Worksheets[0]; 
int lastRow = worksheet.Range.LastRow; 
 
(or) int lastRow1 = worksheet.UsedRange.LastRow; 

(or) 

int lastRow2 = worksheet.Rows.Length; 

Kindly let us know if this helps. 

Regards, 
Shamini 


TG The GridLock August 27, 2020 06:13 AM UTC

Hi shamini, 
can you check the specific results for me?
I have compared the result from lastrow and the result from the vba code, and the result from vba is 21 while lastrow is 34 for the first sheet.
i have 2nd question with merge files
Before:
file1-sheet1

file2-sheet1

Notes: I will ignore the header from file2, however
although I chose exportRangecopyoption = all
file merge results that I get

from row22, my rows does not contain format
Please check this!

TG The GridLock August 27, 2020 06:14 AM UTC

Attach Merge files.

Attachment: Downloads_c6ba09bc.rar

SK Shamini Kiruba Sobers Syncfusion Team August 27, 2020 01:54 PM UTC

Hi Khanh Dang, 

Please find the response for your queries from the following table. 

Query 
Response 
can you check the specific results for me? 
I have compared the result from lastrow and the result from the vba code, and the result from vba is 21 while lastrow is 34 for the first sheet. 
For the given file, XlsIO returns last row index as 34 equivalent to Microsoft Excel’s “Ctrl+End” behavior, which considers the used blank cells too. You can get the last row index (equivalent to “Ctrl+DownArrow”) without considering the blank cells by setting the UsedRangeIncludesFormatting property to false as below. 

Code snippet: 

worksheet.UsedRangeIncludesFormatting = false; 

After setting this property to false, you can get the following results with the suggested properties to get the index of last row. 

 

Kindly let us know if this helps. 

i have 2nd question with merge files 
Notes: I will ignore the header from file2, however 
although I chose exportRangecopyoption = all 
file merge results that I get 
from row22, my rows does not contain format 
Please check this! 
With the shared information, we have reproduced the mentioned issue with the below code and validating it currently. We will share the validation details on August 31st, 2020. 

Code snippet: 

using (ExcelEngine excelEngine = new ExcelEngine()) 
{ 
    IApplication application = excelEngine.Excel; 
 
    IWorkbook workbook = application.Workbooks.Open("Block P1.xlsx"); 
    IWorksheet destinationSheet = workbook.Worksheets[0]; 
    destinationSheet.UsedRangeIncludesFormatting = false; 
    int lastRow = destinationSheet.Rows.Length;                 
 
    IWorkbook workbook1 = application.Workbooks.Open("Block P2.xlsx"); 
    IWorksheet sourceSheet = workbook1.Worksheets[0]; 
    int endRow = sourceSheet.Rows.Length; 
    int endColumn = sourceSheet.Columns.Length; 
    IRange sourceRange = sourceSheet[2, 1, endRow, endColumn]; 
 
    int destRowIndex = lastRow + 1; 
    IRange destinationRange = destinationSheet[destRowIndex, 1, destRowIndex + endRow, endColumn]; 
    sourceRange.CopyTo(destinationRange, ExcelCopyRangeOptions.All); 
 
    workbook.SaveAs("Output.xlsx"); 
} 

Please confirm us whether you are also facing the issue while copying using CopyTo method as highlighted. 


Regards, 
Shamini

TG The GridLock August 27, 2020 02:21 PM UTC

Issue 1 has been resolved. I didn't remember the name of this property.
problem 2, can you take a picture of the result you achieved?
That's the way I'm doing it but i can't get.

SK Shamini Kiruba Sobers Syncfusion Team August 28, 2020 06:27 AM UTC

Hi Khanh Dang, 

Thanks for the update. 

We are getting the same issue as you. 

 

Here, we’re sharing the output file too for your reference. 

Since you didn’t share the code, we just wanted to confirm whether you are also getting this issue when using CopyTo method or with some other. As mentioned earlier, we are validating this issue with CopyTo method and we will share the details on August 31st, 2020. 

Regards,
Shamini 


TG The GridLock August 28, 2020 07:34 AM UTC

Hi shamini,
1. Suppose the table in file1 automatically adds rows to combine table 2 in table 1(table2 have table1'sformat). if so I think it will need the listobject.listrow.add (alwayinsert: = true) method as shown vba.
2. otherwise possible because possible range from table2. (it's in the table that hasn't been checked) so just got the value.
Not sure but Maybe converting table 2 to range will work. But I don't see both 2 options!

SK Shamini Kiruba Sobers Syncfusion Team August 31, 2020 04:48 PM UTC

Hi Khanh Dang, 

Thanks for the update. We have found a different solution that works. We have included the highlighted code lines in the following code snippet to extend the table location till the last row in the destination worksheet so that the copied rows will have the same table formatting. 

Code snippet: 

using (ExcelEngine excelEngine = new ExcelEngine()) 
{ 
    IApplication application = excelEngine.Excel; 
 
    IWorkbook workbook = application.Workbooks.Open("Block P1.xlsx"); 
    IWorksheet destinationSheet = workbook.Worksheets[0]; 
    destinationSheet.UsedRangeIncludesFormatting = false; 
    int lastRow = destinationSheet.Rows.Length;                 
 
    IWorkbook workbook1 = application.Workbooks.Open("Block P2.xlsx"); 
    IWorksheet sourceSheet = workbook1.Worksheets[0]; 
    int endRow = sourceSheet.Rows.Length; 
    int endColumn = sourceSheet.Columns.Length; 
    IRange sourceRange = sourceSheet[2, 1, endRow, endColumn]; 
 
    int destinationRowIndex = lastRow + 1; 
    IRange destinationRange = destinationSheet[destinationRowIndex, 1, lastRow + endRow - 1, endColumn]; 
    sourceRange.CopyTo(destinationRange, ExcelCopyRangeOptions.All); 
 
    IRange range = destinationSheet[1, 1, destinationSheet.Rows.Length, endColumn]; 
    IListObject listObject = destinationSheet.ListObjects[0]; 
    listObject.Location = range; 
                 
    workbook.SaveAs("Output1.xlsx"); 
} 
 

Kindly let us know if this helps. However, we have logged a bug report for not copying the table cell styles as it is in the source sheet. The fix will be available on September 14th, 2020. You can track the status of the issue through the following link. 


Also, please confirm your working version of Syncfusion XlsIO, so that we can provide patch in your required version. 

Regards, 
Shamini 
 


TG The GridLock September 1, 2020 05:47 AM UTC

Hi shamini,
My version is 18.1.042.
Please check for help if the area in file 1 is not table (normal range), then the area copied from table 2 to range1 is preserved (format,..) as your option.
Maybe I will wait for this because I have many cases where I cannot edit them all.

SK Shamini Kiruba Sobers Syncfusion Team September 1, 2020 12:17 PM UTC

Hi Khanh Dang, 

Thanks for confirming the version. We will provide patch for the fix in 18.1.0.42 on September 14th, 2020 as promised earlier. 

If the area in the source sheet is not a table (normal range), then the format gets preserved when copied to the destination sheet. There won’t be any issue even when the entire table is copied without excluding the first row. The issue comes only when a custom data range from the table is copied. 

Regards, 
Shamini

SK Shamini Kiruba Sobers Syncfusion Team September 14, 2020 02:59 PM UTC

Hi Khanh Dang, 

We appreciate your patience. 

We have fixed the issue CopyTo method does not copy Excel table styles if copied from second row or more and generated patch in the version v18.1.0.42. The patch with fix to resolve the reported issue can be downloaded from the following link. 


Recommended approach - exe will perform automatic configuration       
   
Please find the patch setup from below location:       
       
Advanced approach – use only if you have specific needs and can directly replace existing assemblies for your build environment       
   
Please find the patch assemblies alone from below location:       

     
Assembly Version: 18.1.0.42 

Installation Directions :        
This patch should replace the files “Syncfusion.XlsIO.Base.dll” under the following folder.       
$system drive:\ Files\Syncfusion\Essential Studio\$Version # \precompiledassemblies\$Version#\4.6       
Eg : $system drive:\Program Files\Syncfusion\Essential Studio\18.1.0.42\precompiledassemblies\18.1.0.42\4.6    
       
To automatically run the Assembly Manager, please check the Run assembly manager checkbox option while installing the patch. If this option is unchecked, the patch will replace the assemblies in precompiled assemblies’ folder only. Then, you will have to manually copy and paste them to the preferred location or you will have to run the Syncfusion Assembly Manager application (available from the Syncfusion Dashboard, installed as a shortcut in the Application menu) to re-install assemblies.       
       
Note :        
You can change how you receive bug fixes by navigating to the following link and updating your preferences.        
    
Disclaimer :   
Please note that we have created this patch for version 18.1.0.42 specifically to resolve the issue confirmed in the forum 157287. 
              
If you have received other patches for the same version for other products, please apply all patches in the order received.       
      
This fix will be included in our release version 18.3 which will be rolled out by end of September 2020.  

Regards,     
Shamini

Marked as answer
TG The GridLock September 17, 2020 03:01 AM UTC

ok shamini,
I will check it out.

SK Shamini Kiruba Sobers Syncfusion Team September 17, 2020 04:57 PM UTC

Hi Khanh Dang, 

Thanks for the update. We will wait to hear from you. 

Regards, 
Shamini 


TG The GridLock September 19, 2020 11:57 AM UTC

Hi Shamini,
This problem has been resolved!, now

I have a new question. is still the merge. try with my attachment (optionCopyALL). Occasionally there are errors that appear after merging.
I want to know what mistakes it is so I can avoid them!






TG The GridLock September 19, 2020 12:38 PM UTC

this is my attachment, and I am trying to merge these file (2sheets/file) into one file(6sheets)
it succeeds but when opening the file, I want to avoid this message.
Maybe for some reason?

Attachment: Downloads_bea97981.rar

.

SK Shamini Kiruba Sobers Syncfusion Team September 21, 2020 12:19 PM UTC

Hi Khanh Dang, 

Thanks for the update. 

We are unable to reproduce the reported issue at our end. The sample we tried at our end can be downloaded from the following link. 

Kindly modify the sample to reproduce the issue and share us the modified sample so that we can analyze further. 

Regards,
Shamini 


TG The GridLock September 21, 2020 05:30 PM UTC

Hi shamini,
please try again, this is another merge type,
I have 3 files (each file contains 2 sheets), now copy all of these sheets into a new file (result is 6 sheets)
I want you to try merging them, and will the new file get an error?
Here is the result I got!

Attachment: Fileresult_62edec0e.rar

SK Shamini Kiruba Sobers Syncfusion Team September 22, 2020 11:23 AM UTC

Hi Khanh Dang, 

Thanks for the details. By seeing your output file, we are able to reproduce the reported corruption issue and validating it currently. We will share the validation details on September 24th, 2020. 

Regards, 
Shamini 


KK Konduru Keerthi Konduru Ravichandra Raju Syncfusion Team September 24, 2020 05:07 PM UTC

Hi Khanh Dang, 

We appreciate your patience. 

We have confirmed the issue with Copying multiple worksheets with tables leads to file corruption and logged a defect report. We will provide the patch for this issue on October 10th,2020. You can track the status of defect report through following feedback link. 


Regards, 
Keerthi. 


SK Shamini Kiruba Sobers Syncfusion Team October 9, 2020 01:59 PM UTC

Hi Khanh Dang, 

We appreciate your patience. 

We have fixed the issue Copying multiple worksheets with tables leads to file corruption and generated patch in the version v18.1.0.42. The patch with fix to resolve the reported issue can be downloaded from the following link. 


Recommended approach - exe will perform automatic configuration       
   
Please find the patch setup from below location: 
       
Advanced approach – use only if you have specific needs and can directly replace existing assemblies for your build environment       
   
Please find the patch assemblies alone from below location:       

     
Assembly Version: 18.1.0.42 

Installation Directions :        
This patch should replace the files “Syncfusion.XlsIO.Base.dll” under the following folder.       
$system drive:\ Files\Syncfusion\Essential Studio\$Version # \precompiledassemblies\$Version#\4.6       
Eg : $system drive:\Program Files\Syncfusion\Essential Studio\18.1.0.42\precompiledassemblies\18.1.0.42\4.6    
       
To automatically run the Assembly Manager, please check the Run assembly manager checkbox option while installing the patch. If this option is unchecked, the patch will replace the assemblies in precompiled assemblies’ folder only. Then, you will have to manually copy and paste them to the preferred location or you will have to run the Syncfusion Assembly Manager application (available from the Syncfusion Dashboard, installed as a shortcut in the Application menu) to re-install assemblies.       
       
Note :        
You can change how you receive bug fixes by navigating to the following link and updating your preferences.        
    
Disclaimer :   
Please note that we have created this patch for version 18.1.0.42 specifically to resolve the issues reported in forum 157287.      
              
If you have received other patches for the same version for other products, please apply all patches in the order received.       
      
This fix will be included in our release version 18.3-SP1 which will be rolled out by first week of November 2020.  

Regards, 
Shamini 


TG The GridLock October 10, 2020 11:28 AM UTC

Hi shamini,
I'm still getting the same old error.
can you check again for me?
I tried with .exe file and .dll patch but the same results.

Attachment: Downloads_eb805b00.rar

SK Shamini Kiruba Sobers Syncfusion Team October 12, 2020 07:19 AM UTC

Hi Khanh Dang, 

Thanks for the update. The shared patch should resolve the issue. Kindly look into the following video screenshot captured when we were checking it again and the issue gets resolved. 

Video screenshot: 

We suspect that the issue might be due to GAC and we request you to remove the assemblies from GAC using Syncfusion Assembly Manager which is available in the Syncfusion Dashboard and also, delete bin and obj folders of the project. Refer to the following link for removing assemblies from GAC.  


Kindly try this and let us know if it helps. 

Regards, 
Shamini 


TG The GridLock October 13, 2020 05:50 AM UTC

hi shamini, your code worked for me.
Please check my code! 
I didn't create a new workbook, I get first workbook and then added other worksheets to the first workbook and it got error.

 IWorkbook workbook = null;

    foreach (string filepath in listpath)
    {
        if (workbook == null)
            workbook = GetWorkbook(application, filepath);
        else
        {
            IWorkbook temp = GetWorkbook(application, filepath );
            workbook.Worksheets.AddCopy(temp.Worksheets, copyflagsoption);
            temp.close(false);
        }
    }
 savefile(....);
Also I'd like to ask about ExcelWorksheetCopyFlags.CopyColumnHeight (Is the name wrong or not?)


SK Shamini Kiruba Sobers Syncfusion Team October 13, 2020 12:46 PM UTC

Hi Khanh Dang, 

Thanks for the update. 

Query 
Response 
Please check my code!  
I didn't create a new workbook, I get first workbook and then added other worksheets to the first workbook and it got error. 
We are able to reproduce the issue when opening the first workbook and adding other worksheets to the first workbook. We will provide patch for the fix on October 19th, 2020. 


We appreciate your patience. 

Also I'd like to ask about ExcelWorksheetCopyFlags.CopyColumnHeight (Is the name wrong or not?) 
The name ExcelWorksheetCopyFlags.CopyColumnHeight is correct. Kindly look into the following link for ExcelWorksheetCopyFlags enumeration. 


Regards, 
Shamini 


TG The GridLock October 13, 2020 01:34 PM UTC

Ok shamini,
I think its name should be CopyColumnWidth instead CopyColumnHeight . right? 

SK Shamini Kiruba Sobers Syncfusion Team October 14, 2020 10:27 AM UTC

Hi Khanh Dang, 

Yes, we agree with your suggestion. Thanks for reporting. But, if we change the public API now, it might cause breaking issues for the existing users. Hence, we will consider your suggestion and rename the API when feasible. 

Regards, 
Shamini 


SK Shamini Kiruba Sobers Syncfusion Team October 20, 2020 05:26 AM UTC

Hi Khanh Dang, 

We appreciate your patience. 

We have fixed the issue Copying multiple worksheets with tables leads to file corruption and generated patch in the version v18.1.0.42. The patch with fix to resolve the reported issue can be downloaded from the following link. 


Recommended approach - exe will perform automatic configuration       
   
Please find the patch setup from below location: 
       
Advanced approach – use only if you have specific needs and can directly replace existing assemblies for your build environment       
   
Please find the patch assemblies alone from below location:       

     
Assembly Version: 18.1.0.42 

Installation Directions :        
This patch should replace the files “Syncfusion.XlsIO.Base.dll” under the following folder.       
$system drive:\ Files\Syncfusion\Essential Studio\$Version # \precompiledassemblies\$Version#\4.6       
Eg : $system drive:\Program Files\Syncfusion\Essential Studio\18.1.0.42\precompiledassemblies\18.1.0.42\4.6    
       
To automatically run the Assembly Manager, please check the Run assembly manager checkbox option while installing the patch. If this option is unchecked, the patch will replace the assemblies in precompiled assemblies’ folder only. Then, you will have to manually copy and paste them to the preferred location or you will have to run the Syncfusion Assembly Manager application (available from the Syncfusion Dashboard, installed as a shortcut in the Application menu) to re-install assemblies.       
       
Note :        
You can change how you receive bug fixes by navigating to the following link and updating your preferences.        
    
Disclaimer :   
Please note that we have created this patch for version 18.1.0.42 specifically to resolve the issues reported in forum 157287.      
              
If you have received other patches for the same version for other products, please apply all patches in the order received.       
      
This fix will be included in our release version 18.3-SP1 which will be rolled out by first week of November 2020.  

Regards, 
Shamini 
 


TG The GridLock October 29, 2020 08:46 AM UTC

Hi shamini,

Notifications are now gone. However please check for me the backcolor header has been changed.
I still use ExcelWorksheetCopyFlags.ALL


SK Shamini Kiruba Sobers Syncfusion Team October 30, 2020 08:02 AM UTC

Hi Khanh Dang, 

We are able to reproduce the reported issue and validating it currently. We will share the details in two business days on November 3rd, 2020. 

Regards, 
Shamini 


SK Shamini Kiruba Sobers Syncfusion Team November 3, 2020 02:04 PM UTC

Hi Khanh Dang, 

We have validated the issue Table header color is changed while copying the worksheets and logged a defect report. The fix for the issue will be available on November 17th, 2020. You can track the status of the issue through the following link. 


Regards, 
Shamini 


TG The GridLock November 3, 2020 02:30 PM UTC

Hi shamini
it seems the issue has not been affected before!

SK Shamini Kiruba Sobers Syncfusion Team November 4, 2020 01:42 PM UTC

Hi Khanh Dang, 

The issue exists when a workbook is created and a worksheet is copied into it. But the issue does not reproduce if an existing workbook is opened and a worksheet is copied into it. 

Kindly explain us if you are facing any other issues. 

Regards, 
Shamini 


SK Shamini Kiruba Sobers Syncfusion Team November 17, 2020 03:20 PM UTC

Hi Khanh Dang, 
 
We regret for the delay. We will share the patch for the reported issue Table header color is changed while copying the worksheets in three business days on November 20th, 2020. 
 
Regards, 
Shamini 
 


SK Shamini Kiruba Sobers Syncfusion Team November 20, 2020 04:04 PM UTC

Hi Khanh Dang, 
 
We appreciate your patience. 
 
In the provided input document, the theme.xml document’s theme color order differs from the standard theme order. So, while copying the cell with theme color into another workbook, the theme index is using different color. It is the Microsoft Excel behavior. XlsIO does the same. So, we can’t fix this. 
 
Kindly use the below workaround for all the workbooks used to resolve the theme conflict. 
 
Code snippet: 
 
foreach (IWorksheet worksheet in workbook2.Worksheets) 
{ 
    IRange range = worksheet.UsedRange; 
    for (int i = range.Row; i <= range.LastRow; i++) 
    { 
        for (int j = range.Column; j <= range.LastColumn; j++) 
        { 
            Color color = Color.FromArgb(worksheet[i, j].CellStyle.Color.A, worksheet[i, j].CellStyle.Color.R, 
                                        worksheet[i, j].CellStyle.Color.G, worksheet[i, j].CellStyle.Color.B); 
            if (color.Name != "ffffffff") 
            { 
                worksheet[i, j].CellStyle.Color = Color.Black; 
                worksheet[i, j].CellStyle.Color = color; 
            } 
        } 
    } 
} 
 
You can also download the modified sample from the following link. 
 
Kindly let us know if it helps. 
 
Regards, 
Shamini 


Loader.
Up arrow icon