private static void CreateBoxCart(Excel.Worksheet targetSheet, Excel.Range xValues, String kwartyl1Desc, Excel.Range kwartyl1Values, String medianaDesc, Excel.Range medianaValues, String kwartyl3Desc, Excel.Range kwartyl3Values, Excel.Range ErrorMaksimum, Excel.Range ErrorMadiana, Excel.Range ErrorMinimum) { Excel.ChartObjects xlCharts = (Excel.ChartObjects)targetSheet.ChartObjects(Type.Missing); Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 100, 300, 250); Excel.Chart chartPage = myChart.Chart; chartPage.HasLegend = false; chartPage.HasTitle = true; SeriesCollection seriesCollection = chartPage.SeriesCollection(); Series series1 = seriesCollection.NewSeries(); series1.Name = kwartyl1Desc; series1.XValues = xValues; series1.Values = kwartyl1Values; series1.HasErrorBars = true; series1.ErrorBar(XlErrorBarDirection.xlY, XlErrorBarInclude.xlErrorBarIncludeMinusValues, XlErrorBarType.xlErrorBarTypeCustom, ErrorMinimum, ErrorMinimum); series1.Format.Fill.Visible = Microsoft.Office.Core.MsoTriState.msoFalse; series1 = seriesCollection.NewSeries(); series1.Name = medianaDesc; series1.XValues = xValues; series1.Values = medianaValues; //series1.Format.Line.Parent series1.Format.Line.Weight = 2.0F; series1.Format.Line.Visible = Microsoft.Office.Core.MsoTriState.msoTriStateMixed; //Tri-State series1.Format.Line.ForeColor.RGB = (int)Microsoft.Office.Interop.Excel.XlRgbColor.rgbBlack; //series1.Format.Line.ForeColor.RGB = (int)XlRgbColor.rgbBlack; //series1.Format.Line.Weight = 5; series1 = seriesCollection.NewSeries(); series1.Name = kwartyl3Desc; series1.XValues = xValues; series1.Values = kwartyl3Values; series1.Format.Line.Weight = 2.0F; series1.Format.Line.Visible = Microsoft.Office.Core.MsoTriState.msoTriStateMixed; //Tri-State series1.Format.Line.ForeColor.RGB = (int)Microsoft.Office.Interop.Excel.XlRgbColor.rgbBlack; series1.HasErrorBars = true; series1.ErrorBar(XlErrorBarDirection.xlY, XlErrorBarInclude.xlErrorBarIncludePlusValues, XlErrorBarType.xlErrorBarTypeCustom, ErrorMaksimum, ErrorMaksimum); chartPage.ChartType = Excel.XlChartType.xlColumnStacked; }
private void EditChart(Excel.Chart chart, int index) { chart.ChartType = Excel.XlChartType.xlColumnClustered; var range1 = shAuswertung1.Range[shAuswertung1.Cells[1, 1], shAuswertung1.Cells[index, 1]]; var range2 = shAuswertung1.Range[shAuswertung1.Cells[1, 5], shAuswertung1.Cells[index, 5]]; var range3 = shAuswertung1.Range[shAuswertung1.Cells[1, 9], shAuswertung1.Cells[index, 9]]; var range4 = shAuswertung1.Range[shAuswertung1.Cells[1, 10], shAuswertung1.Cells[index, 10]]; chart.SetSourceData(Source: Globals.ThisAddIn.Application.Union(range1, range2, range3, range4)); chart.Legend.Position = Excel.XlLegendPosition.xlLegendPositionBottom; chart.Legend.Font.Size = 8; chart.Legend.Font.Name = "Arial"; chart.SeriesCollection(1).Format.Fill.ForeColor.RGB = Color.FromArgb(192, 0, 0); chart.SeriesCollection(2).Format.Fill.ForeColor.RGB = Color.FromArgb(0, 112, 192); chart.SeriesCollection(3).Format.Fill.ForeColor.RGB = Color.FromArgb(255, 140, 0); chart.Axes(Excel.XlAxisType.xlCategory).CategoryType = Excel.XlCategoryType.xlCategoryScale; chart.Axes(Excel.XlAxisType.xlCategory).TickLabels.Font.Size = 8; chart.Axes(Excel.XlAxisType.xlCategory).TickLabels.Font.Name = "Arial"; chart.Axes(Excel.XlAxisType.xlCategory).TickLabels.Orientation = 90; chart.Axes(Excel.XlAxisType.xlValue).TickLabels.Font.Size = 8; chart.Axes(Excel.XlAxisType.xlValue).TickLabels.Font.Name = "Arial"; chart.Axes(Excel.XlAxisType.xlValue).MinimumScale = 0; }
private void button1_Click(object sender, EventArgs e) { Excel.Application xlApp = new Excel.Application(); Excel.Workbook wb = xlApp.Workbooks.Add(Missing.Value); Excel.Worksheet ws = wb.ActiveSheet; for (int i = 0; i < dataGridView1.Rows.Count - 1; i++) { ws.Cells[1, i + 2].Value = dataGridView1.Rows[i].Cells[0].Value.ToString(); } ws.Cells[2, 1].Value = "Amenities Sold"; ws.Cells[3, 1].Value = "Tickets Sold"; ws.Cells[4, 1].Value = "Commission Earned"; for (int i = 0; i < dataGridView1.Rows.Count - 1; i++) { ws.Cells[2, i + 2].Value = dataGridView1.Rows[i].Cells[1].Value.ToString(); ws.Cells[3, i + 2].Value = dataGridView1.Rows[i].Cells[2].Value.ToString(); ws.Cells[4, i + 2].Value = dataGridView1.Rows[i].Cells[3].Value.ToString(); } Excel.ChartObjects xlObj = ws.ChartObjects(Missing.Value); Excel.ChartObject obj = xlObj.Add(10, 50, 450, 250); Excel.Chart chart = obj.Chart; Excel.Series s = chart.SeriesCollection().Add(ws.Range[ws.Cells[1, 1], ws.Cells[4, dataGridView1.Rows.Count]]); //s.XValues = ws.Range[ws.Cells[1, 2], ws.Cells[1, dataGridView1.Rows.Count - 1]]; //Excel.Range rangeSourceChart = ws.Range[ws.Cells[1, 1], ws.Cells[4, dataGridView1.Rows.Count - 1]]; //chart.SetSourceData(rangeSourceChart, Excel.XlRowCol.xlRows); using (SaveFileDialog sf = new SaveFileDialog()) { sf.Filter = "Excel Files|*.xlsx"; sf.FileName = "CommissionReport"; if (sf.ShowDialog() == DialogResult.OK) { wb.SaveAs(sf.FileName); wb.Close(); xlApp.Quit(); } } }
private void DrawChart_FeedbackVsQuarter_per_FeedbackCategory_individualDepartment(Department department, int startYear, bool isPercentage = false) { Excel.Chart chart = (startYear == reportDate.Year) ? InitialiseChart($"Feedback category in {reportDate.Year}-Q{reportDateQuarter} for {department.Name}{(isPercentage ? " (percentage)" : "")}", isPercentage) : InitialiseChart($"Feedback category trend since {startYear} for {department.Name}{(isPercentage ? " (percentage)" : "")}", isPercentage); var data = new Dictionary <string, IList <Feedback> >(); for (int year = startYear; year <= reportDate.Year; year++) { for (int quarter = 1; quarter <= 4; quarter++) { data.Add($"{year}-Q{quarter}", feedbacks.Where(f => f.ResponsibleDepartment == department.Name && f.DateReceived.Year == year && ((f.DateReceived.Month + 2) / 3) == quarter).ToList()); if (year == reportDate.Year && quarter == reportDateQuarter) { break; } } } var seriesCollection = (Excel.SeriesCollection)chart.SeriesCollection(); foreach (var category in department.Categories) { var series = seriesCollection.NewSeries(); series.Name = category; series.XValues = data.Keys.ToArray(); if (isPercentage) { series.Values = data.Values.Select(v => GetRatio(v.Where(f => f.Category == category).Count(), v.Count)).ToArray(); } else { series.Values = data.Values.Select(v => v.Where(f => f.Category == category).Count()).ToArray(); } series.ApplyDataLabels(); if (isPercentage) { series.DataLabels().NumberFormat = "0%"; } } ExportChart(chart); }
/// <summary> /// Обновление Диаграммы /// </summary> internal void UpdateDiagramm() { Excel.ChartObject shp = SheetUrv11.ChartObjects("Chart 2"); Excel.Chart chartPage = shp.Chart; Excel.SeriesCollection seriesCol = (Excel.SeriesCollection)chartPage.SeriesCollection(); Excel.FullSeriesCollection fullColl = chartPage.FullSeriesCollection(); Debug.WriteLine(fullColl.Count); int lastCol = GetLastColumnUrv(SheetUrv11, _rowStart); int lastRow = GetLastRowUrv11(); int ix = 1; string letterCost = "G"; fullColl.Item(ix).Name = $"={SheetUrv11.Name}!${letterCost}10"; fullColl.Item(ix).Values = $"={SheetUrv11.Name}!${letterCost}{_rowStart}:${letterCost}{lastRow}"; fullColl.Item(ix).XValues = $"={SheetUrv11.Name}!$C{_rowStart}:$C{lastRow}"; for (int col = 9; col <= lastCol; col += 3) { Excel.Range cellFirstCost = SheetUrv11.Cells[_rowStart, col]; string text = cellFirstCost.Value?.ToString() ?? ""; if (string.IsNullOrEmpty(text)) { continue; } letterCost = ExcelHelper.GetColumnLetter(cellFirstCost); ix++; if (ix > fullColl.Count) { seriesCol.NewSeries(); } fullColl.Item(ix).Name = $"={SheetUrv11.Name}!${letterCost}10"; fullColl.Item(ix).Values = $"={SheetUrv11.Name}!${letterCost}{_rowStart}:${letterCost}{lastRow}"; fullColl.Item(ix).XValues = $"={SheetUrv11.Name}!$C{_rowStart}:$C{lastRow}"; } if (ix < fullColl.Count) { for (int i = ix + 1; i <= fullColl.Count; i++) { fullColl.Item(i).Delete(); } } }
public void AddInfo(FunctionTableItem[] fun, ValueIs value = ValueIs.Magnitude, bool addChart = false) { Excel.Series series; Excel.Range temp; range = range.get_Resize(2, fun.Length); range.set_Value(Missing.Value, GetArray(fun, value)); if (addChart) { LastIndex += chartHeight + 10; chartObject = charts.Add(400, LastIndex, chartWidth, chartHeight); } currentChart = chartObject.Chart; series = ((Excel.SeriesCollection)currentChart.SeriesCollection(Type.Missing)).NewSeries(); temp = range.get_Resize(1, fun.Length); series.Values = temp; series.XValues = temp.get_Offset(1, 0); currentChart.ChartType = Excel.XlChartType.xlXYScatterSmoothNoMarkers; range = range.get_Offset(3, 0); }
private static void SetChartData(Excel.Chart chart, Dictionary <string, IList <Feedback> > data, IEnumerable <string> feedbackNatures, bool isPercentage) { var seriesCollection = (Excel.SeriesCollection)chart.SeriesCollection(); foreach (var feedbackNature in feedbackNatures) { var series = seriesCollection.NewSeries(); series.Name = feedbackNature; series.XValues = data.Keys.ToArray(); if (feedbackNature == FeedbackNature.TotalFeedbacks) { if (isPercentage) { series.Values = data.Values.Select(v => GetRatio(v.Count, v.Count)).ToArray(); } else { series.Values = data.Values.Select(v => v.Count()).ToArray(); } } else { if (isPercentage) { series.Values = data.Values.Select(v => GetRatio(v.Where(f => f.FeedbackNature == feedbackNature).Count(), v.Count)).ToArray(); } else { series.Values = data.Values.Select(v => v.Where(f => f.FeedbackNature == feedbackNature).Count()).ToArray(); } } series.ApplyDataLabels(); if (isPercentage) { (series.DataLabels()).NumberFormat = "0%"; } } }
public void AddInfo(FunctionTableItem[] fun, ValueIs value = ValueIs.Magnitude, bool addChart = false) { Excel.Series series; Excel.Range temp; range = range.get_Resize(2, fun.Length); range.set_Value(Missing.Value, GetArray(fun, value)); if (addChart) { LastIndex += chartHeight + 10; chartObject = charts.Add(400, LastIndex, chartWidth, chartHeight); } currentChart = chartObject.Chart; series = ((Excel.SeriesCollection) currentChart.SeriesCollection(Type.Missing)).NewSeries(); temp = range.get_Resize(1, fun.Length); series.Values = temp; series.XValues = temp.get_Offset(1, 0); currentChart.ChartType = Excel.XlChartType.xlXYScatterSmoothNoMarkers; range = range.get_Offset(3, 0); }
/// <summary> /// Initialize Excel Application /// </summary> /// <param name="sheetname">Desired Sheetname</param> /// <param name="visible">Determine whether Excel-Sheet is visible or not</param> public static void InitializeExcel(string sheetname, bool visible = true) { // Create a new Excel Application _app = new Excel.Application(); _app.Visible = visible; // Create a new, empty workbook and add it to the collection returned _workbook = _app.Workbooks.Add(Missing.Value); _workbook.Worksheets.Item[1].Name = sheetname; // Initialize _mainChart. var worksheet = _workbook.Worksheets.Item[1] as Microsoft.Office.Interop.Excel.Worksheet; var charts = worksheet.ChartObjects() as Microsoft.Office.Interop.Excel.ChartObjects; var chartObject = charts.Add(10, 10, 800, 450) as Microsoft.Office.Interop.Excel.ChartObject; _mainChart = chartObject.Chart; _mainChart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlXYScatterLinesNoMarkers; _mainChart.ChartWizard(Title: sheetname); _mainChartSeriesCollection = _mainChart.SeriesCollection(); }
private void GerarGraficoExcel(String c1, String c2, int tamanho, double left, double top, double width, double height) { // Cria um Chart(Gráfico) Ex.ChartObjects cb = (Ex.ChartObjects)oSheetChart.ChartObjects(Type.Missing); Ex.ChartObject cbc = (Ex.ChartObject)cb.Add(left, top, width, height); Ex.Chart cp = cbc.Chart; Ex.Range valores = oSheet.get_Range(c1, c2); // Seta o título do gráfico cp.HasTitle = true; cp.ChartTitle.Text = "MÉDIA DE TEMPO EM VETORES DE TAMANHO " + tamanho + " EM " + (tamanho <= 1000 ? "NANO" : "MILLI"); cp.ChartTitle.Font.Name = "Arial"; // Seta os nomes das colunas Ex.SeriesCollection seriesCollection = cp.SeriesCollection(); Ex.Series series = seriesCollection.NewSeries(); series.Values = valores; series.XValues = oSheet.get_Range("B1", "J1"); // Oculta a legenda da serie cp.Legend.Clear(); }
/// <summary> /// Рисует график public void drawGraph(string filename, int numSheets, string firstCell, string lastCell /*, double[] approxY*/) { ObjWorkBooks = ObjWorkExcel.Workbooks; ObjSheets = ObjWorkBook.Worksheets; Excel.ChartObjects chartsobjrcts = (Excel.ChartObjects)ObjWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject chartsobjrct = chartsobjrcts.Add(1, 360, 400, 250); Excel.Chart chart = chartsobjrct.Chart; Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chart.SeriesCollection(Type.Missing); Excel.Series series = seriesCollection.NewSeries(); Excel.Trendlines trendlines = (Excel.Trendlines)series.Trendlines(System.Type.Missing); Excel.Trendline newTrendline = trendlines.Add(Excel.XlTrendlineType.xlPower, 2, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true, true, Type.Missing); newTrendline.Select(); chart.Legend.Delete(); series.XValues = ObjWorkSheet.get_Range("E7", "E26"); series.Values = ObjWorkSheet.get_Range("D7", "D26"); chart.ChartType = Excel.XlChartType.xlXYScatter; }
/// <summary> /// Method fills the sheet with calculation results /// </summary> /// <param name="worksheet">Worksheet to save the results there</param> /// <param name="calculationTypeName">CalculationType</param> /// <param name="result">Container with result variables</param> /// <param name="allVariables">Cantainer with variables values for each time step</param> /// <param name="calcTime">Time reqiured for calculation</param> private static void SetCalculationResults(Excel.Worksheet worksheet, CalculationTypeName calculationTypeName, List <Variable> leftVariables, List <DEVariable> result, List <List <DEVariable> > allVariables, double calcTime) { worksheet.Name = calculationTypeName.ToString(); int rowIndex = 1; int columnIndex = 1; for (int i = 0; i < result.Count; i++) { worksheet.Cells[rowIndex, columnIndex] = $"{result[i].Name} = "; worksheet.Cells[rowIndex, columnIndex + 1] = result[i].Value; rowIndex++; } rowIndex++; worksheet.Cells[rowIndex, columnIndex] = "Calculation time = "; worksheet.Cells[rowIndex, columnIndex + 1] = calcTime; rowIndex++; if (allVariables != null) { rowIndex++; worksheet.Cells[rowIndex, columnIndex] = "Detailed results"; rowIndex++; for (int i = 0; i < allVariables[0].Count; i++) { worksheet.Cells[rowIndex, columnIndex + i] = allVariables[0][i].Name; } rowIndex++; string leftTopCellforChart = GetExcelColumnName(columnIndex) + rowIndex.ToString(); string horinzontalAxisTop = GetExcelColumnName(columnIndex + allVariables[0].Count - 1) + rowIndex.ToString(); int idxStart = rowIndex; for (int i = 0; i < allVariables.Count; i++) { for (int j = 0; j < allVariables[0].Count; j++) { worksheet.Cells[rowIndex, columnIndex + j] = allVariables[i][j].Value; } rowIndex++; } string rightDownCellForChart = GetExcelColumnName(columnIndex + allVariables[0].Count - 2) + (allVariables.Count - 1 + idxStart).ToString(); string horizontalAlignmentDown = GetExcelColumnName(columnIndex + allVariables[0].Count - 1) + (allVariables.Count - 1 + idxStart).ToString(); Excel.Range chartRange; Excel.Range horizontalAlignmentRange; Excel.ChartObjects xlCharts = (Excel.ChartObjects)worksheet.ChartObjects(Type.Missing); Excel.ChartObject mychart = xlCharts.Add(10, 80, 500, 450); Excel.Chart chartPage = mychart.Chart; chartRange = worksheet.get_Range(leftTopCellforChart, rightDownCellForChart); chartPage.SetSourceData(chartRange); chartPage.ChartType = Excel.XlChartType.xlLine; horizontalAlignmentRange = worksheet.get_Range(horinzontalAxisTop, horizontalAlignmentDown); chartPage.SeriesCollection(1).XValues = horizontalAlignmentRange; for (int i = 0; i < leftVariables.Count; i++) { chartPage.SeriesCollection(i + 1).Name = leftVariables[i].Name; } } }
public void CreateExcelGraph_AnimalsCountPerGroup(List <Model.AnimalCountPerGroup> Data) { try { Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Excel.Application(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); //add data // TODO xlWorkSheet.Cells[1, 1] = ""; xlWorkSheet.Cells[2, 1] = "Count"; int col = 2, row = 2; foreach (Model.AnimalCountPerGroup animalGroup in Data) { xlWorkSheet.Cells[1, col] = "Group " + animalGroup.Group_ID; xlWorkSheet.Cells[row, col++] = "" + animalGroup.Count; } Excel.Range chartRange; Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250); Excel.Chart chartPage = myChart.Chart; chartRange = xlWorkSheet.get_Range("A1", (char)('A' + (col - 2)) + "2"); chartPage.SetSourceData(chartRange, misValue); chartPage.ChartType = Excel.XlChartType.xl3DPie; chartPage.ChartTitle.Caption = "Animal Count Per Group"; Excel.Series series1 = (Excel.Series)chartPage.SeriesCollection(1); series1.HasDataLabels = true; string filename = "csharp.net-" + System.DateTime.Now.Ticks + ".xls"; xlWorkBook.SaveAs(filename, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); string filepath = xlWorkBook.Path + "\\" + filename; Console.WriteLine("Excel saved at " + xlWorkBook.Path); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); FileInfo fi = new FileInfo(filepath); if (fi.Exists) { System.Diagnostics.Process.Start(filepath); } else { //file doesn't exist } } catch (Exception e) { } }
public void click_importData(Office.IRibbonControl control) { // Initialize Open File Dialog OpenFileDialog dialog_openFile = new OpenFileDialog(); dialog_openFile.Filter = "Excel Files (*.xls;*.xlsx; *.xlsm; *.csv)| *.xls; *.csv; *.xlsx; *.xlsm|Unicode Text|*.txt"; dialog_openFile.Title = "Select Files To Import..."; dialog_openFile.Multiselect = true; // Set Active Elements Excel.Workbook workbook_active = GetActiveWorkbook(); // Set Default Directory SetDefaultDirectory(dialog_openFile, workbook_active); // Show Open File Dialog DialogResult result_openFile = dialog_openFile.ShowDialog(); if (result_openFile == System.Windows.Forms.DialogResult.OK) { DataSet dataset_import = new DataSet(); foreach (String fileName in dialog_openFile.FileNames) { try { DataTable datatable_temp = new DataTable(Path.GetFileNameWithoutExtension(fileName)); string ext = Path.GetExtension(fileName); using (TextFieldParser parser = new TextFieldParser(@fileName)) { parser.TextFieldType = FieldType.Delimited; string delimeter = ConfigurationManager.AppSettings[ext + "_delimeter"]; parser.SetDelimiters(delimeter); parser.HasFieldsEnclosedInQuotes = Convert.ToBoolean(ConfigurationManager.AppSettings[ext + "_quotes"]); if (ext == ".csv") { string str_checkName = ConfigurationManager.AppSettings[ext + "_name"]; string str_checkValue = ConfigurationManager.AppSettings[ext + "_value"]; string str_checkTitle = ConfigurationManager.AppSettings[ext + "_title"]; while (!parser.EndOfData) { // TODO: DO NOT HARDCODE FIRST VAL and TYPEOF VALUES string[] fields_temp = parser.ReadFields(); if (fields_temp[0] == str_checkName) { for (int i = 1; i < fields_temp.Length; i++) { datatable_temp.Columns.Add(new DataColumn(fields_temp[i], typeof(double))); } } else if (fields_temp[0] == str_checkValue) { double[] values_temp = Array.ConvertAll <string, double>(SubArray <string>(fields_temp, 1, fields_temp.Length - 1), Convert.ToDouble); if (values_temp.Length != datatable_temp.Columns.Count) { throw new Exception("Column mismatch."); } DataRow datarow_temp = datatable_temp.NewRow(); for (int i = 0; i < values_temp.Length; i++) { datarow_temp[i] = values_temp[i]; } datatable_temp.Rows.Add(datarow_temp); } else if (fields_temp[1] == str_checkTitle) { datatable_temp.TableName = fields_temp[2]; } } } } // End of 'using' dataset_import.Tables.Add(datatable_temp); } catch (Exception IE) { MessageBox.Show(IE.Message + " error: could not load file"); continue; } } Excel.Worksheet worksheet_new = CreateNewWorksheet(); string[] arr_names = new string[dataset_import.Tables.Count + 1]; DataTable voltage_table = dataset_import.Tables[0]; arr_names[0] = "Vgs"; double[] voltage_values = voltage_table.AsEnumerable().Select(r => r.Field <double>("Vgs")).ToArray(); for (int v = 0; v < voltage_table.Rows.Count; v++) { worksheet_new.Cells[v + 2, 1] = voltage_values[v]; } for (int i = 0; i < dataset_import.Tables.Count; i++) { DataTable datatable_temp = dataset_import.Tables[i]; arr_names[i + 1] = "Id of " + datatable_temp.TableName; double[] values_fromTable = datatable_temp.AsEnumerable().Select(r => r.Field <double>("Id")).ToArray(); for (int j = 0; j < datatable_temp.Rows.Count; j++) { worksheet_new.Cells[j + 2, i + 2] = -1 * values_fromTable[j]; } } for (int h = 0; h < arr_names.Length; h++) { worksheet_new.Cells[1, h + 1] = arr_names[h]; } object misValue = System.Reflection.Missing.Value; Excel.Range range_chart; Excel.ChartObjects xlCharts = (Excel.ChartObjects)worksheet_new.ChartObjects(Type.Missing); Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(50, 150, 500, 250); Excel.Chart chartPage = myChart.Chart; range_chart = worksheet_new.UsedRange; Excel.Range range_tempA = range_chart.Columns[1].Find("-40"); range_chart = range_chart.Resize[range_tempA.Row]; Excel.Range range_tempB = worksheet_new.Cells[range_chart.Columns[1].Find("-20").Row, 2]; Excel.Range c2 = worksheet_new.Cells[range_chart.Rows.Count, range_chart.Columns.Count]; Excel.Range range_trendline = (Excel.Range)worksheet_new.get_Range(range_tempB, c2); range_chart.Cells[1, 1].Value = ""; chartPage.SetSourceData(range_chart, misValue); //chartPage.SetSourceData(range_trendline, misValue); chartPage.ChartType = Excel.XlChartType.xlXYScatter; foreach (Excel.Series series in chartPage.SeriesCollection()) { Excel.Trendline trendline = series.Trendlines().Add(Excel.XlTrendlineType.xlLinear, System.Type.Missing, System.Type.Missing, 20, System.Type.Missing, System.Type.Missing, true, true, System.Type.Missing); Excel.DataLabel datalabel_temp = trendline.DataLabel; datalabel_temp.NumberFormat = "0.0000E+00"; } Excel.Range a1 = range_chart.Columns[1].Find("-20"); Excel.Range a2 = worksheet_new.Cells[range_chart.Rows.Count, 1]; Excel.Range a3 = (Excel.Range)worksheet_new.get_Range(a1, a2); for (int i = 1; i <= range_trendline.Columns.Count; i++) { Excel.Series series_temp = chartPage.SeriesCollection().Add(range_trendline.Columns[i]); series_temp.XValues = a3; Excel.Trendlines trendlines_temp = series_temp.Trendlines(); Excel.Trendline trendline_temp = trendlines_temp.Add(Excel.XlTrendlineType.xlLinear, System.Type.Missing, System.Type.Missing, 20, System.Type.Missing, System.Type.Missing, true, true, System.Type.Missing); Excel.DataLabel datalabel_temp = trendline_temp.DataLabel; datalabel_temp.NumberFormat = "0.0000E+00"; } } }
public void click_analyzeChart(Office.IRibbonControl control) { Excel.Chart chart_active = GetActiveChart(); List <double> x_intercepts = new List <double>(); foreach (Excel.Series series in chart_active.SeriesCollection()) { Excel.Trendlines trendlines_temp = series.Trendlines(); if (trendlines_temp.Count > 0) { Excel.DataLabel dlabel_temp = trendlines_temp.Item(1).DataLabel; string label = dlabel_temp.Text; int x = label.IndexOf("R²"); int y = label.IndexOf("y"); string equation = label.Substring(y, x); string r_val = label.Substring(x); Regex pattern = new Regex(@"[\d]*\.?[\d]+(E[-+][\d]+)?"); Match match = pattern.Match(r_val); double r_squared = Double.Parse(match.Value, System.Globalization.NumberStyles.Float); if (r_squared < 0.9) { trendlines_temp.Item(1).Delete(); if (r_squared < 0.7) { series.Delete(); } continue; } Regex pattern_x = new Regex(@"[\d]*\.?[\d]+(E[-+][\d]+)?"); MatchCollection matches = pattern_x.Matches(equation); Stack <double> eq_vals = new Stack <double>(); foreach (Match match_temp in matches) { double val = Double.Parse(match_temp.Value, System.Globalization.NumberStyles.Float); eq_vals.Push(val); } double y_int = eq_vals.Pop(); double y_slope = eq_vals.Pop(); if (y_slope < 1e-8) { trendlines_temp.Item(1).Delete(); series.Delete(); continue; } double x_int = (-1 * y_int) / y_slope; x_intercepts.Add(x_int); } } if (x_intercepts.Count > 0) { MessageBox.Show(x_intercepts.Average().ToString()); } }
private Excel.Chart SetStyleXlsChart(Excel.Chart xlChart) { Excel.SeriesCollection sc = xlChart.SeriesCollection(); Excel.Series xlSeries; for (int i = 0; i < sc.Count; i++) { xlSeries = sc.Item(i + 1); xlSeries.ChartType = GetSeriesChartType(chart.Series[i]); if (chart.Series[i].ChartType == SeriesChartType.Bar || chart.Series[i].ChartType == SeriesChartType.Column) { for (int j = 1; j <= chart.Series[i].Points.Count; j++) { xlSeries.Points(j).Interior.Color = ColorTranslator.ToOle(chart.Series[i].Color); } } else { xlSeries.Border.Color = chart.Series[i].Color; } xlSeries.Border.LineStyle = GetLineDashStyle(chart.Series[i]); xlSeries.Border.Weight = chart.Series[i].BorderWidth; xlSeries.MarkerStyle = GetSeriesMarkerStyle(chart.Series[i]); xlSeries.Name = chart.Series[i].Name; progress++; } if (!string.IsNullOrWhiteSpace(chart.Titles[0].Text)) { xlChart.HasTitle = true; xlChart.ChartTitle.Text = chart.Titles[0].Text; } else { xlChart.HasTitle = false; } progress++; xlChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).HasTitle = true; xlChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).AxisTitle.Text = chart.ChartAreas[0].AxisX.Title; xlChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).HasMajorGridlines = chart.ChartAreas[0].AxisX.MajorGrid.Enabled; xlChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).HasMinorGridlines = chart.ChartAreas[0].AxisX.MinorGrid.Enabled; if (xlChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).HasMajorGridlines) { xlChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).MajorGridlines.Border.LineStyle = GetLineDashStyle(chart.ChartAreas[0].AxisX.MajorGrid); } if (xlChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).HasMinorGridlines) { xlChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).MinorGridlines.Border.LineStyle = GetLineDashStyle(chart.ChartAreas[0].AxisX.MinorGrid); } progress += 4; xlChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).HasTitle = true; xlChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).AxisTitle.Text = chart.ChartAreas[0].AxisY.Title; xlChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).HasMajorGridlines = chart.ChartAreas[0].AxisY.MajorGrid.Enabled; xlChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).HasMinorGridlines = chart.ChartAreas[0].AxisY.MinorGrid.Enabled; if (xlChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).HasMajorGridlines) { xlChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).MajorGridlines.Border.LineStyle = GetLineDashStyle(chart.ChartAreas[0].AxisY.MajorGrid); } if (xlChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).HasMinorGridlines) { xlChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).MinorGridlines.Border.LineStyle = GetLineDashStyle(chart.ChartAreas[0].AxisY.MinorGrid); } progress += 4; xlChart.HasLegend = true; progress++; return(xlChart); }
//формируем отчет в ексель по методу поспелова private void Client_GetListExpertiseReportPospelovCompleted(object sender, ServiceReference1.GetListExpertiseReportPospelovCompletedEventArgs e) { if (e.Error == null) { Excel.Application xlApp = new Excel.Application(); if (xlApp == null) { MessageBox.Show("Excel is not properly installed!!"); return; } Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet = null; object misValue = System.Reflection.Missing.Value; xlWorkBook = xlApp.Workbooks.Add(); var experts = e.Result.list_marks_factors.GroupBy(o => o.id_expert).ToList(); for (int i = 0; i < experts.Count(); i++) { int id_expert = experts[i].Key; if (i == 0) { xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); xlWorkSheet.Name = "Отчет эксперта " + e.Result.list_marks_factors.Where(o => o.id_expert == id_expert).FirstOrDefault().experts.first_name; //e.Result.list_marks.Where(o => o.id_expert == id_expert).FirstOrDefault().experts.first_name + " " + e.Result.list_marks.Where(o => o.id_expert == id_expert).FirstOrDefault().experts.second_name + " " + e.Result.list_marks.Where(o => o.id_expert == id_expert).FirstOrDefault().experts.patronymic; } else { xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.Add(Type.Missing, xlWorkSheet, Type.Missing, Type.Missing); xlWorkSheet.Name = "Отчет эксперта " + e.Result.list_marks_factors.Where(o => o.id_expert == id_expert).FirstOrDefault().experts.first_name; } var ls_marks_factors_expert = e.Result.list_marks_factors.Where(o => o.id_expert == id_expert).OrderBy(o => o.factors.priority).ToList(); var ls_marks_ribs_expert = e.Result.list_marks_ribs.Where(o => o.id_expert == id_expert).OrderBy(o => o.ribs.factors.priority).ToList(); var list_priority = e.Result.list_marks_factors.Select(o => o.factors.priority).GroupBy(o => o).OrderBy(o => o.Key).ToList(); int number_row = 0; for (int j = 0; j < list_priority.Count() - 1; j++) { int number_col = 1; number_row++; int count_pr_row = ls_marks_factors_expert.Where(o => o.factors.priority == list_priority[j].Key + 1).Count(); for (int t = 0; t < ls_marks_factors_expert.Count(); t++) { //if (ls_marks_factors_expert[t].factors.priority == 0) //{ // number_col++; // xlWorkSheet.Cells[1,number_col] = ls_marks_factors_expert[t].factors.name //} if (ls_marks_factors_expert[t].factors.priority == list_priority[j].Key) { number_col++; xlWorkSheet.Cells[number_row, number_col] = ls_marks_factors_expert[t].factors.name + "\n" + " " + Math.Round(ls_marks_factors_expert[t].value * 100); //number_row++; } else if (ls_marks_factors_expert[t].factors.priority == list_priority[j].Key + 1) { number_row++; //xlWorkSheet.Cells.Style.WrapText = true; xlWorkSheet.Cells[number_row, 1] = ls_marks_factors_expert[t].factors.name + "\n" + Math.Round(ls_marks_factors_expert[t].value * 100); var values = ls_marks_ribs_expert.Where(o => o.ribs.id_factor_in == ls_marks_factors_expert[t].factors.id_factor).ToList(); for (int l = 0; l < values.Count(); l++) { xlWorkSheet.Cells[number_row, 2 + l] = Math.Round(values[l].value * 100); } //var mark_rib = ls_marks_ribs_expert.Where(o=>o.ribs.factors.name == ) } } number_row++; } //Постройка диаграммы int last_priority2 = list_priority.Max(o => o.Key); var ls_res_fact_last2 = e.Result.list_marks_factors.Where(o => o.factors.priority == last_priority2 && o.id_expert == id_expert).OrderBy(o => o.factors.priority).ToList(); List <double> masY2 = new List <double>(); List <string> masX2 = new List <string>(); for (int t = 0; t < ls_res_fact_last2.Count(); t++) { masX2.Add(ls_res_fact_last2[t].factors.name); masY2.Add(Math.Round(ls_res_fact_last2[t].value * 100)); } //строим диаграмму Excel.ChartObjects _xlCharts2 = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject _myChart2 = (Excel.ChartObject)_xlCharts2.Add(10 * last_priority2, 80, 300, 250); Excel.Chart _chartPage2 = _myChart2.Chart; SeriesCollection _seriesCollection2 = (SeriesCollection)_chartPage2.SeriesCollection(Type.Missing); Series _series2 = _seriesCollection2.NewSeries(); _series2.XValues = masX2.ToArray(); _series2.Values = masY2.ToArray(); _chartPage2.ApplyLayout(2, _chartPage2.ChartType); _chartPage2.Legend.LegendEntries(_chartPage2.Legend.LegendEntries().Count).Delete(); _chartPage2.ChartTitle.Text = "Результат"; } //Строим итоговую таблицу var ls_results_factors = e.Result.list_results_factors.OrderBy(o => o.factors.priority).ToList(); var ls_results_ribs_expert = e.Result.list_results_ribs.OrderBy(o => o.ribs.factors.priority).ToList(); var list_priority_result = e.Result.list_results_factors.Select(o => o.factors.priority).GroupBy(o => o).OrderBy(o => o.Key).ToList(); int number_row2 = 0; xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.Add(Type.Missing, xlWorkSheet, Type.Missing, Type.Missing); xlWorkSheet.Name = "Отчет общий "; for (int j = 0; j < list_priority_result.Count() - 1; j++) { int number_col = 1; number_row2++; int count_pr_row = ls_results_factors.Where(o => o.factors.priority == list_priority_result[j].Key + 1).Count(); for (int t = 0; t < ls_results_factors.Count(); t++) { if (ls_results_factors[t].factors.priority == list_priority_result[j].Key) { number_col++; xlWorkSheet.Cells[number_row2, number_col] = ls_results_factors[t].factors.name + "\n" + Math.Round(ls_results_factors[t].value * 100); //number_row++; } else if (ls_results_factors[t].factors.priority == list_priority_result[j].Key + 1) { number_row2++; //xlWorkSheet.Cells.Style.WrapText = true; xlWorkSheet.Cells[number_row2, 1] = ls_results_factors[t].factors.name + "\n" + Math.Round(ls_results_factors[t].value * 100); var values = ls_results_ribs_expert.Where(o => o.ribs.id_factor_in == ls_results_factors[t].factors.id_factor).ToList(); for (int l = 0; l < values.Count(); l++) { xlWorkSheet.Cells[number_row2, 2 + l] = Math.Round(values[l].value * 100); } //var mark_rib = ls_marks_ribs_expert.Where(o=>o.ribs.factors.name == ) } } number_row2++; } int last_priority = list_priority_result.Max(o => o.Key); var ls_res_fact_last = e.Result.list_results_factors.Where(o => o.factors.priority == last_priority).OrderBy(o => o.factors.priority).ToList(); List <double> masY = new List <double>(); List <string> masX = new List <string>(); for (int i = 0; i < ls_res_fact_last.Count(); i++) { masX.Add(ls_res_fact_last[i].factors.name); masY.Add(Math.Round(ls_res_fact_last[i].value * 100)); } //строим диаграмму Excel.ChartObjects _xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject _myChart = (Excel.ChartObject)_xlCharts.Add(10 * last_priority, 80, 300, 250); Excel.Chart _chartPage = _myChart.Chart; SeriesCollection _seriesCollection = (SeriesCollection)_chartPage.SeriesCollection(Type.Missing); Series _series = _seriesCollection.NewSeries(); _series.XValues = masX.ToArray(); _series.Values = masY.ToArray(); _chartPage.ApplyLayout(2, _chartPage.ChartType); _chartPage.Legend.LegendEntries(_chartPage.Legend.LegendEntries().Count).Delete(); _chartPage.ChartTitle.Text = "Результат"; //chartPage.ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowValue, false, true, false, false, false, false, true, false, false); //_chartPage.ChartType = Excel.XlChartType.xlPie; //Excel.Range cellRange = (Excel.Range)xlWorkSheet.Cells[1, 1]; //Excel.Range rowRange = cellRange.EntireRow; //rowRange.Insert(Excel.XlInsertShiftDirection.xlShiftDown, false); //xlWorkSheet.Cells[1,1] = "awdwad"; //xlWorkSheet.Columns.AutoFit(); xlWorkSheet.Cells.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Cells.Style.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; SaveFileDialog sFile = new SaveFileDialog(); sFile.Filter = "Excel files (*.xls)|*.xls|All files (*.*)|*.*"; try { if (sFile.ShowDialog() == true) { string path = sFile.FileName; xlWorkBook.Application.DisplayAlerts = false; //MessageBox.Show(path); xlWorkBook.SaveAs(path, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); //xlApp.Visible = true; xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); MessageBox.Show("Ексель файл создан. Вы можете найти его по пути " + path); } Marshal.ReleaseComObject(xlWorkSheet); Marshal.ReleaseComObject(xlWorkBook); Marshal.ReleaseComObject(xlApp); GC.Collect(); GC.WaitForPendingFinalizers(); } catch (Exception ex) { MessageBox.Show("Error", ex.Message); } } else { MessageBox.Show(e.Error.Message); } cLoading.stop(); }
private void button2_Click(object sender, EventArgs e) { try { string fileName = Dir + "\\" + string.Format("{0}.xls", Guid.NewGuid()).Replace(" ", "_"); //if (File.Exists(fileName)) //{ // try // { // File.Delete(fileName); // } // catch (Exception) // { // MessageBox.Show(string.Format("В настоящий момент используется файл:\r\n{0}\r\nДля создания договора закройте пожалуйста файл.", fileName), // "Невозможно создать договор", MessageBoxButtons.OK, MessageBoxIcon.Warning); // return; // } //} Excel.Application xla = new Excel.Application(); xla.Visible = true; Excel.Workbook wb = xla.Workbooks.Add(Excel.XlSheetType.xlWorksheet); Excel.Worksheet ws = (Excel.Worksheet)xla.ActiveSheet; // Now create the chart. Excel.ChartObjects chartObjs = (Excel.ChartObjects)ws.ChartObjects(); Excel.ChartObject chartObj = chartObjs.Add(512, 80, 300, 300); Excel.Chart xlChart = chartObj.Chart; xlChart.ChartType = Excel.XlChartType.xlBarClustered; // generate some random data dateTimePicker1.Value = dateTimePicker1.Value.Date.AddDays(1 - dateTimePicker1.Value.Day); dateTimePicker2.Value = dateTimePicker2.Value.Date.AddMonths(1).AddDays(-dateTimePicker2.Value.Day); ws.Cells[2, 2] = string.Format("Результативность работы фирмы за период с {0} по {1}", dateTimePicker1.Value, dateTimePicker2.Value); ws.Cells[4, 3] = "Период"; ws.Cells[4, 4] = "Количество сделок"; ws.Cells[4, 5] = "Сумма"; int row = 5; for (var dateFrom = dateTimePicker1.Value; dateFrom <= dateTimePicker2.Value; dateFrom = dateFrom.AddMonths(1)) { var dateTo = dateFrom.AddMonths(1).AddDays(-1); var trips = dbj.Jurnal.Where(t => t.data >= dateFrom && t.data <= dateTo); foreach (var i in trips) { ws.Cells[row, 3] = GetMonth(dateFrom.Month); ws.Cells[row, 4] = trips.Count(); ws.Cells[row, 5] = i.summa; } row++; } Excel.Range xValues = ws.Range["C5", "C" + (row - 1)]; Excel.Range values = ws.Range["E5", "E" + (row - 1)]; Excel.SeriesCollection seriesCollection = xlChart.SeriesCollection(); Excel.Series series1 = seriesCollection.NewSeries(); series1.XValues = xValues; series1.Values = values; //xla.ActiveChart.HasTitle = true; //xla.ActiveChart.ChartTitle.Text = "Тестовая диаграмма"; //xla.ActiveChart.ChartTitle.Font.Size = 13; //xla.ActiveChart.ChartTitle.Font.Color = 254; //xla.ActiveChart.ChartTitle.Shadow = true; //xla.ActiveChart.ChartTitle.Border.LineStyle = Excel.Constants.xlSolid; } catch { MessageBox.Show("Ошибка Excel не найден"); } }
private void AddDataPoints(Excel.Chart bubbleChart, Excel.Worksheet sourceWorksheet) { var dataMatrix = GetDataMatrixFromExcelWorksheet(sourceWorksheet); var seriesCollection = bubbleChart.SeriesCollection(); var minXValue = double.MaxValue; var minYValue = double.MaxValue; var maxXValue = double.MinValue; var maxYValue = double.MinValue; foreach (var row in dataMatrix) { var series = seriesCollection.NewSeries(); series.Has3DEffect = true; series.HasDataLabels = true; var dataLabelText = row[0]; var xValue = Convert.ToDouble(row[1]); var yValue = Convert.ToDouble(row[2]); var revenue = Convert.ToDouble(row[3]) / 1000; var projectTypeId = GetProjectTypeIdFromProjectTypeString(row[4]); var bubbleSize = GetBubbleSizeFromRevenue(revenue); series.Format.Fill.ForeColor.RGB = _projectTypeColors[projectTypeId]; series.XValues = new[] { xValue }; series.Values = new[] { yValue }; series.BubbleSizes = new[] { bubbleSize }; Excel.DataLabel dataLabel = series.DataLabels(1); dataLabel.Text = dataLabelText; if (xValue < minXValue) { minXValue = xValue; } if (yValue < minYValue) { minYValue = yValue; } if (xValue > maxXValue) { maxXValue = xValue; } if (yValue > maxYValue) { maxYValue = yValue; } } var xAxis = (Excel.Axis)bubbleChart.Axes(Excel.XlAxisType.xlCategory); xAxis.MinimumScale = (Math.Floor(minXValue / 100) - 1) * 100; xAxis.MaximumScale = (Math.Ceiling(maxXValue / 100) + 1) * 100; var yAxis = (Excel.Axis)bubbleChart.Axes(Excel.XlAxisType.xlValue); yAxis.MinimumScale = (Math.Floor(minYValue * 10) - 1) / 10; yAxis.MaximumScale = (Math.Ceiling(maxYValue * 10) + 1) / 10; }
private void DrawBubbleLegend(Excel.Chart chart) { var revenues = new double[] { 200, 400, 600, 800, 1000 }; const double revenueIncrement = 200; var zeroArray = revenues.Select(x => x).ToArray(); var bubbleSizes = revenues.Select(x => GetBubbleSizeFromRevenue(x - revenueIncrement / 2)).ToArray(); Excel.Series dummyBubbleSeries = chart.SeriesCollection().NewSeries(); dummyBubbleSeries.XValues = zeroArray; dummyBubbleSeries.Values = zeroArray; dummyBubbleSeries.BubbleSizes = bubbleSizes; var bubbleWidths = new List <float>(); foreach (Excel.Point bubble in dummyBubbleSeries.Points()) { bubbleWidths.Add((float)bubble.Width); } var bubbleLegendNeededSpace = bubbleWidths.Max() + BubbleLegendTop; chart.PlotArea.Height -= bubbleLegendNeededSpace; float currentLeft = BubbleLegendLeft + RevenueTitleLength; for (var i = 0; i < bubbleWidths.Count; i++) { var bubbleWidth = bubbleWidths[i]; var topMargin = (float)chart.PlotArea.Height + BubbleLegendTop + bubbleWidths.Max() / 2 - bubbleWidth / 2; var oval = chart.Shapes.AddShape(MsoAutoShapeType.msoShapeOval, currentLeft, topMargin, bubbleWidth, bubbleWidth); oval.Fill.ForeColor.RGB = (int)Excel.XlRgbColor.rgbWhite; oval.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbBlack; var label = chart.Shapes.AddLabel(MsoTextOrientation.msoTextOrientationHorizontal, currentLeft + bubbleWidth + BubbleLegendLabelMargin, topMargin + bubbleWidth / 2 - RevenueTextLabelHeight / 2, RevenueTextLabelLength, RevenueTextLabelHeight); var revenueText = string.Empty; if (i == 0) { revenueText = "<" + revenues[i]; } else if (i == revenues.Length - 1) { revenueText = ">" + revenues[i - 1]; } else { revenueText = revenues[i - 1] + "-" + revenues[i]; } label.TextFrame.Characters().Text = revenueText; currentLeft += bubbleWidth + RevenueTextLabelLength + BubbleLegendLabelMargin * 2; } var revenueLabel = chart.Shapes.AddLabel(MsoTextOrientation.msoTextOrientationHorizontal, BubbleLegendLeft, BubbleLegendTop + bubbleWidths.Max() / 2 + (float)chart.PlotArea.Height - RevenueTextLabelHeight / 2, RevenueTitleLength, RevenueTextLabelHeight); revenueLabel.TextFrame.Characters().Text = "Turnover kkr:"; dummyBubbleSeries.Delete(); }
/// <summary> /// Writes an Excel worksheet containing a Respiratory Signal's values and Plot /// </summary> /// <param name="ws"> The Excel worksheet object </param> /// <param name="SignalName"> The name of the respiratory signal </param> /// <param name="table"> The respiratory signal values and peak/onset locations </param> /// <param name="ROWS"> The number of rows in the table </param> /// <param name="COLUMNS"> The number of columns in the table </param> public static void AddRespiratorySignalToWorksheet(Excel.Worksheet ws, string SignalName, object[,] table, int ROWS, int COLUMNS) { // Make Table with Values Excel.Range range = ws.Range[ws.Cells[3, 2], ws.Cells[3 + ROWS - 1, 2 + COLUMNS - 1]]; range.Value = table; ws.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, range, System.Reflection.Missing.Value, Excel.XlYesNoGuess.xlGuess, System.Reflection.Missing.Value).Name = ws.Name; ws.ListObjects[ws.Name].TableStyle = "TableStyleLight9"; ws.Columns["A:I"].ColumnWidth = 20; ws.Columns["E:H"].Hidden = true; ws.Columns["B:H"].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // Add Conditional Formatting Excel.Range range2 = ws.Range[ws.Cells[4, 2], ws.Cells[3 + ROWS - 1, 2 + COLUMNS - 1]]; range2.FormatConditions.Add(Excel.XlFormatConditionType.xlExpression, System.Reflection.Missing.Value, "=NOT(ISBLANK($E4))", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value); range2.FormatConditions.Add(Excel.XlFormatConditionType.xlExpression, System.Reflection.Missing.Value, "=NOT(ISBLANK($F4))", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value); range2.FormatConditions.Add(Excel.XlFormatConditionType.xlExpression, System.Reflection.Missing.Value, "=NOT(ISBLANK($G4))", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value); range2.FormatConditions.Add(Excel.XlFormatConditionType.xlExpression, System.Reflection.Missing.Value, "=NOT(ISBLANK($H4))", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value); range2.FormatConditions[1].Interior.Color = 5296274; range2.FormatConditions[2].Interior.Color = 255; range2.FormatConditions[3].Interior.Color = 65535; range2.FormatConditions[4].Interior.Color = 15773696; range2.Columns[2].NumberFormat = "m/d/yyyy h:mm:ss.000"; // Add Chart Excel.Chart chart = ((Excel.ChartObject)((Excel.ChartObjects)ws.ChartObjects()).Add(500, 100, 900, 500)).Chart; chart.SetSourceData(range.Columns["B:G"]); chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlXYScatterLines; chart.ChartWizard(Source: range.Columns["B:G"], Title: SignalName, CategoryTitle: "Time", ValueTitle: SignalName); chart.PlotVisibleOnly = false; ((Excel.Series)chart.SeriesCollection(1)).ChartType = Excel.XlChartType.xlXYScatterLinesNoMarkers; ((Excel.Series)chart.SeriesCollection(2)).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleSquare; ((Excel.Series)chart.SeriesCollection(3)).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleSquare; ((Excel.Series)chart.SeriesCollection(4)).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleSquare; ((Excel.Series)chart.SeriesCollection(5)).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleSquare; ((Excel.Series)chart.SeriesCollection(2)).Format.Fill.ForeColor.RGB = 5296274; ((Excel.Series)chart.SeriesCollection(3)).Format.Fill.ForeColor.RGB = 255; ((Excel.Series)chart.SeriesCollection(4)).Format.Fill.ForeColor.RGB = 65535; ((Excel.Series)chart.SeriesCollection(5)).Format.Fill.ForeColor.RGB = 15773696; ((Excel.Series)chart.SeriesCollection(1)).Format.Line.ForeColor.RGB = 38450; ((Excel.Series)chart.SeriesCollection(2)).Format.Line.ForeColor.RGB = 5296274; ((Excel.Series)chart.SeriesCollection(3)).Format.Line.ForeColor.RGB = 255; ((Excel.Series)chart.SeriesCollection(4)).Format.Line.ForeColor.RGB = 65535; ((Excel.Series)chart.SeriesCollection(5)).Format.Line.ForeColor.RGB = 15773696; System.Runtime.InteropServices.Marshal.ReleaseComObject(chart); System.Runtime.InteropServices.Marshal.ReleaseComObject(range); System.Runtime.InteropServices.Marshal.ReleaseComObject(range2); }
private void CreateDocument() { Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); app.Visible = true; app.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlMaximized; object misValue = System.Reflection.Missing.Value; Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Open(@"C:\LPA_IPAMLx_V2\LPA_IPAM.xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); Worksheet ws = wb.Worksheets[1]; int col = 1; int row = 16; ws.Range["B8"].Value = Convert.ToString(replaceNome).TrimStart(); ws.Range["B9"].Value = Convert.ToString(replaceNrAluno).TrimStart(); ws.Range["B10"].Value = Convert.ToString(replaceCurso).TrimStart(); ws.Range["B11"].Value = Convert.ToString(replaceData).TrimStart(); GetNotas(); foreach (Notas nota in listaNotas) { ws.Cells[row, col].Font.Size = 10; ws.Cells[row, col].Value = nota.UC.TrimStart(); ws.Cells[row, col + 5].Font.Size = 10; ws.Cells[row, col + 5].Value = nota.Nota; ws.Cells[row, col + 6].Font.Size = 10; ws.Cells[row, col + 6].Value = nota.ECTS; row++; } GetMedia((string)replaceNrAluno, (string)replaceCdCurso); ws.Range["B59"].Value = (string)replaceMedia + " valores"; ws.Range["B73"].Value = Convert.ToString(replaceNome).TrimStart(); ws.Range["B74"].Value = Convert.ToString(replaceNrAluno).TrimStart(); ws.Range["B75"].Value = Convert.ToString(replaceCurso).TrimStart(); ws.Range["B76"].Value = Convert.ToString(replaceData).TrimStart(); Competencias competencias = GetDadosCompetencias((string)replaceNrAluno); Microsoft.Office.Interop.Excel.ChartObjects xlCharts = (Microsoft.Office.Interop.Excel.ChartObjects)ws.ChartObjects(Type.Missing); Microsoft.Office.Interop.Excel.ChartObject myChart = (Microsoft.Office.Interop.Excel.ChartObject)xlCharts.Add(5, 1165, 470, 300); Microsoft.Office.Interop.Excel.Chart chartPage = myChart.Chart; Microsoft.Office.Interop.Excel.SeriesCollection seriesCollection = (Microsoft.Office.Interop.Excel.SeriesCollection)chartPage.SeriesCollection(); var ser = seriesCollection.NewSeries(); chartPage.Legend.Delete(); ser.Values = new double[] { competencias.adaptacao, competencias.trabalhar, competencias.decisoes, competencias.objactivos, competencias.ideias, competencias.aprendizagem, competencias.mentalidadeGlobal, competencias.gestaoEquipas }; ser.XValues = new string[] { "Adaptação", "Trabalhar com os Outros", "Tomar Decisões", "Alcançar Objetivos", "Geração de Ideias", "Aprendizagem", "Mentalidade Global", "Gestão de Equipas" }; //chartRange = ws.get_Range("A1", "d5"); //chartPage.SetSourceData(chartRange, misValue); chartPage.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlBarClustered; chartPage.SeriesCollection(1).Points(1).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 212, 83, 10); chartPage.SeriesCollection(1).Points(2).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 212, 83, 10); chartPage.SeriesCollection(1).Points(3).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 212, 83, 10); chartPage.SeriesCollection(1).Points(4).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 212, 83, 10); chartPage.SeriesCollection(1).Points(5).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 212, 83, 10); chartPage.SeriesCollection(1).Points(6).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 212, 83, 10); chartPage.SeriesCollection(1).Points(7).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 212, 83, 10); chartPage.SeriesCollection(1).Points(8).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 212, 83, 10); ws.Range["B127"].Value = Convert.ToString(replaceNome).TrimStart(); ws.Range["B128"].Value = Convert.ToString(replaceNrAluno).TrimStart(); ws.Range["B129"].Value = Convert.ToString(replaceCurso).TrimStart(); ws.Range["B130"].Value = Convert.ToString(replaceData).TrimStart(); GetLinguas((string)replaceNrAluno); GetRespAcademicas((string)replaceNrAluno); GetEstagios((string)replaceNrAluno); GetMobilidade((string)replaceNrAluno); GetPremios((string)replaceNrAluno); GetAtividades((string)replaceNrAluno); GetOutrasExperiencias((string)replaceNrAluno); GetRespSocial((string)replaceNrAluno); col = 1; row = 135; if (listaLinguas.Count > 0) { string header = "LÍNGUAS"; ws.Cells[row, col].Font.Color = System.Drawing.Color.FromArgb(0, 212, 83, 10); ws.Cells[row, col].Font.Bold = true; ws.Cells[row, col].Value = header; Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]]; Microsoft.Office.Interop.Excel.Borders border = cells.Borders; border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; border[XlBordersIndex.xlEdgeBottom].Weight = 4d; border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.FromArgb(0, 212, 83, 10); foreach (Linguas l in listaLinguas) { row++; ws.Cells[row, col].Value = " > " + l.lingua + " | Nível " + l.nivel; } cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]]; border = cells.Borders; border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; border[XlBordersIndex.xlEdgeBottom].Weight = 2d; row += 2; } if (listaRespAcademicas.Count > 0) { string header = "RESPONSABILIDADES ACADÉMICAS"; ws.Cells[row, col].Font.Color = System.Drawing.Color.FromArgb(0, 212, 83, 10); ws.Cells[row, col].Font.Bold = true; ws.Cells[row, col].Value = header; Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]]; Microsoft.Office.Interop.Excel.Borders border = cells.Borders; border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; border[XlBordersIndex.xlEdgeBottom].Weight = 4d; border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.FromArgb(0, 212, 83, 10); foreach (ResponsabilidadesAcademicas l in listaRespAcademicas) { row++; ws.Cells[row, col].Value = " > " + l.responsabilidade + " | " + l.anoLetivo; } cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]]; border = cells.Borders; border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; border[XlBordersIndex.xlEdgeBottom].Weight = 2d; row += 2; } if (listaEstagios.Count > 0) { string header = "ESTÁGIOS"; ws.Cells[row, col].Font.Color = System.Drawing.Color.FromArgb(0, 212, 83, 10); ws.Cells[row, col].Font.Bold = true; ws.Cells[row, col].Value = header; Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]]; Microsoft.Office.Interop.Excel.Borders border = cells.Borders; border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; border[XlBordersIndex.xlEdgeBottom].Weight = 4d; border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.FromArgb(0, 212, 83, 10); foreach (Estagios l in listaEstagios) { if (l.dataInicio != "01/01/1900") { row++; ws.Cells[row, col].Value = " > " + l.tipoEstagio + " na " + l.empresa + " | De " + l.dataInicio.Substring(0, 10) + " a " + l.dataFim.Substring(0, 10); } else { row++; ws.Cells[row, col].Value = " > " + l.tipoEstagio + " na " + l.empresa; } } cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]]; border = cells.Borders; border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; border[XlBordersIndex.xlEdgeBottom].Weight = 2d; row += 2; } if (listaMobilidade.Count > 0) { string header = "MOBILIDADE INTERNACIONAL"; ws.Cells[row, col].Font.Color = System.Drawing.Color.FromArgb(0, 212, 83, 10); ws.Cells[row, col].Font.Bold = true; ws.Cells[row, col].Value = header; Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]]; Microsoft.Office.Interop.Excel.Borders border = cells.Borders; border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; border[XlBordersIndex.xlEdgeBottom].Weight = 4d; border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.FromArgb(0, 212, 83, 10); foreach (Mobilidade l in listaMobilidade) { row++; ws.Cells[row, col].Value = " > " + l.tipo + " na " + l.programa + " | " + l.anoLetivo; } cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]]; border = cells.Borders; border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; border[XlBordersIndex.xlEdgeBottom].Weight = 2d; row += 2; } if (listaRespSocial.Count > 0) { string header = "RESPONSABILIDADE SOCIAL & VOLUNTARIADO"; ws.Cells[row, col].Font.Color = System.Drawing.Color.FromArgb(0, 212, 83, 10); ws.Cells[row, col].Font.Bold = true; ws.Cells[row, col].Value = header; Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]]; Microsoft.Office.Interop.Excel.Borders border = cells.Borders; border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; border[XlBordersIndex.xlEdgeBottom].Weight = 4d; border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.FromArgb(0, 212, 83, 10); foreach (ResposabilidadeSocial l in listaRespSocial) { row++; ws.Cells[row, col].Value = " > " + l.AcaoSocial + " | " + l.AnoLetivo; } cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]]; border = cells.Borders; border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; border[XlBordersIndex.xlEdgeBottom].Weight = 2d; row += 2; } if (listaPremios.Count > 0) { string header = "PRÉMIOS & RECONHECIMENTOS"; ws.Cells[row, col].Font.Color = System.Drawing.Color.FromArgb(0, 212, 83, 10); ws.Cells[row, col].Font.Bold = true; ws.Cells[row, col].Value = header; Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]]; Microsoft.Office.Interop.Excel.Borders border = cells.Borders; border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; border[XlBordersIndex.xlEdgeBottom].Weight = 4d; border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.FromArgb(0, 212, 83, 10); foreach (Premios l in listaPremios) { row++; ws.Cells[row, col].Value = " > " + l.premio + " | " + l.anoLetivo; } cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]]; border = cells.Borders; border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; border[XlBordersIndex.xlEdgeBottom].Weight = 2d; row += 2; } if (listaAtividades.Count > 0) { string header = "ATIVIDADES DESPORTIVAS"; ws.Cells[row, col].Font.Color = System.Drawing.Color.FromArgb(0, 212, 83, 10); ws.Cells[row, col].Font.Bold = true; ws.Cells[row, col].Value = header; Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]]; Microsoft.Office.Interop.Excel.Borders border = cells.Borders; border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; border[XlBordersIndex.xlEdgeBottom].Weight = 4d; border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.FromArgb(0, 212, 83, 10); foreach (ActDesportivas l in listaAtividades) { row++; ws.Cells[row, col].Value = " > " + l.atividade + " | " + l.anoLetivo; } cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]]; border = cells.Borders; border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; border[XlBordersIndex.xlEdgeBottom].Weight = 2d; row += 2; } if (listaExperiencias.Count > 0) { string header = "OUTRAS EXPERIÊNCIAS"; ws.Cells[row, col].Font.Color = System.Drawing.Color.FromArgb(0, 212, 83, 10); ws.Cells[row, col].Font.Bold = true; ws.Cells[row, col].Value = header; Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]]; Microsoft.Office.Interop.Excel.Borders border = cells.Borders; border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; border[XlBordersIndex.xlEdgeBottom].Weight = 4d; border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.FromArgb(0, 212, 83, 10); foreach (OutrasExperiencias l in listaExperiencias) { row++; ws.Cells[row, col].Value = " > " + l.experiencia + " | " + l.anoLetivo; } cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]]; border = cells.Borders; border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; border[XlBordersIndex.xlEdgeBottom].Weight = 2d; row += 2; } string data = Convert.ToString(DateTime.Now.ToLongDateString()); ws.Cells[164, 1].Value = "Lisboa, " + data; int nrCertificado = GravaCertificado(competencias); string anoLetivo = getAnoLetivo((string)replaceNrAluno, (string)replaceCdCurso); ws.Range["E11"].Value = nrCertificado + " | POR | " + anoLetivo; ws.Range["E76"].Value = nrCertificado + " | POR | " + anoLetivo; ws.Range["E130"].Value = nrCertificado + " | POR | " + anoLetivo; }
/// <summary> /// Метод создания и сохранения документов /// в форматах Microsoft Word (doc, PDF), /// Excel (exls) /// </summary> /// <param name="type">Тип создаваемого документа /// отчёт или статистика</param> /// <param name="format">Формат сохранения /// документ или таблица</param> /// <param name="name">Название документа</param> /// <param name="table">Входная таблица с данными</param> public void Document_Create(Document_Type type, Document_Format format, string name, DataTable table) { //Получение данных о конфигурации документа Configuration_class configuration_Class = new Configuration_class(); configuration_Class.Document_Configuration_Get(); //Проверка на пустоту названия switch (name != "" || name != null) { case true: //Выбор формата либо Word либо Excel switch (format) { case Document_Format.Word: //Запуск процесса в дистпечере задач word.Application application = new word.Application(); //создание документа в процессе word.Document document //Присвоение документа процессу, Visible: true //возможность редактирования документа = application.Documents.Add(Visible: true); try { //Объявление дипапазона для формирования текста word.Range range = document.Range(0, 0); //89Настройка отступов в документе document.Sections.PageSetup.LeftMargin = application.CentimetersToPoints( (float)Configuration_class. doc_Left_Merge); document.Sections.PageSetup.TopMargin = application.CentimetersToPoints( (float)Configuration_class. doc_Top_Merge); document.Sections.PageSetup.RightMargin = application. CentimetersToPoints((float) Configuration_class.doc_Right_Merg); document.Sections.PageSetup.BottomMargin = application.CentimetersToPoints( (float)Configuration_class. doc_Bottom_Merge); //Присвоение текстового знеачения в дипазон range.Text = "Мвидео"; //Настройка выравнивания текста range.ParagraphFormat.Alignment = word.WdParagraphAlignment. wdAlignParagraphCenter; //Настройка интервала после абзаца range.ParagraphFormat.SpaceAfter = 1; //Настройка интервала перед абзаца range.ParagraphFormat.SpaceBefore = 1; //Настройка межстрочного интервала range.ParagraphFormat.LineSpacingRule = word.WdLineSpacing.wdLineSpaceSingle; //Настройка названия шрифта range.Font.Name = "Times New Roman"; //Настройка размера шрифта range.Font.Size = 12; //Добавление параграфов document.Paragraphs.Add(); //В конце текста document.Paragraphs.Add(); //Свободный document.Paragraphs.Add(); //Для будущего текста //Параграф для названия документа word.Paragraph Document_Name = document.Paragraphs.Add(); //Настройка параграфа через свойство диапазона Document_Name.Format.Alignment = word.WdParagraphAlignment.wdAlignParagraphCenter; Document_Name.Range.Font.Name = "Times New Roman"; Document_Name.Range.Font.Size = 16; //Проверка на тип документа, отчёт или статистика switch (type) { case Document_Type.Report: Document_Name.Range.Text = "ОТЧЁТ"; break; case Document_Type.Statistic: Document_Name.Range.Text = "СТАТИСТИЧЕСКИЙ ОТЧЁТ"; break; } document.Paragraphs.Add(); document.Paragraphs.Add(); document.Paragraphs.Add(); word.Paragraph statparg = document.Paragraphs.Add(); //Создание области таблицы в документе word.Table stat_table //Добавление таблицы в область документа //Указывается параграф в котором документ создан //Количество строк и столбцов = document.Tables.Add(statparg.Range, table.Rows.Count, table.Columns.Count); //Настройка границ таблицы внутренние stat_table.Borders.InsideLineStyle = word.WdLineStyle.wdLineStyleSingle; //Настройка границ таблицы внешние stat_table.Borders.OutsideLineStyle = word.WdLineStyle.wdLineStyleSingle; //Выравнивание текста внутри ячеек по ширине stat_table.Rows.Alignment = word.WdRowAlignment.wdAlignRowCenter; //Выравнивание текста внутри ячеек по высоте stat_table.Range.Cells.VerticalAlignment = word.WdCellVerticalAlignment.wdCellAlignVerticalCenter; stat_table.Range.Font.Size = 11; stat_table.Range.Font.Name = "Times New Roman"; //Индексация столбцов и строк в Word начинается с 1,1 for (int row = 1; row <= table.Rows.Count; row++) { for (int col = 1; col <= table.Columns.Count; col++) { stat_table.Cell(row, col).Range.Text = table.Rows[row - 1][col - 1].ToString(); } } document.Paragraphs.Add(); document.Paragraphs.Add(); //Парадграф с фиксациейц даты создания документа word.Paragraph Footparg = document.Paragraphs.Add(); Footparg.Range.Text = string.Format("Дата создания \t\t\t{0}", DateTime.Now.ToString("dd.MM.yyyy")); } catch (Exception ex) { System.Windows.Forms.MessageBox.Show(ex.Message); } finally { switch (format) { case Document_Format.Word: //Сохранение документа с названием из метода, //и в формате doc document.SaveAs2(string.Format("{0}\\{1}", Environment.CurrentDirectory, name), word.WdSaveFormat.wdFormatDocument); break; case Document_Format.PDF: //Сохранение документа в формате PDF document.SaveAs2(string.Format("{0}\\{1}", Environment.CurrentDirectory, name), word.WdSaveFormat.wdFormatPDF); break; } //Закрываем документ document.Close(); //Выходим из процесса с его закрытием application.Quit(); } break; case Document_Format.Excel: //Создание процесса Excel excel.Application application_ex = new excel.Application(); //Создание книги excel.Workbook workbook = application_ex.Workbooks.Add(); //Создание страницы excel.Worksheet worksheet = (excel.Worksheet)workbook.ActiveSheet; try { switch (type) { case Document_Type.Report: //Название страницы worksheet.Name = "Отчёт"; for (int row = 0; row < table.Rows.Count; row++) { for (int col = 0; col < table.Columns.Count; col++) { //ЗАнесение данных в ячейку worksheet.Cells[row + 1][col + 1] = table.Rows[row][col].ToString(); } } //Указание диапазона работы с ячеёками листа excel.Range border //Начало диапазона = worksheet.Range[worksheet.Cells[1, 1], //Динамический конец диапазона в зависимости от //выдодимых данных worksheet.Cells[table.Rows.Count + 1] [table.Columns.Count + 1]]; //Стиль линий границ ячеек border.Borders.LineStyle = excel.XlLineStyle.xlContinuous; //Выравнивание во высоте border.VerticalAlignment = excel.XlHAlign.xlHAlignCenter; //Выравнивание по ширине border.HorizontalAlignment = excel.XlHAlign.xlHAlignCenter; //Внесение даты создания документа worksheet.Cells[table.Rows.Count + 3][2] = string.Format("Дата создания {0}", DateTime.Now.ToString()); //Объединение ячеек worksheet.Range[worksheet.Cells[table.Rows.Count + 3, 2], worksheet.Cells[table.Rows.Count + 2, table.Columns.Count + 2]].Merge(); break; case Document_Type.Statistic: worksheet.Name = "Статистический отчёт"; for (int row = 0; row < table.Rows.Count; row++) { for (int col = 0; col < table.Columns.Count; col++) { worksheet.Cells[row + 1][col + 1] = table.Rows[row][col].ToString(); } } excel.Range border1 = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[table.Rows.Count + 1] [table.Columns.Count + 1]]; border1.Borders.LineStyle = excel.XlLineStyle.xlContinuous; border1.VerticalAlignment = excel.XlHAlign.xlHAlignCenter; border1.HorizontalAlignment = excel.XlHAlign.xlHAlignCenter; worksheet.Cells[table.Rows.Count + 3][2] = string.Format("Дата создания {0}", DateTime.Now.ToString()); worksheet.Range[worksheet.Cells[table.Rows.Count + 3, 2], worksheet.Cells[table.Rows.Count + 2, table.Columns.Count + 2]].Merge(); //Класс области графиков excel.ChartObjects chartObjects = (excel.ChartObjects)worksheet.ChartObjects( Type.Missing); //Область размещения графиков: отступы слева сверху, //размер ширина и высота excel.ChartObject chartObject = chartObjects.Add(300, 50, 250, 250); //Объявление области графика excel.Chart chart = chartObject.Chart; //Объявление колекции построений графиков excel.SeriesCollection seriesCollection = (excel.SeriesCollection)chart.SeriesCollection( Type.Missing); //Объявление посторения графика excel.Series series = seriesCollection.NewSeries(); //Тип графика chart.ChartType = excel.XlChartType.xl3DColumn; //Диапазон значений по оси X series.XValues = worksheet.get_Range("B2", "B" + table.Rows.Count + 1); //Диапазон значений по оси Y series.Values = worksheet.get_Range("C2", "C" + table.Rows.Count + 1); break; } } catch { } finally { //Сохранение книги workbook.SaveAs(string.Format("{0}\\{1}", Environment.CurrentDirectory, name), application_ex.DefaultSaveFormat); //Закрытие книги workbook.Close(); //Завершение процесса application_ex.Quit(); } break; } break; case false: System.Windows.Forms.MessageBox.Show ("Введите название документа"); break; } }
/// <summary> /// Method sets the sheet with common results /// </summary> /// <param name="worksheet">Worksheet where the information will be saved</param> /// <param name="calculationTimes">Time results for each calculation types</param> /// <param name="results">results for each calculation types</param> private static void SetCommonResults(Excel.Worksheet worksheet, Dictionary <CalculationTypeName, double> calculationTimes, Dictionary <CalculationTypeName, List <DEVariable> > results) { worksheet.Name = "Common results"; int rowIndex = 1; int columnIndex = 1; worksheet.Cells[rowIndex, columnIndex] = "Calculation results"; rowIndex++; int i = 0; foreach (KeyValuePair <CalculationTypeName, List <DEVariable> > item in results) { int j = 0; List <DEVariable> result; if (i == 0) { result = item.Value; foreach (DEVariable variable in result) { worksheet.Cells[rowIndex + j + 1, columnIndex] = variable.Name; j++; } } worksheet.Cells[rowIndex, columnIndex + i + 1] = item.Key.ToString(); j = 0; result = item.Value; foreach (DEVariable variable in result) { worksheet.Cells[rowIndex + 1 + j, columnIndex + i + 1] = variable.Value; j++; } i++; } rowIndex += 3; worksheet.Cells[rowIndex, columnIndex] = "Time results"; rowIndex++; i = 0; foreach (KeyValuePair <CalculationTypeName, double> calculationTime in calculationTimes) { worksheet.Cells[rowIndex, columnIndex + i] = calculationTime.Key.ToString(); worksheet.Cells[rowIndex + 1, columnIndex + i] = calculationTime.Value; i++; } string leftTopTimeChart = GetExcelColumnName(1) + rowIndex.ToString(); string rightDownTimeChart = GetExcelColumnName(calculationTimes.Count) + (rowIndex + 1).ToString(); Excel.Range chartRange; Excel.ChartObjects xlCharts = (Excel.ChartObjects)worksheet.ChartObjects(Type.Missing); Excel.ChartObject mychart = xlCharts.Add(10, 80, 500, 450); Excel.Chart chartPage = mychart.Chart; chartRange = worksheet.get_Range(leftTopTimeChart, rightDownTimeChart); chartPage.SetSourceData(chartRange); chartPage.ChartType = Excel.XlChartType.xl3DColumnClustered; chartPage.SeriesCollection(1).Name = "Calculation times"; }
private void GenerateReportTopGenres(Dictionary <string, double> dictionary) { excel.Application excelApp = new excel.Application(); excel.Workbook workbook; excel.Worksheet workSheet; workbook = excelApp.Workbooks.Add(); //workSheet = (excel.Worksheet)workbook.Worksheets.get_Item(1); workSheet = workbook.ActiveSheet; int i = 1; foreach (var word in dictionary.OrderByDescending(q => q.Value)) { workSheet.Cells[1, i] = word.Key; workSheet.Cells[2, i] = word.Value; i++; } excel.Range Erange = workSheet.Range["B3"]; Erange.Formula = "=SUM(A2:J2)"; Erange.FormulaHidden = false; excel.Borders border = Erange.Borders; border.LineStyle = excel.XlLineStyle.xlContinuous; excel.ChartObjects chObs = (excel.ChartObjects)workSheet.ChartObjects(); excel.ChartObject chOb = chObs.Add(5, 50, 300, 300); excel.Chart xlchart = chOb.Chart; excel.Range Erange2 = workSheet.Range["A1:J1"]; excel.Range Erange3 = workSheet.Range["A3:J1"]; xlchart.ChartType = excel.XlChartType.xlColumnClustered; excel.SeriesCollection seriesCollection = (excel.SeriesCollection)xlchart.SeriesCollection(Type.Missing); excel.Series series = seriesCollection.NewSeries(); //series.XValues = workSheet.Range["A1:J1"]; string[] matrix = new string[10]; for (int j = 0; j < 10; j++) { matrix[j] = (string)(workSheet.Cells[1, j + 1] as excel.Range).Value; } //series.XValues = workSheet.Range[workSheet.Cells[1, 3]]; series.XValues = matrix; series.Values = workSheet.get_Range("A2", "J2"); xlchart.HasTitle = true; xlchart.ChartTitle.Text = "Жанры и их рейтинги"; xlchart.HasLegend = true; series.Name = "Жанры"; excelApp.Visible = true; excelApp.UserControl = true; string outputPath = @"C:\Users\acer\Desktop\Учеба\АИС\Отчет по рейтингу жанров (" + Guid.NewGuid().ToString() + ").xlsx"; workbook.SaveAs(outputPath); object misValue = System.Reflection.Missing.Value; xlchart.Export("C:\\Users\\acer\\Desktop\\Учеба\\АИС\\Graf.bmp", "BMP", misValue); string path = @"C:\Users\acer\Desktop\Учеба\АИС\Отчет по рейтингу жанров.doc"; WordReportTopBookOfGenre wordReport = new WordReportTopBookOfGenre(path); wordReport.GenerateReportTopGenres(dictionary); excelApp.Quit(); }
//Revenues Comparison public string RevenuesComparison(int ByDDMMYYYY, string DateFrom, string DateTo, bool ExportToExcel = false, string ExportPath = "") { try { string ReportName = "Revenues Comparison Report"; DataTable aTable = ReportsMgmt.RevenuesComparions(ByDDMMYYYY, DateFrom, DateTo); if (aTable.Rows.Count > 0) { xlApp = new Excel.Application(); xlApp.DisplayAlerts = false; xlApp.Visible = false; xlApp.SheetsInNewWorkbook = 1; xlWorkBook = xlApp.Workbooks.Add(misValue);//misValue int RowCnt = 1; xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); xlWorkSheet.Name = ReportName; List <string> aHeader = ReportsHelper.ImportReportHeader(0, 1); List <string> aFooter = ReportsHelper.ImportReportHeader(1, 1); for (int i = 0; i < aHeader.Count; i++) { xlWorkSheet.Cells[RowCnt, 2] = aHeader[i]; RowCnt++; } xlWorkSheet.Cells[RowCnt++, 1] = ReportName; xlWorkSheet.Cells[RowCnt, 2] = "Date From:\t" + DateFrom; xlWorkSheet.Cells[RowCnt, 3] = "Date To:\t" + DateTo; RowCnt++; xlWorkSheet.Cells[RowCnt, 1] = "Date"; xlWorkSheet.Cells[RowCnt, 2] = "Total Cost JOD"; xlWorkSheet.Cells[RowCnt, 3] = "Total Sales JOD"; xlWorkSheet.Cells[RowCnt, 4] = "Gross Profit JOD"; xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).Font.Bold = true; //xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).WrapText = true; xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; RowCnt++; int DataStart = RowCnt; foreach (DataRow aRow in aTable.Rows) { if (ByDDMMYYYY == 0) //BY DAY { xlWorkSheet.Cells[RowCnt, 1] = aRow["Day"].ToString() + '\\' + aRow["Month"].ToString() + "\\" + aRow["Year"].ToString(); } else if (ByDDMMYYYY == 1)//BY MONTH { xlWorkSheet.Cells[RowCnt, 1] = aRow["Month"].ToString() + '\\' + aRow["Year"].ToString(); } else //BY YEAR { xlWorkSheet.Cells[RowCnt, 1] = aRow["Year"].ToString(); } xlWorkSheet.Cells[RowCnt, 2] = Math.Round(double.Parse(aRow["TotalCost"].ToString()), 2); xlWorkSheet.Cells[RowCnt, 3] = Math.Round(double.Parse(aRow["TotalPrice"].ToString()), 2); xlWorkSheet.Cells[RowCnt, 4] = Math.Round(double.Parse(aRow["TotalProfit"].ToString()), 2); RowCnt++; } xlWorkSheet.get_Range("A" + DataStart.ToString(), "K" + RowCnt.ToString()).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; xlWorkSheet.get_Range("A" + DataStart.ToString(), "K" + RowCnt.ToString()).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; oRng = xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()); oRng.EntireColumn.AutoFit(); Excel.ChartObjects myCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); xlWorkSheet.DisplayRightToLeft = false; int size = aTable.Rows.Count * 100; if (size > 600) { size = 600; } if (size < 300) { size = 300; } Excel.ChartObject myChart = (Excel.ChartObject)myCharts.Add(0, RowCnt * 15, size, 300); Excel.Chart chartPage = myChart.Chart; Excel.SeriesCollection seriesCollection = chartPage.SeriesCollection(); Excel.Series series1 = seriesCollection.NewSeries(); Excel.Series series2 = seriesCollection.NewSeries(); Excel.Series series3 = seriesCollection.NewSeries(); RowCnt--; //because we started from 1 suppose to be 0 series1.Name = "Total Cost"; series1.XValues = xlWorkSheet.Range["A" + DataStart.ToString(), "A" + RowCnt.ToString()]; series1.Values = xlWorkSheet.Range["B" + DataStart.ToString(), "B" + RowCnt.ToString()]; series2.Name = "Total Sales"; series2.XValues = xlWorkSheet.Range["A" + DataStart.ToString(), "A" + RowCnt.ToString()]; series2.Values = xlWorkSheet.Range["C" + DataStart.ToString(), "C" + RowCnt.ToString()]; series3.Name = "Gross Profit"; series3.XValues = xlWorkSheet.Range["A" + DataStart.ToString(), "A" + RowCnt.ToString()]; series3.Values = xlWorkSheet.Range["D" + DataStart.ToString(), "D" + RowCnt.ToString()]; chartPage.ChartType = Excel.XlChartType.xlLineMarkers; Excel.Axis axis = chartPage.Axes(Excel.XlAxisType.xlValue, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary) as Excel.Axis; series1.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowBubbleSizes); series2.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowBubbleSizes); series3.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowBubbleSizes); object format = Microsoft.Office.Interop.Excel.XlFileFormat.xlHtml; xlWorkSheet.SaveAs(ReportsHelper.TempOutputPath, format); if (ExportToExcel) { format = Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7; xlWorkSheet.SaveAs(ExportPath, format); } xlApp.UserControl = false; //Once done close and quit Excel return("TRUE"); } else { return("EMPTY"); } } catch (Exception ex) { MessageBox.Show("{EXCEPTION in Revenues Comparison}" + ex.Message + ex.ToString()); return("ERROR " + ex.Message); } finally { //Once done close and quit Excel xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); } }
//формируем отчет в ексель по методу паттерна private void Client_GetListExpertiseReportCompleted(object sender, ServiceReference1.GetListExpertiseReportCompletedEventArgs e) { if (e.Error == null) { Excel.Application xlApp = new Excel.Application(); if (xlApp == null) { MessageBox.Show("Excel is not properly installed!!"); return; } Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet = null; object misValue = System.Reflection.Missing.Value; xlWorkBook = xlApp.Workbooks.Add(); var experts = e.Result.list_marks.GroupBy(o => o.id_expert).ToList(); int count_project = e.Result.list_marks.GroupBy(o => o.id_project).ToList().Count(); var count_criterion = e.Result.list_marks.GroupBy(o => o.id_criterion).ToList().Count(); for (int i = 0; i < experts.Count(); i++) { int id_expert = experts[i].Key; if (i == 0) { xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); xlWorkSheet.Name = "Отчет эксперта " + e.Result.list_marks.Where(o => o.id_expert == id_expert).FirstOrDefault().experts.first_name; //e.Result.list_marks.Where(o => o.id_expert == id_expert).FirstOrDefault().experts.first_name + " " + e.Result.list_marks.Where(o => o.id_expert == id_expert).FirstOrDefault().experts.second_name + " " + e.Result.list_marks.Where(o => o.id_expert == id_expert).FirstOrDefault().experts.patronymic; } else { xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.Add(Type.Missing, xlWorkSheet, Type.Missing, Type.Missing); xlWorkSheet.Name = "Отчет эксперта " + e.Result.list_marks.Where(o => o.id_expert == id_expert).FirstOrDefault().experts.first_name; } var ls_result_expert = e.Result.list_res_ex.Where(o => o.id_expert == id_expert).OrderBy(o => o.id_project).ToList(); var ls_marks = e.Result.list_marks.Where(o => o.id_expert == id_expert).ToList(); var ls_criterion = e.Result.list_marks.Where(o => o.id_expert == id_expert).OrderBy(o => o.id_criterion).GroupBy(o => o.id_criterion).ToList(); for (int j = 0; j < ls_criterion.Count(); j++) { int id_criterion = ls_criterion[j].Key; if (j == 0) { xlWorkSheet.Cells[1, 1] = "Критерии"; var projects = ls_marks.OrderBy(o => o.id_project).GroupBy(o => o.id_project).ToList(); for (int k = 0; k < projects.Count(); k++) { string name_project = ls_marks.Where(o => o.id_project == projects[k].Key).FirstOrDefault().projects.name; xlWorkSheet.Cells[1, k + 1 + 1] = name_project; } } string name_criterion = ls_marks.Where(o => o.id_criterion == id_criterion).FirstOrDefault().criterions.name; var values = ls_marks.Where(o => o.id_criterion == id_criterion).OrderBy(o => o.id_project).ToList(); for (int k = 0; k < values.Count(); k++) { xlWorkSheet.Cells[j + 2, 2 + k] = Math.Round(values[k].value * 100); } xlWorkSheet.Cells[j + 2, 1] = name_criterion; } xlWorkSheet.Cells[count_criterion + 2, 1] = "ИТОГ"; for (int j = 0; j < ls_result_expert.Count(); j++) { xlWorkSheet.Cells[count_criterion + 2, j + 2] = Math.Round(ls_result_expert[j].value * 100); } xlWorkSheet.Columns.AutoFit(); //строим диаграмму Excel.ChartObjects _xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject _myChart = (Excel.ChartObject)_xlCharts.Add(10, 80, 300, 250); Excel.Chart _chartPage = _myChart.Chart; SeriesCollection _seriesCollection = (SeriesCollection)_chartPage.SeriesCollection(Type.Missing); Series _series = _seriesCollection.NewSeries(); _series.XValues = xlWorkSheet.get_Range("B1", xlWorkSheet.Cells[1, 1 + count_project] as Range); _series.Values = xlWorkSheet.get_Range(xlWorkSheet.Cells[2 + count_criterion, 2] as Range, xlWorkSheet.Cells[2 + count_criterion, 1 + count_project] as Range); _chartPage.ApplyLayout(2, _chartPage.ChartType); _chartPage.ChartTitle.Text = "Отчет(" + e.Result.list_marks.Where(o => o.id_expert == id_expert).FirstOrDefault().experts.first_name + ")"; //chartPage.ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowValue, false, true, false, false, false, false, true, false, false); _chartPage.ChartType = Excel.XlChartType.xlPie; } //строим итоговую таблицу xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.Add(Type.Missing, xlWorkSheet, Type.Missing, Type.Missing); xlWorkSheet.Name = "Отчет общий"; var ls_expertise_crt = e.Result.list_res_ex_cr.ToList(); for (int i = 0; i < ls_expertise_crt.Count(); i++) { xlApp.Columns.AutoFit(); var ls_criterion = ls_expertise_crt.OrderBy(o => o.id_criterion).GroupBy(o => o.id_criterion).ToList(); for (int j = 0; j < ls_criterion.Count(); j++) { int id_criterion = ls_criterion[j].Key; if (j == 0) { xlWorkSheet.Cells[1, 1] = "Критерии"; var projects = ls_expertise_crt.OrderBy(o => o.id_project).GroupBy(o => o.id_project).ToList(); for (int k = 0; k < projects.Count(); k++) { string name_project = ls_expertise_crt.Where(o => o.id_project == projects[k].Key).FirstOrDefault().projects.name; xlWorkSheet.Cells[1, k + 1 + 1] = name_project; } } string name_criterion = ls_expertise_crt.Where(o => o.id_criterion == id_criterion).FirstOrDefault().criterions.name; var values = ls_expertise_crt.Where(o => o.id_criterion == id_criterion).OrderBy(o => o.id_project).ToList(); for (int k = 0; k < values.Count(); k++) { xlWorkSheet.Cells[j + 2, 2 + k] = Math.Round(values[k].value * 100); } xlWorkSheet.Cells[j + 2, 1] = name_criterion; } var ls_result_all = e.Result.list_res_exppertise.ToList(); xlWorkSheet.Cells[count_criterion + 2, 1] = "ИТОГ"; for (int j = 0; j < ls_result_all.Count(); j++) { xlWorkSheet.Cells[count_criterion + 2, j + 2] = Math.Round(ls_result_all[j].value * 100); } } Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, count_criterion * 25, 300, 250); Excel.Chart chartPage = myChart.Chart; SeriesCollection seriesCollection = (SeriesCollection)chartPage.SeriesCollection(Type.Missing); Series series = seriesCollection.NewSeries(); series.XValues = xlWorkSheet.get_Range("B1", xlWorkSheet.Cells[1, 1 + count_project] as Range); series.Values = xlWorkSheet.get_Range(xlWorkSheet.Cells[2 + count_criterion, 2] as Range, xlWorkSheet.Cells[2 + count_criterion, 1 + count_project] as Range); chartPage.ApplyLayout(2, chartPage.ChartType); chartPage.ChartTitle.Text = "Отчет(Общий)"; //chartPage.ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowValue, false, true, false, false, false, false, true, false, false); chartPage.ChartType = Excel.XlChartType.xlPie; SaveFileDialog sFile = new SaveFileDialog(); sFile.Filter = "Excel files (*.xls)|*.xls|All files (*.*)|*.*"; try { if (sFile.ShowDialog() == true) { string path = sFile.FileName; xlWorkBook.Application.DisplayAlerts = false; //MessageBox.Show(path); xlWorkBook.SaveAs(path, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); //xlApp.Visible = true; xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); MessageBox.Show("Ексель файл создан. Вы можете найти его по пути " + path); } } catch (Exception ex) { MessageBox.Show("Error", ex.Message); } //xlWorkBook.Close(false, false, false); //xlApp.Quit(); Marshal.ReleaseComObject(xlWorkSheet); Marshal.ReleaseComObject(xlWorkBook); Marshal.ReleaseComObject(xlApp); GC.Collect(); GC.WaitForPendingFinalizers(); } else { MessageBox.Show(e.Error.Message); } cLoading.stop(); }
public void Document_Create(Document_Type type, Document_Format format, string name, DataTable table) { Configuration_class configuration_Class = new Configuration_class(); configuration_Class.Document_Configuration_Get(); switch (name != "" || name != null) { case true: switch (format) { case Document_Format.Word: word.Application application = new word.Application(); word.Document document = application.Documents.Add(Visible: true); try { word.Range range = document.Range(0, 0); document.Sections.PageSetup.LeftMargin = application.CentimetersToPoints((float)Configuration_class.doc_Left_Merge); document.Sections.PageSetup.RightMargin = application.CentimetersToPoints((float)Configuration_class.doc_Right_Merge); document.Sections.PageSetup.TopMargin = application.CentimetersToPoints((float)Configuration_class.doc_Top_Merge); document.Sections.PageSetup.BottomMargin = application.CentimetersToPoints((float)Configuration_class.doc_Bottom_Merge); range.Text = Configuration_class.Organiztion_Name; range.ParagraphFormat.Alignment = word.WdParagraphAlignment.wdAlignParagraphCenter; range.ParagraphFormat.SpaceAfter = 1; range.ParagraphFormat.SpaceBefore = 1; range.ParagraphFormat.LineSpacingRule = word.WdLineSpacing.wdLineSpaceSingle; range.Font.Name = "Times New Roman"; range.Font.Size = 12; document.Paragraphs.Add(); document.Paragraphs.Add(); document.Paragraphs.Add(); word.Paragraph Document_Name = document.Paragraphs.Add(); Document_Name.Format.Alignment = word.WdParagraphAlignment.wdAlignParagraphCenter; Document_Name.Range.Font.Name = "Times New Roman"; Document_Name.Range.Font.Size = 16; switch (type) { case Document_Type.Report: Document_Name.Range.Text = "ОТЧЁТ"; break; case Document_Type Statistic: Document_Name.Range.Text = "СТАТИСТИЧЕСКИЙ ОТЧЁТ"; break; } document.Paragraphs.Add(); document.Paragraphs.Add(); document.Paragraphs.Add(); word.Paragraph statparg = document.Paragraphs.Add(); word.Table stat_table = document.Tables.Add(statparg.Range, table.Rows.Count, table.Columns.Count); stat_table.Borders.InsideLineStyle = word.WdLineStyle.wdLineStyleSingle; stat_table.Borders.OutsideLineStyle = word.WdLineStyle.wdLineStyleSingle; stat_table.Rows.Alignment = word.WdRowAlignment.wdAlignRowCenter; stat_table.Range.Cells.VerticalAlignment = word.WdCellVerticalAlignment.wdCellAlignVerticalCenter; stat_table.Range.Font.Size = 12; stat_table.Range.Font.Name = "Times New Roman"; for (int row = 1; row <= table.Rows.Count; row++) { for (int col = 1; col <= table.Columns.Count; col++) { stat_table.Cell(row, col).Range.Text = table.Rows[row - 1][col - 1].ToString(); } } document.Paragraphs.Add(); document.Paragraphs.Add(); word.Paragraph Footparg = document.Paragraphs.Add(); Footparg.Range.Text = string.Format("Дата создания \t\t\t{0}", DateTime.Now.ToString("dd.MM.yyyy")); } catch { } finally { switch (format) { case Document_Format.Word: document.SaveAs2(name, word.WdSaveFormat.wdFormatDocument); break; case Document_Format.PDF: document.SaveAs2(name, word.WdSaveFormat.wdFormatPDF); break; } document.Close(); application.Quit(); } break; case Document_Format.Excel: excel.Application application_ex = new excel.Application(); excel.Workbook workbook = application_ex.Workbooks.Add(); excel.Worksheet worksheet = (excel.Worksheet)workbook.ActiveSheet(); try { switch (type) { case Document_Type.Report: worksheet.Name = "Отчет"; for (int row = 0; row < table.Rows.Count; row++) { for (int col = 0; col < table.Columns.Count; col++) { worksheet.Cells[row + 1][col + 1] = table.Rows[row][col].ToString(); } } excel.Range border = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[worksheet.Rows.Count + 1][table.Columns.Count + 1]]; border.Borders.LineStyle = excel.XlLineStyle.xlContinuous; border.VerticalAlignment = excel.XlHAlign.xlHAlignCenter; border.HorizontalAlignment = excel.XlHAlign.xlHAlignCenter; worksheet.Cells[table.Rows.Count + 3][2] = string.Format("Дата создания {0}", DateTime.Now.ToString()); worksheet.Range[worksheet.Cells[table.Rows.Count + 3, 2], worksheet.Cells[table.Rows.Count + 2, table.Columns.Count + 2]].Merge(); break; case Document_Type.Statistic: worksheet.Name = "Статистический отчет"; for (int row = 0; row < table.Rows.Count; row++) { for (int col = 0; col < table.Columns.Count; col++) { worksheet.Cells[row + 1][col + 1] = table.Rows[row][col].ToString(); } } excel.Range border1 = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[worksheet.Rows.Count + 1][table.Columns.Count + 1]]; border1.Borders.LineStyle = excel.XlLineStyle.xlContinuous; border1.VerticalAlignment = excel.XlHAlign.xlHAlignCenter; border1.HorizontalAlignment = excel.XlHAlign.xlHAlignCenter; worksheet.Cells[table.Rows.Count + 3][2] = string.Format("Дата создания {0}", DateTime.Now.ToString()); excel.ChartObjects chartObjects = (excel.ChartObjects)worksheet.ChartObjects(Type.Missing); excel.ChartObject chartObject = chartObjects.Add(300, 50, 250, 250); excel.Chart chart = chartObject.Chart; excel.SeriesCollection seriesCollection = (excel.SeriesCollection)chart.SeriesCollection(Type.Missing); excel.Series series = seriesCollection.NewSeries(); chart.ChartType = excel.XlChartType.xl3DColumn; series.XValues = worksheet.get_Range("B2", "B" + table.Rows.Count + 1); series.Values = worksheet.get_Range("C2", "C" + table.Rows.Count + 1); break; } } catch { } finally { workbook.SaveAs(name, application_ex.DefaultSaveFormat); workbook.Close(); application_ex.Quit(); } break; } break; case false: System.Windows.Forms.MessageBox.Show("Введите название документа"); break; } }
public string FastMoveItems(bool FastOrSlowReport, int NumberOfItems, string DateFrom, string DateTo, bool ExportToExcel = false, string ExportPath = "") { try { Excel.Range chartRange; xlApp = new Excel.Application(); xlApp.DisplayAlerts = false; xlApp.Visible = false; xlApp.SheetsInNewWorkbook = 1; xlWorkBook = xlApp.Workbooks.Add(misValue);//misValue string ReportName = (FastOrSlowReport ? "Fast Move Items Report" : "Slow Move Items Report"); DataTable aTable = ReportsMgmt.FastMovItemBasedOnQty(NumberOfItems, DateFrom, DateTo, FastOrSlowReport); if (aTable.Rows.Count > 0) { int RowCnt = 1; xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); xlWorkSheet.Name = ReportName; List <string> aHeader = ReportsHelper.ImportReportHeader(0, 1); List <string> aFooter = ReportsHelper.ImportReportHeader(1, 1); for (int i = 0; i < aHeader.Count; i++) { string astringss = aHeader[i]; xlWorkSheet.Cells[RowCnt, 2] = aHeader[i]; RowCnt++; } xlWorkSheet.Cells[RowCnt++, 1] = ReportName; xlWorkSheet.Cells[RowCnt, 2] = "Date From:\t" + DateFrom; xlWorkSheet.Cells[RowCnt, 3] = "Date To:\t" + DateTo; RowCnt++; xlWorkSheet.Cells[RowCnt, 1] = "Item Description"; xlWorkSheet.Cells[RowCnt, 2] = "Qty Sold"; xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).Font.Bold = true; //xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).WrapText = true; xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; RowCnt++; int DataStart = RowCnt; foreach (DataRow aRow in aTable.Rows) { xlWorkSheet.Cells[RowCnt, 1] = aRow["ItemDescription"].ToString(); xlWorkSheet.Cells[RowCnt, 2] = aRow["Summation"].ToString(); RowCnt++; } xlWorkSheet.get_Range("A" + DataStart.ToString(), "K" + RowCnt.ToString()).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; xlWorkSheet.get_Range("A" + DataStart.ToString(), "K" + RowCnt.ToString()).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; oRng = xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()); oRng.EntireColumn.AutoFit(); Excel.ChartObjects myCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); xlWorkSheet.DisplayRightToLeft = false; int size = aTable.Rows.Count * 100; if (size >= 600) { size = 600; } if (size < 300) { size = 300; } Excel.ChartObject myChart = (Excel.ChartObject)myCharts.Add(0, RowCnt * 15, size, 300); Excel.Chart chartPage = myChart.Chart; Excel.SeriesCollection seriesCollection = chartPage.SeriesCollection(); Excel.Series series1 = seriesCollection.NewSeries(); RowCnt--; //because we started from 1 suppose to be 0 series1.Name = ReportName; series1.XValues = xlWorkSheet.Range["A" + DataStart.ToString(), "A" + RowCnt.ToString()]; series1.Values = xlWorkSheet.Range["B" + DataStart.ToString(), "B" + RowCnt.ToString()]; chartPage.ChartType = Excel.XlChartType.xlColumnClustered; Excel.Axis axis = chartPage.Axes(Excel.XlAxisType.xlValue, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary) as Excel.Axis; series1.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowBubbleSizes); object format = Microsoft.Office.Interop.Excel.XlFileFormat.xlHtml; xlWorkSheet.SaveAs(ReportsHelper.TempOutputPath, format); if (ExportToExcel) { format = Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7; xlWorkSheet.SaveAs(ExportPath, format); } xlApp.UserControl = false; return("TRUE"); } else { return("EMPTY"); } } catch (Exception ex) { MessageBox.Show(ex.ToString()); return("ERROR"); } finally { //Once done close and quit Excel xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); } }
public void CreateChart(string pivotTableName, int chartTop, string cTitle) { Excel.Worksheet activeSheet = null; Excel.Range selectedRange = null; Excel.Shapes shapes = null; Excel.Chart chart = null; Excel.ChartTitle chartTitle = null; try { activeSheet = (Excel.Worksheet)(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveSheet; Excel.Range rangePivot; rangePivot = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A1", "AA20000"); selectedRange = IdentifyPivotRangesByName(pivotTableName); shapes = activeSheet.Shapes; // Width original 255 if (Globals.ExcelVersion == "15.0" || Globals.ExcelVersion == "16.0") { // 204 is a nice style with shadow shapes.AddChart2(Style: 259, XlChartType: Excel.XlChartType.xlColumnClustered, Left: 10, Top: chartTop, Width: 450, Height: 210, NewLayout: true).Select(); } else { shapes.AddChart(XlChartType: Excel.XlChartType.xlColumnClustered, Left: 10, Top: chartTop, Width: 450, Height: 210).Select(); } chart = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveChart; chart.HasTitle = true; chart.ChartTitle.Text = cTitle; chart.ChartTitle.Format.TextFrame2.TextRange.Font.Caps = Microsoft.Office.Core.MsoTextCaps.msoNoCaps; chart.ChartArea.Interior.Color = System.Drawing.Color.FromArgb(242, 244, 244); // Change chart to light gray // chart.ChartArea.Interior.Color = System.Drawing.Color.FromRgb(0, 255, 0); // chart.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowValue, true, true, true, true, true, true, true, true, true); // Turn on data labels chart.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowValue, true, true, true, false, false, true, true, true, true); // Turn on data labels chart.SetSourceData(selectedRange); chart.HasLegend = false; chart.ApplyDataLabels(); if (Globals.ExcelVersion == "15.0" || Globals.ExcelVersion == "16.0") { chart.FullSeriesCollection(1).DataLabels.ShowValue = true; } else { chart.SeriesCollection(1).DataLabels.ShowValue = true; } if (pivotTableName == "PivotTableApplicationName") { chart.Axes(Excel.XlAxisType.xlCategory).TickLabels.Orientation = Excel.XlOrientation.xlUpward; } selectedRange.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; selectedRange.Borders[Excel.XlBordersIndex.xlEdgeRight].ColorIndex = 0; selectedRange.Borders[Excel.XlBordersIndex.xlEdgeRight].TintAndShade = 0; selectedRange.Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThin; selectedRange.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; selectedRange.Borders[Excel.XlBordersIndex.xlEdgeLeft].ColorIndex = 0; selectedRange.Borders[Excel.XlBordersIndex.xlEdgeLeft].TintAndShade = 0; selectedRange.Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThin; Globals.ChartBottom = (int)chart.ChartArea.Top + (int)chart.ChartArea.Height + 15; (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A2", "A2").Select(); // chart.Export(pivotTableName + ".png"); } catch (Exception ex) { // ex.Data.Add("ExcelHelper", "Error occurred in the CreateChart() method"); ex.Data.Add("ExcelHelper", " Error occurred in the CreateChart() method\r\n" + ex.Message); throw; } finally { if (chartTitle != null) { Marshal.ReleaseComObject(chartTitle); } if (chart != null) { Marshal.ReleaseComObject(chart); } if (shapes != null) { Marshal.ReleaseComObject(shapes); } if (selectedRange != null) { Marshal.ReleaseComObject(selectedRange); } } }
// Generates an excel spreadsheet with graphs of each summary element by depth and saves each graph as a PNG file. public static void CreateGTGraphs(string domainName, string timeStamp, int depth, List <List <Tuple <String, String> > > summaries) { // The top level log directory. string directory = Parser.GetTopDirectory() + @"GameTrees\" + domainName + @"\" + timeStamp + @"\"; // The graph image directory. string imageDir = directory + @"graphs\"; // Check if the image directory exists. if (!File.Exists(imageDir)) { // If not, create it. Directory.CreateDirectory(imageDir); } // This is needed for a lot of the Excel initialization tasks. object misValue = System.Reflection.Missing.Value; // Create an Excel application object. Excel.Application xl = new Excel.Application(); // Create an Excel work sheet variable. Excel.Worksheet xlWorkSheet; // Create an Excel work books variable and point it at the application object's work books.. Excel.Workbooks xlWorkBooks = xl.Workbooks; // Make the Excel application visible? Not sure, it's magic. xl.Visible = true; // Open the summary CSV file in the Excel work book. xlWorkBooks.OpenText(directory + "summary.csv", misValue, misValue, Excel.XlTextParsingType.xlDelimited, Excel.XlTextQualifier.xlTextQualifierNone, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue); // Grab the work sheet that represents the CSV file. xlWorkSheet = (Excel.Worksheet)xlWorkBooks[1].Worksheets.get_Item(1); // Loop through every summary element, excluding the depth count which should be first. for (int summary = 1; summary < summaries[0].Count; summary++) { // Create a new Excel chart holder in the work sheet. Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); // Create a new Excel chart and position it below the table and other charts. Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, (15 * summaries[0].Count) + (220 * (summary - 1)), 360, 210); // Select the chart object's chart. Don't ask me, this Excel interface is strange. Excel.Chart chartPage = myChart.Chart; // Make the chart object active. myChart.Select(); // Set the chart's style. 227 should be a white background with a blue line. chartPage.ChartStyle = 227; // Set the type of chart. We are using a line chart. chartPage.ChartType = Excel.XlChartType.xlLine; // Turn the legend off. chartPage.HasLegend = false; // Create a new series collection to hold the series that will contain the chart data. Again, Excel's interface is dumb. Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chartPage.SeriesCollection(); // Create a new series. Excel.Series series1 = seriesCollection.NewSeries(); // Select the chart's X values. These should be the depth counts. series1.XValues = xlWorkSheet.Range["summary!$A$2:$A$" + depth]; // Select the chart's Y values. These should be the current summary element data. series1.Values = xlWorkSheet.Range["summary!$" + ToLetter(summary) + "$2:$" + ToLetter(summary) + "$" + depth]; // Name the chart according to the current summary element. series1.Name = summaries[0][summary].First; // Export the current chart as a PNG image. chartPage.Export(imageDir + series1.Name + ".png", "PNG", false); } // Save the current work book as an XLS file. xlWorkBooks[1].SaveAs(directory + "graphsummary-" + timeStamp + ".xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); // Close the current work book. xlWorkBooks[1].Close(true, misValue, misValue); // Quit the Excel application. xl.Quit(); // Do some garbage collection? Not sure, all examples I saw had this. releaseObject(xlWorkSheet); releaseObject(xlWorkBooks); releaseObject(xl); }