Esempio n. 1
0
        private void CopyToNew(ref IWorkbook interim, int columnId, string cell) // this method copy one column to interrim column worksheet
        {
            SpreadsheetGear.IWorkbookSet workbookset = SpreadsheetGear.Factory.GetWorkbookSet();
            SpreadsheetGear.IWorkbook    workbook    = workbookset.Workbooks.OpenFromMemory(GetWorkbookFile(columnId));
            string columnName    = GetRequiredCell(columnId).ColumnName;
            string worksheetName = GetCellWorksheetName(columnId);
            string address       = FindAddressByName(workbook.Worksheets[worksheetName], columnName);
            string columnLast    = FindLastRow(address, workbook.Worksheets[worksheetName], GetRequiredCell(columnId).StartRow);
            int    diference     = GetRequiredCell(columnId).StartRow - GetRequiredCell(columnId).HeaderRow;

            address = Modify(diference, address);
            workbook.Worksheets[worksheetName].Cells[address + ":" + columnLast].Copy(interim.Worksheets[0].Cells[cell]);
        }
Esempio n. 2
0
        public string GetOneData(int cellId) //this method gives us one data
        {
            SpreadsheetGear.IWorkbookSet workbookset = SpreadsheetGear.Factory.GetWorkbookSet();
            SpreadsheetGear.IWorkbook    workbook    = workbookset.Workbooks.OpenFromMemory(GetWorkbookFile(cellId));
            string columnName    = GetRequiredCell(cellId).ColumnName;
            string worksheetName = GetCellWorksheetName(cellId);
            string address       = FindAddressByName(workbook.Worksheets[worksheetName], columnName);
            int    diference     = GetRequiredCell(cellId).StartRow - GetRequiredCell(cellId).HeaderRow;

            address = Modify(diference, address);
            string data = workbook.Worksheets[worksheetName].Cells[address].Value.ToString();

            return(data);
        }
Esempio n. 3
0
 private void WorksheetInitialization(int p1, byte[] p2) //initialization worksheet and call class who initialization cell
 {                                                       // p1 is worksheet id p2 is file
     SpreadsheetGear.IWorkbookSet workbookSet = SpreadsheetGear.Factory.GetWorkbookSet();
     SpreadsheetGear.IWorkbook    workbook    = workbookSet.Workbooks.OpenFromMemory(p2);
     foreach (IWorksheet worksheet in workbook.Sheets)
     {
         var workSheet = new Worksheet();
         workSheet.WorkbookId    = p1;
         workSheet.WorksheetName = worksheet.Name;
         db.Worksheets.Add(workSheet);
         db.SaveChanges();
         var SaveCells = new OutputCellFill(worksheet, workSheet.Id); //Saving Cell to database
         SaveCells.SaveToDatabase();
     }
 }
Esempio n. 4
0
        private byte[] GetOutFile(int columnWhere)
        {
            SpreadsheetGear.IWorkbookSet workbookset   = SpreadsheetGear.Factory.GetWorkbookSet();
            SpreadsheetGear.IWorkbook    interim       = workbookset.Workbooks.Add();
            SpreadsheetGear.IWorkbook    workbookWhere = workbookset.Workbooks.OpenFromMemory(GetWhereToCopy(columnWhere));
            Cell   requiredcell = GetRequiredCell(columnWhere); // this 3 data until number need to workbookWhere
            string name         = GetCellWorksheetName(columnWhere);
            string address      = FindColumnWhereCopy(workbookWhere, requiredcell.ColumnName, name);

            PutemporaryData(ref interim);
            int number = interim.Worksheets[0].Cells["A1"].CurrentRegion.RowCount; // number to need for cycle

            if (actions.Length == 2)                                               // because last element is ""
            {
                return(JustCopyOperation(requiredcell, interim, workbookWhere, name, address, number));
            }
            return(MathActions(interim, workbookWhere, number, requiredcell, name, address));
        }
Esempio n. 5
0
 //this method calculate custom formula to required interim workbook column
 private void CostumFormulaToNew(ref IWorkbook interim, string p, string cell) // p is customformula
 {
     SpreadsheetGear.IWorkbookSet workbookset   = SpreadsheetGear.Factory.GetWorkbookSet();
     SpreadsheetGear.IWorkbook    costumFormula = workbookset.Workbooks.Add();
     if (customcolumns[number] != "#")
     {
         string[] cColumns = customcolumns[number].Split(' ');
         for (int i = 0; i < cColumns.Length - 1; i++)
         {
             string customCell = columnChar[i] + 1.ToString();
             CopyToNew(ref costumFormula, int.Parse(cColumns[i]), customCell);// prepare costum formula required columns
         }
         int quantity = costumFormula.Worksheets[0].Cells["A1"].CurrentRegion.RowCount;
         PerformCustomFormula(ref costumFormula, p, cColumns.Length - 1, customnames[number].Split(' '), quantity);
         costumFormula.Worksheets[0].Cells["AA1:" + "AA" + quantity.ToString()].Copy(interim.Worksheets[0].Cells[cell]);
     }
     else
     {
         interim.Worksheets[0].Cells[cell].Value = costumFormula.Worksheets[0].EvaluateValue(p);
     }
     number++;
 }
