C# 读写Excel及文本文件
C# 读写Excel及文本文件using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
namespace WindowsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
// excel的最大列是256列，dataTabel的最大列为255最后一列怎么解决的问题
// strCom可以写成。 6XXXX是最大行数（忘了是多少，所以只能XXX了）
// 也可以写成以下形式，把SHEET分成多块，这样就避免了上述问题
private void Form1_Load(object sender, EventArgs e)
{
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = C:\\test.xls;Extended Properties='Excel 8.0;HDR=NO;IMEX=1'";
OleDbConnection myConn = new OleDbConnection(strCon);
string strCom = " SELECT * FROM ";
myConn.Open();
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet, "test1");
myConn.Close();
}
// 还在为不知道SHEET名烦恼么？请看下例
private void button1_Click(object sender, EventArgs e)
{
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = C:\\test.xls;Extended Properties='Excel 8.0;HDR=NO;IMEX=1'";
OleDbConnection myConn = new OleDbConnection(strCon);
//string strCom = " SELECT * FROM ";
myConn.Open();
//OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
//DataSet myDataSet = new DataSet();
//myCommand.Fill(myDataSet, "test1");
DataTable dt = myConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
foreach (DataRow dr in dt.Rows)
{
MessageBox.Show((String)dr["TABLE_NAME"]);
}
myConn.Close();
}
// 向EXCEL中追加数据
private void button2_Click(object sender, EventArgs e)
{
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = C:\\input.xls;Extended Properties='Excel 8.0;HDR=YES'";
OleDbConnection myConn = new OleDbConnection(strCon);
myConn.Open();
OleDbCommand cmd1 = new OleDbCommand();
cmd1.Connection = myConn;
cmd1.CommandText = "CREATE TABLE EmployeeData (Id char(255), Name char(255))";
cmd1.ExecuteNonQuery();
OleDbDataAdapter da = new OleDbDataAdapter("Select * From ", myConn);
DataSet ds = new DataSet();
da.Fill(ds, "inputTable");
da.InsertCommand = new OleDbCommand("INSERT INTO VALUES(?, ?)", myConn);
da.InsertCommand.Parameters.Add("@Id", OleDbType.VarChar).SourceColumn = "Id";
da.InsertCommand.Parameters.Add("@Name", OleDbType.VarChar).SourceColumn = "Name";
DataRow dr = ds.Tables.NewRow();
dr = "すべて";
dr = "0";
ds.Tables.Rows.Add(dr);
da.Update(ds, "inputTable");
myConn.Close();
}
// 读取csv文件
private void button3_Click(object sender, EventArgs e)
{
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = C:\\;Extended Properties='text;HDR=NO;FMT=Delimited'";
OleDbConnection myConn = new OleDbConnection(strCon);
string strCom = " SELECT * FROM test.csv ";
myConn.Open();
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet, "test1");
myConn.Close();
}
// 其实本意是想读取后缀名为tsv的文件的。但是貌似jet4.0支持的后缀只有.txt,.csv,.tab,.asc,.htn,.html
// 所以只能重命名。另外文本如果不是用逗号分割的话，必须在当前目录下建schema.ini文件。
// 文件写入：
// ColNameHeader =false
// Format =TABDelimited
private void button4_Click(object sender, EventArgs e)
{
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = C:\\;Extended Properties='text'";
OleDbConnection myConn = new OleDbConnection(strCon);
string strCom = " SELECT * FROM test.txt ";
myConn.Open();
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet, "test1");
myConn.Close();
}
}
}
页:
[1]