public void CopyChartsToClipboard()
{
Clipboard.SetText("xltoolbox test", TextDataFormat.Text);
ChartObjects cos = ws.ChartObjects();
ChartObject co1 = cos.Add(10, 10, 150, 100);
ChartObject co2 = cos.Add(150, 150, 100, 60);
co1.Select(true);
co2.Select(false);
svm.CopyToClipboard();
Assert.IsTrue(Clipboard.ContainsData("EnhancedMetaFile"));
Clipboard.Clear();
}
public void TwoChartsSelection()
{
ChartObjects cos = ws.ChartObjects();
ChartObject co1 = cos.Add(10, 10, 150, 100);
ChartObject co2 = cos.Add(150, 150, 100, 60);
Rect boundingRect = new Rect(10, 10, 240, 200);
co1.Select(true);
co2.Select(false);
Rect r = svm.Bounds;
Console.WriteLine(r);
Assert.AreEqual(boundingRect, r,
"Incorrect bounding rectangle for multiple selected charts.");
}
private void button16_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application eApp = new Microsoft.Office.Interop.Excel.Application();
eApp.Visible = true;
Microsoft.Office.Interop.Excel.Workbook workBook = eApp.Workbooks.Open(@"e:/2.xlsx");
Worksheet sheet = workBook.ActiveSheet;
PivotTable pt = sheet.PivotTables()[1];
Microsoft.Office.Interop.Excel.Range range = pt.TableRange2;
range.CopyPicture(XlPictureAppearance.xlScreen, XlCopyPictureFormat.xlPicture);
Worksheet newSheet = workBook.Sheets.Add();
//newSheet.Paste();
//Microsoft.Office.Interop.Excel.Shape s = newSheet.Shapes.Item(1);
//s.Chart.Export(@"e:\2.png", "PNG");
ChartObjects objs = newSheet.ChartObjects();
ChartObject chartobj = objs.Add(0, 0, range.Width, range.Height);
//if (chartobj.Chart != null)
chartobj.ProtectChartObject = false;
chartobj.Chart.Paste();
chartobj.Chart.Export(@"e:\1.png", "PNG");
chartobj.Delete();
eApp.Quit();
}
public void Draw(double[] nums, string fname)
{
try
{
Application application = new Application();
application.Visible = true;
Workbook workbook = application.Workbooks.Add(1);
Worksheet sheet = (Worksheet)workbook.ActiveSheet;
sheet.Name = fname;
int i = 0;
for (; i < 10; i++)
{
sheet.Cells[i + 1, 1] = i;
double f = nums[i];
sheet.Cells[i + 1, 2] = f;
}
ChartObjects xlCharts = (ChartObjects)sheet.ChartObjects();
ChartObject myChart = (ChartObject)xlCharts.Add(110, 0, 350, 250);
Chart chart = myChart.Chart;
SeriesCollection seriesCollection = (SeriesCollection)chart.SeriesCollection();
Series series = seriesCollection.NewSeries();
string A = "A" + Convert.ToString(i + 1);
string B = "B" + Convert.ToString(i + 1);
series.XValues = sheet.get_Range("A1", A);
series.Values = sheet.get_Range("B1", B);
chart.ChartType = XlChartType.xlXYScatterSmooth;
}
catch (Exception e)
{
Console.OutputEncoding = System.Text.Encoding.UTF8;
Console.Write(e.Message);
}
}
public void CreateDiagram(string fileName, List <EmployeeViewModel> students)
{
Application excel_report = new Application {
SheetsInNewWorkbook = 1
};
// добавить книгу
Workbook workBook = excel_report.Workbooks.Add(Type.Missing);
// получаем первый лист документа
Worksheet worksheet = (Worksheet)excel_report.Worksheets.get_Item(1);
ChartObjects chartObjs = (ChartObjects)worksheet.ChartObjects();
var groups = students.GroupBy(x => x.Subdivision);
int row = 0;
foreach (var group in groups)
{
row++;
// записываем отдел в 1 столбце
worksheet.Cells[row, 1] = group.Key.ToString();
// записываем количество сотрудников
worksheet.Cells[row, 2] = group.Count();
}
ChartObjects xlCharts = (ChartObjects)worksheet.ChartObjects(Type.Missing);
ChartObject myChart = xlCharts.Add(110, 0, 350, 250);
Chart chart = myChart.Chart;
SeriesCollection seriesCollection = (SeriesCollection)chart.SeriesCollection(Type.Missing);
Series series = seriesCollection.NewSeries();
series.XValues = worksheet.get_Range("A1", "A" + row);
series.Values = worksheet.get_Range("B1", "B" + row);
chart.ChartType = XlChartType.xlPie;
excel_report.Application.ActiveWorkbook.SaveAs(fileName, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
protected override void GenerateDataGraph()
{
string yAxisName = string.Format("{0}", unit);
string xAxisName = "Data Points";
ChartObjects chartObjects = this.dataGraph.ChartObjects(Type.Missing);
ChartObject chartObject = chartObjects.Add(0, 0, 850, 500);
Chart chart = chartObject.Chart;
Range dataRange = null;
string dataStartColumnIndex = "B";
if (_officeVersion > 11.0f)
{
yAxisName = string.Format("Temperature({0})", unit);
xAxisName = "Date Time";
dataStartColumnIndex = "A";
int xAxisPointCount = 10;
int tickSpacing = this.device.tempList.Count / xAxisPointCount;
if (tickSpacing == 0)
{
tickSpacing = 1;
}
if (tickSpacing == 1 && this.device.tempList.Count != xAxisPointCount)
{
tickSpacing = 2;
}
try
{
chart.PlotArea.Width = chart.PlotArea.Width - 10;
chart.PlotArea.Height = chart.PlotArea.Height - 30;
chart.PlotArea.Top = 30;
Microsoft.Office.Interop.Excel.Axis xAxis = (Microsoft.Office.Interop.Excel.Axis)chart.Axes(Microsoft.Office.Interop.Excel.XlAxisType.xlCategory, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary);
xAxis.TickMarkSpacing = tickSpacing;
xAxis.TickLabelSpacing = tickSpacing;
xAxis.TickLabelPosition = Microsoft.Office.Interop.Excel.XlTickLabelPosition.xlTickLabelPositionLow;
xAxis.MajorTickMark = Microsoft.Office.Interop.Excel.XlTickMark.xlTickMarkNone;
}
catch (Exception)
{
}
}
if (_alarmLimits.Count > 0)
{
dataRange = this.dataList.get_Range(dataStartColumnIndex + "3", string.Format("{0}{1}", ConvertAlarmLimitsCountToColumnIndex(_alarmLimits.Count), this.device.tempList.Count + 2));
}
else
{
dataRange = this.dataList.get_Range(dataStartColumnIndex + "3", string.Format("B{0}", this.device.tempList.Count + 2));
}
chart.ChartWizard(dataRange, Microsoft.Office.Interop.Excel.XlChartType.xlLine, Type.Missing, Type.Missing, Type.Missing, Type.Missing, false, "Data Graph", xAxisName, yAxisName, Type.Missing);
for (int i = 0; i < _alarmLimits.Count + 1; i++)
{
Series series = (Series)chart.SeriesCollection(i + 1);
series.MarkerStyle = Microsoft.Office.Interop.Excel.XlMarkerStyle.xlMarkerStyleNone;
series.Smooth = true;
if (i > 0)
{
series.Border.Color = Color.Red;
}
}
}
private void CreateChart(double startPositionLeft, double startPositionTop)
{
try
{
ChartObjects charts = sheet.ChartObjects();
//In case we create both temperature and humdity graphs in one sheet we need to separate them. Otherwise they will be on top of eachother
/*if (!temperature)
* {
* startPositionLeft += 100;
* startPositionTop += 50;
* }//*/
string chartTitle = sheet.Name + (temperature ? "_T" : "_H");
Range DateRange = usedRange.Range[topDateCell, bottomDateCell];
Range ValueRange = usedRange.Range[topValueCell, bottomValueCell];
ChartObject chart = charts.Add(startPositionLeft, startPositionTop, chartWidth, chartHeigth);
Chart xlChartPage = chart.Chart;
Series xlChartSeries = xlChartPage.SeriesCollection().Add(ValueRange);
xlChartSeries.XValues = DateRange;
xlChartPage.ChartType = XlChartType.xlLine;
xlChartPage.HasTitle = true;
xlChartPage.ChartTitle.Text = chartTitle;
xlChartPage.Legend.Delete();
DateRange = null;
ValueRange = null;
Marshal.ReleaseComObject(chart);
chart = null;
}
catch (Exception)
{
}
}
public void CreatePieChart(string workbook, int sheetIndex, string chartData, string chartLocation, string chartTitle)
{
Worksheet operationalWorksheet = (Worksheet)workbookIndex[workbook].Sheets[sheetIndex];
//Range chartWidthAndHeight = ParseRange(operationalWorksheet, chartLocation);
//double width = double.Parse(chartWidthAndHeight.Cells.ColumnWidth.ToString());
//double height = double.Parse(chartWidthAndHeight.Cells.RowHeight.ToString());
// Column A width, plus half again column A
//Range rngA = ParseRange(operationalWorksheet, "A");
//double aPlusHalfA = double.Parse(rngA.Cells.ColumnWidth.ToString());
//// The top of the chart will start below the bottom of the data?
////Range rngDataBottom = GetHeaderPlusSubHeaderHeight();
ChartObjects chartObjs = (ChartObjects)operationalWorksheet.ChartObjects(Type.Missing);
ChartObject chartObj = chartObjs.Add(50, 150, 300, 300);
Chart xlChart = chartObj.Chart;
xlChart.Location(XlChartLocation.xlLocationAutomatic, operationalWorksheet.Name);
Range sourceData = ParseRange(operationalWorksheet, chartData);
xlChart.ChartType = XlChartType.xl3DPie;
xlChart.SetSourceData(sourceData, XlRowCol.xlRows);
xlChart.HasTitle = true;
xlChart.ChartTitle.Text = chartTitle;
xlChart.HasLegend = true;
xlChart.Legend.Position = XlLegendPosition.xlLegendPositionBottom;
xlChart.Legend.Font.Size = 14;
Series oSeries = (Series)xlChart.SeriesCollection(1);
}
public void ExportChartObject(FileType fileType, int dpi, ColorSpace colorSpace)
{
// ExcelInstance.Application.Visible = true;
Workbook wb = Instance.Default.CreateWorkbook();
Worksheet ws = wb.Worksheets[1];
ws.Cells[1, 1] = 1;
ws.Cells[2, 1] = 2;
ws.Cells[3, 1] = 3;
ChartObjects cos = ws.ChartObjects();
ChartObject co = cos.Add(20, 20, 300, 200);
SeriesCollection sc = co.Chart.SeriesCollection();
sc.Add(ws.Range["A1:A3"]);
co.Chart.ChartArea.Select();
Preset preset = PresetsRepository.Default.Add(fileType, dpi, colorSpace);
SingleExportSettings settings = SingleExportSettings.CreateForSelection(preset);
settings.FileName = Path.Combine(
Path.GetTempPath(),
Path.GetTempFileName() + fileType.ToFileNameExtension()
);
settings.Unit = Unit.Millimeter;
settings.Width = 160;
settings.Height = 40;
File.Delete(settings.FileName);
Exporter exporter = new Exporter(settings);
exporter.Execute();
Assert.IsTrue(File.Exists(settings.FileName));
}
private void PlotSParameter(Workbook xlWorkBook, int points)
{
Range oRng;
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
ChartObject myChart = (Excel.ChartObject)xlCharts.Add(700, 50, 300, 300);
Chart chart = myChart.Chart;
oRng = xlWorkSheet.Range["A1:A" + points + ",B1:B" + points + ""];
chart.ChartType = XlChartType.xlXYScatterSmoothNoMarkers;
chart.ChartStyle = 7;
chart.ChartWizard(Source: oRng, Title: stimform.SParameter, HasLegend: true);
chart.ChartType = XlChartType.xlLine;
Axis axisS21 = (Axis)chart.Axes(XlAxisType.xlValue);
axisS21.TickLabels.NumberFormat = "#,##0.00";
axisS21.MaximumScale = 20;
axisS21.MinimumScale = -140;
axisS21.HasMajorGridlines = false;
Axis axisCatS21 = (Axis)chart.Axes(XlAxisType.xlCategory);
axisCatS21.TickLabels.NumberFormat = "0.00E+00";
axisCatS21.TickLabelPosition = XlTickLabelPosition.xlTickLabelPositionHigh;
}
static void FormExcelChart()
{
Application excel;
Workbook worKbooK;
Worksheet worKsheeT;
try
{
excel = new Application();
excel.Visible = false;
excel.DisplayAlerts = false;
worKbooK = excel.Workbooks.Add(Type.Missing);
worKsheeT = (Worksheet)worKbooK.ActiveSheet;
int i = 1;
foreach (var str in lstData)
{
worKsheeT.Cells[i, 1] = str.Key;
worKsheeT.Cells[i++, 2] = str.Value;
}
Range chartRange;
ChartObjects xlCharts = (ChartObjects)
worKsheeT.ChartObjects(Type.Missing);
ChartObject myChart = (ChartObject)
xlCharts.Add(110, 15, 468, 315);
Chart chartPage = myChart.Chart;
chartRange = worKsheeT.get_Range("B1", "B360");
chartPage.SetSourceData(chartRange, Type.Missing);
chartPage.ChartType = XlChartType.xlLine;
Series ser = (Series)chartPage.SeriesCollection(1);
ser.Values = worKsheeT.Range[worKsheeT.Cells[1, 2], worKsheeT.Cells[360, 2]];
ser.XValues = worKsheeT.Range[worKsheeT.Cells[1, 1], worKsheeT.Cells[360, 1]];
chartPage.HasLegend = false;
Axis vertAxis = (Axis)chartPage.Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary);
vertAxis.HasMajorGridlines = true;
vertAxis.MaximumScaleIsAuto = false;
vertAxis.MaximumScale = 80;
vertAxis.MinimumScaleIsAuto = false;
vertAxis.MinimumScale = -80;
vertAxis.MajorUnit = 20;
vertAxis.MinorUnit = 4;
string path = ConfigurationManager.AppSettings["Path"];
chartPage.Export(path + "TanChart.bmp", "BMP", Type.Missing);
worKbooK.SaveAs(path + "TanChart.xlsx");
excel.Quit();
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
}
public void CountSelectChartObjects()
{
Workbook wb = Instance.Default.CreateWorkbook();
Worksheet ws = wb.Worksheets.Add();
ChartObjects cos = ws.ChartObjects();
cos.Add(10, 10, 200, 100);
cos.Add(250, 10, 200, 100);
SheetViewModel svm = new SheetViewModel(ws);
Assert.AreEqual(2, svm.CountCharts(), "CountCharts()");
Assert.AreEqual(2, svm.CountShapes(), "CountGraphicObjects()");
Assert.IsTrue(svm.SelectCharts(),
"SelectCharts() should return true if the sheet contains an embedded chart.");
Assert.IsTrue(svm.SelectShapes(),
"SelectGraphicObjects() should return true if the sheet contains an embedded chart.");
}
public void WriteFullReport(IEnumerable <MeasurementsYear> measurements, string path)
{
Application xlApp;
Workbook xlWorkBook;
dynamic xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Worksheet)xlWorkBook.Worksheets.get_Item(1);
var measurementsList = measurements.ToList();
for (int i = 0; i < measurements.Count(); i++)
{
xlWorkSheet.Cells[1, i + 2] = measurementsList[i].Year;
}
xlWorkSheet.Cells[2, 1] = "schaner";
xlWorkSheet.Cells[3, 1] = "fevrer";
xlWorkSheet.Cells[4, 1] = "mart";
xlWorkSheet.Cells[5, 1] = "avrel";
xlWorkSheet.Cells[6, 1] = "matg";
xlWorkSheet.Cells[7, 1] = "zercladur";
xlWorkSheet.Cells[8, 1] = "fenadur";
xlWorkSheet.Cells[9, 1] = "uost";
xlWorkSheet.Cells[10, 1] = "setember";
xlWorkSheet.Cells[11, 1] = "october";
xlWorkSheet.Cells[12, 1] = "november";
xlWorkSheet.Cells[13, 1] = "december";
Range chartRange;
var to = this.map[measurements.Count() + 1];
var toString = string.Format("{0}13", to);
var writeRange = xlWorkSheet.Range("B2", toString);
writeRange.Value2 = CreateValuesMatrixAsNumber(measurements.ToList());
ChartObjects xlCharts = (ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
ChartObject myChart = (ChartObject)xlCharts.Add(10, 80, 300, 250);
Chart chartPage = myChart.Chart;
chartRange = xlWorkSheet.Range("A1", toString);
chartPage.SetSourceData(chartRange, misValue);
ChartSettings.Configure(chartPage);
xlWorkBook.SaveAs(string.Format("{0}\\test.xlsx", path));
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
public void BuiltChart(List <Setting> list, string fileName)
{
if (File.Exists(fileName))
{
File.Delete(fileName);
}
var excel = new Microsoft.Office.Interop.Excel.Application();
try
{
excel.SheetsInNewWorkbook = 1;
excel.Workbooks.Add(Type.Missing);
excel.Workbooks[1].SaveAs(fileName, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
Sheets excelsheets = excel.Workbooks[1].Worksheets;
var excelworksheet = (Worksheet)excelsheets.get_Item(1);
excelworksheet.Cells.Clear();
excelworksheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
excelworksheet.PageSetup.CenterHorizontally = true;
excelworksheet.PageSetup.CenterVertically = true;
for (int i = 0; i < list.Count; i++)
{
excelworksheet.Cells[1, i + 1] = list[i].legend;
excelworksheet.Cells[2, i + 1] = list[i].value;
}
ChartObjects chartObjs = (ChartObjects)excelworksheet.ChartObjects();
ChartObject chartObj = chartObjs.Add(5, 50, 300, 300);
Chart xlChart = chartObj.Chart;
Range rng2 = excelworksheet.Range["A1", (Convert.ToChar(65 + list.Count - 1)).ToString() + "2"];
xlChart.ChartType = XlChartType.xlPie;
xlChart.SetSourceData(rng2);
Series series = (Series)xlChart.SeriesCollection(1);
xlChart.Legend.Delete();
series.HasDataLabels = true;
excel.Workbooks[1].Save();
excel.Workbooks.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw;
}
finally
{
excel.Quit();
}
}
private void GenerateByClient()
{
workSheet_range = xlWorkSheet.Range["A23:C23"];
//columns heading
// xlWorkSheet.Range["A60"].Value = "TOTAL CLIENT";
xlWorkSheet.Range["A23"].Value = "TOTAL CLIENT";
xlWorkSheet.Range["B23"].Value = "PASS";
xlWorkSheet.Range["C23"].Value = "FAIL";
//format headings
workSheet_range.Font.Bold = true;
workSheet_range.Interior.Color = ColorTranslator.FromHtml(HelperClass.excelSummHeaderBackgound);
workSheet_range.Font.Color = ColorTranslator.FromHtml(HelperClass.excelSummHeaderFontColor);
workSheet_range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
workSheet_range.WrapText = true;
clientCount = ExecutionSession.lstClient.Count();
int count = 0;
foreach (clientList ClientList in ExecutionSession.lstClient)
{
if (ExecutionSession.lstExecutedTestCases.Where(testcase => testcase.Status == OverAllResult.FAIL && testcase.clientUrl == ClientList.clientUrl).Count() > 0)
{
count++;
}
}
xlWorkSheet.Range["A24"].Value = clientCount;
xlWorkSheet.Range["B24"].Value = clientCount - count;
xlWorkSheet.Range["C24"].Value = count;
PrevRepData.Add((clientCount - count).ToString());
PrevRepData.Add(count.ToString());
PrevRepData.Add(now.ToString("d"));
workSheet_range = xlWorkSheet.Range["A24", "C24"];
workSheet_range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
workSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb();
workSheet_range.WrapText = true;
myCharts = xlWorkSheet.ChartObjects(Type.Missing);
myCharts1 = myCharts.Add(340, 320, 370, 210);
oChart = myCharts1.Chart;
chartRange = xlWorkSheet.Range["B23", "C24"];
oChart.SetSourceData(chartRange);
oChart.PlotBy = XlRowCol.xlRows;
oChart.ChartType = XlChartType.xl3DPie;
oChart.ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowPercent);
oChart.HasLegend = true;
oChart.HasTitle = true;
oChart.ChartTitle.Text = "Current client summary report";
string overAllPieChartPath = summaryChartsFolder + "\\ClientSummaryReport.jpg";
oChart.Export(overAllPieChartPath, "jpg", Missing.Value);
}
public static void SaveDataToExcel(string path, Variable[] variables)
{
Application xlApp;
Workbook wbook;
Worksheet wsheet;
xlApp = new Application();
wbook = xlApp.Workbooks.Add(Missing.Value);
wsheet = wbook.ActiveSheet;
ChartObjects xlCharts = wsheet.ChartObjects(Type.Missing);
ChartObject chart = xlCharts.Add(10, 80, 300, 250);
Chart chartPage = chart.Chart;
chartPage.ChartType = XlChartType.xlLine;
SeriesCollection collection = chartPage.SeriesCollection();
int col = 1;
for (int i = 0; i < variables.Length; i++)
{
if (variables[i].plotValues.Count == 0)
{
continue;
}
wsheet.Cells[1, col] = "Time";
wsheet.Cells[1, col + 1] = variables[i].name;
for (int j = 0; j < variables[i].plotValues.Count; j++)
{
wsheet.Cells[j + 2, col] = variables[i].plotValues[j].X;
wsheet.Cells[j + 2, col + 1] = variables[i].plotValues[j].Y;
}
Series s = collection.NewSeries();
s.Smooth = true;
s.Name = variables[i].name;
s.XValues = wsheet.Range[VecToCell(col, 2), VecToCell(col, variables[i].plotValues.Count + 1)];
s.Values = wsheet.Range[VecToCell(col + 1, 2), VecToCell(col + 1, variables[i].plotValues.Count + 1)];
int color = colorToRGB(variables[i].plotColor);
s.Format.Line.ForeColor.RGB = color;
col += 3;
}
wsheet.SaveAs(path);
wbook.Close(true, Missing.Value, Missing.Value);
xlApp.Quit();
Marshal.ReleaseComObject(wsheet);
Marshal.ReleaseComObject(wbook);
Marshal.ReleaseComObject(xlApp);
}
public async Task CeateAndSaveExcelFileProductCartAsync(string fileName, IDictionary <ItemKey, IEnumerable <Product> > cartProductsByItemKey)
{
await Task.Run(() =>
{
object misValue = Missing.Value;
var xlApp = new Application();
var xlWorkBook = xlApp.Workbooks.Add(misValue);
var xlWorkSheet = (Worksheet)xlWorkBook.Worksheets.Item[1];
xlWorkSheet.Cells[1, 1] = "";
var numberOfProducts = cartProductsByItemKey.Keys.Count;
foreach (var pair in cartProductsByItemKey)
{
var counter = pair.Value.Count();
xlWorkSheet.Cells[2 + numberOfProducts, 1] = pair.Value.First().ProductName;
foreach (var item in pair.Value)
{
var chainName = GetChainName(item.CaindId);
xlWorkSheet.Cells[1, 1 + counter] = chainName;
xlWorkSheet.Cells[2 + numberOfProducts, 1 + counter] = item.ItemPrice;
counter--;
}
numberOfProducts--;
}
numberOfProducts = cartProductsByItemKey.Keys.Count;
var numberOfChains = cartProductsByItemKey.Values.First().Count();
ChartObjects xlCharts =
(ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
ChartObject myChart =
xlCharts.Add(10, 80, 300, 250);
Chart chartPage = myChart.Chart;
var chartRange =
xlWorkSheet.Range[
(Range)xlWorkSheet.Cells[1, 1],
(Range)
xlWorkSheet.Cells[numberOfProducts + 2, numberOfChains + 1]];
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = XlChartType.xlColumnClustered;
xlWorkBook.SaveAs(fileName, XlFileFormat.xlWorkbookNormal, misValue,
misValue, misValue, misValue,
XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue,
misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
});
}
public void CountSelectShapesAndCharts()
{
Workbook wb = Instance.Default.CreateWorkbook();
Worksheet ws = wb.Worksheets.Add();
Shapes shs = ws.Shapes;
shs.AddLine(10, 10, 20, 30);
shs.AddLine(50, 50, 20, 30);
ChartObjects cos = ws.ChartObjects();
cos.Add(10, 10, 200, 100);
cos.Add(250, 10, 200, 100);
SheetViewModel svm = new SheetViewModel(ws);
Assert.AreEqual(2, svm.CountCharts());
Assert.AreEqual(4, svm.CountShapes());
Assert.IsTrue(svm.SelectCharts(),
"SelectCharts() should return true if the sheet contains charts and shapes.");
Assert.IsTrue(svm.SelectShapes(),
"SelectGraphicObjects() should return true if the sheet contains charts and shapes.");
}
private void GenerateOverAllStepsPieChart()
{
//format headings
workSheet_range = xlWorkSheet.Range["A3:D3"];
//columns heading
xlWorkSheet.Range["A3"].Value = "";
xlWorkSheet.Range["B3"].Value = "Pass Steps";
xlWorkSheet.Range["C3"].Value = "Fail Steps";
xlWorkSheet.Range["D3"].Value = "Warning Steps";
//format headings
workSheet_range.Font.Bold = true;
workSheet_range.Interior.Color = ColorTranslator.FromHtml(HelperClass.excelSummHeaderBackgound);
workSheet_range.Font.Color = ColorTranslator.FromHtml(HelperClass.excelSummHeaderFontColor);
workSheet_range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
workSheet_range.WrapText = true;
int noOfStepsPassed = (from testCase in ExecutionSession.lstTestCase
select testCase.NoOfStepsPassed)
.Sum();
int noOfStepsFailed = (from testCase in ExecutionSession.lstTestCase
select testCase.NoOfStepsFailed)
.Sum();
int noOfWarningSteps = (from testCase in ExecutionSession.lstTestCase
select testCase.NoOfWarningSteps)
.Sum();
xlWorkSheet.Range["B4"].Value = noOfStepsPassed;
xlWorkSheet.Range["C4"].Value = noOfStepsFailed;
xlWorkSheet.Range["D4"].Value = noOfWarningSteps;
workSheet_range = xlWorkSheet.Range["A3", "D4"];
workSheet_range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
workSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb();
workSheet_range.WrapText = true;
myCharts = xlWorkSheet.ChartObjects(Type.Missing);
myCharts1 = myCharts.Add(400, 30, 370, 210);
oChart = myCharts1.Chart;
chartRange = xlWorkSheet.Range["A3", "D4"];
oChart.SetSourceData(chartRange);
oChart.PlotBy = XlRowCol.xlRows;
oChart.ChartType = XlChartType.xl3DPie;
oChart.ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowPercent);
oChart.HasLegend = true;
oChart.HasTitle = true;
oChart.ChartTitle.Text = "Overall steps report";
string overAllPieChartPath = summaryChartsFolder + "\\OverallStepsSummaryReport.jpg";
oChart.Export(overAllPieChartPath, "jpg", Missing.Value);
}
public void SetChart(string start, string end, XlChartType type)
{
Range chartRange;
ChartObjects xlCharts = (ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
ChartObject myChart = (ChartObject)xlCharts.Add(500, 80, 350, 350);
Chart chartPage = myChart.Chart;
chartRange = xlWorkSheet.get_Range(start, end);
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = type;
}
public void GenerateDiagram(Range range)
{
Workbook.Worksheets.Add();
Worksheet = Workbook.Worksheets[Index : 2];
ChartObjects xlCharts = Worksheet.ChartObjects(Index: Type.Missing);
ChartObject myChart = xlCharts.Add(Left: 10, Top: 80, Width: 300, Height: 250);
Chart chartPage = myChart.Chart;
chartPage.ChartType = XlChartType.xl3DColumnClustered;
chartPage.SetSourceData(Source: range);
}
static void Main(string[] args)
{
// Open Excel
Application xl = new Application();
Workbooks books = xl.Workbooks;
// Get the location of the rates file as path is relative to Excel
string ratesFile = GetRatesFilePath();
// Open the rates file in Excel
Workbook book = books.Open(ratesFile);
dynamic sheet = book.Worksheets[1];
// Set the format of the first column to date
Range range = sheet.Range("A:A");
range.NumberFormat = "yyyy-mm-dd";
// Create a new chart
ChartObjects xlCharts = sheet.ChartObjects;
ChartObject myChart = xlCharts.Add(100, 80, 700, 400);
Chart chartPage = myChart.Chart;
// Set the data for the chart and chart type
Range chartRange = sheet.Range("A:A,B:B");
chartPage.SetSourceData(chartRange);
chartPage.ChartType = XlChartType.xlArea;
// Show Excel to the world
xl.Visible = true;
Console.WriteLine("Press enter to quit Excel ...");
Console.ReadLine();
// Tell Excel to shutdown
xl.Visible = false;
xl.Quit();
// Release all acquired COM resources to allow the
// Excel proces to terminate
Marshal.ReleaseComObject(chartRange);
Marshal.ReleaseComObject(chartPage);
Marshal.ReleaseComObject(myChart);
Marshal.ReleaseComObject(xlCharts);
Marshal.ReleaseComObject(range);
Marshal.ReleaseComObject(sheet);
Marshal.ReleaseComObject(book);
Marshal.ReleaseComObject(books);
Marshal.ReleaseComObject(xl);
}
private void CreateChart(int length, Worksheet excelWorkSheet)
{
ChartObjects xlCharts = (ChartObjects)excelWorkSheet.ChartObjects(Type.Missing);
ChartObject myChart = (ChartObject)xlCharts.Add(300, 0, length / 1.5, 350);
Microsoft.Office.Interop.Excel.Chart chart = myChart.Chart;
Microsoft.Office.Interop.Excel.SeriesCollection seriesCollection = (Microsoft.Office.Interop.Excel.SeriesCollection)chart.SeriesCollection(Type.Missing);
Microsoft.Office.Interop.Excel.Series series = seriesCollection.NewSeries();
series.XValues = excelWorkSheet.get_Range("B2", "B" + (length + 1));
series.Values = excelWorkSheet.get_Range("C2", "C" + (length + 1));
series.Name = "Fluctuation";
chart.ChartType = XlChartType.xlXYScatterSmooth;
}
/*
* http://stackoverflow.com/questions/11223641/how-do-i-create-a-new-worksheet-and-populate-it-with-rows-of-data-using-excel-dn
*/
private void MakeGraph()
{
Application app = (Application)ExcelDnaUtil.Application;
//var app = new Application();
app.Visible = true;
var workbook = app.ActiveWorkbook;
Sheets excelSheets = workbook.Worksheets;
string currentSheet = "Sheet1";
Worksheet worksheet1 = (Worksheet)excelSheets.get_Item(currentSheet);
worksheet1.Cells[1, 1] = "";
worksheet1.Cells[1, 2] = "Year 1";
worksheet1.Cells[1, 3] = "Year 2";
worksheet1.Cells[1, 4] = "Year 3";
worksheet1.Cells[1, 5] = "Year 4";
worksheet1.Cells[1, 6] = "Year 5";
worksheet1.Cells[2, 1] = "Company A";
worksheet1.Cells[2, 2] = "10";
worksheet1.Cells[2, 3] = "50";
worksheet1.Cells[2, 4] = "70";
worksheet1.Cells[2, 5] = "70";
worksheet1.Cells[2, 6] = "70";
worksheet1.Cells[3, 1] = "Company B";
worksheet1.Cells[3, 2] = "30";
worksheet1.Cells[3, 3] = "70";
worksheet1.Cells[3, 4] = "80";
worksheet1.Cells[3, 5] = "80";
worksheet1.Cells[3, 6] = "80";
worksheet1.Cells[4, 1] = "Company C";
worksheet1.Cells[4, 2] = "55";
worksheet1.Cells[4, 3] = "65";
worksheet1.Cells[4, 4] = "75";
worksheet1.Cells[4, 5] = "75";
worksheet1.Cells[4, 6] = "75";
ChartObjects xlCharts = (ChartObjects)worksheet1.ChartObjects(Type.Missing);
ChartObject myChart = (ChartObject)xlCharts.Add(60, 10, 300, 300);
Range chartRange = worksheet1.get_Range("A1", "F4");
Chart chartPage = myChart.Chart;
chartPage.SetSourceData(chartRange, System.Reflection.Missing.Value);
chartPage.ChartType = XlChartType.xlLine;
}
public void createChart(ChartData chartData)
{
Range chartRange;
ChartObjects xlCharts = (ChartObjects)ActiveSheet.ChartObjects(Type.Missing);
ChartObject myChart = (ChartObject)xlCharts.Add(chartData.left, chartData.top, chartData.width, chartData.height);
Chart chartPage = myChart.Chart;
chartPage.HasTitle = true;
chartPage.ChartTitle.Text = chartData.title;
chartRange = ActiveSheet.get_Range(chartData.cellDataFrom, chartData.cellDataTo);
chartRange.Font.Name = "Arial";
chartRange.Font.Size = 8;
chartPage.SetSourceData(chartRange, Type.Missing);
chartPage.ChartType = (XlChartType)(chartData.chartType);
chartPage.Legend.Font.Name = "Arial";
chartPage.Legend.Font.Size = 9;
if (chartData.showLabel)
{
if (chartData.showPercent)
{
chartPage.ApplyDataLabels(
XlDataLabelsType.xlDataLabelsShowValue,
false, false, false, false, false, false, true,
false, false);
}
else
{
chartPage.ApplyDataLabels(
XlDataLabelsType.xlDataLabelsShowValue,
false, false, false, false, false, true, false,
false, false);
}
}
if (!chartData.showLegend)
{
chartPage.HasLegend = false;
}
Axis Xaxis = (Axis)chartPage.Axes(XlAxisType.xlValue,
XlAxisGroup.xlPrimary);
try
{
Xaxis.MinimumScale = 0;
}
catch { }
}
private static void AddChart(_Worksheet excellWorkSheet, string startSel, string endSel)
{
ChartObjects xlCharts = (ChartObjects)excellWorkSheet.ChartObjects(Type.Missing);
ChartObject myChart = xlCharts.Add(10, 80, 600, 400);
Chart chartPage = myChart.Chart;
Range chartRange = excellWorkSheet.Range[startSel, endSel];
chartPage.SetSourceData(chartRange, Type.Missing);
chartPage.ChartType = XlChartType.xlXYScatter;
var seriesCollection = (SeriesCollection)chartPage.SeriesCollection();
seriesCollection.Item(1).Name = "PROPORTION";
}
/// <summary>
/// Plot line chart with date and adjusted close prices.
/// </summary>
/// <param name="sheet">Excel worksheet with pricing data.</param>
/// <param name="misValue">Object in case of mishandled value.</param>
/// <param name="rowCount">Number of rows filled with data.</param>
static void makeChart(Worksheet sheet, object misValue, int rowCount)
{
ChartObjects xlCharts = (ChartObjects)sheet.ChartObjects(Type.Missing);
ChartObject myChart = xlCharts.Add(600, 120, 305, 240);
Chart chartPage = myChart.Chart;
// Classify Cells
string firstPriceCell = "F2";
string lastPriceCell = "F" + rowCount;
string firstDateCell = "A2";
string lastDateCell = "A" + rowCount;
formatChart(sheet, chartPage, firstPriceCell, lastPriceCell, firstDateCell, lastDateCell, misValue, rowCount);
}
// Function that creates the plot
public static void plotChart(string columnTitle)
{
Range chartRange;
object misValue = System.Reflection.Missing.Value;
// Creates the chart
ChartObjects xlCharts = (ChartObjects)ExcelExecutions.xlWorksheet.ChartObjects(Type.Missing);
ChartObject myChart = xlCharts.Add(250, 50, 600, 350);
Chart xlChart = myChart.Chart;
xlChart.ChartType = XlChartType.xlXYScatter;
// Gets the range for the column title
Range XcolumnTitleLocation = findCell(ExcelExecutions.xTitle);
Range YcolumnTitleLocation = findCell(columnTitle);
// The title for the x- and y-axis
Axis xAxis = xlChart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary) as Axis;
Axis yAxis = xlChart.Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary) as Axis;
xAxis.HasTitle = true;
yAxis.HasTitle = true;
xAxis.AxisTitle.Text = XcolumnTitleLocation.Value;
yAxis.AxisTitle.Text = YcolumnTitleLocation.Value;
// The values for the chart
string xColumnPrefix = ExcelExecutions.columnNames[XcolumnTitleLocation.Column - 1];
string yColumnPrefix = ExcelExecutions.columnNames[YcolumnTitleLocation.Column - 1];
Range xValues = ExcelExecutions.xlWorksheet.Range[xColumnPrefix + "2", (xColumnPrefix + ExcelExecutions.xlRange.Rows.Count.ToString())];
Range yvalues = ExcelExecutions.xlWorksheet.Range[yColumnPrefix + "2", (yColumnPrefix + ExcelExecutions.xlRange.Rows.Count.ToString())];
// Assigns the value to the chart
SeriesCollection seriesCollection = xlChart.SeriesCollection();
Series pointPlot = seriesCollection.NewSeries();
pointPlot.XValues = xValues;
pointPlot.Name = columnTitle;
pointPlot.Values = yvalues;
// Creates trendline(regression)
Trendlines trendlines = (Trendlines)pointPlot.Trendlines(Type.Missing);
Trendline linearRegression = trendlines.Add(XlTrendlineType.xlLinear);
linearRegression.DisplayEquation = true;
linearRegression.DisplayRSquared = true;
saveChart(xlChart, pointPlot.Name);
}
private Chart InsertBubbleChart(Worksheet chartWorksheet, Worksheet reportWorksheet)
{
ChartObjects xlCharts = (ChartObjects)chartWorksheet.ChartObjects(Type.Missing);
ChartObject myChart = xlCharts.Add(5, 5, 600, 330);
Chart chartPage = myChart.Chart;
chartPage.ChartType = XlChartType.xlBubble;
chartPage.HasLegend = false;
Range chartRange = reportWorksheet.Range["ChartDataRange"];
chartPage.SetSourceData(chartRange);
return(chartPage);
}
public static void CreateSomeCharts(Worksheet worksheet, int number)
{
Random random = new Random(worksheet.GetHashCode());
ChartObjects cos = worksheet.ChartObjects();
ChartObject co;
SeriesCollection sc;
for (int n = 0; n < number; n++)
{
co = cos.Add(10 + random.Next(30), 10 + random.Next(40),
300 + random.Next(100), 200 + random.Next(50));
sc = co.Chart.SeriesCollection();
sc.Add(worksheet.Range["A1:A4"]);
}
}
