Exemple #1
0
        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;
        }
Exemple #2
0
        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);
        }
Exemple #5
0
        /// <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();
                }
            }
        }
Exemple #6
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);
        }
        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%";
                }
            }
        }
Exemple #8
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;
                }
            }
        }
Exemple #13
0
        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)
            {
            }
        }
Exemple #14
0
        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";
                }
            }
        }
Exemple #15
0
        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());
            }
        }
Exemple #16
0
        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();
        }
Exemple #18
0
        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 не найден"); }
        }
Exemple #19
0
        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;
        }
Exemple #20
0
        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();
        }
Exemple #21
0
        /// <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);
        }
Exemple #22
0
        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;
        }
Exemple #23
0
        /// <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";
        }
Exemple #25
0
        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();
        }
Exemple #26
0
        //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();
        }
Exemple #28
0
        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;
            }
        }
Exemple #29
0
        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);
            }
        }
Exemple #30
0
        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);
                }
            }
        }
Exemple #31
0
        // 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);
        }