asp.net HttpHandler從資料庫表匯出Excel檔案

asp教程.net HttpHandler從資料庫教程表匯出Excel檔案

1.建立“匯出Excel”asp.net教程 Web應用程式 檔案。

2.拷貝NPOI庫檔案到建立的“lib”目錄下，添加引用把７個.dll選上。

3.添加一般處理常式，檔案名稱DownloadExcel1.ashx

4.建資料庫UserDB.mdf 欄位為：UserName,Password

5.在Default.aspx檔案中加上：

    <div>
    <a href ="DownloadExcel1.ashx">下載Excel</a>
    </div>

6.在DownloadExcel1.ashx.cs檔案中添加以下代碼：

 context.Response.ContentType = "application/x-excel";
            string filename = HttpUtility.UrlEncode("使用者資料.xls");           
            context.Response.AddHeader("Content-Disposition", "attachment;filename=" + filename);
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.CreateSheet();
            using (SqlConnection conn = new SqlConnection(@"Data Source=.SQLEXPRESS;AttachDBFilename=|DataDirectory|UserDB.mdf;Integrated Security=True;User Instance=True"))
               {
                conn.Open();
                using (IDbCommand cmd = conn.CreateCommand())
                {
                  cmd.CommandText = "select * from T_Users";
                  using (IDataReader reader = cmd.ExecuteReader())
                   {
                     int rownum = 0;
                     while (reader.Read())
                       {
                         string username = reader.GetString(reader.GetOrdinal("UserName"));
                         string password = reader.GetString(reader.GetOrdinal("Password"));
                         HSSFRow row = sheet.CreateRow(rownum);
                         row.CreateCell(0, HSSFCell.CELL_TYPE_STRING).SetCellValue(username);
                         row.CreateCell(1, HSSFCell.CELL_TYPE_STRING).SetCellValue(password);
                         rownum++;
                        }
                    }
                }
            }
            workbook.Write(context.Response.OutputStream);
        }