如何使用C#将DataTable中的数据导入Excle,求大神指导!!!

2024-11-01 02:18:36
推荐回答(1个)
回答(1):

public static void ToExcel(DataTable dt, string Filter, string FileName, string SheetName) { if (string.IsNullOrEmpty(FileName)) { return; } //要保证文件存在 string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="" + FileName + "";Extended Properties="Excel 8.0;HDR=YES""; OleDbConnection oleConnection = new OleDbConnection(connStr); oleConnection.Open(); OleDbCommand oleCmd = new OleDbCommand(); //删除同名的表 try { oleCmd.Connection = oleConnection; oleCmd.CommandText = string.Format("drop table [{0}]", SheetName); oleCmd.CommandType = CommandType.Text; oleCmd.ExecuteNonQuery(); } catch { } //创建表(相同结构),表不能重复 StringBuilder strsql = new StringBuilder(); strsql.AppendFormat("Create table [{0}]", SheetName); strsql.Append(" ("); foreach (DataColumn col in dt.Columns) { strsql.AppendFormat("[{0}] {1},", col.ColumnName, GetDataType(col.DataType)); } strsql.Remove(strsql.Length - 1, 1); strsql.Append(" ) "); oleCmd.Connection = oleConnection; oleCmd.CommandType = CommandType.Text; oleCmd.CommandText = strsql.ToString(); oleCmd.ExecuteNonQuery(); oleConnection.Close(); //将dt中的数据填充到当前的excle的表中 OleDbDataAdapter oleAdpater = new OleDbDataAdapter("select * from [" + SheetName + "]", oleConnection); DataTable dt_excel = new DataTable(); oleAdpater.Fill(dt_excel); oleAdpater.InsertCommand = SqlInsert(SheetName, dt, oleConnection); DataRow dr_excel; if (Filter == null) Filter = ""; foreach (DataRow dr in dt.Select(Filter)) { dr_excel = dt_excel.NewRow(); foreach (DataColumn col in dt.Columns) { dr_excel[col.ColumnName] = dr[col.ColumnName]; } dt_excel.Rows.Add(dr_excel); } oleAdpater.Update(dt_excel); }不需要任何插件,只需要引用using System.Data.OleDb;直接可用给你源码,自己研究去吧如何使用C#将DataTable中的数据导入Excle,求大神指导!!!