操作excel

我操作的是2007个2010版本的excel

首先是读取

        /// <summary>
        /// 读取xls\xlsx格式的Excel文件的方法
        /// </summary>
        /// <param name="path">待读取Excel的全路径</param>
        /// <returns></returns>
        private DataTable ReaderExcelToTable(string path)
        {
            //连接字符串
            string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";
            // Office 07及以上版本 不能出现多余的空格 而且分号注意
            using (OleDbConnection conn = new OleDbConnection(connString))
            {
                conn.Open();
                //DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
                //    new object[] { null, null, null, "Table" });    //得到所有sheet的名字
                string danXuanTiName = "sheet1";
                string sql = string.Format("select * from [{0}]", danXuanTiName + "$");    //查询字符串
                OleDbDataAdapter dta = new OleDbDataAdapter(sql, connString);
                DataSet set = new DataSet();
                dta.Fill(set);
                return set.Tables[0];
            }
        }

 

写入excel

private void TableOutExcel(DataTable dt)
        {
            //指定要创建副本文件的路径和Name
            string filePath = Server.MapPath("/_Layouts/" + Guid.NewGuid().ToString() + ".xlsx");
            //根据现存模板文件创建副本
            File.Copy(Server.MapPath("/_Layouts/QuestionDemo.xlsx"), filePath);
            //使用OleDb连接副本
            OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=YES';");
            using (conn)
            {
                conn.Open();
                foreach (DataRow row in dt.Rows)
                {
                    OleDbCommand comd = new OleDbCommand("INSERT INTO [单选题$]([试题内容],[试题选项],[正确答案],[试题等级]) VALUES(@Question,@Option,@RAnser,@Drg)", conn);
                    comd.Parameters.AddWithValue("@Question", row[0].ToString());
                    comd.Parameters.AddWithValue("@Option", row[1].ToString());
                    comd.Parameters.AddWithValue("@RAnser", row[2].ToString());
                    comd.Parameters.AddWithValue("@Drg", row[3].ToString());
                    comd.ExecuteNonQuery();
                }
            }
            // 输出副本的二进制字节流
            Response.ContentType = "application/ms-excel";
            Response.AppendHeader("Content-Disposition", "attachment;filename=Question.xlsx");
            Response.BinaryWrite(File.ReadAllBytes(filePath));
            // 删除副本
            File.Delete(filePath);
        }

 

这个方法的做法是，先copy服务器上的一个模板，然后根据模板生成一个excel，然后添加数据，最后输出excel，让用户下载