Generate Statistic Chart with ZedGraph in ASP.NET

The chart generation work is handled by my former colleague Peter. But he left.
He did the job with his own little chart library which I don’t know how to use it.
So I look for an open source and/or free solution. I find ZedGraph and it looks pretty much 90% what I need.

After experiencing ZedGraph, it is very amazing how easy to use ZedGraph and its features are so complete!

Download ZedGraph first.
If you are not going to make your own version or dive into the source, just download dll only.

Create a Web Application, add both ZedGraph.dll and ZedGraph.Web.dll to the reference.
And then right click on the toolbox, add new server control by adding ZedGraph.Web.dll.
You can now drag the ZedGraph control to your design view.
Remember to *REMOVE* all other tags, just leave

and the ZedGraphWeb control behind.
Result in something like this :
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="GenerateLineChart.aspx.cs" Inherits="TaoYuan.HistoryData.ByItemGroup.GenerateLineChart" %>
<%@ Register Assembly="ZedGraph.Web" Namespace="ZedGraph.Web" TagPrefix="zgw" %>

As you can read in their document, ZedGraph works in two modes.
I am not going to explain it since their have nice document on that.
I will show how to use the stream mode to expose a Graph as a url.

Back to the page which we have a ZedGraph control.
We assign an event handler to RenderGraph event which will define how the graph is rendered. Assign like the following :
zedGraphControl.RenderGraph += new ZedGraph.Web.ZedGraphWebControlEventHandler(zedGraphControl_RenderGraph);
public void zedGraphControl_RenderGraph(ZedGraphWeb zgw, Graphics g, MasterPane masterPane){...}

Inside the handler method, you will be given a default master pane.
I maybe talk about master pane later, let’s just use one graph in this post.
What you need to do here is setup the graph style, bind data and done.
I will just show a demo snippet with a little explaination.

//get a graph pane
GraphPane masterPaneDefault = masterPane[0];
//style the title and axises
masterPaneDefault.Title.Text = m_selectedStation.Name;
masterPaneDefault.XAxis.Title.IsVisible = false;
masterPaneDefault.YAxis.Title.Text = m_selectedItemInfo.Name+'('+ m_selectedItemInfo.Unit +')';
//we are going to use line chart here
//use data to make a curve line
PointPairList theLine = new PointPairList();
foreach (DataRow dr in m_DataFound.Rows)
DateTime l_date = (DateTime)dr[0];
double x = (double)new XDate(l_date);
double y = 0;
if (!DBNull.Value.Equals(dr[1]))
y = (Single)dr[1];
theLine.Add(x, y);
//set angle of xaxis label
masterPaneDefault.XAxis.Scale.FontSpec.Angle = 45;
//add the line to our graph pane
LineItem theCurve = masterPaneDefault.AddCurve(m_selectedItemInfo.Name, theLine, Color.Red, SymbolType.XCross);
masterPaneDefault.XAxis.Type = AxisType.DateAsOrdinal;
masterPaneDefault.XAxis.Scale.Format = "yyyy/MM/dd HH:mm";
//smooth the curve
//theCurve.Line.IsSmooth = true;
// Make curves thicker
//theCurve.Line.Width = 3.0F;
// Add gridlines to the plot, and make them gray
masterPaneDefault.XAxis.MajorGrid.IsVisible = true;
masterPaneDefault.YAxis.MajorGrid.IsVisible = true;
masterPaneDefault.XAxis.MajorGrid.Color = Color.Gray;
masterPaneDefault.YAxis.MajorGrid.Color = Color.LightGray;
masterPaneDefault.XAxis.MinorGrid.IsVisible = true;
masterPaneDefault.XAxis.MinorGrid.Color = Color.Khaki;
// Move the legend location
masterPaneDefault.Legend.Position = LegendPos.Right;
// Add a background gradient fill to the axis frame
masterPaneDefault.Chart.Fill = new Fill(Color.White, Color.FromArgb(255, 255, 210), -45F);
masterPaneDefault.Fill = new Fill(Color.FromArgb(250, 250, 255));
//masterPaneDefault.XAxis.Scale.MajorStep = 2.5d;
masterPaneDefault.Legend.IsVisible = false;
//calculate the axis scale ranges

The result will look something like this.
line chart
That’s it. There are other great charting tools avaiable.
The MS Chart control and Flot are both great. Check them out if you like.

Create in-memory OpenXml Document on-the-fly part-2

In create in-memory OpenXml Document on-the-fly part-1, We have create a .xlsx with a workbook, but that is not a valid document yet. A spreadsheet document must have spreadsheet, I will show how to do it in this article and how to add data to an empty spreadsheet. So stay put and here we go~

Adding worksheet is a little more work than adding a workbook, because it needs a name and a unique ID for the relationship. I generate a trivial name with an increment integer for worksheet name and use the autogenerated part ID as the relationship ID.I wrap these up in a method so that each time I need a new worksheet, I just invoke it and I am done.

