private static void ReportExcelToImage(String filePath, String[] columnList, String title, ChartType chartType = ChartType.COLUMN)
{
FileInfo file = new FileInfo(filePath);
String directory = "";
if (Constant.ANALYZE_CHART_DIR.Equals(""))
{
directory = file.DirectoryName;
}
else
{
directory = Constant.ANALYZE_CHART_DIR;
}
String fileName = file.Name.Substring(0, file.Name.IndexOf('.'));
String imagePath = directory + @"\" + fileName + "-" + title + ".jpg";
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Open(filePath);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 1000, 850);
Excel.Chart chartPage = myChart.Chart;
//non-empty count of csv file
int count = Convert.ToInt32(xlApp.WorksheetFunction.CountA(xlWorkSheet.get_Range("A:A")));
//non-empty count of header
int headerCount = Convert.ToInt32(xlApp.WorksheetFunction.CountA(xlWorkSheet.Rows[1]));
string chartString = "A1:A" + count;
foreach (var column in columnList)
{
int columnIndex = GetColumnIndex(xlWorkSheet, headerCount, column);
chartString += "," + GetString(columnIndex) + "1:" + GetString(columnIndex) + count;
}
chartRange = xlWorkSheet.get_Range(chartString);
chartPage.SetSourceData(chartRange, misValue);
//chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
chartPage.ChartType = GetChartType(chartType);
chartPage.Axes(Excel.XlAxisType.xlValue);
//export chart as picture file
chartPage.Export(imagePath, "JPG", misValue);
xlWorkBook.Close(false, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
/// <summary>
/// re modify the chart with numbers of row changed
/// </summary>
/// <param name="numRows"></param>
/// <returns></returns>
public bool ModifyChart(int numRows)
{
try
{
object misValue = System.Reflection.Missing.Value;
// assign fomular for each sheet
for (int i = 2; i <= xlWorkBook.Worksheets.Count; i++)// sheet
{
xlWorkSheet = xlWorkBook.Worksheets[i];
// delete old chart
ChartObjects objs = xlWorkSheet.ChartObjects();
int chartCount = objs.Count;
for (int j = 2; j <= chartCount; j++)
{
ChartObject currChartObj = objs.Item(j - 1);
Microsoft.Office.Interop.Excel.Range chartRange;
Microsoft.Office.Interop.Excel.Chart chartPage = currChartObj.Chart;
chartRange = xlWorkSheet.Range[String.Format("A1:A{0},{1}1:{1}{0}", numRows + "", (char)((int)'A' + j - 1))];
chartPage.SetSourceData(chartRange, misValue);
}
}
return(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(false);
}
}
public static void CreateChart(
ref Excel.Worksheet page,
ExcelChartInfo excelCI)
{
try
{
var xlCharts =
page.ChartObjects() as Excel.ChartObjects;
Excel.ChartObject myChart =
xlCharts.Add(excelCI.m_Left, excelCI.m_Top, excelCI.m_Width, excelCI.m_Height);
Excel.Chart chartPage = myChart.Chart;
var chartRange = page.get_Range(excelCI.m_NameLRange, excelCI.m_NameHRange);
var chartRange2 = page.get_Range(excelCI.m_SourceLRange, excelCI.m_SourceHRange);
chartPage.SetSourceData(chartRange);
chartPage.ChartType =
excelCI.m_ChartType; //xlLine || xlColumnClustered
chartPage.ChartWizard(
Source: chartRange2,
Title: excelCI.m_Title,
CategoryTitle: excelCI.m_CategoryTitle,
ValueTitle: excelCI.m_ValueTitle);
}
catch (System.Runtime.InteropServices.COMException ex)
{
MessageBox.Show(ex.Message);
}
}
private void AddBugChart(
ExcelInterop.Worksheet sheet,
int chartStartRow, string chartStartCol, int chartEndRow, string chartEndCol,
string bugDataSource, string chartTitle)
{
ExcelInterop.Range bugChartRange = sheet.Range[sheet.Cells[chartStartRow, chartStartCol], sheet.Cells[chartEndRow, chartEndCol]];
ExcelInterop.ChartObjects charts = sheet.ChartObjects(Type.Missing) as ExcelInterop.ChartObjects;
Utility.AddNativieResource(charts);
ExcelInterop.ChartObject bugChartObject = charts.Add(0, 0, bugChartRange.Width, bugChartRange.Height);
Utility.AddNativieResource(bugChartObject);
ExcelInterop.Chart bugChart = bugChartObject.Chart;//设置图表数据区域。
Utility.AddNativieResource(bugChart);
ExcelInterop.Range datasource = sheet.get_Range(bugDataSource);//不是："B14:B25","F14:F25"
Utility.AddNativieResource(datasource);
bugChart.SetSourceData(datasource);
bugChart.ChartType = ExcelInterop.XlChartType.xlColumnClustered;
//bugChart.ChartWizard(datasource, XlChartType.xlColumnClustered, Type.Missing, XlRowCol.xlColumns, 1, 1, false, chartTitle, "", "", Type.Missing);
bugChart.ApplyDataLabels();//图形上面显示具体的值
//将图表移到数据区域之下。
bugChartObject.Left = Convert.ToDouble(bugChartRange.Left) + 20;
bugChartObject.Top = Convert.ToDouble(bugChartRange.Top) + 20;
bugChartObject.Locked = false;
bugChartObject.Select();
bugChartObject.Activate();
}
protected void CreateGraph(int columnIndex)
{
// Get the alphabetical column index...
string columnAlphabetical = ((char)(columnIndex + 64)).ToString( );
// Activate the test Chart worksheet...
this.testChartWorkSheet.Activate( );
// Get any charts currently existing in the worksheet...
Excel.ChartObjects charts =
(Excel.ChartObjects) this.testChartWorkSheet.ChartObjects(Type.Missing);
// Adds a chart at x = 100, y = 300, 500 points wide and 300 tall.
Excel.ChartObject chartObj = (Excel.ChartObject)charts.Item(columnIndex - 1);
Excel.Chart chart = chartObj.Chart;
// Gets the data cells ( X values )
Excel.Range chartRange = this.testWorkSheet.get_Range((columnAlphabetical + "2"), (columnAlphabetical + (rows).ToString( )));
chart.SetSourceData(chartRange, Type.Missing);
// Set the type of the chart...
chart.ChartType = Excel.XlChartType.xlLine;
// Get any existing series collection (there should be only one)...
Excel.SeriesCollection seriesCollection =
(Excel.SeriesCollection)chart.SeriesCollection(Type.Missing);
Excel.Series series = seriesCollection.Item(seriesCollection.Count);
// Gets the data cells ( Y values )
series.XValues = this.testWorkSheet.get_Range("A2", "A" + (rows).ToString( ));
}
private void buttonGrafik_Click(object sender, EventArgs e)
{
excelOrnek = new EXCEL.Application();
excelKitap = excelOrnek.Workbooks.Add(kayipDeger);
excelSayfa = (EXCEL.Worksheet)excelKitap.Worksheets.get_Item(1);
excelSayfa.Cells[1, 1] = ""; excelSayfa.Cells[1, 2] = "İstanbul"; excelSayfa.Cells[1, 3] = "Ankara";
excelSayfa.Cells[2, 1] = "Ocak"; excelSayfa.Cells[2, 2] = "350"; excelSayfa.Cells[2, 3] = "500";
excelSayfa.Cells[3, 1] = "Şubat"; excelSayfa.Cells[3, 2] = "300"; excelSayfa.Cells[3, 3] = "450";
excelSayfa.Cells[4, 1] = "Mart"; excelSayfa.Cells[4, 2] = "400"; excelSayfa.Cells[4, 3] = "600";
EXCEL.ChartObjects excelGrafikler = (EXCEL.ChartObjects)excelSayfa.ChartObjects(Type.Missing);
EXCEL.ChartObject excelGrafik = (EXCEL.ChartObject)excelGrafikler.Add(10, 80, 300, 250);
EXCEL.Chart grafikSayfa = excelGrafik.Chart;
EXCEL.Range grafikAlan = excelSayfa.get_Range("A1", "C4");
grafikSayfa.SetSourceData(grafikAlan, kayipDeger);
grafikSayfa.ChartType = EXCEL.XlChartType.xlColumnClustered;
excelKitap.SaveAs("C:\\Users\\ufukcelik\\OneDrive\\ornekGrafik.xlsx",
EXCEL.XlFileFormat.xlWorkbookNormal,
kayipDeger, kayipDeger, kayipDeger, kayipDeger,
EXCEL.XlSaveAsAccessMode.xlExclusive,
kayipDeger, kayipDeger, kayipDeger, kayipDeger, kayipDeger);
excelKitap.Close(true, kayipDeger, kayipDeger);
excelOrnek.Quit();
richTextBoxEXCEL.Text = "Excel Grafik Dosyası oluşturuldu";
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelOrnek);
excelOrnek = null;
GC.Collect();
}
private void button3_Click(object sender, EventArgs e)
{
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);
//add data
//xlWorkSheet.Cells[1, 1] = "";
//xlWorkSheet.Cells[1, 2] = "Student1";
//xlWorkSheet.Cells[1, 3] = "Student2";
//xlWorkSheet.Cells[1, 4] = "Student3";
//xlWorkSheet.Cells[2, 1] = "Term1";
//xlWorkSheet.Cells[2, 2] = "80";
//xlWorkSheet.Cells[2, 3] = "65";
//xlWorkSheet.Cells[2, 4] = "45";
//xlWorkSheet.Cells[3, 1] = "Term2";
//xlWorkSheet.Cells[3, 2] = "78";
//xlWorkSheet.Cells[3, 3] = "72";
//xlWorkSheet.Cells[3, 4] = "60";
//xlWorkSheet.Cells[4, 1] = "Term3";
//xlWorkSheet.Cells[4, 2] = "82";
//xlWorkSheet.Cells[4, 3] = "80";
//xlWorkSheet.Cells[4, 4] = "65";
//xlWorkSheet.Cells[5, 1] = "Term4";
//xlWorkSheet.Cells[5, 2] = "75";
//xlWorkSheet.Cells[5, 3] = "82";
//xlWorkSheet.Cells[5, 4] = "68";
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);
Excel.Chart chartPage = myChart.Chart;
chartRange = xlWorkSheet.get_Range("A1", "d5");
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xl3DArea;
xlWorkBook.SaveAs("\\\\dsview.pcoip.ki.agh.edu.pl\\Biblioteki-Pracownicy$\\szsz\\Desktop\\csharp-Excel.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
MessageBox.Show("Excel file created , you can find the file c:\\csharp.net-informations.xls");
}
private void ReportExcel2()
{
Excel.Application app = new Excel.Application();
app.Visible = true;
Excel.Workbook wb = app.Workbooks.Add();
Excel.Worksheet ws = wb.Worksheets[1];
ws.Columns[1].ColumnWidth = 40;
ws.Columns[2].ColumnWidth = 20;
ws.Columns["A:B"].WrapText = true;
Excel.Range rng = ws.Range["A1:B1"];
ws.Cells[1, 1].value = "ОТЧЕТ О ПРОДАЖАХ ЗА " +
dateTimePicker1.Value.ToShortDateString() +
" - " + dateTimePicker2.Value.ToShortDateString();
rng.Font.Bold = true;
rng.Font.Size = 14;
rng.MergeCells = true;
ws.Range["A1:B2"].HorizontalAlignment =
Excel.Constants.xlCenter;
ws.Cells[2, 1].value = "Поставщик";
ws.Cells[2, 2].value = "Сумма продаж";
int i = 3;
report2TableAdapter1.Fill(kondirDataSet1.Report2,
dateTimePicker1.Value, dateTimePicker2.Value);
foreach (KondirDataSet.Report2Row r in kondirDataSet1.Report2)
{
ws.Cells[i, 1].value = r.SupplierName;
ws.Cells[i, 2].value = r.Total;
i++;
}
ws.Cells[i, 1].value = "ИТОГО";
ws.Cells[i, 2].FormulaR1C1 = "=SUM(R[-" + (i - 3) + "]C:R[-1]C)";
rng = ws.Range[ws.Cells[2, 1], ws.Cells[i, 2]];
rng.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle =
Excel.XlLineStyle.xlContinuous;
rng.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle =
Excel.XlLineStyle.xlContinuous;
rng.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle =
Excel.XlLineStyle.xlContinuous;
rng.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle =
Excel.XlLineStyle.xlContinuous;
rng.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle =
Excel.XlLineStyle.xlContinuous;
rng.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle =
Excel.XlLineStyle.xlContinuous;
Excel.Shape sh =
ws.Shapes.AddChart(Excel.XlChartType.xlPie);
sh.Left = 0;
sh.Top = (float)ws.Rows[i + 2].Top;
float scale = (float)ws.Columns[3].Left / sh.Width;
sh.ScaleWidth(scale, MsoTriState.msoFalse,
MsoScaleFrom.msoScaleFromTopLeft);
Excel.Chart ch = sh.Chart;
ch.SetSourceData(ws.Range[ws.Cells[2, 1], ws.Cells[i - 1,
2]]);
ch.SeriesCollection(1).ApplyDataLabels();
}
private void CreateChart(Excel.Worksheet worksheet)
{
Excel.Range usedRange = worksheet.UsedRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)worksheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = xlCharts.Add(300, 80, 600, 350);
Excel.Chart chartPage = myChart.Chart;
chartPage.ChartType = Excel.XlChartType.xlXYScatterLines;
chartPage.SetSourceData(usedRange, System.Reflection.Missing.Value);
}
//Создаем лист книги
static void DisplayInExcel(IEnumerable <Account> accounts)
{
//создаем объект приложения и делаем его видимым
var excelApp = new Excel.Application();
excelApp.Visible = true;
//создаем новую рабочую книгу - она автоматически становится активной
excelApp.Workbooks.Add();
//этот пример использует только одну таблицу
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
//вставляем значения в первые два столбца первой строки (заголовок)
workSheet.Cells[1, "A"] = "ID Number";
workSheet.Cells[1, "B"] = "Current Balance";
//помещаем данные о счетах в таблицу
var row = 1;
foreach (var acct in accounts)
{
row++;
workSheet.Cells[row, "A"] = acct.ID;
workSheet.Cells[row, "B"] = acct.Balance;
}
//Вычисляем сумму по всем счетам
Excel.Range rng = workSheet.Range["B4"]; // указываем ячейку где будет прописанна сумма
rng.Formula = "=СУММ(B2:B3)";
rng.FormulaHidden = false;
//форматирование таблицы - выделяем границы у ячейки суммы
Excel.Borders border = rng.Borders;
border.LineStyle = Excel.XlLineStyle.xlContinuous;
//чтобы ширина столбца изменилась в соответствии с содержимым
//workSheet.Columns.AutoFit();
((Excel.Range)workSheet.Columns[1]).AutoFit();
((Excel.Range)workSheet.Columns[2]).AutoFit();
//строим круговую диаграмму
Excel.ChartObjects chartObjs = (Excel.ChartObjects)workSheet.ChartObjects();
Excel.ChartObject chartObj = chartObjs.Add(50, 100, 300, 300);
Excel.Chart xlChart = chartObj.Chart;
Excel.Range rng2 = workSheet.Range["B1:B3"];
//Устанавливаем тип диаграммы
xlChart.ChartType = Excel.XlChartType.xlColumnClustered;
//устанавливаем источник даных
xlChart.SetSourceData(rng2);
}
private Excel.Chart MakeColumnChart(Excel.Worksheet xlWorkSheet)
{
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = xlCharts.Add(10, 80, 300, 250);
Excel.Chart chartPage = myChart.Chart;
var chartRange = xlWorkSheet.Range["A1", "d5"];
chartPage.SetSourceData(chartRange, _misValue);
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
return(chartPage);
}
private void GenerateChart()
{
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", "AA2000");
// selectedRange = (Excel.Range)(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Selection;
selectedRange = IdentifyPivotRanges(rangePivot);
shapes = activeSheet.Shapes;
shapes.AddChart2(Style: 201, XlChartType: Excel.XlChartType.xlColumnClustered,
Left: 480, Top: 190, Width: 450,
Height: Type.Missing, NewLayout: true).Select();
chart = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveChart;
chart.HasTitle = false;
// chart.ChartTitle.Text = "Threats History";
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.HasLegend = true;
chart.SetSourceData(selectedRange);
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A2", "A2").Select();
}
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);
}
}
}
public void Saveto_Excel_Click()
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
// xlApp.Visible = true;
//xlApp.UserControl = true;
System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US");
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
//add data
xlWorkSheet.Cells[2, 1] = user.LastName;
xlWorkSheet.Cells[3, 1] = "Total Points";
for (int i = 1; i <= Name_Stage.Count; i++)
{
xlWorkSheet.Cells[1, i + 1] = Name_Stage[i - 1];
}
for (int j = 1; j <= Student_Stage_Count.Count; j++)
{
xlWorkSheet.Cells[2, j + 1] = Student_Stage_Count[j - 1].ToString();
}
for (int j = 1; j <= Total_Stage_Count.Count; j++)
{
xlWorkSheet.Cells[3, j + 1] = Total_Stage_Count[j - 1].ToString();
}
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);
Excel.Chart chartPage = myChart.Chart;
chartRange = xlWorkSheet.get_Range("g3", "a1");
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
string temp_name = "Statistic for studet " + user.LastName + " for curriculum " + curriculum.Name + ".xls";
xlWorkBook.SaveAs(temp_name, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
}
/// <summary>
/// Writes an Excel worksheet containing a Respiratory Signal's values and Plot
/// </summary>
/// <param name="ws"> The Excel worksheet object </param>
/// <param name="SignalName"> The name of the respiratory signal </param>
/// <param name="table"> The respiratory signal values and peak/onset locations </param>
/// <param name="ROWS"> The number of rows in the table </param>
/// <param name="COLUMNS"> The number of columns in the table </param>
public static void AddRespiratorySignalToWorksheet(Excel.Worksheet ws, string SignalName, object[,] table, int ROWS, int COLUMNS)
{
// Make Table with Values
Excel.Range range = ws.Range[ws.Cells[3, 2], ws.Cells[3 + ROWS - 1, 2 + COLUMNS - 1]];
range.Value = table;
ws.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, range, System.Reflection.Missing.Value, Excel.XlYesNoGuess.xlGuess, System.Reflection.Missing.Value).Name = ws.Name;
ws.ListObjects[ws.Name].TableStyle = "TableStyleLight9";
ws.Columns["A:I"].ColumnWidth = 20;
ws.Columns["E:H"].Hidden = true;
ws.Columns["B:H"].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
// Add Conditional Formatting
Excel.Range range2 = ws.Range[ws.Cells[4, 2], ws.Cells[3 + ROWS - 1, 2 + COLUMNS - 1]];
range2.FormatConditions.Add(Excel.XlFormatConditionType.xlExpression, System.Reflection.Missing.Value, "=NOT(ISBLANK($E4))", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
range2.FormatConditions.Add(Excel.XlFormatConditionType.xlExpression, System.Reflection.Missing.Value, "=NOT(ISBLANK($F4))", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
range2.FormatConditions.Add(Excel.XlFormatConditionType.xlExpression, System.Reflection.Missing.Value, "=NOT(ISBLANK($G4))", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
range2.FormatConditions.Add(Excel.XlFormatConditionType.xlExpression, System.Reflection.Missing.Value, "=NOT(ISBLANK($H4))", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
range2.FormatConditions[1].Interior.Color = 5296274;
range2.FormatConditions[2].Interior.Color = 255;
range2.FormatConditions[3].Interior.Color = 65535;
range2.FormatConditions[4].Interior.Color = 15773696;
range2.Columns[2].NumberFormat = "m/d/yyyy h:mm:ss.000";
// Add Chart
Excel.Chart chart = ((Excel.ChartObject)((Excel.ChartObjects)ws.ChartObjects()).Add(500, 100, 900, 500)).Chart;
chart.SetSourceData(range.Columns["B:G"]);
chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlXYScatterLines;
chart.ChartWizard(Source: range.Columns["B:G"], Title: SignalName, CategoryTitle: "Time", ValueTitle: SignalName);
chart.PlotVisibleOnly = false;
((Excel.Series)chart.SeriesCollection(1)).ChartType = Excel.XlChartType.xlXYScatterLinesNoMarkers;
((Excel.Series)chart.SeriesCollection(2)).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleSquare;
((Excel.Series)chart.SeriesCollection(3)).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleSquare;
((Excel.Series)chart.SeriesCollection(4)).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleSquare;
((Excel.Series)chart.SeriesCollection(5)).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleSquare;
((Excel.Series)chart.SeriesCollection(2)).Format.Fill.ForeColor.RGB = 5296274;
((Excel.Series)chart.SeriesCollection(3)).Format.Fill.ForeColor.RGB = 255;
((Excel.Series)chart.SeriesCollection(4)).Format.Fill.ForeColor.RGB = 65535;
((Excel.Series)chart.SeriesCollection(5)).Format.Fill.ForeColor.RGB = 15773696;
((Excel.Series)chart.SeriesCollection(1)).Format.Line.ForeColor.RGB = 38450;
((Excel.Series)chart.SeriesCollection(2)).Format.Line.ForeColor.RGB = 5296274;
((Excel.Series)chart.SeriesCollection(3)).Format.Line.ForeColor.RGB = 255;
((Excel.Series)chart.SeriesCollection(4)).Format.Line.ForeColor.RGB = 65535;
((Excel.Series)chart.SeriesCollection(5)).Format.Line.ForeColor.RGB = 15773696;
System.Runtime.InteropServices.Marshal.ReleaseComObject(chart);
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
System.Runtime.InteropServices.Marshal.ReleaseComObject(range2);
}
public void Create(List <ExcelValues> dict)
{
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);
xlWorkSheet.Cells[1, 1] = "Time";
xlWorkSheet.Cells[1, 2] = "Value";
for (int i = 0; i < dict.Count; i++)
{
xlWorkSheet.Cells[i + 2, 1] = string.Format("{0}:{1}:{2}", dict[i].time.Hours, dict[i].time.Minutes, dict[i].time.Seconds);
xlWorkSheet.Cells[i + 2, 2] = dict[i].value;
}
//xlWorkSheet.Cells[2, 1] = "00:0:0";
//xlWorkSheet.Cells[2, 2] = "25.8";
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);
Excel.Chart chartPage = myChart.Chart;
string c = (dict.Count + 1).ToString();
chartRange = xlWorkSheet.get_Range("A1", "b" + c);
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlLine;
string f = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location);
try
{
xlWorkBook.SaveAs(System.IO.Path.Combine(f, @"informations.xls"), Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
public void SetChartData(int dataCol, int legCol, int rowStart, int rowEnd, Excel.XlChartType chartType, bool showLegend)
{
Excel.Worksheet wss = (Excel.Worksheet)ewb.Sheets["Graph"];
Excel.ChartObjects cos = (Excel.ChartObjects)wss.ChartObjects(Type.Missing);
Excel.ChartObject co = (Excel.ChartObject)cos.Item("Диаграмма 1");
Excel.Chart ch = (Excel.Chart)co.Chart;
ch.HasLegend = showLegend;
ch.ChartType = chartType;
ch.SetSourceData(ewsh.Range[ewsh.Cells[rowStart, legCol], ewsh.Cells[rowEnd, dataCol]], Type.Missing);
ReleaseObject(ch);
ReleaseObject(co);
ReleaseObject(cos);
ReleaseObject(wss);
}
public static void PieChartReport(int pass, int fail, int notRun)
{
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);
int totalNumberOfTestcase = pass + fail + notRun;
//add data
xlWorkSheet.Cells[1, 1] = "Status";
xlWorkSheet.Cells[1, 2] = "Result";
xlWorkSheet.Cells[2, 1] = "Pass";
xlWorkSheet.Cells[2, 2] = pass;
xlWorkSheet.Cells[3, 1] = "Fail";
xlWorkSheet.Cells[3, 2] = fail;
xlWorkSheet.Cells[4, 1] = "NotRun";
xlWorkSheet.Cells[4, 2] = notRun;
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);
Excel.Chart chartPage = myChart.Chart;
chartRange = xlWorkSheet.get_Range("A1", "B4");
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xl3DPie;
xlWorkBook.SaveAs(ParallelSelenium.Parallel_Quest.targetdir + "\\" + "Rrsult_Piechart.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);;
releaseObject(xlApp);
}
internal static void Export(DataGridView dataTable)
{
_Application excelApp = new _Excel.Application();
Workbook wb;
wb = excelApp.Workbooks.Add();
Worksheet ws = wb.Worksheets[1];
double[] arrOfX = new double[dataTable.Rows.Count - 1];
double[] arrOfY = new double[dataTable.Rows.Count - 1];
GetValues.FillArrays(arrOfX, arrOfY, dataTable);
excelApp.Visible = true;
excelApp.Cells[1, 1] = "X:";
excelApp.Cells[1, 2] = "Y:";
int row = 3;
int column = 1; //начинать заполнение необходимо с 3 строчки, т к при построении графика левая верхняя клеточка должна быть пустой
for (int i = 0; i < arrOfX.Length; i++)
{
excelApp.Cells[row, column] = arrOfX[i];
column++;
excelApp.Cells[row, column] = arrOfY[i];
column--;
row++;
}
_Excel.Range eRange = ws.get_Range("a2", "b" + row);
_Excel.ChartObjects eChartObjects = (_Excel.ChartObjects)ws.ChartObjects(Type.Missing);
_Excel.ChartObject eChartObj = eChartObjects.Add(10, 30, 300, 300); //размеры диаграммы
_Excel.Chart eChart = eChartObj.Chart;
eChart.ChartType = _Excel.XlChartType.xlLine;
eChart.ChartWizard(
Source: eRange,
Title: "Cassini Oval",
CategoryTitle: "xAxis",
CategoryLabels: 2,
ValueTitle: "yAxis",
HasLegend: false);
eChart.SetSourceData(eRange);
}
public void createTempChart()
{
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);
xlWorkSheet.Cells[1, 2] = "a";
xlWorkSheet.Cells[1, 3] = "b";
Excel.Application xlApp1 = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp1.Workbooks.Open
(@"E:\temp.csv");
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet
.ChartObjects(Type.Missing);
Excel.ChartObject myChart =
(Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);
Excel.Chart chartPage = myChart.Chart;
chartRange = xlWorkSheet.get_Range("A1", "C6");
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
// Export chart as picture file
chartPage.Export(@"E:\temp_data.bmp", "BMP",
misValue);
xlWorkBook.SaveAs("temp.csv",
Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue,
misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive,
misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
DeallocateObject(xlWorkSheet);
DeallocateObject(xlWorkBook);
DeallocateObject(xlApp);
DeallocateObject(xlApp1);
}
private void button1_Click(object sender, EventArgs e)
{
if (cht1.Series[0].Points.Count > 1)
{
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);
for (int i = 0; i < cht1.Series.Count; i++)
{
xlWorkSheet.Cells[1, 1] = "";
xlWorkSheet.Cells[1, 2] = "DateTime"; //put your column heading here
xlWorkSheet.Cells[1, 3] = "Data"; // put your column heading here
for (int j = 0; j < cht1.Series[i].Points.Count; j++)
{
xlWorkSheet.Cells[j + 2, 2] = cht1.Series[i].Points[j].XValue;
xlWorkSheet.Cells[j + 2, 3] = cht1.Series[i].Points[j].YValues[0];
}
}
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(150, 10, 800, 300);
Excel.Chart chartPage = myChart.Chart;
chartRange = xlWorkSheet.get_Range("c2", "c100");//update the range here
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
xlWorkBook.SaveAs("grafico.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
MessageBox.Show("Arquivo excel grafico.xls criado na pasta documentos");
}
}
public static void CreateChart(string filename)
{
Excel.Application excelApplication = new Excel.Application();
excelApplication.Visible = true;
//Opens the excel file
Excel.Workbook excelWorkbook = excelApplication.Workbooks.Add();
Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelWorkbook.Worksheets.get_Item(1);
excelWorksheet.Cells[1, 1] = "Product ID";
excelWorksheet.Cells[1, 2] = "Stock";
excelWorksheet.Cells[2, 1] = "BB12";
excelWorksheet.Cells[2, 2] = "15";
excelWorksheet.Cells[3, 1] = "xy40";
excelWorksheet.Cells[3, 2] = "18";
excelWorksheet.Cells[4, 1] = "AX50";
excelWorksheet.Cells[4, 2] = "23";
//Add a char object
Excel.Chart myChart = null;
Excel.ChartObjects charts = excelWorksheet.ChartObjects();
Excel.ChartObject chartObj = charts.Add(50, 50, 300, 300); //left; top; width; height
myChart = chartObj.Chart;
//set chart range -- cell values to be used in the graph
Excel.Range myRange = excelWorksheet.get_Range("B1:B4");
myChart.SetSourceData(myRange);
//chart properties using the named properties and default parameters functionality in
//the .Net Framework
myChart.ChartType = Excel.XlChartType.xlLine;
myChart.ChartWizard(Source: myRange,
Title: "Graph Title",
CategoryTitle: "Title of X axis... ",
ValueTitle: "Title of Y axis... ");
excelWorkbook.SaveAs(filename);
excelWorkbook.Close();
excelApplication.Quit();
ReleaseCOMObjects(excelWorksheet);
ReleaseCOMObjects(excelWorkbook);
ReleaseCOMObjects(excelApplication);
}
/// <summary>
/// Writes an Excel worksheet containing a signal's values and Plot
/// </summary>
/// <param name="ws"> The Excel worksheet object </param>
/// <param name="SignalName"> The name of the signal </param>
/// <param name="table"> The signal values </param>
/// <param name="ROWS"> The number of rows in the table </param>
/// <param name="COLUMNS"> The number of columns in the table </param>
/// <param name="color"> The color of the plot </param>
public static void AddSignalToWorksheet(Excel.Worksheet ws, string SignalName, object[,] table, int ROWS, int COLUMNS, OxyColor color)
{
Excel.Range range = ws.Range[ws.Cells[3, 2], ws.Cells[3 + ROWS - 1, 2 + COLUMNS - 1]];
range.Value = table;
ws.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, range, System.Reflection.Missing.Value, Excel.XlYesNoGuess.xlGuess, System.Reflection.Missing.Value).Name = ws.Name;
ws.ListObjects[ws.Name].TableStyle = "TableStyleLight9";
ws.Columns["A:I"].ColumnWidth = 20;
ws.Columns["B:H"].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.Columns[2].NumberFormat = "m/d/yyyy h:mm:ss.000";
Excel.Chart chart = ((Excel.ChartObject)((Excel.ChartObjects)ws.ChartObjects()).Add(500, 100, 900, 500)).Chart;
chart.SetSourceData(range.Columns["B:C"]);
chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlXYScatterLines;
chart.ChartWizard(Source: range.Columns["B:C"], Title: SignalName, CategoryTitle: "Time", ValueTitle: SignalName);
((Excel.Series)chart.SeriesCollection(1)).ChartType = Excel.XlChartType.xlXYScatterLinesNoMarkers;
((Excel.Series)chart.SeriesCollection(1)).Format.Line.ForeColor.RGB = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(color.A, color.R, color.G, color.B));
System.Runtime.InteropServices.Marshal.ReleaseComObject(chart);
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
}
private Excel.Chart MakePolarChart(Excel.Worksheet xlWorkSheet)
{
xlWorkSheet.Range["A1", "A5"].Value2 = 22;
xlWorkSheet.Range["B1", "B5"].Value2 = 55;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = xlCharts.Add(10, 80, 300, 250);
Excel.Chart chartPage = myChart.Chart;
chartPage.SetSourceData(xlWorkSheet.Range["A1", "B5"],
Excel.XlRowCol.xlColumns);
chartPage.ChartType = Excel.XlChartType.xlRadar;
Excel.ChartGroup group =
(Excel.ChartGroup)chartPage.RadarGroups(1);
group.HasRadarAxisLabels = true;
return(chartPage);
}
static void Main(string[] args)
{
// Создаём экземпляр нашего приложения
Excel.Application excelApp = new Excel.Application();
// Создаём экземпляр рабочий книги Excel
Excel.Workbook workBook;
// Создаём экземпляр листа Excel
Excel.Worksheet workSheet;
workBook = excelApp.Workbooks.Add();
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(1);
// Заполняем первую строку числами от 1 до 10
for (int j = 1; j <= 10; j++)
{
workSheet.Cells[1, j] = j;
}
// Вычисляем сумму этих чисел
Excel.Range rng = workSheet.Range["A2"];
rng.Formula = "=SUM(A1:L1)";
rng.FormulaHidden = false;
// Выделяем границы у этой ячейки
Excel.Borders border = rng.Borders;
border.LineStyle = Excel.XlLineStyle.xlContinuous;
// Строим круговую диаграмму
Excel.ChartObjects chartObjs = (Excel.ChartObjects)workSheet.ChartObjects();
Excel.ChartObject chartObj = chartObjs.Add(5, 50, 300, 300);
Excel.Chart xlChart = chartObj.Chart;
Excel.Range rng2 = workSheet.Range["A1:L1"];
// Устанавливаем тип диаграммы
xlChart.ChartType = Excel.XlChartType.xlPie;
// Устанавливаем источник данных (значения от 1 до 10)
xlChart.SetSourceData(rng2);
// Открываем созданный excel-файл
excelApp.Visible = true;
excelApp.UserControl = true;
}
/// <summary>
/// создание Excel отчета о наиболее/наименее продаваемом букете
/// </summary>
/// <param name="pie_values"></param>
public void Create_Excel_Doc(List <KeyValuePair <string, int> > pie_values)
{
if (pie_values != null)
{
Excel.Application exapp = new Excel.Application();
exapp.SheetsInNewWorkbook = 1;
exapp.Workbooks.Add(Type.Missing);
exapp.DisplayAlerts = true;
exapp.Visible = true;
Excel.Workbooks exappworkbooks = exapp.Workbooks;
Excel.Workbook exappworkbook = exappworkbooks[1];
exappworkbook.Saved = false;
Excel.Sheets excellsheets = exappworkbook.Worksheets;
Excel.Worksheet excellworksheet = (Excel.Worksheet)excellsheets.get_Item(1);
excellworksheet.Activate();
Excel.Range excelcells;
exapp.Cells[1, 1] = "Букет";
exapp.Cells[1, 2] = "Количество";
exapp.Cells[2, 1] = pie_values[0].Key;
exapp.Cells[2, 2] = pie_values[0].Value;
exapp.Cells[3, 1] = pie_values[1].Key;
exapp.Cells[3, 2] = pie_values[1].Value;
Excel.ChartObjects chartsobjrcts = (Excel.ChartObjects)excellworksheet.ChartObjects(Type.Missing);
Excel.ChartObject chartsobj = chartsobjrcts.Add(10, 200, 500, 300);
Excel.Chart excelchart = chartsobj.Chart;
excelcells = excellworksheet.get_Range("A1", "B3");
excelchart.SetSourceData(excelcells, Type.Missing);
excelchart.ChartType = Excel.XlChartType.xlPie;
excelchart.HasTitle = true;
excelchart.ChartTitle.Text = "Наиболее/наименее продаваемый букет";
}
else
{
System.Windows.MessageBox.Show("нет данных для отчета");
}
}
/// <summary>
/// Создать в Excel таблицу, по которой можно построить 3D поверхность
/// </summary>
/// <param name="f">Функция двух переменных</param>
/// <param name="x0">Начало отрезка по первому аргументу</param>
/// <param name="X">Конец отрезка по первому аргументу</param>
/// <param name="xcount">Число шагов по первому аргументу</param>
/// <param name="y0">Начало отрезка по второму аргументу</param>
/// <param name="Y">Конец отрезка по второму аргументу</param>
/// <param name="ycount">Число шагов по второму аргументу</param>
public static void CreatTableInExcel(DRealFunc f, double x0, double X, int xcount, double y0, double Y, int ycount)
{
Application application = new Application();
application.Workbooks.Add(Type.Missing);
Worksheet sheet = (Worksheet)application.Sheets[1];
var t = Type.Missing;
double hx = (X - x0) / (xcount - 1), hy = (Y - y0) / (ycount - 1);
for (int i = 0; i < ycount; i++)
{
sheet.Range[CharForExcel('B', i) + "2", t].Value2 = y0 + i * hy;
}
for (int i = 0; i < xcount; i++)
{
sheet.Range["A" + (3 + i).ToString(), t].Value2 = x0 + i * hx;
}
//for (int j = 0; j < xcount; j++)
Parallel.For(0, xcount, (int j) =>
{
for (int i = 0; i < ycount; i++)
{
sheet.Range[CharForExcel('B', i) + (3 + j).ToString(), t].Value2 = f(sheet.Range["A" + (3 + j).ToString(), t].Value2, sheet.Range[CharForExcel('B', i) + "2", t].Value2);
}
});
sheet.Range["A2", CharForExcel('B', ycount) + (3 + xcount).ToString()].Activate();
Excel.Chart chart = sheet.ChartObjects().Add(10, 10, 500, 500).Chart;
chart.ChartType = Excel.XlChartType.xl3DArea;
chart.SetSourceData(sheet.Range["A2", CharForExcel('B', ycount) + (3 + xcount).ToString()]);
application.Visible = true;
}
private void button3_Click(object sender, EventArgs e) //Refresh/ get charts
{
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);
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);
Excel.Chart chartPage = myChart.Chart;
chartRange = xlWorkSheet.get_Range("A1", "d5");
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
//export chart as picture file
chartPage.Export(@"C:\Dumpster\testcsv.bmp", "BMP", misValue);
// pictureBox1.Image = new Bitmap(@"C:\Dumpster\testcsv.bmp");
xlWorkBook.SaveAs("csharp.net-informations.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
MessageBox.Show("Excel file created , you can find the file c:\\csharp-Excel.xls");
}
private void EditChart(Excel.Chart chart, int index)
{
chart.ChartType = Excel.XlChartType.xlColumnClustered;
var range1 = shAuswertung1.Range[shAuswertung1.Cells[1, 1], shAuswertung1.Cells[index, 1]];
var range2 = shAuswertung1.Range[shAuswertung1.Cells[1, 5], shAuswertung1.Cells[index, 5]];
var range3 = shAuswertung1.Range[shAuswertung1.Cells[1, 9], shAuswertung1.Cells[index, 9]];
var range4 = shAuswertung1.Range[shAuswertung1.Cells[1, 10], shAuswertung1.Cells[index, 10]];
chart.SetSourceData(Source: Globals.ThisAddIn.Application.Union(range1, range2, range3, range4));
chart.Legend.Position = Excel.XlLegendPosition.xlLegendPositionBottom;
chart.Legend.Font.Size = 8;
chart.Legend.Font.Name = "Arial";
chart.SeriesCollection(1).Format.Fill.ForeColor.RGB = Color.FromArgb(192, 0, 0);
chart.SeriesCollection(2).Format.Fill.ForeColor.RGB = Color.FromArgb(0, 112, 192);
chart.SeriesCollection(3).Format.Fill.ForeColor.RGB = Color.FromArgb(255, 140, 0);
chart.Axes(Excel.XlAxisType.xlCategory).CategoryType = Excel.XlCategoryType.xlCategoryScale;
chart.Axes(Excel.XlAxisType.xlCategory).TickLabels.Font.Size = 8;
chart.Axes(Excel.XlAxisType.xlCategory).TickLabels.Font.Name = "Arial";
chart.Axes(Excel.XlAxisType.xlCategory).TickLabels.Orientation = 90;
chart.Axes(Excel.XlAxisType.xlValue).TickLabels.Font.Size = 8;
chart.Axes(Excel.XlAxisType.xlValue).TickLabels.Font.Name = "Arial";
chart.Axes(Excel.XlAxisType.xlValue).MinimumScale = 0;
}
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");
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 CreateChart()
{
chart = oWS.ChartObjects().Add(0,0,800,500).Chart;
chart.SetSourceData(oWS.UsedRange);
chart.ChartType = Excel.XlChartType.xlColumnClustered;
chart.ChartStyle = 17;
foreach (Excel.ChartGroup group in chart.ChartGroups())
{
group.GapWidth = 0;
group.Overlap = 0;
}
}
private void отчетToolStripMenuItem_Click(object sender, EventArgs e)
{
SelectDateRange srd = new SelectDateRange();
srd.dateTimePicker1.Value = (DateTime)db.Invoices.First().sale_date;
DialogResult dr = srd.ShowDialog(this);
DateTime startDate = srd.dateTimePicker1.Value;
DateTime endDate = srd.dateTimePicker2.Value;
List <Invoice> invoices = db.Invoices.Where(i => i.sale_date.Value >= startDate && i.sale_date.Value <= endDate).OrderByDescending(i => i.Product.name).ToList();
//string[] head = { "Код", "Название товара", "Цена за единицу", "Заказано КГ.", "Дата продажи", "Общая стоимость" };
if ((dr == DialogResult.OK) && (0 < invoices.Count))
{
// Load Excel application
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
// Create empty workbook
excel.Workbooks.Add();
// Create Worksheet from active sheet
Microsoft.Office.Interop.Excel._Worksheet workSheet = excel.ActiveSheet;
try
{
workSheet.Cells[1, "A"] = "Код накладной";
workSheet.Cells[1, "B"] = "Название товара";
workSheet.Cells[1, "C"] = "Цена за единицу";
workSheet.Cells[1, "D"] = "Заказано (КГ.)";
workSheet.Cells[1, "E"] = "Дата покупки";
workSheet.Cells[1, "F"] = "Сумма за покупку";
workSheet.Cells[1, "H"] = "Название товара";
workSheet.Cells[1, "I"] = "Количество заказанного товара";
workSheet.Cells[1, "K"] = "Нужно закупить больше:";
workSheet.Cells[1, "L"] = "Нужно закупить меньше:";
int row = 2;
ArrayList nameArray = new ArrayList();
ArrayList sumArray = new ArrayList();
int sum;
string name;
foreach (Invoice invoice in invoices)
{
workSheet.Cells[row, "A"] = invoice.invoice_code;
workSheet.Cells[row, "B"] = invoice.Product.name;
workSheet.Cells[row, "C"] = string.Format("{0} грн.", invoice.Product.price);
workSheet.Cells[row, "D"] = string.Format("{0} кг.", invoice.quantity);
workSheet.Cells[row, "E"] = invoice.sale_date.Value.Date;
workSheet.Cells[row, "F"] = string.Format("{0} грн.", invoice.price);
name = db.Invoices.Where(nn => nn.product_id == invoice.Product.id && nn.sale_date.Value >= startDate && nn.sale_date.Value <= endDate).First().Product.name.ToString();
sum = (int)db.Invoices.Where(i => i.Product.name == invoice.Product.name && i.sale_date.Value >= startDate && i.sale_date.Value <= endDate).Sum(s => s.quantity);
if (!nameArray.Contains(name))
{
nameArray.Add(name);
}
if (!sumArray.Contains(sum))
{
sumArray.Add(sum);
}
row++;
}
row = 2;
foreach (string nameRow in nameArray)
{
workSheet.Cells[row, "H"] = nameRow;
row++;
}
row = 2;
foreach (int sumRow in sumArray)
{
workSheet.Cells[row, "I"] = sumRow;
row++;
}
Excel.ChartObjects chartObjs = (Excel.ChartObjects)workSheet.ChartObjects(Type.Missing);
Excel.ChartObject chartObj = chartObjs.Add(100, 20, 150, 200);
Excel.Chart xlChart = chartObj.Chart;
Excel.Range rg = workSheet.get_Range("H2:H" + (row - 1).ToString(), "I2:I" + (row - 1).ToString());
xlChart.ChartType = Excel.XlChartType.xlPieExploded;
xlChart.SetSourceData(rg, Type.Missing);
//rg.FormulaLocal = "MAX(I2:I"+(row-1).ToString();
//workSheet.Cells[2, "M"].Value = "=MAX(I2:I" + (row - 1).ToString();
//workSheet.Cells[2, "N"].Value = "=MIN(I2:I" + (row - 1).ToString();
//for (int i = 2; i < row; i++)
// {
// workSheet.Cells[i, "K"].Value = "=ЕСЛИ(M2=I" + i.ToString() + ";H" + i.ToString() + ";0)";
// }
//for (int i = 2; i < row; i++)
// {
// workSheet.Cells[i, "L"].Value = "=ЕСЛИ(N2=I" + i.ToString() + ";H" + i.ToString() + ";0)";
// }
// rg.FormulaLocal = "MAX(I2:I"+(row-1).ToString();
//rg.FormulaLocal = "MIN(I2:I"+(row-1).ToString();
// Apply some predefined styles for data to look nicely :)
workSheet.Range["A1"].AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic1);
workSheet.Range["H1"].AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic1);
workSheet.Range["K1"].AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic1);
// Define filename
string fileName = string.Format(@"{0}\ExcelData.xlsx", Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory));
// Save this data as a file
workSheet.SaveAs(fileName);
// Display SUCCESS message
MessageBox.Show(string.Format("Файл '{0}' успешно сохранен!", fileName));
}
catch (Exception exception)
{
MessageBox.Show("Exception",
"Ошибка записи файла!\n" + exception.Message,
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
// Quit Excel application
excel.Quit();
//excel.Workbooks.Open(Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory));
// Release COM objects (very important!)
if (excel != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
}
if (workSheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
}
// Empty variables
excel = null;
workSheet = null;
// Force garbage collector cleaning
GC.Collect();
}
}
else
{
MessageBox.Show("В выбраном диапазоне, нет накладных!");
}
if (dr == DialogResult.Cancel)
{
return;
}
}
