Custom Timer to export List Items to Excel missing data (Network Steve Forum)

Custom Timer to export List Items to Excel missing data

Hi, I'm working on a SP 2010 custom timer job that will monthly take last month's items from a SP list into an Excel spreadsheet and store it in a doc library. The whole process is working, except when I create the Excel sheet from SPListItems collection, the Excel sheet is missing data. NOT missing columns, but missing data from the columns at the end.

My test list has 5 items. When I step thru the code, the SPListItemCollection has all 5 items and all data in it. However, after the rest of the code has run (posted below), I get a spreadsheet with the first 3 lines intact, the 4th row data ends halfway through the 3rd column, and the last row is completely missing.

In the code below, I initially tried the GetDataTable method, then wrote my own function to try to capture all the data, but neither works - both are missing the last row and part of the 2nd to last row of data, even though "items" contains all rows and all data. Any ideas? Thanks!

web.AllowUnsafeUpdates = True

Using stream As New MemoryStream()

Using streamWriter As New StreamWriter(stream, Encoding.UTF8)

Using stringWriter As New StringWriter()

Using htmlTextWriter As New HtmlTextWriter(stringWriter)

Dim gv As New SPGridView

Dim list As SPList = TryGetList(web, strListName, False)

Dim wpView As SPView = list.Views("All Items")

Dim items As SPListItemCollection = list.GetItems(wpView)

' Dim listItemsTable As DataTable = items.GetDataTable()

                                    Dim listItemsTable As DataTable = GetMyDataTable(items)
                                    Dim ds As New DataSet
                                    ds.Tables.Add(listItemsTable)
                                    gv.DataSource = ds
                                    gv.DataBind()

                                    If listItemsTable.Rows.Count > 0 Then
                                        Dim tbl As New Table
                                        'tbl.Rows.Add(gv.HeaderRow)
                                        For Each row As GridViewRow In gv.Rows
                                            tbl.Rows.Add(row)
                                        Next

tbl.RenderControl(htmlTextWriter)

streamWriter.Write(stringWriter.ToString())

Dim monthNow As String = DateTime.Now.Month.ToString
Dim yearNow As String = DateTime.Now.Year.ToString

Dim fileName As String = "TE_Reg_" + yearNow + "_" + monthNow + ".xls"
Dim ToLibrary As SPList = TryGetList(web, "TE Training Excel Archive", False)

Dim url As String = ToLibrary.RootFolder.ServerRelativeUrl.ToString()

                                        Dim docLibraryPath As String = url + "/" + fileName
                                        Dim file As SPFile = web.Files.Add(docLibraryPath, stream, False)
                                        file.Item("Title") = fileName
                                        file.Item.Update()
                                    End If
                                End Using
                            End Using
                        End Using
                    End Using

September 1st, 2015 8:36am

I actually did more research and found that by adding streamWriter.Autoflush, the spreadsheet contains all the data.

https://social.msdn.microsoft.com/Forums/vstudio/en-US/69586b61-8885-4c1b-ad5d-bd56858d4901/streamwriter-stops-in-the-middle-of-writing-a-line

Marked as answer by KimFromPA 18 hours 5 minutes ago

Free Windows Admin Tool Kit Click here and download it now

September 1st, 2015 9:37am

Hi,

Thanks for sharing, it will help others who stuck will the same issue in the future.

Thanks

Best Regards

September 1st, 2015 9:33pm

This topic is archived. No further replies will be accepted.