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)
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.
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.
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.
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.
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
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}
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".
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With