C# 读取Excel文件代码的几种方法

C# 读取Excel文件代码的几种方法

片段 1

using System.Data.OleDb;

...

static void Main()

{

Application.Run(new Form1());

}

private void Form1_Load(object sender, System.EventArgs e)

{

DataTable myT=ExcelToDataTable("D:/文件/新武昌站点资料.xls","sheet1");

String mystr=myT.Rows[0][0].ToString();

this.textBox1.Text=mystr;

}

public static DataTable ExcelToDataTable(string strExcelFileName, string strSh eetName)

{

//源的定义

string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExc elFileName + ";" + "Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";

//Sql语句

//string strExcel = string.Format("select * from [{0}$]", strSheetName); 这是一种方法

string strExcel = "select * from [sheet1$]";

//定义存放的数据表

DataSet ds = new DataSet();

//连接数据源

OleDbConnection conn = new OleDbConnection(strConn);

conn.Open();

//适配到数据源

OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);

adapter.Fill(ds, strSheetName);

conn.Close();

return ds.Tables[strSheetName];

}

很简单的代码，但是问题就出在连接字符串上面，后面一定要加上Extended Properties='Excel 8.0;HDR =NO;IMEX=1'，HDR和IMEX也一定要配合使用，哈哈,老实说,我也不知道为什么,这样配合的效果最好,这是我艰苦调试的结果.IMEX=1应该是将所有的列全部视为文本,我也有点忘记了.至于HDR本来只是说是否要出现一行标题头而已,但是结果却会导致某些字段值丢失,所以其实我至今也搞不明白为什么,很可能是驱动的问题...

片段 2

提供两种方法：一个是直接打开excel文件，然后逐行读取，速度较慢；还有一种方法是通过OleDb连接，把e xcel文件作为数据源来读取

方法一：这种直接读取单元格的方法释放很重要。

Excel.Application excel = null;

Excel.Workbooks wbs = null;

Excel.Workbook wb = null;

Excel.Worksheet ws = null;

Excel.Range range1 = null;

object Nothing = System.Reflection.Missing.Value;

try

{

excel = new Excel.Application();

https://www.360docs.net/doc/bb5460421.html,erControl = true;

excel.DisplayAlerts = false;

excel.Application.Workbooks.Open(this.FilePath,Nothing,Nothing,Nothing,Not hing,Nothing,Nothing,Nothing,Nothing,Nothing,Nothing,Nothing,Nothing ) ;

wbs = excel.Workbooks;

wb = wbs[1];

ws = (Excel.Worksheet)wb.Worksheets["Sheet2"];

int rowCount = https://www.360docs.net/doc/bb5460421.html,edRange.Rows.Count;

int colCount = https://www.360docs.net/doc/bb5460421.html,edRange.Columns.Count;

if (rowCount <= 0)

throw new InvalidFormatException("文件中没有数据记录");

if (colCount < 4 )

throw new InvalidFormatException("字段个数不对");

for (int i = 0;i {

this.rowNo = i + 1;

object[] row = new object[4];

for (int j = 0;j<4;j++)

{

range1 = ws.get_Range(ws.Cells[i+2,j+1],ws.Cells[i+2,j+1]); row[j] = range1.Value;

if (row[0] == null)

{

this.isNullRecord++;

break;

}

}

if (this.isNullRecord > 0)

continue;

DataRow dataRow = this.readExcel(row);

if (this.isNullRecord == 1)

continue;

if (this.verifyData(dataRow) == false)

errFlag++;

this.updateTableCurr(dataRow);

}

}

finally

{

if (excel != null)

{

if (wbs != null)

{

if (wb != null)

{

if (ws != null)

{

if (range1 != null)

{

System.Runtime.InteropServices.Marshal.ReleaseComObject(range1); range1 = null;

}

System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);

ws = null;

}

wb.Close(false,Nothing,Nothing);

System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);

wb = null;

}

wbs.Close();

System.Runtime.InteropServices.Marshal.ReleaseComObject(wbs);

wbs = null;

}

excel.Application.Workbooks.Close();

excel.Quit();

System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);

excel = null;

GC.Collect();

}

}

