How to Create Excel Chart by C# Based on Data from SQL Server

Comparing with numbers, chart can present data information more clearly. This article will show the way to create an Excel chart in C# and the data is from SQL Server. Therefore, we need to copy data in SQL database to Excel workbook and format the data. And then we can create a chart by giving the new range of data.

Now, let’s get started. Firstly, create a Visual Studio .Net project and then create an Excel Workbook. We need to start Visual Studio .Net. Click Project on the File menu. Select Visual C# Project by expanding Microsoft Office System Projects. Select Excel Workbook from New Project dialog box. Give the project a name and store it. Finally, accept the defaults in the Microsoft Office Project Wizard and finish it.

Secondly, we need to create a hyperlink within the workbook to react to the FollowHyperlink event. Select Hyperlink from the Insert menu in Excel after opening the workbook. Set the Text to display value to Create New Chart in Insert Hyperlink dialog box. Select Place in This Document in the Link to pane and then make sure the cell reference matches the hyperlink location. Save changes and close Excel to finish.

What we should do next is to hook up the event handler by using C#. Locate the existing declarations for the openEvent and beforeCloseEvent variables and add a new variable showing the workbook’s SheetFollowHyperlink event. Then, add ThisWorkbook Open procedure stud to the class. Modify ThisWorkbook_SheetFollowHyperlink procedure to finish.

Then, import data to Workbook. Add statements to reduce the amount of typing required to refer to the objects and members. Add declarations for ThisApplication and ThisWorkbook. Add procedure which refreshes the workbook and its contents in the OfficeCodeBehind class. Connect the procedure to SQL Server and return a SqlDataAdapter ready to retrieve data in workbook. Add procedure to create the sheet that holds data from SQL Server and format column headings. Then, retrieve data from SqlDataReader and format data. Call the other procedures to load data to Excel Workbook. Save the entire solution and open Excel Workbook. Click the link and check if the data has been imported and formatted. Finally, save the workbook.

Now, add the Chart. We can use ChartWizard method offered by the Excel Chart object. At first, call the ChartWizard method and add some formatting to the chart elements. Modify the LoadDataAndCreateChart method to add a call to the new CreateChart method. Save the project and run it. Click Create New Chart link in Excel and check if the chart has been created and formatted with data. We need to click the link again to make sure that the extra sheets are deleted correctly. Finally, save workbook and close Excel.

OK, the Excel chart has been created and shows us the obvious data information.