一、将excel数据只读到程序中显示：
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using System.Reflection;
namespace ExcelDemo
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.ApplicationClass app = new ApplicationClass();
app.Visible = false;
WorkbookClass w = (WorkbookClass)app.Workbooks.Open(@"C:\Documents and Settings\qqq\桌面\002.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, 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.");
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 += "\r\n";
for (int j = 1; j <= len2; j++)
{
if (values.GetValue(i, j).ToString().Length == 0)
this.textBox1.Text += "\t\t";
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.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using System.Data.OleDb;
namespace ExcelDemo
{
public partial class Form2 : Form
{
public Form2()
{
InitializeComponent();
}
/// <summary>
/// 读取Excel文档
/// </summary>
/// <param name="Path">文件名称</param>
/// <returns>返回一个数据集</returns>
public DataSet ExcelToDS(string Path)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "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.Fill(ds);
return ds;
}
/// <summary>
/// 写入Excel文档
/// </summary>
/// <param name="Path">文件名称</param>
//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 cmd = new OleDbCommand();
// cmd.Connection = conn;
// //cmd.CommandText ="UPDATE [sheet1$] SET 姓名='2005-01-01' WHERE 工号='日期'";
// //cmd.ExecuteNonQuery ();
// for (int i = 0; i < fp2.Sheets[0].RowCount - 1; i++)
// {
// if (fp2.Sheets[0].Cells[i, 0].Text != "")
// {
// cmd.CommandText = "INSERT INTO [sheet1$] (工号,姓名,部门,职务,日期,时间) VALUES('" + fp2.Sheets[0].Cells[i, 0].Text + "','" +
// fp2.Sheets[0].Cells[i, 1].Text + "','" + fp2.Sheets[0].Cells[i, 2].Text + "','" + fp2.Sheets[0].Cells[i, 3].Text +
// "','" + fp2.Sheets[0].Cells[i, 4].Text + "','" + fp2.Sheets[0].Cells[i, 5].Text + "')";
// cmd.ExecuteNonQuery();
// }
// }
// conn.Close();
// return true;
// }
// catch (System.Data.OleDb.OleDbException ex)
// {
// System.Diagnostics.Debug.WriteLine("写入Excel发生错误：" + ex.Message);
// }
// return false;
//}
private void Form2_Load(object sender, EventArgs e)
{
DataSet ds=ExcelToDS(@"C:\Documents and Settings\qqq\桌面\002.xls");
int x = ds.Tables.Count;
System.Data.DataTable dt = new System.Data.DataTable();
dt.Columns.AddRange(
new DataColumn[]{
new 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))
});
for(int i=0;i<ds.Tables[0].Rows.Count;i++)
{
if (ds.Tables[0].Rows[i][13].ToString().Length <= 0)
continue;
DataRow dr=dt.NewRow();
dr.ItemArray=ds.Tables[0].Rows[i].ItemArray;
dt.Rows.Add(dr);
}
this.dataGridView1.DataSource=dt;
this.dataGridView1.AutoGenerateColumns=false;
}
}
}
