使用C＃编写到现有的Excel文件 [英] Writing to an existing Excel File using c#

问题描述

我试图打开（或创建一个新XLS）Excel文件和写一些值吧。虽然下面的程序工作得很好，如果我只需创建一个新的xls文件，我遇到行的一些问题。

  ** mWorkBook = oXL.Workbooks.Open（路径，0，假，5，，，假Microsoft.Office.Interop.Excel.XlPlatform.xlWindows，，真，假0，真的，假的，假的）; ** 
  

下面的错误：
'LOG.xls'不能被访问。该文件可能已损坏，位于不响应的服务器上，或只读。
它不是只读的，它的损坏（因为是在运行时创建的某个文件）。 ？
有什么问题再

 私有静态Microsoft.Office.Interop.Excel.Workbook mWorkBook; 
私有静态Microsoft.Office.Interop.Excel.Sheets mWorkSheets; 
私有静态Microsoft.Office.Interop.Excel.Worksheet mWSheet1; 
私有静态Microsoft.Office.Interop.Excel.Application OXL; 
 
私人无效btnSignIn_Click（对象发件人，EventArgs五）
 {
 
路径字符串=D：\\LOG.xls （！File.Exists（路径））
如果
 {
 File.Create（路径）; 
} 
 
 
 OXL =新Microsoft.Office.Interop.Excel.Application（）; 
 oXL.Visible = TRUE; 
 oXL.DisplayAlerts = FALSE; 
 //在这一行
 mWorkBook = oXL.Workbooks.Open错误（路径，0，假，5，，假的，Microsoft.Office.Interop.Excel.XlPlatform.xlWindows， 真，假0，真的，假的，假的）; 
 
 //获取工作簿中的
 mWorkSheets = mWorkBook.Worksheets所有表; 
 
 //获取媒体链接存在表
 mWSheet1 =（Microsoft.Office.Interop.Excel.Worksheet）mWorkSheets.get_Item（工作表Sheet1）; 
 
 Microsoft.Office.Interop.Excel.Range范围= mWSheet1.UsedRange; 
 
 INT colCount = range.Columns.Count; 
 INT rowCount等= range.Rows.Count; 
 
为（INT指数= 1;指数< 15;指数++）
 {
 mWSheet1.Cells [rowCount等指数+ 1] = rowCount时+指数; 
 mWSheet1.Cells [rowCount等+指数，2] =新项+指数; 
} 
 
 mWorkBook.SaveAs（路径，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的（）; 
} 
  

解决方案

使用的 File.Create 是不恰当的在这里。它返回一个（空白）的FileStream ，而不是一个Excel文件（定义以.xls的路径不会创建一个Excel文件... ）。

和你不关闭和处置它，你有一个例外，当你试图打开它，因为它已经在使用（你将在任何异常即使这样，你放开它，因为您还没有建立在这个阶段的Excel文件）。

所以，如果工作簿中存在，可以通过使用 oXL.Workbooks.Open 打开。
如果工作簿不存在，您使用来创建它： oXL.Workbooks.Add（），然后调用 mWorkBook.SaveAs（...）方法来真正创造它。

  OXL =新Microsoft.Office.Interop.Excel.Application（）; 
 oXL.Visible = TRUE; 
 oXL.DisplayAlerts = FALSE; 
 
路径字符串=D：\\LOG.xls 
如果
 {
 mWorkBook = oXL.Workbooks.Add（File.Exists（路径）！）; 
} 
，否则
 {
 mWorkBook = oXL.Workbooks.Open（路径，0，假，5，，假的，的Microsoft.Office.Interop.Excel .XlPlatform.xlWindows，真，
假，0，真的，假的，假的）; 
} 
  

（望VB => C＃是正确的！）

而在你的另存为方法，您必须设置正确的 FILEFORMAT <如果你想创建一个特定版本/ STRONG> Excel文件（重新设置的.xls路径是不够的......）

可选的对象。该文件格式，当您保存文件使用。为了有效选项的
列表，请参阅FileFormat属性。对于现有的
文件，默认格式是指定的最后的文件格式; 作为一个新的
文件，默认正在使用的Excel版本的格式

希望这有助于。

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 ();
    }

解决方案

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.

这篇关于使用C＃编写到现有的Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！