Excel报表开发

 

读取Excel数据

/// <summary>
        /// 封装方法
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        public DataSet gridview(string path)//path路径
        {
            //导入头                                                               //2003版的头   

            OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0;\";Data Source=" + path);
            conn.Open();
            OleDbDataAdapter da = new OleDbDataAdapter("select * from [Sheet1$]", conn);
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds;
        }　 

 设置IMEX=1 时将强制混合数据转换为文本

Excel读取数据默认为8行 就推测每列的数据类型   

 解决方法 :         
  设置IMEX=1 时将强制混合数据转换为文本(不能根本解决,数据过大时)

  修改注册表  修改注册表的Jet-->4.0-->Engines-->Excel的TypeGuessRows的值为0 (根本解决)

写入数据库:

 public string how( string path)
        {
            OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0;\";Data Source=" + path);
            conn.Open();
            OleDbDataAdapter da = new OleDbDataAdapter("select * from [Sheet1$]", conn);
            DataSet ds = new DataSet();
            da.Fill(ds);
            string sql = string.Empty;
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                sql += string.Format("insert into class values({0},'{1}','{2}','{3}')", ds.Tables[0].Rows[i][0].ToString(),ds.Tables[0].Rows[i][1].ToString(),ds.Tables[0].Rows[i][2].ToString(),ds.Tables[0].Rows[i][3].ToString());    
            }
            int rowcount = SqlHelper.ExecuteNonQuery(sql);
            if (rowcount > 0)
            {
                return "ok";
            }
            else
            {
                return "no";
            }
        }

　　

写入Excel:

   private bool   showAdd( string path, string id)
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";" + "Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            System.Data.OleDb.OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;
            string sql;
            if (id == null)
            {
                 sql = string.Format("select * from class ");
            }
            else
            {
                sql = string.Format("select * from class where id =" + id);
            }
            DataSet ds = SqlHelper.ExecuteDataSet( sql,null );
            int RowCount = ds.Tables[0].Rows.Count;
            for (int j = 0; j < RowCount; j++)
            {
                string xh = ds.Tables[0].Rows[j][0].ToString();//学号
                string xm = ds.Tables[0].Rows[j][1].ToString();//姓名
                string xb = ds.Tables[0].Rows[j][2].ToString();//性别
                string ca = ds.Tables[0].Rows[j][3].ToString();//班级
                cmd.CommandText = "INSERT INTO [sheet1$](编号,姓名,性别,班级) VALUES('" + xh + "','" + xm + "','" + xb + "','"+ca+"')";
                cmd.ExecuteNonQuery();
            }
            conn.Close();
            cmd.Dispose();
            return true;
        }