Data not filling in spreadsheet cells

Aspose.Cells Product Family
1

Hi, I'm trying to test to see how well Excel (old version) to Cells work. This line of code below worked well under Excel but when I try using Cells, nothing gets populated in the spreadsheet cells

If Not IsDBNull(MyReader("MTDSTAT")) Then Excel.Worksheets(0).Cells(CellRow2, CellCol).PutValue(Val(MyReader("MTDSTAT")))

There are values returning from the Store procedure:

{Aspose.Cells.Cell [ N37; ValueType : IsNumeric; Value : 1155 ]}

The spreadsheet pops up but it's blank and gives no error messages.

2

Hi,

Thanks for your posting and using Aspose.Cells for .NET

Please download and use the latest version:
Aspose.Cells
for .NET v7.2.0.4
and let us know your feedback.

If the problem still occurs, then please provide us your runnable simpler sample code replicating this issue.

We will look into it and if it is a bug, we will log it.

3

mshakeel.faiz:
Hi,

Thanks for your posting and using Aspose.Cells for .NET

Please download and use the latest version: Aspose.Cells for .NET v7.2.0.4 and let us know your feedback.

If the problem still occurs, then please provide us your runnable simpler sample code replicating this issue.

We will look into it and if it is a bug, we will log it.

Forgot to mention Aspose version, yes I'm using the latest version

4

Hi,

I will require your complete project replicating this issue without database dependency. If you have any database values, then replace them with hard-coded sample test values.

Anyway, I have written the following code to test your issue with a similar code, as you can see, it is working fine.

Please see the output.xlsx generated by this code.

VB.NET


Dim MyReader As New Hashtable

MyReader.Add(“MTDSTAT”, 115)



Dim Excel As New Workbook


Dim CellRow2 As Integer = 2

Dim CellCol As Integer = 2



Excel.Worksheets(0).Cells(CellRow2, CellCol).PutValue(Val(MyReader(“MTDSTAT”)))


Excel.Save(“output.xlsx”, Aspose.Cells.SaveFormat.Xlsx)

5

Thanks for testing that line of code. Let me double check and make sure I’m not missing anything.

6

Ok I put Excel.Save("output.xlsx", Aspose.Cells.SaveFormat.Xlsx) that thatmshakeel.faiz had in your code and it saves fine so verifying it saves correctly using that line of code, I went back into my application to figure out how my application save and found this.

This is the original code:

ReportName = ReportName +".xls"

excel.Save(ReportName,FileFormatType.Default,SaveType.OpenInExcel,Me.Response)'this is the old excel aspose.

Now I try implementing the new Aspose.Cells dll

Dim wb As Aspose.Cells.Workbook = New Aspose.Cells.Workbook

wb.Save(ReportName, FileFormatType.Default, SaveType.OpenInExcel, Me.Response)

but I get an error message:

Error 16 Overload resolution failed because no accessible 'Save' can be called without a narrowing conversion:
'Public Sub Save(fileName As String, saveType As Aspose.Cells.SaveType, fileFormatType As Aspose.Cells.FileFormatType, response As System.Web.HttpResponse)': Argument matching parameter 'saveType' narrows from 'Aspose.Excel.FileFormatType' to 'Aspose.Cells.SaveType'.
'Public Sub Save(fileName As String, saveType As Aspose.Cells.SaveType, fileFormatType As Aspose.Cells.FileFormatType, response As System.Web.HttpResponse)': Argument matching parameter 'fileFormatType' narrows from 'Aspose.Excel.SaveType' to 'Aspose.Cells.FileFormatType'.
'Public Sub Save(fileName As String, fileFormatType As Aspose.Cells.FileFormatType, saveType As Aspose.Cells.SaveType, response As System.Web.HttpResponse)': Argument matching parameter 'fileFormatType' narrows from 'Aspose.Excel.FileFormatType' to 'Aspose.Cells.FileFormatType'.
'Public Sub Save(fileName As String, fileFormatType As Aspose.Cells.FileFormatType, saveType As Aspose.Cells.SaveType, response As System.Web.HttpResponse)': Argument matching parameter 'saveType' narrows from 'Aspose.Excel.SaveType' to 'Aspose.Cells.SaveType'. C:\VBtest\programming\PRACTICEProgramming\INDIVIDUALprograms\webmis\ReportView.aspx.vb 281 17 C:\...\webmis\

