为什么此代码为什么在写入的两行之间插入空白行(C#Excel Interop)? [英] Why does this code insert a blank row between two rows written (C# Excel Interop)?

问题描述

在使用Excel Interop的C#(.NET 4.5)Windows窗体应用程序中，我改编了

In my C# (.NET 4.5) Windows Forms app using Excel Interop, I adapted the code from here like so (removing redundant things grayed out by Resharper, and add "Type." before the "missing" args (and changing their case to upper):

private void WriteListObjectTestSheet()
{
    _xlSheetPlatypus.Cells[5, 1] = "Apple";
    _xlSheetPlatypus.Cells[6, 1] = "Strawberry";
    _xlSheetPlatypus.Cells[7, 1] = "Cashew";
    _xlSheetPlatypus.Cells[8, 1] = "Kumquat";
    _xlSheetPlatypus.Cells[9, 1] = "Pomegranate";
    _xlSheetPlatypus.Cells[10, 1] = "Banana";
    _xlSheetPlatypus.Cells[11, 1] = "Pineapple";
    _xlSheetPlatypus.Cells[12, 1] = "Kiwi";
    _xlSheetPlatypus.Cells[13, 1] = "Huckleberry";
    _xlSheetPlatypus.Cells[14, 1] = "Gooseberry";

    Excel.ListObject fruitList =
        _xlSheetPlatypus.
            ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange,
                _xlSheetPlatypus.Range[
                    _xlSheetPlatypus.Cells[4, 1],
                    _xlSheetPlatypus.Cells[4, 1]], 
                Type.Missing, Excel.XlYesNoGuess.xlNo);
}

将ListObject根据需要添加到第4行第1列(或"A")，并将水果"写入单元格，但不是从第5行(应为"Apple"的位置)开始，而是在第1行6，如您在此处看到的(突出显示了不需要的空白行):

The ListObject is added at row 4, column 1 (or "A") as desired, and the "fruits" are written into the cells, but not starting at row 5 (where "Apple" should be) but at row 6, as you can see here (unwanted blank row highlighted):

可能还有很多(更多)，但这可能至少是ListObject不知道要排序和过滤什么的部分原因，如单击时提供的选项所证明的那样.在这里:

There may be a (a lot more) to it, but this may be at least part of the reason why the ListObject has no idea what to sort and filter, as evidenced by the options it affords when it is clicked as seen here:

为什么要添加一个空白的第5行，我该如何防止呢?

Why is a blank row 5 added, and how can I prevent that?

推荐答案

从范围创建ListObject并说表没有标题时，它会将范围向下移动一行并添加Column1单元格.

When you create a ListObject from a range and saying the table has no header, It shifts the range one row down and add a Column1 cell.

在上面的代码中，您说创建了一个从[4,1]到[4,1]的表，并且说该表没有标题，因此它创建了一个具有一行的表，并添加了Column1标题并将所有内容移动了1行下.因此，结果就是您所说的擅长为您做的事情.

In the above code you said create a table from [4,1] to [4,1] and you said the table has no headers, So it creates a table with one row and adds a Column1 header and shifts all things 1 row down. So the result is what you said to excel to do for you.

您可以通过指定正确的范围并说您有标题来更正它.

You can correct it by specifying the correct range and saying you have a header.

在下面的代码中，我首先在[4,1]处添加一个Header，然后添加水果，最后创建从[4, 1]到[14, 1]的列表，并说该表具有标题.这样就保留了我的标题.

In the below code, I first add a Header at [4,1] and then add fruits and at last create the list from [4, 1] to [14, 1], and saying the table has headers. So it keeps my header.

using XL = Microsoft.Office.Interop.Excel;

private void button1_Click(object sender, EventArgs e)
{
    XL.Application application = new XL.Application();
    application.Visible = true;
    XL.Workbook book = application.Workbooks.Add();
    XL.Worksheet sheet = (XL.Worksheet)book.Worksheets[1];
    sheet.Cells[4, 1] = "Header";
    sheet.Cells[5, 1] = "Apple";
    sheet.Cells[6, 1] = "Strawberry";
    sheet.Cells[7, 1] = "Cashew";
    sheet.Cells[8, 1] = "Kumquat";
    sheet.Cells[9, 1] = "Pomegranate";
    sheet.Cells[10, 1] = "Banana";
    sheet.Cells[11, 1] = "Pineapple";
    sheet.Cells[12, 1] = "Kiwi";
    sheet.Cells[13, 1] = "Huckleberry";
    sheet.Cells[14, 1] = "Gooseberry";

    XL.ListObject fruitList = 
        sheet.ListObjects.Add(XL.XlListObjectSourceType.xlSrcRange,
            sheet.Range[sheet.Cells[4, 1], sheet.Cells[14, 1]], 
                Type.Missing, XL.XlYesNoGuess.xlYes);
}

这篇关于为什么此代码为什么在写入的两行之间插入空白行(C#Excel Interop)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！