Esempio n. 1
0
        private void Openexcel_Click_1(object sender, EventArgs e)
        {
            List <int> processesbeforegen = getRunningProcesses();

            if (excelac.ShowDialog() == DialogResult.OK)
            {
                textBox3.Text = excelac.FileName;
                Excel.Application exc = new Excel.Application();
                {
                    exc.Visible = false;
                }
                exc.Workbooks.Open(textBox3.Text);
                Worksheet sheet = exc.Worksheets[1];
                sheet.Activate();

                foreach (Worksheet ws in exc.Worksheets)
                {
                    ChartObjects chartobjects = ws.ChartObjects();

                    foreach (ChartObject co in chartobjects)
                    {
                        co.Select();
                        Excel.Chart chart = co.Chart;
                        chart.Export(exportpath.SelectedPath + @"\" + chart.Name + ".png", "PNG", false);
                    }
                }
                progressBar1.Value = 40;
                MessageBox.Show("The graphs are imported successfully.");
                List <int> processesaftergen = getRunningProcesses();
                killProcesses(processesbeforegen, processesaftergen);
            }
        }
Esempio n. 2
0
        /// <summary>
        /// geterate thumbnail for a shape or a chart
        /// </summary>
        /// <param name="shape"> desired shape</param>
        /// <param name="path">path where the thumbnail will be created, excluding the .extension</param>
        /// <returns>full path of the created thumbnail</returns>
        private String GenerateThumbnail(Microsoft.Office.Interop.Excel.Chart shape, String path)
        {
            shape.Export(path + DocumentService.IMAGE_EXTENSION, DocumentService.IMAGE_FILTER, false);

            Bitmap bmp = new Bitmap(path + DocumentService.IMAGE_EXTENSION);

            int T_H, T_W;

            if (bmp.Width <= bmp.Height)
            {
                T_W = (int)(DocumentService.THUMB_WIDTH * bmp.Width / bmp.Height);
                T_H = DocumentService.THUMB_WIDTH;
            }
            else
            {
                T_H = (int)(DocumentService.THUMB_WIDTH * bmp.Height / bmp.Width);
                T_W = DocumentService.THUMB_WIDTH;
            }

            Size s = new Size(T_W, T_H);

            Bitmap bmp1 = new Bitmap(bmp, s);

            bmp.Dispose();

            FileInfo fi = new FileInfo(path + DocumentService.IMAGE_EXTENSION);

            fi.Delete();

            bmp1.Save(path + DocumentService.IMAGE_EXTENSION);

            return(path + DocumentService.IMAGE_EXTENSION);
        }
Esempio n. 3
0
        static void Main(string[] excel)
        {
            Excel.Application imgconverter = System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application") as Microsoft.Office.Interop.Excel.Application;

            ConsoleColor c = Console.ForegroundColor;

            Console.ForegroundColor = ConsoleColor.Red;
            Console.Write("Export To: ");
            Console.ForegroundColor = c;
            string exportPath = Console.ReadLine();

            if (exportPath == "")
            {
                exportPath = EXPORT_TO_DIRECTORY;
            }
            Excel.Workbook wb = imgconverter.ActiveWorkbook;

            foreach (Excel.Worksheet ws in wb.Worksheets)

            {
                Excel.ChartObjects chartobjects = (Excel.ChartObjects)(ws.ChartObjects(Type.Missing));

                foreach (Excel.ChartObject co in chartobjects)

                {
                    co.Select();
                    Excel.Chart chart = co.Chart;
                    chart.Export(exportPath + chart.Name + ".png", "PNG", false);
                }
            }

            Process.Start(exportPath);
        }
Esempio n. 4
0
        private static void ReportExcelToImage(String filePath, String[] columnList, String title, ChartType chartType = ChartType.COLUMN)
        {
            FileInfo file      = new FileInfo(filePath);
            String   directory = "";

            if (Constant.ANALYZE_CHART_DIR.Equals(""))
            {
                directory = file.DirectoryName;
            }
            else
            {
                directory = Constant.ANALYZE_CHART_DIR;
            }

            String fileName  = file.Name.Substring(0, file.Name.IndexOf('.'));
            String imagePath = directory + @"\" + fileName + "-" + title + ".jpg";

            Excel.Application xlApp;
            Excel.Workbook    xlWorkBook;
            Excel.Worksheet   xlWorkSheet;
            object            misValue = System.Reflection.Missing.Value;

            xlApp       = new Excel.Application();
            xlWorkBook  = xlApp.Workbooks.Open(filePath);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            Excel.Range chartRange;

            Excel.ChartObjects xlCharts  = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
            Excel.ChartObject  myChart   = (Excel.ChartObject)xlCharts.Add(10, 80, 1000, 850);
            Excel.Chart        chartPage = myChart.Chart;

            //non-empty count of csv file
            int count = Convert.ToInt32(xlApp.WorksheetFunction.CountA(xlWorkSheet.get_Range("A:A")));
            //non-empty count of header
            int headerCount = Convert.ToInt32(xlApp.WorksheetFunction.CountA(xlWorkSheet.Rows[1]));

            string chartString = "A1:A" + count;

            foreach (var column in columnList)
            {
                int columnIndex = GetColumnIndex(xlWorkSheet, headerCount, column);
                chartString += "," + GetString(columnIndex) + "1:" + GetString(columnIndex) + count;
            }
            chartRange = xlWorkSheet.get_Range(chartString);

            chartPage.SetSourceData(chartRange, misValue);
            //chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
            chartPage.ChartType = GetChartType(chartType);

            chartPage.Axes(Excel.XlAxisType.xlValue);
            //export chart as picture file
            chartPage.Export(imagePath, "JPG", misValue);

            xlWorkBook.Close(false, misValue, misValue);
            xlApp.Quit();

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);
        }
Esempio n. 5
0
        public void click_exportChart(Office.IRibbonControl control)
        {
            // Get Active Elements
            Excel.Chart    chart_active    = GetActiveChart();
            Excel.Workbook workbook_active = GetActiveWorkbook();

            if (chart_active != null && workbook_active != null)
            {
                // Save File Dialog
                SaveFileDialog dialog_saveFile = new SaveFileDialog();
                dialog_saveFile.Filter = "JPEG Image|*.jpg|Bitmap Image|*.bmp|GIF Image|*.gif|PNG Image|*.png|JPE Image|*.jpe";
                dialog_saveFile.Title  = "Export Chart As...";

                // Set Default File Name
                dialog_saveFile.FileName = chart_active.ChartTitle.Text;

                // Set Default Directory
                SetDefaultDirectory(dialog_saveFile, workbook_active);

                // Show Save File Dialog
                dialog_saveFile.ShowDialog();

                // Export Chart
                if (!String.IsNullOrEmpty(dialog_saveFile.FileName))
                {
                    chart_active.Export(dialog_saveFile.FileName);
                }
            }
        }
        private void ExportChart(Excel.Chart chart)
        {
            chart.Export($@"{destinationFolder}\{chartNumber} - {chart.ChartTitle.Text}.png", "PNG");
            chartNumber++;
            chart.Delete();

            ChartCreated?.Invoke(this, $"{chart.ChartTitle.Text} created");
        }
Esempio n. 7
0
        public void ExportCharts(string item, string folder, string format)
        {
            Excel.Worksheet    xlWorkSheet = (Excel.Worksheet)_xlWorkBook.Worksheets.Item[item];
            Excel.ChartObjects xlCharts    = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);

            foreach (Excel.ChartObject xlChart in xlCharts)
            {
                Excel.Chart chartPage = xlChart.Chart;
                chartPage.Export(Path.Combine(folder, $"{chartPage.Name}.{format}"), format, _misValue);
            }
        }
Esempio n. 8
0
        public void createTempChart()
        {
            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);

            xlWorkSheet.Cells[1, 2] = "a";
            xlWorkSheet.Cells[1, 3] = "b";

            Excel.Application xlApp1     = new Excel.Application();
            Excel.Workbook    xlWorkbook = xlApp1.Workbooks.Open
                                               (@"E:\temp.csv");
            Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
            Excel.Range      xlRange     = xlWorksheet.UsedRange;


            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", "C6");
            chartPage.SetSourceData(chartRange, misValue);
            chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
            // Export chart as picture file
            chartPage.Export(@"E:\temp_data.bmp", "BMP",
                             misValue);
            xlWorkBook.SaveAs("temp.csv",
                              Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue,
                              misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive,
                              misValue, misValue, misValue, misValue, misValue);
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();

            DeallocateObject(xlWorkSheet);
            DeallocateObject(xlWorkBook);
            DeallocateObject(xlApp);
            DeallocateObject(xlApp1);
        }
Esempio n. 9
0
        private void button14_Click(object sender, EventArgs e)
        {
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            excel.Visible = true;
            Workbook      wb    = excel.Workbooks.Add();
            Worksheet     sheet = wb.ActiveSheet;
            ListObject    tab1  = sheet.ListObjects.Add(XlListObjectSourceType.xlSrcRange, sheet.get_Range("A1:D4"));
            PublishObject po    = wb.PublishObjects.Add(XlSourceType.xlSourceRange, "e:\\1.htm", "Sheet1", "A1:D4", XlHtmlType.xlHtmlStatic, "wkbk_0009", "");

            Microsoft.Office.Interop.Excel.Shape shape = sheet.Shapes.AddChart(Microsoft.Office.Interop.Excel.XlChartType.xlPie);
            Microsoft.Office.Interop.Excel.Chart chart = shape.Chart;// (Microsoft.Office.Interop.Excel.Chart)
            chart.Export("e:\\11.png");
            //chart.ExportAsFixedFormat(XlFixedFormatType.xlTypeXPS, "e:\\111.xps");
            po.Publish(true);
            po.AutoRepublish = false;
            wb.Close(false);
            excel.Quit();
        }
Esempio n. 10
0
File: Form1.cs Progetto: a-vodka/ics
        private void button1_Click(object sender, EventArgs e)
        {
            Excel.Application excelApp = new Excel.Application();
            excelApp.Visible = true;

            Excel.Workbook  wb    = excelApp.Workbooks.Add();
            Excel.Worksheet sheet = wb.ActiveSheet;

//add data
            sheet.Cells[1, 1] = "";
            sheet.Cells[1, 2] = "Student1";
            sheet.Cells[1, 3] = "Student2";
            sheet.Cells[1, 4] = "Student3";
            sheet.Cells[2, 1] = "Term1";
            sheet.Cells[2, 2] = "80";
            sheet.Cells[2, 3] = "65";
            sheet.Cells[2, 4] = "45";
            sheet.Cells[3, 1] = "Term2";
            sheet.Cells[3, 2] = "78";
            sheet.Cells[3, 3] = "72";
            sheet.Cells[3, 4] = "60";
            sheet.Cells[4, 1] = "Term3";
            sheet.Cells[4, 2] = "82";
            sheet.Cells[4, 3] = "80";
            sheet.Cells[4, 4] = "65";
            sheet.Cells[5, 1] = "Term4";
            sheet.Cells[5, 2] = "75";
            sheet.Cells[5, 3] = "82";
            sheet.Cells[5, 4] = "68";

            Excel.Range        chartRange = sheet.get_Range("A1", "d5");
            Excel.ChartObjects xlCharts   = (Excel.ChartObjects)sheet.ChartObjects();
            Excel.ChartObject  myChart    = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);
            Excel.Chart        chartPage  = myChart.Chart;
            chartPage.ChartWizard(chartRange, Excel.XlChartType.xlColumnClustered, Title: "Diagram title");

            chartPage.Export(Application.StartupPath + @"./excel_chart_export.png", "png");
        }
Esempio n. 11
0
        private void button3_Click(object sender, EventArgs e) //Refresh/ get charts
        {
            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);

            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", "d5");
            chartPage.SetSourceData(chartRange, misValue);
            chartPage.ChartType = Excel.XlChartType.xlColumnClustered;

            //export chart as picture file
            chartPage.Export(@"C:\Dumpster\testcsv.bmp", "BMP", misValue);

            //  pictureBox1.Image = new Bitmap(@"C:\Dumpster\testcsv.bmp");

            xlWorkBook.SaveAs("csharp.net-informations.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);

            MessageBox.Show("Excel file created , you can find the file c:\\csharp-Excel.xls");
        }
