我excel的组件用的是12.0.6425.1000,然后我在运行的时候在本地发布没有问题,但是我发布到excel之后就提示异常来自 HRESULT:0x800A03EC的错误。我的电脑20032007都装了,不知道什么问题求解决。代码如下protected void btnDelete_Click(object sender, EventArgs e)
{
DataSet ds = errorlog_bll.GetList(" dtDate <= DateAdd(Month,-3,getdate())"); CreateExcel(ds.Tables[0], "D:\\Sysnote"+(int.Parse(DateTime.Now.Month.ToString())-2)+"月之前"); //if (export)
//{
// string strwhere = " datediff(m,dtdate,getdate())>3"; // if (errorlog_bll.Delete(strwhere))
// {
// FineUI.Alert.Show("删除成功");
// } // BindGrid();
//}
} public void CreateExcel(DataTable dt, string fileName)
{
if (dt.Rows.Count == 0)
{
FineUI.Alert.Show("无数据可导出");
} try
{
System.Diagnostics.Process[] arrProcesses;
arrProcesses = System.Diagnostics.Process.GetProcessesByName("Excel");
foreach (System.Diagnostics.Process myProcess in arrProcesses)
{
myProcess.Kill();
} Object missing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application m_objExcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks m_objWorkBooks = m_objExcel.Workbooks;
Microsoft.Office.Interop.Excel.Workbook m_objWorkBook = m_objWorkBooks.Add(true);
Microsoft.Office.Interop.Excel.Sheets m_objWorkSheets = m_objWorkBook.Sheets; ;
Microsoft.Office.Interop.Excel.Worksheet m_objWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)m_objWorkSheets[1];
int intFeildCount = dt.Columns.Count;
for (int col = 0; col < intFeildCount; col++)
{
m_objWorkSheet.Cells[1, col + 1] = dt.Columns[col].ToString();
}
for (int intRowCount = 0; intRowCount < dt.Rows.Count; intRowCount++)
{
for (int intCol = 0; intCol < dt.Columns.Count; intCol++)
{
m_objWorkSheet.Cells[intRowCount + 2, intCol + 1] = "'" + dt.Rows[intRowCount][intCol].ToString();
}
} if (File.Exists(fileName))
{
File.Delete(fileName);
}
//m_objWorkBook.SaveAs(fileName, missing, missing, missing, missing, // missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
// missing, missing, missing, missing, missing);
m_objWorkBook.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel3,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); m_objExcel = null;
}
catch (Exception ex)
{
string strEXMessage = ex.Message; FineUI.Alert.Show(strEXMessage);
}
}
{
DataSet ds = errorlog_bll.GetList(" dtDate <= DateAdd(Month,-3,getdate())"); CreateExcel(ds.Tables[0], "D:\\Sysnote"+(int.Parse(DateTime.Now.Month.ToString())-2)+"月之前"); //if (export)
//{
// string strwhere = " datediff(m,dtdate,getdate())>3"; // if (errorlog_bll.Delete(strwhere))
// {
// FineUI.Alert.Show("删除成功");
// } // BindGrid();
//}
} public void CreateExcel(DataTable dt, string fileName)
{
if (dt.Rows.Count == 0)
{
FineUI.Alert.Show("无数据可导出");
} try
{
System.Diagnostics.Process[] arrProcesses;
arrProcesses = System.Diagnostics.Process.GetProcessesByName("Excel");
foreach (System.Diagnostics.Process myProcess in arrProcesses)
{
myProcess.Kill();
} Object missing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application m_objExcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks m_objWorkBooks = m_objExcel.Workbooks;
Microsoft.Office.Interop.Excel.Workbook m_objWorkBook = m_objWorkBooks.Add(true);
Microsoft.Office.Interop.Excel.Sheets m_objWorkSheets = m_objWorkBook.Sheets; ;
Microsoft.Office.Interop.Excel.Worksheet m_objWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)m_objWorkSheets[1];
int intFeildCount = dt.Columns.Count;
for (int col = 0; col < intFeildCount; col++)
{
m_objWorkSheet.Cells[1, col + 1] = dt.Columns[col].ToString();
}
for (int intRowCount = 0; intRowCount < dt.Rows.Count; intRowCount++)
{
for (int intCol = 0; intCol < dt.Columns.Count; intCol++)
{
m_objWorkSheet.Cells[intRowCount + 2, intCol + 1] = "'" + dt.Rows[intRowCount][intCol].ToString();
}
} if (File.Exists(fileName))
{
File.Delete(fileName);
}
//m_objWorkBook.SaveAs(fileName, missing, missing, missing, missing, // missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
// missing, missing, missing, missing, missing);
m_objWorkBook.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel3,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); m_objExcel = null;
}
catch (Exception ex)
{
string strEXMessage = ex.Message; FineUI.Alert.Show(strEXMessage);
}
}
出现你说的这个情况,一般都是COM组件的注册和配置,以及文件操作权限的问题
具体情况具体分析
using System;
using System.Data;
using System.Web;
using System.Collections;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;using System.Xml;
using System.Xml.Xsl;namespace ASUS.Saber.SystemFramework
{
/// <summary>
/// TableToExcel 的摘要说明。
/// </summary>
public class TableToExcel
{
public TableToExcel()
{
//
// TODO: 在此处添加构造函数逻辑
//
} public void CreatExcel(DataTable dt,string excel_file)
{
//提供下载
HttpResponse response = HttpContext.Current.Response;
string urlPath = HttpContext.Current.Request.ApplicationPath + "/athena/Temp/";
string physicPath = HttpContext.Current.Server.MapPath(urlPath);
if (!File.Exists(physicPath))
{
Directory.CreateDirectory(physicPath);
}
string fileName = Guid.NewGuid() + ".Xls"; string table_string=@"
CREATE TABLE ProductSpecification
(
"; table_string+=CreatSqlString(dt,0);
table_string+=" )"; response.Write(table_string); string insert_string=@"
INSERT INTO ProductSpecification
(
";
insert_string+=CreatSqlString(dt,1);
insert_string+=" )";
insert_string+=@"
VALUES (
";
insert_string+=CreatSqlString(dt,2);
insert_string+=" )"; response.Write(insert_string);
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + physicPath + fileName +";Extended Properties=Excel 8.0;"; OleDbConnection objConn = new OleDbConnection(connString);
OleDbCommand objCmd = new OleDbCommand();
objCmd.Connection = objConn; //建立表结构
objCmd.CommandText =table_string;
objConn.Open();
objCmd.ExecuteNonQuery();
//建立插入动作的Command
objCmd.CommandText =insert_string;
for (int i=0;i<dt.Columns.Count;i++)
{
objCmd.Parameters.Add(new OleDbParameter("@a_"+i, OleDbType.LongVarChar,8000));
} string str_content="";
//插入新数据
foreach (DataRow row in dt.Rows)
{
for (int i=0; i<objCmd.Parameters.Count; i++)
{
str_content=row[i].ToString();
if (str_content.Length>200)
{
str_content=str_content.Substring(0,200);
}
objCmd.Parameters[i].Value =str_content;
}
objCmd.ExecuteNonQuery();
}
objConn.Close();
response.Clear();
response.WriteFile(physicPath + fileName);
string httpHeader="attachment;filename="+excel_file;
response.AppendHeader("Content-Disposition", httpHeader);
response.Flush(); System.IO.File.Delete(physicPath + fileName);//删除临时文件
response.End();
}
/// <summary>
/// via dt generate SqlString
/// 0 for creat a,b
/// 1 for insert
/// 2 for @a,@b
/// </summary>
/// <param name="dt"></param>
/// <param name="type"></param>
/// <returns></returns> private string CreatSqlString(DataTable dt,int type)
{
string temp="";
for (int i=0;i<dt.Columns.Count;i++)
{
if (type==0) //for create table(a varchar)
{
temp+=FixColumName(dt.Columns[i].ColumnName);
temp+=" varchar";
}
else if (type==1) //for insert table(a,b)
{
temp+=FixColumName(dt.Columns[i].ColumnName);
}
else if (type==2) //for @a,@b
{
temp+="@a_"+i;
} if (i<dt.Columns.Count-1)
{
temp+=",\r\n";
}
}
return temp;
} public string FixColumName(string source)
{
string temp=source;
ArrayList sp_array=SpecialArrayList();
for (int i=0;i<sp_array.Count;i++)
{
if (source.IndexOf(sp_array[i].ToString())!=-1)
{
temp="["+source+"]";
break;
}
}
return temp;
} private ArrayList SpecialArrayList()
{
ArrayList temp=new ArrayList();
temp.Add(" ");
temp.Add("INDEX");
temp.Add("(");
return temp;
} // you could have other overloads if you want to get creative...
//public static string CreateWorkbook(DataSet ds)
//{
// XmlDataDocument xmlDataDoc = new XmlDataDocument(ds);
// XslTransform xt = new XslTransform();
// StreamReader reader = new
// StreamReader(typeof(WorkbookEngine).Assembly.GetManifestResourceStream(typeof(WorkbookEngine), "Excel.xsl"));
// XmlTextReader xRdr = new XmlTextReader(reader);
// xt.Load(xRdr, null, null);
// StringWriter sw = new StringWriter();
// xt.Transform(xmlDataDoc, null, sw, null);
// return sw.ToString();
//}
}
}
参考http://blog.csdn.net/chinajiyong/article/details/9187485
参考http://blog.csdn.net/chinajiyong/article/details/9187485using NPOI.SS.UserModel; using NPOI.SS.Util; 这两个我这里点不出来
参考http://blog.csdn.net/chinajiyong/article/details/9187485using NPOI.SS.UserModel; using NPOI.SS.Util; 这两个我这里点不出来
到我这篇文章里去下载dll试试
我解决这个问题是采用了直接网页输出方法:如下
string apppath = ApplicationPath + "datafiles\\schoolTemplete";
if (!System.IO.Directory.Exists(apppath))
{
System.IO.Directory.CreateDirectory(apppath);
}
apppath = apppath + "\\studentdtz.xls";
string webpath = AppVirtualPath + "/datafiles/schoolTemplete/studentdtz.xls";
if (System.IO.File.Exists(apppath))
{
System.IO.File.Delete(apppath);
}
System.Data.DataTable dt = TData.SQLGetDataTable(sqlstr);
System.IO.FileStream objstream;
System.IO.StreamWriter objwriter;
objstream = new System.IO.FileStream(apppath, System.IO.FileMode.Create, System.IO.FileAccess.Write);
objwriter = new System.IO.StreamWriter(objstream, System.Text.Encoding.Unicode);
try
{
string strLine = "";
//写出列名
string[] col_alias = exportcol_alias.Split(',');
string[] col_names = exportcol_name.Split(',');
for (int i = 0; i < col_alias.Length; i++)
{
strLine += col_alias[i] + "\t";
}
objwriter.WriteLine(strLine);
strLine = "";
foreach (System.Data.DataRow dr in dt.Rows)
{//写出数据
for (int i = 0; i < col_names.Length; i++)
{
strLine += "'" + dr[col_names[i]].ToString() + "\t";
}
objwriter.WriteLine(strLine);
strLine = "";
}
}
catch (Exception ex)
{
//throw ex;
}
finally
{
objwriter.Close();
objstream.Close();
Response.Redirect(webpath);
}
}
这么好的东西不用.