Exporting Excel data to SharePoint 64 bit's List environment.
If you are creating the windows application to export the data from Excel 2003 files to MOSS 64 bit environment, the following solution will help to achieve this functionality. we have to adopt this approach because till now there is no 64 bit version of the Microsoft.JET.OLEDB.4.0 driver available. So If you compile you assembly by choosing the "Any CPU" option in Visual Studio for windows application, you will not be able to connect and read the data from 32 bit of "Microsoft.JET.OLEDB.4.0" drivers. First create a custom web service in SharePoint, which add the reference of 64 bit API to export the data to SharePoint's List. Choose the Compilation options of "Any CPU"
1:
2: using System;
3: using System.Web;
4: using System.Web.Services;
5: using System.Web.Services.Protocols;
6: using System.Collections.Generic;
7: using System.Collections;
8: using Microsoft.SharePoint;
9: using System.Data;
10:
11: namespace ServiceLogic
12: {
13: [WebService(Namespace = "https://tempuri.org/")]
14: [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
15: public class Service : System.Web.Services.WebService
16: {
17: public Service()
18: {
19:
20: //Uncomment the following line if using designed components
21: //InitializeComponent();
22: }
23:
24: [WebMethod]
25: public string UpdateList(DataSet dataSet)
26: {
27:
28: using (SPSite site = new SPSite("https://blr3r07-19c:5794/sites/TestSite"))
29: {
30: using (SPWeb web = site.OpenWeb())
31: {
32: SPList list = web.Lists["test"];
33: SPListItem listItem = list.Items.Add();
34: listItem["Title"] = dataSet.Tables[0].Rows[0][0].ToString();
35: listItem.Update();
36: return "Data Inserted";
37: }
38: }
39: }
40:
41: }
42: }
Once you completed with programming & installation of your custom web service in you SharePoint 64 bit environment, move towards your exporting utility. Create a windows or console project in visual studio, which ever you like, I choose windows application. Add the web reference of your custom web service. Use the Microsoft.JET.OLEDB.4.0 to read the excel data and rapped it in a DataSet then pass to your SharePoint custom web service's web method.
1: using System;
2: using System.Collections.Generic;
3: using System.ComponentModel;
4: using System.Data;
5: using System.Drawing;
6: using System.Text;
7: using System.Windows.Forms;
8: using System.Data.OleDb;
9:
10: namespace ConsumeWebServiceWindows
11: {
12: public partial class Form1 : Form
13: {
14: public Form1()
15: {
16: InitializeComponent();
17: }
18:
19: private void button1_Click(object sender, EventArgs e)
20: {
21: HelloService.Service lobjService = new HelloService.Service();
22: lobjService.Credentials = System.Net.CredentialCache.DefaultCredentials;
23: string lstrMessage = lobjService.HelloWorld();
24: MessageBox.Show(lstrMessage);
25: }
26:
27: private void button2_Click(object sender, EventArgs e)
28: {
29: string DBLocation = txtFilePath.Text.Trim();
30: string TableName = "GSIS";
31: string FileNames = "GSIS16.xls";
32:
33: string connectionString = "Provider=Microsoft.JET.OLEDB.4.0;data source=" + DBLocation + FileNames + ";Extended Properties=\"Excel 8.0;HDR=YES;\"";
34: MessageBox.Show("Processing File: " + FileNames + " at: " + DateTime.Now.ToString());
35: OleDbConnection conn = new OleDbConnection(connectionString);
36:
37: string sql = "SELECT * FROM " + TableName;
38: OleDbCommand cmd = new OleDbCommand(sql, conn);
39: conn.Open();
40:
41: OleDbDataReader reader = cmd.ExecuteReader();
42: int Count = 0;
43: while (reader.Read())
44: {
45: string VendorId = Convert.ToString(reader["VENDOR_ID"]).Trim();
46: if (!string.IsNullOrEmpty(VendorId))
47: {
48: try
49: {
50: string lstrMessage = Convert.ToString(reader["VENDOR_NAME_LINE1"]).Trim();
51: DataSet lobjSet = new DataSet();
52: DataTable lobjtable = new DataTable();
53: lobjSet.Tables.Add(lobjtable);
54: DataColumn column = new DataColumn("Title",typeof(string));
55: lobjtable.Columns.Add(column);
56: DataRow row = lobjtable.Rows.Add();
57: row["Title"] = lstrMessage;
58:
59:
60: HelloService.Service lobjService = new HelloService.Service();
61: lobjService.Credentials = System.Net.CredentialCache.DefaultCredentials;
62: string lstrMessage1 = lobjService.UpdateList(lobjSet);
63:
64: MessageBox.Show(lstrMessage1);
65:
66:
67: }
68: catch (Exception ex)
69: {
70: MessageBox.Show(ex+ "VendorID:");
71: }
72: }
73: }
74:
75: MessageBox.Show("End Processing File: " + FileNames + " at: " + DateTime.Now.ToString());
76: reader.Close();
77: conn.Close();
78: }
79: }
80: }