1、介绍和思路请参照
2、前台代码
3、后台代码
3.1、项目添加引用 Microsoft Office Web Components 11.0。 Microsoft Office Template an Media Control 1.0 Type Library。3.2、选择“引用”文件夹中引入的dll:OWC11,鼠标右键选择属性,把“嵌入互操作类型”设置为False。
3.2、在文件里面引用如下
using Microsoft.Office.Interop.Owc11;//Owc组件 using System.Data.Sql; using System.Data.SqlClient; using System.Configuration; using System.Data;
3.3、具体程序代码
1 namespace GenerateCharts 2 { 3 public partial class Excel : System.Web.UI.Page 4 { 5 protected void Page_Load(object sender, EventArgs e) 6 { 7 8 } 9 10 protected void btn_Excel_Click(object sender, EventArgs e) 11 { 12 //创建图表空间 13 ChartSpace myspace = new ChartSpace(); 14 //添加一个表容器 15 SpreadsheetClass myexcel = new SpreadsheetClass(); 16 Worksheet mysheet = myexcel.ActiveSheet; 17 //添加表标题 18 mysheet.Cells[1, 1] = "籍贯"; 19 mysheet.Cells[1, 2] = "人数"; 20 //连接并且打开数据库 21 SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ConnectionString); 22 sqlcon.Open(); 23 string strsqls = "SELECT 籍贯, COUNT(籍贯) AS 人数 FROM tb_ygxx GROUP BY 籍贯"; 24 SqlDataAdapter adsa = new SqlDataAdapter(strsqls, sqlcon); 25 DataSet adds = new DataSet(); 26 adsa.Fill(adds); 27 if (adds.Tables[0].Rows.Count > 0) 28 { 29 for (int j = 0; j < adds.Tables[0].Rows.Count; j++) 30 { 31 mysheet.Cells[j + 2, 1] = adds.Tables[0].Rows[j][0].ToString(); 32 mysheet.Cells[j + 2, 2] = adds.Tables[0].Rows[j][1].ToString(); 33 } 34 //导出表格 35 myexcel.Export(@"d:\test.xls", SheetExportActionEnum.ssExportActionOpenInExcel, SheetExportFormat.ssExportXMLSpreadsheet); 36 } 37 sqlcon.Close(); 38 } 39 } 40 }
4、效果图如下