Esempio n. 6
0
 private IWorksheet InitializationSpreedsheetGearWorksheet(string worksheetName) // Create needed worksheet
 {
     SpreadsheetGear.IWorkbookSet workbookSet    = SpreadsheetGear.Factory.GetWorkbookSet();
     SpreadsheetGear.IWorkbook    spreadworkbook = workbookSet.Workbooks.OpenFromMemory(workbook.WorkbookFile);
     return(spreadworkbook.Worksheets[worksheetName]);
 }
Esempio n. 7
0
 private IWorkbook GetWorkbookFile(byte[] p) // p - excel workbook bibary file
 {
     SpreadsheetGear.IWorkbookSet workbookset = SpreadsheetGear.Factory.GetWorkbookSet();
     SpreadsheetGear.IWorkbook    workbook    = workbookset.Workbooks.OpenFromMemory(p); // workbook is required excel file, not binary
     return(workbook);
 }
Esempio n. 8
0
        public static void Main(string[] args)
        {
            double numberOfRuns = 1000000.0;

            //generator = new RandomAdapter();
            generator = new UniformGenerator();
            int factor = 3;

            if (args.Length > 0)
            {
                factor       = Convert.ToInt32(args[0]);
                numberOfRuns = Convert.ToDouble(args[1]);
            }

            Console.WriteLine(string.Format("{0} workers used in this run of {1}", factor, numberOfRuns));


            PointEstimate estimate = new PointEstimate();
            Stopwatch     sw       = Stopwatch.StartNew();

            using (var q = new AsyncWorkQueue <double>(factor))
            {
                for (int i = 0; i < factor; i++)
                {
                    var id = i;
                    q.Start(delegate
                    {
                        SpreadsheetGear.IWorkbookSet workbookSet = SpreadsheetGear.Factory.GetWorkbookSet();
                        IWorkbook workbook = workbookSet.Workbooks.Open(@"c:\data\dev\Async\AsyncWorkQueueTest\AsyncWorkQueueTest\excel\helloWorld_template.xls");
                        for (long j = 0; j < numberOfRuns / factor; j++)
                        {
                            try
                            {
                                double annRevenue = generate(30, 70);
                                double annCosts   = generate(5, 20);
                                double numYears   = generate(2, 6);
                                var sheet         = workbook.Worksheets["Hello"];
                                sheet.Cells["AnnRevenue"].Value = Convert.ToDouble(annRevenue);
                                sheet.Cells["AnnCosts"].Value   = Convert.ToDouble(annCosts);
                                sheet.Cells["numYears"].Value   = Convert.ToDouble(numYears);
                                var npvRange = sheet.Cells["npv"];
                                double npv   = (double)npvRange.Value;
                                //Console.WriteLine("annRevenue = " + annRevenue + ", annCosts = " + annCosts + ", numYears = " + numYears+", NPV = "+npv);
                                estimate.nextValueIs(npv);
                            }
                            catch (Exception e)
                            {
                                Console.WriteLine("Process " + id + ", run " + j + ", exception thrown!");
                                Console.WriteLine(e.StackTrace);
                            }
                        }
                        return(0);
                    });
                    Console.WriteLine("Started delegate " + i);
                }

                Console.WriteLine("*** Done queueing.");


                foreach (var result in q.GetResults())
                {
                    if (result.IsError)
                    {
                        //handle / log errors here
                        throw result.Error;
                    }
                }
                SummaryStatistics statistics = estimate.computeConfidenceIntervalForPercent(99);
                sw.Stop();
                Console.WriteLine("Mean: " + statistics.pointEstimate + ", [" + statistics.cLower + ", " + statistics.cUpper + "]");
                Console.WriteLine("Elapsed time = " + sw.Elapsed);
            }



            //Test(new List<int>() { 5, 6, 5, 4, 3, 2, 1 });

            Console.ReadLine();
        }
Esempio n. 9
0
 public static void CreateWorkBook(string filename)
 {
     SpreadsheetGear.IWorkbookSet workbookSet = SpreadsheetGear.Factory.GetWorkbookSet();
     SpreadsheetGear.IWorkbook    workbook    = workbookSet.Workbooks.Add();
     workbook.SaveAs(filename, FileFormat.Excel8);
 }