Esempio n. 12
0
    //// 關閉Excel Message
    // Microsoft.Office.Interop.Excel.Application.DisplayAlerts = false;
    // Microsoft.Office.Interop.Excel.Application.Visible = false; // 設為true,則一開始就會顯現Excel檔.



    //   //ExlBook = ExlApp.Workbooks.Add(Server.MapPath(".") + "\\Tool_Prod_sample.xls");
    //    ExlBook = ExlApp.Workbooks.Add("c:\TAIWAN_BANK_OutSite_Salary_FA_20160111.xls");



    protected void Page_Load(object sender, EventArgs e)
    {
        // xl.Application ExlApp ;
        // ExlApp = new xl.ApplicationClass();
        // object missValue = System.Reflection.Missing.Value;

        // string path=@"c:\\TAIWAN_BANK_OutSite_Salary_FA_20160111.xls";
        // Workbook w = ExlApp.Workbooks.Open(path, 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);
        //// (path, 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 = (Microsoft.Office.Interop.Excel.Worksheet)w.Sheets["Sheet1"];
        // //ws.Protect(Contents: false);
        // Range r = ws.get_Range("B2","H20");

        // r.CopyPicture(XlPictureAppearance.xlScreen, XlCopyPictureFormat.xlBitmap);


        // Bitmap image = new Bitmap(Clipboard.GetImage());
        // image.Save(@"C:\abc\image.png");
        xl.Application xlApp;
        xl.Workbook    xlWorkBook;
        xl.Worksheet   xlWorkSheet;
        object         misValue = System.Reflection.Missing.Value;

        xlApp = new xl.Application();
        string path = @"c:\\abcd.xls";

        xlWorkBook = xlApp.Workbooks.Open(path, 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);

        //xlWorkBook = xlApp.Workbooks.Add(misValue);



        xlWorkSheet = (xl.Worksheet)xlWorkBook.Worksheets.get_Item(1);

        xl.Range xlRange;

        xlRange = xlWorkSheet.get_Range("A1", "d5");
        xlRange.CopyPicture(Microsoft.Office.Interop.Excel.XlPictureAppearance.xlScreen, Microsoft.Office.Interop.Excel.XlCopyPictureFormat.xlPicture);


        xl.ChartObjects xlCharts  = (xl.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
        xl.ChartObject  myChart   = (xl.ChartObject)xlCharts.Add(10, 80, 300, 250);
        xl.Chart        chartPage = myChart.Chart;
        xl.Range        chartRange;
        chartRange = xlWorkSheet.get_Range("A1", "d5");
        chartPage.SetSourceData(chartRange, misValue);
        chartPage.ChartType = xl.XlChartType.xlColumnClustered;

        //export chart as picture file
        //chartPage.Paste();

        chartPage.Export(@"C:\excel_chart_export.JPG", "JPG", misValue);
        xlCharts.Delete();
        //xlWorkBook.SaveAs(@"C:\excel_chart_export.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
        xlWorkBook.Close(true, misValue, misValue);
        xlApp.Quit();

        releaseObject(xlWorkSheet);
        releaseObject(xlWorkBook);
        releaseObject(xlApp);
    }
Esempio n. 13
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);
        }
        public static void CreateWord()
        {
            //杀掉winword.exe进程,加快执行速度,防止打开副本等Bug
            killWinWordProcess();

            //生成每个解决方案的图片
            if (HeatSourceLayoutApp.createScreen() == false)
            {
                return;
            }

            //当前表格数目
            int currentTableNum = 0;
            var dialog          = new SaveFileDialog("选择文件存储位置", "燃气热源方案比选", "docx", "SaveFile", SaveFileDialog.SaveFileDialogFlags.DefaultIsFolder);

            System.Windows.Forms.DialogResult result = dialog.ShowDialog();
            if (result != System.Windows.Forms.DialogResult.OK)
            {
                return;
            }
            object savePath = dialog.Filename;
            var    wordApp  = new Microsoft.Office.Interop.Word.Application();

#if DEBUG
            object templatePath = HeatSourceLayoutApp.CurrentDirectory + @"/Template.docx";
#else
            object templatePath = HeatSourceLayoutApp.CurrentDirectory + @"/Resource/Template.docx";
#endif
            //object savaPath = System.IO.Directory.GetCurrentDirectory() + @"./燃气热源方案比选.docx";
            object   miss = System.Reflection.Missing.Value;
            Document doc  = wordApp.Documents.Open(ref templatePath, ref miss, ref miss,
                                                   ref miss, ref miss, ref miss, ref miss,
                                                   ref miss, ref miss, ref miss, ref miss,
                                                   ref miss, ref miss, ref miss, ref miss,
                                                   ref miss);

            //解决方案数目solNum
            //每个解决方案有1 + 热源数*3 + 1 个表格
            int solNum = HeatSourceLayoutApp.solutions.Count;
            //基本信息表
            Microsoft.Office.Interop.Word.Table infoTable;
            //热源能耗表
            Microsoft.Office.Interop.Word.Table hsEnergyTable;
            //热源运行费用表
            Microsoft.Office.Interop.Word.Table hsOperationTable;
            //热源初投资(造价)表
            Microsoft.Office.Interop.Word.Table hsInvestTable;
            //工程总表
            Microsoft.Office.Interop.Word.Table totalTable;

            //根据书签定位到待插入方案的位置
            object oStart = "SolForInsert";
            Range  range  = doc.Bookmarks.get_Item(ref oStart).Range;
#if DEBUG
            string heatPart  = HeatSourceLayoutApp.CurrentDirectory + @"\heatPart.docx";
            string startPart = HeatSourceLayoutApp.CurrentDirectory + @"\startPart.docx";
            string endPart   = HeatSourceLayoutApp.CurrentDirectory + @"\endPart.docx";
            object heatFile  = HeatSourceLayoutApp.CurrentDirectory + @"\heatPart.docx";
            object startFile = HeatSourceLayoutApp.CurrentDirectory + @"\startPart.docx";
            object endFile   = HeatSourceLayoutApp.CurrentDirectory + @"\endPart.docx";
#else
            string heatPart  = HeatSourceLayoutApp.CurrentDirectory + @"\Resource\heatPart.docx";
            string startPart = HeatSourceLayoutApp.CurrentDirectory + @"\Resource\startPart.docx";
            string endPart   = HeatSourceLayoutApp.CurrentDirectory + @"\Resource\endPart.docx";
            object heatFile  = HeatSourceLayoutApp.CurrentDirectory + @"\Resource\heatPart.docx";
            object startFile = HeatSourceLayoutApp.CurrentDirectory + @"\Resource\startPart.docx";
            object endFile   = HeatSourceLayoutApp.CurrentDirectory + @"\Resource\endPart.docx";
#endif
            //首先根据模板文件Template.docx,插入表格
            //insertFile()是倒着插入
            for (int sl = solNum - 1; sl >= 0; sl--)
            {
                // insert a new line
                var pText = range.Paragraphs.Add();
                pText.Range.Text = String.Format("\n");
                pText.Range.InsertParagraphAfter();

                //插入解决方案的图片
                string imagePath        = HeatSourceLayoutApp.CurrentDirectory + "\\tmp\\" + sl + ".jpg";
                object linktofile       = false;
                object savewithdocument = true;
                Microsoft.Office.Interop.Word.InlineShape shape = wordApp.ActiveDocument.InlineShapes.AddPicture(imagePath,
                                                                                                                 ref linktofile, ref savewithdocument, range);
                shape.Range.ParagraphFormat.Alignment = Microsoft.Office.Interop.Word.WdParagraphAlignment.wdAlignParagraphCenter;

                //倒序插入,先插入末尾部分
                Document endPartFile = wordApp.Documents.Open(ref endFile, ref miss, ref miss,
                                                              ref miss, ref miss, ref miss, ref miss,
                                                              ref miss, ref miss, ref miss, ref miss,
                                                              ref miss, ref miss, ref miss, ref miss,
                                                              ref miss);
                object oEnd          = "EndPartPosition";
                object endPartIndex  = "EndPartIndex";
                Range  endRange      = endPartFile.Bookmarks.get_Item(ref oEnd).Range;
                Range  endIndexRange = endPartFile.Bookmarks.get_Item(ref endPartIndex).Range;
                endIndexRange.Text = (sl + 1).ToString();
                endRange.Text      = (sl + 1).ToString();
                range.InsertFile(endPart, ref miss, ref miss, ref miss);
                //不保存直接关闭
                object isSave = false;
                endPartFile.Close(ref isSave, ref miss, ref miss);
                currentTableNum++;

                //根据当前解决方案内的热源数目,插入热源部分
                int heatSourceNum = HeatSourceLayoutApp.solutions[sl].HeatProducers.Count;
                for (int h = heatSourceNum; h > 0; h--)
                {
                    Document heatPartFile = wordApp.Documents.Open(ref heatFile, ref miss, ref miss,
                                                                   ref miss, ref miss, ref miss, ref miss,
                                                                   ref miss, ref miss, ref miss, ref miss,
                                                                   ref miss, ref miss, ref miss, ref miss,
                                                                   ref miss);
                    object solutionIndex        = "SolutionIndex";
                    object heatProducerIndex    = "HeatProducerIndex";
                    object heatProducerPosition = "HeatProducerPosition";
                    Range  solRange             = heatPartFile.Bookmarks.get_Item(ref solutionIndex).Range;
                    Range  heatIndexRange       = heatPartFile.Bookmarks.get_Item(ref heatProducerIndex).Range;
                    Range  heatPositionRange    = heatPartFile.Bookmarks.get_Item(ref heatProducerPosition).Range;
                    solRange.Text          = (sl + 1).ToString();
                    heatIndexRange.Text    = h.ToString();
                    heatPositionRange.Text = h.ToString();
                    range.InsertFile(heatPart, ref miss, ref miss, ref miss);
                    heatPartFile.Close(ref isSave, ref miss, ref miss);
                    currentTableNum += 3;
                }

                //最后插入开头部分
                Document startPartFile = wordApp.Documents.Open(ref startFile, ref miss, ref miss,
                                                                ref miss, ref miss, ref miss, ref miss,
                                                                ref miss, ref miss, ref miss, ref miss,
                                                                ref miss, ref miss, ref miss, ref miss,
                                                                ref miss);
                object oStartPart   = "StartPartPosition";
                object oStartPart2  = "StartPartPosition_2";
                Range  startRange   = startPartFile.Bookmarks.get_Item(ref oStartPart).Range;
                Range  startRange_2 = startPartFile.Bookmarks.get_Item(ref oStartPart2).Range;
                startRange.Text   = (sl + 1).ToString();
                startRange_2.Text = (sl + 1).ToString();
                range.InsertFile(startPart, ref miss, ref miss, ref miss);
                startPartFile.Close(ref isSave, ref miss, ref miss);
                currentTableNum++;
            }

            ////填数据
            ////当前解决方案的下标是sl
            for (int sl = 0; sl < solNum; sl++)
            {
                //之前解决方案内热源数目
                int lastHsNum = 0;
                for (int k = 0; k < sl; k++)
                {
                    lastHsNum += HeatSourceLayoutApp.solutions[k].HeatProducers.Count;
                }
                //之前共有表格数
                int tableNum = 3 * lastHsNum + 2 * sl;


                //第一部分,基本信息表 6*2
                //报System.Runtime.InteropServices.COMException (0x800706BA): RPC 服务器不可用。
                infoTable = doc.Tables[tableNum + 1];
                //填充地点信息
                infoTable.Cell(1, 2).Range.Text = HeatSourceLayoutApp.globalProperty.CityName;
                //填充地区类型
                string typeString = Constants.LocationType[HeatSourceLayoutApp.globalProperty.LocationType];
                infoTable.Cell(2, 2).Range.Text = typeString;
                infoTable.Cell(2, 2).Range.Bold = 1;
                //填充气象参数
                //室外温度 t_(a.h) 室外均温 t_a 供暖天数 D
                double IndoorTemperature = HeatSourceLayoutApp.globalProperty.IndoorTemperature;
                double OutDoorTemp       = HeatSourceLayoutApp.globalProperty.OutDoorTemp;
                double OutAverageTemp    = HeatSourceLayoutApp.globalProperty.OutAverageTemp;
                double HeatingDays       = HeatSourceLayoutApp.globalProperty.HeatingDays;
                infoTable.Cell(3, 2).Range.Text = HeatingDays.ToString();
                infoTable.Cell(4, 2).Range.Text = IndoorTemperature.ToString();
                infoTable.Cell(5, 2).Range.Text = OutDoorTemp.ToString();
                infoTable.Cell(6, 2).Range.Text = OutAverageTemp.ToString();
                infoTable.Cell(3, 2).Range.Bold = 1;
                infoTable.Cell(4, 2).Range.Bold = 1;
                infoTable.Cell(5, 2).Range.Bold = 1;
                infoTable.Cell(6, 2).Range.Bold = 1;

                //第二部分,热源对应的三个表
                //当前解决方案内热源数目
                int hsNum = HeatSourceLayoutApp.solutions[sl].HeatProducers.Count;
                //当前解决方案供热总面积 全年总耗热量 总运行费用 总造价
                double totalArea = 0;
                double totalHeat = 0;
                for (int hs = 0; hs < hsNum; hs++)
                {
                    //1 - 能耗计算
                    //根据热源包含的楼房数目添加行数
                    hsEnergyTable = doc.Tables[tableNum + 1 + 1 + 3 * hs];
                    int directBuildingNum = 0;
                    int slaveBuildingNum  = 0;
                    //记录热力站中的buildings
                    List <Building> substationBuildings = new List <Building>();
                    if (HeatSourceLayoutApp.solutions[sl].HeatProducers.ElementAt(hs).Value.OwnSlaveDistrict != null)
                    {
                        foreach (var sub in HeatSourceLayoutApp.solutions[sl].HeatProducers.ElementAt(hs).Value.OwnSlaveDistrict.SubStations)
                        {
                            if (sub.OwnMasterDistrict != null)
                            {
                                foreach (var b in sub.OwnMasterDistrict.Buildings)
                                {
                                    substationBuildings.Add(b);
                                }
                            }
                        }
                    }
                    slaveBuildingNum = substationBuildings.Count;

                    //如果不含有OwnSlaveDistrict,报NullRefException
                    try
                    {
                        directBuildingNum = HeatSourceLayoutApp.solutions[sl].HeatProducers.ElementAt(hs).Value.OwnMasterDistrict.Buildings.Count;
                    }
                    catch (Exception ex)
                    {
                        ex.ToString();
                    }
                    //填充热源中楼房信息,从第2行开始,依次为
                    //楼号-建筑类型-面积-供暖方式-采暖热指标-采暖热负荷-节能措施-直接全年耗热量
                    int totalBuildingNum = directBuildingNum + slaveBuildingNum;
                    int lineIndex        = 2;
                    for (int b = 0; b < directBuildingNum; b++)
                    {
                        Building building = HeatSourceLayoutApp.solutions[sl].HeatProducers.ElementAt(hs).Value.OwnMasterDistrict.Buildings[b];
                        hsEnergyTable.Rows.Add(ref miss);
                        hsEnergyTable.Cell(lineIndex, 1).Range.Text = (b + 1).ToString();
                        //建筑类型
                        string typeStr = Constants.BuildingType[building.BuildingType];
                        hsEnergyTable.Cell(lineIndex, 2).Range.Text = typeStr;
                        hsEnergyTable.Cell(lineIndex, 3).Range.Text = building.Area.ToString();
                        //供暖方式
                        string styleStr = Constants.HeatStyle[building.HeatStyle];
                        hsEnergyTable.Cell(lineIndex, 4).Range.Text = styleStr;
                        hsEnergyTable.Cell(lineIndex, 5).Range.Text = building.HeatIndex.ToString();
                        hsEnergyTable.Cell(lineIndex, 6).Range.Text = building.HeatLoad.ToString();
                        //节能措施
                        string eneStyleStr = Constants.SaveStyle[building.EnergySavingStyle];
                        hsEnergyTable.Cell(lineIndex, 7).Range.Text = eneStyleStr;
                        hsEnergyTable.Cell(lineIndex, 8).Range.Text = building.YearHeat.ToString();

                        lineIndex++;
                    }
                    //如果热源中包含热力站,把热力站中的楼房也加进去
                    for (int b = 0; b < slaveBuildingNum; b++)
                    {
                        Building building = substationBuildings[b];
                        hsEnergyTable.Rows.Add(ref miss);
                        hsEnergyTable.Cell(lineIndex, 1).Range.Text = (b + 1).ToString();
                        //建筑类型
                        string typeStr = Constants.BuildingType[building.BuildingType];
                        hsEnergyTable.Cell(lineIndex, 2).Range.Text = typeStr;
                        hsEnergyTable.Cell(lineIndex, 3).Range.Text = building.Area.ToString();
                        //供暖方式
                        string styleStr = Constants.HeatStyle[building.HeatStyle];
                        hsEnergyTable.Cell(lineIndex, 4).Range.Text = styleStr;
                        hsEnergyTable.Cell(lineIndex, 5).Range.Text = building.HeatIndex.ToString();
                        hsEnergyTable.Cell(lineIndex, 6).Range.Text = building.HeatLoad.ToString();
                        //节能措施
                        string eneStyleStr = Constants.SaveStyle[building.EnergySavingStyle];
                        hsEnergyTable.Cell(lineIndex, 7).Range.Text = eneStyleStr;
                        hsEnergyTable.Cell(lineIndex, 8).Range.Text = building.YearHeat.ToString();

                        lineIndex++;
                    }

                    //2 - 运行费用 2*8
                    //燃料耗量-燃气单价-燃料耗费-水泵全年运行能耗-水泵运行耗电-电费单价-运行电费-总运行费用
                    hsOperationTable = doc.Tables[tableNum + 1 + 2 + 3 * hs];
                    //燃料耗量--采暖总耗热量???
                    HeatProducer heatProducer = HeatSourceLayoutApp.solutions[sl].HeatProducers.ElementAt(hs).Value;
                    //更新解决方案的工程总表信息
                    totalArea += heatProducer.TotalArea;
                    totalHeat += heatProducer.TotalLoad;
                    hsOperationTable.Cell(2, 1).Range.Text = heatProducer.TotalLoad.ToString();
                    hsOperationTable.Cell(2, 2).Range.Text = HeatSourceLayoutApp.globalProperty.GasPrice.ToString();
                    hsOperationTable.Cell(2, 3).Range.Text = heatProducer.GetGasHeatingCost().ToString("0.0");
                    hsOperationTable.Cell(2, 4).Range.Text = heatProducer.totalWaterPumpEnergyConsumption.ToString("0.0");
                    hsOperationTable.Cell(2, 5).Range.Text = HeatSourceLayoutApp.globalProperty.ElecPrice.ToString("0.0");
                    hsOperationTable.Cell(2, 6).Range.Text = heatProducer.GetWaterPumpOperationCost().ToString("0.0");
                    hsOperationTable.Cell(2, 7).Range.Text = (heatProducer.GetGasHeatingCost() + heatProducer.GetWaterPumpOperationCost()).ToString("0.0");

                    //3 - 初投资费用(造价)
                    //锅炉造价-换热器造价-水泵造价-管线造价-总造价
                    hsInvestTable = doc.Tables[tableNum + 1 + 3 + 3 * hs];
                    hsInvestTable.Cell(2, 1).Range.Text = heatProducer.GetBoildersCost().ToString("0.0");
                    hsInvestTable.Cell(2, 2).Range.Text = heatProducer.GetHeatExchangerCost().ToString("0.0");
                    hsInvestTable.Cell(2, 3).Range.Text = heatProducer.GetWaterPumpCost().ToString("0.0");
                    hsInvestTable.Cell(2, 4).Range.Text = heatProducer.GetPipeLineCost().ToString("0.0");
                    hsInvestTable.Cell(2, 5).Range.Text = heatProducer.GetTotalCost().ToString("0.0");
                }
                //当前方案的工程总表, 2*4
                //建筑总面积-全年总耗热量-总运行费用-总造价
                //之前共有表格数
                int tableN = tableNum + 1 + 3 * hsNum;
                totalTable = doc.Tables[tableN + 1];
                totalTable.Cell(2, 1).Range.Text = totalArea.ToString();
                totalTable.Cell(2, 2).Range.Text = totalHeat.ToString("0.0");
                double totalFee = HeatSourceLayoutApp.solutions[sl].GetTotalFee();
                totalTable.Cell(2, 3).Range.Text = totalFee.ToString("0.0");
                //总造价
                totalTable.Cell(2, 4).Range.Text = HeatSourceLayoutApp.solutions[sl].GetTotalCost().ToString("0.0");
            }


            //最终的方案比较,根据解决方案数目动态增加列数
            //int currentTableNum = doc.Tables.Count; //不能用
            //方案比较表 8 * (solNUm + 1)
            Microsoft.Office.Interop.Word.Table solTable = doc.Tables[currentTableNum + 1];
            for (int s = 1; s <= solNum; s++)
            {
                //为每个解决方案增加一列
                solTable.Columns.Add(ref miss);

                solTable.Cell(1, s + 1).Range.Text = "方案" + s;
                //建筑总面积
                solTable.Cell(2, s + 1).Range.Text = HeatSourceLayoutApp.solutions[s - 1].TotalArea.ToString("0.0");
                //全年耗热量
                solTable.Cell(3, s + 1).Range.Text = HeatSourceLayoutApp.solutions[s - 1].TotalLoad.ToString("0.0");
                //燃料费用
                solTable.Cell(4, s + 1).Range.Text = HeatSourceLayoutApp.solutions[s - 1].GetGasHeatingCost().ToString("0.0");
                //水泵全年运行能耗
                solTable.Cell(5, s + 1).Range.Text = HeatSourceLayoutApp.solutions[s - 1].GetTotalWaterPumpEnergyConsumption().ToString("0.0");
                //运行电费
                solTable.Cell(6, s + 1).Range.Text = HeatSourceLayoutApp.solutions[s - 1].GetWaterPumpCost().ToString("0.0");
                //总运行费用
                solTable.Cell(7, s + 1).Range.Text = HeatSourceLayoutApp.solutions[s - 1].GetTotalFee().ToString("0.0");
                //总造价(初投资)
                solTable.Cell(8, s + 1).Range.Text = HeatSourceLayoutApp.solutions[s - 1].GetTotalCost().ToString("0.0");
            }

            //生成总方案对比图片
            int attrNum             = 4;
            Excel.Application excel = new Excel.Application();
            //excel.Visible = true;
            object          misValue    = System.Reflection.Missing.Value;
            Excel.Workbook  xlWorkBook  = excel.Workbooks.Add(misValue);
            Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            //fill data
            xlWorkSheet.Cells[1, 1] = "";
            for (int i = 1; i <= solNum; i++)
            {
                xlWorkSheet.Cells[1, i + 1] = "方案" + i.ToString();
            }
            xlWorkSheet.Cells[2, 1] = "全年能耗(GJ)";
            xlWorkSheet.Cells[3, 1] = "水泵功耗(KW∙h)";
            xlWorkSheet.Cells[4, 1] = "运行费用(万元)";
            xlWorkSheet.Cells[5, 1] = "总造价(万元)";
            for (int s = 1; s <= solNum; s++)
            {
                xlWorkSheet.Cells[2, s + 1] = HeatSourceLayoutApp.solutions[s - 1].TotalLoad.ToString("0.0");
                xlWorkSheet.Cells[3, s + 1] = HeatSourceLayoutApp.solutions[s - 1].GetTotalWaterPumpEnergyConsumption().ToString("0.0");
                double totalFee = HeatSourceLayoutApp.solutions[s - 1].GetTotalFee() / 10000.0;   // 万元
                xlWorkSheet.Cells[4, s + 1] = totalFee.ToString("0.0");
                double totalCost = HeatSourceLayoutApp.solutions[s - 1].GetTotalCost() / 10000.0; //   万元
                xlWorkSheet.Cells[5, s + 1] = totalCost.ToString("0.0");
            }

            // generate one chart for every attributes
            string[] imageNames = new string[4];
            for (int i = 2; i <= attrNum + 1; ++i)
            {
                // create chart
                Excel.Range chartRange;
                string      r1 = "A" + (char)('0' + i);
                string      r2 = new string((char)('A' + solNum), 1) + (char)('0' + i);
                chartRange = xlWorkSheet.get_Range(r1, r2);
                Excel.ChartObjects xlCharts  = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
                Excel.ChartObject  myChart   = (Excel.ChartObject)xlCharts.Add(10, 80, 600, 450);
                Excel.Chart        chartPage = myChart.Chart;
                chartPage.SetSourceData(chartRange, misValue);
                chartPage.ChartType = Excel.XlChartType.xlColumnClustered;

                // save chart into image
                string path      = Directory.GetCurrentDirectory();
                string imageName = path + "../../../chart" + i + ".bmp";
                imageNames[i - 2] = imageName;
                chartPage.Export(imageName, "BMP", misValue);
            }

            for (int i = 0; i < attrNum; ++i)
            {
                // append chart images
                string fileName         = imageNames[i];
                object linkToFile       = false;
                object saveWithDocument = true;
                object oEndOfDoc        = "\\endofdoc";
                Range  pictureRange     = doc.Bookmarks.get_Item(ref oEndOfDoc).Range;
                //pictureRange.InsertAfter("总解决方案对比图");
                doc.InlineShapes.AddPicture(fileName, linkToFile, saveWithDocument, pictureRange);
            }

            // show Word
            wordApp.Visible = true;

            // save Word
            doc.SaveAs(ref savePath, ref miss, ref miss,
                       ref miss, ref miss, ref miss, ref miss,
                       ref miss, ref miss, ref miss, ref miss,
                       ref miss, ref miss, ref miss, ref miss,
                       ref miss);
            //关闭doc,wordApp对象
            //((_Document)doc).Close();
            //((_Application)wordApp).Quit();

            // close and release
            xlWorkBook.Close(false, misValue, misValue);
            excel.Quit();

            //releaseObject(xlWorkSheet);
            //releaseObject(xlWorkBook);
            //releaseObject(excel);
            //Console.WriteLine("Excel file created , you can find the file: " + excelName);
        }
