日期:2014-05-18  浏览次数:20485 次

ASP.net中用数据库连接方式向Excel表格中插入数据遇到的问题
各位同仁:
  您们好,前段时间在这里跟大家请教了不少问题,都已一一解决,向大家表示感谢。现在又遇到了问题,恳请大家帮忙。我在C# + ASP.net网页中有一个类,主要完成把Dataset里的数据导入到Excel表格,是通过数据库连接的方式打开Excel的连接的方式,用Insert语句插入,现在的问题是程序在本机运行正常,一发布到IIS服务器上就出错,错误提示:操作必须使用一个可更新的查询
类代码如下:
using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.IO;
using Microsoft.Office.Interop.Excel;

/// <summary>
///Output 的摘要说明
/// </summary>
public class Output
{
  DataSet OutputSource = new DataSet();
  string OutputFileInfo = "";
  string fileName = "";
  string errorMessage = "";
public Output()
{
//
//TODO: 在此处添加构造函数逻辑
//
}
  public Output(DataSet source, string fileinfo,string filename)
  {
  this.OutputFileInfo = fileinfo;
  OutputSource = source;
  this.fileName = filename;
  }
  public string getErrormessage()
  {
  return this.errorMessage;
  }
  public int OutputData()
  {
  int OutputCount = 0;
  string ExcelConnectstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + this.OutputFileInfo + ";Extended Properties='Excel 8.0;HDR=YES';";
  System.Data.OleDb.OleDbConnection myoledbcon = new System.Data.OleDb.OleDbConnection(ExcelConnectstring);
  System.Data.OleDb.OleDbCommand myExcelCmd = new System.Data.OleDb.OleDbCommand();
  myExcelCmd.Connection = myoledbcon;
  try
  {
  myoledbcon.Open();
  }
  catch (Exception error)
  {
  this.errorMessage += "打开Excel表格错误:" + error.Message;
  }
  foreach (DataRow mySourceDataRow in OutputSource.Tables[0].Rows)
  {
  string Insertstring = "Insert into [sheet1$] Values('";
  object[] myRowB = mySourceDataRow.ItemArray;
  bool start = true;
  foreach (object myData in myRowB)
  {
  if (!start)
  {
  Insertstring += "','";
  }
  Insertstring += myData.ToString().Trim();
  start = false;
  }
  Insertstring += "')";
  myExcelCmd.CommandText = Insertstring;
  try
  {
  OutputCount += myExcelCmd.ExecuteNonQuery();
  }
  catch (Exception error)
  {
  this.errorMessage += "插入Excel表格数据错误:" + error.Message;
  }
  }
  myoledbcon.Close();
  myoledbcon = null;
  myExcelCmd = null;
  return OutputCount;
  }
 }
请教大家,是什么原因呢?

------解决方案--------------------
NPOI 这个绝对的好使