Hi all,

I need create an ASP.NET page that does the following

1)Open an existing Excel file (testBook1.xls)

2)Modify it

3)Then attach it to an Email directly from the stream (without saving).

Everything seems to work fine, but when I receive the email and open the attachment I get a message like "file is in a different format from the extension". When I look at the file I see "Root Entry" and "Workbook" amidst a bunch of nonsense characters.

Here's where I'm at (this code is in an Button_Click event)

Dim license As License = New License

Dim licenseFileName = MapPath("~") & "\Bin\Aspose.Total.lic"

license.SetLicense(licenseFileName)

Dim inputFile = MapPath("~") & "\Aspose Reports\testBook1.xls"

Dim workbook As Workbook = New Workbook

Dim fstream As FileStream = New FileStream(inputFile, FileMode.Open)

workbook.Open(fstream)

Dim worksheet As Worksheet = workbook.Worksheets(0)

Dim message As MailMessage = New MailMessage("test@naimanlaw.com", "bkilpatrick@technijian.com")

message.Subject = "Aspose Test"

message.Body = "message boddy"

message.Attachments.Add(New Attachment(fstream, "emample.xls", "application/ms-excel"))

Dim client As New SmtpClient

client.Send(message)

fstream.Close()

Any ideas?

 

Hi,

 

Thank you for considering Aspose.

 

After opening the workbook, you have to set the stream to its starting position before attaching the file to the email.  Please see the following updated code,

 

Dim license As License = New License

 

Dim licenseFileName = MapPath("~") & "\Bin\Aspose.Total.lic"

 

license.SetLicense(licenseFileName)

 

Dim inputFile = MapPath("~") & "\Aspose Reports\testBook1.xls"

 

Dim workbook As Workbook = New Workbook

 

Dim fstream As FileStream = New FileStream(inputFile, FileMode.Open)

 

workbook.Open(fstream)

 

Dim worksheet As Worksheet = workbook.Worksheets(0)

 

fstream.Seek(0, SeekOrigin.Begin)

 

Dim message As MailMessage = New MailMessage("test@naimanlaw.com", "bkilpatrick@technijian.com")

 

message.Subject = "Aspose Test"

 

message.Body = "message boddy"

 

message.Attachments.Add(New Attachment(fstream, "emample.xls", "application/ms-excel"))

 

Dim client As New SmtpClient

 

client.Send(message)

 

fstream.Close()

 

 

Thank You & Best Regards,

---

Nausherwan Aslam
Support Developer,
Aspose Sialkot Team
Contact Us

Thanks,

That works, except when I try to Modify the file (see added lines in bold). The attachment ends up looking like the original without the modified cell. Any thoughts?

Dim license As License = New License

Dim licenseFileName = MapPath("~") & "\Bin\Aspose.Total.lic"

license.SetLicense(licenseFileName)

Dim inputFile = MapPath("~") & "\Aspose Reports\testBook1.xls"

Dim workbook As Workbook = New Workbook

Dim fstream As FileStream = New FileStream(inputFile, FileMode.Open)

workbook.Open(fstream)

Dim worksheet As Worksheet = workbook.Worksheets(0)

Dim cell As Cell = worksheet.Cells("C4")

cell.PutValue("hello world")

fstream.Seek(0, SeekOrigin.Begin)

Dim message As MailMessage = New MailMessage("test@naimanlaw.com", "bkilpatrick@technijian.com")

message.Subject = "Aspose Test"

message.Body = "message boddy"

message.Attachments.Add(New Attachment(fstream, "emample.xls", "application/ms-excel")) Dim client As New SmtpClient

client.Send(message)

fstream.Close()

 

Hi,

Thank you for considering Aspose.

Well, after making changes to the workbook, you have to save it again before sending as an attachment via email. Please see the updated code for your reference,

 

Dim license As License = New License

 

Dim licenseFileName = MapPath("~") & "\Bin\Aspose.Total.lic"

 

license.SetLicense(licenseFileName)

 

Dim inputFile = MapPath("~") & "\Aspose Reports\testBook1.xls"

 

Dim workbook As Workbook = New Workbook

 

Dim fstream As FileStream = New FileStream(inputFile, FileMode.Open)

 

workbook.Open(fstream)

 

Dim worksheet As Worksheet = workbook.Worksheets(0)

 

Dim cell As Cell = worksheet.Cells("C4")

 

cell.PutValue("hello world")

 

workbook.Save(fstream, FileFormatType.Default)

 

fstream.Seek(0, SeekOrigin.Begin)

 

Dim message As MailMessage = New MailMessage("test@naimanlaw.com", "bkilpatrick@technijian.com")

 

message.Subject = "Aspose Test"

 

message.Body = "message boddy"

 

message.Attachments.Add(New Attachment(fstream, "emample.xls", "text/plain"))

 

Dim client As New SmtpClient

 

client.Send(message)

 

fstream.Close()

 

Thank You & Best Regards,

---

Nausherwan Aslam
Support Developer,
Aspose Sialkot Team
Contact Us

That did the trick. Thank so much for your help.

Okay, so I spoke too soon.

I implemented the solution in your post, but there is a problem.

When I do 

workbook.save(fstream, FileFormatType.Default)

the changes are saved to the original file.

Unfortunately, the whole point of what I'm trying to do is to open an Excel file, add data, then attach to an outgoing email without having to save to disk.  I was hoping I could do all my work in the stream and then attach the stream directly. I don't want to have to write the changes to either my original Excel file or a temporary output file.

Any ideas?

Hi,

Thank you for considering Aspose.

Please save your workbook to a Memory Stream and then sand it as an attachment. Please see the following sample code in this regard,

Dim license As License = New License

 

Dim licenseFileName = MapPath("~") & "\Bin\Aspose.Total.lic"

 

