Sample C# Code: Excel reporting - richly format data

<%@ Page Language="C#"%>

<%@ Import Namespace="System.IO" %>

<%@ Import Namespace="PagosSpreadSheet" %>

<script runat="server">

      protected void Page_Load(object sender, EventArgs e)

      {

            //Create an instance of the application

            Application app = new Application();

            //Add a workbook

            Workbook wb = app.Workbooks.Add();

            //Load a worksheet from added workbook

            Worksheet ws = wb.Worksheets["Sheet1"];

 

            //Prepare worksheet data

            object[,] data = new object[,] {

            { "Employee", "Volume Q1", "Sales Q1", "Volume Q2", "Sales Q2", "Volume Q3", "Sales Q3",
"Volume Q4", "Sales Q4" }

            ,{ "Helen", 5, 7906.25F, 40, 63250, 69, 109106.25F, 31, 49018.75F }

            ,{ "Fred", 32, 45640, 80, 114100, 45, 64181.25, 87, 124083.75F }

            ,{ "John", 60, 107355, 65, 116301.25F, 90, 161032.5F, 50, 89462.5F }

            ,{ "Jane", 40, 54770, 1, 1369.25F, 22, 30123.5F, 65, 89001.25F }

            ,{ "Total", 137, 215671.25F, 186, 295020.5F, 226, 364443.5F, 233, 351566.25F }

            };

 

            int row;

            int column;

 

            //Bind values to cells

            for (row = 0; row < data.GetLength(0); row++)

            {

                for (column = 0; column < data.GetLength(1); column++)

                {

                    ws.get_Cells(row, column).Value = data[row, column];

                }

            }

 

            //Apply styles

            Range a1i6 = ws.get_Range("A1:I6");

            a1i6.Font.Name = "Arial";

            a1i6.Font.Size = 8;

            a1i6.HorizontalAlignment = HorizontalAlignment.Center;

 

            Range a1i1 = ws.get_Range("A1:I1");

            a1i1.Font.Bold = true;

            a1i1.Interior.Color = System.Drawing.Color.FromArgb(51, 204, 204);

 

            Range a6i6 = ws.get_Range("A6:I6");

            a6i6.Font.Bold = true;

            a6i6.Interior.Color = System.Drawing.Color.FromArgb(51, 204, 204);

 

            string numberFormat = "_(\"$\"* #,##0.00_);_(\"$\"* \\(#,##0.00\\);_(\"$\"* \"-\"??_);_(@_)";   

            Range c2c6 = ws.get_Range("C2:C6");

            Range e2e6 = ws.get_Range("E2:E6");

            Range g2g6 = ws.get_Range("G2:G6");

            Range i2i6 = ws.get_Range("I2:I6");

            c2c6.NumberFormat = numberFormat;

            e2e6.NumberFormat = numberFormat;

            g2g6.NumberFormat = numberFormat;

            i2i6.NumberFormat = numberFormat;

           

            //Save workbook to a temporary file for streaming

            string fileName = Server.MapPath("Spreadsheets/Temp/EmployeeDataWithFormat" + Session.SessionID + ".xml");

            wb.SaveAs(fileName);

 

            //Prepare response for streaming

            Response.Clear();

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

            Response.AddHeader("Content-Disposition", "attachment; filename=EmployeeDataWithFormat.xml");

 

            //Stream workbook to response

            Response.WriteFile(fileName, true);

 

            //Delete temporary file

            File.Delete(fileName);

 

            //Close workbook

            wb.Close();

 

            //End response

            Response.End();

      }

</script>