诶呀呀 我好久没有写blog了 在干嘛呢 想想哦, 对了最近迷上了个网页游戏 哈哈~~ 是什么 我不说~ 我就不帮 pchome做广告
今天我要做的是 在 winform 中 读取 excel 表中的数据。
好建立一个 winform 的窗体 中间放一个 datagridview 控件 干嘛的呢? 就是让我先直观的看出数据是不是进入了ds, 当然我这个是偷懒的做法 哇哈哈 原谅我的懒~
不准批评我的 设计功底
好了 界面做好了 现在就是要写后台了。
首先
我们做个方法 返回一个 datatable
- public static DataTable ExcelToDataTable(string strExcelFileName, string strSheetName)
- {
- FileInfo fileInfo = new FileInfo(strExcelFileName);
- if (!fileInfo.Exists)
- return null;
- string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileInfo + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1'";
- string strExcel = string.Format("select * from [{0}$]", strSheetName);
- DataSet ds = new DataSet();
- using (OleDbConnection conn = new OleDbConnection(connectionString))
- {
- conn.Open();
- OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, connectionString);
- adapter.Fill(ds, strSheetName);
- conn.Close();
- }
- return ds.Tables[strSheetName];
- }
然后呢 我在button的 click的事件中 调用他 方法是这样的
- private void btnReadExcel_Click(object sender, EventArgs e)
- {
- if (openFileDialog1.ShowDialog() == DialogResult.OK)
- {
- dt = ExcelToDataTable(this.openFileDialog1.FileName, "Sheet1");//获得Excel
- this.dataGridView1.DataSource = dt.DefaultView;
- }
- else
- {
- return;
- }
- }
然后我们就可以看到 在数据控件中出现了数据 如下图
恩 初看还是不错的吧 哇哈哈
好了 下面就是 怎么将datatable里面的数据存入数据库里面
- private void btnSaveData_Click(object sender, EventArgs e)
- {
- SqlConnection myConnection1 = new SqlConnection(@"Data Source=CASPER-LAPTOP/SQLEXPRESS;Initial Catalog=PrintSys;Integrated Security=True");
- try
- {
- myConnection1.Open();
- int i;
- i = dt.Rows.Count;
- int count = 0;
- for (int j = 1; j < i; j++) //循环插入数据库的
- {
- string City;
- string Hotelno;
- string HotelName;
- string Remark;
- string Sales;
- string Ac;
- string Reservation;
- string Tel;
- string Fax;
- string Address;
- City = dt.Rows[j][0].ToString();
- Hotelno = dt.Rows[j][1].ToString();
- HotelName = dt.Rows[j][2].ToString();
- Remark = dt.Rows[j][3].ToString();
- Sales = dt.Rows[j][4].ToString();
- Ac = dt.Rows[j][5].ToString();
- Reservation = dt.Rows[j][6].ToString();
- Tel = dt.Rows[j][7].ToString();
- Fax = dt.Rows[j][8].ToString();
- Address = dt.Rows[j][9].ToString();
- string insertcmd1 = "insert into Hotel_info values ('" + City + "','" + Hotelno + "','" + HotelName + "','" + Remark + "','" + Sales + "','" + Ac + "','" + Reservation + "','" + Tel + "','" + Fax + "','" + Address + "')"; //赋值写入数据库
- SqlDataAdapter dap = new SqlDataAdapter(insertcmd1, myConnection1);
- count = dap.Fill(dt);
- }
- MessageBox.Show("成功更新到数据库中!", "系统消息!");
- }
- catch (Exception ex)
- {
- MessageBox.Show("更新到数据库中出错!", "系统消息!");
- }
- finally
- {
- myConnection1.Close();
- }
- }
这段有问题的 因为这个格式是特别为转门有固定格式的excel写的 所以格式是死的 我还是会改一下 让他通用一点 嘿嘿i
到这里 基本ok啦 ,下面贴出全部的代码
- using System;
- using System.Collections.Generic;
- using System.ComponentModel;
- using System.IO;
- using System.Data;
- using System.Data.OleDb;
- using System.Data.SqlClient;
- using System.Drawing;
- using System.Linq;
- using System.Text;
- using System.Windows.Forms;
- namespace ExcelRead
- {
- public partial class frmReadExcel : Form
- {
- DataTable dt;
- public frmReadExcel()
- {
- InitializeComponent();
- }
- private void Form1_Load(object sender, EventArgs e)
- {
- }
- public static DataTable ExcelToDataTable(string strExcelFileName, string strSheetName)
- {
- FileInfo fileInfo = new FileInfo(strExcelFileName);
- if (!fileInfo.Exists)
- return null;
- string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileInfo + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1'";
- string strExcel = string.Format("select * from [{0}$]", strSheetName);
- DataSet ds = new DataSet();
- using (OleDbConnection conn = new OleDbConnection(connectionString))
- {
- conn.Open();
- OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, connectionString);
- adapter.Fill(ds, strSheetName);
- conn.Close();
- }
- return ds.Tables[strSheetName];
- }
- private void btnReadExcel_Click(object sender, EventArgs e)
- {
- if (openFileDialog1.ShowDialog() == DialogResult.OK)
- {
- dt = ExcelToDataTable(this.openFileDialog1.FileName, "Sheet1");//获得Excel
- this.dataGridView1.DataSource = dt.DefaultView;
- }
- else
- {
- return;
- }
- }
- private void btnSaveData_Click(object sender, EventArgs e)
- {
- SqlConnection myConnection1 = new SqlConnection(@"Data Source=CASPER-LAPTOP/SQLEXPRESS;Initial Catalog=PrintSys;Integrated Security=True");
- try
- {
- myConnection1.Open();
- int i;
- i = dt.Rows.Count;
- int count = 0;
- for (int j = 1; j < i; j++) //循环插入数据库的
- {
- string City;
- string Hotelno;
- string HotelName;
- string Remark;
- string Sales;
- string Ac;
- string Reservation;
- string Tel;
- string Fax;
- string Address;
- City = dt.Rows[j][0].ToString();
- Hotelno = dt.Rows[j][1].ToString();
- HotelName = dt.Rows[j][2].ToString();
- Remark = dt.Rows[j][3].ToString();
- Sales = dt.Rows[j][4].ToString();
- Ac = dt.Rows[j][5].ToString();
- Reservation = dt.Rows[j][6].ToString();
- Tel = dt.Rows[j][7].ToString();
- Fax = dt.Rows[j][8].ToString();
- Address = dt.Rows[j][9].ToString();
- string insertcmd1 = "insert into Hotel_info values ('" + City + "','" + Hotelno + "','" + HotelName + "','" + Remark + "','" + Sales + "','" + Ac + "','" + Reservation + "','" + Tel + "','" + Fax + "','" + Address + "')"; //赋值写入数据库
- SqlDataAdapter dap = new SqlDataAdapter(insertcmd1, myConnection1);
- count = dap.Fill(dt);
- }
- MessageBox.Show("成功更新到数据库中!", "系统消息!");
- }
- catch (Exception ex)
- {
- MessageBox.Show("更新到数据库中出错!", "系统消息!");
- }
- finally
- {
- myConnection1.Close();
- }
- }
- }
- }