用EPPlus导出数据就这么简单
下载EPPlus应用程序集插件:http://download.csdn.net/download/baidu_25883413/8809405
或百度网盘分享下载链接(可能无法下载):http://pan.baidu.com/s/1b3TyRO
新建Windows窗体应用程序,引入下载的EPPlus.dll
界面设计如下:
后台代码如下:
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.IO; using OfficeOpenXml; using OfficeOpenXml.Style; namespace ReportDataByEPPlus { public partial class Form1 : Form { public Form1() { InitializeComponent(); } //数据库连接字符串 public string SQLConnection = ""; private void Form1_Load(object sender, EventArgs e) { //从配置文件中得到连接数据库字符串 SQLConnection = System.Configuration.ConfigurationManager.AppSettings["SQLConnection"]; } /// <summary> /// 查询数据 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnSelect_Click(object sender, EventArgs e) { //查询数据并绑定 string sql = "select * from userInfo"; DataTable dt=DbHelperSQL.Query(sql).Tables[0]; this.dataGridView1.DataSource = dt; } /// <summary> /// 导出数据 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnReport_Click(object sender, EventArgs e) { //新建一个Excel var excle = new ExcelPackage(); string sql = "select uno 编号,name as 姓名,sex as 性别,age as 年龄 from userInfo"; DataTable dt = DbHelperSQL.Query(sql).Tables[0]; ReportData(excle,"全部用户信息",dt); sql = "select uno 编号,name as 姓名,sex as 性别,age as 年龄 from userInfo where sex=0"; dt = DbHelperSQL.Query(sql).Tables[0]; ReportData(excle, "全部男性用户信息", dt); sql = "select uno 编号,name as 姓名,sex as 性别,age as 年龄 from userInfo where sex=1"; dt = DbHelperSQL.Query(sql).Tables[0]; ReportData(excle, "全部女性用户信息", dt); //存储Excel文件 excle.SaveAs(new FileInfo(@"D:\cnblogs\test.xlsx")); MessageBox.Show("导出成功!!!"); } /// <summary> /// 导出数据 /// </summary> /// <param name="excel">新建的Excel对象</param> /// <param name="_sheetName">工作表名称</param> /// <param name="_sheetData">工作表导出的数据</param> public void ReportData(ExcelPackage excel,string _sheetName, DataTable _sheetData) { //向新建的Excel中添加一个sheet var sheet = excel.Workbook.Worksheets.Add(_sheetName); //注:Excel中行的索引从1开始,DataTable的索引从0开始 int rowIndex = 1; //起始行为第二行 int columnIndex = 0;//起始列为第一列 //绑定列头并设置样式 foreach (DataColumn dc in _sheetData.Columns) { columnIndex++; ExcelRange cell = sheet.Cells[rowIndex, columnIndex]; cell.Value = dc.ColumnName; cell.Style.Font.Bold = true; //字体为粗体 cell.Style.Font.Color.SetColor(Color.Red); //字体颜色 cell.Style.Font.Name = "微软雅黑"; //字体样式 cell.Style.Font.Size = 14; //字体大小 cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中 cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center; //垂直居中 } //绑定数据 for (int i = 0; i < _sheetData.Rows.Count; i++) { for (int j = 0; j < _sheetData.Columns.Count; j++) { //从第二行开始绑定数据 //修改性别显示方式 if (j == 2) { sheet.Cells[i + 2, j + 1].Value = int.Parse(_sheetData.Rows[i][j].ToString()) == 0 ? "男" : "女"; } else { sheet.Cells[i + 2, j + 1].Value = _sheetData.Rows[i][j]; } } } } } }
运行程序,点击查询数据:
导出数据:
查看存储的文件目录:
打开Excel查看:
查看全部男性信息:
查看全部女性信息:
导出成功!!!
相关参考链接:
导出Excel之Epplus使用教程2(样式设置)
不积跬步,无以至千里;不积小流,无以成江海。ヾ(◍°∇°◍)ノ゙