Errors when creating workbook from external worksheets

Aspose.Cells Product Family
1

Background:
I am trying to create a workbook from a set of templates. Each template will have one or more worksheets. At run time, depending on the user, I will copy the template worksheet into the workbook, fill in the data, and send it out.


The symptons I get are interesting (but wrong!). The first 2 sheets come across OK. The 3rd sheet and subsequent, all have the same word repeating in all cells.

I have tried to save the workbook in between adding new sheets, but this did not help.

The sheets I am importing are fairly complex and have formulas in the first 10 lines or so.

I have written a routine to do the sheet copying. Here is the code for that:

Public Function CreateNewSheetFromTemplate(ByVal oExcel As Aspose.Excel.Excel, _

ByVal ExternalWorkbook As String, _

ByVal WorkSheetName As String, _

ByVal NewWorkSheetName As String) As Aspose.Excel.Worksheet

Dim oSheet As Aspose.Excel.Worksheet

sheetNumber = oExcel.Worksheets.Add()

oSheet = oExcel.Worksheets(sheetNumber)

oExternalExcel = New Aspose.Excel.Excel

Call SetLicense()

Try

oExternalExcel.Open(ExternalWorkbook)

Catch ex As Exception

Throw New Exception("Could not open external worksheet " & WorkSheetName & " in workbook " & ExternalWorkbook & vbNewLine & ex.Message)

End Try

Dim oExtWs As Aspose.Excel.Worksheet

Dim bFoundSheet As Boolean = False

For Each oExtWs In oExternalExcel.Worksheets

If oExtWs.Name.ToUpper() = Trim(WorkSheetName).ToUpper() Then

bFoundSheet = True

Exit For

End If

Next

If bFoundSheet = False Then

Throw New Exception("Could not find sheeet " & WorkSheetName & " in workbook " & ExternalWorkbook & vbNewLine)

End If

oSheet.Copy(oExtWs)

oSheet.Name = NewWorkSheetName

oExtWs = Nothing

oExternalExcel = Nothing

Return oSheet

End Function


This is an important feature we need to support in the application.


Mitch Stephens
Janis Group

2

Hi Mitch,

Have you tried the latest version at

If the problem still occurs in the new version, please send me your templates. Thank you very much.

3

Lawrence,
that did it! The worksheets are being created correctly now with the latest build.

PS: We love this product!

Mitch

4

@JgiMitch,
Aspose.Excel is discarded and no more available now. It is replaced by Aspose.Cells that has much advanced features as compared to its predecessor. You can copy/move within and between multiple worksheets using Aspose.Cells. Here is an example that demonstrates the feature of copying worksheets from multiple external workbooks.

for(int i = 0; i < 10; i++)
{
    Workbook workbook = new Workbook();
    workbook.Worksheets[0].Name = "Sheet " + (i + 1);
    workbook.Worksheets[0].Cells["A1"].Value = i + 1;
    workbook.Save("Book" + i + 1 + ".xlsx");
}
Workbook workbookCombined = new Workbook();
workbookCombined.Worksheets.Clear();
for (int i = 0; i < 10; i++)
{
    Workbook source = new Workbook("Book" + i + 1 + ".xlsx");
    int index =  workbookCombined.Worksheets.Add();
    workbookCombined.Worksheets[index].Copy(source.Worksheets[0]);
    source.Dispose();

    File.Delete("Book" + i + 1 + ".xlsx");
}
workbookCombined.Save("output.xlsx");

For more information about copying and moving worksheets, refer to the following article:
Copying and Moving Worksheets

Get a free trial version of this product here:
Aspose.Cells for .NET (Latest Version)

A runnable solution can be downloaded here to test different features of this product.