Create and Export
Excel sheet using NPOI and C#
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();
-
- 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();
-
- 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();
-
- 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;
- }
- }
View full source code