Database: MS SQL Server-2005
AdventureWork (Download)
In this article we will try to learn drawing graph in MS Excel.
For this article, we have used Customer table of Northwind database. Here we are counting companies in various countries.
First we populate the GridView. This will give you an idea about the data only. There is nothing to do with drawing the graph.
On clicking btnExport, all functionality of drawing graph is written.
DatatTable that has the resultant table of the query using which we have to draw the graph, first it is being written on the Excel file and on the basis of the data written on the excel, graph is plotted. Data written in the excel file and the graph plotted into it is interrelated. At the end don’t forget the go through the function ReleaseObject(). Also there will be needed a .Net reference of Microsoft.Office.Interop.Excel
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
DataTable dt = new DataTable();
dt = PopulateCompanyCount(0);
xlWorkSheet.Cells[1, 1] = "";
xlWorkSheet.Cells[1, 2] = "Count";
for (int i = 0; i <>
{
for (int j = 0; j <>
{
xlWorkSheet.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString();
}
}
chartRange = xlWorkSheet.get_Range("A1", str);
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
string strPath = @"C:\Documents and Settings\awadheshs\My Documents\";
string strFileName = "CompanyCount";
xlWorkBook.SaveAs(strPath + strFileName, 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);
lblAlert.Text = "Excel file created...
" +
"Created on: " + DateTime.Now.ToString("dd-MMM-yyyy hh:mm:ss") +
"
Collect the file " + strFileName + " at location " + strPath;
//This function will be used as it is….
private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
lblAlert.Text = "Exception Occured while releasing object " + ex.ToString();
}
finally
{
GC.Collect();
}
}
Click on download to get the complete code
