g.hnyhua.cn/Mtxfw.Utility/ExcelHelper.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Web;
using System.Data;

namespace Mtxfw.Utility
{
    public class ExcelHelper
    {
        #region 导出 (立即下载excel文件)
        public static void CreateExcelNow(DataTable dt, string ExcelPathName, string DataKey)
        {
            try
            {
                HttpContext.Current.Response.Clear();
                HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";   //申明传输类型
                HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + ExcelPathName);//输出文件名
                HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
                StringWriter sw = new StringWriter();
                int coluCount = dt.Columns.Count;
                //字段标题
                if (coluCount > 0)
                {
                    if (DataKey.Contains(","))
                    {
                        string[] array = Utils.SplitString(DataKey, ",");
                        foreach (string s in array)
                        {
                            sw.Write(s);
                            sw.Write("\t");
                        }
                        sw.WriteLine();
                    }
                    else
                    {
                        sw.WriteLine(DataKey);
                    }
                }
                int rowCount = dt.Rows.Count;
                if (rowCount > 0)
                {
                    for (int i = 0; i < rowCount; i++)
                    {
                        for (int j = 0; j < coluCount; j++)
                        {
                            sw.Write(dt.Rows[i][j].ToString());
                            sw.Write("\t");
                        }
                        sw.WriteLine();
                    }
                }
                HttpContext.Current.Response.Write(sw);
                HttpContext.Current.Response.End();
            }
            catch { }
        }
        #endregion
        
        #region 导出 (返回文件路径)
        /// <summary>
        /// 生成 Excel 文件并保存到服务器端
        /// </summary>
        /// <param name="dt">数据</param>
        /// <param name="ExcelPathName">生成Excel文件的文件名</param>
        /// <param name="DataKey">列名集合(格式是"客户名称\t客户类型\t所在地区\t结算类型")</param>
        /// <returns>返回Excel文件的相对路径地址</returns>
        public static string CreateExcel(DataTable dt, string ExcelPathName, string DataKey)
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                int coluCount = dt.Columns.Count;
                //字段标题
                if (coluCount > 0)
                {
                    if (DataKey.Contains(","))
                    {
                        string[] array = Utils.SplitString(DataKey, ",");
                        foreach (string s in array)
                        {
                            sb.Append(s);
                            sb.Append("\t");
                        }
                        sb.AppendLine();
                    }
                    else
                    {
                        sb.AppendLine(DataKey);
                    }
                }
                int rowCount = dt.Rows.Count;
                if (rowCount > 0)
                {
                    for (int i = 0; i < rowCount; i++)
                    {
                        for (int j = 0; j < coluCount; j++)
                        {
                            sb.Append(dt.Rows[i][j].ToString());
                            sb.Append("\t");
                        }
                        sb.AppendLine();
                    }
                }

                // 保存到服务器端
                string relativePath = "/dd/Dw_Datadd";
                string absolutePath = HttpContext.Current.Server.MapPath(relativePath);

                // 确保目录存在
                if (!Directory.Exists(absolutePath))
                {
                    Directory.CreateDirectory(absolutePath);
                }

                // 生成文件名（如果文件名没有扩展名，则添加.xls）
                string fileName = ExcelPathName;
                if (!fileName.EndsWith(".xls") && !fileName.EndsWith(".xlsx"))
                {
                    fileName += ".xls";
                }

                // 如果文件已存在，添加时间戳
                string fullPath = Path.Combine(absolutePath, fileName);
                if (File.Exists(fullPath))
                {
                    string fileNameWithoutExt = Path.GetFileNameWithoutExtension(fileName);
                    string extension = Path.GetExtension(fileName);
                    fileName = string.Format("{0}_{1}{2}", fileNameWithoutExt, DateTime.Now.ToString("yyyyMMddHHmmssfff"), extension);
                    fullPath = Path.Combine(absolutePath, fileName);
                }

                // 写入文件
                File.WriteAllText(fullPath, sb.ToString(), Encoding.GetEncoding("GB2312"));

                // 返回相对路径
                return relativePath + fileName;
            }
            catch
            {
                return string.Empty;
            }
        }
        #endregion

        #region 导入

        #endregion


        /// <summary>
        /// add by pld 20140117
        /// </summary>
        /// <param name="sb">excel内容字符串</param>
        /// <paramname="FileName">文件名</param>
        public static void Exportxls(string sb, String FileName)
        {
            //当前对话
            System.Web.HttpContext curContext = System.Web.HttpContext.Current;
            //IO用于导出并返回excel文件
            System.IO.StringWriter strWriter = null;
            System.Web.UI.HtmlTextWriter htmlWriter = null;

            if (sb != null)
            {
                //设置编码和附件格式
                //System.Web.HttpUtility.UrlEncode(FileName,System.Text.Encoding.UTF8)作用是方式中文文件名乱码
                curContext.Response.AddHeader("content-disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + ".xls");
                curContext.Response.ContentType = "application/vnd.ms-word";
                curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
                curContext.Response.Charset = "GB2312";

                //导出Excel文件
                strWriter = new System.IO.StringWriter();
                htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);

                curContext.Response.Write(sb);
                curContext.Response.End();
            }
        }
    }
}