方法二：这里是fill进dataset，也可以返回OleDbDataReader来逐行读，数据较快

注：这种方法容易把混合型的字段作为null值读取进来，解决办法是改造连接字符串

strConn = "Provider=Microsoft.J et.OLEDB.4.0;Data Source=C:\\Erp1912.xls;Extended Prop erties='Excel 8.0;HDR=Yes;IMEX=1'";

通过Imex=1来把混合型作为文本型读取，避免null值,详细请见https://www.360docs.net/doc/bb5460421.html, private DataSet importExcelToDataSet(string FilePath)

{

string strConn;

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FilePat

h + ";Extended Properties=Excel 8.0;";

OleDbConnection conn = new OleDbConnection(strConn);

OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);

DataSet myDataSet = new DataSet();

try

{

myCommand.Fill(myDataSet);

}

catch(Exception ex)

{

throw new InvalidFormatException("该Excel文件的工作表的名字不正确," + ex.Messa ge);

}

return myDataSet;

}

片段 3

用c#读取excel文件,写到datagridview控件中

用c#读取excel文件,写到datagridview控件中

string strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:￥MyExcel.xls; Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1''"" ;

OleDbConnection conn = new OleDbConnection(strconn);

conn.Open();

if (bo == false)

{

comboBox1.Items.Clear();

DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Table s, new object[] { null, null, null, "TABLE" });

foreach (DataRow dr in dt.Rows)

{

comboBox1.Items.Add((String)dr["TABLE_NAME"]);

//MessageBox.Show((String)dr["TABLE_NAME"]);

}

//comboBox1.Text = comboBox1.Items[0].ToString();

}

else

{

string sql = "select * from " + comboBox1.Text;

OleDbDataAdapter aper = new OleDbDataAdapter(sql, conn); DataSet myset = new DataSet();

aper.Fill(myset, comboBox1.Text);

dataGridView1.DataSource = myset.Tables[comboBox1.Text]; }

conn.Close();

备注：

@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:￥MyExcel.xls;Extended Properties=""E xcel 8.0;HDR=Yes;IMEX=1"""

"HDR=Yes;" indicates that the first row contains columnnames, not data

"IMEX=1;" tells the driver to always read "intermixed" data columns as text

TIP! SQL syntax: "SELECT * FROM [sheet1$]" - i.e. worksheet name followed by a "$" a nd wrapped in "[" "]" brackets.

如果第一行是数据而不是标题的话, 应该写: "HDR=No;"

"IMEX=1;" tells the driver to always read "intermixed" data columns as text

片段 4

C#读取Excel文件数据

相当简单,Excel就像数据库,每个Sheet就是一个Table. Microsoft.Jet.OLEDB驱动.

之后是DataReader循环,或DataSet处理都非常简单.

https://www.360docs.net/doc/bb5460421.html,/CRABO/

注意:数据类型的转换!!

#region set connection

string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= "+this.txt Path.Text+";Extended Properties=Excel 8.0;";

myDataReader = null;

craboDbConnection = new OleDbConnection(strConn);

OleDbCommand myOleDbCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", m yOleDbConnection);

#endregion

try

{

myOleDbConnection.Open();

myDataReader = myOleDbCommand.ExecuteReader();

while (myDataReader.Read())

{

this.txtSeq.Text=Convert.ToString(myDataReader.GetValue(0));//列1

this.txtName.Text=Convert.ToString(myDataReader.GetValue(1));//列2

this.txtPIN.Text=Convert.ToString(myDataReader.GetValue(2));//列3

}

}

#region Catch

catch(System.Threading.ThreadAbortException e)

{

System.Threading.Thread.ResetAbort();

this.lblResult.Text = "线程被中断..."+e.Message;

}

catch(Exception ex)

{

System.Windows.Forms.MessageBox.Show(ex.ToString());

}

finally

{

// Always call Close when done reading.

if (myDataReader != null)

myDataReader.Close();

// Close the connection when done with it.

if (craboDbConnection!=null && craboDbConnection.State == ConnectionState. Open)

craboDbConnection.Close();

if(webResponse!=null)

webResponse.Close();

}

#endregion