作者:乔山办公网日期:
返回目录:excel表格制作
/// <summary>
/// 由7a686964616fe4b893e5b19e361EXCEL转换成DataTable
/// </summary>
/// <param name="strpath">文件路径及文件名 </param>
/// <returns> </returns>
private DataTable XlsToDataTable(String strpath)
{
string strConn;
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + strpath + ";" +
"Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
DataTable dt = new DataTable();
myCommand.Fill(dt);
return dt;
}
注意Sheet1$,这是工作表的名字.
// 从数据库中查询数据,返回DataTable
DataTable Query(OleDbConnection conn, string sql)
{
OleDbDataAdapter adapter = new OleDbDataAdapter(sql, conn);
DataSet ds = new DataSet();
try
{
adapter.Fill(ds, "factors");
}
catch (Exception ex)
{
_log.Error("GetFactorsInfo() fail: ", ex);
}
return ds.Tables[0];
}
// 将e799bee5baa6e59b9ee7ad94361DataTable中的数据显示到DataGridView中
void ShowData(DataTable dt, DataGridview dgv)
{
dgv.Columns.Clear();
if (dt != null)
{
dgv.AutoGenerateColumns = true;
dgv.DataSource = dt;
}
}
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);
}
不需要任何插件,e799bee5baa6e4b893e5b19e361只需要引用using System.Data.OleDb;直接可用
给你源码,自己研究去吧
首先先建立一个实体类,然后利用反射把datatable转成实体集合,然后写SQL,添加到数据库;或者直接遍历datatable每行构建实体,在加到数据库也一样