How C # implements Excel import into SQL Server, how to implement export from SQL Server to Excel (verbose)

For importing and exporting excel from sqlserver, although sqlserver has given a relatively simple way to realize it through an interactive dialog box, but sometimes there are many problems in this way, such as importing and exporting data is incomplete. Moreover, for a project, we do not want the realization of the function to leave the software program. Therefore, we thought of using a program to import and export sqlserver.

1. Export excel sheet from sqlserver

    We will first save the detected data to the DataTable in the data table. Here I will not specifically explain how to find the result and store it in the DataTable. I believe there are many examples of implementation on the Internet.

     The next thing we do is to find the path to store this file, the code is as follows:

  This code means to see if there is a folder, if it exists, then check if there is a file, if there is no folder or file, it will be created.

 private void exportout_Click (object sender, EventArgs e)
        {
            Helper helper = new Helper (); // This is a class I wrote by myself, you don't need to look at it, just know that it is used to get the debug path. For the specific code, see my previous article: http://blog.csdn.net/ztzi321/article/details/44077563

            Global.filepath = helper.GetAssemblyFileDirectory () [email protected] "\ Excel";
            if (! Directory.Exists (Global.filepath))
            {
                Directory.CreateDirectory (Global.filepath);
            }
            saveFile = Global.filepath [email protected] "\ Basic Party Member Information Table.xlsx";
            if (! File.Exists (saveFile))
            {
                File.Create (saveFile);
            }
            Global.ExportExcel (partyMemberTable, Global.filepath); // Call the export excel function, partyMemberTable is the DataTable that stores the query results. Global is a global class that I have defined, which contains global variables and functions. Here I will also implement the export excel function.

        }
The specific implementation of the exported excel function is as follows:

  public static void ExportExcel (System.Data.DataTable dt, String saveFile)
        {
            object objectMissing;
            objectMissing = System.Reflection.Missing.Value; // Return a default value to objectMissing
            if (dt == null || dt.Rows.Count == 0) return;
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application (); // Create an Excel application.

            if (xlApp == null)
            {
                return;
            }
            //System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
            //System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo ("en-US");
            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks; // Create a workbook collection object.
            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add (objectMissing); // Create a new workbook
            Microsoft.Office.Interop.Excel.Worksheet worksheet = workbook.Worksheets [1] as Microsoft.Office.Interop.Excel.Worksheet; // Create a worksheet object.
            Microsoft.Office.Interop.Excel.Range range; // Create a range object of excel form.
            long totalCount = dt.Rows.Count; // Get the number of rows of exported data
            long rowRead = 0;
            float percent = 0;
This code is the content of the table title column
            for (int i = 0; i <dt.Columns.Count; i ++)
            {
                worksheet.Cells [1, i + 1] = dt.Columns [i] .ColumnName; // The rows and columns of excel start from 1
                range = (Microsoft.Office.Interop.Excel.Range) worksheet.Cells [1, i + 1];
                range.Interior.ColorIndex = 15; // font size 15
                range.Font.Bold = true; // Bold
            }
This code is to import the contents of the data table into the excel table, so it starts from the second line
            for (int r = 0; r <dt.Rows.Count; r ++)
            {
                for (int i = 0; i <dt.Columns.Count; i ++)
                {
                    worksheet.Cells [r + 2, i + 1] = dt.Rows [r] [i] .ToString ();
                }
                rowRead ++;
                percent = ((float) (100 * rowRead)) / totalCount;
            }
           // xlApp.Visible = true;
            try
            {// Because we have created an excel sheet before, but there is no data before, so we need to save it. The usage of save is introduced on msdn. "When saving Excel for the first time, it means using SaveAs to save the file. For the specific parameter meanings below, please refer to the WookBool.SaveAs () method introduced by msdn"
                workbook.SaveAs (saveFile, objectMissing, objectMissing, objectMissing, objectMissing, objectMissing,
                        XlSaveAsAccessMode.xlShared, objectMissing, objectMissing, objectMissing, objectMissing, objectMissing);
               
            }
            catch (Exception ex)
            {
                MessageBox.Show ("There is an error:" + ex.ToString ());
            }
            MessageBox.Show ("Save successfully!");
           
            xlApp.Quit (); // Close the program.
            // xlApp.

            
        }

In this way, we have finished exporting the data in the database to excel.
Second, import the data from the Excel table to the sqlserver

     First, we need to make sure that the table column names in the excel table should be consistent with the table column names in the database.

     Here I first explain the principle of the implementation process: create a DataTable, first create the connection between the excel table and the DataTable, save the data in the excel table to the DataTable, and then use the sql statement to loop into the sqlserver.

     The specific implementation code is as follows:

Here I created a button to handle the click event of the button


private void exportin_Click (object sender, EventArgs e)
        {
            DataTable dt = new DataTable (); // Create a DataTable table, used to store the data read from the excel table
            OpenFileDialog open = new OpenFileDialog (); // Create an open file window class
            if (open.ShowDialog () == DialogResult.OK)
            {
                string fileName = open.FileName; // Get the selected file, here you can also use the filtering method to filter the file type.
                bind (dt, fileName); // Procedure function of importing data from excel table into DataTable
            }

        }
     Import data from Excel into DataTable
