Click here to Skip to main content
14,422,431 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi Team,

We need to create complex excel file with dynamic data. We have multiple merge cells, matrix data combinations but do not have any chart or graph requirement in excel output file.  We are thinking of creating excel template and at run time fill dynamic data and create excel file from it. 

Kindly suggest the best approach to achieve above requirement. Also, in future if any changes will be demanded in template then it should be very minimum impact and code changes required in the system.


Thanks & Regards,
Santosh

What I have tried:

We are thinking of creating excel template and at run time fill dynamic data and create excel file from it
Posted
Updated 5 days ago
Comments
Richard MacCutchan 5 days ago
   
You have tagged this question C# and .NET, are you sure that is the best approach? What you are asking for can be accomplished with a template file and Excel macros.
hertzogth 4 days ago
   
See this example of how to create Excel file in C# or see this example of how to edit existing Excel template in C#.
Rate this:
Please Sign up or sign in to vote.

Solution 1

Quote:
Kindly suggest the best approach to achieve above requirement.

The problem is that 'best' solution deeply depend on details of requirement and on your skills, which we don't know.
Quote:
complex excel file

Complex for you does not mean complex to us, details on what is complex are needed.
What make it complex, why is it a problem ?
Quote:
multiple merge cells

Why is it a problem ?
Quote:
matrix data combinations

details are needed.

I work on a daily basis on an excel WorkBook that is vital for the company I work for, if the WorkBook go wrong, it can kill the company.
It got about 30,000 cells of data organized in 200+ matrix, and about 20,000 cells with formulas that use the matrix (VLookUp) to extract useful data.
The main sheet is heavily cross checked so that I am pretty confident that results are OK, because I add things very often and users can duplicate parts of the sheet as they need.
I have also created a set of functions like a custom VLookUp that do linear interpolation for missing values in reference matrix.
I qualify the WorkBook as complex.

[UpDate]
In order to feed data in an existing excel file (template), you will need a library like Interop.
   
v2
Comments
Santosh Shere 13-Jan-20 19:39pm
   
Thanks Patrice for suggestions.
Apologize for less information. I am looking for .Net c# approach which can create excel file at run time with dynamic data in more efficient way. The dynamic values can be calculated in program and that is not an issue. The excel file has 200+ rows and 30+ columns with variety of matrix tables. To accomplish this we are thinking to have template and at run time we feed the values. As we are referring excel cell as column and row number combination if any addition or deletion of column or row may need to change all references in program.So looking for suggestions to create excel file through c# program which has lots off formatting and data.
Patrice T 13-Jan-20 20:55pm
   
Use Improve question to update your question.
So that everyone can pay attention to this information.
CPallini 5 days ago
   
5.
Patrice T 4 days ago
   
Thank you
Rate this:
Please Sign up or sign in to vote.

Solution 2

Quote:
We are thinking of creating excel template and at run time fill dynamic data and create excel file from it


As per my experience this is the best option. Steps to do:
1) create new Excel workbook based on existing template of workbook,
2) fill-in dynamic data,
3) save the workbook.
   
Comments
CPallini 5 days ago
   
5.
Maciej Los 5 days ago
   
Thank you, Carlo.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100