记住这个参数:IMEX=0
0:写入
1:读取
2:写入和读取
用2好像没用 没深究
读取Excel 返回 DataSet:
ToDataTable("E:\\2.xlsx");
public static DataSet ToDataTable(string filePath)
{
string connStr = "";
string fileType = System.IO.Path.GetExtension(filePath);
if (string.IsNullOrEmpty(fileType)) return null;
if (fileType == ".xls")
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
else
connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
string sql_F = "Select * FROM [{0}]";
OleDbConnection conn = null;
OleDbDataAdapter da = null;
DataTable dtSheetName = null;
DataSet ds = new DataSet();
try
{
// 初始化连接,并打开
conn = new OleDbConnection(connStr);
conn.Open();
// 获取数据源的表定义元数据
string SheetName = "";
dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
// 初始化适配器
da = new OleDbDataAdapter();
for (int i = 0; i < dtSheetName.Rows.Count; i++)
{
SheetName = (string)dtSheetName.Rows[i]["TABLE_NAME"];
if (SheetName.Contains("$") && !SheetName.Replace("'", "").EndsWith("$"))
{
continue;
}
da.SelectCommand = new OleDbCommand(String.Format(sql_F, SheetName), conn);
DataSet dsItem = new DataSet();
da.Fill(dsItem, "table" + i);
ds.Tables.Add(dsItem.Tables[0].Copy());
}
}
catch (Exception ex)
{
}
finally
{
// 关闭连接
if (conn.State == ConnectionState.Open)
{
conn.Close();
da.Dispose();
conn.Dispose();
}
}
return ds;
}
传入DataSet保存到Excel
DataSetToExcel("E:\\2.xlsx", DataSet, "表名");
public static void DataSetToExcel(string filePath, DataSet ds, string tableName)
{
DataTable dt = ds.Tables[0];
string connStr = "";
string fileType = System.IO.Path.GetExtension(filePath);
if (string.IsNullOrEmpty(fileType)) return;
if (fileType == ".xls")
connStr = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=0;'", filePath);
else
connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=0\"";
try
{
using (OleDbConnection conn = new OleDbConnection(connStr))
{
conn.Open();
//创建表格字段
StringBuilder strSQL = new StringBuilder();
strSQL.Append("CREATE TABLE ").Append("[" + tableName + "]");
strSQL.Append("(");
for (int i = 0; i < dt.Columns.Count; i++)
{
strSQL.Append("[" + dt.Columns[i].ColumnName + "] text,");
}
strSQL = strSQL.Remove(strSQL.Length - 1, 1);
strSQL.Append(")");
OleDbCommand cmd = new OleDbCommand(strSQL.ToString(), conn);
cmd.ExecuteNonQuery();
//添加数据
for (int i = 0; i < dt.Rows.Count; i++)
{
strSQL.Clear();
StringBuilder strvalue = new StringBuilder();
for (int j = 0; j < dt.Columns.Count; j++)
{
strvalue.Append("'" + dt.Rows[i][j].ToString() + "'");
if (j != dt.Columns.Count - 1)
{
strvalue.Append(",");
}
}
cmd.CommandText = strSQL.Append(" insert into [" + tableName + "] values (").Append(strvalue).Append(")").ToString();
cmd.ExecuteNonQuery();
}
conn.Close();
}
}
catch (Exception ex)
{
}
}