I needed to write a system that would allow us to upload the contents of Excel spreadsheets into a SQL database. I could use the built in import functionality of SQL, however this requires alot of extra work on top of the excel list, also I wanted the uploads to be carried out by users that don't understand SQL.
What I came up with is demonstrated below, first the file is uploaded into a temp directory where it is then reads the first two columns of the specified sheet of the uploaded excel document and sends them to a stored procedure insertExcelData.
The example takes ignores the first row assuming it's a header row but takes the rest of the data but you could easily change the for statement to limit the number of rows to import.
1: using System;
2: using System.Collections.Generic;
3: using System.Linq;
4: using System.Web;
5: using System.Web.UI;
6: using System.Web.UI.WebControls;
7: using System.Collections;
8: using System.Configuration;
9: using System.Data;
10: using System.Web.Security;
11: using System.Web.UI.HtmlControls;
12: using System.Web.UI.WebControls.WebParts;
13: using System.Data.SqlClient;
14: using System.Collections.Specialized;
15: using Microsoft.SqlServer.Server;
16: using System.Data.OleDb;
17: using System.IO;
18: public partial class Upload : System.Web.UI.Page
19: {
20: public string serverPath = "/Temp/";
21: protected void submit_Upload(object Src, EventArgs e)
22: {
23: string filetoupload = EditFileUpload.FileName;
24: string strFullFilePath = Server.MapPath(serverPath + filetoupload);
25: if (EditFileUpload.HasFile)
26: {
27: FileInfo checkFile = new FileInfo(serverPath + filetoupload);
28: if (!checkFile.Exists)
29: {
30: if (Path.GetExtension(filetoupload) == ".xls")
31: {
32: EditFileUpload.SaveAs(strFullFilePath);
33: InsertFileintoDatabase(filetoupload);
34: }
35: else
36: {
37: lblResult.Text = "Sorry. Only Excel files excepted!!!";
38: }
39: }
40: }
41: else
42: {
43: lblResult.Text = "You have not specified a file to upload!!!";
44: }
45: }
46: protected void InsertFileintoDatabase(string strFile)
47: {
48: SqlConnection strConn = null;
49: strConn = new SqlConnection(ConfigurationManager.AppSettings["strConn"]);
50: string strUploadedFile = Server.MapPath(serverPath + strFile);
51: if (File.Exists(strUploadedFile))
52: {
53: //get the dataset from the Execl Worksheet
54: DataSet dsExcelData = fGetExcelDataset(strFile);
55: //loop through the Excel dataset and insert the data to SQL table
56: int i;
57: if (dsExcelData.Tables[0].Rows.Count > 0)
58: {
59: for (i = 0; i < dsExcelData.Tables[0].Rows.Count; i++)
60: {
61: string sqlStr;
62: sqlStr = "insertExcelData '" + dsExcelData.Tables[0].Rows[i][0] + "','"
63: + dsExcelData.Tables[0].Rows[i][1] + "'";
64: SqlCommand myInsertCommand = new SqlCommand();
65: myInsertCommand.CommandText = sqlStr;
66: myInsertCommand.Connection = strConn;
67: strConn.Open();
68: myInsertCommand.ExecuteNonQuery();
69: strConn.Close();
70: }
71: lblResult.Text = "You have succesfuly inserted data";
72: }
73: dsExcelData.Dispose();
74: //File is deleted after being uploaded and data inserted into database
75: File.Delete(strUploadedFile);
76: }
77: else
78: {
79: lblResult.Text = "Excel File NOT Found";
80: }
81: }
82: public DataSet fGetExcelDataset(string strExcelFile)
83: {
84: DataSet dsExcelData;
85: OleDbDataAdapter cmdReadExcelFile;
86: string strSheetToImport = txtSheet.Text;
87: if (strSheetToImport == null)
88: {
89: strSheetToImport = "sheet1";
90: }
91: string docpath = Server.MapPath(serverPath + strExcelFile);
92: string strConn = ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + docpath + ";" + "Extended Properties=Excel 8.0;");
93: cmdReadExcelFile = new System.Data.OleDb.OleDbDataAdapter("Select * From [" + strSheetToImport + "$]", strConn);
94: dsExcelData = new System.Data.DataSet();
95: cmdReadExcelFile.Fill(dsExcelData);
96: return dsExcelData;
97: }
98: protected void Page_Load(object sender, EventArgs e)
99: {
100: if (!Directory.Exists(Server.MapPath(serverPath)))
101: {
102: Directory.CreateDirectory(Server.MapPath(serverPath));
103: }
104: }
105: protected void cboCountry_SelectedIndexChanged(object sender, EventArgs e)
106: {
107: cboState.DataBind();
108: if (cboState.Items.Count != 0)
109: {
110: cboState.Visible = true;
111: ListItem li = new ListItem("No State", "0");
112: cboState.Items.Add(li);
113: cboState.SelectedIndex = cboState.Items.IndexOf(cboState.Items.FindByValue("0"));
114: }
115: else
116: {
117: cboState.Visible = false;
118: }
119: }