Create and Export Excel sheet using NPOI and C#

LINQ queries equivalent to SQL
Sorting Grideview using Generic List

Here we create excel sheet using NPOI dll in excel we download expenditure for all months and also create formulae for Total expenditure of all months and also calculate total expense for each month for all expenditure which will be displayed at the bottom. If we changes any value of expenditure then total will also change for all months total and all expenditure total for a month.

First download NPOI dll from http://npoi.codeplex.com/releases and add reference in project.

Default.apx

<%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true"

    CodeFile="Default.aspx.cs" Inherits="_Default" %>

<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">

</asp:Content>

<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">

    <h2>

       Create and Export Excel Sheet using NPOI and C#

    </h2>

    <p>

        <asp:Button ID="btnExport" runat="server" Text="Export"

            onclick="btnExport_Click" />

    </p>

</asp:Content>

Default.aspx.cs

using System;

using System.Collections.Generic;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data;

public partial class _Default : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

    }

    protected void btnExport_Click(object sender, EventArgs e)

    {

        DataTable dtExpenditure = new DataTable();

        Expenditure objExpenditure=new Expenditure();

        dtExpenditure = objExpenditure.GetExpenditure(AddExpenses());

        ExportUtility export = new ExportUtility();

        export.ExportToExcel(dtExpenditure);

        string saveAsFileName = string.Format("Expenditure-{0:d}.xls", DateTime.Now);

        Response.ContentType = "application/vnd.ms-excel";

        Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));

        Response.Clear();

        Response.BinaryWrite(export.GetBytes());

        Response.End();

    }

    private List<Expenditure> AddExpenses()

    {

        List<Expenditure> lstExpenditure = new List<Expenditure>();

        Expenditure objExpenditure1 = new Expenditure();

        //Adding expenditure for all months

        objExpenditure1.ExpenditureName = "Salary";

        objExpenditure1.Jan = 34000;

        objExpenditure1.Feb = 34500;

        objExpenditure1.Mar = 34000;

        objExpenditure1.Apr = 34000;

        objExpenditure1.May = 36000;

        objExpenditure1.Jun = 34000;

        objExpenditure1.Jul = 34000;

        objExpenditure1.Aug = 36000;

        objExpenditure1.Sep = 34000;

        objExpenditure1.Oct = 35000;

        objExpenditure1.Nov = 34500;

        objExpenditure1.Dec = 34700;

        Expenditure objExpenditure2 = new Expenditure();

        //Adding expenditure for all months

        objExpenditure2.ExpenditureName = "Transport";

        objExpenditure2.Jan = 4600;

        objExpenditure2.Feb = 6000;

        objExpenditure2.Mar = 4590;

        objExpenditure2.Apr = 3000;

        objExpenditure2.May = 6000;

        objExpenditure2.Jun = 6000;

        objExpenditure2.Jul = 34000;

        objExpenditure2.Aug = 6000;

        objExpenditure2.Sep = 6000;

        objExpenditure2.Oct = 3000;

        objExpenditure2.Nov = 6000;

        objExpenditure2.Dec = 3000;

        Expenditure objExpenditure3 = new Expenditure();

        //Adding expenditure for all months

        objExpenditure3.ExpenditureName = "Medical";

        objExpenditure3.Jan = 1500;

        objExpenditure3.Feb = 4500;

        objExpenditure3.Mar = 1200;

        objExpenditure3.Apr = 3600;

        objExpenditure3.May = 6000;

        objExpenditure3.Jun = 6000;

        objExpenditure3.Jul = 4900;

        objExpenditure3.Aug = 6000;

        objExpenditure3.Sep = 4500;

        objExpenditure3.Oct = 3000;

        objExpenditure3.Nov = 6000;

        objExpenditure3.Dec = 5000;

        lstExpenditure.Add(objExpenditure1);

        lstExpenditure.Add(objExpenditure2);

        lstExpenditure.Add(objExpenditure3);

        return lstExpenditure;

    }

}

Create two files in App_Code Folder

Expenditure.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Data;

/// <summary>

/// Summary description for Expenditure

/// </summary>

public class Expenditure

{

    public string ExpenditureName { get; set; }

    public double Jan { get; set; }

    public double Feb { get; set; }

    public double Mar { get; set; }

    public double Apr { get; set; }

    public double May { get; set; }

    public double Jun { get; set; }

    public double Jul { get; set; }

    public double Aug { get; set; }

    public double Sep { get; set; }

    public double Oct { get; set; }

    public double Nov { get; set; }

    public double Dec { get; set; }

       public Expenditure()

