/*注意：測試過程中將執行時間設為非月末，此類放在實際中需要改成月末。改變方法是把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類
}