在打开时更新现有的Excel文件

I read a lot about how to communicate from C# to Excel and saw some good references.

我读了很多关于如何从C＃到Excel的通信，并看到了一些很好的参考资料。

The thing is I'm looking for an easy way to update existing excel file while it is still open, using the most advanced way (linq for example) and not OLEDB.

问题是我正在寻找一种简单的方法来更新现有的excel文件，当它仍处于打开状态时，使用最先进的方式（例如linq）而不是OLEDB。

This should be few lines of code describing how can I read current cell, update his value and take into consideration the file might not be exist, but if it does exist and open, it will just update the file without giving the notification the file is already exist. If the file doesn't exist it will create a new one.

这应该是几行代码，描述如何读取当前单元格，更新其值并考虑文件可能不存在，但如果它确实存在并打开，它将只更新文件而不通知文件是已经存在。如果该文件不存在，它将创建一个新文件。

SO: 1. connect to an excel file, check if it exist, if not create one
2. read from cell
3. update cell
4. do this while the excel sheet can be still open wild.

SO：1。连接到excel文件，检查它是否存在，如果没有创建一个2.从单元格读取3.更新单元格4.执行此操作，同时excel表格仍可以开放。

I already visited the following places:

我已经访问了以下地方：

http://social.msdn.microsoft.com/Forums/vstudio/en-US/ef11a193-54f3-407b-9374-9f5770fd9fd7/writing-to-excel-using-c

Updating an excel document programmatically

以编程方式更新Excel文档

Update specific cell of excel file using oledb

使用oledb更新excel文件的特定单元格

I used the following code:

我使用了以下代码：

   if (File.Exists(@"C:\\temp\\test.xls"))
        {
            Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbooks workBooks = excelApp.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook workBook = workBooks.Open(@"C:\\temp\\test.xls");
            Microsoft.Office.Interop.Excel.Worksheet workSheet = workBook.Worksheets.get_Item(1);
            int nColumns = workSheet.UsedRange.Columns.Count;
            int nRows = workSheet.UsedRange.Rows.Count;

            for (int i = 2; i < nRows; i++)
            {
                workSheet.Columns["1","A"] = "test";
            }

            workBook.Save();
            workBook.Close();
        }

2 个解决方案

#1

So I use VSTO Contrib to help out with COM Interop and memory management and that's why you see .WithComCleanup().

所以我使用VSTO Contrib来帮助解决COM Interop和内存管理问题，这就是你看到.WithComCleanup（）的原因。

To open up a spreadsheet:

要打开电子表格：

try
{
    using (var xlApp = new Microsoft.Office.Interop.Excel.Application().WithComCleanup())
    using (var wrkbooks = xlApp.Resource.Workbooks.WithComCleanup())
    using (var wrkbook = wrkbooks.Resource.Open(filePath, false, true).WithComCleanup())
    {

If the excel file is already open, then to get around the Read-Only follow this tip:

如果excel文件已经打开，那么为了绕过只读，请按照以下提示操作：

wrkbooks.Resource.Open(filePath, false, FALSE).WithComCleanup())

Here's how I iterate though the sheets (note that some Excel sheets are ChartSheets):

这是我如何迭代表格（注意一些Excel表格是ChartSheets）：

foreach (object possibleSheet in xlApp.Resource.Sheets)
{
    Microsoft.Office.Interop.Excel.Worksheet aSheet = possibleSheet as Microsoft.Office.Interop.Excel.Worksheet;
    if (aSheet == null)
        continue;

Here is a quick way to get a reference to the sheet you're interested in:

以下是获取您感兴趣的工作表的快速方法：

activeSheet = wrkbook.Resource.Sheets[sheetToImport];

You read and write to cells just as you've identified:

您正如您所确定的那样读取和写入单元格：

for (int i = 2; i < nRows; i++)
{
  activeSheet.Columns["1","A"] = "test";
}

Here is how I close Excel:

以下是我关闭Excel的方法：

MathematicaAPI.XlHelper.CloseExcel((Worksheet)activeSheet, (Workbook)wrkbook.Resource , (Workbooks)wrkbooks.Resource);

public static void CloseExcel(Worksheet activeSheet, Workbook wrkbook, Workbooks wrkbooks)
{
    //http://support.microsoft.com/kb/317109 -> excel just wont close for some reason
    if (activeSheet != null)
    {
        Marshal.FinalReleaseComObject(activeSheet);
        activeSheet = null;
    }
    if (wrkbook != null)
    {
        wrkbook.Saved = true;
        wrkbook.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlDoNotSaveChanges);
    }
    if (wrkbooks != null)
    {
        wrkbooks.Close();
    }
    GC.Collect();
    GC.WaitForPendingFinalizers();
    GC.Collect();
    GC.WaitForPendingFinalizers();
}

Sometimes Excel just wont close and you have to kill it (after trying to close it properly of course) - I dont recommend this, but if you cant track down the un-disposed memory and all else fails then...

有时Excel只是不会关闭你必须杀死它（当然试图正确关闭它） - 我不推荐这个，但如果你不能追踪未处置的内存而其他一切都失败那么......

if (xlApp != null)
{
  ExcelDataSourceHelper.GetWindowThreadProcessId(new IntPtr(xlApp.Resource.Hwnd), ref excelProcessId);
}

if (excelProcessId > 0)
{
    XlHelper.KillProcess(excelProcessId);
}

public static void KillProcess(int excelProcessId)
{
    if (excelProcessId > 0)
    {
        System.Diagnostics.Process ExcelProc = null;
        try
        {
            ExcelProc = System.Diagnostics.Process.GetProcessById(excelProcessId);
            if (ExcelProc != null)
            {
                ExcelProc.Kill();
            }
        }
        catch
        { }
    }
}

Note: I reduce the chances of needing to kill Excel by using VSTO Contrib with Using's.

注意：通过使用VSTO Contrib with Using，我减少了需要杀死Excel的几率。

#2

OK thank you all for trying to solve the issue The solution was using Excel 2011/2013 Add-In which can communicate excel as a plugin

好的，谢谢大家试图解决问题解决方案是使用Excel 2011/2013加载项，可以将excel作为插件进行通信

create an application-level add-in for Microsoft Office Excel. The features that you create in this kind of solution are available to the application itself, regardless of which workbooks are open.

为Microsoft Office Excel创建应用程序级外接程序。无论打开哪些工作簿，您在此类解决方案中创建的功能都可供应用程序本身使用。

You can visit MSDN

您可以访问MSDN

#1