Aspose inserting directly into SQL Server Datatalbe

Original link: http://www.cnblogs.com/hellohongfu/p/7362830.html

The following code can generate the SQL that creates the table and test the insertsql based on the Excel file. method to create an Excel data into SQL table

 private void Getexcelfile (string path, string folder = "") {StringBuilder sb = new StringBuilder ();                        LoadFile Workbook Workbook = new Workbook (path); for (int i = 0; i < workbook. Worksheets.count; i++) {var sheetname = workbook. Worksheets[i].                Name; Sb.                               AppendFormat ("CREATE TABLE [biupload_{0}] (", sheetname); Sb.                Appendline (); Cells cells = workbook. Worksheets[i].                Cells;                #region Create SQL StringBuilder sql_log = new StringBuilder ();                StringBuilder Sql_insert = new StringBuilder (); Sql_insert.                AppendFormat ("Insert biupload_{0} (", sheetname); for (int c = 0; c < cells. Maxdatacolumn + 1; C + +) {if (cells[0, c]! = null && cells[0, C]. Value = null) {var colname = cells[0, c]. ValUe.                        ToString (); Sb.                        Appendline ("colname:" + colname); Sb.                        AppendFormat ("[{0}] nvarchar (),", colname); Sb.                        Appendline (); if (c = = 0) {Sql_insert.                        AppendFormat ("[{0}]", colname); } else {Sql_insert.                        AppendFormat (", [{0}]", colname); } sql_insert.                    Appendline (); }} sb.                Appendline (")"); Sql_insert.                Appendline (")"); #endregion #region Create values for (int r = 1; r <= cells. Maxdatarow;                    r++) {var sql_values = new StringBuilder (); Sql_values. Appendline (Sql_insert.                    ToString ()); Sql_values.                    Appendline ("values"); SqL_log. Appendline (sql_values.                    ToString ());                    list<sqlparameter> parameters = new list<sqlparameter> (); for (int c = 0; c < cells. Maxdatacolumn + 1;                        C + +) {var pName = "@P" + C; if (Cells[r, c]! = null && cells[r, C]. Value = null) {var value = Cells[r, C]. Value.tostring ().                                                        Trim ();                            SqlParameter SqlParameter = new SqlParameter (pName, value); Parameters.                            ADD (SqlParameter); if (c = = 0) {sql_values.                                AppendFormat ("{0}", pName); Sql_log.                                                        AppendFormat ("' {0} '", value); } else {sql_values. AppendfOrmat (", {0}", pName); Sql_log.                            AppendFormat (", ' {0} '", value);                            }} else {if (c = = 0) {sql_values.                                AppendFormat ("'"); Sql_log.                            AppendFormat ("'"); } else {sql_values.                                AppendFormat ("," "); Sql_log.                            AppendFormat ("'"); }}} sql_values.                    Appendline (")"); Sql_log.                    Appendline (")"); Sqlhelper.executenonquery (_connstring, CommandType.Text, Sql_values. ToString (), parameters.                ToArray ());    } #endregion//insert Values #region insertsql            var sqlfile = file.create (path.combine (folder, sheetname+datetime.now.tostring ("YYYYMMDDHHMMSS") + ". sql"));                StreamWriter SW2 = new StreamWriter (sqlfile); String excutesql = delete + Sql_log.               ToString ();                Sqlhelper.executenonquery (_connstring, System.Data.CommandType.Text, Excutesql); SW2.                WriteLine (Excutesql); SW2.                Flush (); SW2.                Close ();                Sqlfile.close (); #endregion} #region CREATE table var file = file.create (path.combine (folder, DATETIME.N ow.            ToString ("YYYYMMDDHHMMSS") + "Entity.txt");            StreamWriter SW = new StreamWriter (file); Sw. WriteLine (sb.)            ToString ()); Sw.            Flush (); Sw.            Close (); File.            Close (); #endregion}

Aspose inserting directly into SQL Server Datatalbe