old_flsystem/类库/Api.Framework/Tools/Nopi/ExcelStream.cs

using System;
using System.Collections.Generic;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using System.IO;
using System.Data;
using System.Web;
namespace Eson.Utils.ExcelHelper
{
    public sealed class ExcelStream : IDisposable
    {
        public void Dispose()
        {
            workSheet = null;
            workBook = null;
        }

        public IWorkbook workBook;
        public ISheet workSheet;

        public ExcelStream() : this(null, true)
        {

        }

        public ExcelStream(string sheetname, bool is07Excel)
        {
            workBook = is07Excel ? (new XSSFWorkbook() as IWorkbook) : (new HSSFWorkbook() as IWorkbook);
            if (!string.IsNullOrEmpty(sheetname))
                workSheet = workBook.CreateSheet(sheetname);
            else
                workSheet = workBook.CreateSheet();

            //_cellStyle = GetDefaultCellStyle();
        }

        /// <summary>
        /// 导出Excel，传入DataTable内存表
        /// </summary>
        /// <param name="dtbl">DataTabke内存表</param>
        /// <param name="dicProperties">想要转换的属性名称列表[key值对应Model属性,value值对应Excel显示的列名]</param>
        /// <param name="strSaveName">保存名称</param>
        /// <param name="is07Excel">是否导出2007以上版本的Excel，默认是</param>
        //public void WriteListDate(DataTable dtbl, Dictionary<string, string> dicProperties, string strSaveName, bool is07Excel = true)
        //{
        //    #region 初始化工作簿
        //    //InitializeWorkbook(strSaveName, is07Excel);

        //    //初始化行
        //    int rowIndex = 0;
        //    int intColumn = 0;
        //    IRow rowExcel = workSheet.CreateRow(rowIndex);
        //    ICell cellExcel;

        //    //初始化列
        //    foreach (KeyValuePair<string, string> property in dicProperties)
        //    {
        //        cellExcel = rowExcel.CreateCell(intColumn);
        //        cellExcel.SetCellValue(property.Value);
        //        ++intColumn;
        //    }
        //    #endregion

        //    #region 写入内容
        //    foreach (DataRow rowInfo in dtbl.Rows)
        //    {
        //        ++rowIndex;
        //        rowExcel = workSheet.CreateRow(rowIndex);
        //        int intColumnContent = 0;
        //        foreach (KeyValuePair<string, string> property in dicProperties)
        //        {
        //            cellExcel = rowExcel.CreateCell(intColumnContent);
        //            cellExcel.SetCellValue(GetColumValue(dtbl, rowInfo, property.Key));
        //            ++intColumnContent;
        //        }
        //    }
        //    #endregion

        //    //内存流导出
        //    MemoryStream ms = new MemoryStream();
        //    ExportExcel(ms, strSaveName, is07Excel);
        //}

        /// <summary>
        /// 通过提供的列名和具体行，返回DataTable中具体的某一列的值
        /// </summary>
        /// <param name="dtbl">内存表</param>
        /// <param name="rowInfo">具体行</param>
        /// <param name="columName">列名</param>
        /// <returns>具体某列的值</returns>
        public string GetColumValue(DataTable dtbl, DataRow rowInfo, string columName)
        {
            string clumValue = "";
            foreach (DataColumn clum in dtbl.Columns)
            {
                if (clum.ColumnName.Equals(columName))
                {
                    clumValue = rowInfo[clum].ToString();
                    break;
                }
            }
            return clumValue;
        }

        /// <summary>
        /// 输出Excel文件
        /// </summary>
        /// <param name="ms">MemoryStream内存流</param>
        /// <param name="strSaveName">文件保存名称</param>
        /// <param name="is07Excel">是否导出2007以上版本的Excel</param>
        //private void ExportExcel(MemoryStream ms, string strSaveName, bool is07Excel)
        //{
        //    try
        //    {
        //        //MemoryStream ms1 = new MemoryStream();
        //        ////ms.Flush();
        //        ////ms.Position = 0;
        //        //workBook.Write(ms1);

        //        //string filename = UrlOper.GetToExcelName(strSaveName) + (is07Excel ? ".xlsx" : ".xls");
        //        //Stream stream = ms1;
        //        //FileDownload.ExportFile(stream, filename);

        //        ////ms.Close();
        //        //ms = null;
        //        //workBook = null;

        //        //ms.Flush();
        //        //ms.Position = 0;
        //        //workBook.Write(ms);
        //        //workBook = null;
        //        //HttpContext current = HttpContext.Current;
        //        //current.Response.ContentEncoding = System.Text.Encoding.UTF8;
        //        //current.Response.ContentType = "application/ms-excel";
        //        //current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + UrlOper.GetToExcelName(strSaveName) + (is07Excel ? ".xlsx" : ".xls"));
        //        //current.Response.BinaryWrite(ms.ToArray());
        //        //ms.Close();
        //        //ms = null;
        //        //current.Response.Flush();
        //        //current.Response.Close();

        //        HttpContext current = HttpContext.Current;
        //        current.Response.Clear();
        //        current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        //        current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", UrlOper.GetToExcelName(strSaveName) + ".xlsx"));
        //        //System.IO.MemoryStream ms = new System.IO.MemoryStream();
        //        workBook.Write(ms);
        //        current.Response.BinaryWrite(ms.ToArray());

        //        current.Response.Flush();
        //        current.Response.End();
        //    }
        //    catch (Exception ex) { }
        //}
    }
}