/// <summary>
/// Generates the bar chart for the Labor Reporting Chart.
/// </summary>
private void GenerateChart()
{
//const string fileName = "C:\\Book1.xlsx";
//string topLeft = "A1";
//string bottomRight = "A4";
string graphTitle = "Graph Title";
string xAxis = "Activity";
string yAxis = "Performance";
//Add chart
var charts = reportWorksheet.ChartObjects() as ChartObjects;
var chartObject = charts.Add(300, 10, 300, 300) as ChartObject;
var barChart = chartObject.Chart;
//Set chart range.
var barChartPhaseCodeRange = reportWorksheet.Range[reportWorksheet.Cells[headerRow + 1, 1], reportWorksheet.Cells[5, 1]];
var barChartPerformanceRange = reportWorksheet.Range[reportWorksheet.Cells[headerRow + 1, 10], reportWorksheet.Cells[5, 10]];
//var barChartPercentCompleteRange = reportWorksheet.Range[reportWorksheet.Cells[headerRow + 1, 8], reportWorksheet.Cells[5, 8]];
var barChartData = Excel.Union(barChartPhaseCodeRange, barChartPerformanceRange);
barChart.SetSourceData(barChartData);
//Set chart properties.
barChart.ChartType = XlChartType.xlBarClustered;
barChart.ChartWizard(Source: barChartData,
Title: graphTitle,
CategoryTitle: xAxis,
ValueTitle: yAxis);
}
public ReportBuilder()
{
app = new Excel.Application();
appBooks = app.Workbooks;
currentBook = appBooks.Add(Missing.Value);
sheets = currentBook.Worksheets;
currentSheet = (Excel._Worksheet)sheets.get_Item(1);
range = currentSheet.get_Range("A1", Missing.Value);
charts = currentSheet.ChartObjects(Type.Missing);
chartObject = charts.Add(400, LastIndex, chartWidth, chartHeight);
}
public ReportBuilder()
{
app = new Excel.Application();
appBooks = app.Workbooks;
currentBook = appBooks.Add(Missing.Value);
sheets = currentBook.Worksheets;
currentSheet = (Excel._Worksheet)sheets.get_Item(1);
range = currentSheet.get_Range("A1", Missing.Value);
charts = currentSheet.ChartObjects(Type.Missing);
chartObject = charts.Add(400, LastIndex, chartWidth, chartHeight);
}
//Создаем лист книги
static void DisplayInExcel(IEnumerable <Account> accounts)
{
//создаем объект приложения и делаем его видимым
var excelApp = new Excel.Application();
excelApp.Visible = true;
//создаем новую рабочую книгу - она автоматически становится активной
excelApp.Workbooks.Add();
//этот пример использует только одну таблицу
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
//вставляем значения в первые два столбца первой строки (заголовок)
workSheet.Cells[1, "A"] = "ID Number";
workSheet.Cells[1, "B"] = "Current Balance";
//помещаем данные о счетах в таблицу
var row = 1;
foreach (var acct in accounts)
{
row++;
workSheet.Cells[row, "A"] = acct.ID;
workSheet.Cells[row, "B"] = acct.Balance;
}
//Вычисляем сумму по всем счетам
Excel.Range rng = workSheet.Range["B4"]; // указываем ячейку где будет прописанна сумма
rng.Formula = "=СУММ(B2:B3)";
rng.FormulaHidden = false;
//форматирование таблицы - выделяем границы у ячейки суммы
Excel.Borders border = rng.Borders;
border.LineStyle = Excel.XlLineStyle.xlContinuous;
//чтобы ширина столбца изменилась в соответствии с содержимым
//workSheet.Columns.AutoFit();
((Excel.Range)workSheet.Columns[1]).AutoFit();
((Excel.Range)workSheet.Columns[2]).AutoFit();
//строим круговую диаграмму
Excel.ChartObjects chartObjs = (Excel.ChartObjects)workSheet.ChartObjects();
Excel.ChartObject chartObj = chartObjs.Add(50, 100, 300, 300);
Excel.Chart xlChart = chartObj.Chart;
Excel.Range rng2 = workSheet.Range["B1:B3"];
//Устанавливаем тип диаграммы
xlChart.ChartType = Excel.XlChartType.xlColumnClustered;
//устанавливаем источник даных
xlChart.SetSourceData(rng2);
}
public void write_chart(string chart_name, string podpis_x, string podpis_y, string diapason_nachalo, string diapason_end)
{
Excel.ChartObjects chartobj = (Excel.ChartObjects)m_objSheet.ChartObjects(Type.Missing);
Excel.ChartObject chart1 = chartobj.Add(chart_x, chart_y, chart_w, chart_h);
chart1.Chart.ChartWizard(m_objSheet.get_Range(diapason_nachalo, diapason_end), Excel.XlChartType.xlLine, 0, Excel.XlRowCol.xlColumns, 1, false, "", chart_name, podpis_x, podpis_y, "");
chart1.Chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlLine;
/* Excel.SeriesCollection seriesCollection =
* (Excel.SeriesCollection)m_objExcel.ActiveChart.SeriesCollection(Type.Missing);
* Excel.Series series = seriesCollection.Item(1);
* series.Name = "к-т отражения";
*
* series = seriesCollection.Item(2);
* series.Name = "к-т прохождения";
*
* series = seriesCollection.Item(3);
* series.Name = "ЭПР";*/
}
private void GerarGraficoExcel(String c1, String c2, int tamanho, double left, double top, double width, double height)
{
// Cria um Chart(Gráfico)
Ex.ChartObjects cb = (Ex.ChartObjects)oSheetChart.ChartObjects(Type.Missing);
Ex.ChartObject cbc = (Ex.ChartObject)cb.Add(left, top, width, height);
Ex.Chart cp = cbc.Chart;
Ex.Range valores = oSheet.get_Range(c1, c2);
// Seta o título do gráfico
cp.HasTitle = true;
cp.ChartTitle.Text = "MÉDIA DE TEMPO EM VETORES DE TAMANHO " + tamanho + " EM " + (tamanho <= 1000 ? "NANO" : "MILLI");
cp.ChartTitle.Font.Name = "Arial";
// Seta os nomes das colunas
Ex.SeriesCollection seriesCollection = cp.SeriesCollection();
Ex.Series series = seriesCollection.NewSeries();
series.Values = valores;
series.XValues = oSheet.get_Range("B1", "J1");
// Oculta a legenda da serie
cp.Legend.Clear();
}
public static void ExportToExcel(int ndata, DataSet var1, DataSet var2, DataSet var3)
{
// Create Excel application (it's like opening the program)
Excel.Application excelApp = new Excel.Application();
// Add a workbook (it's like creating a new empty file with one empty active worksheet)
excelApp.Workbooks.Add();
// Create a local worksheet and gets the reference of the active worksheet in Excel (excelApp)
Excel._Worksheet workSheet = (Excel._Worksheet)excelApp.ActiveSheet;
//----------------------------------------
// Filling the worksheet with data
//----------------------------------------
// Setting "table name"
Excel.Range headerRange = excelApp.get_Range("A1", "C1");
headerRange.Merge();
workSheet.Cells[1, "A"] = "<Case name>";
headerRange.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
headerRange.Font.Bold = true;
headerRange.Interior.Color = System.Drawing.Color.Yellow;
// Writting headers
workSheet.Cells[2, "A"] = "Time (days)";
workSheet.Cells[2, "B"] = "Oil rate (STB/d)";
workSheet.Cells[2, "C"] = "Gas rate (STB/d)";
// Writting data
int counter = 2;
for (int i = 0; i < ndata; i++)
{
counter++;
workSheet.Cells[counter, "A"] = var1.GetDataItem(i);
workSheet.Cells[counter, "B"] = var2.GetDataItem(i);
workSheet.Cells[counter, "C"] = var3.GetDataItem(i);
}
// Formating data range
string beg_range = "A3";
int endRow = ndata + 2;
string end_range = "C" + endRow;
Excel.Range dataRange = excelApp.get_Range(beg_range, end_range);
//dataRange.Cells.NumberFormat = "#######";
// Formating the data
//workSheet.Range["A2"].AutoFormat(Excel.XlRangeAutoFormat.xlRangeAutoFormatTable3);
//----------------------------------------
// Creating the chart
//----------------------------------------
// Creating the objects
var charts = workSheet.ChartObjects() as Excel.ChartObjects;
var chartObject = charts.Add(100, 100, 500, 200);
var oilChart = chartObject.Chart;
Excel.Chart gasChart = new Excel.Chart();
end_range = "A" + endRow;
Excel.Range timeRange = excelApp.get_Range("A3", end_range);
end_range = "B" + endRow;
Excel.Range oilRange = excelApp.get_Range("B3", end_range);
// Filling chart with data
oilChart.SetSourceData(oilRange);
//oilChart.
// Setting chart type
oilChart.ChartType = Excel.XlChartType.xlXYScatterSmoothNoMarkers;
// Another approach
//Excel.Series oilSeries = null;
//Excel.Shape _Shape = workSheet.Shapes.AddChart2();
//oilSeries.XValues = dataRange;
//_Shape.Chart.SeriesCollection. = oilSeries;
// Saving the file
workSheet.SaveAs($@"{Environment.CurrentDirectory}\outfile.xlsx");
excelApp.Quit();
}
/// <summary>
/// Initializes Excel vareables
/// </summary>
public static void ExcelPrep()
{
UsingExcel = true;
R1EPCsDic.Clear();
R2EPCsDic.Clear();
#region config Excel application and workbook
oXL = new Excel.Application
{
Visible = true,
WindowState = Excel.XlWindowState.xlMaximized
};
oWB = oXL.Workbooks.Add(Missing.Value);
#endregion
try
{
#region config sheets
oSheetDataRSSIs = (Excel._Worksheet)oWB.ActiveSheet;
oSheetDataRSSIs.Name = "Rssis Data";
oSheetDataRSSIs.get_Range("A1:Z1").Font.Bold = true;
oSheetDataTags = (Excel._Worksheet)oWB.Sheets.Add(Type.Missing, oSheetDataRSSIs, Type.Missing, Type.Missing);
oSheetDataTags.Name = "Tags Data";
oSheetDataTags.get_Range("A1:Z2").Font.Bold = true;
oSheetDataTags.get_Range("A1:Z2").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
oSheetCharts = (Excel._Worksheet)oWB.Sheets.Add(Type.Missing, oSheetDataTags, Type.Missing, Type.Missing);
oSheetCharts.Name = "Charts";
oSheetTagFlags = (Excel._Worksheet)oWB.Sheets.Add(Type.Missing, oSheetCharts, Type.Missing, Type.Missing);
oSheetTagFlags.Name = "Flags";
oSheetTagFlags.Cells[1, 1] = "A";
oSheetTagFlags.Cells[1, 2] = "B";
// Selects sheet if needed
//oSheetDataTags.Activate();
#endregion
#region config charts
Excel.ChartObjects xlCharts = oSheetCharts.ChartObjects(Type.Missing);
#region config RSSI chart
oRng = oSheetCharts.get_Range("B2:K36");
oChartRSSIs = xlCharts.Add(oRng.Left, oRng.Top, oRng.Width, oRng.Height);
oChartRSSIs.Chart.ChartWizard(
Gallery: Excel.XlChartType.xlLine,
Format: 4,
PlotBy: Excel.XlRowCol.xlColumns,
CategoryLabels: false,
SeriesLabels: 5,
HasLegend: true,
Title: "RSSIs",
CategoryTitle: "Values_Range",
ValueTitle: "RSSI"
);
oChartRSSIs.Chart.SetSourceData(oSheetCharts.get_Range("A1:A2"));
#region RSSIs chart axis
Excel.Axis axis = oChartRSSIs.Chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
axis.MinimumScaleIsAuto = false;
axis.MaximumScaleIsAuto = false;
axis.MajorUnit = 1;
axis.MinimumScale = 170;
axis.MaximumScale = 220;
#endregion
#endregion
#region config Tags chart
oRng = oSheetCharts.get_Range("M2:U36");
oChartTags = xlCharts.Add(oRng.Left, oRng.Top, oRng.Width, oRng.Height);
oChartTags.Chart.ChartWizard(
Gallery: Excel.XlChartType.xlXYScatter,
Format: 3,
CategoryLabels: true,
SeriesLabels: 5,
HasLegend: true,
Title: "Tags Location",
CategoryTitle: "X",
ValueTitle: "Y"
);
oChartTags.Chart.ChartType = Excel.XlChartType.xlXYScatter;
oChartTags.Chart.SetSourceData(oSheetCharts.get_Range("A1:A2"));
#region Tags chart axis
axis = oChartTags.Chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
axis.MinimumScaleIsAuto = false;
axis.MaximumScaleIsAuto = false;
axis.MajorUnit = 1;
axis.MinimumScale = 0;
axis.MaximumScale = 10;
axis = oChartTags.Chart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
axis.MinimumScaleIsAuto = false;
axis.MaximumScaleIsAuto = false;
axis.MajorUnit = 1;
axis.MinimumScale = -4;
axis.MaximumScale = 4;
#endregion
#endregion
#endregion
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
public void AddCharts(string reportFile)
{
var excel = new Excel.Application()
{
DisplayAlerts = false
};
excel.Workbooks.Open(reportFile);
Excel._Worksheet workSheet = (Excel.Worksheet)excel.ActiveSheet;
var sheetNumber = 1;
var chartPositionNumber = 2;
foreach (Excel.Worksheet sheet in excel.Worksheets)
{
if (sheet.Name != "Charts")
{
// Set the datasheet for the source of the data
Excel.Worksheet dataSheet = excel.Worksheets[sheetNumber];
// Open the chart sheet to save the charts
workSheet = excel.ActiveWorkbook.Sheets["Charts"];
workSheet.Select();
// Get column & row length
var colomn = dataSheet.UsedRange.Columns.Count;
var rows = dataSheet.UsedRange.Rows.Count - 1;
// Chart settings and stuff
Excel.ChartObjects xlCharts = (Excel.ChartObjects)workSheet.ChartObjects(Type.Missing);
Excel.ChartObject runChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);
Excel.Chart runChartPage = runChart.Chart;
runChartPage.ChartType = Excel.XlChartType.xlLine;
// set ChartStyle based on Office version
var chartStyle = 301;
if (Type.GetTypeFromProgID("Excel.Application.14") != null)
{
chartStyle = 2;
}
runChartPage.HasTitle = true;
runChartPage.HasLegend = true;
runChartPage.ChartTitle.Text = sheet.Name;
runChartPage.ChartStyle = chartStyle;
// Position of chart
var runChartPosition = "B" + chartPositionNumber;
Excel.Range runChartPlacementRange = workSheet.get_Range(runChartPosition, runChartPosition);
runChart.Top = runChartPlacementRange.Top;
runChart.Left = runChartPlacementRange.Left;
chartPositionNumber = chartPositionNumber + 21;
// Size of Chart
runChart.Width = 500;
runChart.Height = 250;
Excel.SeriesCollection runSeriesCollection = runChartPage.SeriesCollection();
// Create run line chart
for (int i = 2; i <= (colomn); i++)
{
Excel.Series runSeries = runSeriesCollection.NewSeries();
runSeries.Name = dataSheet.Cells[1, i].Value;
// set correct range for chart data
var ia = i;
// Time range
var xValuesBegin = ParseColumnName(1) + "2";
var xValuesEnd = ParseColumnName(1) + (rows.ToString());
var valuesBegin = ParseColumnName(ia) + (2).ToString();
var valuesEnd = ParseColumnName(ia) + (rows + 1).ToString();
runSeries.XValues = dataSheet.get_Range(xValuesBegin, xValuesEnd);
runSeries.Values = dataSheet.get_Range(valuesBegin, valuesEnd);
}
}
sheetNumber++;
}
var error = new ErrorHandler();
try
{
// Save excel sheet
workSheet.SaveAs(reportFile);
}
catch (Exception)
{
excel.Quit();
error.Exit(93);
}
// Close sheet
excel.Quit();
}
private void Test()
{
Report.XL.Visible = true;
Report.XL.UserControl = true;
Microsoft.Office.Interop.Excel._Workbook oWB = Report.CreateWorkBook();
Microsoft.Office.Interop.Excel._Worksheet oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;
int nRows = 4;
int nColumns = 4;
string upperLeftCell = "B3";
int endRowNumber = System.Int32.Parse(upperLeftCell.Substring(1))
+ nRows - 1;
char endColumnLetter = System.Convert.ToChar(
Convert.ToInt32(upperLeftCell[0]) + nColumns - 1);
string upperRightCell = System.String.Format("{0}{1}",
endColumnLetter, System.Int32.Parse(upperLeftCell.Substring(1)));
string lowerRightCell = System.String.Format("{0}{1}",
endColumnLetter, endRowNumber);
// Now create the chart.
ChartObjects chartObjs = (ChartObjects)oSheet.ChartObjects(Type.Missing);
ChartObject chartObj = chartObjs.Add(100, 120, 300, 300);
Chart xlChart = chartObj.Chart;
oSheet.Cells[1, 1] = "Data for chart";
object[,] oData = new object[4, 4];
oData[0, 0] = "Year";
oData[0, 1] = "Construction";
oData[0, 2] = "Maintenance";
oData[0, 3] = "Rehabilitation";
oData[1, 0] = "2008";
oData[1, 1] = 13400000;
oData[1, 2] = 25500000;
oData[1, 3] = 49900000;
oData[2, 0] = "2009";
oData[2, 1] = 10400000;
oData[2, 2] = 45500000;
oData[2, 3] = 14500000;
oData[3, 0] = "2010";
oData[3, 1] = 8400000;
oData[3, 2] = 75500000;
oData[3, 3] = 44500000;
Range rg = oSheet.get_Range("B3", "E6");
//rg.Value2 = AddData(nRows, nColumns);
rg.Value2 = oData;
// Data points
Range chartRange = oSheet.get_Range("C4", lowerRightCell);
xlChart.SetSourceData(chartRange, Type.Missing);
xlChart.ChartType = XlChartType.xlColumnClustered;
// Xaxis data labels
Series oS = (Series)xlChart.SeriesCollection(1);
//Series oS = (Series)xlChart.Chart.SeriesCollection(1);
oS.XValues = "='Sheet1'!$B$4:$" + "B$6";
// Add title:
xlChart.HasTitle = true;
xlChart.ChartTitle.Text = "My Title";
// Xaxis title
Axis xAxis = (Axis)xlChart.Axes(XlAxisType.xlCategory,
XlAxisGroup.xlPrimary);
xAxis.HasTitle = true;
xAxis.AxisTitle.Text = "Budget Years";
xAxis.AxisTitle.Font.Size = 11;
// legend:
xlChart.HasLegend = true;
int seriesNum = 1;
string [] aStr = new string[] { "Construction", "Maintenance", "Rehab" };
foreach (string str in aStr)
{
oS = (Series)xlChart.SeriesCollection(seriesNum++);
//oS = (Series)xlChart.Chart.SeriesCollection(seriesNum++);
oS.Name = str;
}
Axis yAxis = (Axis)xlChart.Axes(XlAxisType.xlValue,
XlAxisGroup.xlPrimary);
yAxis.HasTitle = true;
yAxis.AxisTitle.Text = "Dollars";
yAxis.AxisTitle.Font.Size = 11;
// Change font size of X-Axis label
//Axis xAxis = (Axis)xlChart.Chart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
//xAxis.AxisTitle.Font.Size = 11;
}
