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]);
}
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);
}
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();
}
}
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));
}
//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++;
}
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]);
}
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);
}
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();
}
public static void CreateWorkBook(string filename)
{
SpreadsheetGear.IWorkbookSet workbookSet = SpreadsheetGear.Factory.GetWorkbookSet();
SpreadsheetGear.IWorkbook workbook = workbookSet.Workbooks.Add();
workbook.SaveAs(filename, FileFormat.Excel8);
}
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);
}
