应用ExcelPackage导出Excel

前阵子工作需要，要实现从数据库中导出数据到Excel。老套路 先去百度上查阅资料，发现了以下几种方法：

1：将DataGrid控件中的数据导出Excel

2：将dataview导出excel

3：从网页上用html绘制Excel 表格  等。。

总体上感觉比较繁琐 比如用dataview导出excel时， Excel表格的格式需要在程序中设置，不利于后期的修改维护。而ExcelPackage的优势就是解放了在程序中设置格式的弊端，总体原理为：在相应路径下放置一个Excel模板，执行导出操作时按照模板样式在指定行插入数据，构建出一个模板样式的新Excel。

 

excelpackage的官网：http://excelpackage.codeplex.com/ ，里面有所需的.dll文件和demo程序。

 

接下来是我应用Excelpackage导出Excel的小程序，就当抛砖引玉了~      代码如下：

 

 

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.IO;
using OfficeOpenXml;

namespace twins
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void btnExcel_Click(object sender, EventArgs e)
        {
            // DataTable dt = getData().Tables[0];
            FileInfo newFile = new FileInfo(Server.MapPath("~/new.xls")); //生成的Excel，放在根目录下了
            string path = Server.MapPath("~/template.xls");   //模板Excel，放在根目录下
            FileInfo template = new FileInfo(path);
            if (!template.Exists)
               throw new Exception("Template file does not exist!");
            using (ExcelPackage xlPackage = new ExcelPackage(newFile, template))
            {
                // uncomment this line if you want the XML written out to the outputDir
                //xlPackage.DebugMode = true; 

                // get handle to the existing worksheet
                ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets["student"];  //Excel的sheet1位置
                if (worksheet != null)
                {
                    ExcelCell cell;
                    const int startRow = 3;           //开始插入数据的数据行，自行设置
                    int row = startRow;

                    using (SqlDataReader sqlReader = getReader())
                    {
                        // get the data and fill rows 5 onwards
                        while (sqlReader.Read())
                        {
                            int col = 1;
                            // we have our total formula on row 7, so push them down so we can insert more data
                            if (row > startRow) worksheet.InsertRow(row);

                            // our query has the columns in the right order, so simply
                            // iterate through the columns
                            for (int i = 0; i < sqlReader.FieldCount; i++)
                            {
                                // use the email address as a hyperlink for column 1
                                if (sqlReader.GetName(i) == "EmailAddress")
                                {
                                    // insert the email address as a hyperlink for the name
                                    string hyperlink = "mailto:" + sqlReader.GetValue(i).ToString();
                                    worksheet.Cell(row, 1).Hyperlink = new Uri(hyperlink, UriKind.Absolute);
                                }
                                else
                                {
                                    // do not bother filling cell with blank data (also useful if we have a formula in a cell)
                                    if (sqlReader.GetValue(i) != null)
                                        worksheet.Cell(row, col).Value = sqlReader.GetValue(i).ToString();
                                    col++;
                                }
                            }
                            row++;
                        }

                        sqlReader.Close();

                        // delete the two spare rows we have in the template
                        worksheet.DeleteRow(row, true);
                        worksheet.DeleteRow(row, true);
                        row--;
                    }
                    //用于设置excel表的格式
                    for (int iCol = 1; iCol <= 5; iCol++)                
                    {
                        cell = worksheet.Cell(startRow, iCol);
                        for (int iRow = startRow; iRow <= row; iRow++)
                        {
                            worksheet.Cell(iRow, iCol).StyleID = cell.StyleID;
                        }
                    }
                
                }
        

                // save the new spreadsheet
                try
                {
                    xlPackage.Save();  //保存生成的Excel
                }
                catch(Exception e3){
                    
                }
                
            }
        }

        protected SqlDataReader getReader()
        {
            const string connStr = "server=.;database =oz;Integrated Security =true";
            SqlConnection mySqlConnection = new SqlConnection(connStr);//新建连接对象  
            string sqlStr = " select name,sex,tel,email,job from student";//SQL语句  
            mySqlConnection.Open();//打开连接  
            SqlCommand common = new SqlCommand(sqlStr, mySqlConnection);
            SqlDataReader sdr = common.ExecuteReader();
            return sdr;
            
        }
    }
}

在根目录下放置一个template.xls文件--用于模板。我的模板如下图所示：

 

 运行程序，会把数据库student表中数据插入到excel中，我本机的student表如下图所示：

 

之后在根目录下会看到生成一个新Excel文件-new.xls. 迫不及待的打开，看到如下图 所示：

 

 成功！！！

 值得注意的是：

1：执行程序之后，template.xls是不会变的（它只是一个模板），改变的只有新生成的new.xls

2：new.xls 1,2行中静态的标题和标头的样式和模板是一致的，而动态的数据样式可以在模板excel中设置。在我此程序设置的方法为：在模板的第三行，右击鼠标->设置单元格格式->对齐居中，字体华文行楷。 设置后生成的excel中就能出现上图所示的效果，是不是很方便呐~

3： 经测试，2步骤的设置貌似只能在Excel2007中生效，也就是说如果在excel2003中设置字体和居中都是无效的！我也没有办法，同学们可以自己试一试 如果可以的话告诉我一声啊。