CODEWITHSUNDEEP
c#excelepplus
bubbajake00
bubbajake00

Reputation: 215

Create Columns of Data Instead of Rows

I would like to insert my data into Excel in columns rather than in rows, like so:

 ---------------
| Name1 | Name2 |
| Qty1  | Qty2  |
| Qtys1 | Qtys2 |
| Per1  | Per2  |
| Old1  | Old2  |
 ---------------

This is what I currently have: 

        using (ExcelPackage excel = new ExcelPackage())
        {
            ExcelWorksheet ws;

            ws = excel.Workbook.Worksheets.Add("Chart");

            var table = ws.Cells["B4"].LoadFromCollection(
                dmr,
                false,
                OfficeOpenXml.Table.TableStyles.Light15,
                BindingFlags.Public,
                new MemberInfo[]
                {
                    typeof(DMR).GetProperty("Name"),
                    typeof(DMR).GetProperty("Qty"),
                    typeof(DMR).GetProperty("Qtys"),
                    typeof(DMR).GetProperty("Per"),
                    typeof(DMR).GetProperty("Old")
                });

This currently displays my data like:

 ------------------------------------
| Name1 | Qty1 | Qtys1 | Per1 | Old1 |
| Name2 | Qty2 | Qtys2 | Per2 | Old2 |
 ------------------------------------

Is there a setting where you tell it to do rows rather than columns, or is additional code needed?

Upvotes: 2

Views: 819

Answers (1)

Chawin
Chawin

Reputation: 1466

Unfortunately there's no easy way to pivot data in that way using EPPlus, however as long as the data size isn't too great the following will work:

using (ExcelPackage excel = new ExcelPackage(file))
{
    ExcelWorksheet ws;
    DataTable dt = new DataTable();

    // Get the worksheet
    ws = excel.Workbook.Worksheets["Chart"];

    // Create as many columns as there are rows
    for (int i = 0; i < ws.Dimension.End.Row; i++) {
        dt.Columns.Add(i.ToString());
    }

    // Go through each column and create a new row of data
    for (int i = 1; i <= ws.Dimension.End.Column; i++) {
        var row = dt.NewRow();
        for (int j = 1; j <= ws.Dimension.End.Row; j++) {
            row[j-1] = ws.Cells[j, i].Value;
        }
        dt.Rows.Add(row);
    }
}

Note that the column headers will just be 1 | 2 | 3 | etc, this can be changed in the first for loop.

Upvotes: 1

Related Questions