#region SQL数据库导入到Excel表中(运用ADO.NET)
/// <summary>
/// 单表导入Excel单页中
/// </summary>
/// <param name="dtname">表名</param>
/// <param name="index"></param>
public static void ExportDataByQueryTableA(string dtname,int index,Label msg)
{
long totalCount=0;
long rowRead=0;
float percent=0;
DataTable dt=new DataTable ();
string cmdStr=String.Format ("select * from {0}",dtname);
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath +";Extended Properties=Excel 8.0;";
OleDbConnection objConn = new OleDbConnection(connString);
OleDbCommand objCmd = new OleDbCommand();
objCmd.Connection = objConn;
objConn.Open();
if(dtname.Equals ("Tran_list1"))//将表[Tran_list1]记录导出
{
cmdStr = String.Format ("select Tbname,Fldname from {0}",dtname);
dt=dataTrans.GetDataBySQL (cmdStr);
totalCount=dt.Rows .Count ;
OleDbParameter[] parm=new OleDbParameter[2];
objCmd.CommandText = @"CREATE TABLE Sheet1(Tbname varchar,Fldname varchar)";
objCmd.ExecuteNonQuery();
objCmd.CommandText = "INSERT INTO Sheet1(Tbname,Fldname) VALUES(@aa,@bb)";
parm[0]=new OleDbParameter("@aa", OleDbType.VarChar);
objCmd.Parameters.Add(parm[0]);
parm[1]=new OleDbParameter("@bb", OleDbType.VarChar);
objCmd.Parameters.Add(parm[1]);
//遍历DataTable将数据插入新建的Excel文件中
for(int i=0;i<dt.Rows.Count;i++)
{
for(int j=0;j<parm.Length;j++)
{
if(dt.Rows [i][j]!=null)
{
parm[j].Value =dt.Rows[i][j];
}
}
objCmd.ExecuteNonQuery();
}
}
else
{
dt=dataTrans.GetDataBySQL (cmdStr);
totalCount=dt.Rows .Count ;
string [] val=dtname.Split(new char []{'_'});
OleDbParameter[] parm=new OleDbParameter[3];
//建立表结构
objCmd.CommandText = String.Format ("CREATE TABLE {0}({1} text,{2}_en text,myid Integer)" ,val[1]+"_"+val[2],val[2],val[2]);
objCmd.ExecuteNonQuery();
//建立插入动作的Command
objCmd.CommandText =String.Format ( "INSERT INTO {0}({1},{2}_en,myid) VALUES(@aa,@bb,@cc)",val[1]+"_"+val[2],val[2],val[2]);
parm[0]=new OleDbParameter("@aa", OleDbType.LongVarChar);
objCmd.Parameters.Add(parm[0]);
parm[1]=new OleDbParameter("@bb", OleDbType.LongVarChar);
objCmd.Parameters.Add(parm[1]);
parm[2]=new OleDbParameter("@cc", OleDbType.Integer);
objCmd.Parameters.Add(parm[2]);
//遍历DataTable将数据插入新建的Excel文件中
for(int i=0;i<dt.Rows.Count;i++)
{
for(int j=0;j<parm.Length;j++)
{
if(dt.Rows [i][j]!=null)
{
parm[j].Value =dt.Rows[i][j];
}
}
try
{
objCmd.ExecuteNonQuery();
rowRead++;
percent=((float)(100*rowRead))/totalCount;
msg.Text =String.Format ("正在导出第{0}个表的数据,已导出[{1}%]...",index,percent.ToString("0.00"));
msg.Refresh ();
}
catch(Exception ex)
{
Log.StrFileName ="Sql导出数据到Excel";
Log.StrDepartment ="ADO.NET导出数据到Excel";
Log.StrDescription =ex.ToString ();
Log.WriteLog ();
continue;
}
}
}
objConn.Close();
}
/// <summary>
/// 多表导出到Excel中
/// </summary>
/// <param name="dt"></param>
public static void ExportDataByQueryTableA(DataTable dt,Label msg)
{
//----------------------------datatabale排序,使得第一个表单是索引表---------------------
DataTable newdt = new DataTable();
newdt=dt.Clone();
DataRow[] dr1=dt.Select ("[name]='Tran_list1'");
DataRow[] drs=dt.Select ("[name]<>'Tran_list1'");
for(int i=0;i<dr1.Length ;i++)
{
newdt.ImportRow((DataRow)dr1[i]);
}
for(int i=0;i<drs.Length;i++)
{
newdt.ImportRow((DataRow)drs[i]);
}
//---------------------------------------------------------------------------------------
for(int i=0;i<newdt.Rows .Count ;i++)
{
ExportDataByQueryTableA(newdt.Rows [i][0].ToString (),i,msg);
}
}
public static void ExportToExcelA(Label msg)
{
ArrayList Tlist=new ArrayList ();
StringBuilder sb=new StringBuilder ();
sb.Append("select ta.[name],tb.[rows] from [sysobjects] ta inner join [sysindexes] tb on ta.id = tb.id where (ta.[name] like '%NeedTran' or ta.[name]='Tran_list1') and ta.xtype='U' and tb.[rows]>0");
DataTable dt=new DataTable ();
dt=dataTrans.GetDataBySQL(sb.ToString ());
ExportDataByQueryTableA(dt,msg);
MessageBox.Show ("数据已经成功导出!");
}
#endregion