|
?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;
namespace WindowsApplication1
{
??? public partial 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("origin"); //打开数据链接，开启导入函数???????????????????
??????????????? //}//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 + 2,1] = NetId;
??????????????????? excel.Cells[i + 2,2] = MemId;
??????????????????? excel.Cells[i + 2,3] = CallNumber;
??????????????????? excel.Cells[i + 2,4] = CompanyName;
??????????????????? excel.Cells[i + 2,5] = Worker;
??????????????????? excel.Cells[i + 2,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
??????
??????? /*数据库连接函数*/
??????? public static SqlConnection creCon()
??????? {
??????????? string sql = "server=127.0.0.1;uid=sa;pwd=123456789;database=test";
??????????? SqlConnection con = new SqlConnection(sql);
??????????? return con;
??????? }
??????? /*主函数*/
??????? [STAThread]
??????? static void Main()
??????? {
??????????? new IntroduceExcel();
??????? }
??? }
}
?
?
?
?
?
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;
namespace WindowsApplication1
{
??? /*文件操作类定义*/
??? 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类
}
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
