Free Support Forum - aspose.com

Missing picture after copying worksheets

I am using worksheet.copy to transfer between my input file and my
output file. In addition I am making new worksheets containing jpg
images.



If I copy the five worksheets from input.xls, and make one extra
worksheet with the jpg on it, I get a red X on the jpg worksheet,
output1.xls. However if I omit the worksheet called “Monsanto Royalty”
from my worksheet copies, I get what I expect, output2.xls.



What can be the problem? Is there an image list somewhere that is overflowing?


Please try this attached version.

We were using the older Aspose.Excel version. I have switched to to new
dll you sent me and modified my code to use the slightly different
object model. I have an aspose.cells.lic file dated 4/24/2006, not sure
if that works. Anyway the result is that I don’t get any images
inserted anywhere. Do I need a new license file? I attach the output
file I produce.

What do you mean you don’t get any images? I see the image in “SAI Pricing - North and South” worksheet. I think you license file is expired on 4/24/2006 which makes the evaluation warning worksheet showing as the active worksheet. Please shift it to access your expected worksheet.

My apologies, I sent you the wrong file. Since your fix, I can now get
all those worksheet copies plus image copied across OK. However when I
also add in a prices page which is constructed out of text and images
on the fly, then I get the result I was describing. I attach an output
file showing this scenario.

Hi,

There are some errors opening your file.

Could you please elaborate your question and send the sample code with template file here. We will try to find out and resove it ASAP.

Thanks for your patience.

Regards

Amjad Sahi

Aspose Nanjing Team

The current error is exactly that, that the file will not open and when
excel tries to mend it I get a file with all images removed. I attach
the class that contains the code that creates the excel file, but you
will not be able to run it becuase you don’t have my database or web
services or web site. What happens is that the user goes through a
series of web pages picking out what product prices and sidebars he
wants to put into his excel file. Then the attached code builds the
worksheets - for prices, it builds up the page cell by cell for the
products and crops he has picked - for sidebars, it either copies
worksheets from the input workbook (06-07_Price_Cards.xls) or puts
pictures from jpg files onto the new sheet. Then the excel file is
opened in the browser for the user to save and print.



I attach also the results of three tests:

(1) the user picks to see all corn prices and SAI Pricing-North and South, which is a jpg. This works and produces test1.xls.

(2) the user picks to see all corn prices and SAI Pricing-North and
South which is a jpg, and Cash Bean Incentives which is a worksheet in
the input xls file. This test fails (test2.xls) and to continue testing
I have to find the Excel process left hanging in the task manager and
kill it.

(3) the user picks to see SAI Pricing-North and South which is a jpg,
and Cash Bean Incentives which is a worksheet in the input xls file.
This works and produces test3.xls.



It is the evening here, but I will continue to check for your replies
as my client is anxious to get this functionality rolled out to
production.


In your tests, only the test2.xls has problem, right? I tried several ways to reproduce your problem but cannot find it.

Could you please:

1. Post your raw test2.xls here.

2. I saw 4 worksheets in test2.xls.

"CORN PRICING": is it copied from "ECO" worksheet in your template file?

"Market Choices": how do you create this worksheet?What's the content in it?

"Cash Bean Incentives": is it copyied from "Cash Bean Incentives" worksheet in your template file?

"SAI Pricing - North and South": are you just add an image in this worksheet?

Is it possible to create a simple runnable console application to show the problem in test2.xls? I think you can discard all code to access database, web service or web site. The problem may be related to copy worksheet and insert images so you just need to keep those code the reproduct the problem.

I have pulled out the relevant parts of the code into a console
application as you requested. It replicates the problem. I attach the
console application.






Thank you for this sample project. Please try this attached fix.

There is still something not quite right. In my real application, where
I am building the prices pages cell by cell, some with strings and some
with images, I still get the images displaced so the wrong image is in
the wrong position or on the wrong worksheet, when I choose several
products.



I have saved off each price sheet as a separate xls file to try to make
the console application work like the real application, but it does not
behave the same - by copying the pre-existing prices worksheets it
works better. However, if I copy the sheets in the exact order
currently in the attached code, I do not see the Marketing Management
jpg. If I change the order, I do see it. I hope this will give you
something to track down what is going wrong with handling the image
collection.


I fixed the image lost problem but I also make some changes according to your sample project. Please try this attached fix.

Thank you. That does work well with the console application, but still
has a problem in the real application. However, seeing how well teh
console app works, I have changed the code in the real app. As I build
a worksheet cell by cell putting in strings and images, I am saving
that one worksheet off to a temporary xls file. Then I open up the
temporary file and worksheet copy the new worksheet into the main
workbook. Here is the code in case this method may be useful to
another aspose user.



Private Sub CreateCropPrices(ByRef ef As Aspose.Cells.Workbook, ByRef
wsPrices As Aspose.Cells.Worksheet, ByVal sCropSkey As String, ByVal
saCellStrings() As String, ByVal sRegion As String, ByVal sDistrict As
String, ByVal sCrop As String, ByVal iHeaderSkey As Integer, ByVal
sUser As String, ByVal sUserName As String, ByVal sSizes As String,
ByRef bFirstSheet As Boolean, ByRef iNextRow As Integer, ByVal
bMultipleCropPerPage As Boolean)

’ copy sheet to workaround image problem with aspose

Dim efCrop As Workbook = New Workbook

Dim bFirstCrop As Boolean = True

’ build the price sheet in the new workbook

CreatePriceSheet(efCrop, sCropSkey, saCellStrings, sRegion, sDistrict,
sCrop, iHeaderSkey, sUser, sUserName, sSizes, bFirstCrop)

Dim sFileName As String = CreateFileName(“tp”) & “.xls”

Dim
sPathFile As String = Request.PhysicalApplicationPath &
“OnlineAccess\PPL\PDF\output” & sFileName

’ save the new workbook to a temporary file

efCrop.Save(sPathFile, FileFormatType.Excel2000)

’ copy from the temporary file to the main workbook

CopyCropSheet(ef, sPathFile, wsPrices, sUserName, bFirstSheet)

efCrop = Nothing

End If

End Sub



Private Sub CopyCropSheet(ByRef ef As
Aspose.Cells.Workbook, ByVal sPathFile As String, ByRef wsOut As
Worksheet, ByVal sUserName As String, ByRef bFirstSheet As Boolean)

Dim wsIn As Worksheet

Dim efCrop As New Workbook



Try

’ open the tmeporary file

efCrop.Open(sPathFile, FileFormatType.Excel2000)

’ get the only worksheet

wsIn = efCrop.Worksheets(0)

’ add a new worksheet to the main workbook

AddSheet(sUserName, ef, wsOut, wsIn.Name, True, bFirstSheet)


copy the worksheet from the temporary file into the main workbook

wsOut.Copy(wsIn)

’ delete the temporary file

File.Delete(sPathFile)

Catch ex As Exception

Response.Write(“Unable to copy crop sheet - Error Message
follows:

” & ex.Message & “

”)

Throw (ex)

End Try

End Sub



Thank you for your help. I’m sorry not to have time to mimic the
building of the price worksheet cell by cell in the console
application, so that you could have tracked down the final problem.


Could you please post your code in CreatePriceSheet(efCrop, sCropSkey, saCellStrings, sRegion, sDistrict, sCrop, iHeaderSkey, sUser, sUserName, sSizes, bFirstCrop) method? I want to see how you create images in this worksheet. Thank you very much.

As one method calls other methods, I have attached the whole vb file for you.