vba 删除数据表中的列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2425296/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Remove column in datatable
提问by Suryakavitha
I am exporting a datatable to an excel sheet successfully... In that excel sheet i have to display the columns(customerid,Productname,referenceno) of the data table except the last column.... now how can i display the data table in excel without display the last column(referenceno)...
我正在成功地将数据表导出到 Excel 表...在该 Excel 表中，我必须显示数据表的列（customerid、Productname、referenceno），但最后一列除外......现在我如何显示数据表在excel中不显示最后一列（referenceno）...
anyone tell me the solution of this problem.. Thanks in Advance..
任何人告诉我这个问题的解决方案.. 提前致谢..
here is my code for export datatable to excel:
这是我将数据表导出到 excel 的代码：
System.Data.DataTable dt = clsobj.convert_datagrid_orderlist_to_datatable(dvgorderlist, txtreferenceno);
oxl = new Excel.Application();
oxl.Visible = true;
oxl.DisplayAlerts = false;
wbook = oxl.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
oxl.ActiveCell.set_Item(2, 4, "Alfa Aesar");
wsheet = (Excel.Worksheet)wbook.ActiveSheet;
wsheet.Name = "Customers";
Excel.Range range = wsheet.get_Range("A6", "H6");
wsheet.get_Range("A6", "H6").Font.Name = "Times new Roman";
wsheet.get_Range("A6", "H6").Font.Size = 12;
wsheet.get_Range("A6", "H6").Interior.Color = ConvertColour(Color.SkyBlue);
oxl.ActiveWindow.DisplayGridlines = false;
int rowCount = 5;
foreach (DataRow dr in dt.Rows)
{
rowCount += 1;
for (int i = 1; i < dt.Columns.Count + 1; i++)
{
// Add the header the first time through
if (rowCount == 7)
{
wsheet.Cells[6, i] = dt.Columns[i - 1].ColumnName;
}
wsheet.Cells[rowCount, i] = dr[i - 1].ToString();
Excel.Range cellRange = (Range)wsheet.Cells[rowCount, i];
//cellRange.Interior.Color = 200;
//cellRange.Interior.Color = ConvertColour(Color.LightBlue);
cellRange.Cells.Borders.LineStyle = BorderStyle.FixedSingle;
}
}
cells = wsheet.get_Range(wsheet.Cells[2, 2],
wsheet.Cells[rowCount, dt.Columns.Count]);
cells.EntireColumn.AutoFit();
wsheet = null;
cells = null;
采纳答案by Amgad Fahmi
In your for statement change this line
在您的 for 语句中更改此行
for (int i = 1; i < dt.Columns.Count + 1; i++)
with this one
有了这个
var filteredColumns = dt.Columns.OfType<DataColumn>()
.Where( x=> x.ColumnName != "referenceno" );
foreach (var column in filteredColumns )
{
//do whatever you want
//if you need the index you can create counter and increase it by 1 each loop
}
don't forget to use linq
不要忘记使用 linq
using System.Linq ;
回答by Steve Danner
Did you try
你试过了吗
dt.Columns.Remove[dt.Columns.Count - 1];
回答by Bj?rn
foreach (DataColumn dc in dt.Columns)
{
bool deleteIt = true;
foreach (StringDictionary sd in sdArray)
{
if (dc.ColumnName.Equals(sd["Name"]))
deleteIt = false;
}
if (deleteIt)
data.Columns.Remove(dc);
}
sdArraycontains all the columns you want in your Excel worksheet. If you prefer you could use a normal string[]instead. I used an array of StringDictionariesbecause I have more information per row, such as width.
sdArray包含您想要的 Excel 工作表中的所有列。如果您愿意，可以改用普通string[]。我使用了一个数组，StringDictionaries因为我每行有更多的信息，比如宽度。
Linq is also very awesome to use for this kinds of tasks, but the example above only supports one row. So I figured we needed some diversity.
Linq 用于此类任务也非常棒，但上面的示例仅支持一行。所以我认为我们需要一些多样性。
回答by zapping
Try Worksheet.get_Range("rangeVal", "rangeVal").EntireColumn.Hidden = true;
尝试 Worksheet.get_Range("rangeVal", "rangeVal").EntireColumn.Hidden = true;
回答by Red Swan
dt.Columns.Remove[ColumnIndex];
dt.Columns.Remove[ColumnIndex];
Or
或者
dt.Columns.Remove["ColumnName"];
dt.Columns.Remove["ColumnName"];
try any ...
尝试任何...
