C# 频繁向Excel表格中追加数据
现用C#读取一硬件中的数据,然后写入到Excel表格中,数据量很小,但要求频繁追加到Excel表格中(第一次新建一表格,后面全部将数据追加到Excel表格中).时间越快越好 小于0.2S.
现写一程序,测试了下,发现达不到要求,偶尔还会报错,提示无法访问文件,也就是上一周期的Excel表格没有关闭,下一周期又来打开.
代码如下:求各位指点,谢谢
private void AutoWriteToExcel()//向Excel表格中写入数据
{
System.Reflection.Missing miss = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.ApplicationClass Excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbooks books = (Microsoft.Office.Interop.Excel.Workbooks)Excel.Workbooks;
int k = 0;
IntPtr intptr = new IntPtr(Excel.Hwnd);
if (IsFirstLoad==true) //第二次写入时直接打开第一新建的表格
{
Microsoft.Office.Interop.Excel.Workbook book = books.Open(FileName, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);
Microsoft.Office.Interop.Excel.Worksheet sheet1 = (Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet;
Microsoft.Office.Interop.Excel.Range range = null;
int Row = sheet1.UsedRange.Count/3;
range = sheet1.UsedRange;
//range = sheet1.get_Range;
string[,] objVal = new string[4, 4];
for (int i = 0; i < this.listView1.Columns.Count; i++)
{
objVal[0, i] = this.listView1.Columns[i].Text;
}
for (int i = 0; i < 3; i++)
{
for (int j = 0; j < 3; j++)
{
objVal[i+1, j] = this.listView1.Items[i].SubItems[j].Text;
}
}
range = sheet1.get_Range("A"+(Row+1).ToString(), "C"+(Row+4).ToString());
range.NumberFormatLocal = "@";
range.Value2 = objVal;
//range.Cells.Interior.Color = System.Drawing.Color.FromArgb(255, 204, 153).ToArgb(); //设置单元格的背景色
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
Excel.DisplayAlerts = false;
Excel.AlertBeforeOverwriting = true;
//System.Windows.Forms.Application.DoEvents();
try
{
sheet1.SaveAs(FileName, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
book.Close(false, miss, miss);
books.Close();
Excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet1);
System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
//System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel);
KillSpecialExcel(Excel);
//GetWindowThreadProcessId(intptr, out k);
//System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
//p.Kill();
GC.Collect();
}
else //第一次写入,新建一表格
{
Microsoft.Office.Interop.Excel.Workbook book = (Microsoft.Office.Interop.Excel.Workbook)(books.Add(miss));
Microsoft.Office.Interop.Excel.Worksheet sheet1 = (Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet;
Microsoft.Office.Interop.Excel.Range range = null;
Microsoft.Office.Interop.Excel.Range range2 = null;
string[,] objVal = new string[4,4];
for (int i = 0; i < this.listView1.Columns.Count; i++)
{
objVal[0,i] = this.listView1.Columns[i].Text;
}
for (int i = 0; i < 3; i++)
{
for (int j = 0; j < 3; j++)
{
objVal[i+1,j] = this.listView1.Items[i].SubItems[j].Text;
}
}
range = sheet1.get_Range("A1", "C4");
range.NumberFormatLocal = "@";
range.Value2 = objVal;
//range.Cells.Interior.Color = System.Drawing.Color.FromArgb(255, 204, 153).ToArgb(); //设置单元格的背景色
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
Excel.DisplayAlerts = false;
Excel.AlertBeforeOverwriting = true;
//System.Windows.Forms.Application.DoEvents();
try
{
sheet1.SaveAs(FileName, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
book.Close(false, miss, miss);
books.Close();
Excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet1);
System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
//System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel);
GetWindowThreadProcessId(intptr, out k);
//System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
//p.Kill();
KillSpecialExcel(Excel); //杀死进程
GC.Collect();
IsFirstLoad = true;
}
}
public void KillSpecialExcel(Microsoft.Office.Interop.Excel.ApplicationClass Excel)//杀死线程
{
IntPtr intptr = new IntPtr(Excel.Hwnd);
try
{
if (Excel != null)
{
int lpdwProcessId;
GetWindowThreadProcessId(intptr, out lpdwProcessId);
System.Diagnostics.Process.GetProcessById(lpdwProcessId).Kill();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
[解决办法]
1.读的速度快并不一定代表一定要写的速度快，快速读取放入队列慢速写入也可
2.不需要频繁读入excel，你可以使用内存文件映射一次打开多次写入
3.不建议你目前使用的vsto方式，这种方式文件打开本身也需要花大量时间，可以使用ado.net直接读写excel，或者NOPI这类轻型读写组件
[解决办法]
Excel二次开发有几个注意点：
1. Office据说有一个“防攻击”保护措施，如果你的程序频繁地快速访问，特别是多线程的情况，它会抛出COM错误。
2. 可以在Excel.Application中打开多个工作簿并保持一段时间，不必频繁关闭和重新打开。不必那么急着关闭。
3. 最重要地，如果你给Excel的一个“单元格区域”赋值，千万不要去写什么悲催的循环语句。你应该写类似于
bok.WorkSheets["test1"].Range["W29].Resize(200，35）。Value2 = arr;
这类语句，直接用一个二维数组给区域赋值就行了。你会发现这可以快几百倍速度！
4. 大多数程序员把Excel当作“白纸”随便乱图画，根本不认真学习。只有少数人真正去学习Excel二次开发。你不应该仅仅会谁都随便可以写出的那几行代码，而应该使用
wb.Application.EnableEvents = false;
wb.Application.Interactive = false;
wb.Application.ScreenUpdating = false;
和
wb.Application.EnableEvents = true;
wb.Application.Interactive = true;
wb.Application.ScreenUpdating = true;
等语句，证明你真的学过一点 Excel 二次开发技术。
[解决办法]
晕死，刚刚回复上面的，csdn竟然就垮掉了、不能回复了。
我又看了一下，你确实是以二维数组的方式写入数据的。sorry。
[解决办法]
时间快还是建议用NPOI 记得释放