Spreadsheet with no rows

Aspose.Cells Product Family
1

I’m new to using Aspose. I use ASP.NET with VB and SQL Server. I call a stored procedure that returns a result set to a datatable. I am looping through the data to have similar output like the Northwind Products By Category. I am currently getting a blank spreadsheet and I have 6 rows in my datatable. If I use PutValue do I also need to call ImportDataTable?

2

If you use PutValue method and loop through your data, you don’t need to call ImportDataTable. Could you post your whole code here? I will check it.

3

Sub CreateAdCosts(ByVal ACExcel As Excel)

Dim dt As DataTable = New DataTable()
Dim con As SqlConnection
Dim cmd As New SqlCommand
Dim da As SqlDataAdapter
Dim ds As New DataSet()
Dim wsTest as Worksheet = ACExcel.Worksheets(0)
Dim ACCells As cells

con = New SqlConnection(ConfigurationSettings.AppSettings("DatabaseConnectionString"))
con.Open()
cmd.Connection = con
cmd.CommandText = ScmsLinkedServer & ".NFIDATA.dbo.P_NFI_DSR_GET_REGION_COSTS"
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add(New SqlParameter("@ps_mainproc", SqlDbType.Text))
cmd.Parameters("@ps_mainproc").Value = "P_NFI_DSR_GET_REGION_COSTS"

Try
da = New SqlDataAdapter(cmd)
Catch se As SqlException
Dim i As Integer
For i = 0 To se.Errors.Count - 1
Trace.Write("SqlException",se.Errors(i).ToString())
Next i
dgMsgs.DataSource = se.Errors
dgMsgs.DataBind()
Catch re As Exception
Trace.Write("Exception",re.ToString())
Finally
da.Fill(ds)
dt = ds.Tables(0)
End Try

wsTest.Name = "Advertising Costs"
ACCells = wsTest.Cells

Dim CurrentRow As Integer = 4
Dim CurrentColumn As Byte = 0
Dim LastRegion As String = ""
Dim CurrentRegion As String, NextRegion As String
Dim RecordCount As Integer = 0

SetAdCostsStyles(ACExcel)

Dim intStart As Integer
For intStart = 0 To dt.Rows.Count - 1

CurrentRegion = CType(dt.Rows(intStart)("RegionDesc"), String)

If CurrentRegion <> LastRegion Then
CurrentRow = 4

If intStart <> 0 Then
CurrentColumn += 4 'move 4 over
End If

CreateAdCostsHeader(ACExcel, ACCells, CurrentRow, CurrentColumn, CurrentRegion)

LastRegion = CurrentRegion
CurrentRow += 2 'start after header
End If

ACCells(CurrentRow, CurrentColumn).PutValue(CType(dt.Rows(intStart)("CostCenter"), String))
ACCells(CurrentRow, CType((CurrentColumn + 1), Byte)).PutValue(CType(dt.Rows(intStart)("Cost"), Short))

If intStart <> dt.Rows.Count - 1 Then
NextRegion = CType(dt.Rows(intStart + 1)("RegionDesc"), String)

If CurrentRegion <> NextRegion Then
Dim style As Aspose.Excel.Style = ACExcel.Styles("TotalLabelStyle")

ACCells(CurrentRow + 1, CurrentColumn).PutValue("Week Total:")
ACCells(CurrentRow + 1, CurrentColumn).Style = style

style = ACExcel.Styles("TotalValueStyle")
ACCells(CurrentRow + 1, CType((CurrentColumn + 1), Byte)).PutValue(RecordCount + 1)
ACCells(CurrentRow + 1, CType((CurrentColumn + 1), Byte)).Style = style
CurrentRow = CurrentRow + 1
RecordCount = 0
Else
RecordCount = RecordCount + 1
End If
Else
Dim style As Aspose.Excel.Style = ACExcel.Styles("TotalLabelStyle")

ACCells(CurrentRow + 1, CurrentColumn).PutValue("Week Total:")
ACCells(CurrentRow + 1, CurrentColumn).Style = style

style = ACExcel.Styles("TotalValueStyle")
ACCells(CurrentRow + 1, CType((CurrentColumn + 1), Byte)).PutValue(RecordCount + 1)
ACCells(CurrentRow + 1, CType((CurrentColumn + 1), Byte)).Style = style
End If

CurrentRow = CurrentRow + 1
Next

ACExcel.Save("testStephAspose.xls", SaveType.OpenInExcel, FileFormatType.Default, Response)

ds.Dispose()
ds = Nothing
da.Dispose()
da = Nothing
cmd.Dispose()
cmd = Nothing
con.Close()
con = Nothing


End Sub

4

I forgot to mention that it saves the file with 0KB, but when I try to open it it says "Unable to read file".

Thanks!

5

Ok. I found out what my problem was. Stick out tongue [:P] I was calling my Sub from my Submit_Click. Not good. It needed to be run before postback. So in my PageLoad I added an If statement to check for IsPostBack. So now I'm going to uncomment my code....and try again to get ALL the data.

Thanks!