C#导出excel

第一种比较实用，Insert添加数据

/// <summary>
        /// 导出数据到Excel并保存
        /// </summary>
        /// <param name="ds">DataSet</param>
        /// <param name="filePath">文件路径</param>
        /// <param name="columName">excel列名 逗号分割的字符串</param>
        /// <param name="tableName">数据库列名 逗号分割字符串 </param>
        /// <returns></returns>
        public static bool ExportReportEmail(DataSet  ds, string filePath,string columName, string tableName)
        {
            if (ds != null && ds.Tables.Count > 0)
            {
                System.Data.DataTable sourceTable = ds.Tables[0];
                // 使用OleDb驱动程序连接到副本
                OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0;");
                using (conn)
                {
                    conn.Open();//  
                    if (sourceTable != null && sourceTable.Rows.Count > 0)
                    {
                        string[] colums = columName.Split(',');
                        string[] tables = tableName.Split(',');
                        for (int i = 0; i < sourceTable.Rows.Count; i++)
                        {
                            // 增加记录
                            OleDbCommand cmd = new OleDbCommand("INSERT INTO [Sheet1$](" + columName + ") VALUES(" + tableName + ")", conn);
　　　　　　　　　　　　　　　　　//循环给列 指定参数
                            for (int j = 0; j < colums.Length;j++ )
                            {
                                cmd.Parameters.AddWithValue(tables[j].Trim(), sourceTable.Rows[i][colums[j].Trim()].ToString());
                                //
                            }
                            cmd.ExecuteNonQuery();
                        }
                    }
                }


                return true;
            }else
                return false;
}

第二种  写入文件

 
//这种写入的文件，其实后缀是xls的 文本文档
try
            {
                long rowRead = 0;

                StreamWriter sw = new StreamWriter(Pathname, false, Encoding.GetEncoding("gb2312"));
                StringBuilder sb = new StringBuilder();
                for (int k = 0; k < sourceTable.Columns.Count; k++)
                {
                    sb.Append(sourceTable.Columns[k].ColumnName.ToString() + "\t");
                }
                sb.Append(Environment.NewLine);

                for (int i = 0; i < sourceTable.Rows.Count; i++)
                {
                    rowRead++;
                    for (int j = 0; j < sourceTable.Columns.Count; j++)
                    {
                        sb.Append(sourceTable.Rows[i][j].ToString() + "\t");
                    }
                    sb.Append(Environment.NewLine);
                }
                sw.Write(sb.ToString());
                sw.Flush();
                sw.Close();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            } 

 

 第三种  HSSFWorkbook

using Microsoft.Office.Interop.Excel;
using NPOI.HSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Web;

namespace JW.Shop.Common
{
    public class NopiHelper
    {
        #region 导出到Excel(仅支持Excel1997-2003)

        /// <summary>
        /// 由DataTable导出Excel
        /// </summary>
        /// <param name="sourceTable">要导出的数据源</param>
        /// <param name="sheetName">工作表名</param>
        /// <param name="columnNames">工作表列名</param>
        /// <returns>Excel工作表</returns>
        private static Stream ExportDataTableToExcel(System.Data.DataTable sourceTable, string sheetName, string columnNames)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            MemoryStream ms = new MemoryStream();
            HSSFSheet sheet = workbook.CreateSheet(sheetName) as HSSFSheet;
            HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
            // handling header.
            if (!string.IsNullOrEmpty(columnNames))
            {
                string[] cols = columnNames.Split(',');
                for (int i = 0; i < sourceTable.Columns.Count; i++)
                {
                    headerRow.CreateCell(sourceTable.Columns[i].Ordinal).SetCellValue(cols[i]);
                }
            }
            else
            {
                foreach (DataColumn column in sourceTable.Columns)
                    headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
            }
            // handling value.
            int rowIndex = 1;
            foreach (DataRow row in sourceTable.Rows)
            {
                HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
                foreach (DataColumn column in sourceTable.Columns)
                {
                    dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                }
                rowIndex++;
            }
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            sheet = null;
            headerRow = null;
            workbook = null;
            return ms;
        }

