c#源码读取excel数据到程式中-sql server-到dat…

一、将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 != "")

标签：

版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有