Esempio n. 15
0
        public PlotGraph(double[] x, double[] y, double k, double b)
        {
            string path     = GetTemplate();
            var    n        = x.Length.ToString();
            object misValue = Missing.Value;
            var    graph    = new Excel.Application()
            {
                Visible = true
            };
            var workbook  = graph.Workbooks.Add(misValue);
            var worksheet = (Excel.Worksheet)workbook.Worksheets.Item[1];

            worksheet.Cells[1, 1] = "";
            worksheet.Cells[1, 2] = "";
            worksheet.Cells[1, 3] = "";
            worksheet.Cells[1, 4] = "";

            for (int i = 1; i < 5; i++)
            {
                for (int j = 1; j <= x.Length; j++)
                {
                    switch (i)
                    {
                    case 1:
                        worksheet.Cells[j, i] = x[j - 1];
                        break;

                    case 2:
                        worksheet.Cells[j, i] = y[j - 1];
                        break;

                    case 3:
                        worksheet.Cells[j, i] = x[j - 1];
                        break;

                    case 4:
                        worksheet.Cells[j, i] = k * x[j - 1] + b;
                        break;
                    }
                }
            }
            Excel.Range range;

            var excelCharts = (Excel.ChartObjects)worksheet.ChartObjects(Type.Missing);
            var chart       = excelCharts.Add(10, 80, 500, 300);
            var chartPage   = chart.Chart;


            chartPage.ApplyChartTemplate(path); //Используем шаблон



            //Коллекция данных
            Excel.SeriesCollection seriesCollection = chart.Chart.SeriesCollection();

            //Данные выборки
            Excel.Series defaultSeries = seriesCollection.NewSeries();

            defaultSeries.ChartType = Excel.XlChartType.xlXYScatter;

            defaultSeries.XValues     = worksheet.Range["A1", "A" + n];
            defaultSeries.Values      = worksheet.Range["B1", "B" + n];
            defaultSeries.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleCircle;

            //Данные полученных значений
            var plotedSeries = seriesCollection.NewSeries();

            plotedSeries.XValues     = worksheet.Range["C1", "C" + n];
            plotedSeries.Values      = worksheet.Range["D1", "D" + n];
            plotedSeries.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleCircle;

            chartPage.HasLegend = true;
            chartPage.Legend.LegendEntries(1).Delete();
            chartPage.Legend.LegendEntries(1).Delete();

            var counter = 1;

            foreach (Excel.Series series in chartPage.SeriesCollection())
            {
                series.Name = counter++ == 3 ? "Данные выборки" : "Функция регрессии";
            }

            chartPage.Legend.Position = Excel.XlLegendPosition.xlLegendPositionBottom;
            foreach (Excel.ChartObject co in excelCharts)
            {
                co.Select();
                Excel.Chart thisChart = (Excel.Chart)co.Chart;
                thisChart.Export(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location) + @"\chart.png", "PNG", false);
            }

            workbook.Close(false, misValue, misValue);
            graph.Quit();
        }
