none
Delete specific columns in Excel document (on every sheet) RRS feed

  • Question

  • I'm creating two excel documents with different names, but for the second one I want to remove some columns.

    I could of course create the first one with all columns and the second one without the desired columns, but I figure It would be less code to delete the columns afterwards.

    Here is the code:

    private void btnCreateExcelDoc_Click(object sender, EventArgs e) { //Print using Ofice InterOp Excel.Application excel = new Excel.Application(); excel.SheetsInNewWorkbook = dataSet1.Tables.Count + 1; //Number of tables/pages var workbook = (Excel._Workbook)(excel.Workbooks.Add(Missing.Value)); var currentSheet = (Excel._Worksheet)workbook.Sheets[1]; currentSheet.Name = "Info"; currentSheet.Cells[1, 1] = fileName; currentSheet.Cells[1, 1].EntireRow.Font.Bold = true; currentSheet.Cells[1, 1].EntireRow.Font.Name = "Arial"; currentSheet.Cells[1, 1].EntireRow.Font.Size = 16; currentSheet.Cells[1, 1].EntireRow.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.MidnightBlue); int table = 0; foreach (DataTable dataTable in dataSet1.Tables) //DataSet1 has 7-8 Tables { if (workbook.Sheets.Count <= table) { workbook.Sheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing); }

    currentSheet = (Excel._Worksheet)workbook.Sheets[table + 2]; currentSheet.Name = dataTable.TableName; //Foreach column in specifik table : FILL FIRST EXCEL ROW WITH COLUMN NAMES for (var column = 0; column < dataTable.Columns.Count; column++) currentSheet.Cells[1, column + 1] = dataTable.Columns[column].ColumnName; // For every row in specifik table : FILL REST OF ROWS WITH TABLE ROWS for (var row = 0; row < dataSet1.Tables[table].Rows.Count; row++) { // For every cell in specifik row for (var col = 0; col < dataSet1.Tables[table].Rows[row].ItemArray.Count(); col++) { currentSheet.Cells[row + 2, col + 1] = dataSet1.Tables[table].Rows[row].ItemArray[col]; int currentColumn = col + 1; if (currentColumn == 7 || currentColumn == 9 || currentColumn == 11) { currentSheet.Cells[row + 2, col + 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight; } if (currentColumn == 19) { currentSheet.Cells[row + 2, col + 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; } if (currentColumn == 20 || currentColumn == 21) { currentSheet.Cells[row + 2, col + 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; currentSheet.Cells[row + 2, col + 1].Font.Name = "Times New Roman"; } } } table++; } string outfile1 = @"C:\Windows\SysWOW64\config\systemprofile\Desktop\first.xlsx";

    //Here I want to remove column 10 and 12 (J and L) on every sheet before creating the actual file string outfile2 = @"C:\Windows\SysWOW64\config\systemprofile\Desktop\second.xlsx"; workbook.SaveAs(outfile1, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); workbook.SaveAs(outfile2, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); workbook.Close(); excel.Quit(); MessageBox.Show("Excel-dokumentet är skapat!"); //excel.Workbooks.Open(outfile2); }

    I want to remove column 10 and 12 (J and L) on every sheet before creating the actual file, "second.xlsx".

    Is that possible? If so how? Or do I need to create a second function to create the Excel document with only the columns needed for "second.xlsx"?


    Sunday, November 15, 2015 12:01 AM

Answers

  • >>>I want to remove column 10 and 12 (J and L) on every sheet before creating the actual file, "second.xlsx".

    Is that possible? If so how? Or do I need to create a second function to create the Excel document with only the columns needed for "second.xlsx"?<<<

    According to your description, you could refer to below code:

    workbook.SaveAs(outfile1, Type.Missing, Type.Missing, Type.Missing,
                                Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,
                                Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                Type.Missing);
    
    foreach (Excel.Worksheet sheet in workbook.Sheets)
    {
         ((Excel.Range)sheet.Range["J1"]).EntireColumn.Delete(null);
         ((Excel.Range)sheet.Range["L1"]).EntireColumn.Delete(null);
    
    }
    
    workbook.SaveAs(outfile2, Type.Missing, Type.Missing, Type.Missing,
                                Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,
                                Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                Type.Missing);
    


    • Proposed as answer by David_JunFeng Thursday, November 19, 2015 1:31 AM
    • Marked as answer by David_JunFeng Wednesday, November 25, 2015 2:00 PM
    Wednesday, November 18, 2015 1:16 AM

All replies

  • Hi Jonas,

    I think this issue is more related to Excel development. I will help move it to Excel dev forum for effective response.

    Thank you for your understanding.

    Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, November 16, 2015 2:07 AM
  • xlSht.columns(12).Delete
    xlSht.columns(10).Delete 

    WHERE

    xlSht is a reference to the worksheet object


    Warm Regards, Crystal http://www.AccessMVP.com/strive4peace * (: have an awesome day :)

    Monday, November 16, 2015 8:17 AM
  • >>>I want to remove column 10 and 12 (J and L) on every sheet before creating the actual file, "second.xlsx".

    Is that possible? If so how? Or do I need to create a second function to create the Excel document with only the columns needed for "second.xlsx"?<<<

    According to your description, you could refer to below code:

    workbook.SaveAs(outfile1, Type.Missing, Type.Missing, Type.Missing,
                                Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,
                                Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                Type.Missing);
    
    foreach (Excel.Worksheet sheet in workbook.Sheets)
    {
         ((Excel.Range)sheet.Range["J1"]).EntireColumn.Delete(null);
         ((Excel.Range)sheet.Range["L1"]).EntireColumn.Delete(null);
    
    }
    
    workbook.SaveAs(outfile2, Type.Missing, Type.Missing, Type.Missing,
                                Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,
                                Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                Type.Missing);
    


    • Proposed as answer by David_JunFeng Thursday, November 19, 2015 1:31 AM
    • Marked as answer by David_JunFeng Wednesday, November 25, 2015 2:00 PM
    Wednesday, November 18, 2015 1:16 AM