|
|
Posted on 2018-08-15 22:58 viery 阅读(106) 评论(0) 编辑 收藏 所属分类: .net
 
1 using Oracle.DataAccess.Client;
2 using System;
3 using System.Collections;
4 using System.Collections.Generic;
5 using System.ComponentModel;
6 using System.Data;
7 using System.Data.OleDb;
8 using System.Drawing;
9 using System.IO;
10 using System.Linq;
11 using System.Text;
12 using System.Threading.Tasks;
13 using System.Windows.Forms;
14
15 namespace ImportToDatabase
16 {
17 public partial class Form1 : Form
18 {
19 public Form1()
20 {
21 InitializeComponent();
22 }
23
24 private void label1_Click(object sender, EventArgs e)
25 {
26
27 }
28
29 private void button3_Click(object sender, EventArgs e)
30 {
31 //初始化一个OpenFileDialog类
32 OpenFileDialog fileDialog = new OpenFileDialog();
33
34 //判断用户是否正确的选择了文件
35 if (fileDialog.ShowDialog() == DialogResult.OK)
36 {
37 //获取用户选择文件的后缀名
38 string extension = Path.GetExtension(fileDialog.FileName);
39 string filename = Path.GetFileName(fileDialog.FileName);
40 string fullpath = Path.GetFullPath(fileDialog.FileName);
41 //声明允许的后缀名
42 string[] str = new string[] { ".xls", ".xlsx" };
43 if (!((IList)str).Contains(extension))
44 {
45 MessageBox.Show("仅能上传excel文件！");
46 }
47 else
48 {
49 //获取用户选择的文件，并判断文件大小不能超过20M，fileInfo.Length是以字节为单位的
50 FileInfo fileInfo = new FileInfo(fileDialog.FileName);
51 if (fileInfo.Length > 2048000000)
52 {
53 MessageBox.Show("上传文件不能大于20M");
54 }
55 else
56 {
57 label1.Text = fullpath;
58 }
59 }
60 }
61 }
62
63 private void button1_Click(object sender, EventArgs e)
64 {
65 GetData();
66 }
67
68 private void GetData()
69 {
70 string connection = "Data Source = orcl;user id = scott; password = tiger";//数据库的连接 数据源 用户 密码
71 OracleConnection coon = new OracleConnection(connection);//建立数据库连接
72 OracleCommand cmd = new OracleCommand("select EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO from emp order by empno desc ", coon);//执行数据连接
73 DataSet ds1 = new DataSet();//定义数据集
74 OracleDataAdapter da1 = new OracleDataAdapter(cmd);//取出数据表
75 da1.Fill(ds1);//将数据加载到数据集中
76 DataTable dt = ds1.Tables[0];//将数据放入表中
77 coon.Close();//关闭数据库连接
78
79 //数据显示 在dataGridView中显示
80
81 this.dataGridView1.DataSource = dt.DefaultView;
82 this.dataGridView1.AutoSizeColumnsMode = System.Windows.Forms.DataGridViewAutoSizeColumnsMode.Fill;
83 this.dataGridView1.Refresh();
84 }
85
86 private void button2_Click(object sender, EventArgs e)
87 {
88 //MessageBox.Show(label1.Text);
89 DataSet dts = LoadDataFromExcel(label1.Text);
90 if (dts == null)
91 {
92 MessageBox.Show("数据为空！");
93 }
94 else
95 {
96 // MessageBox.Show("数据不为空!");
97 addData(dts);
98 GetData();
99 }
100
101 }
102
103
104
105 public DataSet LoadDataFromExcel(string filePath)
106 {
107 try
108 {
109 string strConn;
110 strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
111 OleDbConnection OleConn = new OleDbConnection(string.Format(strConn));
112 OleConn.Open();
113 String sql = "SELECT * FROM [Sheet1$]";//可是更改Sheet名称，比如sheet2，等等
114
115 OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
116 DataSet OleDsExcle = new DataSet();
117 OleDaExcel.Fill(OleDsExcle, "Sheet1");
118 OleConn.Close();
119 return OleDsExcle;
120 }
121 catch (Exception err)
122 {
123 MessageBox.Show("数据绑定Excel失败!失败原因：" + err.Message, "提示信息",
124 MessageBoxButtons.OK, MessageBoxIcon.Information);
125 return null;
126 }
127 }
128
129 public void addData(DataSet obj)
130 {
131 try
132 {
133 string strConnect = "Data Source = orcl;user id = scott; password = tiger";//数据库的连接 数据源 用户 密码
134 OracleConnection dbConn = new OracleConnection(strConnect);
135 dbConn.Open();
136
137 foreach (DataTable dt in obj.Tables) //MyDataSet是自已定义并已赋值的DataSet对象。
138 {
139 foreach (DataRow dr in dt.Rows) ///遍历所有的行
140 {
141 string sql = string.Format("INSERT INTO emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES(seq_empno.nextval,'" + dr["ENAME"] + "','" + dr["JOB"] + "','" + dr["MGR"] + "'," + "to_date('" + (dr["HIREDATE"]) + "','YYYY/MM/dd HH24:mi:ss" + "'),'" + dr["SAL"] + "','" + dr["COMM"] + "','" + Convert.ToInt32(dr["DEPTNO"]) + "')");
142 // MessageBox.Show(sql);
143 OracleCommand aCommand = new OracleCommand(sql, dbConn);
144 aCommand.ExecuteNonQuery();
145 }
146 }
147 MessageBox.Show("导入成功");
148 dbConn.Close();
149 }
150 catch (Exception e)
151 {
152 MessageBox.Show(e.Message);
153 }
154 }
155
156 }
157 }
158
|