This is a migrated thread and some comments may be shown as answers.

How to save a spreadsheet

5 Answers 134 Views
Spreadsheet
This is a migrated thread and some comments may be shown as answers.
LACDA-IT
Top achievements
Rank 1
LACDA-IT asked on 28 Feb 2018, 05:35 PM

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)?

5 Answers, 1 is accepted

Sort by
0
Peter Milchev
Telerik team
answered on 05 Mar 2018, 02:34 PM
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
Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
0
Steve
Top achievements
Rank 1
answered on 18 May 2018, 05:26 PM
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.
0
Peter Milchev
Telerik team
answered on 23 May 2018, 03:26 PM
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
Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
0
Eric
Top achievements
Rank 1
answered on 05 Apr 2019, 07:35 PM

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
0
Matthew
Top achievements
Rank 1
Veteran
answered on 28 Mar 2021, 12:47 AM

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.

Tags
Spreadsheet
Asked by
LACDA-IT
Top achievements
Rank 1
Answers by
Peter Milchev
Telerik team
Steve
Top achievements
Rank 1
Eric
Top achievements
Rank 1
Matthew
Top achievements
Rank 1
Veteran
Share this question
or