批量導出access某表內容到word文檔

2019-11-14 13:54:20

字體：大中小

來源：轉載

供稿：網友

一、需求：

需要將表中每一條記錄中的某些內容導出在一個Word文檔中，并將這些文檔保存在指定文件夾目錄下

二、界面，簡單設計如下：

三、添加office相關引用

添加后可在解決方案資源管理器中看到：

四、添加form1中的引用

using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using Microsoft.Office.Core;
using Word=Microsoft.Office.Interop.Word;
using System.Reflection;

五、窗體Form1中代碼如下：

using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Windows.Forms;using System.Data.OleDb;using System.Data.SqlClient;using System.IO;using Microsoft.Office.Core;using Word=Microsoft.Office.Interop.Word;using System.Reflection;using System.Threading;//線程需用，進程中namespace word{    delegate void ShowPRogressDelegate(int totalStep, int currentStep); //定義委托，異步調用    public partial class Form1 : Form    {        public Form1()        {            InitializeComponent();        }        public string filepath = "D://zjy//其他//NCTDCBJYQ04.mdb";   //數據庫所在位置設置        public string path;    //輸出路徑        private void Form1_Load(object sender, EventArgs e)        {            string sqlstr = "select OBJECTID,CBFBM,CBFMC from CBF";            //string sqlstr = "select * from CBF";            DataSet ds = accessDAO.getDataSetFromAccessTable(sqlstr, filepath);            this.dataGridView1.DataSource = ds.Tables[0].DefaultView;               dataGridView1.AllowUserToAddRows = false;        }        private void textBox1_MouseClick(object sender, MouseEventArgs e)//輸出路徑設置        {            FolderBrowserDialog dilog = new FolderBrowserDialog();            dilog.Description = "請選擇文件夾";            if (dilog.ShowDialog() == DialogResult.OK || dilog.ShowDialog() == DialogResult.Yes)            {                path = dilog.SelectedPath;                this.textBox1.Text = path;            }        }        object pathword;                      //聲明文件路徑變量        private void button2_Click(object sender, EventArgs e)    //批量輸出        {            ParameterizedThreadStart start = new ParameterizedThreadStart(SetProgress);            Thread progressThread = new Thread(start);            progressThread.IsBackground = true;//標記為后臺進程，在窗口退出時，正常退出            progressThread.Start();        }         /// <summary>        /// 刷新進度條        /// </summary>        /// <param name="totalStep"></param>        /// <param name="currentStep"></param>        void ShowProgress(int totalStep, int currentStep)        {            this.progressBar1.Maximum = totalStep;            this.progressBar1.Value = currentStep;            if (this.progressBar1.Value * 100 / progressBar1.Maximum != 100)            {                this.label2.Text = "當前輸出進度為：" + this.progressBar1.Value * 100 / progressBar1.Maximum + "%" + "    請耐心等待：)";            }            else if (this.progressBar1.Value * 100 / progressBar1.Maximum == 100)            {                this.label2.Text = "輸出結束!";            }        }        /// <summary>        /// 設置當前進度        /// </summary>        /// <param name="state"></param>        void SetProgress(object state)        {            if (this.textBox1.Text == "")            {                MessageBox.Show("請選擇文件輸出路徑", "提示");            }            else            {                for (int i = 0; i < this.dataGridView1.Rows.Count; i++)   //遍歷獲取table中需要的值，并分別創建word文檔                {                    #region 打開進度條                    Thread.Sleep(1);                    object[] objs = new object[] { this.dataGridView1.RowCount, i+1 };                    //異步調用                    this.Invoke(new ShowProgressDelegate(ShowProgress), objs);                    #endregion                    #region 獲取word中需要添加的內容                    string dm = this.dataGridView1.Rows[i].Cells[1].Value.ToString();//承包方編碼                    string mc = this.dataGridView1.Rows[i].Cells[2].Value.ToString();//承包方名稱                    #endregion                    #region 創建word文檔，并將內容寫入word，并保存起來                    //初始化變量                    object Nothing = Missing.Value;                       //COM調用時用于占位                    object format = Word.WdSaveFormat.wdFormatDocument;   //Word文檔的保存格式                    Word.applicationClass wordApp = new Word.ApplicationClass();              //聲明一個wordAPP對象                    Word.Document worddoc = wordApp.Documents.Add(ref Nothing, ref Nothing, ref Nothing, ref Nothing);//新建一個word對象                    //向文檔中寫入內容                    string wordstr = "承包方代碼:" + dm + "/n" + "承包方名稱:" + mc;                    worddoc.Paragraphs.Last.Range.Text = wordstr;                    //保存文檔                             pathword = path + "//" + dm;   //設置文件保存路徑                    worddoc.SaveAs(ref pathword, ref format, ref Nothing, ref Nothing,                        ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing,                        ref Nothing, ref Nothing, ref Nothing, ref Nothing,                        ref Nothing, ref Nothing, ref Nothing);                    //關閉文檔                    worddoc.Close(ref Nothing, ref Nothing, ref Nothing);  //關閉worddoc文檔對象                    wordApp.Quit(ref Nothing, ref Nothing, ref Nothing);   //關閉wordApp組對象                    #endregion                }                MessageBox.Show("文檔創建成功!","提示");            }                  }                           }}

