片段 1
using System.Data.OleDb;
...
static void Main()conn.Open();
//适配到数据源conn.Close();
return ds.Tables[strSheetName];
很简单的代码，但是问题就出在连接字符串上面，后面一定要加上Extended Properties='Excel 8.0;HDR=NO;IMEX=1'，HDR和IMEX也一定要配合使用，哈哈,老实说,我也不知道为什么,这样配合的效果最好,这是我艰苦调试的结果.IMEX=1应该是将所有的列全部视为文本,我也有点忘记了.至于HDR本来只是说是否要出现一行标题头而已,但是结果却会导致某些字段值丢失,所以其实我至今也搞不明白为什么,很可能是驱动的问题...
片段 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)方法二：这里是fill进dataset，也可以返回OleDbDataReader来逐行读，数据较快
注：这种方法容易把混合型的字段作为null值读取进来，解决办法是改造连接字符串
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Erp1912.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
通过Imex=1来把混合型作为文本型读取，避免null值,详细请见http://www.connectionstrings.com
private DataSet importExcelToDataSet(string FilePath)
{
string strConn;
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FilePath + ";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.Message);
}
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.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"]);{
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处理都非常简单.
HTTP://BLOG.CSDN.NET/CRABO/
注意:数据类型的转换!!
#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);
#endregiontry
{
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
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。