public static bool ChangeDatesToString(DataTable dt)
{
try
{
for (int i = 0; i < dt.Columns.Count; i++)
{
DataColumn col = dt.Columns[i];
if (col.DataType == typeof(DateTime))
{
SpreadsheetGearUtils.ChangeColumnDataType(dt, col.ColumnName, typeof(string));
}
}
}
catch (Exception)
{
return(false);
}
return(true);
}
// Main MSStackedColumn
public static System.Web.UI.DataVisualization.Charting.Chart MSStackedColumn(
DataTable dt_rawdata,
string x_var,
string y_var,
string series_var,
string x_vartype,
DataTable colors,
string chartname,
string charttitle,
string xaxistitle,
string yaxistitle,
double xaxismin,
double xaxismax,
double yaxismin,
double yaxismax,
int wd,
int ht,
DateTime?xaxismin_date,
DateTime?xaxismax_date)
{
if (dt_rawdata != null)
{
//SQL_utils sql = new SQL_utils();
if (x_vartype == "numeric")
{
SpreadsheetGearUtils.ChangeColumnDataType(dt_rawdata, x_var, typeof(double));
}
//CREATE THE CHART
System.Web.UI.DataVisualization.Charting.Chart chart = new System.Web.UI.DataVisualization.Charting.Chart();
foreach (DataColumn col in dt_rawdata.Columns)
{
if (col.ColumnName == x_var)
{
if (x_vartype == "date")
{
col.DataType = typeof(DateTime);
}
else if (x_vartype == "numeric")
{
col.DataType = typeof(Double);
}
else if (x_vartype == "string")
{
col.DataType = typeof(string);
}
}
}
try
{
chart.DataBindCrossTable(dt_rawdata.Rows, series_var, x_var, y_var, "", PointSortOrder.Descending);
List <string> series_names = new List <string>();
foreach (Series s in chart.Series)
{
series_names.Add(s.Name);
}
List <string> random_colors = new List <string> {
"123245", "76AD23", "E40011", "7D2138", "ABCD33", "4561AA", "9C3872", "ED4421", "8921DD"
, "341951", "ADED21", "5599DB"
};
int series_counter = 0;
//Assign the correct colors
foreach (Series s in chart.Series)
{
s.ChartType = SeriesChartType.StackedColumn;
switch (x_vartype)
{
case "numeric":
s.XValueType = ChartValueType.Double;
break;
case "date":
s.XValueType = ChartValueType.Date;
break;
case "string":
s.XValueType = ChartValueType.String;
break;
}
//not helpful --> s.IsXValueIndexed = true;
//s.Sort(PointSortOrder.Ascending, sortBy: ("Y"));
string str_color;
try
{
str_color = colors.AsEnumerable().Where(r => r.Field <string>("group_var") == s.Name).Select(r => r.Field <string>("group_color")).First();
}
catch (Exception ex)
{
string x = ex.Message;
str_color = random_colors[series_counter];
}
s.Color = System.Drawing.ColorTranslator.FromHtml("#" + str_color);
s.ToolTip = s.Name;
series_counter++;
}
DataSet ds_series1 = chart.DataManipulator.ExportSeriesValues();
//Insert empty points as need so stacking is correct
if (series_names.Count > 0)
{
if (x_vartype == "numeric")
{
chart.DataManipulator.InsertEmptyPoints(1, IntervalType.Number, string.Join(",", series_names));
}
else if (x_vartype == "date")
{
chart.DataManipulator.InsertEmptyPoints(1, IntervalType.Months, string.Join(",", series_names));
}
else if (x_vartype == "string")
{
chart.DataManipulator.InsertEmptyPoints(1, IntervalType.Number, string.Join(",", series_names));
}
DataSet ds_series2 = chart.DataManipulator.ExportSeriesValues();
}
chart.ImageType = System.Web.UI.DataVisualization.Charting.ChartImageType.Png;
chart.Width = wd;
chart.Height = ht;
//ADD A PLACE HOLDER CHART AREA TO THE CHART
chart.ChartAreas.Add(new ChartArea());
if (x_vartype == "numeric")
{
if (xaxismin != -999)
{
chart.ChartAreas[0].AxisX.Minimum = xaxismin;
}
if (xaxismax != -999)
{
chart.ChartAreas[0].AxisX.Maximum = xaxismax;
}
}
else if (x_vartype == "date")
{
if (xaxismin_date != null)
{
DateTime mydate = Convert.ToDateTime(xaxismin_date);
chart.ChartAreas[0].AxisX.Minimum = mydate.ToOADate();
}
if (xaxismax_date != null)
{
DateTime mydate = Convert.ToDateTime(xaxismax_date);
chart.ChartAreas[0].AxisX.Maximum = mydate.ToOADate();
}
}
if (yaxismin != -999)
{
chart.ChartAreas[0].AxisY.Minimum = yaxismin;
}
if (yaxismax != -999)
{
chart.ChartAreas[0].AxisY.Maximum = yaxismax;
}
chart.ChartAreas[0].RecalculateAxesScale();
//here not helpgful --> chart.AlignDataPointsByAxisLabel();
chart.ChartAreas["ChartArea1"].AxisX.MajorGrid.LineColor = Color.Gainsboro;
chart.ChartAreas["ChartArea1"].AxisY.MajorGrid.LineColor = Color.Gainsboro;
chart.ChartAreas["ChartArea1"].AxisX.Title = xaxistitle;
chart.ChartAreas["ChartArea1"].AxisY.Title = yaxistitle;
//if (YAxisType == "free")
//{
//ADD A PLACE HOLDER LEGEND TO THE CHART
chart.Legends.Add("Default");
chart.Legends["Default"].LegendStyle = LegendStyle.Table;
chart.Legends["Default"].TableStyle = LegendTableStyle.Auto;
chart.Legends["Default"].Docking = Docking.Right;
//}
//else if (YAxisType == "fixed")
//{
// //chart.Legends.Clear();
//}
Title t = new Title();
t.Text = charttitle + "\n(created " + System.DateTime.Now.ToShortDateString() + " " + System.DateTime.Now.ToShortTimeString() + ")";
if (dt_rawdata.Rows.Count == 0)
{
t.Text += "\n** 0 records entered **";
}
t.Alignment = ContentAlignment.TopLeft;
t.Font = new System.Drawing.Font("Arial", 12, GraphicsUnit.Point);
chart.Titles.Add(t);
return(chart);
}
catch (Exception ex)
{
string x = ex.Message;
return(null);
}
}
else
{
return(null);
}
}
// Geom: this can be either Point or Line
public static System.Web.UI.DataVisualization.Charting.Chart MSGeom(string geom,
DataTable dt_rawdata,
string x_var,
string y_var,
string series_var,
string x_vartype,
string y_vartype,
string chartname,
string charttitle,
string xaxistitle,
string yaxistitle,
double xaxismin,
double xaxismax,
double yaxismin,
double yaxismax,
int wd,
int ht,
string customProps)
{
if (dt_rawdata != null)
{
//SQL_utils sql = new SQL_utils();
if (x_vartype == "numeric")
{
SpreadsheetGearUtils.ChangeColumnDataType(dt_rawdata, x_var, typeof(double));
}
//CREATE THE CHART
System.Web.UI.DataVisualization.Charting.Chart chart = new System.Web.UI.DataVisualization.Charting.Chart();
//Define axis data types
foreach (DataColumn col in dt_rawdata.Columns)
{
if (col.ColumnName == x_var)
{
if (x_vartype == "date")
{
col.DataType = typeof(DateTime);
}
else if (x_vartype == "numeric")
{
col.DataType = typeof(Double);
}
else if (x_vartype == "string")
{
col.DataType = typeof(string);
}
}
}
try
{
//Pass in the custom properties in the ToolTip and then
//parse later. THis is a hack that could/should be better.
//Though I wasn't able to get this "otherFields" parameter
//to work for anything but the ToolTip.
if (customProps != "")
{
//chart.DataBindCrossTable(dt_rawdata.Rows, series_var, x_var, y_var, "ToolTip=" + customProps, PointSortOrder.Descending);
chart.DataBindCrossTable(dt_rawdata.Rows, series_var, x_var, y_var, "ToolTip=" + customProps, PointSortOrder.Descending);
}
else
{
chart.DataBindCrossTable(dt_rawdata.Rows, series_var, x_var, y_var, "", PointSortOrder.Descending);
}
List <string> series_names = new List <string>();
foreach (Series s in chart.Series)
{
series_names.Add(s.Name);
}
List <string> random_colors = new List <string> {
"123245", "76AD23", "E40011", "7D2138", "ABCD33", "4561AA", "9C3872", "ED4421", "8921DD"
, "341951", "ADED21", "5599DB"
};
int series_counter = 0;
//Assign the correct colors
foreach (Series s in chart.Series)
{
switch (geom)
{
case "point":
s.ChartType = SeriesChartType.Point;
//s.CustomProperties = "MarkerSize=markerSize";
break;
case "line":
s.ChartType = SeriesChartType.Line;
s.BorderColor = Color.Red;
break;
}
switch (x_vartype)
{
case "numeric":
s.XValueType = ChartValueType.Double;
break;
case "date":
s.XValueType = ChartValueType.Date;
break;
case "string":
s.XValueType = ChartValueType.String;
break;
}
switch (y_vartype)
{
case "numeric":
s.YValueType = ChartValueType.Double;
break;
case "date":
s.YValueType = ChartValueType.Date;
break;
case "string":
s.YValueType = ChartValueType.String;
break;
}
s.IsValueShownAsLabel = false;
//s.LabelForeColor = System.Drawing.ColorTranslator.FromHtml("#" + random_colors[series_counter]);
//s.MarkerColor = System.Drawing.ColorTranslator.FromHtml("#" + random_colors[series_counter]);
//s.LabelForeColor = ColorTranslator.FromHtml( random_colors[series_counter]);
//s.LabelBackColor = s.MarkerColor;
int counter = 2;
foreach (var point in s.Points)
{
if (geom == "line")
{
//point.MarkerStyle = MarkerStyle.Diamond;
//point.MarkerColor = Color.Lime;
}
else
{
//point.ToolTip = s.Name + ": " + counter.ToString();
//point.MarkerSize = counter;
if (point.ToolTip.Contains("|"))
{
string[] props = point.ToolTip.Split('|');
point.MarkerSize = Convert.ToInt16(props[0]);
if (props[0] == "12")
{
point.MarkerBorderColor = Color.Black;
}
else if (props[0] == "20")
{
point.MarkerBorderColor = Color.Black;
if (props.Length >= 3)
{
point.Label = props[3].ToString();
}
}
if (props.Length >= 3)
{
point.Url = props[2];
}
point.ToolTip = props[1];
counter++;
}
else
{
//
}
}
}
s.MapAreaAttributes = "target=\"_blank\"";
s.ToolTip = s.Name;
series_counter++;
}
DataSet ds_series1 = chart.DataManipulator.ExportSeriesValues();
//Insert empty points as need so stacking is correct
if (series_names.Count > 0)
{
if (x_vartype == "numeric")
{
chart.DataManipulator.InsertEmptyPoints(1, IntervalType.Number, string.Join(",", series_names));
}
else if (x_vartype == "date")
{
chart.DataManipulator.InsertEmptyPoints(1, IntervalType.Months, string.Join(",", series_names));
}
else if (x_vartype == "string")
{
chart.DataManipulator.InsertEmptyPoints(1, IntervalType.Number, string.Join(",", series_names));
}
//DataSet ds_series2 = chart.DataManipulator.ExportSeriesValues();
}
chart.ImageType = System.Web.UI.DataVisualization.Charting.ChartImageType.Png;
chart.Width = wd;
chart.Height = ht;
//ADD A PLACE HOLDER CHART AREA TO THE CHART
chart.ChartAreas.Add(new ChartArea());
if (x_vartype == "numeric")
{
if (xaxismin != -999)
{
chart.ChartAreas[0].AxisX.Minimum = xaxismin;
}
if (xaxismax != -999)
{
chart.ChartAreas[0].AxisX.Maximum = xaxismax;
}
}
if (yaxismin != -999)
{
chart.ChartAreas[0].AxisY.Minimum = yaxismin;
}
if (yaxismax != -999)
{
chart.ChartAreas[0].AxisY.Maximum = yaxismax;
}
chart.ChartAreas[0].RecalculateAxesScale();
//here not helpgful --> chart.AlignDataPointsByAxisLabel();
chart.ChartAreas["ChartArea1"].AxisX.MajorGrid.LineColor = Color.Gainsboro;
chart.ChartAreas["ChartArea1"].AxisY.MajorGrid.LineColor = Color.Gainsboro;
chart.ChartAreas["ChartArea1"].AxisX.Title = xaxistitle;
chart.ChartAreas["ChartArea1"].AxisY.Title = yaxistitle;
chart.ChartAreas[0].AxisX.LabelStyle.Angle = -90; // Can vary from -90 to 90.
chart.ChartAreas[0].AxisX.LabelStyle.Interval = 1;
//if (YAxisType == "free")
//{
//ADD A PLACE HOLDER LEGEND TO THE CHART
chart.Legends.Add("Default");
chart.Legends["Default"].LegendStyle = LegendStyle.Column; //.Table;
chart.Legends["Default"].TableStyle = LegendTableStyle.Auto;
chart.Legends["Default"].Docking = Docking.Right;
//chart.Legends["Default"].MarkerSize = new Size(15, 15);
chart.CustomizeLegend += new EventHandler <CustomizeLegendEventArgs>(utilMSCharts.chart_CustomizeLegend);
//LegendCellColumnCollection legendcells = chart.Legends["Default"].CellColumns;
//foreach(LegendCellColumn lcc in legendcells)
//{
// lcc.SeriesSymbolSize = new System.Drawing.Size(20,20);
//}
//}
//else if (YAxisType == "fixed")
//{
// //chart.Legends.Clear();
//}
Title t = new Title();
t.Text = charttitle + "\n(created " + System.DateTime.Now.ToShortDateString() + " " + System.DateTime.Now.ToShortTimeString() + ")";
if (dt_rawdata.Rows.Count == 0)
{
t.Text += "\n** 0 records entered **";
}
t.Alignment = ContentAlignment.TopLeft;
t.Font = new System.Drawing.Font("Arial", 12, GraphicsUnit.Point);
chart.Titles.Add(t);
return(chart);
}
catch (Exception ex)
{
string x = ex.Message;
return(null);
}
}
else
{
return(null);
}
}
