//
把DataTable导出为纯Excle文件
// 参数:DataTable, 文件服务器端物理全路径,每个sheet最多行数,Excel2003及以前版本最多允许6万5千块。
public static bool ExportToExcel(DataTable dt, string filename, int RowsPerSheet)
{
string strConn = " Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " ;
strConn = strConn + filename + " ; " ;
strConn = strConn + " Extended Properties=Excel 8.0; " ;
string strSql = "" ;
System.Data.OleDb.OleDbConnection conn = null ;
int intSheet = 0 ;
int intCounts = 0 ;
try
{
conn = new System.Data.OleDb.OleDbConnection(strConn);
conn.Open();
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
cmd.Connection = conn;
System.Data.OleDb.OleDbDataAdapter oda = new System.Data.OleDb.OleDbDataAdapter(cmd);
oda.InsertCommand = cmd;
DataTable dtTmp = dt.Clone();
string strColDef = "" ;
foreach (DataColumn dc in dt.Columns)
{
if (dc.DataType == typeof (Decimal))
strColDef += (strColDef.Equals( "" ) ? "" : " , " ) + " [ " + dc.ColumnName + " ] " + " Numeric " ;
else if (dc.DataType == typeof (DateTime))
strColDef += (strColDef.Equals( "" ) ? "" : " , " ) + " [ " + dc.ColumnName + " ] " + " DateTime " ;
else
strColDef += (strColDef.Equals( "" ) ? "" : " , " ) + " [ " + dc.ColumnName + " ] " + " VarChar " ;
}
foreach (DataRow dr in dt.Rows)
{
if (intCounts == 0 )
{
#region add Excel sheet
cmd.Parameters.Clear();
// 新增Excel工作表
intSheet += 1 ;
strSql = " Create Table [Sheet " + intSheet.ToString() + " ] " ;
strSql += " ( " + strColDef + " ) " ;
cmd.CommandText = strSql;
cmd.ExecuteNonQuery();
#endregion
#region Insert SQL
oda.InsertCommand.Parameters.Clear();
// Insert SQL
strSql = "" ;
foreach (DataColumn dc in dt.Columns)
{
strSql += (strSql.Equals( "" ) ? " Insert Into [Sheet " + intSheet.ToString() + " $] Values( " : " , " ) + " ? " ;
if (dc.DataType == typeof (Decimal))
oda.InsertCommand.Parameters.Add( " @ " + dc.ColumnName, System.Data.OleDb.OleDbType.Numeric);
else if (dc.DataType == typeof (DateTime))
oda.InsertCommand.Parameters.Add( " @ " + dc.ColumnName, System.Data.OleDb.OleDbType.Date);
else
oda.InsertCommand.Parameters.Add( " @ " + dc.ColumnName, System.Data.OleDb.OleDbType.VarChar);
oda.InsertCommand.Parameters[ " @ " + dc.ColumnName].SourceColumn = dc.ColumnName;
}
strSql += " ) " ;
oda.InsertCommand.CommandText = strSql;
#endregion
}
dtTmp.Rows.Add(dr.ItemArray);
intCounts += 1 ;
if (intCounts == RowsPerSheet)
{
intCounts = 0 ;
oda.Update(dtTmp); // Insert Data to excel
dtTmp.Rows.Clear();
}
}
if (dtTmp.Rows.Count > 0 )
oda.Update(dtTmp); // Insert Data to excel
return true ;
}
catch (Exception ex)
{
return false ;
}
finally
{
if (conn != null )
{
conn.Close();
}
}
}
public static bool ExportToExcel(DataSet ds, string filename, bool sheetNmaeIsTableName)
{
string strConn = " Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " ;
strConn = strConn + filename + " ; " ;
strConn = strConn + " Extended Properties=Excel 8.0; " ;
string strSql = "" ;
System.Data.OleDb.OleDbConnection conn = null ;
int intSheet = 0 ;
string strSheetName = "" ;
try
{
conn = new System.Data.OleDb.OleDbConnection(strConn);
conn.Open();
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
cmd.Connection = conn;
System.Data.OleDb.OleDbDataAdapter oda = new System.Data.OleDb.OleDbDataAdapter(cmd);
oda.InsertCommand = cmd;
foreach (DataTable dt in ds.Tables)
{
DataTable dtTmp = dt.Clone();
string strColDef = "" ;
foreach (DataColumn dc in dt.Columns)
{
if (dc.DataType == typeof (Decimal))
strColDef += (strColDef.Equals( "" ) ? "" : " , " ) + " [ " + dc.ColumnName + " ] " + " Numeric " ;
else if (dc.DataType == typeof (DateTime))
strColDef += (strColDef.Equals( "" ) ? "" : " , " ) + " [ " + dc.ColumnName + " ] " + " DateTime " ;
else
strColDef += (strColDef.Equals( "" ) ? "" : " , " ) + " [ " + dc.ColumnName + " ] " + " VarChar " ;
}
#region add Excel sheet
cmd.Parameters.Clear();
// 新增Excel工作表
if (sheetNmaeIsTableName)
{
strSql = " Create Table [ " + dt.TableName + " ] " ;
strSheetName = dt.TableName;
}
else
{
intSheet += 1 ;
strSql = " Create Table [Sheet " + intSheet.ToString() + " ] " ;
strSheetName = " Sheet " + intSheet.ToString();
}
strSql += " ( " + strColDef + " ) " ;
cmd.CommandText = strSql;
cmd.ExecuteNonQuery();
#endregion
#region Insert SQL
oda.InsertCommand.Parameters.Clear();
// Insert SQL
strSql = "" ;
foreach (DataColumn dc in dt.Columns)
{
strSql += (strSql.Equals( "" ) ? " Insert Into [ " + strSheetName + " $] Values( " : " , " ) + " ? " ;
if (dc.DataType == typeof (Decimal))
oda.InsertCommand.Parameters.Add( " @ " + dc.ColumnName, System.Data.OleDb.OleDbType.Numeric);
else if (dc.DataType == typeof (DateTime))
oda.InsertCommand.Parameters.Add( " @ " + dc.ColumnName, System.Data.OleDb.OleDbType.Date);
else
oda.InsertCommand.Parameters.Add( " @ " + dc.ColumnName, System.Data.OleDb.OleDbType.VarChar);
oda.InsertCommand.Parameters[ " @ " + dc.ColumnName].SourceColumn = dc.ColumnName;
}
strSql += " ) " ;
oda.InsertCommand.CommandText = strSql;
#endregion
foreach (DataRow dr in dt.Rows)
{
dtTmp.Rows.Add(dr.ItemArray);
}
if (dtTmp.Rows.Count > 0 )
oda.Update(dtTmp); // Insert Data to excel
}
return true ;
}
catch (Exception ex)
{
return false ;
}
finally
{
if (conn != null )
{
conn.Close();
}
}
}
// 参数:DataTable, 文件服务器端物理全路径,每个sheet最多行数,Excel2003及以前版本最多允许6万5千块。
public static bool ExportToExcel(DataTable dt, string filename, int RowsPerSheet)
{
string strConn = " Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " ;
strConn = strConn + filename + " ; " ;
strConn = strConn + " Extended Properties=Excel 8.0; " ;
string strSql = "" ;
System.Data.OleDb.OleDbConnection conn = null ;
int intSheet = 0 ;
int intCounts = 0 ;
try
{
conn = new System.Data.OleDb.OleDbConnection(strConn);
conn.Open();
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
cmd.Connection = conn;
System.Data.OleDb.OleDbDataAdapter oda = new System.Data.OleDb.OleDbDataAdapter(cmd);
oda.InsertCommand = cmd;
DataTable dtTmp = dt.Clone();
string strColDef = "" ;
foreach (DataColumn dc in dt.Columns)
{
if (dc.DataType == typeof (Decimal))
strColDef += (strColDef.Equals( "" ) ? "" : " , " ) + " [ " + dc.ColumnName + " ] " + " Numeric " ;
else if (dc.DataType == typeof (DateTime))
strColDef += (strColDef.Equals( "" ) ? "" : " , " ) + " [ " + dc.ColumnName + " ] " + " DateTime " ;
else
strColDef += (strColDef.Equals( "" ) ? "" : " , " ) + " [ " + dc.ColumnName + " ] " + " VarChar " ;
}
foreach (DataRow dr in dt.Rows)
{
if (intCounts == 0 )
{
#region add Excel sheet
cmd.Parameters.Clear();
// 新增Excel工作表
intSheet += 1 ;
strSql = " Create Table [Sheet " + intSheet.ToString() + " ] " ;
strSql += " ( " + strColDef + " ) " ;
cmd.CommandText = strSql;
cmd.ExecuteNonQuery();
#endregion
#region Insert SQL
oda.InsertCommand.Parameters.Clear();
// Insert SQL
strSql = "" ;
foreach (DataColumn dc in dt.Columns)
{
strSql += (strSql.Equals( "" ) ? " Insert Into [Sheet " + intSheet.ToString() + " $] Values( " : " , " ) + " ? " ;
if (dc.DataType == typeof (Decimal))
oda.InsertCommand.Parameters.Add( " @ " + dc.ColumnName, System.Data.OleDb.OleDbType.Numeric);
else if (dc.DataType == typeof (DateTime))
oda.InsertCommand.Parameters.Add( " @ " + dc.ColumnName, System.Data.OleDb.OleDbType.Date);
else
oda.InsertCommand.Parameters.Add( " @ " + dc.ColumnName, System.Data.OleDb.OleDbType.VarChar);
oda.InsertCommand.Parameters[ " @ " + dc.ColumnName].SourceColumn = dc.ColumnName;
}
strSql += " ) " ;
oda.InsertCommand.CommandText = strSql;
#endregion
}
dtTmp.Rows.Add(dr.ItemArray);
intCounts += 1 ;
if (intCounts == RowsPerSheet)
{
intCounts = 0 ;
oda.Update(dtTmp); // Insert Data to excel
dtTmp.Rows.Clear();
}
}
if (dtTmp.Rows.Count > 0 )
oda.Update(dtTmp); // Insert Data to excel
return true ;
}
catch (Exception ex)
{
return false ;
}
finally
{
if (conn != null )
{
conn.Close();
}
}
}
public static bool ExportToExcel(DataSet ds, string filename, bool sheetNmaeIsTableName)
{
string strConn = " Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " ;
strConn = strConn + filename + " ; " ;
strConn = strConn + " Extended Properties=Excel 8.0; " ;
string strSql = "" ;
System.Data.OleDb.OleDbConnection conn = null ;
int intSheet = 0 ;
string strSheetName = "" ;
try
{
conn = new System.Data.OleDb.OleDbConnection(strConn);
conn.Open();
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
cmd.Connection = conn;
System.Data.OleDb.OleDbDataAdapter oda = new System.Data.OleDb.OleDbDataAdapter(cmd);
oda.InsertCommand = cmd;
foreach (DataTable dt in ds.Tables)
{
DataTable dtTmp = dt.Clone();
string strColDef = "" ;
foreach (DataColumn dc in dt.Columns)
{
if (dc.DataType == typeof (Decimal))
strColDef += (strColDef.Equals( "" ) ? "" : " , " ) + " [ " + dc.ColumnName + " ] " + " Numeric " ;
else if (dc.DataType == typeof (DateTime))
strColDef += (strColDef.Equals( "" ) ? "" : " , " ) + " [ " + dc.ColumnName + " ] " + " DateTime " ;
else
strColDef += (strColDef.Equals( "" ) ? "" : " , " ) + " [ " + dc.ColumnName + " ] " + " VarChar " ;
}
#region add Excel sheet
cmd.Parameters.Clear();
// 新增Excel工作表
if (sheetNmaeIsTableName)
{
strSql = " Create Table [ " + dt.TableName + " ] " ;
strSheetName = dt.TableName;
}
else
{
intSheet += 1 ;
strSql = " Create Table [Sheet " + intSheet.ToString() + " ] " ;
strSheetName = " Sheet " + intSheet.ToString();
}
strSql += " ( " + strColDef + " ) " ;
cmd.CommandText = strSql;
cmd.ExecuteNonQuery();
#endregion
#region Insert SQL
oda.InsertCommand.Parameters.Clear();
// Insert SQL
strSql = "" ;
foreach (DataColumn dc in dt.Columns)
{
strSql += (strSql.Equals( "" ) ? " Insert Into [ " + strSheetName + " $] Values( " : " , " ) + " ? " ;
if (dc.DataType == typeof (Decimal))
oda.InsertCommand.Parameters.Add( " @ " + dc.ColumnName, System.Data.OleDb.OleDbType.Numeric);
else if (dc.DataType == typeof (DateTime))
oda.InsertCommand.Parameters.Add( " @ " + dc.ColumnName, System.Data.OleDb.OleDbType.Date);
else
oda.InsertCommand.Parameters.Add( " @ " + dc.ColumnName, System.Data.OleDb.OleDbType.VarChar);
oda.InsertCommand.Parameters[ " @ " + dc.ColumnName].SourceColumn = dc.ColumnName;
}
strSql += " ) " ;
oda.InsertCommand.CommandText = strSql;
#endregion
foreach (DataRow dr in dt.Rows)
{
dtTmp.Rows.Add(dr.ItemArray);
}
if (dtTmp.Rows.Count > 0 )
oda.Update(dtTmp); // Insert Data to excel
}
return true ;
}
catch (Exception ex)
{
return false ;
}
finally
{
if (conn != null )
{
conn.Close();
}
}
}