c#中的数据导出至EXCEL后,EXCEL进程不能退出

tytyh970410 2007-07-02 10:43:02
我写了一个类,内有二个方法,其中一个方法(TableSaveExcel)能正常退出,另一个(ViewSaveExcel)用到了Range保存大量数据,EXCEL进程不能退出
using System;
using Excel;
using System.Reflection;
using System.Windows.Forms;

namespace 管理系统主程序
{
/// <summary>
/// SaveExcel 的摘要说明。
/// </summary>
public class SaveExcel
{
public SaveExcel()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
public void TableSaveExcel(System.Data.DataTable tempTableName,string filename)
{
try
{
Excel.Application exc = new Excel.Application();
if (exc == null)
{
MessageBox.Show("不能建立EXCEL对象,请在机器上安装EXCEL","提示",MessageBoxButtons.OK,MessageBoxIcon.Error);
return;
}

// exc.set_Visible(0, true);
// exc.Visible=true;
object MissValue = System.Reflection.Missing.Value;
Workbooks workbooks = exc.Workbooks;
_Workbook workbook = workbooks.Add(MissValue);
// _Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Sheets sheets = workbook.Worksheets;

_Worksheet worksheet = (_Worksheet) sheets.get_Item(1);
// worksheet.Cells.
int i,numCols=tempTableName.Rows.Count,j;
int lnumCols = tempTableName.Columns.Count;
for (i = 0;i< lnumCols;i++) //从第二列开始,不显示第一列的“职员编号”
{
worksheet.Cells[1,i+1]=tempTableName.Columns[i].ColumnName;
}
string ttyy;
for (i = 0;i< numCols;i++) //从第二列开始,不显示第一列的“职员编号”
{
for (j = 0;j< lnumCols;j++) //从第二列开始,不显示第一列的“职员编号”
{
ttyy="'"+tempTableName.Rows[i][j].ToString();
worksheet.Cells[i+2,j+1]=ttyy;
}
}
worksheet.SaveAs(filename, MissValue, MissValue, MissValue, MissValue, MissValue, Excel.XlSaveAsAccessMode.xlNoChange, MissValue, MissValue);
workbooks.Close();
exc.Quit();
worksheet=null;workbooks=null;exc=null;GC.Collect();
}
catch
{
MessageBox.Show("导出至EXCEL错误!!!!","提示",MessageBoxButtons.OK,MessageBoxIcon.Error);
return ;
}
}
public void ViewSaveExcel(System.Data.DataView tempTableName,string filename)
{
try
{
// System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
// System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Excel.Application exc = new Excel.Application();
if (exc == null)
{
MessageBox.Show("不能建立EXCEL对象,请在机器上安装EXCEL","提示",MessageBoxButtons.OK,MessageBoxIcon.Error);
return;
}

// exc.set_Visible(0, true);
// exc.Visible=true;
object MissValue = System.Reflection.Missing.Value;
Workbooks workbooks = exc.Workbooks;
// _Workbook workbook = workbooks.Add(MissValue);
_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Sheets sheets = workbook.Worksheets;

_Worksheet worksheet = (_Worksheet) sheets.get_Item(1);
// worksheet.Cells.
int iRowCount=tempTableName.Count;
int lnumCols = tempTableName.Table.Columns.Count;
for (int i = 0;i< lnumCols;i++) //从第二列开始,不显示第一列的“职员编号”
{
worksheet.Cells[1,i+1]=tempTableName.Table.Columns[i].ColumnName;
}
Range xlRang = null;
int iParstedRow = 0, iCurrSize = 0;
int iEachSize = 2000; // each time you
object[,] objVal = new object[iEachSize, lnumCols];
try
{
iCurrSize = iEachSize;
while (iParstedRow < iRowCount)
{
if ((iRowCount - iParstedRow) < iEachSize)
iCurrSize = iRowCount - iParstedRow;
for (int i = 0; i < iCurrSize; i++)
{
for (int j = 0; j < lnumCols; j++)
objVal[i, j] ="'" +tempTableName[i + iParstedRow][j].ToString();
}
xlRang = worksheet.get_Range("A" + ((int)(iParstedRow + 2)).ToString(), ((char)('A' + lnumCols - 1)).ToString() + ((int)(iParstedRow + iCurrSize + 1)).ToString());

xlRang.Value2 = objVal;

iParstedRow = iParstedRow + iCurrSize;
}

// change number format of the data column

}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return ;
}
// exc.Visible=true;

worksheet.SaveAs(filename, MissValue, MissValue, MissValue, MissValue, MissValue, Excel.XlSaveAsAccessMode.xlNoChange, MissValue, MissValue);
workbooks.Close();
exc.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlRang);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(exc);
worksheet=null;workbooks=null;exc=null;xlRang = null;GC.Collect();
// System.Threading.Thread.CurrentThread.CurrentCulture = CurrentCI;
}
catch
{
MessageBox.Show("导出至EXCEL错误!!!!","提示",MessageBoxButtons.OK,MessageBoxIcon.Error);
return ;
}
}
}
}
...全文
744 5 打赏 收藏 转发到动态 举报
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
tytyh970410 2007-07-03
  • 打赏
  • 举报
