Home > Articles > .Net Framework Articles > Export Data from Database to Excel and Generate Chart in C#

Export Data from Database to Excel and Generate Chart in C#

by Sam   on Apr 26, 2016   Category: .Net Framework  | Level: Beginner  |  Views: 877    |  Points: 100   
Like this article? Bookmark and Share:
This article explains how to export data from database to excel and generate chart with a .net excel library - free Spire.Xls.

People who use Access or other databases always encounter a common scenario, that is, at some point, they will need to export data from their database to an excel document. After exporting, they can use excel to analyze, calculate these data, generate chart along with distribute the data to someone else who may not have any database.

I currently need to do the same task. Fortunately, after a long time of searching, I found a free .NET excel library-Spire.Xls which helped me save a lot of time. In this article I will introduce how to export data from Access database to excel and generate chart with free Spire.Xls.

The following is what my data in Access database look like:


                                                          Figure 1: Access Database table

To use the following code, make sure you have installed the library correctly and add the dll file that is correspond with the version of your .NET Framework as reference.


Using the code:

Part 1 - Export database to excel

Here I am going to export data from one of my Access database tables to excel.

Step1: Create a new OleDbConnection instance and connect the Access dadabase, next load the specific table from database into a datatable.

string ConnectionString ="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=";

ConnectionString += "Database1.accdb";

OleDbConnection Conn = new OleDbConnection(ConnectionString);

Conn.Open();

OleDbCommand command = new OleDbCommand();

command.CommandText = "select * from [Sheet1]";

DataSet dataSet = new System.Data.DataSet();

OleDbDataAdapter dataAdapter = new OleDbDataAdapter(command.CommandText, Conn);

dataAdapter.Fill(dataSet);

DataTable datatable = dataSet.Tables[0];

 

Step2: Insert datatable into excel.

Initialize a new instance of Workbook class, get the first worksheet and invoke XlsWorksheet.InsertDataTable(System.Data.DataTable dataTable, bool columnHeaders, int firstRow, int firstColumn) method to insert the datatable into sheet1.

Workbook book = new Workbook();

Worksheet sheet = book.Worksheets[0];

sheet.InsertDataTable(datatable, true, 1, 1);

Conn.Close(); 


As this is a temperature table, so I changed the worksheet name to "TemperatureSheet", then set some properties of the worksheet such as column width, worksheet cell style and borders in order to achieve a better visual effect.

sheet.Name = "TemperatureSheet";

for(int i=1;i<=12;i++)

{

    sheet.SetColumnWidth(i,12);

}

sheet.GridLinesVisible = true;

sheet.Range["A1:L1"].Style.Font.IsBold = true;

sheet.Range["A2:L2"].Style.KnownColor = ExcelColors.LightYellow;

sheet.Range["A3:L3"].Style.KnownColor = ExcelColors.LightGreen1;

sheet.Range["A1:L3"].Style.Borders[BordersLineType.EdgeTop].Color = Color.FromArgb(0, 0, 128);

sheet.Range["A1:L3"].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;

sheet.Range["A1:L3"].Style.Borders[BordersLineType.EdgeBottom].Color = Color.FromArgb(0, 0, 128);

sheet.Range["A1:L3"].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;

sheet.Range["A1:L3"].Style.Borders[BordersLineType.EdgeLeft].Color = Color.FromArgb(0, 0, 128);

sheet.Range["A1:L3"].Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;

sheet.Range["A1:L3"].Style.Borders[BordersLineType.EdgeRight].Color = Color.FromArgb(0, 0, 128);

sheet.Range["A1:L3"].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;


Part 2 - Generate Chart

Here I am going to use these data to generate a line chart.

Step 3: Add a new chart to worksheet, select data range of the chart and set the chart type as line chart. 

Chart chart = sheet.Charts.Add();

chart.DataRange = sheet.Range["B1:L3"];

chart.ChartType = ExcelChartType.Line;

 

Apart from line chart, there are also lots of other chart types available, such as pie chart, column chart, bar chart and radar chart.



                                         Figure 2: Chart Types

 

Step 4: After creating chart, set the properties of the chart like chart position, chart title, etc. according to my requirements. 

chart.LeftColumn = 2;

chart.TopRow = 5;

chart.RightColumn = 12;

chart.BottomRow = 30;

chart.ChartTitle = "Tepmerature Chart";

chart.ChartTitleArea.IsBold = true;

chart.ChartTitleArea.Size = 12;

chart.PrimaryCategoryAxis.Title = "Day";

