C# 读写Excel及文本文件

最新推荐文章于 2024-06-07 16:19:41 发布

awark

最新推荐文章于 2024-06-07 16:19:41 发布

阅读量195

点赞数

文章标签： Excel C C++ C# Microsoft

本文链接：https://blog.csdn.net/awark/article/details/83915475

版权

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可以写成[test$A1:IU6XXXX]。 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 [test$A:B] ";
            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 [0523$] ";
            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 [EmployeeData$]", myConn);
            DataSet ds = new DataSet();
            da.Fill(ds, "inputTable");

            da.InsertCommand = new OleDbCommand("INSERT INTO [EmployeeData$] VALUES(?, ?)", myConn);
            da.InsertCommand.Parameters.Add("@Id", OleDbType.VarChar).SourceColumn = "Id";
            da.InsertCommand.Parameters.Add("@Name", OleDbType.VarChar).SourceColumn = "Name";

            DataRow dr = ds.Tables[0].NewRow();
            dr[0] = "すべて";
            dr[1] = "0";
            ds.Tables[0].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文件。

// 文件写入：[test.txt]

// 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();
        }
    }
}