C#操作Excel(导入导出)
转载地址:http://www.pconline.com.cn/pcedu/empolder/net/cs/0507/674430.html
有很多朋友说需要C#导出到Excel的代码,现共享给大家
///
/// 读取Excel文档
///
/// 文件名称
/// 返回一个数据集
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);
ds = new DataSet();
myCommand.Fill(ds, " table1 " );
return ds;
}
///
/// 写入Excel文档
///
/// 文件名称
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 {
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文档
///
/// 文件名称
/// 返回一个数据集
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);
ds = new DataSet();
myCommand.Fill(ds, " table1 " );
return ds;
}
///
/// 写入Excel文档
///
/// 文件名称
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 {
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 ;
}