Appending multiple sheet

Aspose.Cells Product Family
1

Hi Team,


I am using Latest aspose.cells and getting multiple sheet tabs output.
Now i want to merge all these sheets(tabs) and get the output in single sheet. i.e.e each sheet ddata should be appended in sheet1 only.
Can you please let me know how this can be coded into vb.net?

2

Hi,

Please see the document/article on how to combine multiple sheets’ data into a single worksheet for your needs for your reference:
Combine Multiple Worksheets into a Single Worksheet

Thank you.

3

Hi Team,


I am able to copy multiple sheets into single sheet but getting two issues with it.
1. Formatting of the source sheet is not getting preserved in the destination sheet.(Column B and C in the source sheet is having different width but in destination its not coming)
2. I want to copy the header from the first sheet and from second sheet onwards i want to copy the date f from row 2.
Below is the code which i have used
Can you please send me the updated code for this?

Dim filePath As String = “\ysiinvgs3\Reports70rel\Demo\TEMP\InvestTS_REPORT.xls”

Dim workbook As Workbook = New Workbook(filePath)
Dim destWorkbook As Workbook = New Workbook()
Dim destSheet As Worksheet = destWorkbook.Worksheets(0)
Dim TotalRowCount As Integer = 0

For I = 0 To workbook.Worksheets.Count - 1
Dim sourceSheet As Worksheet = workbook.Worksheets(I)
Dim sourceRange As Range = sourceSheet.Cells.MaxDisplayRange

Dim destRange As Range = destSheet.Cells.CreateRange(sourceRange.FirstRow + TotalRowCount, sourceRange.FirstColumn, _
sourceRange.RowCount, sourceRange.ColumnCount)

destRange.Copy(sourceRange)
TotalRowCount = sourceRange.RowCount + TotalRowCount
Next
destWorkbook.Save("\ysiinvgs3\Reports70rel\Demo\TEMP\oUTPUT.xls")
4

Hi there,


Thank you for writing back.

You may modify your code as follow to make sure that everything gets pasted onto a new range including column widths, styles etc.

Visual Basic

Dim sourceBook = New Workbook(myDir & “InvestTS_REPORT.xls”)
Dim destbook = New Workbook()
Dim destSheet = destbook.Worksheets(0)
Dim totalRowCount = 0
For Each sheet As Worksheet In sourceBook.Worksheets
Dim sourceRange = sheet.Cells.MaxDisplayRange
Dim destRange = destSheet.Cells.CreateRange(sourceRange.FirstRow + totalRowCount, sourceRange.FirstColumn, sourceRange.RowCount, sourceRange.ColumnCount)

'Create an instance of PasteOptions
Dim options = New PasteOptions()
'Set PasteType to All in order to paste formatting, column widths etc
options.PasteType = PasteType.All

destRange.Copy(sourceRange, options)
totalRowCount = sourceRange.RowCount + totalRowCount
Next sheet
destbook.Save(myDir & “OUTPUT.xls”)

Please feel free to write back in case you have further questions for us.
5

Hi There,


please send me the code which can be compiled as lots of variable declaration is missing the new parameter.
Also as i said i want to exclude the first row from the second sheet onwards.
please add that part also and send me the vb code.
6

Hi Raj,


The previously provided code is complete as well as the below provided one. Please let us know what object names couldn’t be resolved. Moreover, the below snippet now handles the header rows appearing only once. Please check the resultant spreadsheet for your reference.

Visual Basic

Dim sourceBook As New Workbook(myDir & “InvestTS_REPORT.xls”)
Dim destbook As New Workbook()
Dim destSheet As Worksheet = destbook.Worksheets(0)
Dim totalRowCount As Integer = 0
Dim iteration As Integer = 0
For Each sheet As Worksheet In sourceBook.Worksheets
'Declare a Range object
Dim sourceRange As Range = Nothing
'Check if first worksheet
If iteration = 0 Then
'Create a range of cells including the header row
sourceRange = sheet.Cells.CreateRange(0,0,sheet.Cells.MaxDataRow + 1, sheet.Cells.MaxDataColumn + 1)
Else
'Create a range of cells excluding the header row
sourceRange = sheet.Cells.CreateRange(1, 0, sheet.Cells.MaxDataRow + 1, sheet.Cells.MaxDataColumn + 1)
End If
'Create an equal destination range as of source range
Dim destRange As Range = destSheet.Cells.CreateRange(sourceRange.FirstRow + totalRowCount, sourceRange.FirstColumn, sourceRange.RowCount, sourceRange.ColumnCount)
'Create an instance of PasteOptions
Dim options As New PasteOptions()
'Set PasteType to All in order to paste formatting, column widths etc
options.PasteType = PasteType.All
'Copy the range while passing the PasteOption object
destRange.Copy(sourceRange, options)
totalRowCount = sourceRange.RowCount + totalRowCount
iteration += 1
Next sheet
destbook.Save(myDir & “OUTPUT.xls”)

Please feel free to write back in case you still face any issue.
7

Hi there,


thanks for the updated code. Now i am able to compile and get the output in single sheet but i am getting one issue.Column width of Column B and C is not coming as source file.(see attached sheet)
i am using aspose.cells 7.3.1.1 version.
8

Hi Raj,


Thank you for the confirmation.

Your recently shared scenario could be a bug in your current version of the API therefore we would suggest you to upgrade the API to the latest version, that is v8.0.0.1. The output attached with my previous response was generated through v8.0.0.1, and you may have observed that the column width is coming fine.

9

Hi Babar,


I have taken latest version and it gives me proper width. Unfortunately i cannot upgrade right now as the product is already launched.
So i am trying to make it work with 7.3.1.1 version and hope you can help me with it.
When i use options.PasteType = PasteType.ColumnWidths then it preserves the column width but gives the blank excel as i am just copying width but when i use options.PasteType = PasteType.All then width is not getting copied.
can you please let me know what is missing?
10

Hi Raj,


As discussed earlier, your presented scenario could be a bug in your current version of the API therefore it is suggested that you should upgrade your project to accommodate the latest build of Aspose.Cells for .NET 8.0.0.x.

Although if you do not wish to upgrade at this time, you may workaround the situation by copying the range twice. Once with PasteType.All and again with PasteType.ColumnWidths in order to make sure that column widths are copied as well. Please check the below code snippet and give it a try at your end.

Visual Basic

'Create an instance of PasteOptions
Dim options As New PasteOptions()
'Set PasteType to All in order to paste formatting, column widths etc
options.PasteType = PasteType.All
'Copy the range while passing the PasteOption object
destRange.Copy(sourceRange, options)
'Set PasteType to ColumnWidths
options.PasteType = PasteType.ColumnWidths
'Copy the range again
destRange.Copy(sourceRange, options)

Hopefully above workaround will work as desired.