/// <summary>
/// When the user click on the export to Excel button
/// so we build a csv and then make it download with good header to make it opened by excel automatically
/// </summary>
protected void ExportExcelButtonClick(object sender, EventArgs e)
{
// We first check all settings
CheckSettings();
// We transform the list into XML Input
ListToXmlConverter conv = new ListToXmlConverter(_listName, _viewName);
// we build the csv string
StringBuilder csv = new StringBuilder();
csv.AppendLine(((XTitle != "") ? XTitle : "X") + ";" + ((YTitle != "") ? YTitle : "Y"));
// We prepare the data in a nice hashtable
Plots plots = conv.PrepareData(this._xValue, this._yValue, this._action);
for (int i = 0; i < plots.Count(); i++)
{
csv.AppendLine(plots.GetX(i).Replace(",", " ").Replace(";", " ") + ";" + plots.GetY(i).ToString().Replace(",", " ").Replace(";", " "));
}
Context.Response.Clear();
Context.Response.ContentType = "application/octet-stream";
Context.Response.AddHeader("Content-Disposition", "attachment; filename=ExcelExport.csv");
Context.Response.AddHeader("Content-Length", csv.Length.ToString());
Context.Response.Write(csv.ToString());
Context.Response.End();
}
/// <summary>
/// Generate the nice XML output for fusion charts
/// </summary>
/// <param name="chartTitle">Title of the chart</param>
/// <param name="xValue">Column name to use as x value</param>
/// <param name="xTitle">Title to give to x</param>
/// <param name="yValue">Column name to use as y value</param>
/// <param name="yTitle">Title to give to y</param>
/// <param name="groupAction">Action to perform (sum, count, ...)</param>
/// <param name="colors">List of colors to use (separated by ;)</param>
/// <returns></returns>
public string GenerateXml(string chartTitle, string xValue, string xTitle, string yValue, string yTitle, GroupAction groupAction, string colors)
{
// We first convert color (string) into a nice array
string[] separator = { ";" };
string[] colorsArray = colors.Split(separator, StringSplitOptions.RemoveEmptyEntries);
StringBuilder sb = new StringBuilder();
// We open the graph tag with a few parameters
sb.AppendLine("<graph caption='" + chartTitle + "' xAxisName='" + xTitle + "' yAxisName='" + yTitle + "'>");
// We prepare the data in a nice hashtable
Plots plots = PrepareData(xValue, yValue, groupAction);
for (int i = 0; i < plots.Count(); i++)
{
// We try to compute the color
string color = "";
try
{
color = colorsArray[i % (colorsArray.Length)]; // computes to color to use
}
catch { } // if we fail we skip the error, default color will be used
// We add a line to the XML
sb.Append("<set name='" + plots.GetX(i) + "' value='" + plots.GetY(i).ToString() + "'");
if (color != "")
{
sb.Append(" color='" + color + "'");
}
sb.AppendLine(" />");
}
// We close the graph tag opened previously
sb.AppendLine("</graph>");
// We finally return the xml data
return(sb.ToString());
}
/// <summary>
/// Transforms the list of items into something easily usable (a Plots object).
/// Performs also operations such as SUM, COUNT over GROUPBY
/// </summary>
/// <param name="xValue">The name of the column to use as xValue</param>
/// <param name="yValue">The nam of the column to use as yValue</param>
/// <param name="action">The action to perform, if any...</param>
/// <returns></returns>
public Plots PrepareData(string xValue, string yValue, GroupAction action)
{
// We first check that xValue and yValue are correct and throw an exception if needed
if (!_items.List.Fields.ContainsField(xValue))
{
string message = "The column \"" + xValue + "\" does not exist in the list \"" + _items.List.Title + "\" or in the specified view \"" + _view.Title + "\".<br /><br />Check the view you specified and make sure that the column exists.";
message += ListValidColumns();
throw (new Exception(message));
}
if (yValue != String.Empty && !_items.List.Fields.ContainsField(yValue))
{
string message = "The column \"" + yValue + "\" does not exist in the list \"" + _items.List.Title + "\" or in the specified view \"" + _view.Title + "\".<br /><br />Check the view you specified and make sure that the column exists.";
message += ListValidColumns();
throw (new Exception(message));
}
// We prepare our output
Plots plots = new Plots();
// We loop through our SPList
foreach (SPListItem item in _items)
{
// We get the X and Y from
string x = "";
try
{
if (item[xValue] == null)
continue;
x = item[xValue].ToString();
}
catch
{
throw (new Exception("The column \"" + xValue + "\" does not exist in the view"));
}
double y = 0;
if (yValue != String.Empty) Double.TryParse(item[yValue].ToString(), out y);
// The value is already in our array
if (plots.Contains(x))
{
int pos = plots.XIndexOf(x);
if (action == GroupAction.COUNT)
{
// Count, we add +1 to the existing value
plots.SetY(pos, Double.Parse(plots.GetY(pos).ToString()) + 1);
}
if (action == GroupAction.SUM)
{
// Sum, we add the value to the existing value
plots.SetY(pos, Double.Parse(plots.GetY(pos).ToString()) + y);
}
if (action == GroupAction.NOTHING)
{
// No action, we simply add the value
plots.Add(x, y);
}
}
else // The value is a new one in our array
{
if (action == GroupAction.COUNT)
{
// Count, the first yValue is 1.
plots.Add(x, 1);
}
else
{
// anything else than Count, we just add the plot
plots.Add(x, y);
}
}
}
return plots;
}
/// <summary>
/// Transforms the list of items into something easily usable (a Plots object).
/// Performs also operations such as SUM, COUNT over GROUPBY
/// </summary>
/// <param name="xValue">The name of the column to use as xValue</param>
/// <param name="yValue">The nam of the column to use as yValue</param>
/// <param name="action">The action to perform, if any...</param>
/// <returns></returns>
public Plots PrepareData(string xValue, string yValue, GroupAction action)
{
// We first check that xValue and yValue are correct and throw an exception if needed
if (!_items.List.Fields.ContainsField(xValue))
{
string message = "The column \"" + xValue + "\" does not exist in the list \"" + _items.List.Title + "\" or in the specified view \"" + _view.Title + "\".<br /><br />Check the view you specified and make sure that the column exists.";
message += ListValidColumns();
throw (new Exception(message));
}
if (yValue != String.Empty && !_items.List.Fields.ContainsField(yValue))
{
string message = "The column \"" + yValue + "\" does not exist in the list \"" + _items.List.Title + "\" or in the specified view \"" + _view.Title + "\".<br /><br />Check the view you specified and make sure that the column exists.";
message += ListValidColumns();
throw (new Exception(message));
}
// We prepare our output
Plots plots = new Plots();
// We loop through our SPList
foreach (SPListItem item in _items)
{
// We get the X and Y from
string x = "";
try
{
if (item[xValue] == null)
{
continue;
}
x = item[xValue].ToString();
}
catch
{
throw (new Exception("The column \"" + xValue + "\" does not exist in the view"));
}
double y = 0;
if (yValue != String.Empty)
{
Double.TryParse(item[yValue].ToString(), out y);
}
// The value is already in our array
if (plots.Contains(x))
{
int pos = plots.XIndexOf(x);
if (action == GroupAction.COUNT)
{
// Count, we add +1 to the existing value
plots.SetY(pos, Double.Parse(plots.GetY(pos).ToString()) + 1);
}
if (action == GroupAction.SUM)
{
// Sum, we add the value to the existing value
plots.SetY(pos, Double.Parse(plots.GetY(pos).ToString()) + y);
}
if (action == GroupAction.NOTHING)
{
// No action, we simply add the value
plots.Add(x, y);
}
}
else // The value is a new one in our array
{
if (action == GroupAction.COUNT)
{
// Count, the first yValue is 1.
plots.Add(x, 1);
}
else
{
// anything else than Count, we just add the plot
plots.Add(x, y);
}
}
}
return(plots);
}