Esempio n. 16
0
        private void button6_Click(object sender, EventArgs e)
        {
            string outputPath1        = @"C:\Users\ReaLBERG\Desktop\3 курс\АИС\Отчеты\Ценабонусы" + Path.GetRandomFileName() + ".xlsx";
            KPgamenotebookContext db2 = new KPgamenotebookContext();
            int num2   = db2.Model.Count();
            var models = db2.Model;

            Model[] gameNotebooks2 = new Model[num2];
            int     p = 1;

            Excel.Application excelApp = new Excel.Application();
            Excel.Workbook    workBook;
            Excel.Worksheet   workSheet;
            workBook  = excelApp.Workbooks.Add();
            workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(1);

            int[] vs  = new int[num2];
            int[] vs2 = new int[num2];
            int   k   = 0;

            foreach (var t in models)
            {
                int value;
                int value1;

                int.TryParse(string.Join("", t.Price.Where(c => char.IsDigit(c))), out value);
                int.TryParse(string.Join("", t.Bonuses.Where(c => char.IsDigit(c))), out value1);
                vs[k]  = value;
                vs2[k] = value1;
                k++;
            }
            int temp;

            for (int i = 0; i < vs.Length - 1; i++)
            {
                for (int j = i + 1; j < vs.Length; j++)
                {
                    if (vs[i] > vs[j])
                    {
                        temp   = vs[i];
                        vs[i]  = vs[j];
                        vs[j]  = temp;
                        temp   = vs2[i];
                        vs2[i] = vs2[j];
                        vs2[j] = temp;
                    }
                }
            }
            for (p = 1; p < 24; p++)
            {
                workSheet.Cells[1, p] = (vs[p - 1]);
                workSheet.Cells[2, p] = (vs2[p - 1]);
            }
            Excel.ChartObjects chartObjs = (Excel.ChartObjects)workSheet.ChartObjects();
            Excel.ChartObject  chartObj  = chartObjs.Add(10, 50, 500, 500);
            Excel.Chart        xlChart   = chartObj.Chart;
            xlChart.ChartType = Excel.XlChartType.xlAreaStacked;
            Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)xlChart.SeriesCollection(Type.Missing);
            Excel.Series           series           = seriesCollection.NewSeries();
            series.XValues          = workSheet.get_Range("A1", "Q1");
            series.Values           = workSheet.get_Range("A2", "Q2");
            xlChart.HasTitle        = true;
            xlChart.ChartTitle.Text = "Зависимость бонусов от цены";
            xlChart.HasLegend       = false;
            excelApp.Visible        = true;
            excelApp.UserControl    = true;
            workSheet.SaveAs(outputPath1);
            object misValue = System.Reflection.Missing.Value;

            xlChart.Export("C:\\Users\\ReaLBERG\\Desktop\\3 курс\\АИС\\Graf.bmp", "BMP", misValue);
            excelApp.Quit();


            Word cOMFormatter        = new Word(@"C:\Users\ReaLBERG\Desktop\3 курс\АИС\Отчеты\lb9tt.doc");
            KPgamenotebookContext db = new KPgamenotebookContext();

            Console.WriteLine("Пожалуйста, введите имя: ");
            string name = Console.ReadLine();

            cOMFormatter.Replace("{Имя}", name);
            int num = db.Model.Count();

            string[] start = new string[3] {
                "ID", "Название", "Цена"
            };
            var gamenotebook = db.Model;

            Model[] gameNotebooks = new Model[num];
            int     y             = 0;

            foreach (var t in gamenotebook)
            {
                gameNotebooks[y] = t;

                y++;
            }
            cOMFormatter.TableCreate(num + 1, 3, start, gameNotebooks);

            cOMFormatter.Close();
        }
