Post
Ask Question

Create and Export Excel sheet using NPOI and C#

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

  1. <%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true"  
  2. CodeFile="Default.aspx.cs" Inherits="_Default" %>  
  3. <asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">  
  4. </asp:Content>  
  5. <asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">  
  6. <h2>  
  7. Create and Export Excel Sheet using NPOI and C#  
  8. </h2>  
  9. <p>  
  10. <asp:Button ID="btnExport" runat="server" Text="Export"   
  11. onclick="btnExport_Click" />  
  12. </p>  
  13. </asp:Content> 

Default.aspx.cs

 
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Web;  
  4. using System.Web.UI;  
  5. using System.Web.UI.WebControls;  
  6. using System.Data;  
  7. public partial class _Default : System.Web.UI.Page  
  8. {  
  9. protected void Page_Load(object sender, EventArgs e)  
  10. {  
  11. }  
  12. protected void btnExport_Click(object sender, EventArgs e)  
  13. {  
  14. DataTable dtExpenditure = new DataTable();  
  15. Expenditure objExpenditure=new Expenditure();  
  16. dtExpenditure = objExpenditure.GetExpenditure(AddExpenses());  
  17. ExportUtility export = new ExportUtility();  
  18. export.ExportToExcel(dtExpenditure);  
  19. string saveAsFileName = string.Format("Expenditure-{0:d}.xls", DateTime.Now);  
  20. Response.ContentType = "application/vnd.ms-excel";  
  21. Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));  
  22. Response.Clear();  
  23. Response.BinaryWrite(export.GetBytes());  
  24. Response.End();  
  25. }  
  26. private List<Expenditure> AddExpenses()  
  27. {  
  28. List<Expenditure> lstExpenditure = new List<Expenditure>();  
  29. Expenditure objExpenditure1 = new Expenditure();  
  30. //Adding expenditure for all months  
  31. objExpenditure1.ExpenditureName = "Salary";  
  32. objExpenditure1.Jan = 34000;  
  33. objExpenditure1.Feb = 34500;  
  34. objExpenditure1.Mar = 34000;  
  35. objExpenditure1.Apr = 34000;  
  36. objExpenditure1.May = 36000;  
  37. objExpenditure1.Jun = 34000;  
  38. objExpenditure1.Jul = 34000;  
  39. objExpenditure1.Aug = 36000;  
  40. objExpenditure1.Sep = 34000;  
  41. objExpenditure1.Oct = 35000;  
  42. objExpenditure1.Nov = 34500;  
  43. objExpenditure1.Dec = 34700;  
  44. Expenditure objExpenditure2 = new Expenditure();  
  45. //Adding expenditure for all months  
  46. objExpenditure2.ExpenditureName = "Transport";  
  47. objExpenditure2.Jan = 4600;  
  48. objExpenditure2.Feb = 6000;  
  49. objExpenditure2.Mar = 4590;  
  50. objExpenditure2.Apr = 3000;  
  51. objExpenditure2.May = 6000;  
  52. objExpenditure2.Jun = 6000;  
  53. objExpenditure2.Jul = 34000;  
  54. objExpenditure2.Aug = 6000;  
  55. objExpenditure2.Sep = 6000;  
  56. objExpenditure2.Oct = 3000;  
  57. objExpenditure2.Nov = 6000;  
  58. objExpenditure2.Dec = 3000;  
  59. Expenditure objExpenditure3 = new Expenditure();  
  60. //Adding expenditure for all months  
  61. objExpenditure3.ExpenditureName = "Medical";  
  62. objExpenditure3.Jan = 1500;  
  63. objExpenditure3.Feb = 4500;  
  64. objExpenditure3.Mar = 1200;  
  65. objExpenditure3.Apr = 3600;  
  66. objExpenditure3.May = 6000;  
  67. objExpenditure3.Jun = 6000;  
  68. objExpenditure3.Jul = 4900;  
  69. objExpenditure3.Aug = 6000;  
  70. objExpenditure3.Sep = 4500;  
  71. objExpenditure3.Oct = 3000;  
  72. objExpenditure3.Nov = 6000;  
  73. objExpenditure3.Dec = 5000;  
  74. lstExpenditure.Add(objExpenditure1);  
  75. lstExpenditure.Add(objExpenditure2);  
  76. lstExpenditure.Add(objExpenditure3);  
  77. return lstExpenditure;  
  78. }  
  79. } 
 

View full source code