导出Excel方法（winform或web）

一、winform形式导出Excel

　　此方法适用于winform项目导出Excel，使用前需要引用Excel.dll，此处是直接用ds导出Excel，导出方法类GetExport如下：

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using System.Windows.Forms;
using Excel;

namespace XX.Common
{
    class ExportExcel
    {

        public void GetExport(DataSet ds)
        {
            if (ds == null)
            {
                return;
            }
            string saveFileName = "";
            bool fileSaved = false;
            SaveFileDialog saveDialog = new SaveFileDialog();
            saveDialog.DefaultExt = "xls";
            saveDialog.Filter = "Excel文件|*.xls";
            saveDialog.FileName = "name";//Excel文件名称
            saveDialog.ShowDialog();
            saveFileName = saveDialog.FileName;
            if (saveFileName.IndexOf(":") < 0)
            {
                return;
            }
            //被点了取消 

            Excel.Application xlApp = new Excel.Application();
            if (xlApp == null)
            {
                MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
                return;
            }
            Excel.Workbooks workbooks = xlApp.Workbooks;
            Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1           
            Excel.Range range;
            //string oldCaption = DateTime.Today.ToString("yy-MM-dd");
            long totalCount = ds.Tables[0].Rows.Count;
            long rowRead = 0;
            //float percent = 0;//本次不显示进度，因此注释掉，需要的话可以放开运用
            //worksheet.Cells[1, 1] = "评分结果";
            //写入字段           
            for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
            {
                //worksheet.Cells[1, i + 1] = ds.Tables[0].Columns[i].ColumnName;
                //range = (Excel.Range)worksheet.Cells[1, i + 1];
                ///////此处直接将列名写死，因为ds中直接取出的是英文字段名，若取出的是中文列名可直接用上面两行代码写入(根据实际需要灵活运用即可)////////////
                worksheet.Cells[1, 1] = "第一行第一列";
                worksheet.Cells[1, 2] = "第一行第二列"; 69                 //range = (Excel.Range)worksheet.Cells[1, i + 4];//此处是为列名加样式（底色，加粗等），此次不加样式
                //range.Interior.ColorIndex = 15;
                //range.Font.Bold = true;
            }
            //写入数值              
            for (int r = 0; r < ds.Tables[0].Rows.Count; r++)
            {
                for (int i = 0; i < ds.Tables[0].Columns.Count - 1; i++)
                {
                    worksheet.Cells[r + 2, 1] = r + 1;
                    worksheet.Cells[r + 2, i + 2] = ds.Tables[0].Rows[r][i];
                }
                rowRead++;
                //percent = ((float)(100 * rowRead)) / totalCount;
                //this.lbl_process.Text = "正在导出数据[" + percent.ToString("0.00") + "%]..."; //这里可以自己做一个label用来显示进度.              

                System.Windows.Forms.Application.DoEvents();
            }
            //this.lbl_process.Visible = false; //label可见性，此次不用
            range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[ds.Tables[0].Rows.Count + 1, ds.Tables[0].Columns.Count + 1]);
            range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);

            range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
            range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;

            range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;

            if (ds.Tables[0].Columns.Count > 1)
            {
                range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;

                range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
                range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
            }

            if (saveFileName != "")
            {
                try
                {

                    workbook.Saved = true;
                    workbook.SaveCopyAs(saveFileName);

                    fileSaved = true;
                }
                catch (Exception ex)
                {

                    fileSaved = false;
                    MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
                }
            }
            else
            {
                fileSaved = false;

            }
            xlApp.Quit();
            GC.Collect();//强行销毁           
            if (fileSaved && File.Exists(saveFileName))
            {
                //System.Diagnostics.Process.Start(saveFileName);
                MessageBox.Show("导出成功！", "通知");
            }

        }
    }
}

二、Web形式导出Excel

　　Web版本导出，此处我使用了NPOI组件，首先需要到官网下载最新组件（http://npoi.codeplex.com/）。需要注意的是，NPOI组件也可以实现winform项目的Excel导出。具体实现导出功能的类如下：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.IO;
using NPOI;
using NPOI.HPSF;
using NPOI.HSSF;
using NPOI.HSSF.UserModel;
using NPOI.POIFS;
using NPOI.Util;
using System.Text;

namespace XX.Common
{
    public class ExcelHelper
    {
        /// <summary>
        /// 由DataSet导出数据流Stream
        /// </summary>
        /// <param name="sourceDs">要导出数据的DataSet</param>
        /// <param name="sheetName">页签名称</param>
        /// <returns>数据流Stream</returns>
        private static Stream ExportDataSetToExcel(DataSet sourceDs, string sheetName)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            MemoryStream ms = new MemoryStream();
            string[] sheetNames = sheetName.Split(\',\');
            for (int i = 0; i < sheetNames.Length; i++)
            {
                HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(sheetNames[i]);
                HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
                // handling header.
                foreach (DataColumn column in sourceDs.Tables[i].Columns)
                    headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                // handling value.
                int rowIndex = 1;
                foreach (DataRow row in sourceDs.Tables[i].Rows)
                {
                    HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
                    foreach (DataColumn column in sourceDs.Tables[i].Columns)
                    {
                        dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                    }
                    rowIndex++;
                }
            }
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            workbook = null;
            return ms;
        }

        /// <summary>
        /// 由DataSet导出Excel
        /// </summary>
        /// <param name="sourceDs">要导出数据的DataSet</param>
        /// <param name="fileName">指定Excel工作表名称</param>
        /// <param name="sheetName">指定Excel页签名称</param>
        public static void ExportDataSetToExcel(DataSet sourceDs, string fileName, string sheetName)
        {
            MemoryStream ms = ExportDataSetToExcel(sourceDs, sheetName) as MemoryStream;
            string UserAgent = HttpContext.Current.Request.ServerVariables["http_user_agent"].ToLower();
            // Firfox和IE下输出中文名显示正常 
            if (UserAgent.IndexOf("firefox") == -1)
            {
                fileName = HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8);
            } 
            HttpContext.Current.Response.ContentType = "application/vnd.ms-excel;charset=UTF-8";
            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
            HttpContext.Current.Response.BinaryWrite(ms.ToArray());
            HttpContext.Current.Response.End();
            ms.Close();
            ms = null;
        }      
    }
}