19

The problem is that I need to insert data into Excel from the collection several times using a single template for the entire collection.

using (var pckg = new ExcelPackage(new FileInfo(association.TemplatePath)))
{
    var workSheet = pckg.Workbook.Worksheets[1];
    var dataTable = WorksheetToDataTable(workSheet);
    /*Some stuff*/
    FindAndReplaceValue(workSheet, dictionary, row);
}

private DataTable WorksheetToDataTable(ExcelWorksheet oSheet)
{
    int totalRows = oSheet.Dimension.End.Row;
    int totalCols = oSheet.Dimension.End.Column;
    DataTable dt = new DataTable(oSheet.Name);
    DataRow dr = null;
    for (int i = 1; i <= totalRows; i++)
    {
        if (i > 1) dr = dt.Rows.Add();
        for (int j = 1; j <= totalCols; j++)
        {
            if (i == 1)
                dt.Columns.Add((oSheet.Cells[i, j].Value ?? "").ToString());
            else
                dr[j - 1] = (oSheet.Cells[i, j].Value ?? "").ToString();
        }
    }
    return dt;
}

First picture - My template. Second - First element of collection (with data, style, merging). Third - Other elements has only data

This is my template First element of collection has data, style, merging Other elements got nothing besides data

cramopy
  • 3,459
  • 6
  • 28
  • 42
Renat Zamaletdinov
  • 1,210
  • 1
  • 21
  • 36

1 Answers1

30

I just made copies of rows

    for (int i = 0; i < invoiceList.Count; i++)
    {
        workSheet.Cells[1, 1, totalRows, totalCols].Copy(workSheet.Cells[i * totalRows + 1, 1]);
    }

If you want to copy range just use :

workSheet.Cells["A1:I1"].Copy(workSheet.Cells["A4:I4"]);
Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
Renat Zamaletdinov
  • 1,210
  • 1
  • 21
  • 36
  • How can i also copy the cell style, backgroundcolor/border etc of the whole range? Thanks – kassi Aug 06 '15 at 08:43
  • 4
    @kassi, You can copy cell with style etc. using solution listed above. If you want to copy range just use `workSheet.Cells["A1:I1"].Copy(workSheet.Cells["A4:I4"]);` Result: http://imgur.com/Ow4UUv5 – Renat Zamaletdinov Aug 06 '15 at 09:55