       {

              //

              // TODO: Add constructor logic here

              //

       }

    /// <summary>

    /// This function will convert Expenditure list into datatable

    /// </summary>

    /// <param name="lstExpenditure"></param>

    /// <returns></returns>

    public DataTable GetExpenditure(List<Expenditure> lstExpenditure)

    {

        DataTable dtExpenditure = new DataTable();

        dtExpenditure.Columns.Add(new DataColumn("ExpenditureName", typeof(string)));

        dtExpenditure.Columns.Add(new DataColumn("Jan", typeof(double)));

        dtExpenditure.Columns.Add(new DataColumn("Feb", typeof(double)));

        dtExpenditure.Columns.Add(new DataColumn("Mar", typeof(double)));

        dtExpenditure.Columns.Add(new DataColumn("Apr", typeof(double)));

        dtExpenditure.Columns.Add(new DataColumn("May", typeof(double)));

        dtExpenditure.Columns.Add(new DataColumn("Jun", typeof(double)));

        dtExpenditure.Columns.Add(new DataColumn("Jul", typeof(double)));

        dtExpenditure.Columns.Add(new DataColumn("Aug", typeof(double)));

        dtExpenditure.Columns.Add(new DataColumn("Sep", typeof(double)));

        dtExpenditure.Columns.Add(new DataColumn("Oct", typeof(double)));

        dtExpenditure.Columns.Add(new DataColumn("Nov", typeof(double)));

        dtExpenditure.Columns.Add(new DataColumn("Dec", typeof(double)));

        foreach (Expenditure expenditure in lstExpenditure)

        {

            DataRow dr = dtExpenditure.NewRow();

            dr["ExpenditureName"] = expenditure.ExpenditureName;

            dr["Jan"] = expenditure.Jan;

            dr["Feb"] = expenditure.Feb;

            dr["Mar"] = expenditure.Mar;

            dr["Apr"] = expenditure.Apr;

            dr["May"] = expenditure.May;

            dr["Jun"] = expenditure.Jun;

            dr["Jul"] = expenditure.Jul;

            dr["Aug"] = expenditure.Aug;

            dr["Sep"] = expenditure.Sep;

            dr["Oct"] = expenditure.Oct;

            dr["Nov"] = expenditure.Nov;

            dr["Dec"] = expenditure.Dec;

            dtExpenditure.Rows.Add(dr);

        }

        return dtExpenditure;

    }

}

ExportUtility.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using NPOI.HSSF.UserModel;

using NPOI.SS.UserModel;

using NPOI.SS.Util;

using NPOI.HSSF.Util;

using NPOI.POIFS.FileSystem;

using NPOI.HPSF;

using System.IO;

using System.Text;

using System.Data;

/// <summary>

/// Summary description for ExportUtility

/// </summary>

public class ExportUtility

{

       public ExportUtility()

       {

              //

              // TODO: Add constructor logic here

              //

       }

    #region Variable

    protected HSSFWorkbook Workbook { get; set; }

    decimal totalJan = 0;

    decimal totalFeb = 0;

    decimal totalMar = 0;

    decimal totalApr = 0;

    decimal totalMay = 0;

    decimal totalJun = 0;

    decimal totalJul = 0;

    decimal totalAug = 0;

    decimal totalSep = 0;

    decimal totalOct = 0;

    decimal totalNov = 0;

    decimal totalDec = 0;

    decimal totalAll = 0;

    string janTotal = string.Empty;

    string febTotal = string.Empty;

    string marTotal = string.Empty;

    string aprTotal = string.Empty;

    string mayTotal = string.Empty;

    string junTotal = string.Empty;

    string julTotal = string.Empty;

    string augTotal = string.Empty;

    string sepTotal = string.Empty;

    string octTotal = string.Empty;

    string novTotal = string.Empty;

    string decTotal = string.Empty;

    string allTotal = string.Empty;

    StringBuilder allMonthsTotal = null;

    #endregion

    #region ExportExcel

    /// <summary>

    /// This function escape special charaters from sheetname

    /// </summary>

    /// <param name="sheetName"></param>

    /// <returns></returns>

    private string EscapeSheetName(string sheetName)

    {

        var escapedSheetName = sheetName

                                    .Replace("/", "-")

                                    .Replace("\\", " ")

                                    .Replace("?", string.Empty)

                                    .Replace("*", string.Empty)

                                    .Replace("[", string.Empty)

                                    .Replace("]", string.Empty)

                                    .Replace(":", string.Empty);

        return escapedSheetName;

    }

    /// <summary>

    /// This function create header for excel sheet

    /// </summary>

