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); } }