ITPub博客

首页 > Linux操作系统 > Linux操作系统 > C#源码读取excel数据到程序中-SQL SERVER-到dataset中

C#源码读取excel数据到程序中-SQL SERVER-到dataset中

原创 Linux操作系统 作者:tclywork 时间:2019-06-22 08:24:04 0 删除 编辑
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 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 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, 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 += "rn";
for (int j = 1; j <= len2; j++)
{
if (values.GetValue(i, j).ToString().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.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using System.Data.OleDb;

namespace ExcelDemo
{
public partial class Form2 : Form
{
public Form2()
{
InitializeComponent();
}

///


/// 读取Excel文档
///

/// 文件名称
/// 返回一个数据集
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;
}

///


/// 写入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 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 Settingsqqq桌面02.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 {

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;

}

}

}

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/54654/viewspace-425486/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2003-08-09

  • 博文量
    129
  • 访问量
    95651