C#实现execl导出

以webform为例，在前端给出一个导出按钮，并且绑定点击事件。后台代码如下，需要引用如下几个DLL文件。（NPOI.DLL,NPOI.HSSF.DLL,NPOI.POIFS.DLL）三个引用文件。

 protected void Button_export_Click(object sender, EventArgs e)
        {
                try
                {
                    //获取数据源dataset
                    DataSet ds = GetDataSetForPainInList();
                    if (ds != null && ds.Tables[0].Rows.Count > 0)
                    {
                        HSSFWorkbook wordbook = new HSSFWorkbook();
                        ExportExcel(wordbook, ds, "Sheet1");
                    }
                    else
                    {
                        Response.Write("<script defer>alert('没有数据！');</script>");
                    }
                }
                catch (Exception ex)
                {
                    Response.Write("<script defer>alert('" + ex.Message.ToString() + "');</script>");
                }
                Download();
         }

         #region 导出Excel
         #region ExportExcel()
         private void ExportExcel(HSSFWorkbook workbook, DataSet ds, string SheetName)
         {
             DataTable dt = ds.Tables[0];

             DataTableRenderToExcel.RenderDataTableToExcel(workbook, dt, Server.MapPath( "Sheet1.xls"), SheetName);
         }
         #endregion

         #region 下载文件
         public void Download()
         {
             string strFileName = Server.MapPath("Sheet1.xls");
             FileInfo fi = new FileInfo(strFileName);

             Response.Clear();
             Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlPathEncode(DateTime.Now + "Sheet1.xls"));
             Response.AddHeader("Content-Length", fi.Length.ToString());
             Response.ContentType = "application/octet-stream";
             Response.Filter.Close();
             Response.WriteFile(fi.FullName);
             Response.Flush();
             Response.Close();
             fi.Delete();
             Response.End();
             Response.Write("<script>window.close();</script>");
         }
         #endregion
         #endregion
   /// <summary>
        /// 获取导出数据列表
        /// </summary>
        /// <param name="YwInterfaceID"></param>
        /// <returns></returns>
        private DataSet GetDataSetForPainInList()
        {
                DataService obj = new DataService();
                DateTime start = DateTime.Parse(StartDate.Value);
                DateTime end = DateTime.Parse(EndDate.Value);
                string textword = TextBox_Word.Text.Trim();
                string strSQL="";
                if (start < end)
                    strSQL = "TranDatetime BETWEEN '" + start + "' AND '" + end + "'";
                if (DropDownList_Dept.SelectedValue.Trim() != "")
                    strSQL += " AND DeptName='" + DropDownList_Dept.SelectedItem.Text.Trim() + "'";
                if (textword != "")
                {
                    strSQL += " And (ReBankAccount like '%" + textword + "%' or PainInAmount like '%" + textword + "%' or PayBankAccountName like '%" + textword + "%' or Remark like '%" + textword + "%' or ClaimedEmpName  like '%" + textword + "%' or GDAmount like '%" + textword + "%' or JYAmount like '%" + textword + "%')";
                }
                string Sql=@"select rebankaccount as '本方账号',trandatetime as '交易时间',painInamount as '发生额',rebankaccountname as '对方单位名称',remark as '备注',claimedEmpname as '款项责任人',gdamount as '勾对金额',jyamount as '结余金额' FROM dbo.vfdPainInListCX where " + strSQL;
                return obj.GetSQLDataset(Sql);

        }

