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 这个绝对的好使