How to generate a chart from an Excel sheet?
41,784
Take a look at the tutorial here (one of the first Google hits).
It very clearly describe how to make a simple chart in Excel from C# code.
The general idea is like this:
// Add chart.
var charts = worksheet.ChartObjects() as
Microsoft.Office.Interop.Excel.ChartObjects;
var chartObject = charts.Add(60, 10, 300, 300) as
Microsoft.Office.Interop.Excel.ChartObject;
var chart = chartObject.Chart;
// Set chart range.
var range = worksheet.get_Range(topLeft, bottomRight);
chart.SetSourceData(range);
// Set chart properties.
chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlLine;
chart.ChartWizard(Source: range,
Title: graphTitle,
CategoryTitle: xAxis,
ValueTitle: yAxis);
Author by
user3467827
Updated on April 21, 2020Comments
-
user3467827 almost 4 years
I can generate an Excel file when I pass a
datatable
into the below function:public static void ExportDataTableToExcel(DataTable dt, string filepath) { object missing = Type.Missing; object misValue = System.Reflection.Missing.Value; //create excel Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); //add excel workbook Microsoft.Office.Interop.Excel.Workbook wb = excel.Workbooks.Add(); //add worksheet to workbook Microsoft.Office.Interop.Excel.Worksheet ws = wb.Sheets[1] as Microsoft.Office.Interop.Excel.Worksheet; //add 2nd worksheet to workbook Microsoft.Office.Interop.Excel.Worksheet ws2 = wb.Sheets[2] as Microsoft.Office.Interop.Excel.Worksheet; //Set the header-row bold ws.Range["A1", "A1"].EntireRow.Font.Bold = true; //Adjust all columns ws.Columns.AutoFit(); //spit top row ws.Application.ActiveWindow.SplitRow = 1; //insert image into worsheet 2 ws2.Shapes.AddPicture("C:\\Koala.JPG", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 50, 50, 300, 300); //freeze top row ws.Application.ActiveWindow.FreezePanes = true; int rowCount = 1; foreach (DataRow dr in dt.Rows) { rowCount += 1; for (int i = 1; i < dt.Columns.Count + 1; i++) { // Add the header the first time through if (rowCount == 2) { ws.Cells[1, i] = dt.Columns[i - 1].ColumnName; ws.Cells[1, i].Interior.ColorIndex = 40; // add cell border ws.Cells[1, i].Borders.LineStyle = Excel.XlLineStyle.xlContinuous; } ws.Cells[rowCount, i] = dr[i - 1].ToString(); // add cell border ws.Cells[rowCount, i].Borders.LineStyle = Excel.XlLineStyle.xlContinuous; } } Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)ws.UsedRange; Console.Write(range.ToString()); wb.SaveAs(@"C:\Test.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); wb.Close(missing, missing, missing); excel.Quit(); }
This function works well. I need to add a graph from C# code to this Excel file. I tried a few methods, but did not find proper way to implement.
-
user3467827 about 10 yearsThanks it is working well. I'm little bit new to c# and interop.
-
Baldrick about 10 years@user3467827: Glad to help.
-
Michael Mormon about 7 years@Baldrick - appreciate the syntax added, this is now a dead link
-
Baldrick about 7 years@MichaelMormon: Thanks, I've updated the link to one that has virtually the same content, but is still alive.