回复
huiaccp
您的方案很好,但身份证后4位全变为0,我现在是在前加'解决但太不像样
有没有更好的办法
再请问您:调试中int变量变了,如0变为0x0,跟踪调试我必须将其转换为字符串才能确定值,是不是那个环境要设一下.
非常感谢您
jinta2001 2007-07-03
  • 打赏
  • 举报
回复
你为什么要把exc.Visible=true;去掉呢,你让excel可见,然后马上exc.Quit();就可以退出的。

如果是杀进程,需要判断是不是你自己开的进程,否则把别人在使用的也杀了问题就大条了

如果是导出简单格式,那是可以使用.CSV格式。
huiaccp 2007-07-03
  • 打赏
  • 举报
回复
using Excel;
永遠的錯誤
看看我是怎麼使用的
Public Sub DsToExcel(ByVal ds As DataSet, ByVal SaveDl As SaveFileDialog)
Dim strFlName As String
With SaveDl
.Title = "請選擇要匯出的csv文件名"
.Filter = "Excel文件(*.xls)|*.xls"
.FileName = ""
End With
If SaveDl.ShowDialog = Windows.Forms.DialogResult.OK Then
If SaveDl.FileName <> "" Then
strFlName = SaveDl.FileName()
Else
Exit Sub
End If
Else
Exit Sub
End If
Dim fs As FileStream = New FileStream(strFlName, FileMode.Create, FileAccess.Write)
Dim sw As StreamWriter = New StreamWriter(fs, System.Text.Encoding.GetEncoding("BIG5"))
Dim ColName As String = "", RowName As String = ""

Dim Col As DataColumn
Dim Row As DataRow
For Each Col In ds.Tables(0).Columns
ColName = ColName + Col.ColumnName + vbTab
Next
sw.WriteLine(ColName)
For Each Row In ds.Tables(0).Rows
For Each Col In ds.Tables(0).Columns
RowName = RowName + Replace(Row(Col.ColumnName).ToString, ",", " ") + vbTab
'如果是c# ,vbTab 改為 \t
Next
sw.WriteLine(RowName)
RowName = ""
Next
sw.Close()
MessageBox.Show("數據已經成功導入EXCEL文件" & strFlName, "數據導出", MessageBoxButtons.OK, MessageBoxIcon.Information)
End Sub
lovingkiss 2007-07-03
  • 打赏
  • 举报
回复
VB
Private Sub KillExcel()
Try
Dim processes As Process() = Diagnostics.Process.GetProcesses
Dim process As Process
For Each process In processes
If process.ProcessName.ToLower.Equals("excel") Then
process.Kill()
End If
Next
Catch exception As Exception
MessageBox.Show(("ERROR " & exception.Message))
End Try
End Sub

C#
private void KillExcel()
{
try {
Process[] processes = Diagnostics.Process.GetProcesses;
Process process;
foreach ( process in processes) {
if (process.ProcessName.ToLower.Equals("excel"))
{
process.Kill();
}
}
}
catch (Exception exception) {
MessageBox.Show(("ERROR " + exception.Message));
}
}

===========================================================
博客空间:http://blog.csdn.net/lovingkiss
资源下载:http://download.csdn.net/user/lovingkiss
Email:loving-kiss@163.com
本人说明:<我的帖子我做主,结贴率保持100%>
优惠接单开发,信誉保证,Q66840199(请注明业务/技术咨询)
===========================================================
lovingkiss 2007-07-03
  • 打赏
  • 举报
回复
必须要手动关闭进程的;

===========================================================
博客空间:http://blog.csdn.net/lovingkiss
资源下载:http://download.csdn.net/user/lovingkiss
Email:loving-kiss@163.com
本人说明:<我的帖子我做主,结贴率保持100%>
优惠接单开发,信誉保证,Q66840199(请注明业务/技术咨询)
===========================================================

110,538

社区成员

发帖
与我相关
我的任务
社区描述
.NET技术 C#
社区管理员
  • C#
  • Web++
  • by_封爱
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告

让您成为最强悍的C#开发者

试试用AI创作助手写篇文章吧