《C#读取excel数据.doc》由会员分享，可在线阅读，更多相关《C#读取excel数据.doc（20页珍藏版）》请在金锄头文库上搜索。
1、C#源码读取excel数据到程序中-SQL SERVER-到dataset中 = 作者: tclywork(http:/)发表于: 2007.05.28 10:07分类: 程序开发 , 出处: http:/ C#源码读取excel数据到程序中-SQL SERVER-到dataset中一、将excel数据只读到程序中显示：using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Text;using Sy
2、stem.Windows.Forms;using Microsoft.Office.Interop.Excel;using System.Reflection;namespace ExcelDemopublic partial class Form1 : Formpublic Form1()InitializeComponent(); private void button1_Click(object sender, EventArgs e)Microsoft.Office.Interop.Excel.ApplicationClass app = new ApplicationClass();
3、app.Visible = false;WorkbookClass w = (WorkbookClass)app.Workbooks.Open(C:Documents and Settingsqqq桌面02.xls, /Environment.CurrentDirectory+Missing.Value, Missing.Value, Missing.Value,Missing.Value, Missing.Value, Missing.Value,Missing.Value, Missing.Value, Missing.Value,Missing.Value, Missing.Value,
4、 Missing.Value,Missing.Value, Missing.Value);object missing = Type.Missing; Sheets sheets = w.Worksheets;Worksheet datasheet = null;foreach (Worksheet sheet in sheets)if (sheet.Name = Recovered_Sheet1)datasheet = sheet;break;if (null = datasheet)MessageBox.Show(this, 没有名称为 Recovered_Sheet1 的Sheet.);
5、return;Range range = datasheet.get_Range(A8,N35);System.Array values = (System.Array)range.Formula;if (values != null)int len1 = values.GetLength(0);int len2 = values.GetLength(1);for (int i = 1; i = len1; i+)this.textBox1.Text += rn;for (int j = 1; j = len2; j+)if (values.GetValue(i, j).ToString().
6、Length = 0)this.textBox1.Text += tt;this.textBox1.Text += t + values.GetValue(i, j).ToString();app.Quit();app = null; 二、读取到dataset中/从dataset中写入sql server：using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Text;using System.Wi
7、ndows.Forms;using Microsoft.Office.Interop.Excel;using System.Data.OleDb;namespace ExcelDemopublic partial class Form2 : Formpublic Form2()InitializeComponent();/ / 读取Excel文档 / / 文件名称 / 返回一个数据集 public DataSet ExcelToDS(string Path)string strConn = Provider=Microsoft.Jet.OLEDB.4.0; + Data Source= + P
8、ath + ; + Extended Properties=Excel 8.0;OleDbConnection conn = new OleDbConnection(strConn);conn.Open();string strExcel = ;OleDbDataAdapter myCommand = null;DataSet ds = null;strExcel = select * from Recovered_Sheet1$;myCommand = new OleDbDataAdapter(strExcel, strConn);ds = new DataSet();myCommand.F
9、ill(ds);return ds;/ / 写入Excel文档 / / 文件名称 /public bool SaveFP2toExcel(string Path)/ try/ / string strConn = Provider=Microsoft.Jet.OLEDB.4.0; + Data Source= + Path + ; + Extended Properties=Excel 8.0;/ OleDbConnection conn = new OleDbConnection(strConn);/ conn.Open();/ System.Data.OleDb.OleDbCommand
10、cmd = new OleDbCommand();/ cmd.Connection = conn;/ /cmd.CommandText =UPDATE sheet1$ SET 姓名=2005-01-01 WHERE 工号=日期; / /cmd.ExecuteNonQuery (); / for (int i = 0; i fp2.Sheets0.RowCount - 1; i+)/ / if (fp2.Sheets0.Cellsi, 0.Text != )/ / cmd.CommandText = INSERT INTO sheet1$ (工号,姓名,部门,职务,日期,时间) VALUES(
11、+ fp2.Sheets0.Cellsi, 0.Text + , +/ fp2.Sheets0.Cellsi, 1.Text + , + fp2.Sheets0.Cellsi, 2.Text + , + fp2.Sheets0.Cellsi, 3.Text +/ , + fp2.Sheets0.Cellsi, 4.Text + , + fp2.Sheets0.Cellsi, 5.Text + );/ cmd.ExecuteNonQuery();/ / / conn.Close();/ return true;/ / catch (System.Data.OleDb.OleDbException
12、 ex)/ / System.Diagnostics.Debug.WriteLine(写入Excel发生错误： + ex.Message);/ / return false;/private void Form2_Load(object sender, EventArgs e)DataSet ds=ExcelToDS(C:Documents and Settingsqqq桌面02.xls);int x = ds.Tables.Count; System.Data.DataTable dt = new System.Data.DataTable();dt.Columns.AddRange(new
13、 DataColumnnew DataColumn(col1,typeof(string),new DataColumn(col2,typeof(string),new DataColumn(col3,typeof(string),new DataColumn(col4,typeof(string),new DataColumn(col5,typeof(string),new DataColumn(col6,typeof(string),new DataColumn(col7,typeof(string),new DataColumn(col8,typeof(string),new DataColumn(col9,typeof(string),new DataColumn(col10,typeof(string),new DataColumn(col11,typeof(string),new DataColumn(col12,typeof(string),new DataColumn(col13,typeof(string),new DataColumn(col14,typeof(string);f