Esempio n. 17
0
        /// <summary>
        /// parse an excel document and build a kinesis document model
        /// </summary>
        /// <param name="path">full path of the excel document</param>
        /// <returns>equivalent kinesis document model</returns>
        public Document ParseNewDocumentCharts(String path, ProcessingProgress pp, Document document)
        {
            //~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
            pp.OverallOperationName = "All Document Charts";
            //~~~~~~~~~~~~~progress~~~~~~~~~~~~~//

            //directory where all the data will be saved
            String folderName   = document.Location;
            String documentPath = System.IO.Path.Combine(DocumentService.TEMP_DIRECTORY, folderName);

            //~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
            pp.CurrentOperationName          = "Opening MS Office";
            pp.CurrentOperationTotalElements = 1;
            pp.CurrentOperationElement       = 0;
            //~~~~~~~~~~~~~progress~~~~~~~~~~~~~//

            //open the given excel document
            Workbook workbook = excelApplication.Workbooks.Open(path, ReadOnly: true);

            //~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
            pp.CurrentOperationElement       = 1;
            pp.OverallOperationTotalElements = EvaluateWorkbook(workbook, pp);
            pp.OverallOperationElement       = 0;
            //~~~~~~~~~~~~~progress~~~~~~~~~~~~~//

            //for every sheet
            for (int i = 1; i <= workbook.Sheets.Count; i++)
            {
                Worksheet worksheet = workbook.Sheets[i];

                //create a new page
                KineSis.ContentManagement.Model.Page page = document.Pages[i - 1];

                //check if chart generation is wanted
                if (DocumentService.CHART_HORIZONTAL_FACES > 0)
                {
                    //get charts
                    ChartObjects chartObjects = worksheet.ChartObjects(Type.Missing);

                    //create directory for charts
                    String chartPath = System.IO.Path.Combine(documentPath, "charts");
                    System.IO.Directory.CreateDirectory(chartPath);

                    //for every chart
                    for (int j = 1; j <= chartObjects.Count; j++)
                    {
                        //~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
                        pp.CurrentOperationName          = "Page " + i + " / Chart " + j + " of " + chartObjects.Count;
                        pp.CurrentOperationTotalElements = EvaluateChart(chartObjects.Item(j).Chart);
                        pp.CurrentOperationElement       = 0;
                        //~~~~~~~~~~~~~progress~~~~~~~~~~~~~//

                        KineSis.ContentManagement.Model.Chart mChart = new KineSis.ContentManagement.Model.Chart();

                        //current chart
                        Microsoft.Office.Interop.Excel.Chart chart = chartObjects.Item(j).Chart;

                        mChart.SetThumbnailUrl(GenerateThumbnail(chart, chartPath + DD + i + _ + j + "_thumb"));

                        //~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
                        pp.OverallOperationElement++;
                        pp.CurrentOperationElement++;
                        //~~~~~~~~~~~~~progress~~~~~~~~~~~~~//

                        if (DocumentService.FORCE_CHART_SIZE)
                        {
                            chart.ChartArea.Height = ((float)DocumentService.CHART_WIDTH * chart.ChartArea.Height) / chart.ChartArea.Width;
                            chart.ChartArea.Width  = DocumentService.CHART_WIDTH;
                        }

                        int chartType = GetChartType(chart);

                        //start from 0 point
                        chart.Rotation = 0;

                        int horizontalAngle = 0;

                        //depending on how many horizontal faces are required, calculate the angle between them
                        if (DocumentService.CHART_HORIZONTAL_FACES > 0)
                        {
                            horizontalAngle = 360 / DocumentService.CHART_HORIZONTAL_FACES;
                        }

                        int verticalAngle = 0;

                        //depending on how many vertical faces are required for a horizontal face, celaculate the angle between them, excluding the vertical face at 90 degrees
                        if (DocumentService.CHART_VERTICAL_FACES > 0)
                        {
                            verticalAngle = 90 / (DocumentService.CHART_VERTICAL_FACES + 1);
                        }

                        if (chart.HasTitle)
                        {
                            mChart.Title = chart.ChartTitle.Caption;
                        }
                        else
                        {
                            mChart.Title = chart.Name;
                        }

                        //does not support rotation (it's plain)
                        if (chartType == 0)
                        {
                            //if horizontal faces number is 0, then no chart will be outputed
                            if (DocumentService.CHART_HORIZONTAL_FACES > 0)
                            {
                                ChartHorizontalView hView = new ChartHorizontalView();

                                //draw chart face as image
                                chart.Export(chartPath + DD + i + _ + j + DocumentService.IMAGE_EXTENSION, DocumentService.IMAGE_FILTER, false);
                                //add to hView
                                hView.ImageUrl = chartPath + DD + i + _ + j + DocumentService.IMAGE_EXTENSION;
                                //add to views
                                mChart.Views.Add(hView);

                                //~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
                                pp.OverallOperationElement++;
                                pp.CurrentOperationElement++;
                                //~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
                            }
                        }
                        else
                        {
                            //for every horizontal face
                            for (int k = 0; k < DocumentService.CHART_HORIZONTAL_FACES; k++)
                            {
                                ChartHorizontalView hView = new ChartHorizontalView();
                                //reset elevation
                                chart.Elevation = 0;
                                //export face as image

                                chart.Export(chartPath + DD + i + _ + j + _ + chart.Rotation + _ + chart.Elevation + DocumentService.IMAGE_EXTENSION, DocumentService.IMAGE_FILTER, false);
                                //set bitmap to view
                                hView.ImageUrl = chartPath + DD + i + _ + j + _ + chart.Rotation + _ + chart.Elevation + DocumentService.IMAGE_EXTENSION;

                                //~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
                                pp.OverallOperationElement++;
                                pp.CurrentOperationElement++;
                                //~~~~~~~~~~~~~progress~~~~~~~~~~~~~//

                                //for every vertical face
                                for (int l = 0; l < DocumentService.CHART_VERTICAL_FACES; l++)
                                {
                                    ChartVerticalView vView = new ChartVerticalView();
                                    //increse elevation
                                    chart.Elevation += verticalAngle;
                                    //export face as image
                                    chart.Export(chartPath + DD + i + _ + j + _ + chart.Rotation + _ + chart.Elevation + DocumentService.IMAGE_EXTENSION, DocumentService.IMAGE_FILTER, false);

                                    //set bitmap to view
                                    vView.ImageUrl = chartPath + DD + i + _ + j + _ + chart.Rotation + _ + chart.Elevation + DocumentService.IMAGE_EXTENSION;
                                    //add vertical view to horizontal UP list
                                    hView.Up.Add(vView);

                                    //~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
                                    pp.OverallOperationElement++;
                                    pp.CurrentOperationElement++;
                                    //~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
                                }

                                //some chart types, like 3D pie, does not support elevation less than 0
                                if (SupportsNegativeElevation(chart))
                                {
                                    //reset elevation
                                    chart.Elevation = 0;

                                    //for every vertical face
                                    for (int m = 0; m < DocumentService.CHART_VERTICAL_FACES; m++)
                                    {
                                        ChartVerticalView vView = new ChartVerticalView();

                                        //decrease elevation
                                        chart.Elevation -= verticalAngle;
                                        //export face as image
                                        chart.Export(chartPath + DD + i + _ + j + _ + chart.Rotation + _ + chart.Elevation + DocumentService.IMAGE_EXTENSION, DocumentService.IMAGE_FILTER, false);
                                        //set bitmap to vertical view
                                        vView.ImageUrl = chartPath + DD + i + _ + j + _ + chart.Rotation + _ + chart.Elevation + DocumentService.IMAGE_EXTENSION;
                                        //add vertical view to horizontal view DOWN list
                                        hView.Down.Add(vView);

                                        //~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
                                        pp.OverallOperationElement++;
                                        pp.CurrentOperationElement++;
                                        //~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
                                    }
                                }

                                //increase horizontal angle in order to get the next horizontal view
                                chart.Rotation += horizontalAngle;
                                //add horizontal view to the chat's views list
                                mChart.Views.Add(hView);
                            }
                        }

                        //add chart to page
                        page.Charts.Add(mChart);
                    }
                }
            }

            //close workbook without saving any possible changes (this way the "Are you sure?" or "Save changes?" dialogs will be supressed)
            workbook.Close(SaveChanges: false);

            CloseOfficeApplication();

            //return the built document
            return(document);
        }
Esempio n. 18
0
        private void graph(bool threeD)
        {
            loadSteps(threeD);
            Excel.Application xlApp;
            Excel.Workbook    xlWorkBook;
            Excel.Worksheet   xlWorkSheet;
            object            misValue = System.Reflection.Missing.Value;

            xlApp = new Excel.Application();
            xlApp.DisplayAlerts = false;

            xlWorkBook  = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            //add data
            int max   = (int)((end - start) / interval);
            int ymax  = (int)((yend - ystart) / yinterval);
            int index = 1;

            progressBar1.Maximum = max;

            for (int i = 1; i <= max; i++)
            {
                progressBar1.Value = i;
                progressBar1.Refresh();

                double x = i * interval + start;

                for (int j = 1; j <= ymax; j++)
                {
                    double y = j * yinterval + ystart;

                    string xlEquation = "=";

                    foreach (char c in T_Equation.Text)
                    {
                        Application.DoEvents();

                        if (c == 'X' || c == 'x')
                        {
                            xlEquation += x;
                        }
                        else if (threeD && (c == 'Y' || c == 'y'))
                        {
                            xlEquation += y;
                        }
                        else
                        {
                            xlEquation += c;
                        }
                    }

                    ++index;
                    try
                    {
                        if (!threeD)
                        {
                            xlWorkSheet.Cells[index, 1] = x.ToString();
                            xlWorkSheet.Cells[index, 2] = xlEquation;
                        }
                        else
                        {
                            xlWorkSheet.Cells[1, j + 1]     = y.ToString();
                            xlWorkSheet.Cells[i + 1, 1]     = x.ToString();
                            xlWorkSheet.Cells[i + 1, j + 1] = xlEquation;
                        }
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Invalid equation.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                    }
                }
            }

            Excel.Range chartYRange, chartXRange, chartZRange;

            Excel.ChartObjects xlCharts  = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
            Excel.ChartObject  myChart   = (Excel.ChartObject)xlCharts.Add(0, 0, 465, 330);
            Excel.Chart        chartPage = myChart.Chart;

            int last = max * ymax;

            chartXRange = xlWorkSheet.get_Range("A1", "A" + last);
            chartYRange = xlWorkSheet.get_Range("B1", "B" + last);

            if (!threeD)
            {
                chartPage.SetSourceData(chartYRange, misValue);
            }
            else
            {
                try
                {
                    char endC = System.Convert.ToChar(Convert.ToInt32('B') + ymax - 1);
                    chartZRange = xlWorkSheet.get_Range("A1", endC + "" + max);
                    chartPage.SetSourceData(chartZRange, misValue);
                }
                catch (Exception ex) {
                    MessageBox.Show("Chart y-range too large.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
            }

            chartPage.ChartType = (threeD) ? Excel.XlChartType.xlSurface : Excel.XlChartType.xlLine;
            chartPage.Legend.Clear();

            if (!threeD)
            {
                chartPage.SeriesCollection(1).XValues = chartXRange;
            }

            Excel.Axis yaxis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
            yaxis.HasTitle       = true;
            yaxis.AxisTitle.Text = "Y";

            Excel.Axis xaxis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
            xaxis.HasTitle       = true;
            xaxis.AxisTitle.Text = "X";

            if (threeD)
            {
                Excel.Axis zAxis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlValue,
                                                              Excel.XlAxisGroup.xlPrimary);
                zAxis.HasTitle       = true;
                zAxis.AxisTitle.Text = "Z";
            }

            //export chart as picture file
            if (pictureBox1.Image != null)
            {
                pictureBox1.Image.Dispose();
                pictureBox1.Image = null;
            }
            chartPage.Export(AppDomain.CurrentDomain.BaseDirectory + "test.bmp", "BMP", misValue);

            //load picture to picturebox
            pictureBox1.Image = new Bitmap(AppDomain.CurrentDomain.BaseDirectory + "test.bmp");

            xlWorkBook.Close(saveXL, misValue, misValue);
            xlApp.Quit();

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);
            releaseObject(chartPage);
        }
Esempio n. 19
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();
        }
