How to save a spreadsheet

[LACDA-IT]

I have a web application which needs to create a spreadsheet and save it to disk. Here's what I have so far:

Imports Telerik.Web.Spreadsheet

Dim wb As New Workbook
Dim ws As New Worksheet
Dim r As Row

wb.Sheets = New List(Of Worksheet) From {ws}
ws.Rows = New List(Of Row)

r = New Row With {.Cells = New List(Of Cell)}
r.Cells.Add(New Cell With {.Value = "Spreadsheet Title", .FontSize = Convert.ToInt16(Unit.Point(12).Value)})
ws.Rows.Add(r)

r = New Row With {.Cells = New List(Of Cell)}
r.Cells.Add(New Cell With {.Value = Today.ToShortDateString, .TextAlign = TextAlign.Left})
ws.Rows.Add(r)

r = New Row With {.Cells = New List(Of Cell)}
r.Cells.Add(New Cell With {.Value = "Column Header"})

<more column headers>

ws.Rows.Add(r)

For Each dr As DataRow In ds.Tables(0).Rows
     r = New Row With {.Cells = New List(Of Cell)}
     If Not IsDBNull(dr("Field1")) Then r.Cells.Add(New Cell With {.Value = dr("Field1")})

     <more fields>

Next dr

ws.Rows.Add(r)

Dim strPath As String = My.Settings.DocPath & "ExcelReports\"
Dim strFileName As String = "Filename.xlsx"

wb.Save(New FileStream(strPath + strFileName, FileMode.Create), ".xlsx")

Problem: When the code gets to the Save statement, I get this error: "nullable object must have a value".

I don't know what this means. The strPath, strFileName and wb objects all have values, so I don't know what nullable object is being referenced.

Can anyone point out what I'm doing wrong? Or am I going about this whole thing the wrong way (entirely possible, since this is my first attempt at this)?

[Peter Milchev]

Hello Grinnell,

There are a few improvements that should be done to the code. Below is a working version of the code. Attached is a sample project implementing the suggested improvements.

Dim wb As New Workbook
Dim ws = wb.AddSheet()
' Dim ws As New Worksheet
Dim r As Row

'wb.Sheets = New List(Of Worksheet) From {ws}
'ws.Rows = New List(Of Row)


Dim rowIndex As Integer = 0
Dim columnIndex As Integer = 0

'r = New Row With {.Cells = New List(Of Cell)}
r = New Row() With {.Index = System.Math.Max(System.Threading.Interlocked.Increment(rowIndex), rowIndex - 1)}
Dim cell = New Cell With {.Index = System.Math.Max(System.Threading.Interlocked.Increment(columnIndex), columnIndex - 1), .Value = "Spreadsheet Title"}
r.AddCell(cell)
'r.AddCell(New Cell With {.Value = "Spreadsheet Title", .FontSize = Convert.ToInt16(Unit.Point(12).Value)})
'ws.Rows.Add(r)
ws.AddRow(r)

'r = New Row With {.Cells = New List(Of Cell)}
r = New Row() With {.Index = System.Math.Max(System.Threading.Interlocked.Increment(rowIndex), rowIndex - 1)}
r.AddCell(New Cell With {.Index = System.Math.Max(System.Threading.Interlocked.Increment(columnIndex), columnIndex - 1), .Value = Today.ToShortDateString(), .TextAlign = TextAlign.Left})
'ws.Rows.Add(r)
ws.AddRow(r)

'r = New Row With {.Cells = New List(Of Cell)}
r = New Row() With {.Index = System.Math.Max(System.Threading.Interlocked.Increment(rowIndex), rowIndex - 1)}
r.AddCell(New Cell With {.Index = System.Math.Max(System.Threading.Interlocked.Increment(columnIndex), columnIndex - 1), .Value = "Column Header"})
'ws.Rows.Add(r)
ws.AddRow(r)

Dim ds = GetData()

For Each dr As DataRow In ds.Rows
    'r = New Row With {.Cells = New List(Of Cell)}
    r = New Row With {.Index = System.Math.Max(System.Threading.Interlocked.Increment(rowIndex), rowIndex - 1)}
    'If Not IsDBNull(dr("ProductID")) Then r.Cells.Add(New Cell With {.Value = dr("ProductID")})
    If Not IsDBNull(dr("ProductID")) Then r.AddCell(New Cell With {.Value = dr("ProductID")})
Next dr

'ws.Rows.Add(r)
ws.AddRow(r)

Dim strPath As String = "d:\ExcelReports\"
Dim strFileName As String = Guid.NewGuid().ToString() + "Filename.xlsx"
Dim fs = New FileStream(strPath + strFileName, FileMode.Create)
wb.Save(fs, ".xlsx")

Regards,
Peter Milchev
Progress Telerik

[Steve]

I'm getting the same error ("nullable object must have a value".) when I add new rows to a spreadsheet and then call the Workbook Save method.  Your example code with changes doesn't help me solve this problem.  Telerik needs some solid working examples of how to add new rows and cells with values to an existing spreadsheet.

[Peter Milchev]

Hello Steve,

The provided code snippet works as you can see in the attached project. 

Another thing that could be improved would be declaring the workbook as follows: 

Dim wb As Workbook = New Workbook()

If you still have issues or that error is thrown with existing workbooks, please modify the attached project so that it represents it and send it back to us in an official support ticket. That would allow us to investigate locally and help you more efficiently. 

Once resolved, we can share the solution here for convenience and better visibility from the community. 

Regards,
Peter Milchev
Progress Telerik

[Eric]

I am having a similar issue.  I tried running the provided solution and I get errors running that as well.  I made a very simple solution to try to get this to work and I am still getting the Nullable Error. What am I doing wrong?  This seems like it should be a simple problem.

Protected Sub Button1_Click1(sender As Object, e As EventArgs) Handles Button1.Click
        Dim book As New Workbook()
        book.AddSheet()
        Dim sheet = book.AddSheet()
        Dim r As New Row()
        sheet.AddRow(r)
        Dim c As New Cell()
        c.Index = 1
        c.Value = "This is a test."
        r.AddCell(c)
        sheet.AddRow(r)
        Dim fs As New FileStream("C:\ExcelReports\test.xlsx", FileMode.Create)
        book.Save(fs, ".xlsx")
    End Sub

[Matthew]

When adding a row or a cell, make sure the index is specified for both.

I notice in LACDA-IT's code that row and cell do not have an index specified.
I notice in Eric's code the row does not have an index specified.

I resolved my issue by using the ToJson  method and saving the json code to a text file.
I noticed my missing index values by comparing the JSON from a workbook I programmatically added rows to vs a workbook I had created in Excel and imported into the spreadsheet widget.

In my opinion, this is a time consuming issue that could have been prevented with a minimal amount of documentation.
This post is two years old and unresolved.
It took me 30 minutes to resolve.
Once again, in my opinion, the support team could have elucidated this issue sooner than me.