Balajiprasad's useful codes: Excel

Tuesday, April 12, 2022

Tuesday, March 7, 2017

JavaScript - Simple Export to excel

To export excel in JavaScript without any server side techiques, we can use following steps

Used blob file saver for download https://github.com/eligrey/FileSaver.js

Step 1: Create Html Content with required styles as below

var tableHtml = '<html xmlns:x="urn:schemas-microsoft-com:office:excel">';

tableHtml += '<head><xml><x:ExcelWorkbook><x:ExcelWorksheets>';

tableHtml += '<x:ExcelWorksheet><x:Name>PlanFundLineup</x:Name>';

tableHtml += '<x:WorksheetOptions><x:Panes></x:Panes></x:WorksheetOptions></x:ExcelWorksheet>';

tableHtml += '</x:ExcelWorksheets></x:ExcelWorkbook></xml></head><body>';

tableHtml += "<div><table border='1'>" +

tableHtml += "<thead>" +

tableHtml += "<tr><th>" +

tableHtml += "Header Text" +

tableHtml += "</th></tr>" +

tableHtml += "</thead>" +

"<tbody>" +

tableHtml += "<tr><td>" +

tableHtml += "Content Text" +

tableHtml += "</td></tr>" +

"</tbody>" +

"</table></div>";

tableHtml += '</body></html>';

Step 2: Download this html content using blob

var blob = new Blob([tableHtml], { type: "application/vnd.ms-excel;charset=utf-8" })

window.saveAs(blob, "excelname.xls");

Thursday, July 11, 2013

WPF - Export to excel using EPPlus

Following code helpful to create a excel file from WPF

Prerequesties:-
EPPlus DLL (Ref: http://epplus.codeplex.com/releases/view/89923)

Step 1: Create a WPF project

Step 2: Add reference of EPPlus DLL

Step 3: Following is the sample class to export

public class Employee

{

public string EmployeeName;

public int EmployeeAge;

public string Designation;

}

Step 4: Create Data with values

List<Employee> lstEmployee = new List<Employee>();

lstEmployee.Add(new Employee() { EmployeeName = "Balaji", EmployeeAge = 16, Designation = "DE" });

lstEmployee.Add(new Employee() { EmployeeName = "prasad", EmployeeAge = 16, Designation = "TA" });

Step 5: Create Header Data for excel

List<string> lstHeader = new List<string>() { "Employee Name", "Employee Age", "Designation" };

Step 6: Create Excel Package and add Header and Column details

ExcelPackage pck = new ExcelPackage();

pck.Workbook.Properties.Author = "Balajiprasad";

pck.Workbook.Properties.Title = "EPPlus in WPF";

pck.Workbook.Properties.Company = "For Aditi Technologies";

var ws = pck.Workbook.Worksheets.Add("Employee Details");

//Header Section

for (int i = 0; i < lstHeader.Count; i++)

{

ws.Cells[1, i + 1].Value = lstHeader[i];

ws.Cells[1, i + 1].Style.Font.Bold = true;

}

//Column Value Section

for (int i = 0; i < lstEmployee.Count; i++)

{

ws.Cells[i + 2, 1].Value = lstEmployee[i].EmployeeName;

ws.Cells[i + 2, 2].Value = lstEmployee[i].EmployeeAge;

ws.Cells[i + 2, 3].Value = lstEmployee[i].Designation;

}

Step 7: Save as the byte array as excel

byte[] fileText = pck.GetAsByteArray();

SaveFileDialog dialog = new SaveFileDialog()

{

Filter = "Excel Worksheets (*.xlsx)|*.xlsx"

};

if (dialog.ShowDialog() == true)

{

File.WriteAllBytes(dialog.FileName, fileText);

}

Following is the entire code

private void GenerateExcel()

{