sqlserver导入Excel

最新推荐文章于 2024-04-01 16:36:14 发布

anya

最新推荐文章于 2024-04-01 16:36:14 发布

阅读量1.2k

点赞数

文章标签： sqlserver string excel dataset exception 数据库

本文链接：https://blog.csdn.net/anya/article/details/3914751

版权

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类

}