Excel Xml Writer Library Sample

| Contact

Sample:

Using in ASP.NET
The following sample shows how you can generate an Excel Xml Workbook from ASP.NET. Notice how you can save the workbook directly into the output stream of the Response.
Worths to mention again that the server does not requires any version of Excel installed to generate the workbook.
Copy the following code into notepad and save it as an ASPX file in a virtual directory, and copy the CarlosAg.ExcelXmlWriter.dll file into the bin directory of the application.

<%@ Page Language="C#" ContentType="text/xml" %>
<%@ Import Namespace="CarlosAg.ExcelXmlWriter" %>
<%
        Workbook book = new Workbook();
        Worksheet sheet = book.Worksheets.Add("Sample");
        WorksheetRow row =  sheet.Table.Rows.Add();
        row.Cells.Add(new WorksheetCell("Hello World from ASP.NET"));
        book.Save(Response.OutputStream);
%>

VB.NET Code in ASP.NET

<%@ Page Language="VB" ContentType="text/xml" %>
<%@ Import Namespace="ExcelXmlWriter" %>
<%
    Dim book As new Workbook()
    Dim sheet As Worksheet = book.Worksheets.Add("Sample")
    Dim row As WorksheetRow = sheet.Table.Rows.Add()

    Dim style As WorksheetStyle = book.Styles.Add("MyHeader")
    style.Font.Bold = true
    style.Interior.Color = "Red"
    style.Interior.Pattern = StyleInteriorPattern.Solid

    row.Cells.Add(new WorksheetCell("Hello from VB.NET", "MyHeader"))
    book.Save(Response.OutputStream)
%>

Advanced generation
The following sample shows a more advanced workbook that changes the default style for the cells and declares another style for the header. Then it generates some rows, a hyperlink and a formula to add the data for the rows. At the end it will open the file in Excel.

namespace TestSuite {
    using System;
    using System.Diagnostics;
    using CarlosAg.ExcelXmlWriter;
    class TestApp {
        static void Main() {
            string filename = @"e:\test.xls";
            Workbook book = new Workbook();

            // Specify which Sheet should be opened and the size of window by default
            book.ExcelWorkbook.ActiveSheetIndex = 1;
            book.ExcelWorkbook.WindowTopX = 100;
            book.ExcelWorkbook.WindowTopY  = 200;
            book.ExcelWorkbook.WindowHeight = 7000;
            book.ExcelWorkbook.WindowWidth = 8000;

            // Some optional properties of the Document
            book.Properties.Author="CarlosAg";
            book.Properties.Title="My Document";
            book.Properties.Created=DateTime.Now;

            // Add some styles to the Workbook
            WorksheetStyle style = book.Styles.Add("HeaderStyle");
            style.Font.FontName = "Tahoma";
            style.Font.Size = 14;
            style.Font.Bold = true;
            style.Alignment.Horizontal = StyleHorizontalAlignment.Center;
            style.Font.Color = "White";
            style.Interior.Color = "Blue";
            style.Interior.Pattern = StyleInteriorPattern.DiagCross;

            // Create the Default Style to use for everyone
            style = book.Styles.Add("Default");
            style.Font.FontName = "Tahoma";
            style.Font.Size = 10;

            // Add a Worksheet with some data
            Worksheet sheet = book.Worksheets.Add("Some Data");

            // we can optionally set some column settings
            sheet.Table.Columns.Add(new WorksheetColumn(150));
            sheet.Table.Columns.Add(new WorksheetColumn(100));

            WorksheetRow row = sheet.Table.Rows.Add();
            row.Cells.Add(new WorksheetCell("Header 1", "HeaderStyle"));
            row.Cells.Add(new WorksheetCell("Header 2", "HeaderStyle"));
            WorksheetCell cell = row.Cells.Add("Header 3");
            cell.MergeAcross = 1;            // Merge two cells together
            cell.StyleID = "HeaderStyle";

            row = sheet.Table.Rows.Add();
            // Skip one row, and add some text
            row.Index = 3;
            row.Cells.Add("Data");
            row.Cells.Add("Data 1");
            row.Cells.Add("Data 2");
            row.Cells.Add("Data 3");

            // Generate 30 rows
            for (int i=0; i<30; i++) {
                row = sheet.Table.Rows.Add();
                row.Cells.Add("Row " + i.ToString());
                row.Cells.Add(new WorksheetCell(i.ToString(), DataType.Number));
            }

            // Add a Hyperlink
            row = sheet.Table.Rows.Add();
            cell = row.Cells.Add();
            cell.Data.Text = "Carlos Aguilar Mares";
            cell.HRef = "http://www.carlosag.net";
            // Add a Formula for the above 30 rows
            cell = row.Cells.Add();
            cell.Formula = "=SUM(R[-30]C:R[-1]C)";

            // Save the file and open it
            book.Save(filename);
            Process.Start(filename);
        }
    }
}