Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to get range in EPPlus

Tags:

c#

excel

epplus

Does anyone know how to execute the following in EPPlus.

The following is the way when using VSTO. I'm trying to get some specific ranges from a worksheet.

sheet.get_Range("7:9,12:12,14:14", Type.Missing)
like image 877
sparta93 Avatar asked Jul 15 '15 14:07

sparta93


People also ask

Is there a way to hack epplus data in advance?

EPPlus won't calculate them in advance from the raw data, so you won't have the opportunity to hack them. This will only happen when you open the file in Excel and save. You can see that vividly if you pretty-print the Excel file once before opening the file and a second time after opening the file in Excel and saving it.

How do I merge cells in a table in EPs epplus?

EPPlus makes it easy to merge cells. Once you selected the appropriate cells ExcelRange cells = ws.Cells [...], you can enable the Merge property cells.Merge = true. To enable auto filter, we need to select the whole table from the top-left cell to the bottom-right cell. Then enable the AutoFilter property on the selected cells.

Do we use any advanced features from epplus?

We are not going to use any advanced features from EPPlus. This Excel is going to serve as the basis for everything that is going to come next. Each tab sheet is a different territory group (Europe, North America, Pacific) and for every territory group we create a table of order revenues.

How does the auto sort work in epplus?

EPPlus will update the sort state after each sort, so your spreadsheet program can visualize the sort parameters. See this example in our wiki EPPlus 5.7 adds support for auto sort via pivot areas.


3 Answers

You can use this

ExcelPackage pck = new ExcelPackage()
var ws = pck.Workbook.Worksheets.Add("Sheet1");
ExcelRange cells = ws.Cells[1, 1, 1, 10];//get 10 cells in row 1 
like image 124
AntonE Avatar answered Oct 11 '22 13:10

AntonE


I am not fully familiar with `get_Range' but based on the documention it works just like the cells object of EPPlus. So this:

[TestMethod]
public void Multi_Range_Test()
{

    //Throw in some data
    var datatable = new DataTable("tblData");
    datatable.Columns.Add(new DataColumn("Col1", typeof(int)));
    datatable.Columns.Add(new DataColumn("Col2", typeof(int)));
    datatable.Columns.Add(new DataColumn("Col3", typeof(int)));

    for (var i = 0; i < 20; i++)
    {
        var row = datatable.NewRow();
        row[0] = i;
        row[1] = i * 10;
        row[2] = i * 100;
        datatable.Rows.Add(row);
    }

    var existingFile2 = new FileInfo(@"c:\temp\temp.xlsx");
    if (existingFile2.Exists)
        existingFile2.Delete();

    using (var package = new ExcelPackage(existingFile2))
    {
        //Add the data
        var sheet = package.Workbook.Worksheets.Add("Sheet1");
        sheet.Cells.LoadFromDataTable(datatable, true);

        var range = sheet.Cells["7:9,12:12,14:14"];
        foreach (var rangeBase in range)
        {
            Console.WriteLine("{{{0} : {1}}}", rangeBase.Address, rangeBase.Value);
        }

        //Save the file
        package.Save();
    }
}

results in this in the output window:

{A7 : 5}
{B7 : 50}
{C7 : 500}
{A8 : 6}
{B8 : 60}
{C8 : 600}
{A9 : 7}
{B9 : 70}
{C9 : 700}
{A12 : 10}
{B12 : 100}
{C12 : 1000}
{A14 : 12}
{B14 : 120}
{C14 : 1200}
like image 4
Ernie S Avatar answered Oct 11 '22 12:10

Ernie S


AntonE's answer is correct, but I thought I'd point out what each index in the subscript operator signifies:

ExcelWorkSheet.Cells[FromRow, FromCol, ToRow, ToCol]

Hence, ws.Cells[1,1,1,10] means: "Get all cells starting from the first row and first column to the 10th column, but first row".

like image 4
Wassim Katbey Avatar answered Oct 11 '22 12:10

Wassim Katbey