Esempio n. 10
0
        public static string ProcessLogFile(string logFilePathName, string graphSetName, GraphConfigsBE config)
        {
            // Activate SpreadsheetGear
            SpreadsheetGear.Factory.SetSignedLicense("SpreadsheetGear.License, Type=Trial, Product=BND, Expires=2019-07-27, Company=Tom Bruns, [email protected], Signature=orH+RFO9hRUB8SJXBSWQZJuXP9OfSkV9fLcU9suehfgA#dgunwBK9VssTgnfowKGWaqMNfVgwVetxEWbayzGM1uIA#K");

            // Create a new empty workbook in a new workbook set.
            SpreadsheetGear.IWorkbookSet workbookSet = SpreadsheetGear.Factory.GetWorkbookSet();

            // import the csv file
            SpreadsheetGear.IWorkbook workbook = workbookSet.Workbooks.Open(logFilePathName);

            // get a reference to the active (only) worksheet
            SpreadsheetGear.IWorksheet dataWorksheet = workbook.ActiveWorksheet;
            dataWorksheet.Name = System.IO.Path.GetFileNameWithoutExtension(logFilePathName);

            // freeze 1st row & 1st column(to make scrolling more user friendly)
            dataWorksheet.WindowInfo.ScrollColumn = 0;
            dataWorksheet.WindowInfo.SplitColumns = 1;
            dataWorksheet.WindowInfo.ScrollRow    = 0;
            dataWorksheet.WindowInfo.SplitRows    = 1;
            dataWorksheet.WindowInfo.FreezePanes  = true;

            // build index of column names
            var columnNameXref = BuildColumnNameXref(dataWorksheet);

            // find the config for the requested Set of Graphs
            GraphSetBE graphSet = config.GraphSets.Where(gs => gs.SetName.ToLower() == graphSetName.ToLower()).FirstOrDefault();

            if (graphSet == null)
            {
                List <string> availableGraphSetNames = config.GraphSets.Select(gs => gs.SetName).ToList();

                throw new ApplicationException($"Requested GraphSet: [{graphSetName}], Options: [{String.Join(",", availableGraphSetNames)}]");
            }

            // do any required conversions on the source data (ex Radians to Degrees)
            if (graphSet.AngleConversions != null)
            {
                foreach (AngleConversionBE angleConversion in graphSet.AngleConversions)
                {
                    PerformAngleConversion(dataWorksheet, angleConversion, columnNameXref);
                }

                // rebuild column name index
                columnNameXref = BuildColumnNameXref(dataWorksheet);
            }

            // resize column widths to fit header text
            dataWorksheet.UsedRange.Columns.AutoFit();

            // ====================================
            // create any new sheets with a subset of the original columns to make analysis easier
            // ====================================
            foreach (NewSheetBE newSheet in graphSet.NewSheets)
            {
                BuildNewSheet(dataWorksheet, newSheet, columnNameXref);
            }

            string pathNameColumnName = graphSet.PathNameColumnName;

            // ====================================
            // build a new line graph for each one in the selected graphset
            // ====================================
            foreach (LineGraphBE lineGraph in graphSet.LineGraphs)
            {
                BuildLineGraph(dataWorksheet, lineGraph, columnNameXref, pathNameColumnName);
            }

            // ====================================
            // build a new XY graph for each one in the selected graphset
            // fyi: these were separated because they require slightly different config data structures
            // ====================================
            foreach (XYGraphBE xyGraph in graphSet.XYGraphs)
            {
                BuildXYGraph(dataWorksheet, xyGraph, columnNameXref, pathNameColumnName);
            }

            // ====================================
            // build a new bar graph for each one in the selected graphset
            // ====================================
            foreach (BarGraphBE barGraph in graphSet.BarGraphs)
            {
                BuildBarGraph(dataWorksheet, barGraph, columnNameXref, pathNameColumnName);
            }

            // ====================================
            // build a new histogram for each one in the selected graphset
            // ====================================
            foreach (HistogramBE histogram in graphSet.Histograms)
            {
                BuildHistogram(dataWorksheet, histogram, columnNameXref, pathNameColumnName);
            }

            // save the workbook
            string pathName = GetCellValue <string>(dataWorksheet, graphSet.PathNameColumnName, 1, columnNameXref);

            string folderPathName = System.IO.Path.GetDirectoryName(logFilePathName);
            string fileName       = System.IO.Path.GetFileNameWithoutExtension(logFilePathName);

            fileName = (!string.IsNullOrEmpty(pathName)) ? $"{fileName}_{pathName}" : fileName;
            fileName = System.IO.Path.ChangeExtension(fileName, @".xlsx");
            string xlsFilePathName = System.IO.Path.Combine(folderPathName, fileName);

            workbook.SaveAs(xlsFilePathName, FileFormat.OpenXMLWorkbook);

            return(xlsFilePathName);
        }