ExcelHelper代码
using System.Data.OleDb;
using System;
using System.IO;
using System.Data;
using System.Text;
using System.Collections;
namespace Wxy.Util
{
/// <summary>
/// ExcelHelper 的摘要说明。
/// </summary>
public class ExcelHelper
{
public ExcelHelper()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
/// <summary>
/// 读取Excel文档
/// </summary>
/// <param name="Path">文件名称</param>
/// <returns>返回一个数据集</returns>
public static DataSet ExcelToDS(string Path)
{
try
{
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);
ds = new DataSet();
myCommand.Fill(ds,"table1");
return ds;
}
catch(System.Data.OleDb.OleDbException ex)
{
System.Diagnostics.Debug.WriteLine ("写入Excel发生错误:"+ex.Message );
return null;
}
}
public static void DataTableToExcel(System.Data.DataView dataview,string Path,Hashtable NameMap)
{
try
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ Path +";"+"Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
System.Data.OleDb.OleDbCommand cmd=new OleDbCommand ();
cmd.Connection = conn;
string strSql = string.Empty ,strSql1 = string.Empty ;
int i , j ;
for( i = 0 ; i < dataview.Count ; i++ )
{
strSql = "INSERT INTO [sheet1$] (";
strSql1 = ") values(";
for( j = 0 ; j < dataview.Table.Columns.Count ; j++)
{
if( NameMap.ContainsKey( dataview.Table.Columns[j].ColumnName ) )
{
strSql += NameMap[dataview.Table.Columns[j].ColumnName] +"," ; //2414210
strSql1 += "'" +dataview[i][j].ToString() + "',";
}
}
try
{
if( strSql.EndsWith(",") )
strSql = strSql.Substring(0,strSql.Length - 1 ) ;
if( strSql1.EndsWith(",") )
strSql1 = strSql1.Substring(0,strSql1.Length - 1 ) ;
strSql1 = strSql1 + ")";
strSql = strSql + strSql1 ;
cmd.CommandText = strSql ;
cmd.ExecuteNonQuery();
}
catch(Exception ex)
{
System.Diagnostics.Debug.WriteLine ("写入Excel发生错误:"+ strSql + strSql1 + ex.Message );
throw new Exception(strSql + ex.Message);
}
}
conn.Close ();
}
catch(System.Data.OleDb.OleDbException ex)
{
System.Diagnostics.Debug.WriteLine ("写入Excel发生错误:"+ex.Message );
}
}
public static void DataTableToExcel(System.Data.DataView dataview,string Path)
{
try
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ Path +";"+"Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
System.Data.OleDb.OleDbCommand cmd=new OleDbCommand ();
cmd.Connection = conn;
string strSql = string.Empty ,strSql1 = string.Empty ;
int i , j ;
for( i = 0 ; i < dataview.Count ; i++ )
{
strSql = "INSERT INTO [sheet1$] (";
strSql1 = ") values(";
for( j = 0 ; j < dataview.Table.Columns.Count ; j++)
{
strSql += dataview.Table.Columns[j].ColumnName +"," ;
strSql1 += "'" +dataview[i][j].ToString() + "',";
}
//
try
{
if( strSql.EndsWith(",") )
strSql = strSql.Substring(0,strSql.Length - 1 ) ;
if( strSql1.EndsWith(",") )
strSql1 = strSql1.Substring(0,strSql1.Length - 1 ) ;
strSql1 = strSql1 + ")";
strSql = strSql + strSql1 ;
cmd.CommandText = strSql ;
cmd.ExecuteNonQuery();
}
catch(Exception ex)
{
System.Diagnostics.Debug.WriteLine ("写入Excel发生错误:"+ strSql + ex.Message );
throw new Exception(strSql + ex.Message);
}
}
conn.Close ();
}
catch(System.Data.OleDb.OleDbException ex)
{
System.Diagnostics.Debug.WriteLine ("写入Excel发生错误:"+ex.Message );
}
}
/// <summary>
/// 写入Excel文档
/// </summary>
/// <param name="Path">文件名称</param>
public bool SaveFP2toExcel(string Path)
{
try
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ Path +";"+"Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
System.Data.OleDb.OleDbCommand cmd=new OleDbCommand ();
cmd.Connection =conn;
cmd.CommandText ="UPDATE [sheet1$] SET 姓名='2005-01-01' WHERE 工号='日期'";
cmd.ExecuteNonQuery ();
/* for(int i=0;i<fp2.Sheets [0].RowCount -1;i++)
{
if(fp2.Sheets [0].Cells[i,0].Text!="")
{
cmd.CommandText ="INSERT INTO [sheet1$] (工号,姓名,部门,职务,日期,时间) VALUES('"+fp2.Sheets [0].Cells[i,0].Text+ "','"+
fp2.Sheets [0].Cells[i,1].Text+"','"+fp2.Sheets [0].Cells[i,2].Text+"','"+fp2.Sheets [0].Cells[i,3].Text+
"','"+fp2.Sheets [0].Cells[i,4].Text+"','"+fp2.Sheets [0].Cells[i,5].Text+"')";
cmd.ExecuteNonQuery ();
}
}
*/
conn.Close ();
return true;
}
catch(System.Data.OleDb.OleDbException ex)
{
System.Diagnostics.Debug.WriteLine ("写入Excel发生错误:"+ex.Message );
}
return false;
}
}
}
以下文章中用到的工具类 发布一个Excel导入数据到GridView的类