We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Workbook Sheets grouped

I am using the following code to combine 2 workbooks into one. The resultant file has the initial sheets grouped and when you type anything in a cell it placed in all the cells except the 2 workbook.

workbook.Open(Application.StartupPath & "\State Stats-FY" & Mid(txtFY.Text, 3, 2) & ".xls", FileFormatType.Excel97To2003)

' Combine workbooks from the previous years
For Each fi In aryFi
blndbFound = False

If fi.Name.Trim.Length = "State Stats-FY".Length + Mid(txtFY.Text, 3, 2).Length + ".xls".Length Then
If fi.Name.IndexOf("State Stats-FY" & Mid(txtFY.Text, 3, 2) & ".xls", 0, StringComparison.CurrentCulture) >= 0 Then
blndbFound = False
Else
blndbFound = True
End If
End If

If blndbFound Then
Dim StatsWorkbook As Workbook = New Workbook
StatsWorkbook.Open(Application.StartupPath & "\" & fi.Name, FileFormatType.Excel97To2003)

workbook.Combine(StatsWorkbook)

workbook.Save(Application.StartupPath & "\State Stats-FY" & Mid(txtFY.Text, 3, 2) & ".xls", FileFormatType.Excel97To2003)
End If
Next

Hi James,

We are unable to reproduce the issue. Please forward us more details about the issue. Also, please send us the two Xls files so that we can combine those and observe the issue.

Thanks,

One of the things I do is to create an individual sheet for each tab. Then I use the following to combine them together.

designerFile = Application.StartupPath & "\State Stats Summary Template.xls"
workbook.Open(designerFile, FileFormatType.Excel97To2003)
workbook.Worksheets(0).Name = workbook.Worksheets(0).Name & "-FY" & Mid(txtFY.Text, 3, 2)

Dim SchoolWorkbook As Workbook = New Workbook
SchoolWorkbook.Open(Application.StartupPath & "\" & strSchoolname & " - State Stats-FY" & Mid(txtFY.Text, 3, 2) & ".xls", FileFormatType.Excel97To2003)
workbook.Worksheets.Add(strSchoolname & "-FY" & Mid(txtFY.Text, 3, 2))
workbook.Worksheets(strSchoolname & "-FY" & Mid(txtFY.Text, 3, 2)).Copy(SchoolWorkbook.Worksheets(0))

workbook.Save(Application.StartupPath & "\State Stats-FY" & Mid(txtFY.Text, 3, 2) & ".xls", FileFormatType.Excel97To2003)

What you get is the resulting file state stats-FY11. I am then combining it together with the FY10.

I hope this is clear.

Thanks Jim

Hi,

Could you try the attached our latest version/fix v5.1.3.x. I have tested your scenario using your sample files and it works fine.



Thank you.

Nope the sheets are still grouped. I have to select one of the tabs and tell it to UNGROUP SHEETS.

Hi,

Well, I use the following sample code to combine your templates (from your previous post) which works fine without your mentioned grouping sheets issue.
Workbook MasterTemplate = new Workbook(“e:\test\combines\State+Stats-FY11.xls”);
Workbook SkeletonTemplate = new Workbook(“e:\test\combines\State+Stats-fy10.xls”);
MasterTemplate.Combine(SkeletonTemplate);
MasterTemplate.Save(“e:\test\combines\outbook1.xls”);

Please create a sample console application, zip it and post it here, to show or reproduce the issue here on our end. We will check your issue soon.

Thank you.

Sorry for not getting back to you sooner about this. I was busy working on another problem. But if you look at the State Stats fy 11, the work sheets are already grouped. Why? I have looked at the code to see as to why it would group the sheets.

Part of the code I use is the following for the individual sheets.

Dim SchoolWorkbook As Workbook = New Workbook

SchoolWorkbook.Open(Application.StartupPath & "\" & strSchoolname & " - State Stats-FY" & Mid(txtFY.Text, 3, 2) & ".xls", FileFormatType.Excel97To2003)

workbook.Worksheets.Add(strSchoolname & "-FY" & Mid(txtFY.Text, 3, 2))

workbook.Worksheets(strSchoolname & "-FY" & Mid(txtFY.Text, 3, 2)).Copy(SchoolWorkbook.Worksheets(0))

It is then saved as the state stats FY 11.

Hi,

I still could not find the issue. I used your template file … F10.xls, copied all the sheets into a new workbook and save as the file, the output file is fine. It has not grouped sheets.

Sample code:

Dim template As New Workbook(“e:\test\State+Stats-fy10.xls”)
Dim workbook As New Workbook()
workbook.Worksheets.Clear()
For Each sheet As Worksheet In template.Worksheets
Dim i As Integer = workbook.Worksheets.Add()
workbook.Worksheets(i).Copy(sheet)
workbook.Worksheets(i).Name = sheet.Name

Next sheet

workbook.Save(“e:\test\myState+Stats-fy11.xls”)

Attached is the output file, please check it.

If you still find the issue, kindly create a sample console application and zip it to post it here to reproduce the issue on our end, so that we may look into your issue soon.


Thank you.

Each sheet in FY 11 is an individual sheet. The firs thing I do is open a summary page as a designer file, then I add each sheet to it that is FY 11. Then I save it as the FY11 state stats workbook. At the end I combine FY11 and FY10 together.

If I have the time I will attempt to generate a program that will do this. Bu you will have to have all the individual sheets for FY11 along with FY10.

I guess the question I need to ask do you know when I say they are grouped together? If you open the FY11 workbook and on the second tab at the bottom go to say A12 and start typing. This places information on all sheets at this place. The only way to stop it is go to the tabs at the bottom and right click and tell it ungroup.

Hi,

Well, I understand, opening your FY11.xls file into MS Excel does produce the issue as you described, but we got to know the steps involved to generate such a file. Therefore, we requested you to create a sample application and post it here, it will help us really to trace and figure out the issue very soon.

Thank you.

Hi,
<!–[if gte mso 9]>
<w:WordDocument>
<w:View>Normal</w:View>
<w:Zoom>0</w:Zoom>
<w:TrackMoves/>
<w:TrackFormatting/>
<w:PunctuationKerning/>
<w:ValidateAgainstSchemas/>
<w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>
<w:IgnoreMixedContent>false</w:IgnoreMixedContent>
<w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>
<w:DoNotPromoteQF/>
<w:LidThemeOther>EN-US</w:LidThemeOther>
<w:LidThemeAsian>X-NONE</w:LidThemeAsian>
<w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript>
<w:Compatibility>
<w:BreakWrappedTables/>
<w:SnapToGridInCell/>
<w:WrapTextWithPunct/>
<w:UseAsianBreakRules/>
<w:DontGrowAutofit/>
<w:SplitPgBreakAndParaMark/>
<w:EnableOpenTypeKerning/>
<w:DontFlipMirrorIndents/>
<w:OverrideTableStyleHps/>
</w:Compatibility>
<w:DoNotOptimizeForBrowser/>
<m:mathPr>
<m:mathFont m:val=“Cambria Math”/>
<m:brkBin m:val=“before”/>
<m:brkBinSub m:val="–"/>
<m:smallFrac m:val=“off”/>
<m:dispDef/>
<m:lMargin m:val=“0”/>
<m:rMargin m:val=“0”/>
<m:defJc m:val=“centerGroup”/>
<m:wrapIndent m:val=“1440”/>
<m:intLim m:val=“subSup”/>
<m:naryLim m:val=“undOvr”/>
</m:mathPr></w:WordDocument>
<![endif]–><!–[if gte mso 10]>

/* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin;}

<![endif]–>
I think we have found your mentioned issue, we need to set license (I was not doing this) before testing your scenario with your file(s).

For your information, if Workbook A combines Workbook B, the active worksheet of B will still be active in Combined A. So, there are two active worksheets, and are grouped worksheets displayed in MS Excel. We could not fix it because we do not know which active worksheet should be not active. Please set Workbook.Worksheets. ActiveSheetIndex property after workbook.Combine method. I should fix your issue.


Thank you.


Ok, guys I got a sample project. No it did not work as you suggested above. In fact the items are being grouped before the combine method. Attached is the sample project with all the stuff. You should be able to restore and run. Add license and run.

Hi,

Thanks for the sample project.

I have logged your issue into our issue tracking system with an id: CELLSNET-20651. We will investigate your issue and get back to you soon.

Thank you.

Hi,

After looking your code segments in your project a bit, you did not do what we suggested in the previous post. Please set the active worksheet after Combine method.
See following code segment (add the bold line):
workbook.Combine(StatsWorkbook)
workbook.worksheets. ActiveSheetIndex =0;// set the active sheet.

It should work fine.

Thank you.

Hi,

I can confirm I had the same behavior after copying sheets and the proposed fix works perfectly.

Many thanks!

Hi,

It’s good to know your issue has been resolved. Please download and use the latest version:
Aspose.Cells
for .NET v7.1.1.1


If you have any other questions, please feel free to post, we will help you asap.