六、讀取數據庫中表需要的數據庫類AccessDAO.cs代碼如下：

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data;using System.Data.OleDb;using System.Data.SqlClient;using System.Text.RegularExpressions; //正則表達式引用所需namespace word{    //access的數據訪問接口    class AccessDAO    {        public static class Property        {            public static string accessFilePath = "d://nCTDCBJYQ04DataSet.mdb";            //若放入主程序，則可如下設置            //one mainFrm = (one)this.Owner;            //string prjName = mainFrm.laPrj.Text;            //string prjPath = mainFrm.laFile_Path.Text;            // public static string accessFilePath = prjPath + "http://矢量數據//" + prjName + ".mdb";        }        //從access數據庫獲取數據        //dataFilePath指定access文件的路徑        //sql指定數據庫的查詢語句        //DataSet為查詢返回的數據集        public static DataSet getDataSetFromAccessTable(string sql, string dataFilePath)        {            // 連接數據庫             OleDbConnection connct = new OleDbConnection();            string oleDB = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dataFilePath;            connct.ConnectionString = oleDB;            //創建命令            OleDbCommand command = new OleDbCommand(sql, connct);            //打開數據庫            connct.Open();            //執行命令            DataSet dataSet = new DataSet();            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(command);            dataAdapter.Fill(dataSet);            // 關閉連接             connct.Close();            return dataSet;        }        //更新或者插入數據到access數據庫        //dataFilePath指定access文件的路徑        //sql指定數據庫的更新或者插入語句        //返回值int表示此次更新影響的行數        public static int updateAccessTable(string sql, string dataFilePath)        {            // 連接數據庫             OleDbConnection connct = new OleDbConnection();            string oleDB = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dataFilePath;            connct.ConnectionString = oleDB;            //打開數據庫            connct.Open();            //執行命令            OleDbCommand myCommand = new OleDbCommand(sql, connct);            int res = myCommand.ExecuteNonQuery();            // 關閉連接             connct.Close();            return res;        }        //更新或者插入數據到access數據庫        //dataFilePath指定access文件的路徑        //command指定操作（更新或者插入）數據庫的命令        //返回值int表示此次更新影響的行數        public static int updateAccessTable(OleDbCommand command, string dataFilePath)        {            // 連接數據庫             OleDbConnection connct = new OleDbConnection();            string oleDB = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dataFilePath;            connct.ConnectionString = oleDB;            //打開數據庫            connct.Open();            //執行命令            //OleDbCommand myCommand = new OleDbCommand(sql, connct);            command.Connection = connct;            int res = command.ExecuteNonQuery();            // 關閉連接             connct.Close();            return res;        }        public bool ckDigital_Num(string digitalItem, int digitalNum)                //正則檢查是否為數字,且位數一定        {            bool isDigital_Num = false;            Regex reGen = new Regex(@"^/d{" + digitalNum.ToString("F0") + "}$");                 //正則表達式,n位數字            if (reGen.IsMatch(digitalItem))                isDigital_Num = true;            return isDigital_Num;        }    }}