    /// <param name="exportData"></param>

    /// <param name="sheetName"></param>

    /// <param name="headerRowStyle"></param>

    /// <returns></returns>

    private Sheet CreateExportDataTableSheetAndHeaderRow(string sheetName)

    {

        var headerLabelCellStyle = Workbook.CreateCellStyle();

        headerLabelCellStyle.BorderBottom = CellBorderType.THIN;

        headerLabelCellStyle.WrapText = true;

        var headerLabelFont = Workbook.CreateFont();

        headerLabelFont.Boldweight = (short)FontBoldWeight.BOLD;

        headerLabelCellStyle.SetFont(headerLabelFont);

        var sheet = Workbook.CreateSheet(EscapeSheetName(sheetName));

        sheet.SetColumnWidth(0, 10000);

        sheet.SetColumnWidth(1, 5000);

       

        // Create the header row

        // Create the header row

        var rowFirst = sheet.CreateRow(0);

        var cellFirst = rowFirst.CreateCell(0);

        cellFirst.SetCellValue("Expenditure:");

        cellFirst.CellStyle = headerLabelCellStyle;

        var cellSecond = rowFirst.CreateCell(1);

        cellSecond.SetCellValue("Year 2012-13");

        cellSecond.CellStyle = headerLabelCellStyle;

        var detailSubtotalCellStyle = Workbook.CreateCellStyle();

        var row2 = sheet.CreateRow(1);

        var row3 = sheet.CreateRow(2);

        var cell31 = row3.CreateCell(0);

        cell31.SetCellValue("Expenditure");

        cell31.CellStyle = headerLabelCellStyle;

        var cell35 = row3.CreateCell(1);

        cell35.SetCellValue("JAN");

        cell35.CellStyle = headerLabelCellStyle;

        var cell36 = row3.CreateCell(2);

        cell36.SetCellValue("FEB");

        cell36.CellStyle = headerLabelCellStyle;

        var cell37 = row3.CreateCell(3);

        cell37.SetCellValue("MAR");

        cell37.CellStyle = headerLabelCellStyle;

        var cell38 = row3.CreateCell(4);

        cell38.SetCellValue("APR");

        cell38.CellStyle = headerLabelCellStyle;

        var cell39 = row3.CreateCell(5);

        cell39.SetCellValue("MAY");

        cell39.CellStyle = headerLabelCellStyle;

        var cell310 = row3.CreateCell(6);

        cell310.SetCellValue("JUN");

        cell310.CellStyle = headerLabelCellStyle;

        var cell311 = row3.CreateCell(7);

        cell311.SetCellValue("JUL");

        cell311.CellStyle = headerLabelCellStyle;

        var cell312 = row3.CreateCell(8);

        cell312.SetCellValue("AUG");

        cell312.CellStyle = headerLabelCellStyle;

        var cell313 = row3.CreateCell(9);

        cell313.SetCellValue("SEP");

        cell313.CellStyle = headerLabelCellStyle;

        var cell314 = row3.CreateCell(10);

        cell314.SetCellValue("OCT");

        cell314.CellStyle = headerLabelCellStyle;

        var cell315 = row3.CreateCell(11);

        cell315.SetCellValue("NOV");

        cell315.CellStyle = headerLabelCellStyle;

        var cell316 = row3.CreateCell(12);

        cell316.SetCellValue("DEC");

        cell316.CellStyle = headerLabelCellStyle;

        var cell317 = row3.CreateCell(13);

        cell317.SetCellValue("TOTAL");

        cell317.CellStyle = headerLabelCellStyle;

        return sheet;

    }

    /// <summary>

    /// This function will export excel based on paramemeter

    /// </summary>

    /// <param name="dtExpenditure"></param>

    public void ExportToExcel(DataTable dtExpenditure)

