代码之家 › 专栏 › 技术社区 › Chris Hayes

强制列顺序,Excel数据表

excel c#

1

Chris Hayes · 技术社区 · 15 年前

我有一个Excel工作簿,用作报表模板。我在C应用程序中更改每个数据透视和数据表上的数据源。当我更改数据表数据源时,它会用镊子夹住列。是否有强制列顺序的方法?

private void RefreshRawData(string dataSource, string connection)
{
    xl._Worksheet ws = (xl._Worksheet)xlTemplate.Worksheets["Raw Data"];
    xl.ListObject table = ws.ListObjects["Table_ExternalData_1"];
    xl.QueryTable qt = table.QueryTable;
    qt.CommandText = dataSource;
    qt.Connection = GetExcelConnectionString((string)qt.Connection);
    qt.BackgroundQuery = false;
    qt.Refresh(m);
    Marshal.ReleaseComObject(ws);
    Marshal.ReleaseComObject(table);
    Marshal.ReleaseComObject(qt);
    ws = null;
    table = null;
    qt = null;
}

1 回复 | 直到 15 年前

1

Chris Hayes 15 年前

欢迎来到Interop的野外世界。

我不得不删除并重新创建表。

请注意ListObject.Add函数中的参数它们是奇怪的生物。

private void RefreshRawData(string dataSource, string connection)
{
    xl._Worksheet ws = (xl._Worksheet)xlTemplate.Worksheets["Raw Data"];
    xl.ListObject table = ws.ListObjects["Table_ExternalData_1"];
    xl.QueryTable qt = table.QueryTable;
    string connStr = GetExcelConnectionString((string)qt.Connection);
    table.Delete();
    xl.Range r = ws.get_Range("A1", "A1");
    table = ws.ListObjects.Add(xl.XlListObjectSourceType.xlSrcExternal, (object)connStr, m, xl.XlYesNoGuess.xlGuess, r);
    qt = table.QueryTable;
    qt.CommandType = xl.XlCmdType.xlCmdSql;
    qt.CommandText = dataSource;
    qt.RowNumbers = false;
    qt.FillAdjacentFormulas = false;
    qt.PreserveFormatting = true;
    qt.RefreshOnFileOpen = false;
    qt.BackgroundQuery = true;
    qt.RefreshStyle = xl.XlCellInsertionMode.xlInsertDeleteCells;
    qt.SavePassword = false;
    qt.SaveData = true;
    qt.AdjustColumnWidth = true;
    qt.RefreshPeriod = 0;
    qt.PreserveColumnInfo = true;
    qt.ListObject.DisplayName = "Table_ExternalData_1";
    qt.Refresh(false);
    Marshal.ReleaseComObject(ws);
    Marshal.ReleaseComObject(table);
    Marshal.ReleaseComObject(qt);
    ws = null;
    table = null;
    qt = null;
}