chart.PrimaryCategoryAxis.Font.IsBold = true;

chart.PrimaryCategoryAxis.TitleArea.IsBold = true;

chart.PrimaryValueAxis.Title = "Temperature";

chart.PrimaryValueAxis.HasMajorGridLines = true;

chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90;

chart.PrimaryValueAxis.MinValue = 1;

chart.PrimaryValueAxis.TitleArea.IsBold = true;

chart.PlotArea.Fill.Visible = false;

chart.Legend.Position = LegendPositionType.Top;

 

Step 5: Save and launch the excel file.

book.SaveToFile("DatabaseToExcel.xls");

System.Diagnostics.Process.Start("DatabaseToExcel.xls");

 

Effective screenshot:


                                            Figure 3: Result


Full codes:

//Connect Database and Load the Specific Table into a Datatable

string ConnectionString ="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=";

ConnectionString += "Database1.accdb";

OleDbConnection Conn = new OleDbConnection(ConnectionString);

Conn.Open();

OleDbCommand command = new OleDbCommand();

command.CommandText = "select * from [Sheet1]";

DataSet dataSet = new System.Data.DataSet();

OleDbDataAdapter dataAdapter = new OleDbDataAdapter(command.CommandText, Conn);

dataAdapter.Fill(dataSet);

DataTable datatable = dataSet.Tables[0];

 

//Export the Datatable to Excel

Workbook book = new Workbook();

Worksheet sheet = book.Worksheets[0];

sheet.InsertDataTable(datatable, true, 1, 1);

Conn.Close();

 

//Set Worksheet Name and Column Width

sheet.Name = "TemperatureSheet";

for(int i=1;i<=12;i++)

{

    sheet.SetColumnWidth(i,12);

}

//Set Style

sheet.GridLinesVisible = true;

sheet.Range["A1:L1"].Style.Font.IsBold = true;

sheet.Range["A2:L2"].Style.KnownColor = ExcelColors.LightYellow;

sheet.Range["A3:L3"].Style.KnownColor = ExcelColors.LightGreen1;

//Set Borders

sheet.Range["A1:L3"].Style.Borders[BordersLineType.EdgeTop].Color = Color.FromArgb(0, 0, 128);

sheet.Range["A1:L3"].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;

sheet.Range["A1:L3"].Style.Borders[BordersLineType.EdgeBottom].Color = Color.FromArgb(0, 0, 128);

sheet.Range["A1:L3"].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;

sheet.Range["A1:L3"].Style.Borders[BordersLineType.EdgeLeft].Color = Color.FromArgb(0, 0, 128);

sheet.Range["A1:L3"].Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;

sheet.Range["A1:L3"].Style.Borders[BordersLineType.EdgeRight].Color = Color.FromArgb(0, 0, 128);

sheet.Range["A1:L3"].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;

 

//Add a New Chart to Worksheet

Chart chart = sheet.Charts.Add();

//Set Data Range of the Chart

chart.DataRange = sheet.Range["B1:L3"];

chart.ChartType = ExcelChartType.;

//Set Position of Chart

chart.LeftColumn = 2;

chart.TopRow = 5;

chart.RightColumn = 12;

chart.BottomRow = 28;

//Set Chart Title, Axis and Other Properties

chart.ChartTitle = "Tepmerature Chart";

chart.ChartTitleArea.IsBold = true;

chart.ChartTitleArea.Size = 12;

chart.PrimaryCategoryAxis.Title = "Day";

chart.PrimaryCategoryAxis.Font.IsBold = true;

chart.PrimaryCategoryAxis.TitleArea.IsBold = true;

chart.PrimaryValueAxis.Title = "Temperature";

chart.PrimaryValueAxis.HasMajorGridLines = true;

chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90;

chart.PrimaryValueAxis.MinValue = 1;

chart.PrimaryValueAxis.TitleArea.IsBold = true;

chart.PlotArea.Fill.Visible = false;

chart.Legend.Position = LegendPositionType.Top;

 

//Save and Launch the Excel File

book.SaveToFile("DatabaseToExcel.xls");

System.Diagnostics.Process.Start("DatabaseToExcel.xls");

 

Conclusion

Exporting Access database to excel is very easy with Spire.Xls, it is also available for other databases like SQL Server. Thank you for reading, hope the tips in this article could help you guys a little.




Like this article? Bookmark and Share:

Most viewed Articles

User Comments


No response found, be the first to review this article.

Submit feedback about this article

Please sign in to post feedback

Latest Posts