Create Excel Report Programmatically Without Installing MS Excel. Asp.Net & Win Form C# Using WebService Example

Assuming you only have open source spreadsheet software i.e OpenOffice.org Calc and you want to make Excel report file programmtically.
You dont have MS Office installed in your computer or server. Sure you can do it using ExcelLibrary library from Google Apps.

In order to try examples in this blog post, please download ExcelLibrary library from this url https://code.google.com/p/excellibrary/downloads/list

Here, I give examples implementing it by creating a WebService so that Asp.Net Website or Win From C# can use this WebService.
This makes ExcelLibrary file only have to copy in WebService’s server without distribute it to local / client box.

First of all we need to know about basic of ExcelLibrary usage. Taken from https://code.google.com/p/excellibrary

//create new xls file
string file = "C:\\newdoc.xls";
Workbook workbook = new Workbook();
Worksheet worksheet = new Worksheet("First Sheet");
worksheet.Cells[0, 1] = new Cell((short)1);
worksheet.Cells[2, 0] = new Cell(9999999);
worksheet.Cells[3, 3] = new Cell((decimal)3.45);
worksheet.Cells[2, 2] = new Cell("Text string");
worksheet.Cells[2, 4] = new Cell("Second string");
worksheet.Cells[4, 0] = new Cell(32764.5, "#,##0.00");
worksheet.Cells[5, 1] = new Cell(DateTime.Now, @"YYYY\-MM\-DD");
worksheet.Cells.ColumnWidth[0, 1] = 3000;
workbook.Worksheets.Add(worksheet);
workbook.Save(file);

// open xls file
Workbook book = Workbook.Load(file);
Worksheet sheet = book.Worksheets[0];

 // traverse cells
 foreach (Pair<Pair<int, int>, Cell> cell in sheet.Cells)
 {
     dgvCells[cell.Left.Right, cell.Left.Left].Value = cell.Right.Value;
 }

 // traverse rows by Index
 for (int rowIndex = sheet.Cells.FirstRowIndex; 
        rowIndex <= sheet.Cells.LastRowIndex; rowIndex++)
 {
     Row row = sheet.Cells.GetRow(rowIndex);
     for (int colIndex = row.FirstColIndex; 
        colIndex <= row.LastColIndex; colIndex++)
     {
         Cell cell = row.GetCell(colIndex);
     }
 }

Creating WebService Project

