public void RunExample() { // start excel and turn off msg boxes Excel.Application excelApplication = new Excel.Application(); excelApplication.DisplayAlerts = false; // add a new workbook Excel.Workbook workBook = excelApplication.Workbooks.Add(); Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1]; // we need some data to display Excel.Range dataRange = PutSampleData(workSheet); // create a nice diagram Excel.ChartObject chart = ((Excel.ChartObjects)workSheet.ChartObjects()).Add(70, 100, 375, 225); chart.Chart.SetSourceData(dataRange); // save the book string fileExtension = GetDefaultExtension(excelApplication); string workbookFile = string.Format("{0}\\Example05{1}", _hostApplication.RootDirectory, fileExtension); workBook.SaveAs(workbookFile); // close excel and dispose reference excelApplication.Quit(); excelApplication.Dispose(); // show dialog for the user(you!) _hostApplication.ShowFinishDialog(null, workbookFile); }
public void RunExample() { // start excel and turn off msg boxes Excel.Application excelApplication = new Excel.Application(); excelApplication.DisplayAlerts = false; // create a utils instance, not need for but helpful to keep the lines of code low CommonUtils utils = new CommonUtils(excelApplication); // add a new workbook Excel.Workbook workBook = excelApplication.Workbooks.Add(); Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1]; // we need some data to display Excel.Range dataRange = PutSampleData(workSheet); // create a nice diagram Excel.ChartObject chart = ((Excel.ChartObjects)workSheet.ChartObjects()).Add(70, 100, 375, 225); chart.Chart.SetSourceData(dataRange); // save the book string workbookFile = utils.File.Combine(HostApplication.RootDirectory, "Example05", Excel.Tools.DocumentFormat.Normal); workBook.SaveAs(workbookFile); // close excel and dispose reference excelApplication.Quit(); excelApplication.Dispose(); // show dialog for the user(you!) HostApplication.ShowFinishDialog(null, workbookFile); }
public static void Main() { // start excel and turn off msg boxes NetOffice.ExcelApi.Application excelApplication = new NetOffice.ExcelApi.Application(); excelApplication.DisplayAlerts = false; // add a new workbook NetOffice.ExcelApi.Workbook workBook = excelApplication.Workbooks.Add(); NetOffice.ExcelApi.Worksheet workSheet = (NetOffice.ExcelApi.Worksheet)workBook.Worksheets[1]; // draw back color and perform the BorderAround method workSheet.Range("$B2:$B5").Interior.Color = ToDouble(Color.DarkGreen); workSheet.Range("$B2:$B5").BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic); // draw back color and border the range explicitly workSheet.Range("$D2:$D5").Interior.Color = ToDouble(Color.DarkGreen); workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlDouble; workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].Weight = 4; workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].Color = ToDouble(Color.Black); workSheet.Cells[1, 1].Value = "We have 2 simple shapes created."; // we need some data to display NetOffice.ExcelApi.Range dataRange = PutSampleData(workSheet); // create a nice diagram NetOffice.ExcelApi.ChartObject chart = ((NetOffice.ExcelApi.ChartObjects)workSheet.ChartObjects()).Add(70, 100, 375, 225); chart.Chart.SetSourceData(dataRange); // save the book string fileExtension = GetDefaultExtension(excelApplication); string workbookFile = Path.Combine(Directory.GetCurrentDirectory(), string.Format("Example01{0}", fileExtension)); workBook.SaveAs(workbookFile); // close excel and dispose reference excelApplication.Quit(); excelApplication.Dispose(); }
public void RunExample() { try { File.Delete(fileName); } catch (Exception ex) { } try { Excel.Application excelApplication = new Excel.Application(); excelApplication.DisplayAlerts = false; // add a new workbook Excel.Workbook workBook = excelApplication.Workbooks.Add(); Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1]; // we need some data to display Excel.Range dataRange = PutSampleData(workSheet); // create a nice diagram Excel.ChartObject chart = ((Excel.ChartObjects)workSheet.ChartObjects()).Add(70, 100, 375, 225); chart.Chart.SetSourceData(dataRange); // save the book workBook.SaveAs(fileName); // close excel and dispose reference excelApplication.Quit(); excelApplication.Dispose(); // show dialog for the user(you!) //_hostApplication.ShowFinishDialog(null, fileName); } catch (Exception ex) { //MessageBox.Show(ex.Message); //TODO Log Error Helper MessageBox.Show("The Excel file could not be created. Please check that you have Microsoft Excel 2003 or Higher Installed", "IQTools", MessageBoxButtons.OK, MessageBoxIcon.Warning); } }
public TestResult DoTest() { Excel.Application application = null; DateTime startTime = DateTime.Now; try { // start excel and turn off 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]; // we need some data to display Excel.Range dataRange = PutSampleData(workSheet); // create a nice diagram Excel.ChartObject chart = ((Excel.ChartObjects)workSheet.ChartObjects()).Add(70, 100, 375, 225); chart.Chart.SetSourceData(dataRange); 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); } }