        /// <summary>
        /// 导出数据到Excel并下载(仅支持Excel1997-2003)
        /// </summary>
        /// <param name="sourceTable">要导出的数据源</param>
        /// <param name="sheetName">Excel的工作表名</param>
        /// <param name="columnNames">工作表列名(逗号分隔)</param>
        /// <param name="fileName">下载保存Excel的文件名</param>
        /// <returns>Excel工作表</returns>
        public static bool ExportDownloadExcel(System.Data.DataTable sourceTable, string sheetName, string columnNames, string fileName)
        {

            try
            {
                MemoryStream ms = ExportDataTableToExcel(sourceTable, sheetName, columnNames) as MemoryStream;
                HttpContext.Current.Response.Clear();
                HttpContext.Current.Response.ClearContent();
                HttpContext.Current.Response.ClearHeaders();
                HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8));
                HttpContext.Current.Response.ContentType = "application/ms-excel"; // application/octet-stream
                HttpContext.Current.Response.HeaderEncoding = Encoding.UTF8;// System.Text.Encoding.GetEncoding("GB2312");
                HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;
                HttpContext.Current.Response.BinaryWrite(ms.ToArray());
                HttpContext.Current.Response.Flush();
                //HttpContext.Current.Response.End();
                ms.Close();
                ms = null;
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// 由DataSet导出Excel
        /// </summary>
        /// <param name="sourceDs">要导出的数据源</param>
        /// <param name="sheetName">工作表名</param>
        /// <param name="columnNames">工作表列名</param>
        /// <returns>Excel工作表</returns>
        private static Stream ExportDataSetToExcel(DataSet sourceDs, string[] sheetNames, string[] columnNames)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            MemoryStream ms = new MemoryStream();
            //string[] sheetNames = sheetName.Split(',');
            for (int i = 0; i < sheetNames.Length; i++)
            {
                HSSFSheet sheet = workbook.CreateSheet(sheetNames[i]) as HSSFSheet;
                HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
                // handling header.
                if (columnNames.Length > 0 && (!string.IsNullOrEmpty(columnNames[i])))
                {
                    string[] cols = columnNames[i].Split(',');
                    for (int j = 0; j < cols.Length; j++)
                    {
                        headerRow.CreateCell(sourceDs.Tables[i].Columns[j].Ordinal).SetCellValue(cols[j]);
                    }
                }
                else
                {
                    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 = sheet.CreateRow(rowIndex) as HSSFRow;
                    if (columnNames.Length > 0 && (!string.IsNullOrEmpty(columnNames[i])))
                    {
                        string[] cols = columnNames[i].Split(',');
                        for (int j = 0; j < cols.Length; j++)
                        {
                            dataRow.CreateCell(sourceDs.Tables[i].Columns[j].Ordinal).SetCellValue(row[j].ToString());
                        }
                    }
                    else
                    {
                        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>
        /// 导出数据到Excel并下载(仅支持Excel1997-2003)
        /// </summary>
        /// <param name="sourceDs">要导出数据的DataTable</param>
        /// <param name="sheetName">工作表名</param>
        /// <param name="columnNames">工作表列名</param>
        /// <param name="fileName">指定Excel工作表名称</param>
        /// <returns>Excel工作表</returns>
        public static bool ExportDownloadExcel(DataSet sourceDs, string[] sheetName, string[] columnNames, string fileName)
        {
            try
            {
                MemoryStream ms = ExportDataSetToExcel(sourceDs, sheetName, columnNames) as MemoryStream;
                HttpContext.Current.Response.Clear();
                HttpContext.Current.Response.ClearContent();
                HttpContext.Current.Response.ClearHeaders();
                HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8));
                HttpContext.Current.Response.ContentType = "application/ms-excel"; // application/octet-stream
                HttpContext.Current.Response.HeaderEncoding = Encoding.UTF8;// System.Text.Encoding.GetEncoding("GB2312");
                HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;
                HttpContext.Current.Response.BinaryWrite(ms.ToArray());
                HttpContext.Current.Response.Flush();
                //HttpContext.Current.Response.End();
                ms.Close();
                ms = null;
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        #endregion
}
}