Formatting a spreadsheet saved to a memory stream

Aspose.Cells Product Family
1

Hope someone can help me with this. I have no problems saving my report to a memory stream and then sending a response to a client but I can't seem to figure out how to format the resulting spreadsheet.

Simplified version of my code is provided below. My question is where in this code will I insert formatting statements for the worksheet and what those statements will be. I was hoping that after I import tblExcel table to cells collection of my worksheet I will be able to loop through rows and columns of that worksheet and assign an appropriate style to a cell but after ImportDataTable command has been executed and I look at cellsRpt.Columns.Count or cellsRpt.Rows.Count properties I get a zero in both cases. cellsRpt.Count, however returns the right number of cells. I can format the content of tblExcel table but thet all my numeric values become strings. If anyone have come into a problem like this or has some insight, please point me in the right direction.

Dim excelObj As New Excel

Dim cellsRpt As Cells = excelObj.Worksheets(0).Cells

< produces table tblExcel >>

cellsRpt.ImportDataTable(tblExcel, True, "A1")

Try

excelObj.Save(memStream, FileFormatType.Default)

Response.ClearHeaders()

Response.Clear()

Response.ContentType = "application/vnd.ms-excel"

Response.AddHeader("Content-Disposition", "inline; filename=report.xls")

Response.BinaryWrite(memStream.ToArray)

Response.End()

2

To loop through cells, you can try:

Dim i As Integer
For i = 0 To tblExcel.Rows.Count- 1

Dim j As Byte
For j = 0 To tblExcel.Columns.Count- 1

cellsRpt(i, j).Style.ForgroundColor = Color.Blue
cellsRpt(i, j).Style.Pattern = BackgroundType.Solid
Next
Next

or

Dim i As Integer
For i = 0 To cellsRpt.MaxDataRow

Dim j As Byte
For j = 0 To cellsRpt.MaxDataColumn

cellsRpt(i, j).Style.ForgroundColor = Color.Blue
cellsRpt(i, j).Style.Pattern = BackgroundType.Solid
Next
Next

"I can format the content of tblExcel table but thet all my numeric values become strings."

Please check the data type of those columns in your data table that converts strings from numberic values.

3

Laurence,

I sorted it out last night. Like you said the problem was the datatypes of the table columns. Thanks for your dedication.