13,473,871 members
home
articles
Sign in
quick answers
discussions
features
community
help
Search for articles, questions, tips
Articles » Enterprise Systems » Office Development » Microsoft Excel
Set Excel cells value using cell name in C#
Article Browse Code
Christian Del Bianco, 23 Jul 2013
Stats
5.00 (5 votes)
Revisions (4) Alternatives
Rate this:
Automate the process of set value to Excel cells, assigning a cell name that matches the query result column name, leaving the end user the possibility to change the layout template.
Comments (2) Add your own alternative version
Download SetExcelCell-noexe.zip - 30.2 KB
Tagged as
Download SetExcelCell.zip - 39.4 KB C#
Automate the process of set value to the Excel cells, assigning a cell name that match the query result column name, leaving the end user the possibility to change the layout template.
MS-Excel Stats
Introduction
72.6K views 2.7K downloads
For work activity, I need to create a report like in the figure below:
21 bookmarked
Posted 23 Jul 2013 CPOL
The template can be modified from the user, changing the cell's position, removing cells and adding new cells that will get the value from the query result.
Desiderata I want to have the possibility to define a cell name equal to the column name returned from a query result, so I will be able to get the cell matching its name with the column name in query result. In this way I can loop over every column in the query result and get the Excel cells, independently by its position: Hide Copy Code
foreach (DataRow dataRow in dataTable.Rows) { foreach (DataColumn dataColumn in dataTable.Columns) { // It NOT works! Excel.Range cell = ws.Cells[dataColumn.ColumnName]; cell.Value = dataRow[dataColumn.ColumnName]; } } The above code, unfortunately, does not work.
Solution To get the desired result, we must use the Evaluate() method, defined in the WorkSheet Excel object. This method takes a string and start looking for a cell (or a range of cells) that match that string. When evaluate positively, it returns an instance of an Excel.Range object, that is the requested cell: Hide Copy Code
foreach (DataRow dataRow in dataTable.Rows) { foreach (DataColumn dataColumn in dataTable.Columns) { Excel.Range cell = ws.Evaluate(dataColumn.ColumnName); if (cell != null) cell.Value = dataRow[dataColumn.ColumnName]; } }
Define cell name To correctly run the above code, we must specify the cell names. To do that, right click on the cell and choose the Define Name item menu, like show below:
This will open a popup window where you can define the cell name:
The Scope option, give you the possibility to choose the name visibility: this name must be unique in the chosen scope: unique per entire Excel document (Workbook ) or unique per sheet (worksheet). Once you have assigned the cell name, you can see it in the left up corner:
After you have set the cell name, it can still be get using the classic mode that is use the coordinates, like show below: Hide Copy Code
Excel.Worksheet workSheet = workbookReport.Worksheets[1]; Excel.Range cell = workSheet.Cells[1, 2];
Delete or change the cell name To remove a cell name or simply change it, you must use the Name Manager menu item under the Formulas tab:
This will open the following page that reports all the cell’s names allowing you to delete or change their names:
Choosing a name in the list, you can modify it simply clicking on the Edit or Delete button.
Create tabular report Starting from a query result, loaded within a DataTable :
We want to create an Excel tabular report like:
As you can see, we need a sort of Pivot table, because we have the records organized row by row. We can easily solve it, applying a cell name that is related to the month: so, for every cell we can assign a name that is identic to the DataTable column’s name plus the month number:
Now, we can simply use the following code to get the automatic set for every cell matching the datatable ’s columns name: Hide Copy Code
foreach (DataRow dataRow in dataTable.Rows) { DateTime period = Convert.ToDateTime(dataRow["PERIOD_START_DATE"]); foreach (DataColumn dataColumn in dataTable.Columns) { string cellName = dataColumn.ColumnName + "_" + period.Month.ToString("d2")); Excel.Range cell = workSheet.Evaluate(cellName) as Excel.Range; if (cell != null) cell.Value = value; } }
Attached project The attached project in this article, include an Excel file used like template. In this template I have defined the cell names, according to the DataTable’s column name: using this template I create a new Excel report on which I set the cell's values.
Requirements The project must reference Microsoft.Office.Interop.Excel.
License This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)
Share TWITTER
FACEBOOK
About the Author Christian Del Bianco Software Developer Italy Email:
[email protected] Skype: christian.delbianco
You may also be interested in... Pro
Building Reactive Apps
Pro
Public, Private, and Hybrid Cloud: What's the difference?
Cell Merging In GridView
Color Topics for Programmers
DataGridView Image Button Cell
ASP.NET Core 2.0 User Role Base Dynamic Menu Management Using Dependency Injection
Comments and Discussions
You must Sign In to use this message board.
Search Comments
Spacing Relaxed
Layout Normal
Per page 25
Update
First Prev Next
Direct access to named range
Phil J Pearson
23-Jul-13 23:44
My vote of 5
fredatcodeproject
23-Jul-13 12:54
Last Visit: 31-Dec-99 18:00 Last Update: 2-Apr-18 23:47 General
News
Suggestion
Question
1
Refresh Bug
Answer
Joke
Praise
Rant
Admin
Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages. Permalink | Advertise | Privacy | Terms of Use | Mobile Web04-2016 | 2.8.180326.1 | Last Updated 23 Jul 2013
Layout: fixed | fluid
Article Copyright 2013 by Christian Del Bianco Everything else Copyright © CodeProject, 1999-2018