This webservice contains function that simply save DataTable instance’s data to Excel file

  • Open MS Visual Web Developer and create new project Asp.Net Empty WebSite
  • Add new WebService
  • Add a reference to ExcelLibrary file, ExcelLibrary.dll
  • Copy below source code:
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.Services;
    using System.Data;
    using System.Data.SqlClient;
    using ExcelLibrary.SpreadSheet;
    
    namespace ExcelLibWS
    {
        
        [WebService(Namespace = &quot;http://tempuri.org/&quot;)]
        [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
        [System.ComponentModel.ToolboxItem(false)]
       
        public class ExcelFunction : System.Web.Services.WebService
        {
    
            [WebMethod]
            public void DataTable2Excel(string filepath, DataTable dt)
            {
                Workbook workbook = new Workbook();
                Worksheet worksheet = new Worksheet(&quot;Sheet1&quot;);
                int row_no = 0;
                foreach (DataRow dr in dt.Rows)
                {
                    for (int col_no = 0; col_no &lt; dt.Columns.Count; col_no++)
                    {
                        worksheet.Cells[row_no, col_no] = new Cell(dr[col_no].ToString());
                        
                    }
    
                    row_no++;
                }
                workbook.Worksheets.Add(worksheet);
                workbook.Save(filepath);
            }
        }
    }
    

    As you can see at above code that <code>DataTable2Excel(..)</code> has two arguments, file path of saved Excel and DataTable given by any application’s Query.

Asp .Net Usage

This example shows Asp.Net project that uses above WebService. Users can export viewed data to Excel.
I use AdventureWorks database to do this.

This Asp .Net usage shows Top 100 Store Sales Value and contains Export To Excel button to save DataTable to Excel file.

  • Open MS Visual Web Developer and create new project Asp.Net Empty WebSite, WebExcelFunction
  • Add a web reference point to your ExcelLibWS
  • Add a Webform, WebForm1.aspx
    <form id="form1" runat="server">
        <div>
            <h3>Top 100 Store Sales Value</h3>
            <asp:ListView ID="ListView1" runat="server">
                <LayoutTemplate>
                    <table>
                        <thead>
                            <th>Store Name</th>
                        <th>Territory Name</th>
                        <th>Sales Value</th>
                        </thead>
                        <asp:PlaceHolder ID="itemplaceholder" runat="server"></asp:PlaceHolder>
                    </table>
                </LayoutTemplate>
                <ItemTemplate>
                    <tr>
                    <td><%# Eval("StoreName") %></td><td><%# Eval("TerritoryName")%></td>
                    <td><%# Eval("SalesValue")%></td>
                    </tr>
                </ItemTemplate>
            </asp:ListView>
            
    
            <asp:Button ID="Button1" runat="server" Text="Export To Excel" 
                onclick="Button1_Click" />
        </div>
        </form>
    

    WebForm1.aspx.cs

    using System.Data;
    using System.Data.SqlClient;
    using WebExcelFunction.ExcelLibWS;
    
    namespace WebExcelFunction
    {
        public partial class WebForm1 : System.Web.UI.Page
        {
            DataTable dt;
            protected void Page_Load(object sender, EventArgs e)
            {
                string connectionstring= @"Data Source=.\SQLDEV;Initial Catalog=AdventureWorks;Integrated Security=True";
                SqlConnection conn = new SqlConnection(connectionstring);
                string query = "select Top 100 row_number() over(order by sum(Sales.SalesOrderHeader.TotalDue) desc) as rowno, Sales.Store.Name as StoreName, Sales.SalesTerritory.Name as TerritoryName, sum(Sales.SalesOrderHeader.TotalDue) as SalesValue from Sales.Customer " +
    				"inner join Sales.Store on Sales.Customer.StoreID = Sales.Store.BusinessEntityID " +
    				"inner join Sales.SalesTerritory on Sales.SalesTerritory.TerritoryID = Sales.Customer.TerritoryID " +
    				"inner join Sales.SalesOrderHeader on Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID " +
    				"where Sales.Customer.StoreID is not null " +
    				"group by Sales.Store.Name,Sales.SalesTerritory.Name " +
                    "order by sum(Sales.SalesOrderHeader.TotalDue) desc";
                SqlDataAdapter da = new SqlDataAdapter(query, conn);
                dt = new DataTable();
                da.Fill(dt);
    
                da.Dispose();
                conn.Close();
                dt.TableName = "Store_Sales";
    
                ListView1.DataSource = dt;
                ListView1.DataBind();
    
                
            }
    
            protected void Button1_Click(object sender, EventArgs e)
            {
                ExcelFunction ef = new ExcelFunction();
                ef.DataTable2Excel(Server.MapPath(".") + "/" + "store_sales.xls", dt);
                Response.Redirect("DownloadHandler.ashx");
    
            }
        }
    }
    

    It looks like below pic:

    I only hard code the excel file => store_sales.xls for demonstration purpose. Off course you can do better.

  • After DataTable’s data exported to Excel and then download it, I use Generic Handler, DownloadHandler.ashx
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    
    namespace WebExcelFunction
    {
        public class DownloadHandler : IHttpHandler
        {
    
            public void ProcessRequest(HttpContext context)
            {
                string fileName = &quot;store_sales.xls&quot;;
                context.Response.ContentType = &quot;application/force-download&quot;;
                context.Response.AddHeader(&quot;Content-Disposition&quot;, &quot;attachment; filename=&quot; + fileName);
                context.Response.WriteFile(context.Server.MapPath(&quot;.&quot;) + &quot;/&quot; + fileName);
            }
    
            public bool IsReusable
            {
                get
                {
                    return false;
                }
            }
        }
    }
    

WinForm C# Usage

Not much different from Asp .Net usage. Only this is win form C#.

  • Create New Win Form Application, WinExcelFunction
  • Add new WebService and point to ExcelLibWS
  • Add new Form, Form1.cs. The Design is like below pic:

    Form1.cs source code:

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Data;
    using System.Data.SqlClient;
    using WinExcelFunction.ExcelLibWS;
    
    namespace WinExcelFunction
    {
        public partial class Form1 : Form
        {
            DataTable dt;
            public Form1()
            {
                InitializeComponent();
            }
    
            private void Form1_Load(object sender, EventArgs e)
            {
                string connectionstring = @"Data Source=.\SQLDEV;Initial Catalog=AdventureWorks;Integrated Security=True";
                SqlConnection conn = new SqlConnection(connectionstring);
                string query = "select Top 100 row_number() over(order by sum(Sales.SalesOrderHeader.TotalDue) desc) as rowno, Sales.Store.Name as StoreName, Sales.SalesTerritory.Name as TerritoryName, sum(Sales.SalesOrderHeader.TotalDue) as SalesValue from Sales.Customer " +
                    "inner join Sales.Store on Sales.Customer.StoreID = Sales.Store.BusinessEntityID " +
                    "inner join Sales.SalesTerritory on Sales.SalesTerritory.TerritoryID = Sales.Customer.TerritoryID " +
                    "inner join Sales.SalesOrderHeader on Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID " +
                    "where Sales.Customer.StoreID is not null " +
                    "group by Sales.Store.Name,Sales.SalesTerritory.Name " +
                    "order by sum(Sales.SalesOrderHeader.TotalDue) desc";
                SqlDataAdapter da = new SqlDataAdapter(query, conn);
                dt = new DataTable();
                da.Fill(dt);
    
                da.Dispose();
                conn.Close();
                dt.TableName = "Store_Sales";
    
                listView1.View = View.Details ;
                listView1.Columns.Add("No", 30);
                listView1.Columns.Add("Store Name", 90);
                listView1.Columns.Add("Teritory", 70);
                listView1.Columns.Add("Sales Value", 90, HorizontalAlignment.Right );
    
                ListViewItem lvi = null;
                foreach (DataRow dr in dt.Rows)
                {
                    for (int i = 0; i &lt; dt.Columns.Count; i++)
                    {
                        if (i == 0)
                        {
                            lvi = listView1.Items.Add(dr[i].ToString());
                        }
                        else
                        {
                            lvi.SubItems.Add(dr[i].ToString());
                        }
                    }
                }
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                SaveFileDialog sfd = new SaveFileDialog();
                sfd.Filter = "Excel .xls File|*.xls";
                if (sfd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                {
                    ExcelFunction ef = new ExcelFunction();
                    
                    ef.DataTable2Excel(sfd.FileName, dt);
                    System.Diagnostics.Process.Start(sfd.FileName);
                    
                }
    
            }
        }
    }
    

    Run it to show Top 100 Store Sales Value and if you click on ‘Export To Excel’ button then SaveFileDialog will pop up.
    You can decide file name & path. After save action the form will show saved excel file.

Summary

ExcelLibrary Library is useful library to create an excel file without having or installing MS Office Excel. Off course, if you have MS Excel it works too. This also good for creating a report that comes from your application.

WebService with ExcelLibrary reference implementation is good so that this library doesnt have to be copied to client box. Since WebService makes application flexible, Asp .Net Website or Win form app can consume this WebService.

Regards,
Agung Gugiaji

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s