private void AddDataPoints(Excel.Chart bubbleChart, Excel.Worksheet sourceWorksheet)
{
var dataMatrix = GetDataMatrixFromExcelWorksheet(sourceWorksheet);
var seriesCollection = bubbleChart.SeriesCollection();
var minXValue = double.MaxValue;
var minYValue = double.MaxValue;
var maxXValue = double.MinValue;
var maxYValue = double.MinValue;
foreach (var row in dataMatrix)
{
var series = seriesCollection.NewSeries();
series.Has3DEffect = true;
series.HasDataLabels = true;
var dataLabelText = row[0];
var xValue = Convert.ToDouble(row[1]);
var yValue = Convert.ToDouble(row[2]);
var revenue = Convert.ToDouble(row[3]) / 1000;
var projectTypeId = GetProjectTypeIdFromProjectTypeString(row[4]);
var bubbleSize = GetBubbleSizeFromRevenue(revenue);
series.Format.Fill.ForeColor.RGB = _projectTypeColors[projectTypeId];
series.XValues = new[] { xValue };
series.Values = new[] { yValue };
series.BubbleSizes = new[] { bubbleSize };
Excel.DataLabel dataLabel = series.DataLabels(1);
dataLabel.Text = dataLabelText;
if (xValue < minXValue)
{
minXValue = xValue;
}
if (yValue < minYValue)
{
minYValue = yValue;
}
if (xValue > maxXValue)
{
maxXValue = xValue;
}
if (yValue > maxYValue)
{
maxYValue = yValue;
}
}
var xAxis = (Excel.Axis)bubbleChart.Axes(Excel.XlAxisType.xlCategory);
xAxis.MinimumScale = (Math.Floor(minXValue / 100) - 1) * 100;
xAxis.MaximumScale = (Math.Ceiling(maxXValue / 100) + 1) * 100;
var yAxis = (Excel.Axis)bubbleChart.Axes(Excel.XlAxisType.xlValue);
yAxis.MinimumScale = (Math.Floor(minYValue * 10) - 1) / 10;
yAxis.MaximumScale = (Math.Ceiling(maxYValue * 10) + 1) / 10;
}
static OutputUnits makePerfMonGraphs(string file)
{
Excel.Application excelApp = null;
Excel.Workbook workbook = null;
Excel.Sheets sheets = null;
Excel.Worksheet dataSheet = null;
Excel.Worksheet newSheet = null;
Excel.ChartObjects xlChart = null;
Excel.Range dataY = null;
Excel.Chart memChart = null;
Excel.Chart diskChart = null;
Excel.Chart cpuChart = null;
Excel.Chart netChart = null;
Excel.Axis xAxis = null;
OutputUnits csData = null;
bool leaking = false;
bool highcpu = false;
string exitFile = "";
try {
excelApp = new Excel.Application();
string dir = file.Substring(0, file.LastIndexOf("\\") + 1);
string fm = file.Substring(0, file.Length - 4).Substring(file.LastIndexOf("\\") + 1);
workbook = excelApp.Workbooks.Open(file, 0, false, 6, Type.Missing, Type.Missing, Type.Missing, XlPlatform.xlWindows, ",",
true, false, 0, false, false, false);
sheets = workbook.Sheets;
dataSheet = sheets[1];
dataSheet.Name = "data";
dataSheet.get_Range("A2:A2", Type.Missing).EntireRow.Delete(XlDeleteShiftDirection.xlShiftUp);//garbage row
newSheet = (Worksheet)sheets.Add(Type.Missing, dataSheet, Type.Missing, Type.Missing);
newSheet.Name = "results";
xlChart = (Excel.ChartObjects)newSheet.ChartObjects(Type.Missing);
memChart = xlChart.Add(20, 100, 450, 175).Chart;
diskChart = xlChart.Add(20, 280, 450, 175).Chart;
cpuChart = xlChart.Add(500, 100, 450, 175).Chart;
netChart = xlChart.Add(500, 280, 450, 175).Chart;
int rowTotal = dataSheet.UsedRange.Rows.Count;
int colTotal = dataSheet.UsedRange.Columns.Count;
dataSheet.get_Range("A2", "A" + rowTotal).NumberFormat = "m/d/yyyy h:mm";
string ttime = dataSheet.Cells[2, 1].Value.ToString();
Array availMem = (System.Array)dataSheet.get_Range("C2", "C" + rowTotal).Value;
Array cpuTotal = (System.Array)dataSheet.get_Range("D2", "D" + rowTotal).Value;
Array diskTotal = (System.Array)dataSheet.get_Range("B2", "B" + rowTotal).Value;
dataSheet.Cells[1, colTotal + 1] = "Total LAN (Bytes Total/Sec)";
double[] netties = new double[rowTotal - 1];
for (int i = 2; i <= rowTotal; i++)
{
if (colTotal > 5)
{
Array netLine = (System.Array)dataSheet.get_Range(xlStr(5) + i, xlStr(colTotal) + i).Value;
double netLineTotal = 0;
for (int j = 1; j <= netLine.Length; j++)
{
netLineTotal += Convert.ToDouble(netLine.GetValue(1, j));
}
netties[i - 2] = netLineTotal;
dataSheet.Cells[i, colTotal + 1] = netLineTotal;
}
else
{
dataSheet.Cells[i, colTotal + 1] = "0";
}
}
#region BuildCounters
double[] mems = ColToDouble(availMem);
double[] cpus = ColToDouble(cpuTotal);
double[] disks = ColToDouble(diskTotal);
//netties[]
double avgCPUs = cpus.Average();
PCounter CPU = new PCounter(avgCPUs, cpus.Max(), cpus.Min());
PCounter MEM = new PCounter(mems.Average(), mems.Max(), mems.Min());
PCounter DISK = new PCounter(disks.Average(), disks.Max(), disks.Min());
PCounter NETS = new PCounter(netties.Average(), netties.Max(), netties.Min());
if (avgCPUs > 40)
{
highcpu = true;
}
#endregion
#region leakCheck
double[] eqMB = new double[2];
List <double> memList = new List <double>();
int cX = availMem.Length;
for (int i = 1; i < rowTotal - 1; i++)
{
memList.Add(Convert.ToDouble(availMem.GetValue(i, 1)));
}
eqMB = LeastSquares(memList);
double stdD1 = StandardDev(memList);
List <double> memList2 = sigma(memList, stdD1, eqMB);
cX = memList2.Count();
eqMB = LeastSquares(memList2);
double stdD2 = StandardDev(memList2) * 1.2;
List <double> memList3 = sigma(memList2, stdD2, eqMB);
eqMB = LeastSquares(memList3);
if (eqMB[0] < 0)
{
leaking = true;
newSheet.get_Range("E4", Type.Missing).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Tomato);
}
#endregion
#region formatting
string lan = xlStr(colTotal + 1);
newSheet.get_Range("A1", Type.Missing).EntireColumn.ColumnWidth = 12;
newSheet.get_Range("A1", Type.Missing).EntireColumn.HorizontalAlignment = XlHAlign.xlHAlignRight;
newSheet.get_Range("A2", Type.Missing).EntireRow.HorizontalAlignment = XlHAlign.xlHAlignCenter;
newSheet.Cells[4, 5] = eqMB[0];
newSheet.Cells[2, 2] = "Avg";
newSheet.Cells[2, 3] = "Min";
newSheet.Cells[2, 4] = "Max";
newSheet.Cells[2, 5] = "Slope(3Sigma)";
newSheet.Cells[3, 1] = "CPU";
newSheet.Cells[3, 2] = "=AVERAGE(data!D:D)";
newSheet.Cells[3, 3] = "=MIN(data!D:D)";
newSheet.Cells[3, 4] = "=MAX(data!D:D)";
newSheet.Cells[4, 1] = "Avail.RAM";
newSheet.Cells[4, 2] = "=AVERAGE(data!C:C)";
newSheet.Cells[4, 3] = "=MIN(data!C:C)";
newSheet.Cells[4, 4] = "=MAX(data!C:C)";
newSheet.Cells[5, 1] = "LAN Usage";
newSheet.Cells[5, 2] = "=AVERAGE(data!" + lan + ":" + lan + ")";
newSheet.Cells[5, 3] = "=MIN(data!" + lan + ":" + lan + ")";
newSheet.Cells[5, 4] = "=MAX(data!" + lan + ":" + lan + ")";
newSheet.Cells[6, 1] = "Disk Usage";
newSheet.Cells[6, 2] = "=AVERAGE(data!B:B)";
newSheet.Cells[6, 3] = "=MIN(data!B:B)";
newSheet.Cells[6, 4] = "=MAX(data!B:B)";
#endregion
#region memChart
dataY = dataSheet.Range["C1", "C" + rowTotal];
memChart.SetSourceData(dataY, Type.Missing);
memChart.ChartType = XlChartType.xlXYScatterLinesNoMarkers;
memChart.HasLegend = false;
xAxis = (Axis)memChart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
xAxis.MaximumScaleIsAuto = false;
xAxis.MaximumScale = rowTotal + 1;
xAxis.MinimumScaleIsAuto = false;
xAxis.MinimumScale = 0;
#endregion
#region diskChart
dataY = dataSheet.Range["B1", "B" + rowTotal];
diskChart.SetSourceData(dataY, Type.Missing);
diskChart.ChartType = XlChartType.xlXYScatterLinesNoMarkers;
diskChart.HasLegend = false;
xAxis = (Axis)diskChart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
xAxis.MaximumScaleIsAuto = false;
xAxis.MaximumScale = rowTotal + 1;
xAxis.MinimumScaleIsAuto = false;
xAxis.MinimumScale = 0;
#endregion
#region cpuChart
dataY = dataSheet.Range["D1", "D" + rowTotal];
cpuChart.SetSourceData(dataY, Type.Missing);
cpuChart.ChartType = XlChartType.xlXYScatterLinesNoMarkers;
cpuChart.HasLegend = false;
xAxis = (Axis)cpuChart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
xAxis.MaximumScaleIsAuto = false;
xAxis.MaximumScale = rowTotal + 1;
xAxis.MinimumScaleIsAuto = false;
xAxis.MinimumScale = 0;
#endregion
#region netChart
dataY = dataSheet.Range[xlStr(colTotal + 1) + "1", xlStr(colTotal + 1) + rowTotal];
netChart.SetSourceData(dataY, Type.Missing);
netChart.ChartType = XlChartType.xlXYScatterLinesNoMarkers;
netChart.HasLegend = false;
xAxis = (Axis)netChart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
xAxis.MaximumScaleIsAuto = false;
xAxis.MaximumScale = rowTotal + 1;
xAxis.MinimumScaleIsAuto = false;
xAxis.MinimumScale = 0;
#endregion
string host = Path.GetFileNameWithoutExtension(dir + fm);
csData = new OutputUnits(host, ttime + " time chunks: " + (rowTotal - 1), CPU, MEM, NETS, DISK, leaking, highcpu);
exitFile = dir + fm;
excelApp.DisplayAlerts = false;
workbook.SaveAs(@exitFile, XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, false, false, XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing, Type.Missing);
workbook.Close(true, Type.Missing, Type.Missing);
excelApp.Quit();
//releaseObject(sC);
//releaseObject(myChart);
} catch {
Console.WriteLine("Had issues interacting with your Excel installation...maybe try a restart?");
//using (StreamWriter outfile = File.AppendText("output.txt")) {
// outfile.WriteLine("Did have issues interacting with Excel on " + file);
//}
} finally {
releaseObject(xAxis);
releaseObject(dataY);
releaseObject(diskChart);
releaseObject(memChart);
releaseObject(cpuChart);
releaseObject(netChart);
releaseObject(xlChart);
releaseObject(newSheet);
releaseObject(dataSheet);
releaseObject(sheets);
releaseObject(workbook);
releaseObject(excelApp);
}
return(csData);
}
private void diagram_Click(object sender, EventArgs e)
{
KURS.allDataSet kursds = new KURS.allDataSet();
KURS.allDataSetTableAdapters.ZakazTableAdapter zta = new KURS.allDataSetTableAdapters.ZakazTableAdapter();
zta.Fill(kursds.Zakaz);
//
object misValue = System.Reflection.Missing.Value;
//
Excel.Application excelapp = new Excel.Application();
excelapp.Visible = true;
excelapp.SheetsInNewWorkbook = 1;
excelapp.Workbooks.Add(misValue);
Excel.Workbooks excelappworkbooks = excelapp.Workbooks;
Excel.Workbook excelappworkbook = excelappworkbooks[1];
Excel.Sheets excelsheets = excelappworkbook.Worksheets;
Excel.Worksheet excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1);
//excelworksheet.Activate();
//
var c = from p in kursds.Zakaz.AsEnumerable() where p.Data.Month == dateTimePicker1.Value.Month select p;
int count = 0;
excelapp.Cells[1][1] = "date";
excelapp.Cells[2][1] = "summ";
foreach (var p in c)
{
count++;
}
//
int[] a = new int[count]; double[] b = new double[count]; int i = 0; double sum = 0;
while (i < count)
{
foreach (var p in c)
{
a[i] = p.Data.Day;
if (i == 0)
{
sum += p.Summ;
}
if (i > 0)
{
if (a[i] == a[i - 1])
{
sum += p.Summ;
}
else
{
b[i - 1] = sum; sum = p.Summ; i++;
}
}
else
{
i++;
}
}
b[i - 1] = sum;
count = i; i = 0;
break;
}
Dictionary <int, double> dic = new Dictionary <int, double>();
while (i < count)
{
excelapp.Cells[1][i + 2] = a[i];
excelapp.Cells[2][i + 2] = b[i];
dic.Add(a[i], b[i]);
i++;
}
//
string str = (count + 1).ToString();
//
//Excel.Window excelWindow = null;
Excel.Range excelcells = null;
//Определяем диаграммы как объекты Excel.ChartObjects
Excel.ChartObjects chartsobjrcts = (Excel.ChartObjects)excelworksheet.ChartObjects(Type.Missing);
//Добавляем одну диаграмму в Excel.ChartObjects - диаграмма пока не выбрана, но место для нее выделено в методе Add
Excel.ChartObject chartsobjrct = chartsobjrcts.Add(100, 40, 300, 300);
Excel.Chart chartPage = chartsobjrct.Chart;
excelcells = excelworksheet.get_Range("B1", "B" + str);
chartPage.SetSourceData(excelcells, misValue);
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
Excel.Axis axis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
axis.HasTitle = true;
axis.AxisTitle.Text = "Data";
//
Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chartPage.SeriesCollection(Type.Missing);
Excel.Series series = seriesCollection.Item(1);
series.XValues = a;
//
excelappworkbook.SaveAs(@"D:\siple.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
excelappworkbook.Close(true, misValue, misValue);
excelapp.Quit();
this.chart1.Titles.Add("Отчет за месяц");
this.chart1.Series[0].Points.Clear();
Series ser = this.chart1.Series[0];
ser.LegendText = "Summ";
foreach (KeyValuePair <int, double> pair in dic)
{
ser.Points.AddXY(pair.Key, pair.Value);
}
//
}
private void btnConvert_Click(object sender, EventArgs e)
{
string inputPath = this.textBox2.Text;
string outputPath = this.textBox3.Text;
Object misValue = System.Reflection.Missing.Value;
Excel.Application xls = new Excel.Application();
String excelExt = "xlsx";
Directory.CreateDirectory(outputPath);
this.textBox4.Clear();
int numCheckedStations = checkedListBox2.CheckedItems.Count;
int numStationsProcessed = 0;
foreach (string stationCode in checkedListBox2.CheckedItems)
{
numStationsProcessed++;
Excel.Workbook xlsWorkBook = xls.Workbooks.Add(misValue);
Excel.Worksheet xlsWorkSheet = (Excel.Worksheet)xlsWorkBook.Sheets[1];
System.IO.StreamReader fileReader;
String info1, info2;
int nt = 0;
double dt;
String filePathPrefix = inputPath + "\\" + stationCode;
String outputFile = outputPath + "\\" + stationCode + "." + excelExt;
//this.textBox4.Text +="Processing "+stationCode + "("+numStationsProcessed+"/"+numCheckedStations+") ";
this.textBox4.AppendText("Processing " + stationCode + "(" + numStationsProcessed + "/" + numCheckedStations + ") ");
xlsWorkSheet.Cells[1, 1] = "Component";
xlsWorkSheet.Cells[2, 1] = "No. of timesteps";
xlsWorkSheet.Cells[3, 1] = "Size of timesteps (seconds)";
xlsWorkSheet.Cells[4, 1] = "Time (seconds)";
xlsWorkSheet.Cells[5, 1] = "Acceleration";
String[] componentsCode = { "000", "090", "ver" };
String[] componentsStr = { "X-axis (000)", "Y-axis (090)", "Z-axis (ver)" };
for (int k = 0; k < 3; k++) //Iterates each component file
{
this.textBox4.AppendText(Convert.ToString(k + 1) + "...");
String ext = componentsCode[k];
fileReader = new StreamReader(new FileStream(filePathPrefix + "." + ext, FileMode.Open));
info1 = fileReader.ReadLine();
info2 = fileReader.ReadLine();
int LastNonEmpty = -1;
String[] info2Array = info2.Split();
for (int i = 0; i < info2Array.Length; i++)
{
if (info2Array[i] != "")
{
LastNonEmpty += 1;
info2Array[LastNonEmpty] = info2Array[i];
}
}
nt = Convert.ToInt32(info2Array[0]); //number of time steps
dt = Convert.ToDouble(info2Array[1]); //time step size
Double[,] timeRange = new double[nt, 1];
xlsWorkSheet.Cells[1, 2 + k] = componentsStr[k];
xlsWorkSheet.Cells[2, 2 + k] = nt;
xlsWorkSheet.Cells[3, 2 + k] = dt;
LastNonEmpty = -1;
String lines = fileReader.ReadToEnd();
String[] strArray = lines.Split();
Double[,] dblArray = new Double[nt, 1]; //needs to be 2-d to be able to bulk write
for (int i = 0; i < strArray.Length; i++)
{
if (strArray[i] != "")
{
LastNonEmpty += 1;
timeRange[LastNonEmpty, 0] = LastNonEmpty * dt;
dblArray[LastNonEmpty, 0] = Convert.ToDouble(strArray[i].Replace("\n", ""));
}
}
int nt2 = LastNonEmpty + 1;
if (k == 0)
{
xlsWorkSheet.Range["A5"].Resize[nt2, 1].Value = timeRange; // first column filled with time steps
}
Excel.Range cell;
cell = xlsWorkSheet.Cells[5, 2 + k];
xlsWorkSheet.Range[cell, cell].Resize[nt2, 1].Value = dblArray; //bulk write at column "B, C, D"
}
Excel.WorksheetFunction wsf = xls.WorksheetFunction;
Double maxAcc = wsf.Max(xlsWorkSheet.Range[xlsWorkSheet.Cells[5, 4], xlsWorkSheet.Cells[nt + 4, 4]]); //find the maximum and minimum
Double minAcc = wsf.Min(xlsWorkSheet.Range[xlsWorkSheet.Cells[5, 4], xlsWorkSheet.Cells[nt + 4, 4]]);
Double maxAmpAcc = Math.Max(maxAcc, -1.0 * minAcc); //Select the max of absolute value of two
for (int k = 0; k < 3; k++) // Plotting
{
String ext = componentsCode[k];
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlsWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(200, 80 + 300 * k, 600, 250);
Excel.Chart chartPage = myChart.Chart;
chartRange = xlsWorkSheet.Range[xlsWorkSheet.Cells[5, 2 + k], xlsWorkSheet.Cells[nt + 4, 2 + k]];
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlLine;
chartPage.HasTitle = true;
Excel.Series series = (Excel.Series)chartPage.SeriesCollection(1);
Microsoft.Office.Interop.Excel.Axis xAxis = (Microsoft.Office.Interop.Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
Microsoft.Office.Interop.Excel.Axis yAxis = (Microsoft.Office.Interop.Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
xAxis.HasTitle = true;
xAxis.AxisTitle.Text = "Time (sec)";
xAxis.CategoryNames = (Excel.Range)xlsWorkSheet.Range["A5"].Resize[nt, 1];
xAxis.TickLabelPosition = Excel.XlTickLabelPosition.xlTickLabelPositionLow;
yAxis.HasTitle = true;
yAxis.AxisTitle.Text = "Acceleration (cm/s^2)";
yAxis.MinimumScale = (-1 * maxAmpAcc * 3.0);
yAxis.MaximumScale = (maxAmpAcc * 3.0);
series.Name = ext;
switch (k)
{
case 0:
chartPage.ChartTitle.Text = "[" + stationCode + "]: " + "Acceleration along X-axis (" + ext + ")";
series.Border.Color = (int)Excel.XlRgbColor.rgbRed;
break;
case 1:
chartPage.ChartTitle.Text = "[" + stationCode + "]: " + "Acceleration along Y-axis (" + ext + ")";
series.Border.Color = (int)Excel.XlRgbColor.rgbBlue;
break;
case 2:
chartPage.ChartTitle.Text = "[" + stationCode + "]: " + "Acceleration along Z-axis (" + ext + ")";
series.Border.Color = (int)Excel.XlRgbColor.rgbGreen;
break;
}
}
this.textBox4.AppendText("Done..!" + Environment.NewLine);
if (File.Exists(outputFile)) // delete file if it already exists
{
File.Delete(outputFile);
}
xlsWorkBook.SaveAs(outputFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, misValue, misValue,
false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
misValue, misValue, misValue, misValue, misValue);
xlsWorkBook.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsWorkSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsWorkBook);
}
xls.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xls);
//MessageBox.Show("Done!");
this.textBox4.AppendText("Finished!!" + Environment.NewLine);
}
public void ExportToExcel(string mapname, int address, int length, byte[] mapdata, int cols, int rows, bool isSixteenbit, int[] xaxisvalues, int[] yaxisvalues, bool isupsidedown, string xaxisdescr, string yaxisdescr, string zaxisdescr)
{
//en-US
CultureInfo tci = new CultureInfo("en-US");
Thread.CurrentThread.CurrentCulture = tci;
try
{
try
{
if (xla == null)
{
xla = new Microsoft.Office.Interop.Excel.Application();
}
}
catch (Exception xlaE)
{
Console.WriteLine("Failed to create office application interface: " + xlaE.Message);
}
// turn mapdata upside down
if (isupsidedown)
{
mapdata = TurnMapUpsideDown(mapdata, cols, rows, isSixteenbit);
}
xla.Visible = true;
Microsoft.Office.Interop.Excel.Workbook wb = xla.Workbooks.Add(XlSheetType.xlWorksheet);
Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)xla.ActiveSheet;
ws.Name = "symboldata";
// Now create the chart.
ChartObjects chartObjs = (ChartObjects)ws.ChartObjects(Type.Missing);
ChartObject chartObj = chartObjs.Add(100, 400, 400, 300);
Microsoft.Office.Interop.Excel.Chart xlChart = chartObj.Chart;
int nRows = rows;
//if (isSixteenbit) nRows /= 2;
int nColumns = cols;
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);
// Send single dimensional array to Excel:
Range rg1 = ws.get_Range("B2", "Z2");
double[] xarray = new double[nColumns];
double[] yarray = new double[nRows];
ws.Cells[1, 1] = "Data for " + mapname;
for (int i = 0; i < xarray.Length; i++)
{
if (xaxisvalues.Length > i)
{
xarray[i] = (int)xaxisvalues.GetValue(i);
}
else
{
xarray[i] = i;
}
//ws.Cells[i + 3, 1] = xarray[i];
ws.Cells[2, 2 + i] = xarray[i];
}
for (int i = 0; i < yarray.Length; i++)
{
if (yaxisvalues.Length > i)
{
if (isupsidedown)
{
yarray[i] = (int)yaxisvalues.GetValue((yarray.Length - 1) - i);
}
else
{
yarray[i] = (int)yaxisvalues.GetValue(i);
}
}
else
{
yarray[i] = i;
}
ws.Cells[i + 3, 1] = yarray[i];
//ws.Cells[2, 2 + i] = yarray[i];
}
Range rg = ws.get_Range(upperLeftCell, lowerRightCell);
rg.Value2 = AddData(nRows, nColumns, mapdata, isSixteenbit);
Range chartRange = ws.get_Range("A2", lowerRightCell);
xlChart.SetSourceData(chartRange, Type.Missing);
if (yarray.Length > 1)
{
xlChart.ChartType = XlChartType.xlSurface;
}
// Customize axes:
Axis xAxis = (Axis)xlChart.Axes(XlAxisType.xlCategory,
XlAxisGroup.xlPrimary);
xAxis.HasTitle = true;
xAxis.AxisTitle.Text = yaxisdescr;
try
{
Axis yAxis = (Axis)xlChart.Axes(XlAxisType.xlSeriesAxis,
XlAxisGroup.xlPrimary);
yAxis.HasTitle = true;
yAxis.AxisTitle.Text = xaxisdescr;
}
catch (Exception E)
{
Console.WriteLine("Failed to set y axis: " + E.Message);
}
Axis zAxis = (Axis)xlChart.Axes(XlAxisType.xlValue,
XlAxisGroup.xlPrimary);
zAxis.HasTitle = true;
zAxis.AxisTitle.Text = zaxisdescr;
// Add title:
xlChart.HasTitle = true;
xlChart.ChartTitle.Text = mapname;
// Remove legend:
xlChart.HasLegend = false;
// add 3d shade
xlChart.SurfaceGroup.Has3DShading = true;
/*if (File.Exists(m_currentfile + "~" + mapname + ".xls"))
* {
*
* }*/
try
{
wb.SaveAs(Tools.Instance.m_currentfile + "~" + mapname + ".xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, false, null, null, null, null);
}
catch (Exception sE)
{
Console.WriteLine("Failed to save workbook: " + sE.Message);
}
}
catch (Exception E)
{
Console.WriteLine("Failed to export to excel: " + E.Message);
Console.WriteLine("Failed to export to excel: " + E.Message);
}
tci = new CultureInfo("nl-NL");
Thread.CurrentThread.CurrentCulture = tci;
}
private void button1_Click(object sender, EventArgs e)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
xlApp = new Excel.Application();
object misValue = System.Reflection.Missing.Value;
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
for (int i = 0; i < 10; i++)
{
xlWorkSheet.Cells[i + 2, 1] = x[i];
xlWorkSheet.Cells[i + 2, 2] = y[i];
}
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.
ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(100, 100, 300, 250);
Excel.Chart chartPage = myChart.Chart;
Excel.Range yRange;
Excel.Range xRange;
yRange = xlWorkSheet.get_Range("B2:B11");
xRange = xlWorkSheet.get_Range("A2:A11");
chartPage.SetSourceData(yRange, Excel.XlRowCol.xlColumns);
chartPage.SeriesCollection(1).XValues = xRange;
chartPage.SeriesCollection(1).Name = "y1-var";
chartPage.ChartType = Excel.XlChartType.xlXYScatter;
Excel.Axis xAxis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType.
xlCategory, Excel.XlAxisGroup.xlPrimary);
Excel.Axis yAxis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType.
xlValue, Excel.XlAxisGroup.xlPrimary);
xAxis.HasTitle = true;
xAxis.AxisTitle.Text = "x-axis";
yAxis.HasTitle = true;
yAxis.AxisTitle.Text = "y-axis";
chartPage.HasTitle = true;
chartPage.ChartTitle.Text = "Example";
chartPage.HasLegend = true;
xlWorkBook.SaveAs(path + "\\out.xls", Excel.XlFileFormat.xlWorkbookNormal,
misValue, misValue, misValue, misValue,
Excel.XlSaveAsAccessMode.xlExclusive, misValue,
misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
Marshal.ReleaseComObject(xlWorkSheet);
Marshal.ReleaseComObject(xlWorkBook);
Marshal.ReleaseComObject(xlApp);
MessageBox.Show("Excel file created");
MessageBox.Show(path);
}
private void buildXlsChart(Xls.Chart chart, Xls.Worksheet dataSheet, ChartInfo graph, int currentDataColumn, out int dataColumnsWritten)
{
chart.ChartTitle.Text = string.Format(Strings.ChartTitle, graph.PrimaryTitle, graph.SecondaryTitle);
dataColumnsWritten = 0;
double[,] xValues = null, yValues = null;
int seriesCounter = 0;
foreach (var s in graph.Series)
{
if (seriesCounter == 0)
{
dataSheet.Cells[2, currentDataColumn].Value = s.XUnitName;
xValues = ConvertToTwoDimensionalArray(s.XValues);
dataSheet.Range[dataSheet.Cells[3, currentDataColumn], dataSheet.Cells[3 + xValues.Length - 1, currentDataColumn]].Value2 = xValues;
dataColumnsWritten += 1;
}
dataSheet.Cells[1, currentDataColumn + dataColumnsWritten].Value = s.Name;
dataSheet.Cells[2, currentDataColumn + dataColumnsWritten].Value = s.YUnitName;
yValues = ConvertToTwoDimensionalArray(s.YValues);
dataSheet.Range[dataSheet.Cells[3, currentDataColumn + dataColumnsWritten], dataSheet.Cells[3 + yValues.Length - 1, currentDataColumn + dataColumnsWritten]].Value2 = yValues;
dataColumnsWritten += 1;
seriesCounter++;
}
if (seriesCounter > 0)
{
chart.SetSourceData(Source: dataSheet.Range[dataSheet.Cells[3, currentDataColumn], dataSheet.Cells[3 + xValues.Length - 1, currentDataColumn + dataColumnsWritten - 1]]);
Xls.SeriesCollection collection = chart.SeriesCollection();
Xls.Axes axes = null;
try
{
for (int i = 0; i < collection.Count; i++)
{
collection.Item(i + 1).Name = graph.Series.ElementAt(i).Name;
if (graph.Series.ElementAt(i).ShowInSecondaryVerticalAxis&& graph.HasSecondaryVerticalAxis)
{
collection.Item(i + 1).AxisGroup = Xls.XlAxisGroup.xlSecondary;
}
}
axes = chart.Axes();
axes.Item(Xls.XlAxisType.xlCategory, Xls.XlAxisGroup.xlPrimary).AxisTitle.Text = graph.GetHorizontalAxisTitle();
axes.Item(Xls.XlAxisType.xlValue, Xls.XlAxisGroup.xlPrimary).AxisTitle.Text = graph.GetPrimaryVerticalAxisTitle();
if (graph.HasSecondaryVerticalAxis)
{
axes.Item(Xls.XlAxisType.xlValue, Xls.XlAxisGroup.xlSecondary).HasTitle = true;
axes.Item(Xls.XlAxisType.xlValue, Xls.XlAxisGroup.xlSecondary).AxisTitle.Text = graph.GetSecondaryVerticalAxisTitle();
}
}
finally
{
if (axes != null)
{
Marshal.FinalReleaseComObject(axes);
}
Marshal.FinalReleaseComObject(collection);
}
}
}
public void CreateChart(string pivotTableName, int chartTop, string cTitle)
{
Excel.Worksheet activeSheet = null;
Excel.Range selectedRange = null;
Excel.Shapes shapes = null;
Excel.Chart chart = null;
Excel.ChartTitle chartTitle = null;
try
{
activeSheet = (Excel.Worksheet)(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveSheet;
Excel.Range rangePivot;
rangePivot = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A1", "AA20000");
selectedRange = IdentifyPivotRangesByName(pivotTableName);
shapes = activeSheet.Shapes;
// Width original 255
if (Globals.ExcelVersion == "15.0" || Globals.ExcelVersion == "16.0")
{
// 204 is a nice style with shadow
shapes.AddChart2(Style: 259, XlChartType: Excel.XlChartType.xlColumnClustered,
Left: 10, Top: chartTop, Width: 450,
Height: 210, NewLayout: true).Select();
}
else
{
shapes.AddChart(XlChartType: Excel.XlChartType.xlColumnClustered,
Left: 10, Top: chartTop, Width: 450,
Height: 210).Select();
}
chart = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveChart;
chart.HasTitle = true;
chart.ChartTitle.Text = cTitle;
chart.ChartTitle.Format.TextFrame2.TextRange.Font.Caps = Microsoft.Office.Core.MsoTextCaps.msoNoCaps;
chart.ChartArea.Interior.Color = System.Drawing.Color.FromArgb(242, 244, 244); // Change chart to light gray
//chart.ChartArea.Interior.Color = System.Drawing.Color.FromRgb(0, 255, 0);
//chart.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowValue, true, true, true, true, true, true, true, true, true); // Turn on data labels
chart.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowValue, true, true, true, false, false, true, true, true, true); // Turn on data labels
chart.SetSourceData(selectedRange);
chart.HasLegend = false;
chart.ApplyDataLabels();
if (Globals.ExcelVersion == "15.0" || Globals.ExcelVersion == "16.0")
{
chart.FullSeriesCollection(1).DataLabels.ShowValue = true;
}
else
{
chart.SeriesCollection(1).DataLabels.ShowValue = true;
}
if (pivotTableName == "PivotTableApplicationName")
{
chart.Axes(Excel.XlAxisType.xlCategory).TickLabels.Orientation = Excel.XlOrientation.xlUpward;
}
selectedRange.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
selectedRange.Borders[Excel.XlBordersIndex.xlEdgeRight].ColorIndex = 0;
selectedRange.Borders[Excel.XlBordersIndex.xlEdgeRight].TintAndShade = 0;
selectedRange.Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThin;
selectedRange.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
selectedRange.Borders[Excel.XlBordersIndex.xlEdgeLeft].ColorIndex = 0;
selectedRange.Borders[Excel.XlBordersIndex.xlEdgeLeft].TintAndShade = 0;
selectedRange.Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThin;
Globals.ChartBottom = (int)chart.ChartArea.Top + (int)chart.ChartArea.Height + 15;
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A2", "A2").Select();
// chart.Export(pivotTableName + ".png");
}
catch (Exception ex)
{
ex.Data.Add("ExcelHelper", " Error occurred in the CreateChart() method\r\n" + ex.Message);
throw;
}
finally
{
if (chartTitle != null)
{
Marshal.ReleaseComObject(chartTitle);
}
if (chart != null)
{
Marshal.ReleaseComObject(chart);
}
if (shapes != null)
{
Marshal.ReleaseComObject(shapes);
}
if (selectedRange != null)
{
Marshal.ReleaseComObject(selectedRange);
}
}
}
private void Exportar(String Empresa, Int32 Anio, String Titulo, String SubTitulo, String Nombre, System.Data.DataTable Reporte, System.Data.DataTable Comparacion1, System.Data.DataTable Comparacion2, Boolean MostrarRebate = false)
{
XLExcel.Application xlApplication;
XLExcel.Workbook xlWorkbook;
XLExcel.Worksheet xlWorksheetData;
XLExcel.Worksheet xlWorksheetPivot;
XLExcel.Worksheet xlWorksheetComparacion;
XLExcel.Worksheet xlWorksheetPivotRebate = null;
XLExcel.Worksheet xlWorksheetPivotFFVV = null;
XLExcel.Worksheet xlWorksheetPivotFFVVServicios = null;
XLExcel.Range xlRange;
XLExcel.Range xlRangeComparacion;
object misValue = System.Reflection.Missing.Value;
xlApplication = new XLExcel.Application();
Int32 _WorksheetsIndex = 1;
xlWorkbook = xlApplication.Workbooks.Add(misValue);
xlWorksheetData = (XLExcel.Worksheet)xlWorkbook.Worksheets[_WorksheetsIndex];
xlWorksheetData.Name = "BD";
_WorksheetsIndex += 1;
if (xlWorkbook.Worksheets.Count < _WorksheetsIndex)
{
xlWorkbook.Worksheets.Add(misValue, xlWorksheetData, misValue, misValue);
}
xlWorksheetPivot = (XLExcel.Worksheet)xlWorkbook.Worksheets[_WorksheetsIndex];
xlWorksheetPivot.Name = "TOTAL " + Nombre + (MostrarRebate ? " SIN REBATE" : "");
if (MostrarRebate)
{
_WorksheetsIndex += 1;
if (xlWorkbook.Worksheets.Count < _WorksheetsIndex)
{
xlWorkbook.Worksheets.Add(misValue, xlWorksheetPivot, misValue, misValue);
}
xlWorksheetPivotRebate = (XLExcel.Worksheet)xlWorkbook.Worksheets[_WorksheetsIndex];
xlWorksheetPivotRebate.Name = "TOTAL " + Nombre + " CON REBATE";
}
_WorksheetsIndex += 1;
if (xlWorkbook.Worksheets.Count < _WorksheetsIndex)
{
xlWorkbook.Worksheets.Add(misValue, xlWorksheetPivot, misValue, misValue);
}
xlWorksheetPivotFFVV = (XLExcel.Worksheet)xlWorkbook.Worksheets[_WorksheetsIndex];
xlWorksheetPivotFFVV.Name = "FUERZA VENTAS";
_WorksheetsIndex += 1;
if (xlWorkbook.Worksheets.Count < _WorksheetsIndex)
{
xlWorkbook.Worksheets.Add(misValue, xlWorksheetPivot, misValue, misValue);
}
xlWorksheetPivotFFVVServicios = (XLExcel.Worksheet)xlWorkbook.Worksheets[_WorksheetsIndex];
xlWorksheetPivotFFVVServicios.Name = "FFVV POR SERVICIOS";
_WorksheetsIndex += 1;
if (xlWorkbook.Worksheets.Count < _WorksheetsIndex)
{
xlWorkbook.Worksheets.Add(misValue, xlWorksheetPivot, misValue, misValue);
}
xlWorksheetComparacion = (XLExcel.Worksheet)xlWorkbook.Worksheets[_WorksheetsIndex];
xlWorksheetComparacion.Name = "COMPARATIVO";
try
{
String fuente = "Calibri";
int fontsize = 11;
int _sheetIndex = 1;
Int32 rowIndex = 0;
Int32 colIndex = 0;
#region [ Statment ]
xlWorksheetData.Activate();
//HEADER
var _columns = new Object[1, Reporte.Columns.Count];
foreach (System.Data.DataColumn _column in Reporte.Columns)
{
_columns[rowIndex, colIndex] = _column.ColumnName; colIndex += 1;
}
xlRange = xlWorksheetData.Range[xlWorksheetData.Cells[1, 1], xlWorksheetData.Cells[1, Reporte.Columns.Count]];
xlRange.Value2 = _columns;
rowIndex = 0;
colIndex = 0;
//CELLS
var _data = new Object[Reporte.Rows.Count, Reporte.Columns.Count];
foreach (System.Data.DataRow _row in Reporte.Rows)
{
colIndex = 0;
foreach (System.Data.DataColumn _column in Reporte.Columns)
{
_data[rowIndex, colIndex] = _row[_column.ColumnName];
colIndex += 1;
}
rowIndex += 1;
}
xlRange = xlWorksheetData.Range[xlWorksheetData.Cells[2, 1], xlWorksheetData.Cells[Reporte.Rows.Count + 1, Reporte.Columns.Count]];
xlRange.Value2 = _data;
#endregion
#region [ Comparacion ]
xlWorksheetComparacion = (XLExcel.Worksheet)xlWorkbook.Worksheets[(MostrarRebate ? 6 : 5)];
xlWorksheetComparacion.Name = "COMPARATIVO";
xlWorksheetComparacion.Activate();
rowIndex = 0;
colIndex = 0;
//HEADER
_columns = new Object[1, Comparacion1.Columns.Count];
foreach (System.Data.DataColumn _column in Comparacion1.Columns)
{
_columns[rowIndex, colIndex] = _column.ColumnName; colIndex += 1;
}
xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[7, 1], xlWorksheetComparacion.Cells[7, Comparacion1.Columns.Count]];
xlRangeComparacion.Value2 = _columns;
rowIndex = 0;
colIndex = 0;
//CELLS
_data = new Object[Comparacion1.Rows.Count, Comparacion1.Columns.Count];
foreach (System.Data.DataRow _row in Comparacion1.Rows)
{
colIndex = 0;
foreach (System.Data.DataColumn _column in Comparacion1.Columns)
{
_data[rowIndex, colIndex] = _row[_column.ColumnName]; colIndex += 1;
}
rowIndex += 1;
}
xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[8, 1], xlWorksheetComparacion.Cells[Comparacion1.Rows.Count + 7, Comparacion1.Columns.Count]];
xlRangeComparacion.Value2 = _data;
xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[7, 1], xlWorksheetComparacion.Cells[7, 1]];
xlRangeComparacion.Value = (Anio - 1).ToString();
xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[7, 1], xlWorksheetComparacion.Cells[10, 14]];
setBorderCelda(xlRangeComparacion, XLExcel.XlBorderWeight.xlThin, XLExcel.XlLineStyle.xlContinuous, true, true, true, true, true, true, true, false, false);
xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[7, 1], xlWorksheetComparacion.Cells[7, 14]];
xlRangeComparacion.Interior.Pattern = XLExcel.XlPattern.xlPatternSolid;
xlRangeComparacion.Interior.PatternColorIndex = XLExcel.XlPattern.xlPatternAutomatic;
xlRangeComparacion.Interior.ThemeColor = XLExcel.XlThemeColor.xlThemeColorAccent2;
xlRangeComparacion.Interior.TintAndShade = 0;
xlRangeComparacion.Interior.PatternTintAndShade = 0;
xlRangeComparacion.Font.ThemeColor = XLExcel.XlThemeColor.xlThemeColorDark1;
xlRangeComparacion.Font.TintAndShade = 0;
xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[7, 1], xlWorksheetComparacion.Cells[10, 1]];
xlRangeComparacion.Interior.Pattern = XLExcel.XlPattern.xlPatternSolid;
xlRangeComparacion.Interior.PatternColorIndex = XLExcel.XlPattern.xlPatternAutomatic;
xlRangeComparacion.Interior.ThemeColor = XLExcel.XlThemeColor.xlThemeColorAccent2;
xlRangeComparacion.Interior.TintAndShade = 0;
xlRangeComparacion.Interior.PatternTintAndShade = 0;
xlRangeComparacion.Font.ThemeColor = XLExcel.XlThemeColor.xlThemeColorDark1;
xlRangeComparacion.Font.TintAndShade = 0;
rowIndex = 0;
colIndex = 0;
//HEADER
_columns = new Object[1, Comparacion2.Columns.Count];
foreach (System.Data.DataColumn _column in Comparacion2.Columns)
{
_columns[rowIndex, colIndex] = _column.ColumnName; colIndex += 1;
}
xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[12, 1], xlWorksheetComparacion.Cells[12, Comparacion2.Columns.Count]];
xlRangeComparacion.Value2 = _columns;
rowIndex = 0;
colIndex = 0;
//CELLS
_data = new Object[Comparacion2.Rows.Count, Comparacion2.Columns.Count];
foreach (System.Data.DataRow _row in Comparacion2.Rows)
{
colIndex = 0;
foreach (System.Data.DataColumn _column in Comparacion2.Columns)
{
_data[rowIndex, colIndex] = _row[_column.ColumnName]; colIndex += 1;
}
rowIndex += 1;
}
xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[13, 1], xlWorksheetComparacion.Cells[Comparacion2.Rows.Count + 12, Comparacion2.Columns.Count]];
xlRangeComparacion.Value2 = _data;
xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[12, 1], xlWorksheetComparacion.Cells[12, 1]];
xlRangeComparacion.Value = Anio.ToString();
xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[12, 1], xlWorksheetComparacion.Cells[15, 14]];
setBorderCelda(xlRangeComparacion, XLExcel.XlBorderWeight.xlThin, XLExcel.XlLineStyle.xlContinuous, true, true, true, true, true, true, true, false, false);
xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[12, 1], xlWorksheetComparacion.Cells[12, 14]];
xlRangeComparacion.Interior.Pattern = XLExcel.XlPattern.xlPatternSolid;
xlRangeComparacion.Interior.PatternColorIndex = XLExcel.XlPattern.xlPatternAutomatic;
xlRangeComparacion.Interior.ThemeColor = XLExcel.XlThemeColor.xlThemeColorAccent2;
xlRangeComparacion.Interior.TintAndShade = 0;
xlRangeComparacion.Interior.PatternTintAndShade = 0;
xlRangeComparacion.Font.ThemeColor = XLExcel.XlThemeColor.xlThemeColorDark1;
xlRangeComparacion.Font.TintAndShade = 0;
xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[12, 1], xlWorksheetComparacion.Cells[15, 1]];
xlRangeComparacion.Interior.Pattern = XLExcel.XlPattern.xlPatternSolid;
xlRangeComparacion.Interior.PatternColorIndex = XLExcel.XlPattern.xlPatternAutomatic;
xlRangeComparacion.Interior.ThemeColor = XLExcel.XlThemeColor.xlThemeColorAccent2;
xlRangeComparacion.Interior.TintAndShade = 0;
xlRangeComparacion.Interior.PatternTintAndShade = 0;
xlRangeComparacion.Font.ThemeColor = XLExcel.XlThemeColor.xlThemeColorDark1;
xlRangeComparacion.Font.TintAndShade = 0;
XLExcel.Chart XLChart1 = (XLExcel.Chart)xlWorksheetComparacion.Shapes.AddChart(XLExcel.XlChartType.xl3DColumnClustered, 100, 250, 500, 300).Chart;
XLExcel.SeriesCollection XLSeriesCollection1 = XLChart1.SeriesCollection();
XLExcel.Series XLSerie1 = XLSeriesCollection1.NewSeries();
XLSerie1.Name = "=COMPARATIVO!$A$7";
XLSerie1.Values = "=COMPARATIVO!$B$8:$M$8";
XLSerie1.XValues = "=COMPARATIVO!$B$7:$M$7";
XLExcel.Series XLSerie2 = XLSeriesCollection1.NewSeries();
XLSerie2.Name = "=COMPARATIVO!$A$12";
XLSerie2.Values = "=COMPARATIVO!$B$13:$M$13";
XLSerie2.XValues = "=COMPARATIVO!$B$7:$M$7";
XLChart1.SetElement(Microsoft.Office.Core.MsoChartElementType.msoElementChartTitleAboveChart);
XLChart1.ChartTitle.Text = String.Format("COMPARATIVO {0} - TEUS {1} VS. {2}", Titulo, (Anio - 1), Anio);
XLChart1.SetElement(Microsoft.Office.Core.MsoChartElementType.msoElementPrimaryValueAxisTitleAdjacentToAxis);
XLExcel.Axis XlAxis1 = XLChart1.Axes(XLExcel.XlAxisType.xlValue, XLExcel.XlAxisGroup.xlPrimary) as XLExcel.Axis;
XlAxis1.HasTitle = true;
XlAxis1.AxisTitle.Caption = "TEUS";
//XLChart1.Activate();
XLChart1.Refresh();
XLExcel.Chart XLChart2 = (XLExcel.Chart)xlWorksheetComparacion.Shapes.AddChart(XLExcel.XlChartType.xl3DColumnClustered, 100, 600, 500, 300).Chart;
XLExcel.SeriesCollection XLSeriesCollection2 = XLChart2.SeriesCollection();
XLExcel.Series XLSerie3 = XLSeriesCollection2.NewSeries();
XLSerie3.Name = "=COMPARATIVO!$A$7";
XLSerie3.Values = "=COMPARATIVO!$B$9:$M$9";
XLSerie3.XValues = "=COMPARATIVO!$B$7:$M$7";
XLExcel.Series XLSerie4 = XLSeriesCollection2.NewSeries();
XLSerie4.Name = "=COMPARATIVO!$A$12";
XLSerie4.Values = "=COMPARATIVO!$B$14:$M$14";
XLSerie4.XValues = "=COMPARATIVO!$B$7:$M$7";
XLChart2.SetElement(Microsoft.Office.Core.MsoChartElementType.msoElementChartTitleAboveChart);
XLChart2.ChartTitle.Text = String.Format("COMPARATIVO {0} - PROFIT {1} VS. {2}", Titulo, (Anio - 1), Anio);
XLChart2.SetElement(Microsoft.Office.Core.MsoChartElementType.msoElementPrimaryValueAxisTitleAdjacentToAxis);
XLExcel.Axis XlAxis2 = XLChart2.Axes(XLExcel.XlAxisType.xlValue, XLExcel.XlAxisGroup.xlPrimary) as XLExcel.Axis;
XlAxis2.HasTitle = true;
XlAxis2.AxisTitle.Caption = "PROFIT";
//XLChart2.Activate();
XLChart2.Refresh();
//TITLE
xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[2, 1], xlWorksheetComparacion.Cells[2, 14]];
xlRangeComparacion.MergeCells = true;
xlRangeComparacion.Value = Titulo + " - COMPARATIVO TEUS Y PROFIT";
xlRangeComparacion.Font.Bold = true;
xlRangeComparacion.Font.Size = 16;
xlRangeComparacion.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter;
xlRangeComparacion.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter;
xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[3, 1], xlWorksheetComparacion.Cells[3, 14]];
xlRangeComparacion.MergeCells = true;
xlRangeComparacion.Value = SubTitulo + " - " + (Anio - 1).ToString() + " VS. " + Anio.ToString();
xlRangeComparacion.Font.Bold = true;
xlRangeComparacion.Font.Size = 12;
xlRangeComparacion.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter;
xlRangeComparacion.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter;
#endregion
#region [ TOTAL SIN REBATE ]
xlWorksheetData.Activate();
xlRange = xlWorksheetData.Range[xlWorksheetData.Cells[1, 1], xlWorksheetData.Cells[Reporte.Rows.Count + 1, Reporte.Columns.Count]];
xlWorksheetPivot = (XLExcel.Worksheet)xlWorkbook.Worksheets[2];
xlWorksheetPivot.Name = "TOTAL " + Nombre + (MostrarRebate ? " SIN REBATE" : "");
xlWorksheetPivot.Activate();
XLExcel.Range xlRangePivot;
//TITLE
xlRangePivot = xlWorksheetPivot.Range[xlWorksheetPivot.Cells[2, 1], xlWorksheetPivot.Cells[2, 14]];
xlRangePivot.MergeCells = true;
xlRangePivot.Value = Empresa + "-" + Titulo;
xlRangePivot.Font.Bold = true;
xlRangePivot.Font.Size = 16;
xlRangePivot.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter;
xlRangePivot.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter;
xlRangePivot = xlWorksheetPivot.Range[xlWorksheetPivot.Cells[3, 1], xlWorksheetPivot.Cells[3, 14]];
xlRangePivot.MergeCells = true;
xlRangePivot.Value = SubTitulo + " " + Anio.ToString();
xlRangePivot.Font.Bold = true;
xlRangePivot.Font.Size = 12;
xlRangePivot.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter;
xlRangePivot.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter;
xlRangePivot = xlWorksheetPivot.Range[xlWorksheetPivot.Cells[6, 1], xlWorksheetPivot.Cells[6, 1]];;
// create Pivot Cache and Pivot Table
XLExcel.PivotCache XLPivotCache = (XLExcel.PivotCache)xlWorkbook.PivotCaches().Add(XLExcel.XlPivotTableSourceType.xlDatabase, xlRange);
XLExcel.PivotTable XLPivotTable = (XLExcel.PivotTable)xlWorksheetPivot.PivotTables().Add(PivotCache: XLPivotCache, TableDestination: xlRangePivot, TableName: "SIN REBATE");
// create Pivot Field, note that name will be the same as column name on sheet one
XLExcel.PivotField XLPivotFieldMES = (XLExcel.PivotField)XLPivotTable.PivotFields("MES");
XLPivotFieldMES.Orientation = XLExcel.XlPivotFieldOrientation.xlColumnField;
XLPivotFieldMES.Name = "MES";
XLExcel.PivotField XLPivotFieldVENDEDOR = (XLExcel.PivotField)XLPivotTable.PivotFields("VENDEDOR");
XLPivotFieldVENDEDOR.Orientation = XLExcel.XlPivotFieldOrientation.xlRowField;
XLPivotFieldVENDEDOR.Name = "VENDEDOR";
XLExcel.PivotField XLPivotFieldTEUS = (XLExcel.PivotField)XLPivotTable.PivotFields("TEUS");
XLPivotFieldTEUS.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField;
XLPivotFieldTEUS.Function = XLExcel.XlConsolidationFunction.xlSum;
XLPivotFieldTEUS.Name = "Sum TEUS";
XLExcel.PivotField XLPivotFieldRENT_TOTAL = (XLExcel.PivotField)XLPivotTable.PivotFields("RENT_TOTAL");
XLPivotFieldRENT_TOTAL.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField;
XLPivotFieldRENT_TOTAL.Function = XLExcel.XlConsolidationFunction.xlSum;
XLPivotFieldRENT_TOTAL.Name = "Sum RENT_TOTAL";
XLPivotTable.SubtotalLocation(XLExcel.XlSubtototalLocationType.xlAtBottom);
XLPivotTable.DataPivotField.Orientation = XLExcel.XlPivotFieldOrientation.xlRowField;
XLPivotTable.DataPivotField.Position = 2;
XLPivotTable.TableStyle2 = "PivotStyleLight18";
XLPivotTable.RowAxisLayout(XLExcel.XlLayoutRowType.xlCompactRow);
#endregion
if (MostrarRebate)
{
#region [ TOTAL CON REBATE ]
xlWorksheetData.Activate();
xlRange = xlWorksheetData.Range[xlWorksheetData.Cells[1, 1], xlWorksheetData.Cells[Reporte.Rows.Count + 1, Reporte.Columns.Count]];
xlWorksheetPivotRebate = (XLExcel.Worksheet)xlWorkbook.Worksheets[3];
xlWorksheetPivotRebate.Name = "TOTAL " + Nombre + " CON REBATE";
xlWorksheetPivotRebate.Activate();
XLExcel.Range xlRangePivotRebate;
//TITLE
xlRangePivotRebate = xlWorksheetPivotRebate.Range[xlWorksheetPivotRebate.Cells[2, 1], xlWorksheetPivotRebate.Cells[2, 14]];
xlRangePivotRebate.MergeCells = true;
xlRangePivotRebate.Value = Empresa + "-" + Titulo;
xlRangePivotRebate.Font.Bold = true;
xlRangePivotRebate.Font.Size = 16;
xlRangePivotRebate.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter;
xlRangePivotRebate.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter;
xlRangePivotRebate = xlWorksheetPivotRebate.Range[xlWorksheetPivotRebate.Cells[3, 1], xlWorksheetPivotRebate.Cells[3, 14]];
xlRangePivotRebate.MergeCells = true;
xlRangePivotRebate.Value = SubTitulo + " " + Anio.ToString();
xlRangePivotRebate.Font.Bold = true;
xlRangePivotRebate.Font.Size = 12;
xlRangePivotRebate.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter;
xlRangePivotRebate.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter;
xlRangePivotRebate = xlWorksheetPivotRebate.Range[xlWorksheetPivotRebate.Cells[6, 1], xlWorksheetPivotRebate.Cells[6, 1]];;
// create Pivot Cache and Pivot Table
//XLExcel.PivotCache XLPivotCacheRebate = (XLExcel.PivotCache)xlWorkbook.PivotCaches().Add(XLExcel.XlPivotTableSourceType.xlDatabase, xlRange);
XLExcel.PivotTable XLPivotTableRebate = (XLExcel.PivotTable)xlWorksheetPivotRebate.PivotTables().Add(PivotCache: XLPivotCache, TableDestination: xlRangePivotRebate, TableName: "CON REBATE");
// create Pivot Field, note that name will be the same as column name on sheet one
XLExcel.PivotField XLPivotFieldMESRebate = (XLExcel.PivotField)XLPivotTableRebate.PivotFields("MES");
XLPivotFieldMESRebate.Orientation = XLExcel.XlPivotFieldOrientation.xlColumnField;
XLPivotFieldMESRebate.Name = "MES";
XLExcel.PivotField XLPivotFieldVENDEDORRebate = (XLExcel.PivotField)XLPivotTableRebate.PivotFields("VENDEDOR");
XLPivotFieldVENDEDORRebate.Orientation = XLExcel.XlPivotFieldOrientation.xlRowField;
XLPivotFieldVENDEDORRebate.Name = "VENDEDOR";
XLExcel.PivotField XLPivotFieldTEUSRebate = (XLExcel.PivotField)XLPivotTableRebate.PivotFields("TEUS");
XLPivotFieldTEUSRebate.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField;
XLPivotFieldTEUSRebate.Function = XLExcel.XlConsolidationFunction.xlSum;
XLPivotFieldTEUSRebate.Name = "Sum TEUS";
XLExcel.PivotField XLPivotFieldRENT_TOTALRebate = (XLExcel.PivotField)XLPivotTableRebate.PivotFields("RENTABILIDAD_CON_REBATE");
XLPivotFieldRENT_TOTALRebate.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField;
XLPivotFieldRENT_TOTALRebate.Function = XLExcel.XlConsolidationFunction.xlSum;
XLPivotFieldRENT_TOTALRebate.Name = "Sum RENTABILIDAD_CON_REBATE";
XLPivotTableRebate.SubtotalLocation(XLExcel.XlSubtototalLocationType.xlAtBottom);
XLPivotTableRebate.DataPivotField.Orientation = XLExcel.XlPivotFieldOrientation.xlRowField;
XLPivotTableRebate.DataPivotField.Position = 2;
XLPivotTableRebate.TableStyle2 = "PivotStyleLight18";
XLPivotTableRebate.RowAxisLayout(XLExcel.XlLayoutRowType.xlCompactRow);
#endregion
}
#region [ FUERZA DE VENTAS ]
xlWorksheetData.Activate();
xlRange = xlWorksheetData.Range[xlWorksheetData.Cells[1, 1], xlWorksheetData.Cells[Reporte.Rows.Count + 1, Reporte.Columns.Count]];
xlWorksheetPivotFFVV = (XLExcel.Worksheet)xlWorkbook.Worksheets[(MostrarRebate ? 4 : 3)];
xlWorksheetPivotFFVV.Name = "FUERZA VENTAS";
xlWorksheetPivotFFVV.Activate();
XLExcel.Range xlRangePivotFFVV;
//TITLE
xlRangePivotFFVV = xlWorksheetPivotFFVV.Range[xlWorksheetPivotFFVV.Cells[2, 1], xlWorksheetPivotFFVV.Cells[2, 14]];
xlRangePivotFFVV.MergeCells = true;
xlRangePivotFFVV.Value = Empresa + "-" + Titulo;
xlRangePivotFFVV.Font.Bold = true;
xlRangePivotFFVV.Font.Size = 16;
xlRangePivotFFVV.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter;
xlRangePivotFFVV.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter;
xlRangePivotFFVV = xlWorksheetPivotFFVV.Range[xlWorksheetPivotFFVV.Cells[3, 1], xlWorksheetPivotFFVV.Cells[3, 14]];
xlRangePivotFFVV.MergeCells = true;
xlRangePivotFFVV.Value = SubTitulo + " " + Anio.ToString();
xlRangePivotFFVV.Font.Bold = true;
xlRangePivotFFVV.Font.Size = 12;
xlRangePivotFFVV.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter;
xlRangePivotFFVV.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter;
xlRangePivotFFVV = xlWorksheetPivotFFVV.Range[xlWorksheetPivotFFVV.Cells[6, 1], xlWorksheetPivotFFVV.Cells[6, 1]];;
// create Pivot Cache and Pivot Table
//XLExcel.PivotCache XLPivotCache = (XLExcel.PivotCache)xlWorkbook.PivotCaches().Add(XLExcel.XlPivotTableSourceType.xlDatabase, xlRange);
XLExcel.PivotTable XLPivotTableFFVV = (XLExcel.PivotTable)xlWorksheetPivotFFVV.PivotTables().Add(PivotCache: XLPivotCache, TableDestination: xlRangePivotFFVV, TableName: "FFVV");
// create Pivot Field, note that name will be the same as column name on sheet one
XLExcel.PivotField XLPivotFieldMESFFVV = (XLExcel.PivotField)XLPivotTableFFVV.PivotFields("MES");
XLPivotFieldMESFFVV.Orientation = XLExcel.XlPivotFieldOrientation.xlColumnField;
XLPivotFieldMESFFVV.Name = "MES";
XLExcel.PivotField XLPivotFieldVENDEDORFFVV = (XLExcel.PivotField)XLPivotTableFFVV.PivotFields("VENDEDOR");
XLPivotFieldVENDEDORFFVV.Orientation = XLExcel.XlPivotFieldOrientation.xlRowField;
XLPivotFieldVENDEDORFFVV.Name = "VENDEDOR";
XLExcel.PivotField XLPivotFieldTEUSFFVV = (XLExcel.PivotField)XLPivotTableFFVV.PivotFields("TEUS");
XLPivotFieldTEUSFFVV.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField;
XLPivotFieldTEUSFFVV.Function = XLExcel.XlConsolidationFunction.xlSum;
XLPivotFieldTEUSFFVV.Name = "Sum TEUS";
if (MostrarRebate)
{
XLExcel.PivotField XLPivotFieldRENT_TOTALFFVV = (XLExcel.PivotField)XLPivotTableFFVV.PivotFields("RENTABILIDAD_CON_REBATE");
XLPivotFieldRENT_TOTALFFVV.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField;
XLPivotFieldRENT_TOTALFFVV.Function = XLExcel.XlConsolidationFunction.xlSum;
XLPivotFieldRENT_TOTALFFVV.Name = "Sum RENTABILIDAD_CON_REBATE";
}
else
{
XLExcel.PivotField XLPivotFieldRENT_TOTALFFVV = (XLExcel.PivotField)XLPivotTableFFVV.PivotFields("RENT_TOTAL");
XLPivotFieldRENT_TOTALFFVV.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField;
XLPivotFieldRENT_TOTALFFVV.Function = XLExcel.XlConsolidationFunction.xlSum;
XLPivotFieldRENT_TOTALFFVV.Name = "Sum RENT_TOTAL";
}
XLPivotTableFFVV.SubtotalLocation(XLExcel.XlSubtototalLocationType.xlAtBottom);
XLPivotTableFFVV.DataPivotField.Orientation = XLExcel.XlPivotFieldOrientation.xlRowField;
XLPivotTableFFVV.DataPivotField.Position = 2;
XLPivotTableFFVV.TableStyle2 = "PivotStyleLight18";
XLPivotTableFFVV.RowAxisLayout(XLExcel.XlLayoutRowType.xlCompactRow);
#endregion
#region [ FUERZA DE VENTAS TRAFICO ]
xlWorksheetData.Activate();
xlRange = xlWorksheetData.Range[xlWorksheetData.Cells[1, 1], xlWorksheetData.Cells[Reporte.Rows.Count + 1, Reporte.Columns.Count]];
xlWorksheetPivotFFVVServicios = (XLExcel.Worksheet)xlWorkbook.Worksheets[(MostrarRebate ? 5 : 4)];
xlWorksheetPivotFFVVServicios.Name = "FF VV Servicios";
xlWorksheetPivotFFVVServicios.Activate();
XLExcel.Range xlRangePivotFFVVServicios;
//TITLE
xlRangePivotFFVVServicios = xlWorksheetPivotFFVVServicios.Range[xlWorksheetPivotFFVVServicios.Cells[2, 1], xlWorksheetPivotFFVVServicios.Cells[2, 14]];
xlRangePivotFFVVServicios.MergeCells = true;
xlRangePivotFFVVServicios.Value = Empresa + "-" + Titulo;
xlRangePivotFFVVServicios.Font.Bold = true;
xlRangePivotFFVVServicios.Font.Size = 16;
xlRangePivotFFVVServicios.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter;
xlRangePivotFFVVServicios.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter;
xlRangePivotFFVVServicios = xlWorksheetPivotFFVVServicios.Range[xlWorksheetPivotFFVVServicios.Cells[3, 1], xlWorksheetPivotFFVVServicios.Cells[3, 14]];
xlRangePivotFFVVServicios.MergeCells = true;
xlRangePivotFFVVServicios.Value = SubTitulo + " " + Anio.ToString();
xlRangePivotFFVVServicios.Font.Bold = true;
xlRangePivotFFVVServicios.Font.Size = 12;
xlRangePivotFFVVServicios.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter;
xlRangePivotFFVVServicios.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter;
xlRangePivotFFVVServicios = xlWorksheetPivotFFVVServicios.Range[xlWorksheetPivotFFVVServicios.Cells[6, 1], xlWorksheetPivotFFVVServicios.Cells[6, 1]];;
// create Pivot Cache and Pivot Table
//XLExcel.PivotCache XLPivotCache = (XLExcel.PivotCache)xlWorkbook.PivotCaches().Add(XLExcel.XlPivotTableSourceType.xlDatabase, xlRange);
XLExcel.PivotTable XLPivotTableFFVVServicios = (XLExcel.PivotTable)xlWorksheetPivotFFVVServicios.PivotTables().Add(PivotCache: XLPivotCache, TableDestination: xlRangePivotFFVVServicios, TableName: "FFVV Servicios");
// create Pivot Field, note that name will be the same as column name on sheet one
XLExcel.PivotField XLPivotFieldMESFFVVServicios = (XLExcel.PivotField)XLPivotTableFFVVServicios.PivotFields("MES");
XLPivotFieldMESFFVVServicios.Orientation = XLExcel.XlPivotFieldOrientation.xlColumnField;
XLPivotFieldMESFFVVServicios.Name = "MES";
XLExcel.PivotField XLPivotFieldVENDEDORFFVVServicios = (XLExcel.PivotField)XLPivotTableFFVVServicios.PivotFields("TRAFICO");
XLPivotFieldVENDEDORFFVVServicios.Orientation = XLExcel.XlPivotFieldOrientation.xlRowField;
XLPivotFieldVENDEDORFFVVServicios.Name = "TRAFICO";
XLExcel.PivotField XLPivotFieldSERVICIOFFVVServicios = (XLExcel.PivotField)XLPivotTableFFVVServicios.PivotFields("VENDEDOR");
XLPivotFieldSERVICIOFFVVServicios.Orientation = XLExcel.XlPivotFieldOrientation.xlRowField;
XLPivotFieldSERVICIOFFVVServicios.Name = "VENDEDOR";
XLExcel.PivotField XLPivotFieldTEUSFFVVServicios = (XLExcel.PivotField)XLPivotTableFFVVServicios.PivotFields("TEUS");
XLPivotFieldTEUSFFVVServicios.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField;
XLPivotFieldTEUSFFVVServicios.Function = XLExcel.XlConsolidationFunction.xlSum;
XLPivotFieldTEUSFFVVServicios.Name = "Sum TEUS";
if (MostrarRebate)
{
XLExcel.PivotField XLPivotFieldRENT_TOTALFFVVServicios = (XLExcel.PivotField)XLPivotTableFFVVServicios.PivotFields("RENTABILIDAD_CON_REBATE");
XLPivotFieldRENT_TOTALFFVVServicios.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField;
XLPivotFieldRENT_TOTALFFVVServicios.Function = XLExcel.XlConsolidationFunction.xlSum;
XLPivotFieldRENT_TOTALFFVVServicios.Name = "Sum RENTABILIDAD_CON_REBATE";
}
else
{
XLExcel.PivotField XLPivotFieldRENT_TOTALFFVVServicios = (XLExcel.PivotField)XLPivotTableFFVVServicios.PivotFields("RENT_TOTAL");
XLPivotFieldRENT_TOTALFFVVServicios.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField;
XLPivotFieldRENT_TOTALFFVVServicios.Function = XLExcel.XlConsolidationFunction.xlSum;
XLPivotFieldRENT_TOTALFFVVServicios.Name = "Sum RENT_TOTAL";
}
XLPivotTableFFVVServicios.SubtotalLocation(XLExcel.XlSubtototalLocationType.xlAtBottom);
XLPivotTableFFVVServicios.DataPivotField.Orientation = XLExcel.XlPivotFieldOrientation.xlRowField;
XLPivotTableFFVVServicios.DataPivotField.Position = 2;
XLPivotTableFFVVServicios.TableStyle2 = "PivotStyleLight18";
XLPivotTableFFVVServicios.RowAxisLayout(XLExcel.XlLayoutRowType.xlCompactRow);
#endregion
xlApplication.Visible = true;
releaseObject(xlWorksheetData);
releaseObject(xlWorksheetPivot);
if (MostrarRebate)
{
releaseObject(xlWorksheetPivotRebate);
}
releaseObject(xlWorksheetComparacion);
releaseObject(xlWorkbook);
releaseObject(xlApplication);
}
catch (Exception ex)
{
releaseObject(xlWorksheetData);
releaseObject(xlWorksheetPivot);
if (MostrarRebate)
{
releaseObject(xlWorksheetPivotRebate);
}
releaseObject(xlWorksheetComparacion);
releaseObject(xlWorkbook);
releaseObject(xlApplication);
throw ex;
}
}
public void newTry(Office.IRibbonControl control)
{
//Limpar as respostas existentes no Inquérito
//this.addin.deleteResponses(globalUrl);
JObject responses = this.addin.getFormResponses(globalUrl);
List <Question> questions = parseResponses(responses);
int i = 0;
foreach (Excel.ChartObject xlsChart in xlsCharts)
{
xlsChart.Delete();
Excel.Worksheet dataSheet = dataWorkSheets[i];
//Setting the range of chart
string[] rangeids = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };
string lowerRange = "1";
int upRange = questions[i].Choices.Count() + 1;
string upperRange = upRange.ToString();
int lastCellUsed = 0;
//Descobrir o initial range
for (int x = 0; x < 70; x++)
{
if (dataSheet.Cells[2, x + 1].Value != null)
{
lastCellUsed = x;
}
}
lowerRange = rangeids[lastCellUsed + 2] + lowerRange;
lowerRangeGlobal = lowerRange[0].ToString();
if (questions[i].Type == "GRID")
{
int aux = 0;
string categoria1 = questions[i].Choices[0].row;
for (int y = 0; y < questions[i].Choices.Count; y++)
{
if (questions[i].Choices[y].row == categoria1)
{
aux++;
}
}
upperRange = rangeids[aux + lastCellUsed + 2] + (questions[i].Choices.Count / 2 - 1).ToString();
gridUpperRangeGlobal = upperRange[0].ToString();
}
else
{
upperRange = rangeids[lastCellUsed + 3] + upperRange;
upperRangeGlobal = upperRange[0].ToString();
}
Excel.Range tRange = dataSheet.Cells.get_Range(lowerRange, upperRange);
Excel.ListObject tbl1;
string tableName = "Tabela";
//Applying the set range on chart data table
DateTime date1 = DateTime.Now;
dataSheet.Cells.get_Range("A50").FormulaR1C1 = tRange;
tableName = date1.ToString();
dataSheet.Cells.get_Range("A51").FormulaR1C1 = tableName;
dataSheet.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, tRange, Type.Missing, Excel.XlYesNoGuess.xlYes, Type.Missing).Name = tableName;
tbl1 = dataSheet.ListObjects[tableName];
tbl1.Resize(tRange);
//Setting values for categories and respective series data
string option = rangeids[lastCellUsed + 2];
string count = rangeids[lastCellUsed + 3];
List <string> rows = new List <string>();
if (questions[i].Type == "GRID")
{
int iteraux = 2;
for (var j = 0; j < questions[i].Choices.Count; j++)
{
string optionaux = option + iteraux;
if (!rows.Contains(questions[i].Choices[j].row))
{
dataSheet.Cells.get_Range(optionaux).FormulaR1C1 = questions[i].Choices[j].row;
rows.Add(questions[i].Choices[j].row);
iteraux++;
}
}
string[] columnids = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };
int column_index = 0;
bool series_written = false;
for (int v = 0; v < rows.Count; v++)
{
string categoria = rows[v];
column_index = lastCellUsed + 2;
for (int u = 0; u < questions[i].Choices.Count; u++)
{
if (questions[i].Choices[u].row == categoria)
{
string celula = columnids[column_index + 1] + (v + 2).ToString();
dataSheet.Cells.get_Range(celula).FormulaR1C1 = questions[i].Choices[u].count.ToString();
if (!series_written)
{
dataSheet.Cells.get_Range(columnids[column_index + 1] + (v + 1).ToString()).FormulaR1C1 = questions[i].Choices[u].option;
}
column_index++;
}
}
series_written = true;
}
dataSheet.Cells.get_Range(lowerRange).FormulaR1C1 = "Try " + (newTryCount + 2).ToString();
}
else if (questions[i].Type == "SCALE")
{
for (var j = 0; j < questions[i].Choices.Count; j++)
{
int index1 = j + 2;
string optionaux = option + index1;
dataSheet.Cells.get_Range(optionaux).FormulaR1C1 = "- " + questions[i].Choices[j].option.ToString() + " -";
}
for (var k = 0; k < questions[i].Choices.Count; k++)
{
int index2 = k + 2;
string countaux = count + index2;
dataSheet.Cells.get_Range(countaux).FormulaR1C1 = questions[i].Choices[k].count.ToString();
}
dataSheet.Cells.get_Range(lowerRange).FormulaR1C1 = "Categoria";
string auxUpperRange = upperRange[0] + "1";
dataSheet.Cells.get_Range(auxUpperRange).FormulaR1C1 = "Try " + (newTryCount + 2).ToString();
}
else
{
for (var j = 0; j < questions[i].Choices.Count; j++)
{
int index1 = j + 2;
string optionaux = option + index1;
dataSheet.Cells.get_Range(optionaux).FormulaR1C1 = questions[i].Choices[j].option;
}
for (var k = 0; k < questions[i].Choices.Count; k++)
{
int index2 = k + 2;
string countaux = count + index2;
dataSheet.Cells.get_Range(countaux).FormulaR1C1 = questions[i].Choices[k].count.ToString();
}
dataSheet.Cells.get_Range(lowerRange).FormulaR1C1 = "Categoria";
string auxUpperRange = upperRange[0] + "1";
dataSheet.Cells.get_Range(auxUpperRange).FormulaR1C1 = "Try " + (newTryCount + 2).ToString();
}
// Insert graphic in Excel
//Output do Gráfico
float width = 500F;
float left = 230F;
if (questions[i].Choices.Count > 6)
{
width = 750F;
left = 130F;
}
else if (questions[i].Choices.Count > 12)
{
width = 1100;
left = 80F;
}
//Save value of last
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)dataSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = xlCharts.Add(50, 150, width, 250);
Excel.Chart chartPage = myChart.Chart;
object paramMissing = Type.Missing;
// Declare variables for the Chart.ChartWizard method.
object paramChartFormat = 1;
object paramCategoryLabels = 0;
object paramSeriesLabels = 0;
bool paramHasLegend = true;
// Create a new chart of the data.
myChart.Chart.ChartWizard(tRange, Excel.XlChartType.xlColumnClustered, paramChartFormat, Excel.XlRowCol.xlRows,
paramCategoryLabels, paramSeriesLabels, paramHasLegend, paramMissing, paramMissing, paramMissing, paramMissing);
chartRange = dataSheet.get_Range(lowerRange, upperRange);
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
Excel.Axis excelaxis = chartPage.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
int maxScale = 0;
for (int h = 0; h < questions[i].Choices.Count; h++)
{
if (questions[i].Choices[h].count > maxScale)
{
maxScale = questions[i].Choices[h].count;
}
}
if (questions[i].Type != "GRID")
{
chartPage.ChartTitle.Delete();
}
excelaxis.MajorUnit = (int)(maxScale + 10.0) / 5;
excelaxis.MinorUnit = (int)(maxScale + 10.0) / 10;
excelaxis.MinimumScale = 0;
excelaxis.MaximumScale = maxScale + 10.0;
PowerPoint.Slide currentSlide = this.addin.Application.ActiveWindow.View.Slide;
pptSlides[i].Select();
try
{
myChart.Copy();
}
catch (COMException e)
{
Thread.Sleep(500);
myChart.Copy();
}
shapeRange = pptSlides[i].Shapes.Paste();
PowerPoint.Shape previousGraph = pptSlides[i].Shapes[2];
previousGraph.Delete();
shapeRange.Left = left;
shapeRange.Top = 160F;
shapeRange.Height = 350F;
shapeRange.Width = width;
xlsChartsTemp.Add(myChart);
i++;
}
xlsCharts = xlsChartsTemp;
xlsChartsTemp = new List <Excel.ChartObject>();
newTryBool = true;
newTryCount++;
// dataWorkbook.Save();
}
public void generateResponsesSlides(Office.IRibbonControl control)
{
JObject responses = this.addin.getFormResponses(globalUrl);
string fileName = this.addin.getTitle(globalUrl);
string paramWorkbookPath = @".\" + fileName + ".xlsx";
string paramPowerpointPath = @".\" + fileName + ".pptx";
List <Question> questions = parseResponses(responses);
PowerPoint.CustomLayout layout = this.addin.Application.ActivePresentation.SlideMaster.CustomLayouts[6];
for (int i = 0; i < questions.Count(); i++)
{
//Adicionar novo slide e mudar slide de foco
pptSlide = this.addin.Application.ActivePresentation.Slides.AddSlide(this.addin.currentSlide + 1, layout);
pptSlides.Add(pptSlide);
this.addin.Application.ActiveWindow.Presentation.Slides[this.addin.currentSlide + 1].Select();
this.addin.currentSlide++;
//Output do Titulo da Pergunta
this.addin.Application.ActiveWindow.View.Slide.Shapes.Title.TextFrame.TextRange.Text = questions[i].Title;
Excel.Worksheet dataSheet;
if (i == 0)
{
//Create instance to Excel workbook to work with chart data
Excel.Application excelApp = new Excel.Application();
excelApp.Visible = true;
if (!File.Exists(paramWorkbookPath))
{
dataWorkbook = excelApp.Workbooks.Add();
dataWorkbooks.Add(dataWorkbook);
dataWorkbook.Windows[1].WindowState = Excel.XlWindowState.xlMinimized;
//Accessing the data worksheet for chart
dataSheet = ((Excel.Worksheet)dataWorkbook.Worksheets[i + 1]);
dataWorkSheets.Add(dataSheet);
}
else
{
dataWorkbook = excelApp.Workbooks.Open(paramWorkbookPath);
dataWorkbooks.Add(dataWorkbook);
dataWorkbook.Windows[1].WindowState = Excel.XlWindowState.xlMinimized;
//Accessing the data worksheet for chart
dataSheet = ((Excel.Worksheet)dataWorkbook.Worksheets[i + 1]);
dataWorkSheets.Add(dataSheet);
}
}
else
{
dataSheet = dataWorkbook.Worksheets.Add();
dataWorkSheets.Add(dataSheet);
}
//Setting the range of chart
string[] rangeids = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };
string lowerRange = "1";
int upRange = questions[i].Choices.Count() + 1;
string upperRange = upRange.ToString();
lowerRange = "A" + lowerRange;
//TODO TIRAR ESTE "F" HARDCODED
if (questions[i].Type == "GRID")
{
upperRange = "F" + upRange / 2;
int aux = 0;
string categoria1 = questions[i].Choices[0].row;
for (int y = 0; y < questions[i].Choices.Count; y++)
{
if (questions[i].Choices[y].row == categoria1)
{
aux++;
}
}
upperRange = rangeids[aux] + (questions[i].Choices.Count / 2 - 1).ToString();
gridUpperRangeGlobal = upperRange;
dataSheet.Cells.get_Range("A50").FormulaR1C1 = upperRange;
}
else
{
upperRange = "B" + upperRange;
dataSheet.Cells.get_Range("A50").FormulaR1C1 = upperRange;
}
Excel.Range tRange = dataSheet.Cells.get_Range(lowerRange, upperRange);
Excel.ListObject tbl1;
string tableName = "Tabela";
//Applying the set range on chart data table
tableName = tableName + i;
dataSheet.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, tRange, Type.Missing, Excel.XlYesNoGuess.xlYes, Type.Missing).Name = tableName;
tbl1 = dataSheet.ListObjects[tableName];
tbl1.Resize(tRange);
//Setting values for categories and respective series data
string option = "A";
string count = "B";
List <string> rows = new List <string>();
if (questions[i].Type == "GRID")
{
int iteraux = 2;
for (var j = 0; j < questions[i].Choices.Count; j++)
{
string optionaux = option + iteraux;
if (!rows.Contains(questions[i].Choices[j].row))
{
dataSheet.Cells.get_Range(optionaux).FormulaR1C1 = questions[i].Choices[j].row;
rows.Add(questions[i].Choices[j].row);
iteraux++;
}
}
string[] columnids = { "A", "B", "C", "D", "E", "F", "G", "H" };
int column_index = 0;
bool series_written = false;
for (int v = 0; v < rows.Count; v++)
{
string categoria = rows[v];
column_index = 0;
for (int u = 0; u < questions[i].Choices.Count; u++)
{
if (questions[i].Choices[u].row == categoria)
{
string celula = columnids[column_index + 1] + (v + 2).ToString();
dataSheet.Cells.get_Range(celula).FormulaR1C1 = questions[i].Choices[u].count.ToString();
if (!series_written)
{
dataSheet.Cells.get_Range(columnids[column_index + 1] + (v + 1).ToString()).FormulaR1C1 = questions[i].Choices[u].option;
}
column_index++;
}
}
series_written = true;
}
dataSheet.Cells.get_Range("A1").FormulaR1C1 = "Try1";
}
else if (questions[i].Type == "SCALE")
{
for (var j = 0; j < questions[i].Choices.Count; j++)
{
int index1 = j + 2;
string optionaux = option + index1;
dataSheet.Cells.get_Range(optionaux).FormulaR1C1 = "- " + questions[i].Choices[j].option.ToString() + " -";
}
for (var k = 0; k < questions[i].Choices.Count; k++)
{
int index2 = k + 2;
string countaux = count + index2;
dataSheet.Cells.get_Range(countaux).FormulaR1C1 = questions[i].Choices[k].count.ToString();
}
dataSheet.Cells.get_Range("A1").FormulaR1C1 = "Categoria";
dataSheet.Cells.get_Range("B1").FormulaR1C1 = "Try1";
}
else
{
for (var j = 0; j < questions[i].Choices.Count; j++)
{
int index1 = j + 2;
string optionaux = option + index1;
dataSheet.Cells.get_Range(optionaux).FormulaR1C1 = questions[i].Choices[j].option;
}
for (var k = 0; k < questions[i].Choices.Count; k++)
{
int index2 = k + 2;
string countaux = count + index2;
dataSheet.Cells.get_Range(countaux).FormulaR1C1 = questions[i].Choices[k].count.ToString();
}
dataSheet.Cells.get_Range("A1").FormulaR1C1 = "Categoria";
dataSheet.Cells.get_Range("B1").FormulaR1C1 = "Try1";
}
// Insert graphic in Excel
//Output do Gráfico
float width = 500F;
float left = 230F;
if (questions[i].Choices.Count > 6)
{
width = 750F;
left = 130F;
}
else if (questions[i].Choices.Count > 12)
{
width = 1100;
left = 80F;
}
//Save value of last
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)dataSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = xlCharts.Add(50, 150, width, 250);
Excel.Chart chartPage = myChart.Chart;
object paramMissing = Type.Missing;
// Declare variables for the Chart.ChartWizard method.
object paramChartFormat = 1;
object paramCategoryLabels = 0;
object paramSeriesLabels = 0;
bool paramHasLegend = true;
// Create a new chart of the data.
myChart.Chart.ChartWizard(tRange, Excel.XlChartType.xlColumnClustered, paramChartFormat, Excel.XlRowCol.xlRows,
paramCategoryLabels, paramSeriesLabels, paramHasLegend, paramMissing, paramMissing, paramMissing, paramMissing);
chartRange = dataSheet.get_Range(lowerRange, upperRange);
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
if (questions[i].Type != "GRID")
{
chartPage.ChartTitle.Delete();
}
Excel.Axis excelaxis = chartPage.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
int maxScale = 0;
for (int h = 0; h < questions[i].Choices.Count; h++)
{
if (questions[i].Choices[h].count > maxScale)
{
maxScale = questions[i].Choices[h].count;
}
}
excelaxis.MajorUnit = (int)(maxScale + 10.0) / 5;
excelaxis.MinorUnit = (int)(maxScale + 10.0) / 10;
excelaxis.MinimumScale = 0;
excelaxis.MaximumScale = maxScale + 10.0;
myChart.Copy();
shapeRange = pptSlide.Shapes.Paste();
// Position the chart on the slide.
shapeRange.Left = left;
shapeRange.Top = 160F;
shapeRange.Height = 350F;
shapeRange.Width = width;
//Copy chart to xlsCharts
xlsCharts.Add(myChart);
}
// Save excel file
if (newTryBool == true)
{
//dataWorkbook.Save();
}
else
{
//dataWorkbook.SaveAs(paramWorkbookPath, paramMissing, paramMissing, paramMissing, paramMissing,
//paramMissing, Excel.XlSaveAsAccessMode.xlNoChange, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing);
}
pptSlides[0].Select();
// Save the presentation.
//this.addin.Application.ActivePresentation.SaveAs(paramPowerpointPath, PowerPoint.PpSaveAsFileType.ppSaveAsOpenXMLPresentation, Office.MsoTriState.msoTrue);
}
/// <summary>
/// Applies the trained neural network model to the selected predictor
/// input data and displays the results in Excel.
///
/// The results consists of 3 columns - the first contains the selected
/// training set input (or predictor) values, the second the selected
/// training set target values and the third contains the trained model
/// output responses to the given input values. An XY-Scatter graph of
/// the data is also produced.
/// </summary>
/// <param name="net">the trained neural network</param>
///
private void ShowOutputInExcel(NeuralNet net)
{
List <double> dX = new List <double>();
List <double> dM = new List <double>();
// plot the model and target data in Excel
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
// open Excel and add a worksheet
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(Type.Missing);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
// add a header line
xlWorkSheet.Cells[1, 1] = "";
xlWorkSheet.Cells[1, 2] = "target";
xlWorkSheet.Cells[1, 3] = "model";
// add the model and training data
for (int i = 0; i < mInputVecs.Count; i++)
{
// calculate the model response value given the predictor value from the training set
dX = mInputVecs[i];
net.GetResponse(dX, dM);
// the required values are stored in vectors and need re-scaling
double xValue = dX[0] * ScaleFactor;
double yValue = mTargetVecs[i][0] * ScaleFactor;
double mValue = dM[0] * ScaleFactor;
// write out the results
xlWorkSheet.Cells[i + 2, 1] = xValue.ToString("G16");
xlWorkSheet.Cells[i + 2, 2] = yValue.ToString("G16");
xlWorkSheet.Cells[i + 2, 3] = mValue.ToString("G16");
}
// plot the data as an xy-scatter graph
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject modelChart = (Excel.ChartObject)xlCharts.Add(200, 10, 450, 300);
Excel.Chart chartPage = modelChart.Chart;
chartRange = xlWorkSheet.get_Range("A1", "C" + (mInputVecs.Count + 1).ToString());
chartPage.SetSourceData(chartRange);
chartPage.ChartType = Excel.XlChartType.xlXYScatter;
// set the axis labels
List <string> colNames = new List <string>();
mDataTab.GetColumnNames(colNames);
Excel.Axis xAxis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
xAxis.HasTitle = true;
xAxis.AxisTitle.Text = colNames[PredictorIdx];
Excel.Axis yAxis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
yAxis.HasTitle = true;
yAxis.AxisTitle.Text = colNames[ResponseIdx];
// set the graph title
chartPage.HasTitle = true;
chartPage.ChartTitle.Font.Size = 12;
chartPage.ChartTitle.Text = GraphTitle();
// make Excel visible to the user
xlApp.Visible = true;
// release resources
ReleaseObject(xlWorkSheet);
ReleaseObject(xlWorkBook);
ReleaseObject(xlApp);
}
/// <summary>
/// Applies the trained neural network model to the selected predictor
/// input data and outputs the results to an Excel .xls file.
///
/// The output consists of 3 columns - the first contains the selected
/// training set input (or predictor) values, the second the selected
/// training set target values and the third contains the trained model
/// output responses to the given input values. An XY-Scatter graph of
/// the data is also produced.
/// </summary>
/// <param name="net">the trained neural network</param>
/// <param name="fname">the name of the file to write the results to</param>
///
private void GenerateExcelOutput(NeuralNet net, string fname)
{
List <double> dX = new List <double>();
List <double> dM = new List <double>();
// open Excel, add a workbook and obtain a worksheet
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(Type.Missing);
Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
// stops Excel asking the user if they want to replace an existing file
// the save file dialog will have already asked this question
xlApp.DisplayAlerts = false;
// add a header line
xlWorkSheet.Cells[1, 1] = "";
xlWorkSheet.Cells[1, 2] = "target";
xlWorkSheet.Cells[1, 3] = "model";
// add the model and training data
for (int i = 0; i < mInputVecs.Count; i++)
{
// calculate the model response value given the predictor value from the training set
dX = mInputVecs[i];
net.GetResponse(dX, dM);
// the required values are stored in vectors and need re-scaling
double xValue = dX[0] * ScaleFactor;
double yValue = mTargetVecs[i][0] * ScaleFactor;
double mValue = dM[0] * ScaleFactor;
// write out the results
xlWorkSheet.Cells[i + 2, 1] = xValue.ToString("G16");
xlWorkSheet.Cells[i + 2, 2] = yValue.ToString("G16");
xlWorkSheet.Cells[i + 2, 3] = mValue.ToString("G16");
}
// plot the data as an xy-scatter graph
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject modelChart = (Excel.ChartObject)xlCharts.Add(200, 10, 450, 300);
Excel.Chart chartPage = modelChart.Chart;
chartRange = xlWorkSheet.get_Range("A1", "C" + (mInputVecs.Count + 1).ToString());
chartPage.SetSourceData(chartRange);
chartPage.ChartType = Excel.XlChartType.xlXYScatter;
// set the axis labels
List <string> colNames = new List <string>();
mDataTab.GetColumnNames(colNames);
Excel.Axis xAxis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
xAxis.HasTitle = true;
xAxis.AxisTitle.Text = colNames[PredictorIdx];
Excel.Axis yAxis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
yAxis.HasTitle = true;
yAxis.AxisTitle.Text = colNames[ResponseIdx];
// set the graph title
chartPage.HasTitle = true;
chartPage.ChartTitle.Font.Size = 12;
chartPage.ChartTitle.Text = GraphTitle();
// save the workbook and close Excel
xlWorkBook.SaveAs(fname, Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
xlWorkBook.Close(true, Type.Missing, Type.Missing);
xlApp.DisplayAlerts = true;
xlApp.Quit();
// release resources
ReleaseObject(xlWorkSheet);
ReleaseObject(xlWorkBook);
ReleaseObject(xlApp);
}
public async Task <byte[]> GenerateChartImageFromXlsxFile <TListEntity>(string excelChartTemplateName, string outPutFolder, IEnumerable <TListEntity> inputItems, int seriesCount, int xAxisDataCount)
{
if (inputItems != null && inputItems.Count() > 0)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
outPutFolder = System.Configuration.ConfigurationManager.AppSettings["ChartOutputFolder"].ToString(); //"C:\\ReportsGenerated\\Chart\\OutPuts";
excelChartTemplateName = System.Configuration.ConfigurationManager.AppSettings["ChartTemplate"].ToString(); //"C:\\ReportsGenerated\\Chart\\InPuts\\FinalTemplate_Test_good_series_xaxis_1.xlsx"; //
Console.WriteLine(excelChartTemplateName);
Console.WriteLine(outPutFolder);
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
//xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkBook = xlApp.Workbooks.Open(excelChartTemplateName);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
try
{
string[] properties = new string[] { "Name", "ValuePrev1", "ValuePrev2", "ValuePrev3", "ValuePrev4", "ValuePrev5", "ValuePrev6" };
List <TListEntity> items = new List <TListEntity>();
foreach (var item in inputItems)
{
items.Add(item);
}
Excel.Range chartRangeXaxis;
Excel.Range chartRange;
var rangeToValue = "";
var xAxisRangeToValue = "";
int i = 0;
var h = 0;
int[] alphabetNumArray = new int[] { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26 };
int i_clear = 0;
var h_clear = 0;
//clearing existing values
for (; i_clear < 4; i_clear++) //rows clearing
{
for (; h_clear < 4; h_clear++) //columns clearing
{
xlWorkSheet.Cells[(i_clear + 1), alphabetNumArray[h_clear]] = "";
}
}
for (i = 0; i < items.Count(); i++)
{
for (h = 0; h < xAxisDataCount + 1; h++)
{
string propertyName = properties[h];
var property = items[i].GetType().GetProperty(propertyName);
if (property != null)
{
var value = property.GetValue(items[i], null);
if (value != null)//required to check, for not allowing empty values
{
xlWorkSheet.Cells[(i + 1), alphabetNumArray[h]] = value;
//xlWorkSheet.Cells["A2"] = value;//this type was not works on microsoft.office.interop.excel
//xlWorkSheet.Cells[1,2] = value;//this is correct one
}
//else
// xlWorkSheet.Cells[(i + 1), alphabetNumArray[h]] = "";
}
}
}
string[] alphabetArray = new string[] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };
rangeToValue = alphabetArray[xAxisDataCount] + i;
xAxisRangeToValue = alphabetArray[xAxisDataCount] + 1;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
//Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);//for new one
Excel.ChartObject chartObject = (Excel.ChartObject)xlCharts.Item(1);
Excel.Chart chart = chartObject.Chart;
chartRangeXaxis = xlWorkSheet.get_Range("B1", xAxisRangeToValue);
chartRange = xlWorkSheet.get_Range("A2", rangeToValue);
//chartPage.SetSourceData(chartRangeXaxis, Excel.XlRowCol.xlRows);
chart.SetSourceData(chartRange, Excel.XlRowCol.xlRows);//sucess
//chartPage.ChartType = Excel.XlChartType.xlLineMarkers;//if creating new chart uncomment this one
Excel.Axis xAxis = (Excel.Axis)chart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
xAxis.HasTitle = false;
//xAxis.AxisTitle.Caption = "Time";
xAxis.CategoryNames = (Excel.Range)xlWorkSheet.get_Range("B1", xAxisRangeToValue);
Excel.Range formatRange;
formatRange = xlWorkSheet.get_Range("B2", rangeToValue);
formatRange.NumberFormat = "#,##0.00";
//chartPage.ChartArea.Width = 200;
//chartPage.ChartArea.Height = 100;
// chartPage.CategoryLabelLevel = Excel.XlCategoryLabelLevel.xlCategoryLabelLevelCustom;
//not working properly
//Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chartPage.SeriesCollection();
//Excel.Series series = seriesCollection.NewSeries();
//series.XValues = chartRangeXaxis;
//have to set for data labels number format as 0.00
//var series = chartObject.Chart.SeriesCollection() as Excel.SeriesCollection;
//foreach (var ser in series)
//{
// var DataLabels = ((Excel.Series)ser).DataLabels(1);
// DataLabels.
//}
var imageFileFullName = outPutFolder + @"\ChartImage_" + DateTime.Now.ToString("yyyyMMdd_HH_mm_ss") + ".png";
chart.Export(imageFileFullName, "PNG", false);
try
{
//xlWorkBook.SaveAs(outPutFolder + @"\Excel_" + DateTime.Now.ToString("yyyyMMdd_HH_mm_ss") + ".xlsx", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.SaveAs(outPutFolder + @"\Excel_" + DateTime.Now.ToString("yyyyMMddHHmmss") + DateTime.Now.Millisecond.ToString(), Excel.XlFileFormat.xlOpenXMLWorkbook, misValue, misValue, false, false, Excel.XlSaveAsAccessMode.xlNoChange, Excel.XlSaveConflictResolution.xlUserResolution, true, misValue, misValue, misValue);
//xlWorkBook.SaveAs(outPutFolder + @"\Excel_" + DateTime.Now.ToString("yyyyMMdd_HH_mm_ss") + ".xlsx", Excel.XlFileFormat.xlXMLSpreadsheet, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlShared, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
await this.releaseObject(xlWorkSheet);
await this.releaseObject(xlWorkBook);
await this.releaseObject(xlApp);
}
catch (Exception ex)
{
Console.WriteLine("Error closing Excel object" + ex.Message);
Console.WriteLine(ex.StackTrace);
}
finally
{
if (xlWorkSheet != null)
{
Marshal.ReleaseComObject(xlWorkSheet);
}
if (xlWorkBook != null)
{
Marshal.ReleaseComObject(xlWorkBook);
}
if (xlApp != null)
{
Marshal.ReleaseComObject(xlApp);
}
}
try
{
Console.WriteLine("Reading image");
byte[] byteArray = File.ReadAllBytes(imageFileFullName);
Console.WriteLine("Reading image 1");
System.IO.DirectoryInfo di = new DirectoryInfo(outPutFolder);
foreach (FileInfo file in di.GetFiles())
{
file.Delete();
}
return(byteArray);
}
catch (Exception ex)
{
Console.WriteLine("Failed to delete " + ex.Message);
}
return(null);
}
catch (Exception ex)
{
await this.releaseObject(xlWorkSheet);
await this.releaseObject(xlWorkBook);
await this.releaseObject(xlApp);
}
}
return(null);
}
// データテーブルをエクセルへエクスポート（ＸＲ管理図・ヒストグラム付き）
public void ExportToExcelWithXrChart(DataTable dt)
{
if (dt.Rows.Count == 0)
{
return;
}
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
// column headings
for (int i = 0; i < dt.Columns.Count; i++)
{
xlWorkSheet.Cells[1, (i + 1)] = dt.Columns[i].ColumnName;
}
// rows
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
xlWorkSheet.Cells[(i + 2), (j + 1)] = dt.Rows[i][j];
}
}
int row = xlWorkSheet.UsedRange.Rows.Count;
string address1 = "B1:B" + row.ToString() + ",K1:K" + row.ToString() + ",M1:N" + row.ToString();
string address2 = "B1:B" + row.ToString() + ",L1:L" + row.ToString();
string address3 = "AB41:AC53";
string address4 = "F1:J" + row.ToString();
// チャート１（Ｘ－Ｒ管理図１）
Excel.Range chartRange1;
Excel.ChartObjects xlCharts1 = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart1 = (Excel.ChartObject)xlCharts1.Add(800, 10, 600, 250);
Excel.Chart chartPage1 = myChart1.Chart;
chartRange1 = xlWorkSheet.get_Range(address1);
chartPage1.SetSourceData(chartRange1, misValue);
chartPage1.ChartType = Excel.XlChartType.xlLine;
chartPage1.HasLegend = false;
chartPage1.HasTitle = true;
chartPage1.ChartTitle.Text = "X " + dt.Rows[0]["inspect"].ToString() + " " + dt.Rows[0]["line"].ToString();
Excel.Axis xAxis1 = (Excel.Axis)chartPage1.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
xAxis1.CategoryType = Excel.XlCategoryType.xlCategoryScale;
Excel.SeriesCollection SeriesCollection1 = (Excel.SeriesCollection)myChart1.Chart.SeriesCollection(misValue);
Excel.Series s2 = SeriesCollection1.Item(2);
s2.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbCoral;
Excel.Series s3 = SeriesCollection1.Item(3);
s3.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbCoral;
// チャート２（Ｘ－Ｒ管理図２）
Excel.Range chartRange2;
Excel.ChartObjects xlCharts2 = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart2 = (Excel.ChartObject)xlCharts1.Add(800, 280, 600, 250);
Excel.Chart chartPage2 = myChart2.Chart;
chartRange2 = xlWorkSheet.get_Range(address2);
chartPage2.SetSourceData(chartRange2, misValue);
chartPage2.ChartType = Excel.XlChartType.xlLine;
chartPage2.HasLegend = false;
chartPage2.HasTitle = true;
chartPage2.ChartTitle.Text = "R " + dt.Rows[0]["inspect"].ToString() + " " + dt.Rows[0]["line"].ToString();
Excel.Axis xAxis2 = (Excel.Axis)chartPage2.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
xAxis2.CategoryType = Excel.XlCategoryType.xlCategoryScale;
// チャート３（ヒストグラム）
Excel.Range chartRange3;
Excel.ChartObjects xlCharts3 = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart3 = (Excel.ChartObject)xlCharts1.Add(800, 550, 350, 250);
Excel.Chart chartPage3 = myChart3.Chart;
string[,] formulas = new string[13, 3];
string[] formula1 = new string[]
{
"BIN",
"=MIN(" + address4 + ")",
"=AA42+(AA$53-AA$42)/10",
"=AA43+(AA$53-AA$42)/10",
"=AA44+(AA$53-AA$42)/10",
"=AA45+(AA$53-AA$42)/10",
"=AA46+(AA$53-AA$42)/10",
"=AA47+(AA$53-AA$42)/10",
"=AA48+(AA$53-AA$42)/10",
"=AA49+(AA$53-AA$42)/10",
"=AA50+(AA$53-AA$42)/10",
"=AA51+(AA$53-AA$42)/10",
"=MAX(" + address4 + ")",
};
string[] formula2 = new string[]
{
@"LABEL",
@"=TEXT(AA42,""0.0"")",
@"=TEXT(AA42,""0.0"")&"" - ""&TEXT(AA43,""0.0"")",
@"=TEXT(AA43,""0.0"")&"" - ""&TEXT(AA44,""0.0"")",
@"=TEXT(AA44,""0.0"")&"" - ""&TEXT(AA45,""0.0"")",
@"=TEXT(AA45,""0.0"")&"" - ""&TEXT(AA46,""0.0"")",
@"=TEXT(AA46,""0.0"")&"" - ""&TEXT(AA47,""0.0"")",
@"=TEXT(AA47,""0.0"")&"" - ""&TEXT(AA48,""0.0"")",
@"=TEXT(AA48,""0.0"")&"" - ""&TEXT(AA49,""0.0"")",
@"=TEXT(AA49,""0.0"")&"" - ""&TEXT(AA50,""0.0"")",
@"=TEXT(AA50,""0.0"")&"" - ""&TEXT(AA51,""0.0"")",
@"=TEXT(AA51,""0.0"")&"" - ""&TEXT(AA52,""0.0"")",
@"=TEXT(AA53,""0.0"")"
};
string[] formula3 = new string[]
{
@"FREQUENCY",
@"=COUNTIF(" + address4 + @",""<=""&AA42)",
@"=COUNTIF(" + address4 + @","">""&AA42)-COUNTIF(" + address4 + @","">""&AA43)",
@"=COUNTIF(" + address4 + @","">""&AA43)-COUNTIF(" + address4 + @","">""&AA44)",
@"=COUNTIF(" + address4 + @","">""&AA44)-COUNTIF(" + address4 + @","">""&AA45)",
@"=COUNTIF(" + address4 + @","">""&AA45)-COUNTIF(" + address4 + @","">""&AA46)",
@"=COUNTIF(" + address4 + @","">""&AA46)-COUNTIF(" + address4 + @","">""&AA47)",
@"=COUNTIF(" + address4 + @","">""&AA47)-COUNTIF(" + address4 + @","">""&AA48)",
@"=COUNTIF(" + address4 + @","">""&AA48)-COUNTIF(" + address4 + @","">""&AA49)",
@"=COUNTIF(" + address4 + @","">""&AA49)-COUNTIF(" + address4 + @","">""&AA50)",
@"=COUNTIF(" + address4 + @","">""&AA50)-COUNTIF(" + address4 + @","">""&AA51)",
@"=COUNTIF(" + address4 + @","">""&AA51)-COUNTIF(" + address4 + @","">=""&AA52)",
@"=COUNTIF(" + address4 + @","">=""&AA53)"
};
for (int i = 0; i < 13; i++)
{
xlWorkSheet.Cells[41 + i, 27].Formula = formula1[i];
xlWorkSheet.Cells[41 + i, 28].Formula = formula2[i];
xlWorkSheet.Cells[41 + i, 29].Formula = formula3[i];
}
chartRange3 = xlWorkSheet.get_Range(address3);
chartPage3.SetSourceData(chartRange3, misValue);
chartPage3.ChartType = Excel.XlChartType.xlColumnClustered;
chartPage3.HasLegend = false;
chartPage3.HasTitle = true;
chartPage3.ChartTitle.Text = "Frequency " + dt.Rows[0]["inspect"].ToString() + " " + dt.Rows[0]["line"].ToString();
Excel.ChartGroup ChartGroup1 = (Excel.ChartGroup)myChart3.Chart.ChartGroups(1);
ChartGroup1.GapWidth = 0;
xlApp.Visible = true;
}
private void button1_Click(object sender, EventArgs e)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlApp.DisplayAlerts = false;
//xlApp.Visible = true;
xlWorkBook = xlApp.Workbooks.Open(fileUrl);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
//Left, Top, Width, Height
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(0, 0, 1000, 600);
Excel.Chart chartPage = myChart.Chart;
int lastRow = xlWorkSheet.UsedRange.Rows.Count;
chartRange = xlWorkSheet.get_Range("B1", "D" + lastRow);
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlXYScatterSmoothNoMarkers;
Excel.Axis horizAxis = chartPage.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
horizAxis.MaximumScaleIsAuto = false;
horizAxis.MaximumScale = lastRow;
horizAxis.MinimumScaleIsAuto = false;
horizAxis.MinimumScale = 0;
horizAxis.HasTitle = false;
//horizAxis.AxisTitle.Text = "across the bottom";
Excel.Axis vertAxis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
vertAxis.HasMajorGridlines = false; // change this to whatever you wish
vertAxis.HasTitle = true;
vertAxis.AxisTitle.Text = "Volts";
vertAxis.MaximumScaleIsAuto = true;
//vertAxis.MaximumScale = 4; // you can pick this based on your input
vertAxis.MinimumScaleIsAuto = true;
// vertAxis.MinimumScale = -4;
//chartPage.ApplyChartTemplate("c:\\ctmp.crtx");
chartPage.Export(fileDir + "/Chart1.PNG", "PNG", false);
Image image = Image.FromFile(fileDir + "/Chart1.PNG");
pictureBox1.Image = image;
pictureBox1.SizeMode = PictureBoxSizeMode.StretchImage;
//xlWorkBook.SaveAs(fileUrlNoExt, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlNoChange, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
// データテーブルをエクセルへエクスポート（箱ヒゲ図付き）
public void ExportToExcelWithBoxPlotChart(DataTable dt)
{
if (dt.Rows.Count == 0)
{
return;
}
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
// column headings
for (int i = 0; i < dt.Columns.Count; i++)
{
xlWorkSheet.Cells[1, (i + 1)] = dt.Columns[i].ColumnName;
}
// rows
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
xlWorkSheet.Cells[(i + 2), (j + 1)] = dt.Rows[i][j];
}
}
int row = xlWorkSheet.UsedRange.Rows.Count;
string address1 = "C1:C" + row.ToString() + ",H1:L" + row.ToString();
string address2 = "M2:M" + row.ToString();
string address3 = "N2:N" + row.ToString();
Excel.Range chartRange1;
Excel.Range chartRange2;
Excel.Range chartRange3;
Excel.ChartObjects xlCharts1 = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart1 = (Excel.ChartObject)xlCharts1.Add(800, 10, 600, 250);
Excel.Chart chartPage1 = myChart1.Chart;
chartRange1 = xlWorkSheet.get_Range(address1);
chartRange2 = xlWorkSheet.get_Range(address2);
chartRange3 = xlWorkSheet.get_Range(address3);
chartPage1.SetSourceData(chartRange1, misValue);
chartPage1.ChartType = Excel.XlChartType.xlColumnStacked;
chartPage1.HasTitle = true;
chartPage1.ChartTitle.Text = dt.Rows[0]["inspect"].ToString() + " " + dt.Rows[0]["line"].ToString();
chartPage1.HasLegend = false;
Excel.Axis xAxis1 = (Excel.Axis)chartPage1.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
xAxis1.CategoryType = Excel.XlCategoryType.xlCategoryScale;
Excel.SeriesCollection oSeriesCollection = (Excel.SeriesCollection)myChart1.Chart.SeriesCollection(misValue);
Excel.Series s5 = oSeriesCollection.Item(5);
s5.ChartType = Excel.XlChartType.xlLine;
s5.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbDarkOrchid;
Excel.Series s4 = oSeriesCollection.Item(4);
s4.ChartType = Excel.XlChartType.xlLine;
s4.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbDarkOrchid;
Excel.Series s1 = oSeriesCollection.Item(1);
s1.Format.Fill.ForeColor.RGB = (int)Excel.XlRgbColor.rgbWhite;
s1.Format.Fill.Transparency = 1;
s1.Format.Line.Weight = 0;
s1.HasErrorBars = true;
s1.ErrorBar(Excel.XlErrorBarDirection.xlY, Excel.XlErrorBarInclude.xlErrorBarIncludeMinusValues,
Excel.XlErrorBarType.xlErrorBarTypeCustom, chartRange2, chartRange2);
Excel.Series s3 = oSeriesCollection.Item(3);
s3.Format.Fill.ForeColor.RGB = (int)Excel.XlRgbColor.rgbAqua;
s3.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbBlack;
s3.Format.Line.Weight = 1.0F;
s3.HasErrorBars = true;
s3.ErrorBar(Excel.XlErrorBarDirection.xlY, Excel.XlErrorBarInclude.xlErrorBarIncludePlusValues,
Excel.XlErrorBarType.xlErrorBarTypeCustom, chartRange3, chartRange3);
Excel.Series s2 = oSeriesCollection.Item(2);
s2.Format.Fill.ForeColor.RGB = (int)Excel.XlRgbColor.rgbAqua;
s2.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbBlack;
s2.Format.Line.Weight = 1.0F;
xlApp.Visible = true;
}
private void graph(bool threeD)
{
loadSteps(threeD);
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlApp.DisplayAlerts = false;
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
//add data
int max = (int)((end - start) / interval);
int ymax = (int)((yend - ystart) / yinterval);
int index = 1;
progressBar1.Maximum = max;
for (int i = 1; i <= max; i++)
{
progressBar1.Value = i;
progressBar1.Refresh();
double x = i * interval + start;
for (int j = 1; j <= ymax; j++)
{
double y = j * yinterval + ystart;
string xlEquation = "=";
foreach (char c in T_Equation.Text)
{
Application.DoEvents();
if (c == 'X' || c == 'x')
{
xlEquation += x;
}
else if (threeD && (c == 'Y' || c == 'y'))
{
xlEquation += y;
}
else
{
xlEquation += c;
}
}
++index;
try
{
if (!threeD)
{
xlWorkSheet.Cells[index, 1] = x.ToString();
xlWorkSheet.Cells[index, 2] = xlEquation;
}
else
{
xlWorkSheet.Cells[1, j + 1] = y.ToString();
xlWorkSheet.Cells[i + 1, 1] = x.ToString();
xlWorkSheet.Cells[i + 1, j + 1] = xlEquation;
}
}
catch (Exception ex)
{
MessageBox.Show("Invalid equation.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
}
Excel.Range chartYRange, chartXRange, chartZRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(0, 0, 465, 330);
Excel.Chart chartPage = myChart.Chart;
int last = max * ymax;
chartXRange = xlWorkSheet.get_Range("A1", "A" + last);
chartYRange = xlWorkSheet.get_Range("B1", "B" + last);
if (!threeD)
{
chartPage.SetSourceData(chartYRange, misValue);
}
else
{
try
{
char endC = System.Convert.ToChar(Convert.ToInt32('B') + ymax - 1);
chartZRange = xlWorkSheet.get_Range("A1", endC + "" + max);
chartPage.SetSourceData(chartZRange, misValue);
}
catch (Exception ex) {
MessageBox.Show("Chart y-range too large.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
chartPage.ChartType = (threeD) ? Excel.XlChartType.xlSurface : Excel.XlChartType.xlLine;
chartPage.Legend.Clear();
if (!threeD)
{
chartPage.SeriesCollection(1).XValues = chartXRange;
}
Excel.Axis yaxis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
yaxis.HasTitle = true;
yaxis.AxisTitle.Text = "Y";
Excel.Axis xaxis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
xaxis.HasTitle = true;
xaxis.AxisTitle.Text = "X";
if (threeD)
{
Excel.Axis zAxis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlValue,
Excel.XlAxisGroup.xlPrimary);
zAxis.HasTitle = true;
zAxis.AxisTitle.Text = "Z";
}
//export chart as picture file
if (pictureBox1.Image != null)
{
pictureBox1.Image.Dispose();
pictureBox1.Image = null;
}
chartPage.Export(AppDomain.CurrentDomain.BaseDirectory + "test.bmp", "BMP", misValue);
//load picture to picturebox
pictureBox1.Image = new Bitmap(AppDomain.CurrentDomain.BaseDirectory + "test.bmp");
xlWorkBook.Close(saveXL, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
releaseObject(chartPage);
}
