Sample C# Code: Excel reporting - data, rich format, formulas

<%@ 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, "=B2*1581.25", 40, "=D2*1581.25", 69, "=F2*1581.25", 31, "=H2*1581.25" }

            ,{ "Fred", 32, "=B3*1426.25", 80, "=D3*1426.25", 45, "=F3*1426.25", 87, "=H3*1426.25" }

            ,{ "John", 60, "=B4*1789.25", 65, "=D4*1789.25", 90, "=F4*1789.25", 50, "=H4*1789.25" }

            ,{ "Jane", 40, "=B5*1369.25", 1, "=D5*1369.25", 22, "=F5*1369.25", 65, "=H5*1369.25" }

            ,{ "Total", "=SUM(B2:B5)", "=SUM(C2:C5)", "=SUM(D2:D5)", "=SUM(E2:E5)", "=SUM(F2:F5)", "=SUM(G2:G5)", "=SUM(H2:H5)", "=SUM(I2:I5)" }

            };

 

            int row;

            int column;

 

            //Bind values/formulas to cells

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

            {

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

                  {

                        //If input starts with "=" then bind it as formula

                        if (data[row, column].ToString().IndexOf("=") != 0)

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

                        else

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

                  }

            }

 

            //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/EmployeeDataFormatFormula" + Session.SessionID + ".xml");

            wb.SaveAs(fileName);

 

            //Prepare response for streaming

            Response.Clear();

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

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

 

            //Stream workbook to response

            Response.WriteFile(fileName, true);

 

            //Delete temporary file

            File.Delete(fileName);

 

            //Close workbook

            wb.Close();

 

            //End response

            Response.End();

      }

</script>