Create complex excel file with dynamic data

Question

1.00/5 (1 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 13-Jan-20 11:45am

Santosh Shere

Updated 14-Jan-20 20:49pm

Add a Solution

Comments

Richard MacCutchan 14-Jan-20 4:57am

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.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Patrice T · Answer 1 · 2020-01-13T12:29:00

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.

Maciej Los · Answer 2 · 2020-01-14T20:50:00

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.

Posted 14-Jan-20 20:50pm

Maciej Los

Comments

CPallini 15-Jan-20 3:16am

5.

Maciej Los 15-Jan-20 4:25am

Thank you, Carlo.