关于用C#开发Excel 的问题

Question

求助各位高手，如何用c#访问读取Excel表格里的数据，谢谢！

Answer 1

string str = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=c:\\test.xls;Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1;\" "; //注意此连接字符串的书写
        DataSet ds = new DataSet();
        OleDbConnection con;
            OleDbDataAdapter myadapter;
            con = new OleDbConnection(str);
                con.Open();
                myadapter = new OleDbDataAdapter("select * from [Sheet1$]", con);
                myadapter.Fill(ds, "ds");
                foreach (DataRow row in ds.Tables["ds"].Rows)
                {
                    string userInfo = row[0].ToString().Trim() + row[1].ToString().Trim() + row[2].ToString().Trim() + row[3].ToString().Trim() + row[4].ToString().Trim();
                    Response.Write(userInfo);
                }
           
                con.Close(); 

Answer 2

访问Excell Access都还是很方便的,楼上的方法是可以实现的.

Answer 3

简单的读写单元格        /// <summary>
        /// Execl對象
        /// </summary>
        private Microsoft.Office.Interop.Excel.Application excel;        /// <summary>
        /// Excel文檔
        /// </summary>
        private Microsoft.Office.Interop.Excel.Workbook workBook;        /// <summary>
        /// Excel頁面
        /// </summary>
        private Microsoft.Office.Interop.Excel.Worksheet workSheet;            // 創建空白Excel
            excel = new Microsoft.Office.Interop.Excel.Application();
            workBook = excel.Workbooks.Add(true);
            workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.ActiveSheet;            //设置行列值
              workSheet.Cells[row, column] = value;

Answer 4

楼上的方法是正确的，不过要确保Excel表是规则的，要不是规则的就只能根据坐标读取单元格了。

