Copying sheet with a designer file

Hi,

I work with Tim Trahan at Hilton Hotels Corp.

I have a designer file containing several different worksheets that I am using to create a report for any number of hotels. I will need a clean copy of one of the sheets for each hotel, however I will not know before runtime how many hotels will be in the report.

Is there a way to copy a sheet within an Excel file and add it to that file. I am able to add a blank sheet using the Worksheets.add() method, but this will not suffice, as I need the formatting from the designer sheet.

Thanks.

~Tyler Fiveash

Dear Tyler,

laurence told me that it’s a very challenging task to implement the combination of spreadsheets. I believe it’s the same to copy one worksheet from a spreadsheet to another. I don’t think it’s impossible, however, it does take a good deal of time. Thanks for understanding.

Based on our current schedule, this feature can be made available after October. So, the question is, how urgent is your requirement? If you can wait for it until October that will be great for us.

Otherwise, could you elborate your specific needs? Maybe we can think out an alternative solution in the meantime. For example, if the to-be-copied worksheet is simple enough so that you can use existing APIs to create the same copy of it, why not implement a Worksheet.Copy by yourself?

We would need to think of every possibility in the worksheet, so a specific Worksheet.Copy must be simpler than a general Worksheet.Copy. Do you agree?

I will need this capability within the next week or two, but I will try to implement the Worksheet.Copy on my own.

One thing I have found, however, is that Aspose.Excel strips away any predefined print area from my designer file. That is, I have defined a print area for the sheets in my designer file so that only the pertinent charts and tables will print and not the raw data. When I open the designer file, put my data in it, and save it, the print area is no longer there, and the entire sheet - data and all - prints.


I will also need this capability rather quickly. I appreciate any help or advice you can give me with this.

~Tyler

Dear Tyler,

I have tested the print area problem. I did the following:
1. Create a designer file contained pictures and charts
2. Set print area on the pictures and charts
3. User Aspose.Excel to open the designer file
4. Put some new data and save it

The result is OK. The print area is still there.

Could you email me your designer file and your sample code? So I can investigate it.

Thanks

Hi.

I’ve found what was causing my designer file to lose the print area I had defined.

For several of the worksheets I had set them to print to x pages wide by y pages tall under page setup. I changed this to make the sheets print out at a percentage of their size, and it has fixed the problem.

~Tyler

Dear Tyler,

Please download the Fix 1.5.5.0. It solves the print area problem even when you set them to print to x pages wide by y pages tall under page setup.

I am trying to implement my own copy procedures as well. Using the range import and export functions, I can easily move my template into a new worksheet. My next process was to move cell by cell within the range and copy the style; however, the Style of all cells in the designer worksheet are null. Strangely, when the worksheet is rendered, the formatting appears to be applied. Are style objects parsed correctly by the designer?

CD

Hi.

The styles of designer worksheet can be only accessed by Aspose.Excel, in order to maintain simplicity and avoid mis-operation.

I am thinking about adding a Copy method in the Cell object. Thus you can copy the data and style of a single cell to another cell. Will this method meet your requirements?

Yes, it would. I would implement Cell.Copy as well as Range.Copy and thus Worksheet.Copy. I could forsee copyspecial (similar to paste special…) but keeping with small iterations, cell.copy would be the best. Then creating Range.Copy and Worksheet.Copy are simple loops to write.

CD

Please download Fix 1.5.6.



Cell.Copy:

///


/// Copys data from a source cell.
///

public void Copy(Cell cell)

@tfiveash,
We recommend you to try the following sample code with the latest version:

private static string fileName ="CopyWorksheets.xlsx";
Workbook newWorkbook = new Workbook();
Worksheet worksheet = newWorkbook.Worksheets.Add("New Sheet");
Cells cells = worksheet.Cells;
cells[0, 0].PutValue("Some Text");
Worksheet worksheet2 = newWorkbook.Worksheets.Add("MySheet");
worksheet2.Copy(worksheet);
newWorkbook.Save(fileName);

For more details have a look at the following article:
Copying and Moving Worksheets

Download the latest version of Aspose.Cells for .NET from the following link:
Aspose.Cells for .NET (Latest Version)

You can download the latest demos here.