How to save a spreadsheet

4 posts, 0 answers
  1. Grinnell
    Grinnell avatar
    6 posts
    Member since:
    Aug 2014

    Posted 28 Feb 2018 Link to this post

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

  2. Peter Milchev
    Admin
    Peter Milchev avatar
    482 posts

    Posted 05 Mar 2018 Link to this post

    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.
  3. Steve
    Steve avatar
    3 posts
    Member since:
    May 2018

    Posted 18 May 2018 in reply to Peter Milchev Link to this post

    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.
  4. Peter Milchev
    Admin
    Peter Milchev avatar
    482 posts

    Posted 23 May 2018 Link to this post

    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.
Back to Top