Answer 5

 Excel.Application myexcel = new Excel.Application();
                        myexcel.Visible = false;
                        Excel.Workbook myBook = myexcel.Workbooks._Open(OFD.FileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                        Excel.Worksheet mysheet = (Excel.Worksheet)myBook.Sheets[1]; for (int i = 2; i < mysheet.UsedRange.Rows.Count;i++ )
                        {
                            dataDT.Rows.Add();
                             for (int j = 1;j < mysheet.UsedRange.Columns.Count;j++ )
                             {
                                 dataDT.Rows[i - 2][j - 1] = ((Excel.Range)mysheet.Cells[i, j]).Text.ToString();
                             }                        }

Answer 6

 #region   gridview 导入excel 并保存至数据库的 操作
    protected void fromExcel_Click(object sender, EventArgs e)
    {
        if (FileUpload1.HasFile == false)//HasFile用来检查FileUpload是否有指定文件
        {
            Response.Write("<script>alert('请您选择Excel文件')</script> ");
            return;//当无文件时,返回
        }
        string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名
        if (IsXls != ".xls")
        {
            Response.Write("<script>alert('只可以选择Excel文件')</script>");
            return;//当选择的不是Excel文件时,返回
        }
        SqlConnection cn = new SqlConnection(strCon);
        cn.Open();
        string filename = DateTime.Now.ToString("yyyymmddhhMMss") + FileUpload1.FileName;              //获取Execle文件名  DateTime日期函数
        string savePath = Server.MapPath(("~\\upfiles\\") + filename);//Server.MapPath 获得虚拟服务器相对路径
        FileUpload1.SaveAs(savePath);                        //SaveAs 将上传的文件内容保存在服务器上
        DataSet ds = ExecleDs(savePath, filename);           //调用自定义方法
        DataRow[] dr = ds.Tables[0].Select();            //定义一个DataRow数组
        int rowsnum = ds.Tables[0].Rows.Count;
        if (rowsnum == 0)
        {
            Response.Write("<script>alert('Excel表为空表,无数据!')</script>");   //当Excel表为空时,对用户进行提示
        }
        else
        {
            for (int i = 0; i < dr.Length; i++)
            {
                string hhaspx_rq = dr[i]["条码始"].ToString();//excel列名【名称不能变,否则就会出错】
                string hhaspx_bh = dr[i]["条码尾"].ToString();// 列名 以下类似
                string hhaspx_xm = dr[i]["代理商ID"].ToString();
                string blockid = dr[i]["批次"].ToString();
                string sqlcheck = "select count(*) from t_Sect where Start_Barcode='" + hhaspx_rq + "'And End_Barcode='" + hhaspx_bh + "'";  //检查用户是否存在   
                SqlCommand sqlcmd = new SqlCommand(sqlcheck, cn);
                int count = Convert.ToInt32(sqlcmd.ExecuteScalar());
                if (count < 1)
                {
                    SqlConnection con = new SqlConnection(strCon);
                    con.Open();
                    string insertstr = "insert into t_Sect (Start_Barcode,End_Barcode,AgentID,BlockID,InputTime) values('" + hhaspx_rq + "','" + hhaspx_bh + "','" + hhaspx_xm + "','"+blockid+"','"+Convert.ToString(DateTime.Today.ToShortDateString())+"' )";
                    SqlCommand cmd = new SqlCommand(insertstr, con);
                    cmd.ExecuteNonQuery();
                }
                else
                {
                    Response.Write("<script>alert('内容重复！禁止导入');location='P_Inbound.aspx'</script></script> ");
                    continue;
                }
            }
            Response.Write("<script>alert('Excle表导入成功!')</script>");
            bind();
           
        }
    }
    #endregion

Answer 7

直接读就可以了啊，把excel当成一个数据库来读。

Answer 8

OleDbConnection myOleConn = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; data source="+xls+"; Extended Properties=Excel 8.0;");
OleDbDataAdapter MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from ["+strSheetName+"$]", myOleConn);
DataSet DS = new System.Data.DataSet();
MyCommand.Fill(DS);
myOleConn.Close();Excel.Application excel = null;
   Excel.Workbooks wbs = null;
   Excel.Workbook wb = null;
   Excel.Worksheet ws = null;
   Excel.Range range1 = null;
   object Nothing = System.Reflection.Missing.Value;
     
   try
   {
    excel = new Excel.Application();
    excel.UserControl = true;
    excel.DisplayAlerts = false;
                     
    excel.Application.Workbooks.Open(this.FilePath,Nothing,Nothing,Nothing,Nothing,Nothing,Nothing,Nothing,Nothing,Nothing,Nothing,Nothing,Nothing ) ;
     
    wbs = excel.Workbooks;
    wb = wbs[1];
    ws = (Excel.Worksheet)wb.Worksheets["Sheet2"];
     
          
    int rowCount = ws.UsedRange.Rows.Count;
    int colCount = ws.UsedRange.Columns.Count;
    if (rowCount <= 0)
     throw new InvalidFormatException("文件中没有数据记录");
    if (colCount < 4 ) 
     throw new InvalidFormatException("字段个数不对");
     
    for (int i = 0;i    {     this.rowNo = i + 1;
     object[] row = new object[4];
     for (int j = 0;j<4;j++)
     {
      range1 = ws.get_Range(ws.Cells[i+2,j+1],ws.Cells[i+2,j+1]);
      row[j] = range1.Value;      if (row[0] == null)
      {
       this.isNullRecord++;
       break;
      }
     }
                    
     if (this.isNullRecord > 0)
      continue;
     DataRow dataRow = this.readExcel(row);
     if (this.isNullRecord == 1)
      continue;
     if (this.verifyData(dataRow) == false)
      errFlag++;
     this.updateTableCurr(dataRow);
    }
     
   }
   finally
   {
    if (excel != null)
    {
     if (wbs != null)
     {
      if (wb != null)
      {
       if (ws != null)
       {
        if (range1 != null)
        {
         System.Runtime.InteropServices.Marshal.ReleaseComObject(range1);
         range1 = null;
        }
        System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
        ws = null;
       }
       wb.Close(false,Nothing,Nothing); 
       System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
       wb = null;
      }
      wbs.Close();
      System.Runtime.InteropServices.Marshal.ReleaseComObject(wbs);
      wbs = null;
     }
     excel.Application.Workbooks.Close();
     excel.Quit();
     System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
     excel = null;
     GC.Collect();
    }
   }