如何添加一个excel ListObject到给定的工作表在C＃中？ [英] How to add an excel ListObject to a given worksheet in C#?

问题描述

我目前正在C＃中使用几种方法（表值函数）开发一个excel加载项，这些方法将可用于excel用户和程序员（VBA）。

我如何编写一个方法，它为给定的Excel工作表添加一个新的ListObject（excel表），并将给定的DataTable绑定为数据源？如下所示：

 使用Excel = Microsoft.Office.Interop.Excel; 
 ... 
 [ClassInterface（ClassInterfaceType.AutoDual）] 
 public class TableFunctions {
 ... 
 
 public Excel.ListObject CreateListObject（Excel。工作表ws，string TableName，DataTable dt，string CellStr =A1）
 {
 ... 
} 
  

将Worksheet对象作为参数发送的方法显然不起作用。还是可以吗

解决方案

经过一些研究，我发现了一个问题的答案，如何以编程方式将ListObject（excel表）添加到工作表中C＃：

  public Excel.ListObject WriteToExcelTable（Excel.Worksheet WSheet，string TableName，string CellStr =A1，bool ClearSheetContent = false）
 {
 Excel.Range范围; 
 
 if（ClearSheetContent）
 WSheet.Cells.ClearContents（）; //清除页面内容
 
 //获取由CellStr 
范围=（Excel.Range）定义的范围的左上角WSheet.get_Range（CellStr）.Cells [1，1]; // 
 
 //将表写入范围
 HelperFunc.WriteTableToExcelSheet（WSheet，this._tbl，range.Address）; 
 
 //派生表的范围，表头的+1行
 range = range.get_Resize（this.RowCount + 1，this.ColumnCount）; 
 
 //将ListObject添加到工作表
 
 // ListObjects.AddEx方法
 // http://msdn.microsoft.com/en-us/library/ microsoft.office.interop.excel.listobjects.addex％28v = office.14％29.aspx 
 
 Excel.ListObject tbl =（Excel.ListObject）WSheet.ListObjects.AddEx（
 SourceType ：Excel.XlListObjectSourceType.xlSrcRange，
来源：范围，
 XlListObjectHasHeaders：Excel.XlYesNoGuess.xlYes）; 
 
 //设置excel表的名称
 tbl.Name = TableName; 
 
 //返回excel表（ListObject）
 return（Excel.ListObject）tbl; 
} 
  

请参阅我的文章关于excel和.net集成的这个和其他相关代码。

I am currently developing an excel add-in in C# with several methods (table valued functions) that will be available to excel users and programmers (VBA).

How can I write a method which adds a new ListObject (excel table) to the given excel worksheet, and bind the given DataTable as data source? Something like the following:

using Excel = Microsoft.Office.Interop.Excel;
...
[ClassInterface(ClassInterfaceType.AutoDual)]
public class TableFunctions {
...

public Excel.ListObject CreateListObject(Excel.Worksheet ws, string TableName, DataTable dt, string CellStr = "A1")
{
...
}

This approach, sending the Worksheet object as parameter apparently doesn't work. Or could it?

解决方案

After some research I found an answer to my question, how to add a ListObject (excel table) to a worksheet programmatically in C#:

public Excel.ListObject WriteToExcelTable(Excel.Worksheet WSheet, string TableName, string CellStr = "A1", bool ClearSheetContent = false)
{
    Excel.Range range;

    if (ClearSheetContent)
        WSheet.Cells.ClearContents();  // clear sheet content

    // get upper left corner of range defined by CellStr
    range = (Excel.Range)WSheet.get_Range(CellStr).Cells[1, 1];   //

    // Write table to range
    HelperFunc.WriteTableToExcelSheet(WSheet, this._tbl, range.Address);

    // derive range for table, +1 row for table header
    range = range.get_Resize(this.RowCount + 1, this.ColumnCount);

    // add ListObject to sheet

    // ListObjects.AddEx Method 
    // http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.listobjects.addex%28v=office.14%29.aspx

    Excel.ListObject tbl = (Excel.ListObject)WSheet.ListObjects.AddEx(
        SourceType: Excel.XlListObjectSourceType.xlSrcRange,
        Source: range,
        XlListObjectHasHeaders: Excel.XlYesNoGuess.xlYes);

    // set name of excel table
    tbl.Name = TableName;

    // return excel table (ListObject)
    return (Excel.ListObject)tbl;
}

See my article about this and other related code for excel and .net integration.

这篇关于如何添加一个excel ListObject到给定的工作表在C＃中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！