Esempio n. 20
0
        public async Task <byte[]> GenerateChartImageFromXlsxFile <TListEntity>(string excelChartTemplateName, string outPutFolder, IEnumerable <TListEntity> inputItems, int seriesCount, int xAxisDataCount)
        {
            if (inputItems != null && inputItems.Count() > 0)
            {
                Excel.Application xlApp;
                Excel.Workbook    xlWorkBook;
                Excel.Worksheet   xlWorkSheet;

                outPutFolder           = System.Configuration.ConfigurationManager.AppSettings["ChartOutputFolder"].ToString(); //"C:\\ReportsGenerated\\Chart\\OutPuts";
                excelChartTemplateName = System.Configuration.ConfigurationManager.AppSettings["ChartTemplate"].ToString();     //"C:\\ReportsGenerated\\Chart\\InPuts\\FinalTemplate_Test_good_series_xaxis_1.xlsx"; //
                Console.WriteLine(excelChartTemplateName);
                Console.WriteLine(outPutFolder);

                object misValue = System.Reflection.Missing.Value;
                xlApp = new Excel.Application();
                //xlWorkBook = xlApp.Workbooks.Add(misValue);
                xlWorkBook  = xlApp.Workbooks.Open(excelChartTemplateName);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

                try
                {
                    string[] properties = new string[] { "Name", "ValuePrev1", "ValuePrev2", "ValuePrev3", "ValuePrev4", "ValuePrev5", "ValuePrev6" };

                    List <TListEntity> items = new List <TListEntity>();
                    foreach (var item in inputItems)
                    {
                        items.Add(item);
                    }
                    Excel.Range chartRangeXaxis;
                    Excel.Range chartRange;
                    var         rangeToValue      = "";
                    var         xAxisRangeToValue = "";
                    int         i = 0;
                    var         h = 0;
                    int[]       alphabetNumArray = new int[] { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26 };

                    int i_clear = 0;
                    var h_clear = 0;
                    //clearing existing values
                    for (; i_clear < 4; i_clear++)     //rows clearing
                    {
                        for (; h_clear < 4; h_clear++) //columns clearing
                        {
                            xlWorkSheet.Cells[(i_clear + 1), alphabetNumArray[h_clear]] = "";
                        }
                    }

                    for (i = 0; i < items.Count(); i++)
                    {
                        for (h = 0; h < xAxisDataCount + 1; h++)
                        {
                            string propertyName = properties[h];
                            var    property     = items[i].GetType().GetProperty(propertyName);
                            if (property != null)
                            {
                                var value = property.GetValue(items[i], null);
                                if (value != null)//required to check, for not allowing empty values
                                {
                                    xlWorkSheet.Cells[(i + 1), alphabetNumArray[h]] = value;
                                    //xlWorkSheet.Cells["A2"] = value;//this type was not works on microsoft.office.interop.excel
                                    //xlWorkSheet.Cells[1,2] = value;//this is correct one
                                }
                                //else
                                //    xlWorkSheet.Cells[(i + 1), alphabetNumArray[h]] = "";
                            }
                        }
                    }
                    string[] alphabetArray = new string[] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };

                    rangeToValue      = alphabetArray[xAxisDataCount] + i;
                    xAxisRangeToValue = alphabetArray[xAxisDataCount] + 1;


                    Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
                    //Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);//for new one
                    Excel.ChartObject chartObject = (Excel.ChartObject)xlCharts.Item(1);
                    Excel.Chart       chart       = chartObject.Chart;

                    chartRangeXaxis = xlWorkSheet.get_Range("B1", xAxisRangeToValue);
                    chartRange      = xlWorkSheet.get_Range("A2", rangeToValue);
                    //chartPage.SetSourceData(chartRangeXaxis, Excel.XlRowCol.xlRows);
                    chart.SetSourceData(chartRange, Excel.XlRowCol.xlRows);//sucess
                    //chartPage.ChartType = Excel.XlChartType.xlLineMarkers;//if creating new chart uncomment this one

                    Excel.Axis xAxis = (Excel.Axis)chart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);

                    xAxis.HasTitle = false;
                    //xAxis.AxisTitle.Caption = "Time";
                    xAxis.CategoryNames = (Excel.Range)xlWorkSheet.get_Range("B1", xAxisRangeToValue);

                    Excel.Range formatRange;
                    formatRange = xlWorkSheet.get_Range("B2", rangeToValue);
                    formatRange.NumberFormat = "#,##0.00";

                    //chartPage.ChartArea.Width = 200;
                    //chartPage.ChartArea.Height = 100;

                    // chartPage.CategoryLabelLevel = Excel.XlCategoryLabelLevel.xlCategoryLabelLevelCustom;
                    //not working properly
                    //Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chartPage.SeriesCollection();
                    //Excel.Series series = seriesCollection.NewSeries();
                    //series.XValues = chartRangeXaxis;

                    //have to set for data labels number format as 0.00
                    //var series = chartObject.Chart.SeriesCollection() as Excel.SeriesCollection;
                    //foreach (var ser in series)
                    //{
                    //    var DataLabels = ((Excel.Series)ser).DataLabels(1);
                    //    DataLabels.
                    //}

                    var imageFileFullName = outPutFolder + @"\ChartImage_" + DateTime.Now.ToString("yyyyMMdd_HH_mm_ss") + ".png";
                    chart.Export(imageFileFullName, "PNG", false);



                    try
                    {
                        //xlWorkBook.SaveAs(outPutFolder + @"\Excel_" + DateTime.Now.ToString("yyyyMMdd_HH_mm_ss") + ".xlsx", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                        xlWorkBook.SaveAs(outPutFolder + @"\Excel_" + DateTime.Now.ToString("yyyyMMddHHmmss") + DateTime.Now.Millisecond.ToString(), Excel.XlFileFormat.xlOpenXMLWorkbook, misValue, misValue, false, false, Excel.XlSaveAsAccessMode.xlNoChange, Excel.XlSaveConflictResolution.xlUserResolution, true, misValue, misValue, misValue);

                        //xlWorkBook.SaveAs(outPutFolder + @"\Excel_" + DateTime.Now.ToString("yyyyMMdd_HH_mm_ss") + ".xlsx", Excel.XlFileFormat.xlXMLSpreadsheet, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlShared, misValue, misValue, misValue, misValue, misValue);
                        xlWorkBook.Close(true, misValue, misValue);
                        xlApp.Quit();

                        await this.releaseObject(xlWorkSheet);

                        await this.releaseObject(xlWorkBook);

                        await this.releaseObject(xlApp);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine("Error closing Excel object" + ex.Message);
                        Console.WriteLine(ex.StackTrace);
                    }
                    finally
                    {
                        if (xlWorkSheet != null)
                        {
                            Marshal.ReleaseComObject(xlWorkSheet);
                        }
                        if (xlWorkBook != null)
                        {
                            Marshal.ReleaseComObject(xlWorkBook);
                        }
                        if (xlApp != null)
                        {
                            Marshal.ReleaseComObject(xlApp);
                        }
                    }


                    try
                    {
                        Console.WriteLine("Reading image");
                        byte[] byteArray = File.ReadAllBytes(imageFileFullName);

                        Console.WriteLine("Reading image 1");

                        System.IO.DirectoryInfo di = new DirectoryInfo(outPutFolder);
                        foreach (FileInfo file in di.GetFiles())
                        {
                            file.Delete();
                        }
                        return(byteArray);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine("Failed to delete " + ex.Message);
                    }

                    return(null);
                }
                catch (Exception ex)
                {
                    await this.releaseObject(xlWorkSheet);

                    await this.releaseObject(xlWorkBook);

                    await this.releaseObject(xlApp);
                }
            }
            return(null);
        }
        private void button1_Click(object sender, EventArgs e)
        {
            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

            xlWorkSheet.Cells[1, 1] = "";
            xlWorkSheet.Cells[1, 2] = "Student1";
            xlWorkSheet.Cells[1, 3] = "Student2";

            xlWorkSheet.Cells[1, 4] = "Student3";



            xlWorkSheet.Cells[2, 1] = "Term1";

            xlWorkSheet.Cells[2, 2] = "80";

            xlWorkSheet.Cells[2, 3] = "65";

            xlWorkSheet.Cells[2, 4] = "45";



            xlWorkSheet.Cells[3, 1] = "Term2";

            xlWorkSheet.Cells[3, 2] = "78";

            xlWorkSheet.Cells[3, 3] = "72";

            xlWorkSheet.Cells[3, 4] = "60";



            xlWorkSheet.Cells[4, 1] = "Term3";

            xlWorkSheet.Cells[4, 2] = "82";

            xlWorkSheet.Cells[4, 3] = "80";

            xlWorkSheet.Cells[4, 4] = "65";



            xlWorkSheet.Cells[5, 1] = "Term4";
            xlWorkSheet.Cells[5, 2] = "75";

            xlWorkSheet.Cells[5, 3] = "82";

            xlWorkSheet.Cells[5, 4] = "68";

            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", "C12");


            chartPage.SetSourceData(chartRange, misValue);

            chartPage.ChartType = Excel.XlChartType.xlConeCol;    //xlCylinderCol;//xlLine;//xlColumnClustered;

            //export chart as picture file

            chartPage.Export(@"H:\img\excel_chart_export.png", "PNG", misValue);



            xlWorkBook.SaveAs(@"H:\img\csharp.net-informations.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();
        }
Esempio n. 22
0
        private void button1_Click(object sender, EventArgs e)
        {
            Excel.Application xlApp;
            Excel.Workbook    xlWorkBook;
            Excel.Worksheet   xlWorkSheet;
            object            misValue = System.Reflection.Missing.Value;


            xlApp = new Excel.Application();
            xlApp.DisplayAlerts = false;
            //xlApp.Visible = true;

            xlWorkBook  = xlApp.Workbooks.Open(fileUrl);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            Excel.Range        chartRange;
            Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);

            //Left, Top, Width, Height
            Excel.ChartObject myChart   = (Excel.ChartObject)xlCharts.Add(0, 0, 1000, 600);
            Excel.Chart       chartPage = myChart.Chart;


            int lastRow = xlWorkSheet.UsedRange.Rows.Count;

            chartRange = xlWorkSheet.get_Range("B1", "D" + lastRow);
            chartPage.SetSourceData(chartRange, misValue);
            chartPage.ChartType = Excel.XlChartType.xlXYScatterSmoothNoMarkers;

            Excel.Axis horizAxis = chartPage.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
            horizAxis.MaximumScaleIsAuto = false;
            horizAxis.MaximumScale       = lastRow;
            horizAxis.MinimumScaleIsAuto = false;
            horizAxis.MinimumScale       = 0;
            horizAxis.HasTitle           = false;
            //horizAxis.AxisTitle.Text = "across the bottom";

            Excel.Axis vertAxis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
            vertAxis.HasMajorGridlines  = false; // change this to whatever you wish
            vertAxis.HasTitle           = true;
            vertAxis.AxisTitle.Text     = "Volts";
            vertAxis.MaximumScaleIsAuto = true;
            //vertAxis.MaximumScale = 4; // you can pick this based on your input
            vertAxis.MinimumScaleIsAuto = true;
            // vertAxis.MinimumScale = -4;


            //chartPage.ApplyChartTemplate("c:\\ctmp.crtx");
            chartPage.Export(fileDir + "/Chart1.PNG", "PNG", false);
            Image image = Image.FromFile(fileDir + "/Chart1.PNG");

            pictureBox1.Image    = image;
            pictureBox1.SizeMode = PictureBoxSizeMode.StretchImage;
            //xlWorkBook.SaveAs(fileUrlNoExt, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlNoChange, misValue, misValue, misValue, misValue, misValue);
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);
        }
Esempio n. 23
0
        private void btnChart_Click(object sender, EventArgs e)
        {
            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
            xlWorkSheet.Cells[1, 2] = "Overview";
            xlWorkSheet.Cells[1, 3] = "Time";

            xlWorkSheet.Cells[2, 1] = "Planned \n" + toTime(totalPlanned).ToString();
            xlWorkSheet.Cells[2, 2] = totalPlanned;

            xlWorkSheet.Cells[3, 1] = "Not Planned \n" + toTime(totalNotPlanned).ToString();
            xlWorkSheet.Cells[3, 2] = totalNotPlanned;

            xlWorkSheet.Cells[4, 1] = "Auto Mode \n" + toTime(totalAutoMode).ToString();
            xlWorkSheet.Cells[4, 2] = totalAutoMode;
            xlWorkSheet.Cells[4, 3] = toTime(totalAutoMode).ToString();

            //xlWorkBook.SaveAs("csharp-Excel.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);

            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", "c4");
            chartPage.SetSourceData(chartRange, misValue);
            chartPage.ChartType = Excel.XlChartType.xlPie;

            //chartPage.SeriesCollection(2).Format.Fill.ForeColor.RGB = System.Drawing.Color.Red.ToArgb();

            //chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
            // if (!File.Exists(@"C:\excel_chart_export.bmp"))
            //{
            chartPage.Export(@"C:\excel_chart_export.bmp", "BMP", misValue);

            //}
            //export chart as picture file

            //xlWorkBook.SaveAs("csharp.net-informations.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            xlWorkBook.Close(false, misValue, misValue);
            xlApp.Quit();
            this.Hide();

            Image img;

            using (var bmpTemp = new Bitmap(@"C:\excel_chart_export.bmp"))
            {
                img = new Bitmap(bmpTemp);
            }

            using (Form form = new Form())
            {
                form.StartPosition = FormStartPosition.CenterScreen;
                form.Size          = new Size(510, 460);

                PictureBox pb = new PictureBox();
                pb.Dock  = DockStyle.Fill;
                pb.Image = img;

                form.Controls.Add(pb);
                form.ShowDialog();
            }
            this.Show();

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);
            releaseObject(chartPage);

            File.Delete(@"C:\excel_chart_export.bmp");
        }
