WorkSheet.DeleteColumn does not delete column, just clears data

Question

I load my sheet with data from a List<T> using LoadFromCollection. I then delete some extra columns and then add a Table to the sheet. Finally I save the file.

I expect 2 columns in my table, but there are 4, of which 2 are empty.

I suspect that it may have something to do with the range that I am using, but I can't seem to find the place to adjust the range to not have the 2 columns that was deleted.

Code:

using OfficeOpenXml;
using System.Collections.Generic;
using System.IO;

namespace DelColCode
{
    class Program
    {
        static void Main(string[] args)
        {
            //get some test data
            var cars = Car.GenerateList();

            var package = new ExcelPackage();

            //Create the worksheet 
            var sheet = package.Workbook.Worksheets.Add("Car List");

            //Read the data into a range
            var range = sheet.Cells["A1"].LoadFromCollection(cars, true);

            //delete the first and last columns
            sheet.DeleteColumn(1);
            sheet.DeleteColumn(3);

            //Make the range a table
            sheet.Tables.Add(range, $"data");

            //save and dispose
            package.File = new FileInfo("carlist.xlsx");
            package.Save();
            package.Dispose();
        }
    }

    public class Car
    {
        public int Id { get; set; }
        public string Make { get; set; }
        public string Model { get; set; }
        public int ReleaseYear { get; set; }
        

        public Car(int id, string make, string model, int releaseYear)
        {
            Id = Id;
            Make = make;
            Model = model;
            ReleaseYear = releaseYear;
        }

        internal static List<Car> GenerateList()
        {
            return new List<Car>
            {
                //random data
                new Car(1,"Toyota", "Carolla", 1950),
                new Car(2,"Toyota", "Yaris", 2000),
                new Car(3,"Toyota", "Hilux", 1990),
                new Car(4,"Nissan", "Juke", 2010),
                new Car(5,"Nissan", "Trail Blazer", 1995),
                new Car(6,"Nissan", "Micra", 2018),
                new Car(7,"BMW", "M3", 1980),
                new Car(8,"BMW", "X5", 2008),
                new Car(9,"BMW", "M6", 2003),
                new Car(10,"Merc", "S Class", 2001)
            };
        }
    }
}

Expected Result:

Result:

score 2 · Accepted Answer · answered Jun 07 '18 at 08:55

2

Try deleting columns after range is loaded in table.

var range = sheet.Cells["A1"].LoadFromCollection(cars, true);
sheet.Tables.Add(range, $"data");
sheet.DeleteColumn(1);
sheet.DeleteColumn(3);

answered Jun 07 '18 at 08:55

Sanjay

515
3
8

That's it! Thanks! – Atron Seige Jun 07 '18 at 09:46
Another extra bit of info: Adding the range first works OK if you delete columns, but when you move/add columns things go haywire again. I ended up just using the LoadFromCollection command, then adding the table later on with the following code: sheet.Tables.Add(sheet.Cells[1, 1, sheet.Dimension.Rows, sheet.Dimension.Columns], $"data{sheetName}"); – Atron Seige Jun 12 '18 at 08:13

score -1 · Answer 2 · edited Jun 06 '18 at 13:54

-1

Use the following:

Excel.Range range = (Excel.Range)sheet.get_Range("C1", Missing.Value);
range.EntireColumn.Delete(Missing.Value);
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);

http://bytes.com/topic/c-sharp/answers/258110-how-do-you-delete-excel-column http://quicktestprofessional.wordpress.com/2008/02/14/delete-columns-from-xl-sheet/

edited Jun 06 '18 at 13:54

Brian

2,078
1
15
28

answered Jun 06 '18 at 13:30

Neel Music

9
5

I think you are referring to the Office Automation API, while the OP specified EPPlus, which is a wrapper around the OOXML SDK, which doesn't use COM at all. – Tsahi Asher Jun 06 '18 at 14:01
get_Range is not available. – Atron Seige Jun 07 '18 at 08:35

WorkSheet.DeleteColumn does not delete column, just clears data

2 Answers2