7

Hi,

Please use the following code if you want to save your workbook in xls/xlsx format inside a response stream.

VB.NET


'Save file and send to client browser using selected format

If yourFileFormat = “XLS” Then

workbook.Save(HttpContext.Current.Response, “output.xls”, ContentDisposition.Attachment, New XlsSaveOptions(SaveFormat.Excel97To2003))

Else

workbook.Save(HttpContext.Current.Response, “output.xlsx”, ContentDisposition.Attachment, New OoxmlSaveOptions(SaveFormat.Xlsx))

End If


HttpContext.Current.Response.End()


8

Thanks that works, I appreciate your reply.

9

One more question, there's a preformatted spreadsheet that we use and

string filepath = c:\spread.xls

excel.Open(filepath)

does not work.

I tried

Dim wb As Aspose.Cells.Workbook = New Aspose.Cells.Workbook

wb.Open(filepath)

but wb.Open(filepath) does not exist. What was it replaced by?

10

Hi,

Workbook.Open() method has now been discarded.

Now you will use Workbook constructor instead.

So your code will look like this.

VB.NET


Dim wb As Aspose.Cells.Workbook = New Aspose.Cells.Workbook(filepath)

11

Ok I have been trying to get this working but with little success:

Dim wbPath As Aspose.Cells.Workbook = New Aspose.Cells.Workbook(filepath)

Try

'If filepath <> "" Then excel.Open(filepath) 'old excel.Open method not in use anymore

If filepath <> "" Then wbPath() 'tried using the workbook constructor: Error 14 Expression is not a method.

Select Case ReportID

Case 1000, 1170, 1370

Case 1010

End Select

I get the an error message:

Error 14 Expression is not a method.

Where wbPath is after the Then

12

Hi,

Your code should be like this.

VB.NET


'Here you will only declare reference, you will instantiate it

'only when filepath is not empty, please see below.

Dim wbPath As Aspose.Cells.Workbook


Try


If filepath <> “” Then 'Here you are checking if file path is empty or not.


wbPath = New Aspose.Cells.Workbook(filepath) 'now when file path is not empty, use workbook constructor to instantiate it


Select Case ReportID


Case 1000, 1170, 1370


Case 1010


End Select

13

Ok I did managed to declare it correctly at one point but thought I didn't do it correctly because I can't manage to load the premade Spreadsheet (SS).

1. The data does populate in the SS fine (just a blank template as the style) using aspose.cells.

2. Using the old excel.aspose dll works and the premade SS is populated with the data but using the new aspose.cells don't

string filepath = c:\spread.xls

Dim wb As Aspose.Cells.Workbook = New Aspose.Cells.Workbook()

Dim wbPath As Aspose.Cells.Workbook

Try

If filepath <> "" Then wbPath = New Aspose.Cells.Workbook(filepath)

Select Case ReportID

Case 1000, 1170, 1370

End Select

ReportName = testSpreadSheet + ".xls"

wb.Save(HttpContext.Current.Response, ReportName, Aspose.Cells.ContentDisposition.Attachment, New Aspose.Cells.XlsSaveOption(Aspose.Cells.SaveFormat.Excel97To2003))

14

Hi,

Actually, the workbook which you are sending to response stream is empty, your correct code will be like this.

This code will send your premade Spreadsheet (SS) to response stream.

VB.NET


string filepath = c:\spread.xls


Dim wbPath As Aspose.Cells.Workbook


Try


If filepath <> “” Then wbPath = New Aspose.Cells.Workbook(filepath)


Select Case ReportID


Case 1000, 1170, 1370


End Select


ReportName = testSpreadSheet + “.xls”


wbPath.Save(HttpContext.Current.Response, ReportName, Aspose.Cells.ContentDisposition.Attachment, New Aspose.Cells.XlsSaveOption(Aspose.Cells.SaveFormat.Excel97To2003))





15

Ok that did it. The premade SS shows and the data is in there. Thanks!

16

Hi,

Thanks for your input.

It’s good to know that you were able to resolve this issue with the above given code.

In case, you still face any other issue relating to Aspose.Cells, please feel free to post on our forums.

We will be glad to help you asap.