web winform导入excel 支持2003 2007

      private void btnImportExcel_Click(object sender, EventArgs e)

        {
          
            SqlConnection conn = new SqlConnection();

            try
            {
                OpenFileDialog openFile = new OpenFileDialog();//打开文件对话框。

                if (openFile.ShowDialog() == DialogResult.OK)
                {

                    string connStr = null;
                    string filename = openFile.FileName;
                    int index = filename.LastIndexOf("\\");//截取文件的名字
                    filename = filename.Substring(index + 1);
                    int suffix_index = filename.LastIndexOf(".");
                    string suffixName = filename.Substring(suffix_index + 1);
                    //hdr=no(第一行是不是标题，作为数据来使用，这样excel可以读取汉字或者格式混乱的内容)
                    if (suffixName == "xls")
                    {
                        connStr = "Provider=Microsoft.Jet.OLEDB.4.0;"
                            //+ "Extended Properties=Excel 8.0;"
                            + "Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1;\";"
                            + "Data Source=" + filename;
                    }
                    else if (suffixName == "xlsx")
                    {
                        connStr = "Provider=Microsoft.ACE.OLEDB.12.0;"
                           + "Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1;\";"
                           + "Data Source=" + filename;
                    }
                    else
                    {
                        MessageBox.Show("格式错误");
                        return;
                    }

                    OleDbConnection objConn = null;
                    objConn = new OleDbConnection(connStr);
                    objConn.Open();

                    // Get the first sheet name.
                    DataTable dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                    string sheetName = "";
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        sheetName = dt.Rows[0]["TABLE_NAME"].ToString();
                    }
                    else
                    {
                        MessageBox.Show("数据为空");
                        return;
                    }

                    // Get the data from the first sheet.
                    string sql = string.Format("SELECT * FROM [{0}]", sheetName);

                    DataSet ds = new DataSet();
                    OleDbDataAdapter da = new OleDbDataAdapter(sql, objConn);
                    da.Fill(ds);
                    DataTable dataTable = ds.Tables[0];
                    int coluNum = dataTable.Columns.Count;
                    int rowNum = dataTable.Rows.Count;
                    bool firstrow_flag = true;

                    string mysql = null;

                    foreach (DataRow row in dataTable.Rows)
                    {

                        bool insertbuilding_flag = true;
                        if (firstrow_flag)
                        {
                            firstrow_flag = false;
                            continue;
                        }

                        Guid gid = Guid.NewGuid();

                        string buildingname = row[6].ToString() + "栋" + row[7].ToString() + "单元";

                        for (int i = 9; i < coluNum; i++)
                        {
                            conn = DBConnection.DBOpen();
                            SqlTransaction tran = conn.BeginTransaction();

                            try
                            {
                                if (insertbuilding_flag)
                                {
                                    insertbuilding_flag = false;
                                    if (!string.IsNullOrEmpty(buildingname))
                                    {
                                        string sql_insertBuilding = "insert into dbo.building(buildingID,building_name) values('" + gid + "','" + buildingname + "');";
                                        mysql += sql_insertBuilding;
                                    }
                                    if (!string.IsNullOrEmpty(row[i].ToString()))
                                    {
                                        string sql_insertRoom = "insert into dbo.Room(roomID,buildingID,room_name) values('" + Guid.NewGuid() + "','" + gid + "','" + row[i].ToString() + "');";
                                        mysql += sql_insertRoom;
                                    }

                                }
                                else
                                {
                                    if (!string.IsNullOrEmpty(row[i].ToString()))
                                    {
                                        string sql_insertRoom = "insert into dbo.Room(roomID,buildingID,room_name) values('" + Guid.NewGuid() + "','" + gid + "','" + row[i].ToString() + "');";
                                        mysql = sql_insertRoom;
                                    }

 

                                }

                                if (!string.IsNullOrEmpty(mysql))
                                {

                                    SqlCommand cmd = new SqlCommand(mysql, conn);
                                    cmd.Transaction = tran;
                                    cmd.ExecuteNonQuery();
                                    tran.Commit();
                                    mysql = null;
                                }

                            }
                            catch
                            {
                                tran.Rollback();

                            }
                            finally
                            {
                                conn.Close();
                            }
                        }

                    }

 

                    MessageBox.Show("操作成功");
                    openFileDialog1.Dispose();

                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());

            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }

        }