private static Excel.Range PutSampleData(Excel.Worksheet workSheet)
{
workSheet.Cells[2, 2].Value = "Datum";
workSheet.Cells[3, 2].Value = DateTime.Now.ToShortDateString();
workSheet.Cells[4, 2].Value = DateTime.Now.ToShortDateString();
workSheet.Cells[5, 2].Value = DateTime.Now.ToShortDateString();
workSheet.Cells[6, 2].Value = DateTime.Now.ToShortDateString();
workSheet.Cells[2, 3].Value = "Columns1";
workSheet.Cells[3, 3].Value = 25;
workSheet.Cells[4, 3].Value = 33;
workSheet.Cells[5, 3].Value = 30;
workSheet.Cells[6, 3].Value = 22;
workSheet.Cells[2, 4].Value = "Column2";
workSheet.Cells[3, 4].Value = 25;
workSheet.Cells[4, 4].Value = 33;
workSheet.Cells[5, 4].Value = 30;
workSheet.Cells[6, 4].Value = 22;
workSheet.Cells[2, 5].Value = "Column3";
workSheet.Cells[3, 5].Value = 25;
workSheet.Cells[4, 5].Value = 33;
workSheet.Cells[5, 5].Value = 30;
workSheet.Cells[6, 5].Value = 22;
return(workSheet.get_Range("$B2:$E6"));
}
private static void AddColumnNames(Excel.Worksheet sheet, object[] tempHeadingArray)
{
var columnNameRange = sheet.get_Range(sheet.Cells[3, 3], sheet.Cells[3, tempHeadingArray.Length + 2]);
columnNameRange.Style = "NewStyle";
columnNameRange.Value = tempHeadingArray;
columnNameRange.UseStandardWidth = true;
}
static void Main(string[] args)
{
Console.WriteLine("NetOffice Release Performance Test - 15000 Cells.");
Console.WriteLine("Write simple text, change Font, NumberFormat, WrapText and add a comment.");
// start excel, and get a new sheet reference
Excel.Application excelApplication = CreateExcelApplication();
Excel.Worksheet sheet = excelApplication.Workbooks.Add().Worksheets.Add() as Excel.Worksheet;
// do test 10 times
List <TimeSpan> timeElapsedList = new List <TimeSpan>();
for (int i = 1; i <= 10; i++)
{
sheet.UsedRange.ClearComments();
DateTime timeStart = DateTime.Now;
for (int y = 1; y <= 15000; y++)
{
string rangeAdress = "$A" + y.ToString();
Excel.Range cellRange = sheet.get_Range(rangeAdress);
cellRange.Value = "value";
cellRange.Font.Name = "Verdana";
cellRange.NumberFormat = "@";
cellRange.WrapText = true;
cellRange.AddComment("Sample Comment");
}
TimeSpan timeElapsed = DateTime.Now - timeStart;
// display info and dispose references
Console.WriteLine("Time Elapsed: {0}", timeElapsed);
timeElapsedList.Add(timeElapsed);
sheet.DisposeChildInstances();
}
// display info & log to file
TimeSpan timeAverage = AppendResultToLogFile(timeElapsedList, "Test3-NetOffice.log");
Console.WriteLine("Time Average: {0}{1}Press any key...", timeAverage, Environment.NewLine);
Console.Read();
// release & quit
excelApplication.Quit();
excelApplication.Dispose();
}
static void Main(string[] args)
{
Console.WriteLine("NetOffice Release Performance Test - 10000 Cells.");
Console.WriteLine("Write simple text, change Font, NumberFormat and do a BorderArround.");
// start excel, and get a new sheet reference
Excel.Application excelApplication = CreateExcelApplication();
Excel.Worksheet sheet = excelApplication.Workbooks.Add().Worksheets.Add() as Excel.Worksheet;
// do test 10 times
List <TimeSpan> timeElapsedList = new List <TimeSpan>();
for (int i = 1; i <= 10; i++)
{
DateTime timeStart = DateTime.Now;
for (int y = 1; y <= 10000; y++)
{
string rangeAdress = "$A" + y.ToString();
Excel.Range cellRange = sheet.get_Range(rangeAdress);
cellRange.Value = "value";
cellRange.Font.Name = "Verdana";
cellRange.NumberFormat = "@";
cellRange.BorderAround(XlLineStyle.xlDouble, XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic, 0);
}
TimeSpan timeElapsed = DateTime.Now - timeStart;
// display info and dispose references
Console.WriteLine("Time Elapsed: {0}", timeElapsed);
timeElapsedList.Add(timeElapsed);
sheet.DisposeChildInstances();
}
// display info & log to file
TimeSpan timeAverage = AppendResultToLogFile(timeElapsedList, "Test2-NetOffice.log");
Console.WriteLine("Time Average: {0}{1}Press any key...", timeAverage, Environment.NewLine);
Console.Read();
// release & quit
excelApplication.Quit();
excelApplication.Dispose();
}
private static void AddColumnNames(Excel.Worksheet sheet)
{
string[] tempHeadingArray =
{
"FirstName",
"LastName",
"Income",
"Tax",
"Total Income"
};
var columnNameRange = sheet.get_Range(sheet.Cells[3, 1], sheet.Cells[3, tempHeadingArray.Length]);
columnNameRange.Style = "NewStyle";
columnNameRange.Font.Size = 10;
columnNameRange.Value = tempHeadingArray;
columnNameRange.UseStandardWidth = true;
columnNameRange.ColumnWidth = 14;
columnNameRange.WrapText = true;
}
static void Main(string[] args)
{
Console.WriteLine("NetOffice Release 1.3 Performance Test - 5000 Cells.");
Console.WriteLine("Write simple text.");
// start excel, and get a new sheet reference
Excel.Application excelApplication = CreateExcelApplication();
Excel.Worksheet sheet = excelApplication.Workbooks.Add().Worksheets.Add() as Excel.Worksheet;
// do test 10 times
List <TimeSpan> timeElapsedList = new List <TimeSpan>();
for (int i = 1; i <= 10; i++)
{
DateTime timeStart = DateTime.Now;
for (int y = 1; y <= 5000; y++)
{
string rangeAdress = "$A" + y.ToString();
sheet.get_Range(rangeAdress).Value = "value";
}
TimeSpan timeElapsed = DateTime.Now - timeStart;
// display info and dispose references
Console.WriteLine("Time Elapsed: {0}", timeElapsed);
timeElapsedList.Add(timeElapsed);
sheet.DisposeChildInstances();
}
// display info & log to file
TimeSpan timeAverage = AppendResultToLogFile(timeElapsedList, "Test1-NetOffice.log");
Console.WriteLine("Time Average: {0}{1}Press any key...", timeAverage, Environment.NewLine);
Console.Read();
// release & quit
excelApplication.Quit();
excelApplication.Dispose();
}
public TestResult DoTest()
{
Excel.Application application = null;
DateTime startTime = DateTime.Now;
try
{
// start excel and turn off Application msg boxes
application = new Excel.Application();
application.DisplayAlerts = false;
// add a new workbook
Excel.Workbook workBook = application.Workbooks.Add();
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];
/* some kind of numerics */
// the given thread culture in all latebinding calls are stored in NetOffice.Settings.
// you can change the culture. default is en-us.
CultureInfo cultureInfo = NetOffice.Settings.Default.ThreadCulture;
string Pattern1 = string.Format("0{0}00", cultureInfo.NumberFormat.CurrencyDecimalSeparator);
string Pattern2 = string.Format("#{1}##0{0}00", cultureInfo.NumberFormat.CurrencyDecimalSeparator, cultureInfo.NumberFormat.CurrencyGroupSeparator);
workSheet.Range("A1").Value = "Type";
workSheet.Range("B1").Value = "Value";
workSheet.Range("C1").Value = "Formatted " + Pattern1;
workSheet.Range("D1").Value = "Formatted " + Pattern2;
int integerValue = 532234;
workSheet.Range("A3").Value = "Integer";
workSheet.Range("B3").Value = integerValue;
workSheet.Range("C3").Value = integerValue;
workSheet.Range("C3").NumberFormat = Pattern1;
workSheet.Range("D3").Value = integerValue;
workSheet.Range("D3").NumberFormat = Pattern2;
double doubleValue = 23172.64;
workSheet.Range("A4").Value = "double";
workSheet.Range("B4").Value = doubleValue;
workSheet.Range("C4").Value = doubleValue;
workSheet.Range("C4").NumberFormat = Pattern1;
workSheet.Range("D4").Value = doubleValue;
workSheet.get_Range("D4").NumberFormat = Pattern2;
float floatValue = 84345.9132f;
workSheet.Range("A5").Value = "float";
workSheet.Range("B5").Value = floatValue;
workSheet.Range("C5").Value = floatValue;
workSheet.Range("C5").NumberFormat = Pattern1;
workSheet.Range("D5").Value = floatValue;
workSheet.Range("D5").NumberFormat = Pattern2;
Decimal decimalValue = 7251231.313367m;
workSheet.Range("A6").Value = "Decimal";
workSheet.Range("B6").Value = decimalValue;
workSheet.Range("C6").Value = decimalValue;
workSheet.Range("C6").NumberFormat = Pattern1;
workSheet.Range("D6").Value = decimalValue;
workSheet.Range("D6").NumberFormat = Pattern2;
workSheet.Range("A9").Value = "DateTime";
workSheet.Range("B10").Value = cultureInfo.DateTimeFormat.FullDateTimePattern;
workSheet.Range("C10").Value = cultureInfo.DateTimeFormat.LongDatePattern;
workSheet.Range("D10").Value = cultureInfo.DateTimeFormat.ShortDatePattern;
workSheet.Range("E10").Value = cultureInfo.DateTimeFormat.LongTimePattern;
workSheet.Range("F10").Value = cultureInfo.DateTimeFormat.ShortTimePattern;
// DateTime
DateTime dateTimeValue = DateTime.Now;
workSheet.Range("B11").Value = dateTimeValue;
workSheet.Range("B11").NumberFormat = cultureInfo.DateTimeFormat.FullDateTimePattern;
workSheet.Range("C11").Value = dateTimeValue;
workSheet.Range("C11").NumberFormat = cultureInfo.DateTimeFormat.LongDatePattern;
workSheet.Range("D11").Value = dateTimeValue;
workSheet.Range("D11").NumberFormat = cultureInfo.DateTimeFormat.ShortDatePattern;
workSheet.Range("E11").Value = dateTimeValue;
workSheet.Range("E11").NumberFormat = cultureInfo.DateTimeFormat.LongTimePattern;
workSheet.Range("F11").Value = dateTimeValue;
workSheet.Range("F11").NumberFormat = cultureInfo.DateTimeFormat.ShortTimePattern;
// string
workSheet.Range("A14").Value = "String";
workSheet.Range("B14").Value = "This is a sample String";
workSheet.Range("B14").NumberFormat = "@";
// number as string
workSheet.Range("B15").Value = "513";
workSheet.Range("B15").NumberFormat = "@";
// set colums
workSheet.Columns[1].AutoFit();
workSheet.Columns[2].AutoFit();
workSheet.Columns[3].AutoFit();
workSheet.Columns[4].AutoFit();
return(new TestResult(true, DateTime.Now.Subtract(startTime), "", null, ""));
}
catch (Exception exception)
{
return(new TestResult(false, DateTime.Now.Subtract(startTime), exception.Message, exception, ""));
}
finally
{
if (null != application)
{
application.Quit();
application.Dispose();
}
}
}
internal void CreateWorkSheet(Worksheet worksheet)
{
try
{
worksheet.DisplayRightToLeft = false;
worksheet.Name = "Loop" + channekNumber;
worksheet.Range("B1:D1").Font.Bold = true;
worksheet.Range("B1").Value = "loop" + channekNumber;
worksheet.Range("C1").Value = "x";
worksheet.Range("D1").Value = "y";
worksheet.Range("A1").EntireColumn.ColumnWidth = 8.38;
worksheet.Range("B1").EntireColumn.ColumnWidth = 11.25;
worksheet.Range("C1").EntireColumn.ColumnWidth = 11.25;
worksheet.Range("D1").EntireColumn.ColumnWidth = 11.25;
worksheet.Range("A1:D50").HorizontalAlignment = XlHAlign.xlHAlignCenter;
List<System.Windows.Point> OrderPoints = Points.OrderBy(x => x.X).ToList();
List<System.Windows.Point> OrderDetectorPoints = DetectorPoints.OrderBy(x => x.X).ToList();
worksheet.Range("C1:D" + (Freqs.Length + 1)).BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic);
worksheet.Range("C1:D" + (Freqs.Length + 1)).Borders[XlBordersIndex.xlInsideVertical].LineStyle = XlLineStyle.xlDouble;
worksheet.Range("C1:D" + (Freqs.Length + 1)).Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlDouble;
worksheet.Range("C1:D" + (Freqs.Length + 1)).Borders[XlBordersIndex.xlInsideVertical].Weight = 4;
worksheet.Range("C1:D" + (Freqs.Length + 1)).Borders[XlBordersIndex.xlInsideHorizontal].Weight = 4;
worksheet.Range("C1:D" + (Freqs.Length + 1)).Borders[XlBordersIndex.xlInsideVertical].Color = ToDouble(Color.Black);
worksheet.Range("C1:D" + (Freqs.Length + 1)).Borders[XlBordersIndex.xlInsideHorizontal].Color = ToDouble(Color.Black);
int startrow = 2;
int i;
for (i = 0; i < Freqs.Length; i++)
{
worksheet.Range("A" + (i + startrow)).Value = "f" + (i + 1);
worksheet.Range("B" + (i + startrow)).Value = Freqs[i];
worksheet.Range("C" + (i + startrow)).Value = Math.Round(OrderPoints[i].X, 2);
worksheet.Range("D" + (i + startrow)).Value = Math.Round(OrderPoints[i].Y, 2);
worksheet.Range("F" + (i + startrow)).Value = Math.Round(OrderDetectorPoints[i].X, 2);
worksheet.Range("G" + (i + startrow)).Value = Math.Round(OrderDetectorPoints[i].Y, 2);
}
startrow = i + 3;
worksheet.Range("A" + (startrow) + ":C" + (Freqs.Length + startrow)).Borders.LineStyle = XlLineStyle.xlContinuous;
worksheet.Range("A" + (startrow) + ":C" + (Freqs.Length + startrow)).Borders.Weight = 2d;
worksheet.Range("B" + (startrow)).Value = "Ltot";
worksheet.Range("C" + (startrow)).Value = "L";
startrow++;
for (i = 0; i < Points.Length; i++)
{
worksheet.Range("A" + (i + startrow)).Value = (i + 1);
worksheet.Range("B" + (i + startrow)).Value = Math.Round(Points[i].X, 2);
worksheet.Range("C" + (i + startrow)).Value = Math.Round(Points[i].Y, 2);
}
startrow += i + 2;
worksheet.Range("A2:A" + startrow).HorizontalAlignment = XlHAlign.xlHAlignLeft;
worksheet.Range("A" + (startrow) + ":C" + (2 + startrow)).Borders.LineStyle = XlLineStyle.xlContinuous;
worksheet.Range("A" + (startrow) + ":C" + (2 + startrow)).Borders.Weight = 2d;
worksheet.Range("A" + startrow + ":C" + startrow).Merge();
worksheet.Range("A" + startrow + ":C" + startrow).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
worksheet.Range("A" + startrow + ":C" + startrow).Value = PolinomFunction1;
startrow++;
worksheet.Range("A" + startrow).Value = "a1";
worksheet.Range("B" + startrow).Value = "b1";
worksheet.Range("C" + startrow).Value = "c1";
startrow++;
worksheet.Range("A" + startrow).Value = A1;
worksheet.Range("B" + startrow).Value = B1;
worksheet.Range("C" + startrow).Value = C1;
startrow++;
startrow++;
worksheet.Range("A" + (startrow) + ":C" + (2 + startrow)).Borders.LineStyle = XlLineStyle.xlContinuous;
worksheet.Range("A" + (startrow) + ":C" + (2 + startrow)).Borders.Weight = 2d;
worksheet.Range("A" + startrow + ":C" + startrow).Merge();
worksheet.Range("A" + startrow + ":C" + startrow).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
worksheet.Range("A" + startrow + ":C" + startrow).Value = PolinomFunction2;
startrow++;
worksheet.Range("A" + startrow).Value = "a2";
worksheet.Range("B" + startrow).Value = "b2";
worksheet.Range("C" + startrow).Value = "c2";
startrow++;
worksheet.Range("A" + startrow).Value = A2;
worksheet.Range("B" + startrow).Value = B2;
worksheet.Range("C" + startrow).Value = C2;
startrow++;
startrow++;
worksheet.Range("A" + (startrow) + ":B" + (2 + startrow)).Borders.LineStyle = XlLineStyle.xlContinuous;
worksheet.Range("A" + (startrow) + ":B" + (2 + startrow)).Borders.Weight = 2d;
worksheet.Range("A" + startrow + ":B" + startrow).Merge();
worksheet.Range("A" + startrow + ":B" + startrow).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
worksheet.Range("A" + startrow + ":B" + startrow).Value = LineFunction;
startrow++;
worksheet.Range("A" + startrow).Value = "k";
worksheet.Range("B" + startrow).Value = "l";
startrow++;
worksheet.Range("A" + startrow).Value = K;
worksheet.Range("B" + startrow).Value = L;
ChartObject chart = ((ChartObjects)worksheet.ChartObjects()).Add(300, 0, 770, 500);
chart.Chart.ChartType = XlChartType.xlXYScatterSmoothNoMarkers;
chart.Chart.HasLegend = true;
chart.Chart.Legend.Position = XlLegendPosition.xlLegendPositionRight;
chart.Chart.Legend.Left = 680;
chart.Chart.Legend.Top = 10;
chart.Chart.Legend.Format.Fill.ForeColor.RGB = ColorTranslator.ToOle(Color.LightGray);
chart.Chart.Legend.Border.Color = ColorTranslator.ToOle(Color.DarkGray);
chart.Chart.HasTitle = true;
chart.Chart.ChartTitle.Text = "Loop " + channekNumber;
Axis axisy = (Axis)chart.Chart.Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary);
axisy.HasTitle = true;
axisy.AxisTitle.Text = "L Loop";
Axis axisx = (Axis)chart.Chart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
axisx.HasTitle = true;
axisx.AxisTitle.Text = "L Total";
SeriesCollection seriesCollection = (SeriesCollection)chart.Chart.SeriesCollection();
#region Calibration Point
using (Series oSeries = seriesCollection.NewSeries())
{
oSeries.XValues = worksheet.get_Range("C2", "C9");
oSeries.Values = worksheet.get_Range("D2", "D9");
oSeries.ChartType = XlChartType.xlXYScatter;
oSeries.ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowLabel);
oSeries.HasDataLabels = true;
DataLabels labels = (DataLabels)oSeries.DataLabels();
labels.Separator = " , ";
labels.ShowCategoryName = true;
labels.ShowValue = true;
oSeries.MarkerForegroundColor = ColorTranslator.ToOle(GetColor(CalibrateModel.Table1Color));
oSeries.MarkerBackgroundColor = ColorTranslator.ToOle(GetColor(CalibrateModel.Table1Color));
oSeries.MarkerStyle = XlMarkerStyle.xlMarkerStyleCircle;
oSeries.MarkerSize = 10;
oSeries.Name = "Calibration";
}
#endregion
#region Measurements Point
using (Series oSeries = seriesCollection.NewSeries())
{
oSeries.XValues = worksheet.get_Range("F2", "F9");
oSeries.Values = worksheet.get_Range("G2", "G9");
oSeries.ChartType = XlChartType.xlXYScatter;
oSeries.ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowNone);
oSeries.MarkerForegroundColor = ColorTranslator.ToOle(GetColor(CalibrateModel.Table2Color));
oSeries.MarkerBackgroundColor = ColorTranslator.ToOle(GetColor(CalibrateModel.Table2Color));
oSeries.MarkerStyle = XlMarkerStyle.xlMarkerStyleDiamond;
oSeries.MarkerSize = 10;
oSeries.Name = "Measurements";
}
#endregion
#region Line
using (Series oSeries = seriesCollection.NewSeries())
{
oSeries.XValues = worksheet.get_Range("C2", "C3");
oSeries.Values = worksheet.get_Range("D2", "D3");
oSeries.ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowNone);
oSeries.MarkerStyle = XlMarkerStyle.xlMarkerStyleNone;
oSeries.ChartType = XlChartType.xlXYScatterLinesNoMarkers;
oSeries.Format.Line.ForeColor.RGB = ColorTranslator.ToOle(GetColor(CalibrateModel.F3Color));
oSeries.Format.Line.BackColor.RGB = ColorTranslator.ToOle(GetColor(CalibrateModel.F3Color));
oSeries.Name = LineFunctionNumber.ToString();
}
#endregion
#region F1
startrow = 100;
foreach (var item in Calc1000Ponts(A1, B1, C1, Gragh1))
{
worksheet.Range("A" + (startrow)).Value = Math.Round(item.X, 2);
worksheet.Range("B" + (startrow)).Value = Math.Round(item.Y, 2);
startrow++;
}
using (Series oSeries = seriesCollection.NewSeries())
{
oSeries.XValues = worksheet.get_Range("A100", "A" + startrow);
oSeries.Values = worksheet.get_Range("B100", "B" + startrow);
oSeries.ChartType = XlChartType.xlXYScatterSmoothNoMarkers;
oSeries.ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowNone);
oSeries.MarkerStyle = XlMarkerStyle.xlMarkerStyleNone;
oSeries.Format.Line.ForeColor.RGB = ColorTranslator.ToOle(GetColor(CalibrateModel.F1Color));
oSeries.Format.Line.BackColor.RGB = ColorTranslator.ToOle(GetColor(CalibrateModel.F1Color));
oSeries.Name = PolinomFunction1Number.ToString();
}
#endregion
#region F1
startrow = 100;
foreach (var item in Calc1000Ponts(A2, B2, C2, Gragh2))
{
worksheet.Range("C" + (startrow)).Value = Math.Round(item.X, 2);
worksheet.Range("D" + (startrow)).Value = Math.Round(item.Y, 2);
startrow++;
}
using (Series oSeries = seriesCollection.NewSeries())
{
oSeries.XValues = worksheet.get_Range("C100", "C" + startrow);
oSeries.Values = worksheet.get_Range("D100", "D" + startrow);
oSeries.ChartType = XlChartType.xlXYScatterSmoothNoMarkers;
oSeries.ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowNone);
oSeries.MarkerStyle = XlMarkerStyle.xlMarkerStyleNone;
oSeries.Format.Line.ForeColor.RGB = ColorTranslator.ToOle(GetColor(CalibrateModel.F2Color));
oSeries.Format.Line.BackColor.RGB = ColorTranslator.ToOle(GetColor(CalibrateModel.F2Color));
oSeries.Name = PolinomFunction2Number.ToString();
}
#endregion
if (!System.IO.Directory.Exists(CalibrateModel.filespath))
System.IO.Directory.CreateDirectory(CalibrateModel.filespath);
try
{
chart.Chart.Export(CalibrateModel.filespath + @"ChartLoop" + channekNumber + ".jpeg", "JPEG");
}
catch (Exception xe)
{
Console.WriteLine(xe.Message);
}
}
catch
{
releaseObject(worksheet);
}
}