Delete column in datatable

Question

I have successfully exported a datatable to an excel sheet ... In this 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 displaying the last column (referenceno) ...

will someone tell me a solution to this problem .. Thanks in Advance ..

here is my datatable export code for 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; 

Answer 1

In your for statement, change this line

  for (int i = 1; i < dt.Columns.Count + 1; i++) 

with this

 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

 using System.Linq ; 

Answer 2

You tried

 dt.Columns.Remove[dt.Columns.Count - 1]; 

Answer 3

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

sdArray contains all the columns you want in an Excel worksheet. If you prefer, you can use the usual string[] instead. I used an array of StringDictionaries because I have more information for each row, such as width.

Linq is also very useful for use in such tasks, but the above example only supports one line. So I decided that we need variety.

Answer 4

Try Worksheet.get_Range("rangeVal", "rangeVal").EntireColumn.Hidden = true;

Answer 5

dt.Columns.Remove [ColumnIndex];

or

dt.Columns.Remove ["ColumnName"];

try any ...