Batch export access content of a table to Word documents and export accessword documents
I. Requirements:
You need to export some content in each record of the table to a Word document and save these documents in the specified folder directory.
2. The interface is designed as follows:
3. Add office references
After adding the package, you can see it in Solution Explorer:
4. Add reference in 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;
5. The code in form Form1 is as follows:
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; // thread required. namespace word {delegate void S in the process HowProgressDelegate (int totalStep, int currentStep); // defines the delegate and asynchronously calls public partial class Form1: Form {public Form1 () {InitializeComponent ();} public string filepath = "D: \ zjy \ others \ NCTDCBJYQ04.mdb "; // set public string path for the database location; // output path private void Form1_Load (object sender, EventArgs e) {string sqlstr = "select OBJECTID, CBFBM, CBFMC from CVR"; // string sqlstr = "select * from CBR"; DataSet ds = AccessDAO. getDataSetFromAccessTable (sqlstr, filepath); this. dataGridView1.DataSource = ds. tables [0]. defaultView; dataGridView1.AllowUserToAddRows = false;} private void textbox#mouseclick (object sender, MouseEventArgs e) // set the output path {FolderBrowserDialog dilog = new FolderBrowserDialog (); dilog. description = "select folder"; if (dilog. showDialog () = DialogResult. OK | dilog. showDialog () = DialogResult. Y Es) {path = dilog. selectedPath; this. textBox1.Text = path ;}} object pathword; // declare the file path variable private void button2_Click (object sender, EventArgs e) // batch output {ParameterizedThreadStart start = new ParameterizedThreadStart (SetProgress ); thread progressThread = new Thread (start); progressThread. isBackground = true; // mark it as a background process. When the window exits, it exits sssthread normally. start () ;}/// <summary> /// refresh progress bar /// </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 = "current output progress:" + this. progressBar1.Value * 100/progressBar1.Maximum + "%" + "please be patient :)";} else if (this. progressBar1.Value * 100/progressBar1.Maximum = 100) {this. label2.Text = "output ends! ";}} /// <Summary> /// set the current progress /// </summary> /// <param name = "state"> </param> void SetProgress (object state) {if (this. textBox1.Text = "") {MessageBox. show ("select file output path", "prompt") ;}else {for (int I = 0; I <this. dataGridView1.Rows. count; I ++) // traverse to obtain the values required in the table and create Word documents {# region to open the progress bar Thread. sleep (1); object [] objs = new object [] {this. dataGridView1.RowCount, I + 1}; // call this asynchronously. invoke (new Sho WProgressDelegate (ShowProgress), objs); # endregion # region get the content to be added in word string dm = this. dataGridView1.Rows [I]. cells [1]. value. toString (); // The Contractor's code string mc = this. dataGridView1.Rows [I]. cells [2]. value. toString (); // contractor name # endregion # region create a word document, write the content to the word, and save it. // initialize the variable object Nothing = Missing. value; // COM is used for placeholder object format = Word. wdSaveFormat. wdFormatDocument; // Word file storage format Word. application Class wordApp = new Word. applicationClass (); // declare a wordAPP object Word. document worddoc = wordApp. documents. add (ref Nothing, ref Nothing); // create a word object // write the content to the document string wordstr = "code: "+ dm +" \ n "+" contractor name: "+ mc; worddoc. paragraphs. last. range. text = wordstr; // Save the document pathword = path + "\" + dm; // set the file storage path worddoc. saveAs (ref pathword, ref format, ref Nothing, ref Nothing, re F Nothing, ref Nothing, ref Nothing ); // close the worddoc. close (ref Nothing, ref Nothing, ref Nothing); // Close the worddoc object wordApp. quit (ref Nothing, ref Nothing, ref Nothing); // close the wordApp Group Object # endregion} MessageBox. show ("document created successfully! "," Prompt ");}}}}
6. The database AccessDAO. cs Code required to read tables in the database is as follows:
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; // Regular Expression references the required namespace word {// access data access interface class AccessDAO {public static class Property {public static string accessFilePath = "d: \ nCTDCBJYQ04DataSet. mdb "; // if it is placed into the main program, you can set it as follows // one mainFrm = (one) this. owner; // string prjName = mainFrm. laPrj. text; // string prjPath = mainFrm. laFile_Path.Text; // public static string accessFilePath = prjPath + "\ Vector Data \" + prjName + ". mdb ";} // obtain data from the access Database // dataFilePath specifies the path of the access File // SQL specifies the query statement of the database // DataSet is the public static DataSet getDataSetFromAccessTable (string SQL, string dataFilePath) {// Connection database OleDbConnection connct = new OleDbConnection (); string oleDB = "Provider = Microsoft. jet. OLEDB.4.0; Data Source = "+ dataFilePath; connct. connectionString = oleDB; // CREATE command OleDbCommand command = new OleDbCommand (SQL, connct); // Open Database connct. open (); // run the DataSet dataSet = new DataSet (); OleDbDataAdapter dataAdapter = new OleDbDataAdapter (command); dataAdapter. fill (dataSet); // close the connection connct. close (); return dataSet ;} // update or insert data to the access database // dataFilePath specifies the path of the access File // SQL specifies the update or insert statement of the database // return value int indicates the number of rows affected by this update public static int updateAccessTable (string SQL, string dataFilePath) {// Connection database OleDbConnection connct = new OleDbConnection (); string oleDB = "Provider = Microsoft. jet. OLEDB.4.0; Data Source = "+ dataFilePath; connct. connectionString = oleDB; // open the database connct. open (); // run OleDbCommand myCommand = new OleDbCommand (SQL, connct); int res = myCommand. executeNonQuery (); // close the connection connct. close (); return res;} // update or insert data to the access database // dataFilePath specifies the path of the access File // command specifies the operation (update or insert) DATABASE command // return value int indicates the number of rows affected by this update public static int updateAccessTable (OleDbCommand command, string dataFilePath) {// Connection database OleDbConnection connct = new OleDbConnection (); string oleDB = "Provider = Microsoft. jet. OLEDB.4.0; Data Source = "+ dataFilePath; connct. connectionString = oleDB; // open the database connct. open (); // run the command // OleDbCommand myCommand = new OleDbCommand (SQL, connct); command. connection = connct; int res = command. executeNonQuery (); // close the connection connct. close (); return res;} public bool ckDigital_Num (string digitalItem, int digitalNum) // check whether the regular expression is a number and the number of digits must be {bool isDigital_Num = false; regex reGen = new Regex (@ "^ \ d {" + digitalNum. toString ("F0") + "} $"); // regular expression, n-digit if (reGen. isMatch (digitalItem) isDigital_Num = true; return isDigital_Num ;}}}
OK. Now you can export the data into WORD Documents in batches.