Using Worksheet.Copy to copy between workbooks

I need to copy a formatted worksheet from a workbook into another workbook.



If I do in in an Excel macro like this:

Windows(“source.xls”).Activate

Sheets(“sheet1”).Copy Before:=Workbooks(“destination.xls”).Sheets(1)

it works as I expect and the new worksheet looks exactly like the original.



If I do it in VB.NET using Aspose like this (where wsOut is a new worksheet I just created in my destination.xls):

Dim efIn As New Excel

Dim wsIn As Worksheet



efIn.Open(Request.PhysicalApplicationPath & “source.xls”, FileFormatType.Excel2003)

wsIn = efIn.Worksheets(sWorksheet)

wsOut.Copy(wsIn)

it copies the worksheet but the column widths look different. If I loop
through the column widths it thinks they are the same, but it doesn’t
look right.



The sheet I am copying is protected, and the new sheet also gets the same protection.






Could ypu please post your source workbook here? I will check it ASAP.

Here is the source file.

And here is the destination file with the worksheet copied by Aspose.

Please try this attached fix with following sample code:

Workbook wb1 = new Workbook();
wb1.Open("d:\\test\\source.xls");

Workbook wb2 = new Workbook();
wb2.Worksheets[0].Copy(wb1.Worksheets[0]);

for(int i = 0; i < 5; i ++)
{
Console.WriteLine("{0},{1}", wb1.Worksheets[0].Cells.GetColumnWidth(i),
wb2.Worksheets[0].Cells.GetColumnWidth(i));
}

wb2.Save("d:\\test\\abc.xls");

However, the column width are still different. This is caused by different default font.

You are right, something in the original workbook had a style with font
Arial 14. Do you know what I should query in the code in wb1 to
extract that factor?

You can try:

Workbook wb1 = new Workbook();
wb1.Open("d:\\test\\source.xls");

Workbook wb2 = new Workbook();

Style defaultStyle = wb2.DefaultStyle;

defaultStyle.Copy(wb1.DefaultStyle);

wb2.DefaultStyle = defaultStyle;
wb2.Worksheets[0].Copy(wb1.Worksheets[0]);

for(int i = 0; i < 5; i ++)
{
Console.WriteLine("{0},{1}", wb1.Worksheets[0].Cells.GetColumnWidth(i),
wb2.Worksheets[0].Cells.GetColumnWidth(i));
}

wb2.Save("d:\\test\\abc.xls");

That works nicely BUT I have already added constructed worksheets to
the workbook and changing the default font also changes them so instead
my solution is to get the ratio of the source and destination default
style font sizes, and multiply up the column widths after the worksheet
copy. I am adding .5 to the new column width to round up - I know I
really want .5 of the source font character size, but it comes out
close enough as is.



efIn.Open(sXLS, FileFormatType.Excel2000)

wsIn = efIn.Worksheets(sWorksheet)

Dim defaultStyleOut As Style = ef.DefaultStyle

Dim defaultStyleIn As Style = efIn.DefaultStyle

Dim
fAdjustCol As Decimal = defaultStyleIn.Font.Size /
defaultStyleOut.Font.Size

wsOut.Copy(wsIn)

For Each col As Column In wsOut.Cells.Columns

’ multiply column width by difference in default fonts and round up

wsOut.Cells.SetColumnWidth(col.Index,
(wsOut.Cells.GetColumnWidth(col.Index) * fAdjustCol) + 0.5)

Next



Thanks for your help!




Hello,

I am having problem while merging two or more worksheets in a empty workbook. Please check the following code:

--------------------------------------------------
//Open a Workbook.
Workbook yearly = new Workbook();
yearly.Open(@"C:\A.xls");

//Open another Workbook.
Workbook monthly = new Workbook();
monthly.Open(@"C:\B.xls");


//Open another Workbook merge the two.
Workbook main = new Workbook();

//Merging
main.Worksheets.Add("Usage Year to Date");
main.Worksheets["Usage Year to Date"].Copy(yearly.Worksheets[0]);

main.Worksheets.Add("Monthly Usage Trend");
main.Worksheets["Monthly Usage Trend"].Copy(monthly.Worksheets[0]);

//Save the file.
main.Worksheets.ActiveSheetIndex = 0;
main.Save(@"C:\Admin.xls", FileFormatType.Default);

-------------------------------------------------------------------------------------------

I attached the generated file. When I open the report it shows the two worksheets are grouped, so I can't select the chart in the first worksheet unless I click right and select "Ungroup Sheets" or go to another sheet and then comeback. As I cannot expect from my end user to ungroup sheets like this, so this is a critical issue for us, user will think it is not an Excel chart, just an image (because it is not selectable for the moment). Please help me on this issue.

Thanks,
Zerin.
								</div>
								</div>

Hi Zerin,

Kindly post your template “A.xls” and “B.xls” files
here, we want to conduct a test for your issue. And which version you are using?

Thank you.