Aspose 直接插入SQL Server DataTalbe

原文链接：http://www.cnblogs.com/hellohongfu/p/7362830.html

 

下面的代码可以根据excel文件，生成创建表的SQL，以及测试InsertSQL 。方法将excel数据创建到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(20) ,", 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.Now.ToString("yyyyMMddHHmmss") + "entity.txt"));
            StreamWriter sw = new StreamWriter(file);
            sw.WriteLine(sb.ToString());
            sw.Flush();
            sw.Close();
            file.Close();
            #endregion




          


        }