/*注意:测试过程中将执行时间设为非月末,此类放在实际中需要改成月末。改变方法是把16行的==变为!=*/ using System; using System.Data; using System.Data.SqlClient; using System.Data.OleDb ; using System.Windows.Forms; using System.Text; using System.IO; using System.Reflection; /*SQL server 2000中的电话记录以及信息发布记录打入数据导入excel文件*/ public class IntroduceExcel:System.Windows.Forms.Form { public IntroduceExcel ( ) { DateTime today = System.DateTime.Today;//获取当前时间 if (today.Day==DateTime.DaysInMonth(today.Year,today.Month)) {//如果并非月底,不执行导入 return; } else//否则执行导入 if (MessageBox.Show("点击确定开始导入,点击否可日后手动导入\n ", "月末电话记录,信息发布记录导入Excel程序自动开启", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) == DialogResult.No) { return; }//if else { GetConnect ( "CallRecord") ; //打开数据链接,开启导入函数 GetConnect ( "PubInfRecord") ; DeleteRecord("CallRecord");//清空本月的数据 DeleteRecord("PubInfRecord"); }//else } /*exel文件导入函数*/ private void GetConnect (string origin) { SqlConnection con=creCon();//创建一个SQL 2000数据库链接 con.Open(); string sql="select * from "+origin+" order by NetId"; //查询数据库 SqlDataAdapter sa=new SqlDataAdapter(sql,con); DataSet ds=new DataSet(); sa.Fill(ds,origin); //填充数据 try { Excel.Application excel = new Excel.Application ( ) ; //开启excel excel.Application.Workbooks.Add ( true ); Excel.Sheets ExcelSheets = excel.Worksheets; //建立一个新的工作表 excel.Cells[ 1 , 1 ] ="NetId(网号)"; excel.Cells[ 1, 2 ] ="MemId(成员号)"; excel.Cells[ 1 , 3 ] ="CurCallNum(当前打入电话)"; excel.Cells[ 1 , 4 ] ="CompanyName(公司名)"; excel.Cells[ 1 , 5 ] ="UpWorker(坐席人员)"; excel.Cells[ 1 , 6 ] ="SumNumber(本月次数统计)"; object missing=Missing.Value; excel.Visible =true ; //excel文件可见 int RoLength=ds.Tables[0].Rows.Count; //行数 int i; for (i=0;i<RoLength;i++) { /*从数据库中取出电话相关信息*/ string NetId=ds.Tables[0].Rows[i][0].ToString(); string MemId=ds.Tables[0].Rows[i][1].ToString(); string CallNumber=ds.Tables[0].Rows[i][2].ToString(); string CompanyName=ds.Tables[0].Rows[i][3].ToString(); string Worker=ds.Tables[0].Rows[i][4].ToString(); string Number=ds.Tables[0].Rows[i][5].ToString(); /*填充到excel的工作表中*/ excel.Cells[ i+1 , 1 ] =NetId; excel.Cells[ i+1, 2 ] =MemId; excel.Cells[ i+1 , 3 ] =CallNumber; excel.Cells[ i+1 , 4 ] =CompanyName; excel.Cells[ i+1 , 5 ] =Worker; excel.Cells[ i+1 , 6 ] =Number; }//for con.Close();//关闭此数据链接
/*文件保存对话框,文件保存采用了一个file自定义类*/ SaveFileDialog saveFileDialog=new SaveFileDialog(); saveFileDialog.Filter= "Excel files(*.xls)|*.xls|All files(*.*)|*.*" ; if(origin=="CallRecord") { saveFileDialog.Title="月末电话数据导入Excel"; } else saveFileDialog.Title="月末信息发布数据导入Excel"; saveFileDialog.FilterIndex=1; saveFileDialog.RestoreDirectory=true; if(saveFileDialog.ShowDialog()==DialogResult.OK) { string fName=saveFileDialog.FileName; File fSaveAs=new File(fName); fSaveAs.WriteFile(fName);
}//if() excel.Workbooks.Close(); excel.Quit();//关闭excel程序 }//try catch(System.Exception e) {
System.Console.WriteLine("something wrong happened about excel excution or dababase operation ",e); } }//connect /*清空当前表内容*/ private void DeleteRecord(string record) { SqlConnection connection=creCon(); connection.Open(); string DeleteString="delete from "+record; SqlCommand deleteCommand=new SqlCommand(DeleteString,connection); deleteCommand.ExecuteNonQuery(); connection.Close(); }
/*数据库连接函数*/ public static SqlConnection creCon() { string sql="server=127.0.0.1;uid=sa;pwd=;database=zhaoxia"; SqlConnection con=new SqlConnection (sql); return con; } /*主函数*/ static void Main ( ) { new IntroduceExcel ( ) ; }
/*文件操作类定义*/ public class File { string fileName; public File(string fileName) { this.fileName=fileName; }
public string ReadFile() { try { StreamReader sr=new StreamReader(fileName,Encoding.Default); string result=sr.ReadToEnd(); sr.Close(); return result; } catch(Exception e){MessageBox.Show(e.Message);} return null; }
public void WriteFile(string str) { try { StreamWriter sw=new StreamWriter(fileName,false,Encoding.Default); sw.Write(str); sw.Close(); } catch(Exception e){MessageBox.Show(e.Message,"保存文件出错!");} } }//file类
}
|
请发表评论