none
c# excel RRS feed

  • 问题

  • 我想把excel的数据提取出来,存数据库里,然后对每插入一条数据,在excel那一行的最右边写个1,插入失败写0,下面是代码

        private void Import(int y, string path)
        {
          if (connectionString == "")
          {
            sslStatu.Text = "数据库连接失败";
            return;
          }
          SqlConnection conn = new SqlConnection(connectionString);
          Workbook wb = excel.Application.Workbooks.Open(path, missing, true, missing, missing, missing, missing, missing, missing, true, missing, missing, missing, missing, missing);
          item temp;
          pbPro.Visible = true;
          pbPro.Visible = true;
          pbPro.Maximum = y;
          pbPro.Step = 1;
          pbPro.Value = pbPro.Minimum;
          Thread.Sleep(5);
          int faild = 0, x = 0;
    
          try
          {
            conn.Open();
            for (int j = 1, m = wb.Worksheets.Count; j <= m; j++)
            {
              Worksheet ws = (Worksheet)wb.Worksheets[j];
              Thread.Sleep(10);
              int rowsint = ws.UsedRange.Cells.Rows.Count;
              Range rng = ws.Cells.get_Range("A2", "C" + rowsint);
              object[,] data = (object[,])rng.Value2;
              for (int i = 1, n = rowsint; i < n; i++)
              {
                if (data[i, 1] == null || data[i, 1].ToString() == "")
                  continue;
                temp = new item();
                temp.name = data[i, 1].ToString();
                temp.uid = data[i, 2].ToString();
                temp.pwd = MD5(data[i, 3].ToString());
                SqlCommand com = new SqlCommand("INSERT INTO Trade_Member (memberlevelid, MemberTypeID, UserName, PassWord, MemberName, Company, Tel, Mobile, Fax, Address, PostCode, ISDEL, enabled, question, answer, QQ, MSN, email, website, lastloginip, lastlogintime, logincount, power, powerbg, senddate) VALUES (1, 8, '" + temp.uid + "', '" + temp.pwd + "', '" + temp.name + "', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '')", conn);
                try
                {
                  com.ExecuteNonQuery();
                  ws.Cells[i + 1, 4] = 1;
                  pbPro.PerformStep();
                }
                catch
                {
                  ws.Cells[i + 1, 4] = 0;
                  faild++;
                }
                x++;
                sslStatu.Text = "当前已尝试导入 " + x + "/" + y + " 条数据, " + faild + " 条失败";
              }
              
            }
            //for (int i = 0; i < n; i++)
            //{
            //  SqlCommand com = new SqlCommand("INSERT INTO Trade_Member (memberlevelid, MemberTypeID, UserName, PassWord, MemberName, Company, Tel, Mobile, Fax, Address, PostCode, ISDEL, enabled, question, answer, QQ, MSN, email, website, lastloginip, lastlogintime, logincount, power, powerbg, senddate) VALUES (1, 8, '" + source[i].uid + "', '" + source[i].pwd + "', '" + source[i].name + "', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '')", conn);
            //  try
            //  {
            //    com.ExecuteNonQuery();
            //  }
            //  catch
            //  {
            //    faild++;
            //  }
            //  pbPro.PerformStep();
            //  sslStatu.Text = "当前已尝试导入 " + i + "/" + n + " 条数据, " + faild + " 条失败";
            //}
            sslStatu.Text = "导入完成,共尝试导入 " + x + " 条数据, " + faild + " 条失败";
          }
          catch
          {
            sslStatu.Text = "数据库连接失败 " + connectionString;
          }
          finally
          {
            excel.DisplayAlerts = false;
            excel.AlertBeforeOverwriting = false;
    
            try
            {
              //wb.Save();
              excel.Save(path);
              excel.SaveWorkspace(path);
            }
            catch
            {
              sslStatu.Text += "状态写入失败";
            }
            excel.Quit();
            pbPro.Visible = false;
            conn.Close();
          }
        }
    
    现在数据是导到数据库里去了,但是这个新改过的excel不能保存了,执行保存的语句就弹出异常,说文件是只读的,文件明明不是只读的
    2010年8月11日 1:40

答案