C#怎样读取excel
网上搜到以下代码，在vs2010中DataSet被标识无法识别，怎么办 。求助怎样简便地读取excel
--------------------编程问答-------------------- Error 3 The type or namespace name 'DataSet' could not be found (are you missing a using directive or an assembly reference?) c:\users\dong\documents\visual studio 2010\Projects\ConsoleApplication2\ConsoleApplication2\Program.cs 25 22 ConsoleApplication2
using System.Data.OleDb;
static public DataSet ExcelToDataSet(string filename)
{
DataSet ds;
string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Extended Properties=Excel 8.0;" +
"data source=" + filename;
OleDbConnection myConn = new OleDbConnection(strCon);
string strCom = " SELECT * FROM [Sheet1$]";
myConn.Open();
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
ds = new DataSet();
myCommand.Fill(ds);
myConn.Close();
return ds;
}
--------------------编程问答-------------------- 关于C#读Excel文件，你可以看一下我写的一个系列文章：
http://blog.csdn.net/column/details/experience-in-pia.html --------------------编程问答--------------------
正关注这些东西，学习一下 --------------------编程问答-------------------- 好像没有读取方面的内容啊，还有，怎样确定自己是否的确读取到了文件 --------------------编程问答-------------------- 继续求助：C# 将excel每一行转化成字符串，贴一段程序
DataSet ds = ExcelToDataSet("D:\\test.xls");
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
string str = ds.Tables[0].Rows.ToString();
Console.WriteLine(str);
}
结果输出的是什么System.Data...我想要数据
大神助我 --------------------编程问答-------------------- 输出的完整信息？？？
是Error还是Warning,详细的信息。
string str = ds.Tables[0].ToString();
--------------------编程问答-------------------- ds.Tables[0].Rows.ToString();
晕。。这样输出来的是rows集合对象，这个对象Tostring()只是把类名告诉你
你想要拿数据需要一个个列的Tostring()，没有整行转成字符串的，除非你自己写个扩展方法
单列输出如：第一列
ds.Tables[0].Rows[0][0].ToString();
--------------------编程问答-------------------- OleDbConnection 读取 Excel 中多sheet 的 名称
string strConn;
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:/test.xls;"+
"Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataTable sheetNames = conn.GetOleDbSchemaTable
(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
conn.Close();
foreach (DataRow dr in sheetNames.Rows)
{
al.Add(dr[2]);
}
dr[2] sheet 名称 。。。 用于多sheet 操作
段 1 OleDbConnection 读取指定 sheet
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 strSheetName)
{
//源的定义
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelFileName + ";" + "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本来只是说是否要出现一行标题头而已,但是结果却会导致某些字段值丢失,所以其实我至今也搞不明白为什么,很可能是驱动的问题...
很简单吧？！一切就像操作数据库一样，只是需要注意的是：
1。数据提供程序使用Jet，同时需要指定Extended Properties 关键字设置 Excel 特定的属性，不同版本的Excel对应不同的属性值：
用于 Extended Properties 值的有效 Excel 版本。
对于 Microsoft Excel 8.0 (97)、9.0 (2000) 和 10.0 (2002) 工作簿，请使用 Excel 8.0。
对于 Microsoft Excel 5.0 和 7.0 (95) 工作簿，请使用 Excel 5.0。
对于 Microsoft Excel 4.0 工作簿，请使用 Excel 4.0。
对于 Microsoft Excel 3.0 工作簿，请使用 Excel 3.0。
片段 2
提供两种方法：一个是直接打开excel文件，然后逐行读取，速度较慢；还有一种方法是通过OleDb连接，把excel文件作为数据源来读取
方法一：这种直接读取单元格的方法释放很重要。
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();
excel.UserControl = true;
excel.DisplayAlerts = false;
excel.Application.Workbooks.Open(this.FilePath,Nothing,Nothing,Nothing,Nothing,Nothing,Nothing,Nothing,Nothing,Nothing,Nothing,Nothing,Nothing ) ;
wbs = excel.Workbooks;
wb = wbs[1];
ws = (Excel.Worksheet)wb.Worksheets["Sheet2"];
int rowCount = ws.UsedRange.Rows.Count;
int colCount = ws.UsedRange.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();
}
}
片段 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.Tables, 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=""Excel 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 "$" and 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处理都非常简单. www.shiapifa.com
注意:数据类型的转换!!
#region set connection
string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= "+this.txtPath.Text+";Extended Properties=Excel 8.0;";
myDataReader = null;
craboDbConnection = new OleDbConnection(strConn);
OleDbCommand myOleDbCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", myOleDbConnection);
#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. www.wanxinyi.com
if (craboDbConnection!=null && craboDbConnection.State == ConnectionState.Open)
craboDbConnection.Close();
if(webResponse!=null)
webResponse.Close();
}
#endregion
==========================================================
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Collections;
using System.Data.OleDb;
namespace xml
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
}
private void button1_Click_1(object sender, EventArgs e)
{
OpenFileDialog openfile = new OpenFileDialog();
openfile.Filter = "工作薄(*.xls)|*.xls|所有文件(*.*)|*.*";
if (openfile.FilterIndex == 1 && openfile.ShowDialog() == DialogResult.OK)
ExcelToDS(openfile .FileName );
}
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 [sheet1$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
DataTable table1 = new DataTable();
ds = new DataSet();
myCommand.Fill(table1);
dataGridView1.DataSource = table1;
return ds;
}
}
}
补充：.NET技术 , C#
