private void Tile_XLS_Click(object sender, EventArgs e)
{
//try
//{
// MessageBox.Show("将选择表格文件保存位置.\r\n" +
// "文件保存后应用程序将启动Excel完成表单填写ヾ(￣▽￣)Bye~Bye~." +
// "\r\n请在填写完成后返回本程序继续操作.(*≧︶≦))(￣▽￣* )ゞ", "Hint");
// var result = saveFileDialog_XLSX.ShowDialog();
// if (!(result == DialogResult.OK || result == DialogResult.Yes))
// {
// MessageBox.Show("操作已取消.＞﹏＜","取消");
// return;
// }
// Log($"XLSX Output Path:{saveFileDialog_XLSX.FileName}");
// File.Copy(Program.DataDir + "TEMPLATE.XLSX", saveFileDialog_XLSX.FileName,true);
// Process.Start(saveFileDialog_XLSX.FileName);
//}
//catch (Exception exception)
//{
// Log(Logger.GetExceptionInfo(exception));
// MessageBox.Show("文件操作失败!\r\n请保证程序有足够的权限在文件系统上操作文件,然后重试","运行时错误",MessageBoxButtons.OK,MessageBoxIcon.Error);
//}
var ew =
new ExcelWindow("填写成绩单 (Hint:可以从原始成绩单中复制)");
Random rd = new Random();
var name = $"Temp\\{rd.Next()}.xlsx";
try
{
Directory.CreateDirectory("Temp");
File.Copy("Data\\tmp.xlsx", name);
}
catch (Exception ce)
{
Log(Logger.GetExceptionInfo(ce));
MessageBox.Show("文件操作失败!\r\n请保证程序有足够的权限在文件系统上操作文件,然后重试", "运行时错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
ew.spreadsheet1.Open(name);
ew.FormClosing += new FormClosingEventHandler((o, b) => { ew.spreadsheet1.Save(); try{ ew.Dispose(); }catch {} });
ew.Show();
}
private void modernButton3_Click(object sender, EventArgs e)
{
Log("%TEST%");
var ew = new ExcelWindow("Test#3");
ew.spreadsheet1.Open("Data\\Test\\c5.xlsx");
ew.ShowDialog();
//ExcelOperator2 ex=new ExcelOperator2(ew.spreadsheet1);
var s = ew.spreadsheet1.ActiveSheet;
var l = s.ExportDataTable(s.UsedRange, ExcelExportDataTableOptions.ColumnNames | ExcelExportDataTableOptions.ComputedFormulaValues);
//MessageBox.Show($"{l.Rows[0][0].ToString()}");
//ScoreTable st=new ScoreTable(new Dictionary<string, ScoreModel>(),new InputDataIndicator() );
List <ScoreModel> li = new List <ScoreModel>();
for (int j = 0; j < l.Rows.Count; j++)
{
ScoreModel sm = new ScoreModel();
for (int i = 0; i < l.Columns.Count; i++)
{
switch (i)
{
case 0:
sm.Name = l.Rows[j][i].ToString();
break;
case 1:
sm.Id = Int32.Parse(l.Rows[j][i].ToString());
break;
case 2:
sm.SumRank = Int32.Parse(l.Rows[j][i].ToString());
break;
case 3:
sm.SumGradeRank = Int32.Parse(l.Rows[j][i].ToString());
break;
case 4:
sm.Zh = Single.Parse(l.Rows[j][i].ToString());
break;
case 5:
sm.ZhRank = Int32.Parse(l.Rows[j][i].ToString());
break;
case 6:
sm.ZhGradeRank = int.Parse(l.Rows[j][i].ToString());
break;
case 7:
sm.M = float.Parse(l.Rows[j][i].ToString());
break;
case 8:
sm.MRank = int.Parse(l.Rows[j][i].ToString());
break;
case 9:
sm.MGradeRank = int.Parse(l.Rows[j][i].ToString());
break;
case 10:
sm.En = float.Parse(l.Rows[j][i].ToString());
break;
case 11:
sm.EnRank = int.Parse(l.Rows[j][i].ToString());
break;
case 12:
sm.EnGradeRank = int.Parse(l.Rows[j][i].ToString());
break;
case 13:
sm.P = float.Parse(l.Rows[j][i].ToString());
break;
case 14:
sm.PRank = int.Parse(l.Rows[j][i].ToString());
break;
case 15:
sm.PGradeRank = int.Parse(l.Rows[j][i].ToString());
break;
case 16:
sm.C = float.Parse(l.Rows[j][i].ToString());
break;
case 17:
sm.CRank = int.Parse(l.Rows[j][i].ToString());
break;
case 18:
sm.CGradeRank = int.Parse(l.Rows[j][i].ToString());
break;
case 19:
sm.Po = float.Parse(l.Rows[j][i].ToString());
break;
case 20:
sm.PoRank = Int32.Parse(l.Rows[j][i].ToString());
break;
case 21:
sm.PoGradeRank = Int32.Parse(l.Rows[j][i].ToString());
break;
case 22:
sm.H = float.Parse(l.Rows[j][i].ToString());
break;
case 23:
sm.HRank = int.Parse(l.Rows[j][i].ToString());
break;
case 24:
sm.HGradeRank = int.Parse(l.Rows[j][i].ToString());
break;
case 25:
sm.G = float.Parse(l.Rows[j][i].ToString());
break;
case 26:
sm.GRank = int.Parse(l.Rows[j][i].ToString());
break;
case 27:
sm.GGradeRank = int.Parse(l.Rows[j][i].ToString());
break;
case 28:
sm.B = float.Parse(l.Rows[j][i].ToString());
break;
case 29:
sm.BRank = int.Parse(l.Rows[j][i].ToString());
break;
case 30:
sm.BGradeRank = int.Parse(l.Rows[j][i].ToString());
break;
default: throw new NotImplementedException();
break;
}
}
li.Add(sm);
//MessageBox.Show($"%C# {name}%=>%JSON%:\r\n{sm.ToString()}");
}
IList <(NfSubjects, float)> aveList = Analyzer.Average(li);
//BindingList<(NfSubjects, float)> aveBindingList = Analyzer.Average(li);//todo;
IList <(NfSubjects, float)> sumList = Analyzer.Sum(li);
IList <(NfSubjects, float[])> modeList = Analyzer.Mode(li);
IList <(NfSubjects, double)> midList = Analyzer.Mid(li);
Log("Average:\r\n" + string.Join(",", aveList));
Log("Sum:\r\n" + string.Join(",", sumList));
Log("Mode:");
foreach (var mode in modeList)
{
Log($"{mode.Item1.ToString()}:{string.Join(",",mode.Item2)}");
}
//Log("Mode:\r\n" + string.Join(",",modeList));
Log("Mid:\r\n" + string.Join(",", midList));
//MessageBox.Show(aveList[0].Item2.ToString());
DataVisualization dv = new DataVisualization();
dv.lDChartDataSourceBindingSource.DataSource = new LDChartDataSource(aveList);
#region Data Convert:
ChartSeries cs = new ChartSeries("%Title%");
ChartDataBindModel cdbm = new ChartDataBindModel(aveList);
ChartDataBindAxisLabelModel cdbalm = new ChartDataBindAxisLabelModel(aveList);
cdbm.XName = "Item1";
cdbalm.LabelName = "Item1";
cdbm.YNames = new String[] { "Item2" };
cs.SeriesModel = cdbm;
dv.chartControl1.Series.Add(cs);
dv.chartControl1.PrimaryXAxis.LabelsImpl = cdbalm;
dv.chartControl2.Series.Add(cs);
dv.chartControl2.PrimaryXAxis.LabelsImpl = cdbalm;
dv.chartControl2.PrimaryYAxis.Range = new MinMaxInfo(0, 150, 100);
#endregion
dv.Show();
//#region PPT Output
//IPresentation ip = Presentation.Create();
//IList<double> avelist_ppt=new List<double>();
//foreach (var ii in aveList)
//{
// avelist_ppt.Add(ii.Item2);
//}
//ISlide sl = ip.Slides.Add(SlideLayoutType.Blank);
//IPresentationChart ct = sl.Shapes.AddChart(avelist_ppt, 5, 5, 100, 100);
//Spire.Presentation.Presentation ppt =new Spire.Presentation.Presentation(File.Open($"{new Random().Next()}.pptx",FileMode.Create,FileAccess.ReadWrite),FileFormat.Pptx2010);
//var cts = ppt.Slides[0].Shapes.AppendChart(ChartType.Column3D,new RectangleF(5,5,200,200));
//foreach (var ave in avelist_ppt)
//{
// //cts.Series.Append(ave);
// cts.Series[0].Values.Add(ave);
//}
//ip.Save($"{new Random().Next()}.pptx");
//#endregion
#region ExcelChart
ExcelEngine ee = new ExcelEngine();
IApplication xls = ee.Excel;
xls.DefaultVersion = Syncfusion.XlsIO.ExcelVersion.Excel2016;
IWorkbook wb = xls.Workbooks.Open("Data\\Test\\c5.xlsx", ExcelOpenType.Automatic);
IWorksheet ws = wb.Worksheets.Create("Chart");
IChartShape chart = ws.Charts.Add();
/////////////////////////////////////////////////////
//IChartSerie serie = chart.Series.Add(Syncfusion.XlsIO.ExcelChartType.Column_Clustered);
//chart.ChartType = Syncfusion.XlsIO.ExcelChartType.Column_Clustered;
IList <string> xave = new List <string>();
IList <object> yave = new List <object>();
int allavei = Int32.MaxValue, allmidi = Int32.MaxValue;
for (var index = 0; index < aveList.Count; index++)
{
if (aveList[index].Item1 == NfSubjects.All)
{
allavei = index;
}
var ave = aveList[index];
xave.Add(ave.Item1.Name());
yave.Add(ave.Item2);
}
//serie.EnteredDirectlyValues = yave.ToArray();
//serie.Name = "平均分";
//serie.EnteredDirectlyCategoryLabels = xave.ToArray();
ChartGen.GenChart(chart, xave.ToArray(), yave.ToArray(), "平均分", ExcelChartType.Column_Clustered);
////////////////////////////////////////////////
//IChartSerie seriemid = chart.Series.Add(Syncfusion.XlsIO.ExcelChartType.Column_Clustered);
//chart.ChartType = Syncfusion.XlsIO.ExcelChartType.Column_Clustered;
IList <string> xmid = new List <string>();
IList <object> ymid = new List <object>();
for (var index = 0; index < midList.Count; index++)
{
if (midList[index].Item1 == NfSubjects.All)
{
allmidi = index;
}
var mid = midList[index];
xmid.Add(mid.Item1.Name());
ymid.Add(mid.Item2);
}
ChartGen.GenChart(chart, xmid.ToArray(), ymid.ToArray(), "中位分", ExcelChartType.Column_Clustered);
//{{{{{{{{{{{{{{
xave.RemoveAt(allavei);
yave.RemoveAt(allavei);
xmid.RemoveAt(allmidi);
ymid.RemoveAt(allmidi);
IChartShape leida = ws.Shapes.AddChart();
ChartGen.GenChart(leida, xave.ToArray(), yave.ToArray(), "平均分", ExcelChartType.Radar);
ChartGen.GenChart(leida, xmid.ToArray(), ymid.ToArray(), "中位分", ExcelChartType.Radar);
leida.Name = "学科成绩分布1";
leida.ChartTitle = "学科成绩分布1";
//}}}}}}}}}}}}}}
//{{{{{{{{{{{{{{
var xyave = Analyzer.ReArrangeData(aveList);
var xymid = Analyzer.ReArrangeData(midList);
IChartShape leida2 = ws.Shapes.AddChart();
ChartGen.GenChart(leida2, xyave.Item1, xyave.Item2, "平均分", ExcelChartType.Radar);
ChartGen.GenChart(leida2, xymid.Item1, xymid.Item2, "中位分", ExcelChartType.Radar);
leida2.Name = "学科成绩分布2";
leida2.ChartTitle = "学科成绩分布2";
//}}}}}}}}}}}}}}
var ordered = li.OrderBy(pp => pp.Sum);
ChartGen.GenChart(ws.Shapes.AddChart(), ordered.Select(sm => (object)(sm.Name)).ToArray(), ordered.Select(sm => (object)(sm.Sum ?? 0)).ToArray(), "总分", ExcelChartType.Column_3D);
//seriemid.EnteredDirectlyValues = ymid.ToArray();
//seriemid.Name = "中位分";
//seriemid.EnteredDirectlyCategoryLabels = xmid.ToArray();
/////////////////////////////////////////////////
xls.Save("output.xlsx");
wb.Close();
ee.Dispose();
#endregion
}
