下面是我生成报表的代码,望大侠们改正下: string sql=s_getchkbox(CB_Sql);
if (sql=="")
{
Response.Write(" <script language=javascript> alert('请选择需要生成报表的字段!') </script>");
return;
}
string rsql=sql.Substring(0,sql.Length-1);
string stxt=s_getCB(CB_Sql);
SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["ConnStr"]);
conn.Open();
string str = "select "+rsql+" from Aims order by Aim_Date desc";
SqlCommand comm = new SqlCommand(str, conn);
SqlDataReader dr = comm.ExecuteReader();
Excel.Application xlApp = new Excel.Application(); if (xlApp == null) { Response.Write("Can’t open Excel!"); return; }
xlApp.Application.Workbooks.Add(true); int row = 2, fieldcount; fieldcount = dr.FieldCount; string[] strn = stxt.Split(',');
for (int col = 0; col < fieldcount; col++)
xlApp.Cells[1, col + 1] =strn[col].ToString();
while (dr.Read())
{
for (int col = 0; col < fieldcount; col++)
xlApp.Cells[row, col+1] = dr.GetValue(col).ToString();
row++;
}
xlApp.Visible = true;
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp.Application.Workbooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
conn.Close();
GC.Collect();
这个能够生成报表,但是每次生成报表关闭Excle后,系统进程--Excel.exe都不能释放,这个问题怎么解决呀,高手些指点下···
if (sql=="")
{
Response.Write(" <script language=javascript> alert('请选择需要生成报表的字段!') </script>");
return;
}
string rsql=sql.Substring(0,sql.Length-1);
string stxt=s_getCB(CB_Sql);
SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["ConnStr"]);
conn.Open();
string str = "select "+rsql+" from Aims order by Aim_Date desc";
SqlCommand comm = new SqlCommand(str, conn);
SqlDataReader dr = comm.ExecuteReader();
Excel.Application xlApp = new Excel.Application(); if (xlApp == null) { Response.Write("Can’t open Excel!"); return; }
xlApp.Application.Workbooks.Add(true); int row = 2, fieldcount; fieldcount = dr.FieldCount; string[] strn = stxt.Split(',');
for (int col = 0; col < fieldcount; col++)
xlApp.Cells[1, col + 1] =strn[col].ToString();
while (dr.Read())
{
for (int col = 0; col < fieldcount; col++)
xlApp.Cells[row, col+1] = dr.GetValue(col).ToString();
row++;
}
xlApp.Visible = true;
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp.Application.Workbooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
conn.Close();
GC.Collect();
这个能够生成报表,但是每次生成报表关闭Excle后,系统进程--Excel.exe都不能释放,这个问题怎么解决呀,高手些指点下···
解决方案 »
- ASP.Net跨框架改写src
- AssemblyFileVersion属性如果删除了会怎么样?
- 用户注册成功,同时把用户注册的ID插入到另外一个表中,在SQL中如何实现呢?
- 谁有时间的控件,是服务端的哦.多谢谢
- 赋值问题-大家进来看看到底错在那里?
- 急求!!Data Access Application Block连接oracle数据库的方法
- 新装visual studio.net2003的问题
- 清除Cookie时如何也把它的身份验证也清除
- 关于DropDownList连接问题
- 为什么我这段代码不好用,very easy
- 关于UpdatePanel中的按钮刷新整个页面的问题?
- ASPxGridView中禁止排序不起作用????
如果不需要格式化输出,那么就使用直接输出TABLE吧,这样节约N多资源
最后我采用了折中的方式,不应用excel,而是直接拼凑字符串,最后生成个csv的文件来解决这个问题
public static string ExportCSV(DataSet ds, System.Web.UI.WebControls.DataControlFieldCollection Columns)
{
string data = "";
string Header = "";
for (int i = 0; i < Columns.Count; i++)
{
Header = Header + Columns[i].HeaderText + ",";
}
if (Header != null)
{
data += Header + "\r\n";
} DataTable tb = ds.Tables[0]; //写出数据
foreach (DataRow row in tb.Rows)
{
for (int i=0;i<Columns.Count;i++)
{
string field = (Columns[i] as System.Web.UI.WebControls.BoundField).DataField;
data += row[field].ToString() + ",";
}
data += "\r\n";
}
data += "\r\n"; return data;
}
主线程等待他完成
如:class Program
{ static void Main(string[] args)
{
Thread thread = new Thread(start);
thread.Start();
thread.Join(); //等待线程结束
Console.WriteLine("完成");
Console.ReadLine(); }
static void start(object o)
{
Microsoft.Office.Interop.Excel.ApplicationClass app=null;
try
{
Console.WriteLine("启动");
app = new Microsoft.Office.Interop.Excel.ApplicationClass();
app.Visible = true;
Console.WriteLine(app.Version);
}
catch(Exception ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
if(app!=null)
{
Console.WriteLine("释放");
app.Quit();
System.Windows.Forms.Application.DoEvents();
//释放com对象
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
} //如果感觉不放心可以加下边的
GC.Collect();
GC.WaitForPendingFinalizers(); //因为在线程内等一会儿也是可以的
}
}
}
虽然是 命令行程序的不过原理类似;关键在 System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
很多 com 对象都需要掉这个释放;也可参考 http://blogs.geekdojo.net/richardhsu/archive/2003/11/14/281.aspx 中的步骤;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.IO;namespace ConsoleApplication2
{
class Program
{
static void Main(string[] args)
{
string path = "C:\\ExcelData1.xls";
if(File.Exists(path))
{
File.Delete(path);
}
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=YES'";
using(OleDbConnection conn = new OleDbConnection(strConn))
{
conn.Open();
string sql = @"CREATE TABLE Sheet1
(
F1 char(255)
, F2 char(255)
)";
using (OleDbCommand cmd = new OleDbCommand(sql, conn))
{
//
cmd.ExecuteNonQuery();
} sql = "insert into [Sheet1$] (F1,F2) values ('0241234567','24:00' )";
using (OleDbCommand cmd = new OleDbCommand(sql, conn))
{
//
cmd.ExecuteNonQuery();
}
}
}
}
}
然后紧接着来一个 {conn.Close(); 去掉,改成 } 也就是说using 的 {} 要包住你的大部分代码
你就不必管理了.
GC.Collect(); 这个我没有看到,不知何用.你可以自行处理.
6楼的方法可取,但是你这样做用户生成excel过后还得去找到生成的文档再打开,这样用户会比较麻烦。
还有没更好的解决方法呀?
这样做太厉害了,但是如果用户原来也开的有word、excel的时候那不是也被关闭了···不太合理·······
谢谢您!
6楼的方法可取,但是你这样做用户生成excel过后还得去找到生成的文档再打开,这样用户会比较麻烦。
还有没更好的解决方法呀?啥意识?为啥还要用excel oldb生成 excel 后直接给用户下载即可
如类似代码
this.Response.Redirect("~/temp/xxxx.xsl");
直接html就可以导成excel格式的文件,不更好
关闭EXCEL进程是需要在using或者是finally里面的,需要一个进程时间的判断
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Collections;
using System.Data.SqlClient;
using System.IO;
namespace FN_Operation.Code
{
public class vExcel
{
private int m_Capacity;
private string[] m_Strings;
private int m_Size;
/// <summary>
/// 构造函數
/// </summary>
public vExcel()
: this(10)
{
}
/// <summary>
/// 构造函數
/// </summary>
public vExcel(int capacity)
{
m_Capacity = capacity;
m_Strings = new string[capacity];
m_Size = 0;
}
/// <summary>
/// 数据个数属性
/// </summary>
public int Count
{
get
{
return m_Size;
}
}
/// <summary>
///內存大小属性
/// </summary>
public int Capacity
{
get
{
return m_Capacity;
}
set
{
if (m_Strings == null)
{
return;
}
if (value != m_Strings.Length)
{
if (value < this.m_Size)
{
throw new ArgumentOutOfRangeException();
} if (value > 0)
{
string[] objArray1 = new string[value];
if (this.m_Size > 0)
{
Array.Copy(this.m_Strings, 0, objArray1, 0, this.m_Size);
}
this.m_Strings = objArray1;
}
else
{
this.m_Strings = new string[0x10];
}
}
}
}
public string Text
{
get
{
return this.ToString();
}
}
public void Dispose()
{
} /// <summary>
/// 读取某行內容
/// </summary>
/// <param name="index"></param>
public string this[int index]
{
get
{
if ((index < 0) || (index >= m_Size))
{
throw new ArgumentOutOfRangeException();
}
return this.m_Strings[index];
}
set
{
if ((index < 0) || (index >= m_Size))
{
throw new ArgumentOutOfRangeException();
}
this.m_Strings[index] = value;
}
} /// <summary>
/// 调整內存大小
/// </summary>
protected void EnsureCapacity(int min)
{
if (this.m_Strings.Length < min)
{
int num1 = (this.m_Strings.Length == 0) ? 0x10 : (this.m_Strings.Length * 2);
if (num1 < min)
{
num1 = min;
}
this.Capacity = num1;
}
} /// <summary>
/// 追加一行
/// </summary>
public int AppendText(string value)
{
if (this.Count == m_Strings.Length)
{
EnsureCapacity(this.Count + 1);
} m_Strings[this.Count] = value;
m_Size++; return m_Size;
} /// <summary>
/// 插入一行
/// </summary>
/// <param name="index"></param>
public int InsertText(int index, string value)
{
if (index < 0)
{
index = 0;
} if (this.Count == m_Strings.Length)
{
EnsureCapacity(this.Count + 1);
} if (index < this.Count)
{
Array.Copy(this.m_Strings, index, this.m_Strings, index + 1, this.m_Size - index);
} m_Strings[index] = value;
m_Size++; return m_Size;
}
/// <summary>
/// 查找数据的位置
/// </summary>
public int IndexOf(string value)
{
return Array.IndexOf(this.m_Strings, value, 0, this.m_Size);
}
/// <summary>
/// 刪除一行
/// </summary>
/// <param name="index"></param>
public void RemoveAt(int index)
{
if ((index < 0) || (index >= this.m_Size))
{
throw new ArgumentOutOfRangeException();
}
this.m_Size--;
if (index < this.m_Size)
{
Array.Copy(this.m_Strings, index + 1, this.m_Strings, index, this.m_Size - index);
}
this.m_Strings[this.m_Size] = null;
}
/// <summary>
/// 输出字符串。
/// </summary>
public override string ToString()
{
System.Text.StringBuilder s = new System.Text.StringBuilder(this.Count); for (int i = 0; i < this.Count; i++)
{
s.Append(m_Strings[i] + "\r\n");
} return s.ToString();
}
/// <summary>
/// 输出字符串。
/// </summary>
/// <param name="startIndex"></param>
/// <param name="count"></param>
/// <returns></returns>
public string ToString(int startIndex, int count)
{
if (startIndex < 0)
{
startIndex = 0;
}
else if (startIndex >= this.Count)
{
return "";
}
if (count <= 0)
{
return "";
}
if (count + startIndex > this.Count)
{
count = this.Count - startIndex;
}
System.Text.StringBuilder s = new System.Text.StringBuilder(this.Count); for (int i = startIndex; i < count; i++)
{
s.Append(m_Strings[i] + "\r\n");
}
return s.ToString();
}
/// <summary>
/// 清除內容
/// </summary>
{
this.m_Size = 0;
} /// <summary>
/// 保存到文件
/// </summary>
/// <param name="fileName"></param>
/// <param name="encoding"></param>
public void SaveToFile(string fileName, System.Text.Encoding encoding)
{
System.IO.StreamWriter sw2 = new System.IO.StreamWriter(fileName, false, encoding);
for (int i = 0; i < this.Count; i++)
{
sw2.Write(m_Strings[i] + "\r\n");
}
sw2.Close();
}
public void SaveToFile(string fileName)
{
System.IO.StreamWriter sw2 = new System.IO.StreamWriter(fileName, false, System.Text.ASCIIEncoding.Default);
for (int i = 0; i < this.Count; i++)
{
sw2.Write(m_Strings[i] + "\r\n");
}
sw2.Close();
}
/// <summary>
/// 导入一文本文件
/// </summary>
/// <param name="fileName"></param>
public void LoadFromFile(string fileName)
{
this.Clear();
System.IO.StreamReader sr2 = new System.IO.StreamReader(fileName, System.Text.ASCIIEncoding.Default); while (sr2.Peek() >= 0)
{
this.AppendText(sr2.ReadLine());
}
sr2.Close();
}
public void LoadFromFile(string fileName, System.Text.Encoding encoding)
{
this.Clear();
System.IO.StreamReader sr2 = new System.IO.StreamReader(fileName, encoding);
while (sr2.Peek() >= 0)
{
this.AppendText(sr2.ReadLine());
}
sr2.Close();
}
}
}
把这个放到CS文件里,不要再用Com操作,直接生成一个Excle,就不需要去释放Excel进程了!
System.Runtime.InteropServices.Marshal.ReleaseComObject
我用控制台应用程序在测试的时候,只要关闭控制台程序, Excel.exe进程就会关闭.
如果控制台程序没有关闭,则可以看到Excel.exe这个进程,但不会对操作有影响.过一会儿就会自动关闭