Create Excel chart using C# winforms application
For Create chart using C# .net dynamically just
Follow 6 stepsStep 1 : Create Worksheet,WorkBook,Worksheet object
private void btnEXLChart_Click(object sender, EventArgs e)
{
Excel.Application xlApp = null;
Excel.Workbook xlWorkBook = null;
Excel.Worksheet xlWorkSheet = null;
}
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Name = "Name of sheet";
Excel.Range chartRange;
Step 2 : Set TITLE for Chart
xlWorkSheet.get_Range("C2", "G2").Merge(false);
chartRange = xlWorkSheet.get_Range("C2", "G2");
chartRange.Borders.Color = System.Drawing.Color.Black.ToArgb();
chartRange.FormulaR1C1 = "Title";
Step 3 : Basic Information
chartRange.Font.Bold = true;
chartRange.Font.Size = 20;
chartRange = xlWorkSheet.get_Range("A4", "C6");
xlWorkSheet.Cells[4, 1] = "Customer ID - " + _owner.lbCustomerId.Text;
xlWorkSheet.Cells[5, 1] = "S/N - " + _owner.lbNo.Text;
xlWorkSheet.Cells[6, 1] = "DT. - " + _owner.lbDateTime.Text;
chartRange.Borders.Color = System.Drawing.Color.Black.ToArgb();
Step 4 : Getting Data for filling the chart
here acDataPOH is the array element which is used filling the chart
for (int i = 1; i < acDataPOH.Length; i++)
{
xlWorkSheet.Cells[8, 1 + i] = LSDateTime.AddDays(-i).ToString("dd/MM/yyyy");
chartRange.NumberFormat = "HH:mm";
chartRange.NumberFormat = "HH:mm";
xlWorkSheet.Cells[9, 1 + i] = acDataPOH[i - 1];
xlWorkSheet.Cells[9, 1] = "Data";
xlWorkSheet.Name = "Chart 1 Data";
}
Step 5 : Fill data into chart
Excel.Range er = xlWorkSheet.get_Range("B8:CM9", System.Type.Missing);
er.EntireColumn.ColumnWidth = 10;
Excel.ChartObjects xlCharts = Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(23, 152, 5040, 328);
Excel.Chart chartPage = myChart.Chart;
chartRange = xlWorkSheet.get_Range("A8", "CM9");
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
chartPage.ApplyDataLabels(Microsoft.Office.Interop.Excel.XlDataLabelsType.xlDataLabelsShowLabel, false, false, false, false, false, true, true, true, true);
Step 6 : Save Chart/Excel file to into Directory
string acFileName = _owner.lbMeterNo.Text + "_" + acReadingDT.Replace('/', '_').Replace(':', '_') + ".xls";
if (File.Exists(Application.StartupPath + "\\FileList\\" + acFileName))
File.Delete(Application.StartupPath + "\\FileList\\" + acFileName);
xlWorkBook.SaveAs(Application.StartupPath + "\\FileList\\" + acFileName, 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);
if (File.Exists(Application.StartupPath + "\\FileList\\" + acFileName))
System.Diagnostics.Process.Start(Application.StartupPath + "\\FileList\\" + acFileName);