导出的类文件cs方法：（公共方法）

 public static Stream RenderDataTableToExcel(HSSFWorkbook workbook, DataTable SourceTable, string SheetName)
        {
            MemoryStream ms = new MemoryStream();
            HSSFSheet sheet = workbook.CreateSheet(SheetName);
            HSSFRow headerRow = sheet.CreateRow(0);

            // handling header.
            foreach (DataColumn column in SourceTable.Columns)
                headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);

            // handling value.
            int rowIndex = 1;
            for (int i = 0; i < SourceTable.Rows.Count; i++)
            //foreach (DataRow row in SourceTable.Rows)
            {
                HSSFRow dataRow = sheet.CreateRow(rowIndex);

                foreach (DataColumn column in SourceTable.Columns)
                {
                    if (column.DataType.Name.ToString().ToLower() == "double" || column.DataType.Name.ToString().ToLower() == "decimal" || column.DataType.Name.ToString().ToLower() == "int32" || column.DataType.Name.ToString().ToLower() == "int64" || column.DataType.Name.ToString().ToLower() == "int16")
                    {
                        double d = 0;
                        if (SourceTable.Rows[i][column] != DBNull.Value)
                            d = double.Parse(SourceTable.Rows[i][column].ToString());
                        dataRow.CreateCell(column.Ordinal).SetCellValue(d);
                    }
                    else if (column.DataType.Name.ToString().ToLower() == "datetime")
                    {
                        string t = "";
                        if (SourceTable.Rows[i][column] != DBNull.Value)
                            t = DateTime.Parse(SourceTable.Rows[i][column].ToString()).ToString("yyyy-MM-dd");
                        dataRow.CreateCell(column.Ordinal).SetCellValue(t);
                    }
                    else
                    {
                        dataRow.CreateCell(column.Ordinal).SetCellValue(SourceTable.Rows[i][column].ToString());
                    }
                }

                rowIndex++;
            }

            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;

            sheet = null;
            headerRow = null;
            workbook = null;

            return ms;
        }
        private static void CreateHeader(object newsheet)
        {
            throw new Exception("The method or operation is not implemented.");
        }
        public static void RenderDataTableToExcel(HSSFWorkbook workbook, DataTable SourceTable, string FileName, string SheetName)
        {
            MemoryStream ms = RenderDataTableToExcel(workbook, SourceTable, SheetName) as MemoryStream;
            FileStream fs = new FileStream(FileName, FileMode.Create, FileAccess.Write);
            byte[] data = ms.ToArray();

            fs.Write(data, 0, data.Length);
            fs.Flush();
            fs.Close();

            data = null;
            ms = null;
            fs = null;
        }
        public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, string SheetName, int HeaderRowIndex)
        {
            HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream);
            HSSFSheet sheet = workbook.GetSheet(SheetName);

            DataTable table = new DataTable();

            HSSFRow headerRow = sheet.GetRow(HeaderRowIndex);
            int cellCount = headerRow.LastCellNum;

            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                table.Columns.Add(column);
            }

            int rowCount = sheet.LastRowNum;

            for (int i = (sheet.FirstRowNum + 1); i < sheet.LastRowNum; i++)
            {
                HSSFRow row = sheet.GetRow(i);
                DataRow dataRow = table.NewRow();

                for (int j = row.FirstCellNum; j < cellCount; j++)
                    dataRow[j] = row.GetCell(j).ToString();
            }

            ExcelFileStream.Close();
            workbook = null;
            sheet = null;
            return table;
        }
        public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, int SheetIndex, int HeaderRowIndex)
        {
            HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream);
            HSSFSheet sheet = workbook.GetSheetAt(SheetIndex);

            DataTable table = new DataTable();

            HSSFRow headerRow = sheet.GetRow(HeaderRowIndex);
            int cellCount = headerRow.LastCellNum;

            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                table.Columns.Add(column);
            }

            int rowCount = sheet.LastRowNum;

            for (int i = (sheet.FirstRowNum + 1); i < sheet.LastRowNum; i++)
            {
                HSSFRow row = sheet.GetRow(i);
                DataRow dataRow = table.NewRow();

                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)
                        dataRow[j] = row.GetCell(j).ToString();
                }

                table.Rows.Add(dataRow);
            }

            ExcelFileStream.Close();
            workbook = null;
            sheet = null;
            return table;
        }