Writing to an existing Excel File using c#

问题:

I am trying to open (or create a new xls) Excel file and write some values to it. Although, the program below works just fine if I simply create a new xls file, I encounter the some problem in line

**mWorkBook = oXL.Workbooks.Open (path, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);**

Here's the error: 'LOG.xls' cannot be accessed. The file may be corrupted, located on a server that is not responding, or read-only. It's not read-only, it's not corrupted(because sometime the file is created on Run Time). What is the problem then?

private static Microsoft.Office.Interop.Excel.Workbook mWorkBook;
    private static Microsoft.Office.Interop.Excel.Sheets mWorkSheets;
    private static Microsoft.Office.Interop.Excel.Worksheet mWSheet1;
    private static Microsoft.Office.Interop.Excel.Application oXL;

    private void btnSignIn_Click ( object sender, EventArgs e )
    {

        string path = "D:\\LOG.xls";
        if(!File.Exists(path))
        {
            File.Create (path);
        }


        oXL = new Microsoft.Office.Interop.Excel.Application ();
        oXL.Visible = true;
        oXL.DisplayAlerts = false;
        //error on this line
        mWorkBook = oXL.Workbooks.Open (path, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);

        //Get all the sheets in the workbook
        mWorkSheets = mWorkBook.Worksheets;

        //Get the allready exists sheet
        mWSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item ("Sheet1");

        Microsoft.Office.Interop.Excel.Range range = mWSheet1.UsedRange;

        int colCount = range.Columns.Count;
        int rowCount = range.Rows.Count;

        for ( int index = 1; index < 15; index++ )
        {
            mWSheet1.Cells [rowCount + index, 1] = rowCount + index;
            mWSheet1.Cells [rowCount + index, 2] = "New Item" + index;
        }

        mWorkBook.SaveAs (path, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
        Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
        Missing.Value, Missing.Value, Missing.Value,
        Missing.Value, Missing.Value);

        mWorkBook.Close (Missing.Value, Missing.Value, Missing.Value);
        mWSheet1 = null;

        mWorkBook = null;

        oXL.Quit ();
        GC.WaitForPendingFinalizers ();
        GC.Collect ();
        GC.WaitForPendingFinalizers ();
        GC.Collect ();
    }

回答1:

The using of File.Create is not appropriate here. It returns a (Blank) FileStream, not an Excel File (Define the path with .xls will not create an Excel File...).

And as you do not close and dispose it, you have an exception when you try to open it because it is already in use (and you will have an exception in any way even if you release it, because you have not create an Excel file at this stage).

So if the workbook exists, you can open it by using oXL.Workbooks.Open. If the workbook does not exist, YOU HAVE to create it by using: oXL.Workbooks.Add() and then you call mWorkBook.SaveAs(...) method to really create it.

oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = true;
oXL.DisplayAlerts = false;

string path = "D:\\LOG.xls";
if (!File.Exists(path)) 
{
    mWorkBook = oXL.Workbooks.Add;
} 
else
{
    mWorkBook = oXL.Workbooks.Open(path, 0, false, 5, "", "", false,    Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true,
        false, 0, true, false, false);
}

(Hope the VB=>C# is correct!)

And in your SaveAs method, you will have to set the correct FileFormat if you want to create a specific version of Excel file (again set .xls in the path is not enough...)

Optional Object. The file format to use when you save the file. For a list of valid choices, see the FileFormat property. For an existing file, the default format is the last file format specified; for a new file, the default is the format of the version of Excel being used.

Hope this helps.

回答2:

If the workbook does not exist, the better way to create one would be as such:

Excel.Workbook newWorkbook = excelApplication.Workbooks.Add();

File.Create() returns an instance of FileStream, which you need to close if you want to access the file. You can change..

    if(!File.Exists(path))
    {
        File.Create (path);
    }

to

    if(!File.Exists(path))
    {
        File.Create (path).Close();
    }