Copy a sheet from one excel to another excel file

Aspose.Cells Product Family
21

@t1jsw

Just move your ExcelCopy.xlsx in some other folder and you will see ExcelCopy.xlsx formulas will also break. But if you keep ExcelCopy.xlsx and Original.xlsx in same folder, everything will work fine.

22

Thanks for you help. It wasn’t that I moved it. It was that rather doing the initial save into the same directory, the initial save was into a different directory. That worked with excel but not with Aspose. However, I have worked out that I need to set the filename to the full file name including path and then it works.

Thanks again,

Julie

23

@t1jsw

Thanks for your feedback and using Aspose APIs.

You are right. When we copy Excel file to some other location using Microsoft Excel, issue does not occur, but if we copy Excel file to some other location using copy/paste, then issue does occur. Setting the full path fixes this issue.

24

Hi Again,

I am still trying to get the VLOOKUP stuff to work properly with Aspose. Now the sheets are copying with correct reference back to original sheet. However if I change a value and recalculate using Aspose the VLOOKUP does not calculate correctly. It resets all the values to #NA and it is not until you open the saved file in excel and enable content that it does the correct recalculation via excel that it puts them all back and fixes the changed one. I tried recalculating again after save but it made no difference.

What do I need to do to make the CalculateFormula calculate VLOOKUP correctly when the lookup references another workbook?

Thanks, Julie

I have the following code:

'Use file stream to open workbook so it can be opened as ReadOnly
Dim loStrm As New System.IO.FileStream("C:\Temp\Original.xlsx", IO.FileMode.Open, IO.FileAccess.Read, IO.FileShare.ReadWrite)
Dim loOrigWB As New Aspose.Cells.Workbook(loStrm)
loStrm.Close()
loOrigWB.FileName = "C:\Temp\Original.xlsx"

Dim loNewWB As New Aspose.Cells.Workbook(Aspose.Cells.FileFormatType.Xlsx)
loNewWB.Worksheets("Sheet1").Copy(loOrigWB.Worksheets("Sheet1"))
loNewWB.Worksheets("Sheet1").Cells(2, 0).Value = "CASH"
Dim loOpt As New Aspose.Cells.CalculationOptions
loNewWB.CalculateFormula(loOpt)
loNewWB.Save("C:\Temp\DiffFolder\AsposeCopy.xlsx")

SampleFiles.zip (13.3 KB)
Opening in Excel.jpg (62.4 KB)
After enabling content.jpg (51.2 KB)

25

@t1jsw

We were able to observe this issue and logged it in our database for investigation and for a fix. Once, the issue is resolved or we have some other news for you, we will update you asap.

This issue has been logged as

  • CELLSNET-45711 - Unable to calculate VLOOKUP correctly when the lookup references another workbook
26

@t1jsw

You should update the external reference data source before calculation.

Code Example - C#

loNewWB.UpdateLinkedDataSource(new Workbook[] { loOrigWB });

27

Thank you so much. I now have it working as expected :slight_smile:

28

@t1jsw

Thanks for your feedback and using Aspose.Cells.

It is good to know that your issue is sorted out now. Let us know if you encounter any other issue, we will be glad to look into it and help you further.

29

Hello guys,

actually i have an issue, i am trying to copy only value from one excel sheet and not formula and trying to paste that value to another excel sheet.

and i am using “copyValue & copy” function and both are giving me value along with formula from one sheet of excel to another excel sheet.

So guys can you please help me out with this or give me some documentation link referring which i can solve this issue.

30

@asposeLegit,

Could you please share your current sample code, we will check and fix your code to cope with it.

31

Below is my sample code please check and guide me

#workbook input file path
wb = api.Workbook(r"input file path")



# getting worksheet
ws = wb.getWorksheets().get(0)

# getting cell reference of current worksheet
cells = ws.getCells()

# Creating range in which data hv to copy
range1 = cells.createRange("A1:D4")
range2 = cells.createRange("A1:D4")

# now copying only value from excel sheet

# I have used copy and copyValue function one after another not in one code just to show you i have entered both(copy & copyValue) function one after another.

range2.copyValue(range1)  # i have used copyValue function to copy value but this also give me value along with the formula
range2.copy(range1) # I have used copy function also to copy value but this also give me value along with the formula

# save the Excel file
wb.save("output file location")
32

@asposeLegit,

Thanks for further details and sample.

You may try to to use Cells.removeFormulas() method for your source worksheet in the source workbook before copying it to destination workbook. This will replace underlying formulas with calculated values in the cells for your needs. You may add a line to your code segment if it works for your needs:

#workbook input file path
wb = api.Workbook(r"input file path")

getting worksheet
ws = wb.getWorksheets().get(0)

getting cell reference of current worksheet
cells = ws.getCells()

cells.removeFormulas()

Creating range in which data hv to copy
range1 = cells.createRange(“A1:D4”)
range2 = cells.createRange(“A1:D4”)
…

Let us know if you still find the issue.

33

yes i did as you have said, but problem is i am getting error for createRange after this, It is showing createRange attribute error

34

@asposeLegit,

range1 = cells.createRange(“A1:D4”)
range2 = cells.createRange(“A1:D4”)

Seeing your lines of code, it looks you are overwriting or copy/paste the same range upon it. So, you may simply use cells.removeFormulas() and do not need to create or copy/paste ranges. The above lines are not needed.

If in any case, you need to replace formulas with calculated values for A1:D4 range of cells, you may simply re-insert value for those cells via, e.g., cell.putValue(cell.getStringValue(), true) (you may try using some loops through those cells). First you may call Workbook.calculateFormula() obviously to calculate all the formulas.

1 Like
35

Thank you so much @Amjad_Sahi your advice worked perfectly fine

36

@asposeLegit,

Good to know that your issue is sorted out by following the suggestion. Feel free to write us back if you have further queries or issue, we will be happy to assist you soon.

1 Like
37

Sorry to disturb you again, but i am not able to find function in asposecells documentation to get the list of name of the sheet from the single workbook so help me with that too.
Thank you

38

@asposeLegit,

See the following sample code for your needs for your reference:
e.g.
Sample code:

String filename = "f:\\files\\Book1.xlsx";
Workbook book = new Workbook(filename);

System.out.println("Total no. of sheets: " + book.getWorksheets().getCount());

//browse the worksheets in the workbook and get each sheet's name
for (Object obj: book.getWorksheets()) 
{
     Worksheet sheet = (Worksheet) obj;
     System.out.println("Sheet: " + sheet.getName());
}

Hope, this helps a bit.

39

Thanks for the reply @Amjad_Sahi but ur advice is not working is their any other way ?

40

@asposeLegit,

Please elaborate your requirements a bit. Also, share what’s wrong with the suggested code segment. Did you find error using the sample code, please share your sample code with details? We will check it soon.