license.SetLicense(licenseFileName)

 

Dim inputFile = MapPath("~") & "\Aspose Reports\testBook1.xls"

 

Dim workbook As Workbook = New Workbook

 

Dim fstream As FileStream = New FileStream(inputFile, FileMode.Open)

 

workbook.Open(fstream)

 

fstream.Close()

 

Dim worksheet As Worksheet = workbook.Worksheets(0)

 

Dim cell As Cell = worksheet.Cells("C4")

 

cell.PutValue("hello world")

 

Dim ms As MemoryStream = New MemoryStream()

 

ms = workbook.SaveToStream()

 

ms.Seek(0, SeekOrigin.Begin)

 

Dim message As MailMessage = New MailMessage("test@naimanlaw.com", "bkilpatrick@technijian.com")

 

message.Subject = "Aspose Test"

 

message.Body = "message boddy"

 

message.Attachments.Add(New Attachment(ms, "emample.xls", "text/plain"))

 

Dim client As New SmtpClient

 

client.Send(message)

Thank You & Best Regards,

---

Nausherwan Aslam
Support Developer,
Aspose Sialkot Team
Contact Us

Thanks, that worked. I really appreaciate all you help.  Unfortunately, we are still having a problem.

 

Here’s the issue we’re having:

We are running large excel reports that have the following form:

                Record 1

                                Note1

                                Note 2

                Record 2

                                Note 3

                                Note 4

                                Note 5

                (etc)

 

There is one Data Reader(objDataReader) that retrieves the main Records. Then, cycling through the returned record set, we use a second Data Reader(objDataReader2) to retrieve the Notes for that Record. The code snippet looks like this:

 

                ‘retrieve main record set

                sSqlStr = "SELECT * FROM Record_Table

      objCommand = New OleDbCommand(sSqlStr, OBJdbConn)

      objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)

               

                ‘read one record at a time

While objDataReader.Read()

      ‘write Record to next excel row

            worksheet.Cells(i, 0).PutValue(Str(objDataReader("FileNo")))

            worksheet.Cells(i, 1).PutValue(objDataReader("Matter"))

            worksheet.Cells(i, 2).PutValue(objDataReader("LoanNumber"))

            worksheet.Cells(i, 3).PutValue(objDataReader("Address"))

            worksheet.Cells(i, 4).PutValue(sdate(objDataReader("Date")))

            worksheet.Cells(i, 5).PutValue(objDataReader("Outsourcer_Name"))

            worksheet.Cells(i, 6).PutValue(objDataReader("Broker_Name"))

            worksheet.Cells(i, 7).PutValue(sdate(objDataReader("OpenDate")))

            i = i + 1

           

            ‘retrieve Notes for the current record

            sSqlStr = "SELECT * FROM Notes_Table WHERE FileNo = " & Trim(objDataReader("FileNo")

            objCommand2 = New OleDbCommand(sSqlStr, OBJdbConn)

            objDataReader2 = objCommand2.ExecuteReader(CommandBehavior.CloseConnection)

           

‘read each Note

            While objDataReader2.Read()

‘write Note to next excel row

                  worksheet.Cells(i, 1).PutValue(sdate(objDataReader2("Date")))

                  worksheet.Cells(i, 2).PutValue(objDataReader2("Note"))

                  i = i + 1

            End While

            objCommand2.Dispose()

            i = i + 1

           

        End While

 

The above is simplified a bit. The main query is actually parameterized and returns different record set depending on user input.  Normally, the Report runs fine … that is until the main Record set grows to about 1050. At that point, the server throws an OUT OF MEMORY Exception. It doesn’t matter how many Notes are retrieved. (I can include “TOP 0” in the Notes query and I still  get the same Exception at about 1050 records) However if I turn off the Notes all together (by commenting out the whole Notes section) the Report runs as many main Records as I need.

 

Two questions:

1)      Do you have any theories about what could be causing this problem? Do you know if there is some issue with either SQL Server or ASPOSE that requires a certain amount contiguous memory?  We’d really like to know what’s causing this issue.

2)      We are considering moving to a 64-bit server to improve performance.  Will ASPOSE work in a 64-bit environment?

 

Any help you could provide would be appreciated

So, we can call off the dogs. I figured out how to fix our OUT OF MEMORY Exception problem. I simply added the line

System.GC.Collect()

right before the last "End While" statement.

However, I'd still like to know if Aspose will work in a 64-bit environment.

Also, can I use my Aspose liscence for multiple websites on different servers?

Thanks again for all you help.

Hi,

Thank you for considering Aspose.

Yes, Aspose.Cells supports 64-bit environment. Please provide further details about your 64-bit environment. Also, you can try the attached latest version of Aspose.Cells for 64-bit environment.

For you license issue, please post your query in our Aspose.Purchase forum where one of our representative will provide you further information about your requirement. Following is the link to Aspose.Purchase Forum,

http://www.aspose.com/community/forums/aspose.purchase/220/showforum.aspx

Thank You & Best Regards,

---

Nausherwan Aslam
Support Developer,
Aspose Sialkot Team
Contact Us

Hi,

brandx:

However, I'd still like to know if Aspose will work in a 64-bit environment.

Yes, Aspose.Cells does support 64-bit environment, check: http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/using-aspose-cells-for-net-on-32bit-and-64bit-platforms.html I have also attached our latest fix Net2.0 compiled version for your need, please try it.

brandx:

Also, can I use my Aspose liscence for multiple websites on different servers?

Please post a query @ Aspose.Purchase forum: http://www.aspose.com/community/forums/aspose.purchase/220/showforum.aspx , one of our sales staff member would help you soon.

 

Thank you.

---

Amjad Sahi
Support Developer,
Aspose Sialkot Team
Contact Us