/// <summary>
/// add a new worksheet to the package
/// with auto generate sheet name, id and relationship id
/// </summary>
/// <param name="package">package object</param>
public void AddNewWorksheet(SpreadsheetDocument package)
//add a worksheet to the workbook
WorkbookPart l_wbp = package.WorkbookPart;
var l_wsp = l_wbp.AddNewPart<WorksheetPart>();
GenerateWorksheetPart().Save(l_wsp);//get unique part id as relationship id
string l_rid = l_wbp.GetIdOfPart(l_wsp);//get unique sheetId
Workbook l_wb = l_wbp.Workbook;
Sheets l_sheets = l_wb.GetFirstChild<Sheets>();
UInt32Value l_sheetId = 1;
//count number of existing worksheets
foreach (Sheet l_sheet in l_sheets.Elements<Sheet>())
l_sheetId += 1;
}//generate sheetName
string l_sheetName = new StringBuilder("sheet").Append(l_sheetId.ToString()).ToString();
//add the relationship element to <sheets>
l_sheets.AppendChild<Sheet>(new Sheet() { Name = l_sheetName, SheetId = l_sheetId, Id = l_rid });

Two things worth mentioning, remember to call Save() after you modified a part, a part is a .xml in your program, you must save it in order to make the modification effective. another thing is add System.Linq reference, part of my code needed it. It helps you to locate an element in a easier way. The method GenerateWorksheetPart() is copy from DocumentReflector, all default values, nothing special.

/// /xl/worksheets/sheet.xml
private static Worksheet GenerateWorksheetPart()
    var element =
        new Worksheet(    //root element worksheet
            new SheetDimension() { Reference = "A1" },
            new SheetViews(
                new SheetView() { TabSelected = true, WorkbookViewId = (UInt32Value)0U }),
            new SheetFormatProperties() { DefaultRowHeight = 16.5D },
            new SheetData(),
            new PhoneticProperties() { FontId = (UInt32Value)1U, Type = PhoneticValues.NoConversion },
            new PageMargins() { Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D });
    return element;

Adding data is done by appending row element under sheetData element. You can use Worksheet.GetFirstChild() to get your sheetdata element. And then the structure of data is like this, rows can be under sheetData and cells can be under row. You will understand after reading the following code.

l_row = new Row() { RowIndex = (UInt32)rowIndex };
cellReference = columnName + rowIndex;    //A1 for example
l_cell = new Cell(new CellValue(col.ColumnName)) { CellReference = cellReference, DataType = CellValues.String };
columnName = getNextColName(columnName);

DataType can be assign using enum CellValues, no problem with that. getNextColName is a method I use to get the next column name, for example AZ will get BA.

/// get the next column name
/// origin column name e.g. AAA
/// next column e.g. AAB
private string getNextColName(string origin)
    return getNextColName(origin, origin.Length);

/// get the next column name
/// origin column name e.g. AAA
/// length of column name
/// next column e.g. AAB
private string getNextColName(string origin, int length)
    StringBuilder l_sb = new StringBuilder(origin.Length + 1);
    char l_endChar = origin.ToCharArray(length - 1, 1)[0];
    if (l_endChar == 'z' || l_endChar == 'Z')
        if (origin.Length == 1)
            return "AA";
        //if ends with Z and length > 1
        l_sb.Append(getNextColName(origin, length - 1));
        l_sb.Append(origin, 0, length - 1);
     return l_sb.ToString();

/// get the next alphabet, circular A->Z->A
/// origin Alphabet e.g. A
/// next Alphabet e.g. B
private char getNextAlphabet(char origin)
    int l_idx;
    Hashtable atoi = new Hashtable(26);
    char[] itoa =  'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z' };
     #region add mapping to hashtable
    atoi.Add('A', 0);
    atoi.Add('B', 1);
    atoi.Add('C', 2);
    atoi.Add('D', 3);
    atoi.Add('E', 4);
    atoi.Add('F', 5);
    atoi.Add('G', 6);
    atoi.Add('H', 7);
    atoi.Add('I', 8);
    atoi.Add('J', 9);
    atoi.Add('K', 10);
    atoi.Add('L', 11);
    atoi.Add('M', 12);
    atoi.Add('N', 13);
    atoi.Add('O', 14);
    atoi.Add('P', 15);
    atoi.Add('Q', 16);
    atoi.Add('R', 17);
    atoi.Add('S', 18);
    atoi.Add('T', 19);
    atoi.Add('U', 20);
    atoi.Add('V', 21);
    atoi.Add('W', 22);
    atoi.Add('X', 23);
    atoi.Add('Y', 24);
    atoi.Add('Z', 25);

    l_idx = (int)atoi[origin];
    //if the origin is Z, go back to A
    if (l_idx == 25)
        l_idx = 0;
        l_idx += 1;
     return itoa[l_idx];

These three method is badly implement and I will tune them when I have time. So, this is all you need to make a .xlsx with data only. I will explain how to generate line chart and bar chart using drawingML in part 3, if I have time to do it.