This post will explain the advantages of Named Ranges in Excel and how do we use it with EPPlus library.
This post would explain how to use the named ranges for reading/importing an excel programmatically(dynamically).
A) What are Named Ranges?
In excel, it is possible to specify an address for a cell or a range of cells. The address of this/these cells is referred as named ranges.
If you check the above image, cell A1 has a text value of "Name" and named range of it is"NameField".
Named Range in other words are user-defined addresses of cells.
Now, since named ranges are addresses, basic rules of variable names apply. Like, it cannot begin with a number, cannot have a '.' character in the name, etc. Also, within a sheet, all the named ranges must have unique names.
B) Writing named ranges using EPPlus:
Many engineering applications (software) provide feature of exporting and importing the excel file. The typical scenario will be to generate some kind of a template information in the form of an excel, exporting to the client from server.
The exported excel file can then be populated from the users and once that is done, the excel file can then be imported into the system by sending it to the server over a web.
In such scenarios, importing an excel will be extremely easy only if we were to write the user defined addresses of the cells along with its text, while exporting. So that while reading we can read only our user-defined addresses.
Assumption:
Users are aware of How to use EPPlus library and the popular APIs it exposes.
ExcelRange range = ws.Cells["A1"]; // ws is the worksheet name
ws.Cells["A1"].Value = "Name";
string sNamedRange = "NameField";
ws.Names.Add(sNamedRange, range);
That's it. A1 cell in your generated excel file must have "NameField" as its address.
Hope this post helps a lot of other developers. I had a tough time reading the excel file.
This post would explain how to use the named ranges for reading/importing an excel programmatically(dynamically).
A) What are Named Ranges?
In excel, it is possible to specify an address for a cell or a range of cells. The address of this/these cells is referred as named ranges.
If you check the above image, cell A1 has a text value of "Name" and named range of it is"NameField".
Named Range in other words are user-defined addresses of cells.
Now, since named ranges are addresses, basic rules of variable names apply. Like, it cannot begin with a number, cannot have a '.' character in the name, etc. Also, within a sheet, all the named ranges must have unique names.
B) Writing named ranges using EPPlus:
Many engineering applications (software) provide feature of exporting and importing the excel file. The typical scenario will be to generate some kind of a template information in the form of an excel, exporting to the client from server.
The exported excel file can then be populated from the users and once that is done, the excel file can then be imported into the system by sending it to the server over a web.
In such scenarios, importing an excel will be extremely easy only if we were to write the user defined addresses of the cells along with its text, while exporting. So that while reading we can read only our user-defined addresses.
Assumption:
Users are aware of How to use EPPlus library and the popular APIs it exposes.
ExcelRange range = ws.Cells["A1"]; // ws is the worksheet name
ws.Cells["A1"].Value = "Name";
string sNamedRange = "NameField";
ws.Names.Add(sNamedRange, range);
That's it. A1 cell in your generated excel file must have "NameField" as its address.
Hope this post helps a lot of other developers. I had a tough time reading the excel file.
