|
项目中经常会用到把excel的文件内容导入到数据库中的,刚刚花了点时间,做了个例子,基本上能实现导入Excel后显示的功能吧,导入的excel文件得是xls,即是2003的.
代码思路如下:要读取的excel文件必得得是在本地硬盘,所以一般来说都是让远程用户选择自己硬盘上的Excel文件,然后把用户选择的文件上传到本地服务器上,再在本地服务器上进行操作.我把界面后置代码重要部分贴出来,大家自己慢慢看吧,都有注释了.
C#代码
1.
2.
protected void
btnUp_Click(object sender, EventArgs e)
3.
{
4.
bool b =
Upload(fuExcel);
5.
if (!b)
6.
{
7.
return;
8.
}
9.
string name =
fuExcel.FileName;
10.
string filepath
= Server.MapPath("~/upload/") + name;
11.
DataSet ds = ExcelDataSource(filepath,
ExcelSheetName(filepath)[0].ToString());
12.
GridView1.DataSource = ds;
13.
GridView1.DataBind();
14.
}
15.
16.
17.
private bool
Upload(FileUpload myFileUpload)
18.
{
19.
bool flag = false;
20.
21.
bool fileAllow
= false;
22.
23.
string[]
allowExtensions = { ".xls" };
24.
25.
26.
string path =
HttpContext.Current.Request.MapPath("~/upload/");
27.
28.
if
(myFileUpload.HasFile)
29.
{
30.
31.
string
fileExtension = System.IO.Path.GetExtension(myFileUpload.FileName).ToLower();
32.
33.
for (int i = 0; i
< allowExtensions.Length; i++)
34.
{
35.
if
(fileExtension == allowExtensions[i])
36.
{
37.
fileAllow = true;
38.
}
39.
}
40.
41.
if
(fileAllow)
42.
{
43.
try
44.
{
45.
46.
myFileUpload.SaveAs(path + myFileUpload.FileName);
47.
lblMes.Text = "文件导入成功";
48.
flag = true;
49.
}
50.
catch
(Exception ex)
51.
{
52.
lblMes.Text += ex.Message;
53.
flag = false;
54.
}
55.
}
56.
else
57.
{
58.
lblMes.Text = "不允许上载：" + myFileUpload.PostedFile.FileName + "，只能上传xls的文件，请检查！";
59.
flag = false;
60.
}
61.
}
62.
else
63.
{
64.
lblMes.Text = "请选择要导入的excel文件!";
65.
flag = false;
66.
}
67.
return flag;
68.
}
69.
70.
71.
public DataSet
ExcelDataSource(string filepath, string
sheetname)
72.
{
73.
string strConn;
74.
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" + filepath + ";Extended
Properties=Excel 8.0;";
75.
OleDbConnection conn = new
OleDbConnection(strConn);
76.
OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetname + "]", strConn);
77.
DataSet ds = new DataSet();
78.
oada.Fill(ds);
79.
conn.Close();
80.
return ds;
81.
}
82.
83.
84.
public ArrayList
ExcelSheetName(string filepath)
85.
{
86.
ArrayList al = new ArrayList();
87.
string strConn;
88.
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" + filepath + ";Extended
Properties=Excel 8.0;";
89.
OleDbConnection conn = new
OleDbConnection(strConn);
90.
conn.Open();
91.
DataTable sheetNames = conn.GetOleDbSchemaTable
92.
(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
93.
conn.Close();
94.
foreach (DataRow
dr in sheetNames.Rows)
95.
{
96.
al.Add(dr[2]);
97.
}
98.
return al;
99.
}
// 上传按钮 protected void btnUp_Click(object sender, EventArgs e) { bool b = Upload(fuExcel); // 上传excel文件 if (!b) { return; } string name = fuExcel.FileName; string filepath = Server.MapPath("~/upload/") + name; DataSet ds = ExcelDataSource(filepath, ExcelSheetName(filepath)[0].ToString()); GridView1.DataSource = ds; GridView1.DataBind(); } //上传文件方法 private bool Upload(FileUpload myFileUpload) { bool flag = false; //是否允许上载 bool fileAllow = false; //设定允许上载的扩展文件名类型 string[] allowExtensions = { ".xls" }; //取得网站根目录路径 string path = HttpContext.Current.Request.MapPath("~/upload/"); //检查是否有文件案 if (myFileUpload.HasFile) { //取得上传文件之扩展文件名，并转换成小写字母 string fileExtension = System.IO.Path.GetExtension(myFileUpload.FileName).ToLower(); //检查扩展文件名是否符合限定类型 for (int i = 0; i < allowExtensions.Length; i++) { if (fileExtension == allowExtensions[i]) { fileAllow = true; } } if (fileAllow) { try { //存储文件到文件夹 myFileUpload.SaveAs(path + myFileUpload.FileName); lblMes.Text = "文件导入成功"; flag = true; } catch (Exception ex) { lblMes.Text += ex.Message; flag = false; } } else { lblMes.Text = "不允许上载：" + myFileUpload.PostedFile.FileName + "，只能上传xls的文件，请检查！"; flag = false; } } else { lblMes.Text = "请选择要导入的excel文件!"; flag = false; } return flag; } //该方法实现从Excel中导出数据到DataSet中，其中filepath为Excel文件的绝对路径， sheetname为excel文件中的表名 public DataSet ExcelDataSource(string filepath, string sheetname) { string strConn; strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;"; OleDbConnection conn = new OleDbConnection(strConn); OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetname + "]", strConn); DataSet ds = new DataSet(); oada.Fill(ds); conn.Close(); return ds; } //获得Excel中的所有sheetname。 public ArrayList ExcelSheetName(string filepath) { ArrayList al = new ArrayList(); string strConn; strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;"; OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); DataTable sheetNames = conn.GetOleDbSchemaTable (System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); conn.Close(); foreach (DataRow dr in sheetNames.Rows) { al.Add(dr[2]); } return al; }
要注意的是我们要一开始就在网站根目录下建立upload文件夹,而且要把他的权限设置为可读可写的?这个权限的问题搞得头大,不知道到底应该怎么搞的,XP系统下新建立的文件夹好像都是只读的,我右键属性把只读去掉,结果再次查看的时候还是只读,不过好像发现对程序没有什么意思,上传完excel文件后还是可以读取查看的.
|
