private void CreateExcelFile(string filePath, DataSet ds)
{
if (File.Exists(filePath))
{
File.Delete(filePath);
}
OleDbConnection oleDbConn = new OleDbConnection();
OleDbCommand oleDbCmd = new OleDbCommand();
try
{
string sSql = "";
oleDbConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + @";Extended ProPerties=""Excel 8.0;HDR=Yes;""";
oleDbConn.Open();
oleDbCmd.CommandType = CommandType.Text;
oleDbCmd.Connection = oleDbConn;
//写列名
for(int k=0;k<ds.Tables.Count;k++)
{
DataTable dt = ds.Tables[k];
sSql = "CREATE TABLE sheet" + (k + 1).ToString() + "(";
for (int i = 0; i < dt.Columns.Count; i++)
{
if (i < dt.Columns.Count - 1)
{
if (dt.Columns[i].DataType.Name == "String" || dt.Columns[i].DataType.Name=="Guid")
{
sSql += "["+dt.Columns[i].ColumnName + "] Text,";
}
else if (dt.Columns[i].DataType.Name == "DateTime")
{
sSql += "[" + dt.Columns[i].ColumnName + "] Datetime,";
}
else
{
sSql += "[" + dt.Columns[i].ColumnName + "] Decimal,";
}
}
else
{
if (dt.Columns[i].DataType.Name == "String")
{
sSql += "[" + dt.Columns[i].ColumnName + "] Text)";
}
else if (dt.Columns[i].DataType.Name == "DateTime")
{
sSql += "[" + dt.Columns[i].ColumnName + "] DateTime)";
}
else
{
sSql += "[" + dt.Columns[i].ColumnName + "] Decimal)";
}
}
}
oleDbCmd.CommandText = sSql;
oleDbCmd.ExecuteNonQuery(); for (int j = 0; j < dt.Rows.Count; j++)
{
sSql = "INSERT INTO sheet" + (k + 1).ToString() + " VALUES(";
for (int i = 0; i < dt.Columns.Count; i++)
{
if (i < dt.Columns.Count - 1)
{
if (DBNull.Value.Equals(dt.Rows[j][i]))
{
sSql += "NULL,";
}
else
{
if (dt.Columns[i].DataType.Name == "Decimal")
{
sSql += dt.Rows[j][i].ToString() + ",";
}
else
{
sSql += "'" + dt.Rows[j][i].ToString().Replace("'", "''") + "',";
}
}
}
else
if (DBNull.Value.Equals(dt.Rows[j][i]))
{
sSql += "NULL)";
}
else
{
if (dt.Columns[i].DataType.Name == "Decimal")
{
sSql += dt.Rows[j][i].ToString() + ")";
}
else
{
sSql += "'" + dt.Rows[j][i].ToString().Replace("'","''") + "')";
}
}
}
oleDbCmd.CommandText = sSql;
oleDbCmd.ExecuteNonQuery();
}
}
}
catch (System.Exception ex)
{
throw ex;
}
finally
{
//断开连接
oleDbCmd.Dispose();
oleDbConn.Close();
oleDbConn.Dispose();
}
}
方法六：
复制代码 代码如下: