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 }