Esempio n. 24
0
        private static void ExcelGraphGeneration()
        {
            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 columns
            xlWorkSheet.Cells[1, 1]  = "SL";
            xlWorkSheet.Cells[1, 2]  = "Name";
            xlWorkSheet.Cells[1, 3]  = "CTC";
            xlWorkSheet.Cells[1, 4]  = "DA";
            xlWorkSheet.Cells[1, 5]  = "HRA";
            xlWorkSheet.Cells[1, 6]  = "Conveyance";
            xlWorkSheet.Cells[1, 7]  = "Medical Expenses";
            xlWorkSheet.Cells[1, 8]  = "Special";
            xlWorkSheet.Cells[1, 9]  = "Bonus";
            xlWorkSheet.Cells[1, 10] = "TA";
            xlWorkSheet.Cells[1, 11] = "TOTAL";
            xlWorkSheet.Cells[1, 11] = "Contribution to PF";
            xlWorkSheet.Cells[1, 12] = "Profession Tax";
            xlWorkSheet.Cells[1, 13] = "TDS";
            xlWorkSheet.Cells[1, 14] = "Salary Advance";
            xlWorkSheet.Cells[1, 15] = "TOTAL";
            xlWorkSheet.Cells[1, 16] = "NET PAY";


            Excel.Application xlApp1     = new Excel.Application();
            Excel.Workbook    xlWorkbook = xlApp1.Workbooks.Open
                                               (@"C:\Users\v-mapall\source\repos\ExcelCustomApps\AppServicesForExcel\Sample Data\Sample Data2.xlsx");
            Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
            Excel.Range      xlRange     = xlWorksheet.UsedRange;

            int rowCount = xlRange.Rows.Count;
            int colCount = xlRange.Columns.Count;

            //for (int i = 1; i <= rowCount; i++)

            for (int i = 1; i <= 2; i++)
            {
                for (int j = 1; j <= colCount; j++)
                {
                    Console.WriteLine(xlRange.Cells[i, j].Value2.ToString());
                    xlWorkSheet.Cells[i, j] = xlRange.Cells[i, j]
                                              .Value2.ToString();
                }
            }

            //Console.ReadLine();

            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", "R22");
            chartPage.SetSourceData(chartRange, misValue);
            chartPage.ChartType = Excel.XlChartType.xlColumnClustered;

            // Export chart as picture file
            chartPage.Export(@"C:\Users\v-mapall\source\repos\ExcelCustomApps\AppServicesForExcel\Sample Data\EmployeeExportData.pdf",
                             "PDF", misValue);

            xlWorkBook.SaveAs("EmployeeExportData.xls",
                              Excel.XlFileFormat.xlWorkbookNormal, misValue,
                              misValue, misValue, misValue,
                              Excel.XlSaveAsAccessMode.xlExclusive, misValue,
                              misValue, misValue, misValue, misValue);
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();

            DeallocateObject(xlWorkSheet);
            DeallocateObject(xlWorkBook);
            DeallocateObject(xlApp);
            DeallocateObject(xlApp1);
        }
