Upload Excel data into SharePiont Custom List using Interop
In this post we will see how to upload excel data (using office.interop.excel) into SharePoint 2007 / SharePoint 2010 Custom List from object model(c#)You can also check the article how to upload excel data using oledb provider
Upload excel data
The following code has method LoadExcelData() which reads data from ‘contacts.xls’ and inserts into SharePoint Custom List ‘ContactList’.According to the schema of the ‘ContactList’ that I have created, the method ‘InsertIntoList’ in the following code has relevant code.
You can modify according to the schema of your Custom List and Excel file
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
| using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.IO; using System.Data; using System.Data.OleDb; using Microsoft.SharePoint; namespace TestSharepointProject { public class UploadExcelData { public void LoadExcelData() { string filePath = "@"C:\AdisGroup\Contacts\contacts.xls"; //if you are using file upload control in sharepoint get the full path as follows assuming fileUpload1 is control instance //string filePath = fileUpload1.PostedFile.FileName if (!(string.IsNullOrEmpty(fileName))) { DataTable excelData = GetExcelDataTable(filePath); if (excelData != null) { InsertIntoList(excelData,"ContactList"); } } } private DataTable GetExcelDataTable(string filePath) { DataTable dt = new DataTable(); Microsoft.Office.Interop.Excel.Application ExcelObj = new Microsoft.Office.Interop.Excel.Application(); ExcelObj.DisplayAlerts = false; if (ExcelObj == null) { return null; } string filead = filePath; Microsoft.Office.Interop.Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(filead, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); Microsoft.Office.Interop.Excel.Sheets sheets = theWorkbook.Worksheets; Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1); int index = 0; object rowIndex = 2; dt.Columns.Add("FirstName"); dt.Columns.Add("LastName"); dt.Columns.Add("FullName"); dt.Columns.Add("LoginID"); dt.Columns.Add("EmailAddress"); dt.Columns.Add("PhoneNumberBusiness"); dt.Columns.Add("Company"); DataRow row; while (((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 1]).Value2 != null) { rowIndex = 2 + index; row = dt.NewRow(); row[0] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 1]).Value2); row[1] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 2]).Value2); row[2] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 3]).Value2); row[3] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 4]).Value2); row[4] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 5]).Value2); row[5] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 6]).Value2); row[6] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 7]).Value2); index++; dt.Rows.Add(row); } ExcelObj.Workbooks.Close(); return dt; } private void InsertIntoList(DataTable listTable, string contactListName) { SPWeb mySite = null; try { mySite = SPContext.Current.Web; //create web object if context is null mySite.AllowUnsafeUpdates = true; SPList contactList = mySite.Lists[contactListName]; for (int iRow = 0; iRow < listTable.Rows.Count; iRow++) { SPListItem newContact = contactList.Items.Add(); newContact["FirstName"] = Convert.ToString(listTable.Rows[iRow][0]); newContact["LastName"] = Convert.ToString(listTable.Rows[iRow][1]); newContact["FullName"] = Convert.ToString(listTable.Rows[iRow][2]); newContact["LoginID"] = Convert.ToString(listTable.Rows[iRow][3]); newContact["EmailAddress"] = Convert.ToString(listTable.Rows[iRow][4]); newContact["PhoneNumber"] = Convert.ToString(listTable.Rows[iRow][5]); newContact["Company"] = Convert.ToString(listTable.Rows[iRow][6]); newContact.Update(); } mySite.AllowUnsafeUpdates = false; } catch (Exception ex) { //log exception } finally { if (mySite != null) //don't dispose if the site is from SPContext { mySite.AllowUnsafeUpdates = false; } } } } } |
Remarks
In the above code‘GetExcelDataTable’ method takes data from first sheet name i.e get_Item(1). Change this if you want to load from another sheetName
‘InsertIntoList’ method uses SPContext to get the current web object. If you are using the above code where SPContext is not available then
you have create SPWeb object and dispose it in finally block
No comments:
Post a Comment