    {

        Workbook = new HSSFWorkbook();

        var headerLabelCellStyle = Workbook.CreateCellStyle();

        //headerLabelCellStyle.BorderBottom = CellBorderType.THIN;

        headerLabelCellStyle.WrapText = true;

        var headerLabelFont = Workbook.CreateFont();

        headerLabelFont.Boldweight = (short)FontBoldWeight.BOLD;

        headerLabelCellStyle.SetFont(headerLabelFont);

     

            #region Cerate sheet

         

                totalJan = 0;

                totalFeb = 0;

                totalMar = 0;

                totalApr = 0;

                totalMay = 0;

                totalJun = 0;

                totalJul = 0;

                totalAug = 0;

                totalSep = 0;

                totalOct = 0;

                totalNov = 0;

                totalDec = 0;

                totalAll = 0;

                totalJan = 0;

                // Create the header row cell style

                janTotal = string.Empty;

                febTotal = string.Empty;

                marTotal = string.Empty;

                aprTotal = string.Empty;

                mayTotal = string.Empty;

                junTotal = string.Empty;

                julTotal = string.Empty;

                augTotal = string.Empty;

                sepTotal = string.Empty;

                octTotal = string.Empty;

                novTotal = string.Empty;

                decTotal = string.Empty;

                allTotal = string.Empty;

                var sheet = CreateExportDataTableSheetAndHeaderRow("Expenditure");

                var index = 4;

                int firstrow = index + 1;

           

                for (var rowIndex = 0; rowIndex < dtExpenditure.Rows.Count; rowIndex++)

                {

                    allMonthsTotal = new StringBuilder();

                    var row = sheet.CreateRow(index++);

                    int totalIndex = dtExpenditure.Columns.Count;

                    for (var colIndex = 0; colIndex < dtExpenditure.Columns.Count; colIndex++)

                    {

                        var cell = row.CreateCell(colIndex);

                        cell.CellStyle = headerLabelCellStyle;

                        if (colIndex == 0)

                        {

                            cell.SetCellType(CellType.STRING);

                            cell.SetCellValue(Convert.ToString(dtExpenditure.Rows[rowIndex][colIndex]));

                        }

                        else

                        {

                            cell.SetCellType(CellType.NUMERIC);

                            cell.SetCellValue(Convert.ToDouble(dtExpenditure.Rows[rowIndex][colIndex]));

                        }

                    }

                    allMonthsTotal.Append("B" + index);

                    allMonthsTotal.Append(",C" + index);

                    allMonthsTotal.Append(",D" + index);

                    allMonthsTotal.Append(",E" + index);

                    allMonthsTotal.Append(",F" + index);

                    allMonthsTotal.Append(",G" + index);

                    allMonthsTotal.Append(",H" + index);

                    allMonthsTotal.Append(",I" + index);

                    allMonthsTotal.Append(",J" + index);

                    allMonthsTotal.Append(",K" + index);

                    allMonthsTotal.Append(",L" + index);

                    allMonthsTotal.Append(",M" + index);

                    var cellTotal = row.CreateCell(totalIndex);

                    cellTotal.CellStyle = headerLabelCellStyle;

                    cellTotal.CellFormula = "SUM(" + allMonthsTotal + ")";

                    totalJan += Convert.ToDecimal(dtExpenditure.Rows[rowIndex]["Jan"]);

                    totalFeb += Convert.ToDecimal(dtExpenditure.Rows[rowIndex]["Feb"]);

                    totalMar += Convert.ToDecimal(dtExpenditure.Rows[rowIndex]["Mar"]);

                    totalApr += Convert.ToDecimal(dtExpenditure.Rows[rowIndex]["Apr"]);

                    totalMay += Convert.ToDecimal(dtExpenditure.Rows[rowIndex]["May"]);

                    totalJun += Convert.ToDecimal(dtExpenditure.Rows[rowIndex]["Jun"]);

                    totalJul += Convert.ToDecimal(dtExpenditure.Rows[rowIndex]["Jul"]);

                    totalAug += Convert.ToDecimal(dtExpenditure.Rows[rowIndex]["Aug"]);

                    totalSep += Convert.ToDecimal(dtExpenditure.Rows[rowIndex]["Sep"]);

                    totalOct += Convert.ToDecimal(dtExpenditure.Rows[rowIndex]["Oct"]);

                    totalNov += Convert.ToDecimal(dtExpenditure.Rows[rowIndex]["Nov"]);

                    totalDec += Convert.ToDecimal(dtExpenditure.Rows[rowIndex]["Dec"]);

                  

                }

                int lastrow = index;

                #region Create Formula

                janTotal = "B" + firstrow + ":B" + lastrow;

                febTotal = "C" + firstrow + ":C" + lastrow;

                marTotal = "D" + firstrow + ":D" + lastrow;

                aprTotal = "E" + firstrow + ":E" + lastrow;

                mayTotal = "F" + firstrow + ":F" + lastrow;

                junTotal = "G" + firstrow + ":G" + lastrow;

                julTotal = "H" + firstrow + ":H" + lastrow;

                augTotal = "I" + firstrow + ":I" + lastrow;

                sepTotal = "J" + firstrow + ":J" + lastrow;

                octTotal = "K" + firstrow + ":K" + lastrow;

                novTotal = "L" + firstrow + ":L" + lastrow;

                decTotal = "M" + firstrow + ":M" + lastrow;

                allTotal = "N" + firstrow + ":N" + lastrow;

             

                #endregion

                index = index + 2;

                var rowTotal = sheet.CreateRow(index);

                #region total expenses

                var expense = rowTotal.CreateCell(0);

                expense.SetCellType(CellType.NUMERIC);

                expense.SetCellValue("Total Expenditure");

                expense.CellStyle = headerLabelCellStyle;

                var jan = rowTotal.CreateCell(1);

                jan.SetCellType(CellType.FORMULA);

                jan.CellFormula = "SUM(" + janTotal + ")";

                jan.CellStyle = headerLabelCellStyle;

                jan.SetCellValue(Convert.ToDouble(totalJan));

                var feb = rowTotal.CreateCell(2);

                feb.SetCellType(CellType.FORMULA);

                feb.CellFormula = "SUM(" + febTotal + ")";

                feb.CellStyle = headerLabelCellStyle;

                feb.SetCellValue(Convert.ToDouble(totalFeb));

                var mar = rowTotal.CreateCell(3);

                mar.SetCellType(CellType.FORMULA);

                mar.CellFormula = "SUM(" + marTotal + ")";

                mar.CellStyle = headerLabelCellStyle;

                mar.SetCellValue(Convert.ToDouble(totalMar));

                var apr = rowTotal.CreateCell(4);

                apr.SetCellType(CellType.FORMULA);

                apr.CellFormula = "SUM(" + aprTotal + ")";

                apr.CellStyle = headerLabelCellStyle;

                apr.SetCellValue(Convert.ToDouble(totalApr));

                var may = rowTotal.CreateCell(5);

                may.SetCellType(CellType.FORMULA);

                may.CellFormula = "SUM(" + mayTotal + ")";

                may.CellStyle = headerLabelCellStyle;

                may.SetCellValue(Convert.ToDouble(totalMay));

                var jun = rowTotal.CreateCell(6);

                jun.SetCellType(CellType.FORMULA);

                jun.CellFormula = "SUM(" + junTotal + ")";

                jun.CellStyle = headerLabelCellStyle;

                jun.SetCellValue(Convert.ToDouble(totalJun));

                var jul = rowTotal.CreateCell(7);

                jul.SetCellType(CellType.FORMULA);

                jul.CellFormula = "SUM(" + julTotal + ")";

                jul.CellStyle = headerLabelCellStyle;

                jul.SetCellValue(Convert.ToDouble(totalJul));

                var aug = rowTotal.CreateCell(8);

                aug.SetCellType(CellType.FORMULA);

                aug.CellFormula = "SUM(" + augTotal + ")";

                aug.CellStyle = headerLabelCellStyle;

                aug.SetCellValue(Convert.ToDouble(totalAug));

                var sep = rowTotal.CreateCell(9);

                sep.SetCellType(CellType.FORMULA);

                sep.CellFormula = "SUM(" + sepTotal + ")";

                sep.CellStyle = headerLabelCellStyle;

                sep.SetCellValue(Convert.ToDouble(totalSep));

                var oct = rowTotal.CreateCell(10);

                oct.SetCellType(CellType.FORMULA);

                oct.CellFormula = "SUM(" + octTotal + ")";

                oct.CellStyle = headerLabelCellStyle;

                oct.SetCellValue(Convert.ToDouble(totalOct));

                var nov = rowTotal.CreateCell(11);

                nov.SetCellType(CellType.FORMULA);

                nov.CellFormula = "SUM(" + novTotal + ")";

                nov.CellStyle = headerLabelCellStyle;

                nov.SetCellValue(Convert.ToDouble(totalNov));

                var dec = rowTotal.CreateCell(12);

                dec.SetCellType(CellType.FORMULA);

                dec.CellFormula = "SUM(" + decTotal + ")";

                dec.CellStyle = headerLabelCellStyle;

                dec.SetCellValue(Convert.ToDouble(totalDec));

                var total = rowTotal.CreateCell(13);

                total.SetCellType(CellType.FORMULA);

                total.CellFormula = "SUM(" + allTotal + ")";

                total.CellStyle = headerLabelCellStyle;

                total.SetCellValue(Convert.ToDouble(totalAll));

                #endregion

            #endregion

        }

    /// <summary>

    /// This function write bytes to workbook

    /// </summary>

    /// <returns></returns>

    public byte[] GetBytes()

    {

        using (MemoryStream buffer = new MemoryStream())

        {

            Workbook.Write(buffer);

            return buffer.GetBuffer();

        }

    }

    #endregion

}

Result Excel Sheet