我现在想要的功能是,点击按钮,进行计算,把计算结果放到excel中并打开,自动保存(没有提醒,自动覆盖),直到用户关闭后释放进程。现在可以成功生成excel文件并打开,但是保存时不能指定文件夹,只可以保存文件名到默认文件夹。
worksheet.SaveAs(name, miss, miss, miss, miss, miss, miss, miss, miss, miss);
可以,但是
worksheet.SaveAs(floder+"//"name, miss, miss, miss, miss, miss, miss, miss, miss, miss);
不行。还有导出excel后,excel进程一直在内存中,尽管我关闭打开的excel后也仍然在内存中。如果进行垃圾处理,又会关闭新打开excel。完整代码如下
private void btCalculate_Click(object sender, EventArgs e)
{
object ms = Type.Missing; Microsoft.Office.Interop.Excel.Application xlApp=new Microsoft.Office.Interop.Excel.Application(); if(xlApp==null)
{
MessageBox.Show("Cannot use this function, install Microsoft Office Excel first.");
return;
}
Microsoft.Office.Interop.Excel.Workbook workbook = xlApp.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
Microsoft.Office.Interop.Excel.Range range; worksheet.Cells[1,1] = "Description";
worksheet.Cells[1, 2] = "Price";
worksheet.Cells[1, 3] = "date"; worksheet.Application.ActiveWindow.SplitRow = 1;
worksheet.Application.ActiveWindow.FreezePanes = true; range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 3]);
range.Activate();
range.Select();
range.AutoFilter(1, Type.Missing, Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true);
range.Font.Bold = true;
range.Font.Color = 10;
range.Font.Size = 14; try
{
int count = 2;
for (int i = 0; i < 6; i++)
{
for (int j = 0; j < 7; j++)
{
if (btns[i, j].Visible == true)
{
if (btns[i, j].Font.Italic)
{
ds = d.ExecDataSet("select * from note where username='" + user + "' and year = '" + currentDate.Year.ToString() + "' and month= '" + currentDate.Month.ToString() + "' and day ='" + btns[i, j].Text + "'");
String memory = ds.Tables[0].Rows[0][7].ToString();
String[] temp1 = memory.Split(new char[] { '\n' });
for (int k = 0; k < temp1.Length; k++)
{
String[] temp2 = temp1[k].Split(new char[] { ':' });
worksheet.Cells[count, 1] = temp2[0];
worksheet.Cells[count, 2] = double.Parse(temp2[1]);
worksheet.Cells[count, 3] = currentDate.Month.ToString() + "/" + btns[i, j].Text + "/" + currentDate.Year.ToString();
count++;
}
}
}
}
}
worksheet.Cells[count, 2] = "Total:";
range = worksheet.get_Range(worksheet.Cells[count + 1, 2], worksheet.Cells[count + 1, 2]);
range.Formula = "=sum(" + "B2:" + "B" + count + ")";
range.Font.Color = 150;
range.Font.Bold = true;
range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[count + 1, 3]);
range.EntireColumn.AutoFit();
range = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[count + 1, 3]);
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
xlApp.Visible = true;
String name = "c:\\Bills" + this.user + "\\" + currentDate.Month.ToString() + "_" + currentDate.Year.ToString() + ".xls";
worksheet.SaveAs(name, ms, ms, ms, ms, ms, ms, ms, ms, ms);
xlApp = null;
}
catch (Exception exc)
{
xlApp.Quit();
xlApp = null;
GC.Collect();
MessageBox.Show(exc.Message);
}
}我要如何编写?
worksheet.SaveAs(name, miss, miss, miss, miss, miss, miss, miss, miss, miss);
可以,但是
worksheet.SaveAs(floder+"//"name, miss, miss, miss, miss, miss, miss, miss, miss, miss);
不行。还有导出excel后,excel进程一直在内存中,尽管我关闭打开的excel后也仍然在内存中。如果进行垃圾处理,又会关闭新打开excel。完整代码如下
private void btCalculate_Click(object sender, EventArgs e)
{
object ms = Type.Missing; Microsoft.Office.Interop.Excel.Application xlApp=new Microsoft.Office.Interop.Excel.Application(); if(xlApp==null)
{
MessageBox.Show("Cannot use this function, install Microsoft Office Excel first.");
return;
}
Microsoft.Office.Interop.Excel.Workbook workbook = xlApp.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
Microsoft.Office.Interop.Excel.Range range; worksheet.Cells[1,1] = "Description";
worksheet.Cells[1, 2] = "Price";
worksheet.Cells[1, 3] = "date"; worksheet.Application.ActiveWindow.SplitRow = 1;
worksheet.Application.ActiveWindow.FreezePanes = true; range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 3]);
range.Activate();
range.Select();
range.AutoFilter(1, Type.Missing, Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true);
range.Font.Bold = true;
range.Font.Color = 10;
range.Font.Size = 14; try
{
int count = 2;
for (int i = 0; i < 6; i++)
{
for (int j = 0; j < 7; j++)
{
if (btns[i, j].Visible == true)
{
if (btns[i, j].Font.Italic)
{
ds = d.ExecDataSet("select * from note where username='" + user + "' and year = '" + currentDate.Year.ToString() + "' and month= '" + currentDate.Month.ToString() + "' and day ='" + btns[i, j].Text + "'");
String memory = ds.Tables[0].Rows[0][7].ToString();
String[] temp1 = memory.Split(new char[] { '\n' });
for (int k = 0; k < temp1.Length; k++)
{
String[] temp2 = temp1[k].Split(new char[] { ':' });
worksheet.Cells[count, 1] = temp2[0];
worksheet.Cells[count, 2] = double.Parse(temp2[1]);
worksheet.Cells[count, 3] = currentDate.Month.ToString() + "/" + btns[i, j].Text + "/" + currentDate.Year.ToString();
count++;
}
}
}
}
}
worksheet.Cells[count, 2] = "Total:";
range = worksheet.get_Range(worksheet.Cells[count + 1, 2], worksheet.Cells[count + 1, 2]);
range.Formula = "=sum(" + "B2:" + "B" + count + ")";
range.Font.Color = 150;
range.Font.Bold = true;
range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[count + 1, 3]);
range.EntireColumn.AutoFit();
range = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[count + 1, 3]);
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
xlApp.Visible = true;
String name = "c:\\Bills" + this.user + "\\" + currentDate.Month.ToString() + "_" + currentDate.Year.ToString() + ".xls";
worksheet.SaveAs(name, ms, ms, ms, ms, ms, ms, ms, ms, ms);
xlApp = null;
}
catch (Exception exc)
{
xlApp.Quit();
xlApp = null;
GC.Collect();
MessageBox.Show(exc.Message);
}
}我要如何编写?
Worksheet sheet = sheets.Open(...);
...
Marshal.ReleaseComObject(sheets);
Marshal.ReleaseComObject(sheet);
finally
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheetData);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbookData);
System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel);
}
或者使用API直接关掉指定的进程
[DllImport("user32.dll", SetLastError = true)]
static extern int GetWindowThreadProcessId(IntPtr hWnd, out int lpdwProcessId);public void KillSpecialExcel(Excel.Application m_objExcel)
{
try
{
if (m_objExcel != null)
{
int lpdwProcessId;
GetWindowThreadProcessId(new IntPtr(m_objExcel.Hwnd), out lpdwProcessId);
System.Diagnostics.Process.GetProcessById(lpdwProcessId).Kill();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
如果不行,在试试1L的方法