Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is it possible to copy row (with data, merging, style) in Excel using Epplus?

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 templateFirst element of collection has data, style, mergingOther elements got nothing besides data

like image 573
Renat Zamaletdinov Avatar asked Jun 02 '15 15:06

Renat Zamaletdinov


People also ask

How do I merge cells in EPPlus?

If you want to merge cells dynamically, you can also use: worksheet. Cells[FromRow, FromColumn, ToRow, ToColumn].

What is the use of EPPlus?

EPPlus is a very helpful open-source 3rd party DLL for writing data to excel. EPPlus supports multiple properties of spreadsheets like cell ranges, cell styling, charts, pictures, shapes, comments, tables, protection, encryption, pivot tables, data validation, conditional formatting, formula calculation, etc.

Does EPPlus require Excel?

NET Core from version 2.0. EPPlus has no dependencies to any other library such as Microsoft Excel. The library is designed with the developer in mind.

Does EPPlus support XLS?

EPPlus is a . NET library that reads and writes Excel files using the Office Open XML format (. xlsx).


1 Answers

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"]);
like image 130
Renat Zamaletdinov Avatar answered Oct 25 '22 21:10

Renat Zamaletdinov