private void bind (DataTable table, string fileName)
        {
            / * 1. Database engine: microsoft.ace.oledb.12.0 and Microsoft.Jet.OLEDB.4.0
             * 2. Extended Properties: Extended properties, Excel 8.0 is above 07 to 03, Excel 12.0 is 07 and 10
             * 3. HDR = Yes, which means that the first line is the title, not used as data. If you use HDR = NO, it means that the first line is not the title, used as data. The system default is YES * 4. IMEX (IMport EXport mode) setting
IMEX has three modes:
0 is Export mode
1 is Import mode
2 is Linked mode (full update capabilities)
What I want to explain here is the IMEX parameter, because different modes represent different read and write behaviors:
When IMEX = 0, it is "export mode", the Excel file opened in this mode can only be used for "write" purpose.
When IMEX = 1, it is "import mode", the Excel file opened in this mode can only be used for "read" purpose.
When IMEX = 2, it is "link mode". The Excel file opened in this mode can support both "read" and "write" purposes.
                The meaning is as follows:
                0 --- output mode;
                1 --- input mode;
                2 ---- Link mode (full update capability)

            * /
            string strConn = "Provider = Microsoft.ACE.OLEDB.12.0;" +
                 "Data Source =" + fileName + ";" +
                 "Extended Properties = 'Excel 12.0; HDR = Yes; IMEX = 1'";
            // String sheetName = "Basic Party Member Information Sheet";
            String excelStr = String.Format ("select * from [Party member basic information table $]"); // Here is the sheet table name
            OleDbDataAdapter da = new OleDbDataAdapter (excelStr, strConn);
            DataSet ds = new DataSet (); // Create a data set, here you can see the data set contains multiple data tables
            try
            {
                da.Fill (ds); // Save the data in the excel table to the DataTable.
                table = ds.Tables [0];
                //this.dataGridView1.DataSource = dt;
            }
            catch (Exception err)
            {
                MessageBox.Show ("Operation failed!" + Err.ToString ());
            }
            
            foreach (DataRow dr in table.Rows)
            {
                 insertToSql (dr); // This function inserts rows of data in the data table table into sqlserver.
            }

        }
Insert the data in the data table into sqlserver
private void insertToSql (DataRow dataRow)
        {// 1, Data Source: database engine name, generally used (lcoal), if an instance is set, use. \ Instance name.
// 2, Initial Catalog: database name
// 3, User ID: user name
// 4 、 Password: password; others do not have too much explanation, you do n’t need to write
            string connString = @ "Data Source =. \ SQLSERVER2008R2; Initial Catalog = organization; Persist Security Info = False; User ID = sa; Password = 123; Min Pool Size = 10; Max Pool Size = 1000";
            SqlConnection conn = new SqlConnection (connString); // Create connection
            conn.Open (); // Open the connection
// Database SQL statement
            String sqlstring = "use Organization insert into partyMembers (memberID, memberName, sex, cardID, birthDate, nation, nativePlace, politicalStatus, education,"
                                 + "partyDate, orgID, branchID, depID, duty, beginYear, endYear, editor, editTime, parentOrg, audit, auditTime, status, remark)"
                                 + "values ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7} ',' {8} ',' {9} ', "
                                 + "'{10}', '{11}', '{12}', '{13}', {14}, {15}, '{16}', '{17}', '{18} ',' {19} ', "
                                 + "'{20}', '{21}', '{22}')";
            sqlstring = String.Format (sqlstring,
                Convert.ToString (dataRow ["memberID"]), Convert.ToString (dataRow ["memberName"]),
                                            Convert.ToString (dataRow ["sex"]), Convert.ToString (dataRow ["cardID"]),
                                          Convert.ToDateTime (dataRow ["birthDate"]). ToString ("yyyy-MM-dd"),
                                          Convert.ToString (dataRow ["nation"]), Convert.ToString (dataRow ["nativePlace"]),
                                            Convert.ToString (dataRow ["politicalStatus"]), Convert.ToString (dataRow ["education"]),
                                            Convert.ToDateTime (dataRow ["partyDate"]). ToString ("yyyy-MM-dd"),
                                            Convert.ToString (dataRow ["orgID"]), Convert.ToString (dataRow ["branchID"]),
                                            Convert.ToString (dataRow ["depID"]), Convert.ToString (dataRow ["duty"]),
                                            Convert.ToString (dataRow ["beginYear"]), Convert.ToString (dataRow ["endYear"]),
                                            Convert.ToString (dataRow ["editor"]),
                                            DateTime.Now.ToString ("yyyy-MM-dd HH: mm: ss"),
                                            Convert.ToString (dataRow ["parentOrg"]), Convert.ToString (dataRow ["audit"]),
                                            Convert.ToString (dataRow ["auditTime"]), Convert.ToString (dataRow ["status"]),
                                            Convert.ToString (dataRow ["remark"]));
            SqlCommand command = new SqlCommand (sqlstring, conn);
            command.ExecuteNonQuery (); // Execute SQL statement

        }


Here I list some problems in writing from excel table to sqlserver, hope these instructions can be useful for you when doing this operation
 1,



At first glance, we will think that there is a problem with the office installed on the computer itself. In fact, the problem is not here. Because the program imported into sqlserver is not good for 64-bit support, so we changed the CPU of the project from ANY Cpu to x86. As shown:



If you don't have x86, you need to create it first, click "Configuration Manager", the specific interface is as follows:



So this problem is solved.

2,



The external table is not in the expected format, this problem has the following:

1) Do not use the excel generated by the program

2), save excel as 97-03 version of excel, the format is .xls (I personally understand it is excel compatibility problem, 97-03 is more suitable for programming)

3. "Login failed to connect" is displayed

 Check whether the connection string is correct, you can check from the following aspects:

 1) Whether the instance name in Data Source is correct

 2) Whether the database name is correct

 3) Whether the database user exists, and if so, whether the password is correct

4. For multi-digit format setting in excel sheet:

 For example, the identity card number: 18 digits, set the format in the excel sheet, it should be set to text format, if it is set to a numeric form, it will be expressed in hexadecimal representation in the database.





At this point, the import and export of sqlserver and excel tables are complete! ! good luck! !

c # How to realize excel import to sqlserver, how to achieve export from sqlserver to excel (detail)