Showing posts with label Excel. Show all posts
Showing posts with label Excel. Show all posts

Tuesday, April 12, 2022

Excel - Find sum of all digits to single digit

In excel, use below formula to get sum of all digits into single digit  (For ex: 3268 -> Total 1). This is useful to findout vehicle number's total.

Formula: =IF(MOD(A1,9)=0,"9",MOD(A1,9))




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()
        {
            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" });

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


            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;
            }

            byte[] fileText = pck.GetAsByteArray();

            SaveFileDialog dialog = new SaveFileDialog()
            {
                Filter = "Excel Worksheets (*.xlsx)|*.xlsx"
            };

            if (dialog.ShowDialog() == true)
            {
                File.WriteAllBytes(dialog.FileName, fileText);
            }

        }


public class Employee
        {
            public string EmployeeName;
            public int EmployeeAge;
            public string Designation;
        }