After you produce a Microsoft office excel table in a worksheet, you may want to create the table data available to alternative users on a web site. In Microsoft office excel 2007, you’ll be able to use the Export Table to SharePoint List Wizard to export the table knowledge to a custom list that’s hold on on a Windows SharePoint Services website.
To export table data to a listing on a Windows SharePoint Services website, you wish to own a connection to that website as well because the permission to create lists. When users update the list on the SharePoint website, you’ll be able to update the data on your worksheet with the newest changes. If you no longer desire a connection between the table data on the worksheet and therefore the SharePoint list, you can unlink the table. This code snippet for upload excel sheet data into SharePoint List.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 |
Imports System Imports System.Web.UI Imports System.Web.UI.WebControls Imports System.Web.UI.WebControls.WebParts Imports System.Text Imports System.IO Partial Public Class UploadUserControl Inherits UserControl Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load End Sub Public Class CSVReader Private objStream As Stream Private objReader As StreamReader 'add name space System.IO.Stream Public Sub New(ByVal filestream As Stream) Me.New(filestream, Nothing) End Sub Public Sub New(ByVal filestream As Stream, ByVal enc As Encoding) Me.objStream = filestream 'check the Pass Stream whether it is readable or not If Not filestream.CanRead Then Return End If objReader = If((enc IsNot Nothing), New StreamReader(filestream, enc), New StreamReader(filestream)) End Sub 'parse the Line Public Function GetCSVLine() As String() Dim data As String = objReader.ReadLine() If data Is Nothing Then Return Nothing End If If data.Length = 0 Then Return New String(-1) {} End If 'System.Collection.Generic Dim result As New ArrayList() 'parsing CSV Data ParseCSVData(result, data) Return DirectCast(result.ToArray(GetType(String)), String()) End Function Private Sub ParseCSVData(ByVal result As ArrayList, ByVal data As String) Dim position As Integer = -1 While position < data.Length result.Add(ParseCSVField(data, position)) End While End Sub Private Function ParseCSVField(ByRef data As String, ByRef StartSeperatorPos As Integer) As String If StartSeperatorPos = data.Length - 1 Then StartSeperatorPos += 1 Return "" End If Dim fromPos As Integer = StartSeperatorPos + 1 If data(fromPos) = """"c Then Dim nextSingleQuote As Integer = GetSingleQuote(data, fromPos + 1) Dim lines As Integer = 1 While nextSingleQuote = -1 data = data + vbLf + objReader.ReadLine() nextSingleQuote = GetSingleQuote(data, fromPos + 1) lines += 1 End While StartSeperatorPos = nextSingleQuote + 1 Dim tempString As String = data.Substring(fromPos + 1, nextSingleQuote - fromPos - 1) tempString = tempString.Replace("'", "''") Return tempString.Replace("""""", """") End If Dim nextComma As Integer = data.IndexOf(","c, fromPos) If nextComma = -1 Then StartSeperatorPos = data.Length Return data.Substring(fromPos) Else StartSeperatorPos = nextComma Return data.Substring(fromPos, nextComma - fromPos) End If End Function Private Function GetSingleQuote(ByVal data As String, ByVal SFrom As Integer) As Integer Dim i As Integer = SFrom - 1 While System.Threading.Interlocked.Increment(i) < data.Length If data(i) = """"c Then If i < data.Length - 1 AndAlso data(i + 1) = """"c Then i += 1 Continue While Else Return i End If End If End While Return -1 End Function End Class Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnUpload.Click Dim ext As String = FileUpload1.FileName.Substring(FileUpload1.FileName.LastIndexOf(".")) Dim reader As New CSVReader(FileUpload1.PostedFile.InputStream) 'get the header Dim headers As String() = reader.GetCSVLine() Dim dt As New DataTable() For Each strHeader As String In headers dt.Columns.Add(strHeader) Next While True Dim data As String() = reader.GetCSVLine() If (data Is Nothing) Then ' TODO: might not be correct. Was : Exit While Exit While End If dt.Rows.Add(data) For Each ss As String In data Next End While Dim dr As DataTable = dt Dim _site As New SPSite(SPContext.Current.Site.Url) Dim _web As SPWeb = _site.OpenWeb() Dim _list As SPList = _web.Lists("Listname") For Each row1 As DataRow In dr.Rows Dim newitem As SPListItem = _list.Items.Add Dim cc As String = row1(0).ToString() Try newitem("Title") = row1(0) Catch ex As Exception lblTEST.Text += cc + ":Name" + ex.Message End Try Try newitem.Update() Catch ex As Exception lblTEST.Text += cc + ": Update" + ex.Message End Try lblTEST.Text += "<br/>" Next End Sub End Class |
SharePoint 2013 Hosting Recommendation
HostForLIFE.eu’s SharePoint 2013 Hosting solution offers a comprehensive feature set that is easy-to-use for new users, yet powerful enough for the most demanding web developer expert. Hosted SharePoint Foundation 2013 is the premiere web-based collaboration and productivity enhancement tool on the market today. With SharePoint 2013 Foundation, you can quickly access and manage documents and information anytime, anywhere though a Web browser in a secure and user friendly way. SharePoint hosting services start at only at €9.99/mo, allowing you to take advantage of the robust feature set for a small business price. HostForLIFE.eu offers a variety of hosted SharePoint Foundation 2013 plans as well as dedicated SharePoint 2013 Foundation options.