Esempio n. 25
0
        static void Main(string[] args)
        {
            // set english culture (for english function names and . decimal)
            System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-GB");

            // list seperator is now , Trying to change it to ; doesnt work :(
            // System.Globalization.CultureInfo.CurrentCulture.TextInfo.ListSeparator = ";";

            // show manual
            bool showMan = false;

            // check pipe
            String pipedText = "";
            bool   isKeyAvailable;
            bool   piped = false;

            try { isKeyAvailable = System.Console.KeyAvailable; }
            catch { pipedText = System.Console.In.ReadToEnd(); piped = true; }

            // if no args or pipe, show manual
            if (piped == false && args.Length == 0)
            {
                showMan = true;
            }

            // set default values
            string        infile = "";
            string        paste  = "";
            List <string> macro  = new List <string>();

            int[] cellA = new int[2] {
                0, 1
            };
            int[] cellB = new int[2] {
                0, 1
            };
            int[] outCellA = new int[2] {
                0, 1
            };
            int[] outCellB = new int[2] {
                0, 0
            };
            string sheet  = "";
            string active = "";

            // warnings are off by default, since excel will warn about ANYTHING, which interupts the program and leads to errors.
            // f.eks. log charts will make the program fail, when they are given blank data in the step before new data is provided.
            bool warning = false;

            // save on exit
            bool save = true;

            // convert error codes to text in output
            bool outErr = true;

            // paste as text
            bool asText = false;


            // blehh..
            string errLine = "--------------------------------------------------------------------------------";

            // tab as default output space character
            string cellSpacer = "\t";


            // all charts that should be saved
            List <string> charts = new List <string>();


            // check input arguments
            int iarg = 0;

            for (int i = 0; i < args.Length; i++)
            {
                if (args[i].StartsWith("-"))
                {
                    // show manual
                    if (args[i] == "-help" || args[i] == "--help" || args[i] == "-?")
                    {
                        showMan = true;
                    }

                    // specify macro to run
                    if (args[i] == "-m")
                    {
                        try {
                            macro.Add(args[i + 1]);
                            i++;
                        }
                        catch {
                            Error("No macro name given for -m.", 1);
                        }
                    }
                    // paste input as text?
                    if (args[i] == "-t")
                    {
                        asText = true;
                    }
                    // dont save
                    if (args[i] == "-n")
                    {
                        save = false;
                    }
                    // hide warnings
                    if (args[i] == "-w")
                    {
                        warning = true;
                    }
                    // set space character
                    if (args[i] == "-b")
                    {
                        cellSpacer = " ";
                        if (args.Length > i + 1)
                        {
                            if (args[i + 1].Length == 1)
                            {
                                cellSpacer = args[i + 1];
                                i++;
                            }
                        }
                    }
                    // set paste sheet
                    if (args[i] == "-p")
                    {
                        try
                        {
                            active = args[i + 1];
                            i++;
                        }
                        catch
                        {
                            Error("No paste name given for -p.", 1);
                        }
                    }
                    // set output sheet
                    if (args[i] == "-s")
                    {
                        try
                        {
                            sheet = args[i + 1];
                            i++;
                        }
                        catch
                        {
                            Error("No sheet name given for -s.", 1);
                        }
                    }
                    // blank errors
                    if (args[i] == "-#")
                    {
                        outErr = false;
                    }
                }

                else
                {
                    // excel file
                    if (iarg == 0)
                    {
                        infile = args[i];
                    }
                    // paste file
                    else if (iarg == 1 && !piped)
                    {
                        paste = args[i];
                        if (paste == "~" || paste == "")
                        {
                            paste = "";
                            iarg++; iarg++;
                        }
                    }
                    // input cell ref
                    else if (iarg == 2)
                    {
                        string[] cellArr = args[i].Split(':');
                        if (cellArr.Length == 1)
                        {
                            cellA = ExcelCellRef(cellArr[0]);
                        }
                        else
                        {
                            cellA = ExcelCellRef(cellArr[0]);
                            cellB = ExcelCellRef(cellArr[1]);
                            iarg++;
                        }
                    }

                    else if (iarg == 3)
                    {
                        cellB = ExcelCellRef(args[i]);
                    }
                    // output cell ref
                    else if (iarg == 4)
                    {
                        string[] cellArr = args[i].Split(':');
                        if (cellArr.Length == 1)
                        {
                            outCellA = ExcelCellRef(cellArr[0]);
                        }
                        else
                        {
                            outCellA = ExcelCellRef(cellArr[0]);
                            outCellB = ExcelCellRef(cellArr[1]);
                            iarg++;
                        }
                    }
                    else if (iarg == 5)
                    {
                        outCellB = ExcelCellRef(args[i]);
                    }

                    // output charts
                    else if (iarg > 5)
                    {
                        charts.Add(args[i]);
                    }

                    iarg++;
                }
            }

            // Print header
            if (showMan)
            {
                Print(@"Usage: excel [OPTIONS] ExcelFile PasteFile Cell1 Cell2 OutCell1 OutCell2 [Chart1 [Chart2 ..]]
   or: excel =FORMULA

Opens ExcelFile and places the contents of PasteFile from position given.
Position is the range between Cell1 and Cell2. Unused cells are cleared.
Echos all filled rows in the range between OutCell1 and OutCell2.
Any charts named will be saved to <ExcelFile_ChartN>.png.
'Sheet.ChartN' can be used if multiple charts has the same name.

    -p PasteSheet   Select the sheet that should be pasted to.
    -s OutSheet     Select the sheet that should be outputted.
    -m Macro        Run macro after paste. If -m is used multiples
                    times, more than 1 macro can be executed.
    -n              Do not save workbook
    -w              display Excel dialogs. Default is to surpress.
    -#              Replace errors with blanks in output
    -b [CHAR]       Set cell-spacing character in output to 
                    'space' or 'CHAR' (default is 'tab')
    -t              Insert PasteFile as text instead of numbers

If '~' is specified as PasteFile no file is loaded and Cell1 and Cell2 
should not be specified.

Version 1.0. Report bugs to <*****@*****.**>");
                Environment.Exit(0);
            }



            if (infile.StartsWith("="))
            {
                string result = "";
                try
                {
                    result = ExcelMath.Calc(infile);
                }
                catch (Exception ex) {
                    Console.Error.WriteLine("Error: " + ex.Message);
#if DEBUG
                    Console.ReadKey();
#endif
                    Environment.Exit(1);
                }

                Print(result);
#if DEBUG
                Console.ReadKey();
#endif
                Environment.Exit(0);
            }

            // open file
            if (piped == false && infile == "")
            {
                Error("No file given.", 1);
            }
            System.IO.TextReader stream = new StringReader(pipedText);
            if (!piped && paste.Length > 0)
            {
                try { stream = new StreamReader(paste); }
                catch (Exception e) { Error("Unable to open file: " + paste + "\n\n" + errLine + "\n\n" + e.ToString(), 1); }
            }

            // number of lines and columns
            int N = 0;
            int C = 0;

            // input data, as numbers and text. only one will be used
            double[,] cells  = null;
            string[,] sCells = null;


            // if pasted text
            if (paste.Length > 0)
            {
                String          line;
                List <string[]> strings = new List <string[]>();

                // trim all lines and split between words
                while ((line = stream.ReadLine()) != null)
                {
                    line = line.Replace(",", " ");
                    line = line.Replace("\t", " ");
                    line = line.Trim();
                    line = System.Text.RegularExpressions.Regex.Replace(line, @"\s+", " ");
                    if (asText)
                    {
                        //if (line.Length > 0)
                        strings.Add(line.Split(' '));
                    }
                    else if (line.Length > 0 && !line.StartsWith("#"))
                    {
                        strings.Add(line.Split(' '));
                    }
                }

                // set number of rows and columns
                N = strings.Count();
                C = 0;
                foreach (string[] str in strings)
                {
                    if (str.Length > C)
                    {
                        C = str.Length;
                    }
                }

                // convert input data to a format the Excel-interop understands: var[,]
                if (asText)
                {
                    sCells = new string[N, C];
                }
                else
                {
                    cells = new double[N, C];
                }

                // parse all cells and add to array
                for (int i = 0; i < N; i++)
                {
                    for (int j = 0; j < strings[i].Length; j++)
                    {
                        try
                        {
                            if (asText)
                            {
                                sCells[i, j] = strings[i][j];
                            }
                            else
                            {
                                cells[i, j] = double.Parse(strings[i][j]);
                            }
                        }
                        catch (Exception e) { Error("Unable to parse number in paste file, line " + (i + 1) + ", column " + (j + 1) + ":\n" + strings[i][j] + "\n\n" + errLine + "\n\n" + e.ToString(), 1); }
                    }
                }
            }


            Excel.Range startCell;
            Excel.Range endCell;

            try
            {
                // open excel app
                oXL = new Excel.Application();

                if (!warning)
                {
                    oXL.DisplayAlerts = false;
                }

                try
                {
                    // try to open the selected excel file
                    // we turn of errors, since excel prompts for macro-enabled files and other things
                    oXL.DisplayAlerts = false;
                    // we need the absolute file path, since excel defaults to the user home dir, not the current working dir :S
                    oWB = oXL.Workbooks.Open(Path.GetFullPath(infile));
                    // turn back on warnings if wanted
                    if (warning)
                    {
                        oXL.DisplayAlerts = true;
                    }
                }
                catch (Exception e) { throw new System.Exception("Unable to open file: " + Path.GetFullPath(infile) + "\n\n" + errLine + "\n\n" + e.ToString()); }

                // set the active sheet
                if (active.Length > 0)
                {
                    try
                    {
                        oSheet = (Excel._Worksheet)oWB.Sheets[active];
                    }
                    catch (Exception e) { throw new System.Exception("Unable to select worksheet: " + active + "\n\n" + errLine + "\n\n" + e.ToString()); }
                }
                else
                {
                    oSheet = (Excel._Worksheet)oWB.Worksheets[1];
                }


                // insert data
                if (paste.Length > 0)
                {
                    // if only columns are specified, find the amount of rows used
                    if (cellA[0] == 0 && cellB[0] == 0)
                    {
                        string      cell = GetExcelColumnName(cellA[1]) + ":" + GetExcelColumnName(cellB[1]);
                        Excel.Range r    = (Excel.Range)oSheet.UsedRange.Columns[cell, Type.Missing];
                        cellA[0] = 1;
                        cellB[0] = r.Rows.Count;
                    }

                    // select and paste values
                    try
                    {
                        startCell = (Excel.Range)oSheet.Cells[cellA[0], cellA[1]];
                        endCell   = (Excel.Range)oSheet.Cells[cellB[0], cellB[1]];
                        oSheet.get_Range(startCell, endCell).Value = null;

                        endCell = (Excel.Range)oSheet.Cells[cellA[0] + N - 1, cellA[1] + C - 1];

                        if (asText)
                        {
                            oSheet.get_Range(startCell, endCell).Value2 = sCells;
                        }
                        else
                        {
                            oSheet.get_Range(startCell, endCell).Value2 = cells;
                        }
                    }
                    catch (Exception e)
                    {
                        string inputCell = GetExcelColumnName(cellA[1]) + cellA[0] + ":" + GetExcelColumnName(cellB[1]) + cellB[0];
                        throw new System.Exception("Unable to select input cells:\n\n         " + inputCell + "\n\n" + errLine + "\n\n" + e.ToString());
                    }
                }

                // run macro
                for (int i = 0; i < macro.Count; i++)
                {
                    try
                    {
                        oXL.Run(macro[i]);
                    }
                    catch (Exception e) { throw new System.Exception("Unable to run macro: " + macro[i] + "\n\n" + errLine + "\n\n" + e.ToString()); }
                }

                // force workbook refresh
                oXL.Calculate();

                // go to result sheet
                if (sheet.Length > 0)
                {
                    try
                    {
                        oSheet = (Excel._Worksheet)oWB.Sheets[sheet];
                    }
                    catch (Exception e) { throw new System.Exception("Unable to select output sheet:" + sheet + "\n\n" + errLine + "\n\n" + e.ToString()); }
                }

                // save charts

                foreach (Excel.Worksheet cSheet in oWB.Worksheets)
                {
                    // loop trough all charts

                    Excel.ChartObjects xlCharts = (Excel.ChartObjects)cSheet.ChartObjects(Type.Missing);
                    for (int i = 1; i <= xlCharts.Count; i++)
                    {
                        Excel.ChartObject oChart = (Excel.ChartObject)xlCharts.Item(i);
                        Excel.Chart       chart  = oChart.Chart;

                        string chartName = "";
                        if (charts.Contains(cSheet.Name + "." + oChart.Name))
                        {
                            chartName = cSheet.Name + "." + oChart.Name;
                        }
                        else if (charts.Contains(oChart.Name))
                        {
                            chartName = oChart.Name;
                        }

                        // if chart is specified for output, save it
                        if (chartName.Length > 0)
                        {
                            int id = charts.FindIndex(s => s == chartName);

                            charts.RemoveAt(id);

                            try
                            {
                                // we need full path name again.. excel defaults to user home dir...
                                string saveas = Path.GetFullPath(infile);
                                saveas = Path.GetDirectoryName(saveas) + "\\" + Path.GetFileNameWithoutExtension(saveas);
                                saveas = saveas + "_" + chartName + ".png";
                                chart.Export(saveas, "PNG");
                            }
                            catch (Exception e) { throw new System.Exception("Unable to save chart '" + chartName + "':\n\n" + errLine + "\n\n" + e.ToString()); }
                        }
                    }
                }

                // if any charts was not found; throw an error.
                if (charts.Count > 0)
                {
                    string list = "";
                    foreach (string s in charts)
                    {
                        list += s + ", ";
                    }
                    throw new Exception("Unable to find chart(s): " + list);
                }

                // if only columns are specified, find amount of rows to use
                if (outCellA[0] == 0 && outCellB[0] == 0)
                {
                    if (outCellB[1] == 0)
                    {
                        outCellB[1] = oSheet.UsedRange.Columns.Count;
                    }

                    string      cell = GetExcelColumnName(outCellA[1]) + ":" + GetExcelColumnName(outCellB[1]);
                    Excel.Range r    = (Excel.Range)oSheet.UsedRange.Columns[cell, Type.Missing];
                    outCellA[0] = 1;
                    outCellB[0] = r.Rows.Count;
                }


                // select the output cell range
                try
                {
                    startCell = (Excel.Range)oSheet.Cells[outCellA[0], outCellA[1]];
                    endCell   = (Excel.Range)oSheet.Cells[outCellB[0], outCellB[1]];
                }
                catch (Exception e)
                {
                    string outcell = GetExcelColumnName(outCellA[1]) + outCellA[0] + ":" + GetExcelColumnName(outCellB[1]) + outCellB[0];
                    throw new System.Exception("Unable to select output cells:\n            " + outcell + "\n\n" + errLine + "\n\n" + e.ToString());
                }


                // get output from selected cells
                object[,] arr = null;
                try
                {
                    Excel.Range r = (Excel.Range)oSheet.get_Range(startCell, endCell);
                    // if only 1 cell is selected, excel will return an object instead of object array!
                    if (r.Cells.Count == 1)
                    {
                        arr       = new object[2, 2];
                        arr[1, 1] = r.Cells.Value2;
                    }
                    else
                    {
                        arr = r.Cells.Value2 as object[, ];
                    }
                }
                catch (Exception e)
                {
                    string outcell = GetExcelColumnName(outCellA[1]) + outCellA[0] + ":" + GetExcelColumnName(outCellB[1]) + outCellB[0];
                    throw new System.Exception("Invalid OutCells given. Unable to retrieve data:\n            " + outcell + "\n\n" + errLine + "\n\n" + e.ToString());
                }

                List <string> results = new List <string>();
                int           last    = 0;
                N = outCellB[0] - outCellA[0] + 1;
                C = outCellB[1] - outCellA[1] + 1;

                // loop trough output rows
                for (int i = 1; i <= N; i++)
                {
                    // loop trough output columns
                    string s = "";
                    for (int j = 1; j <= C; j++)
                    {
                        // check if cell contains an error
                        if (arr[i, j] is Int32)
                        {
                            if (outErr)
                            {
                                int    eCode = (int)arr[i, j];
                                string e     = "";

                                if (eCode == -2146826281)
                                {
                                    e = "#DIV/0!";
                                }
                                else if (eCode == -2146826246)
                                {
                                    e = "#N/A";
                                }
                                else if (eCode == -2146826259)
                                {
                                    e = "#NAME?";
                                }
                                else if (eCode == -2146826288)
                                {
                                    e = "#NULL!";
                                }
                                else if (eCode == -2146826252)
                                {
                                    e = "#NUM!";
                                }
                                else if (eCode == -2146826265)
                                {
                                    e = "#REF!";
                                }
                                else if (eCode == -2146826273)
                                {
                                    e = "#VALUE!";
                                }
                                // no more error codes exists (?) as of 2013.. But to be sure / support future ones:
                                else
                                {
                                    e = "#ERR" + eCode.ToString();
                                }

                                s = s + e + " " + cellSpacer;
                            }
                            else
                            {
                                s = s + " " + cellSpacer;
                            }
                        }
                        else if (arr[i, j] != null)
                        {
                            s = s + arr[i, j].ToString() + cellSpacer;
                        }
                        else
                        {
                            s = s + " " + cellSpacer;
                        }
                    }
                    // remove cellspacer from last column
                    if (C > 0)
                    {
                        results.Add(s.Remove(s.Length - 1).TrimEnd());
                    }
                    // record last row column with content
                    if (s.Replace(cellSpacer, " ").TrimEnd().Length > 0)
                    {
                        last = results.Count();
                    }
                }

                // write output to console
                for (int i = 0; i < last; i++)
                {
                    Console.WriteLine(results[i]);
                }

                // save file
                if (save)
                {
                    // if macros are enabled, excel would prompt about saving
                    oXL.DisplayAlerts = false;
                    oWB.Save();
                }
            }

            // catch any exception
            catch (Exception theException)
            {
                Error(errLine + "\n  Error: " + theException.Message, 1);
            }

            finally
            {
                // clean up and exit
                CleanUp();
            }


            #if DEBUG
            Console.ReadKey();
            #endif
        }
Esempio n. 26
0
        public void ExportChartsAsCombined(string item, string imageFile, int imagesPerRow, int splitBoardWidth)
        {
            if (string.IsNullOrEmpty(item) || string.IsNullOrEmpty(imageFile))
            {
                throw new ArgumentNullException($"The parameter item or imageFile is null");
            }

            Excel.Worksheet    xlWorkSheet = (Excel.Worksheet)_xlWorkBook.Worksheets.Item[item];
            Excel.ChartObjects xlCharts    = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);

            IList <string> chartFiles = new List <string>();
            string         folder     = Path.GetDirectoryName(imageFile);
            string         format     = Path.GetExtension(imageFile).TrimStart('.');

            foreach (Excel.ChartObject xlChart in xlCharts)
            {
                Excel.Chart chartPage = xlChart.Chart;
                var         chartFile = Path.Combine(folder, $"{chartPage.Name}.{format}");
                chartFiles.Add(chartFile);
                chartPage.Export(chartFile, format, _misValue);
            }

            var chartImage = chartFiles.FirstOrDefault();

            if (chartImage == null)
            {
                throw new ArgumentNullException($"No chart found");
            }

            var rows = (int)Math.Ceiling(chartFiles.Count / (decimal)imagesPerRow);

            var imageBitmap = new Bitmap(chartImage);

            var combinedImageWidth  = imageBitmap.Width * imagesPerRow + (imagesPerRow - 1) * splitBoardWidth;
            var combinedImageHeight = imageBitmap.Height * rows + (imagesPerRow - 1) * splitBoardWidth;
            var combinedImage       = new Bitmap(combinedImageWidth, combinedImageHeight);

            // copy images row by row
            int combinedImageY = 0;

            for (int row = 0; row < rows; row++)
            {
                // copy images one after one in one row
                for (int colomn = 0; colomn < imagesPerRow; colomn++)
                {
                    int imageIndex     = colomn + row * imagesPerRow;
                    var image          = new Bitmap(chartFiles[imageIndex]);
                    var combinedImageX = (imageBitmap.Width + splitBoardWidth) * colomn;
                    for (int y = 0; y < image.Height; y++)
                    {
                        // copy image
                        for (int x = 0; x < image.Width; x++)
                        {
                            combinedImage.SetPixel(combinedImageX + x, combinedImageY + y, image.GetPixel(x, y));
                        }

                        // draw vertical boarder
                        for (int i = 0; i < splitBoardWidth; i++)
                        {
                            var boarderX = combinedImageX + imageBitmap.Width + i;
                            if (boarderX < combinedImageWidth)
                            {
                                combinedImage.SetPixel(boarderX, combinedImageY + y, Color.Black);
                            }
                        }
                    }
                }
                combinedImageY += imageBitmap.Height;
                // draw horizon boarder
                {
                    if (combinedImageY < combinedImageHeight)
                    {
                        for (int x = 0; x < combinedImageWidth; x++)
                        {
                            combinedImage.SetPixel(x, combinedImageY, Color.Black);
                        }
                        combinedImageY++;
                    }
                }
            }
            combinedImage.Save(imageFile);
        }