Export excel with dynamic formatting from ASP.NET

Question

I am working on an ASP.NET 4.0 website. I want to export data from database to excel (2013 Xlsx file) from the website.

I do not want to use Microsoft Interop excel dlls since it requires excel on the webserver.

I want to export the data to excel (2013 Xlsx file) and format the excel cells dynamically. (Font, Color coding, Merging cells and calculation)

How to format the excel cells dynamically based on the values in the cells without Interop dlls? Any suggestions or links to excel formatting would be helpful.

Thanks Ashok

Lon Prosser · Accepted Answer · 2015-04-07 16:01:43Z

1

EPPLUS does what you need and it is free and relatively painless to configure. There are samples included that help you get started.

answered Apr 7, 2015 at 16:01

Lon Prosser

2322 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

alex.pulver · Accepted Answer · 2015-04-08 08:53:16Z

You can also use EasyXLS.

Check this sample for starting. It will guide you step by step how to export to xlsx, format and merge cells:

http://www.easyxls.com/manual/FAQ/export-to-excel-in-dot-net.html

In order to calculate formulas use a similar code:

//Create the Excel file and one sheet
ExcelDocument workbook = new ExcelDocument(); 
ExcelWorksheet xlsWorksheet = new ExcelWorksheet("Sheet1"); 
workbook.easy_addWorksheet(xlsWorksheet); 

// Adding some data to sheet 
ExcelTable xlsTable = xlsWorksheet.easy_getExcelTable(); 
xlsTable.easy_getCell(0,0).setValue("1"); 
xlsTable.easy_getCell(1,0).setValue("2"); 
xlsTable.easy_getCell(2,0).setValue("3"); 

//Set a formula on a cell
xlsTable.easy_getCell(3,0).setValue("=SUM(A1:A3)");

//Call the method used to compute the formulas 
String sError = xlsWorksheet.easy_computeFormulas(xls, true);

//Display the formula
Console.WriteLine("The result of the formula entered at position A4 is: " + 
           xlsTable.easy_getCell(3,0).getFormulaResultValue());

Collectives™ on Stack Overflow

Export excel with dynamic formatting from ASP.NET

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related