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


namespace vs13.Code
{
    /// <summary>
    /// 功能说明：此工具主要采用NOPI组件，实现对Excel的获取数据、导出数据到Excel等功能。
    /// </summary>
    /// <remarks>
    /// 创建时间：2012年12月12日0:17:22   创建人：张晓斌
    /// 备注信息：
    /// 注意事项：采用NOPI进行操作Excel可以在服务器上不用安装Office的情况下进行，而且性能比直接操作Office要高很多
    /// 修改时间：              
    /// </remarks>
    public class NPOIExcelHelper
    {
        #region ExportEasy：NPOI简单Demo，快速入门代码,以MemoryStream形式实现导出DataTable数据到Excel
        /// <summary>
        /// NPOI简单Demo，快速入门代码
        /// </summary>
        /// <param name="dtSource"></param>
        /// <param name="strFileName"></param>
        /// <remarks>NPOI认为Excel的第一个单元格是：(0，0)</remarks>
        public static void ExportEasy(DataTable dtSource, string strFileName)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.CreateSheet();

            //填充表头
            HSSFRow dataRow = sheet.CreateRow(0);
            foreach (DataColumn column in dtSource.Columns)
            {
                dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
            }


            //填充内容
            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                dataRow = sheet.CreateRow(i + 1);
                for (int j = 0; j < dtSource.Columns.Count; j++)
                {
                    dataRow.CreateCell(j).SetCellValue(dtSource.Rows[i][j].ToString());
                }
            }


            //保存
            using (MemoryStream ms = new MemoryStream())
            {
                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                {
                    workbook.Write(ms);
                    ms.Flush();
                    ms.Position = 0;
                    byte[] data = ms.ToArray();
                    fs.Write(data, 0, data.Length);
                    fs.Flush();
                }
            }
            sheet.Dispose();
            workbook.Dispose();
        }
        #endregion

        #region ExportByWeb:Web形式导出DataTable数据到Excel
        /// <summary>
        /// 用于Web导出
        /// </summary>
        /// <param name="dtSource"></param>
        /// <param name="strHeaderText"></param>
        /// <param name="strFileName"></param>
        public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName)
        {

            HttpContext curContext = HttpContext.Current;
            if (!strFileName.Contains(".xls") || !strFileName.Contains(".xlsx"))
                strFileName += ".xls";
            // 设置编码和附件格式
            curContext.Response.ContentType = "application/vnd.ms-excel";
            curContext.Response.ContentEncoding = Encoding.UTF8;
            curContext.Response.Charset = "";
            curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));

            curContext.Response.BinaryWrite(Export(dtSource, strHeaderText).GetBuffer());
            curContext.Response.End();

        }
        #endregion

        #region Export:DataTable导出到Excel的MemoryStream
        /// <summary>
        /// DataTable导出到Excel的MemoryStream
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        /// <returns></returns>
        public static MemoryStream Export(DataTable dtSource, string strHeaderText)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.CreateSheet();

            #region 右击文件 属性信息
            {
                DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                dsi.Company = "";//公司
                workbook.DocumentSummaryInformation = dsi;

                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                si.Author = ""; //填加xls文件作者信息
                si.ApplicationName = ""; //填加xls文件创建程序信息
                si.LastAuthor = ""; //填加xls文件最后保存者信息
                si.Comments = ""; //填加xls文件作者信息
                si.Title = ""; //填加xls文件标题信息
                si.Subject = "";//填加文件主题信息
                si.CreateDateTime = DateTime.Now;
                workbook.SummaryInformation = si;
            }
            #endregion

            HSSFCellStyle dateStyle = workbook.CreateCellStyle();
            HSSFDataFormat format = workbook.CreateDataFormat();
            //dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd");

            //取得列宽
            int[] arrColWidth = new int[dtSource.Columns.Count];
            foreach (DataColumn item in dtSource.Columns)
            {                                               /*gb2312*/
                arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
            }
            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                for (int j = 0; j < dtSource.Columns.Count; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
            }

            int rowIndex = 0;

            foreach (DataRow row in dtSource.Rows)
            {
                #region 新建表，填充表头，填充列头，样式
                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet();
                    }

                    #region 表头及样式
                    {
                        HSSFRow headerRow = sheet.CreateRow(0);
                        headerRow.HeightInPoints = 25;
                        headerRow.CreateCell(0).SetCellValue(strHeaderText);

                        HSSFCellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = CellHorizontalAlignment.CENTER;
                        HSSFFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 20;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);

                        headerRow.GetCell(0).CellStyle = headStyle;

                        sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
                        headerRow.Dispose();
                    }
                    #endregion


                    #region 列头及样式
                    {
                        HSSFRow headerRow = sheet.CreateRow(1);


                        HSSFCellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = CellHorizontalAlignment.CENTER;
                        headStyle.BorderBottom = CellBorderType.THIN;
                        headStyle.BorderLeft = CellBorderType.THIN;
                        headStyle.BorderRight = CellBorderType.THIN;
                        headStyle.BorderTop = CellBorderType.THIN;
                        HSSFFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 10;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);


                        foreach (DataColumn column in dtSource.Columns)
                        {
                            headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                            headerRow.GetCell(column.Ordinal).CellStyle = headStyle;

                            //设置列宽
                            sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);

                        }
                        headerRow.Dispose();
                    }
                    #endregion

                    rowIndex = 2;
                }
                #endregion


                #region 填充内容
                foreach (DataColumn column in dtSource.Columns)
                {
                    HSSFRow dataRow = sheet.CreateRow(rowIndex);
                    HSSFCell newCell = dataRow.CreateCell(column.Ordinal);

                    newCell.CellStyle.BorderBottom = CellBorderType.THIN;
                    newCell.CellStyle.BorderLeft = CellBorderType.THIN;
                    newCell.CellStyle.BorderRight = CellBorderType.THIN;
                    newCell.CellStyle.BorderTop = CellBorderType.THIN;

                    string drValue = row[column].ToString();

                    switch (column.DataType.ToString())
                    {
                        case "System.String"://字符串类型
                            newCell.SetCellValue(drValue);
                            break;
                        case "System.DateTime"://日期类型
                            DateTime dateV;
                            DateTime.TryParse(drValue, out dateV);
                            newCell.SetCellValue(dateV);

                            newCell.CellStyle = dateStyle;//格式化显示
                            break;
                        case "System.Boolean"://布尔型
                            bool boolV = false;
                            bool.TryParse(drValue, out boolV);
                            newCell.SetCellValue(boolV);
                            break;
                        case "System.Int16"://整型
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            int intV = 0;
                            int.TryParse(drValue, out intV);
                            newCell.SetCellValue(intV);
                            break;
                        case "System.Decimal"://浮点型
                        case "System.Double":
                            double doubV = 0;
                            double.TryParse(drValue, out doubV);
                            newCell.SetCellValue(doubV);
                            break;
                        case "System.DBNull"://空值处理
                            newCell.SetCellValue("");
                            break;
                        default:
                            newCell.SetCellValue("");
                            break;
                    }

                }
                #endregion

                rowIndex++;
            }

            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;

                sheet.Dispose();
                workbook.Dispose();

                return ms;
            }
        }
        #endregion

        #region Export:以MemoryStream形式将DataTable导出到Excel文件
        /// <summary>
        /// DataTable导出到Excel文件
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        /// <param name="strFileName">保存位置</param>
        public static void Export(DataTable dtSource, string strHeaderText, string strFileName)
        {
            using (MemoryStream ms = Export(dtSource, strHeaderText))
            {
                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                {
                    byte[] data = ms.ToArray();
                    fs.Write(data, 0, data.Length);
                    fs.Flush();
                }
            }
        }
        #endregion

        public static DataTable Import(string strFileName, string SheetName, int HeaderRowIndex)
        {
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                return Import(file, SheetName, HeaderRowIndex);
            }
        }
        public static DataTable Import(string strFileName, int SheetIndex, int HeaderRowIndex)
        {
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                return Import(file, SheetIndex, HeaderRowIndex);
            }
        }


        #region Import:读取Excel默认第一行为列名
        /// <summary>
        /// 读取Excel默认第一行为标头
        /// </summary>
        /// <param name="strFileName">excel文档路径</param>
        /// <returns></returns>
        public static DataTable Import(string strFileName)
        {
            DataTable dt = new DataTable();

            HSSFWorkbook hssfworkbook;
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new HSSFWorkbook(file);
            }
            HSSFSheet sheet = hssfworkbook.GetSheetAt(0);
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

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

            for (int j = 0; j < cellCount; j++)
            {
                HSSFCell cell = headerRow.GetCell(j);
                dt.Columns.Add(cell.ToString());
                // dt.Columns.Add(getCellValueByType(cell));
            }

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

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

                dt.Rows.Add(dataRow);
            }

            return dt;
        }
        #endregion

        #region Import:读取Excel数据到DataTable第一行为列名
        /// <summary>
        /// 读取Excel数据到DataTable第一行为列名
        /// </summary>
        /// <param name="ExcelFileStream">Excel文件流</param>
        /// <param name="SheetName"></param>
        /// <param name="HeaderRowIndex">从第几行开始读取</param>
        /// <returns></returns>
        public static DataTable Import(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;
            int startRow = HeaderRowIndex + 1;

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

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

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

        #region Import：读取Excel数据到DataTable第一行为列名
        /// <summary>
        /// 读取Excel数据到DataTable第一行为列名
        /// </summary>
        /// <param name="ExcelFileStream">Excel文件流</param>
        /// <param name="SheetIndex"></param>
        /// <param name="HeaderRowIndex">从第几行开始读取</param>
        /// <returns></returns>
        public static DataTable Import(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;
            int cellCount = headerRow.Cells.Count;

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

            int rowCount = sheet.LastRowNum;
            int startRow = HeaderRowIndex + 1;
            for (int i = startRow; 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] = getCellValueByType(row.GetCell(j));
                        //dataRow[j] = row.GetCell(j).ToString();
                    }
                }

                table.Rows.Add(dataRow);
            }

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

        #region getCellValueByType:获取Excel对象单元格中的数据值
        /// <summary>
        /// 获取Excel对象单元格中的数据值
        /// </summary>
        /// <param name="cell">HSSFCell：Excel单元格对象</param>
        /// <returns></returns>
        public static string getCellValueByType(HSSFCell cell)
        {
            string ret = "";
            switch (cell.CellType)
            {
                case HSSFCellType.BLANK: ret = ""; break;
                case HSSFCellType.BOOLEAN: ret = "[cell.BooleanCellValue]"; break;
                case HSSFCellType.NUMERIC:
                    try
                    {
                        if (cell.ToString().Contains("/"))
                        {
                            ret = cell.DateCellValue.ToString();
                        }
                        else
                        {
                            ret = cell.NumericCellValue.ToString();
                        }
                    }
                    catch (Exception e)
                    {
                        ret = cell.NumericCellValue.ToString();
                    }
                    //This is a trick to get the correct value of the cell. NumericCellValue will return a numeric value no matter the cell value is a date or a number.
                    break;
                case HSSFCellType.STRING: ret = cell.StringCellValue; break;
                case HSSFCellType.ERROR: ret = "[cell.ErrorCellValue]"; break;
                case HSSFCellType.FORMULA: ret = cell.StringCellValue; break;//如果是公式，则返回值
                default: ret = "=" + cell.CellFormula; break;
            }
            return ret;
        }
        #endregion

        #region ExportExcelByWeb：导出WebControl.GridView数据到Excel，隐藏的数据列不导出
        /// <summary>
        /// 导出WebControl.GridView数据到Excel，隐藏的数据列不导出
        /// </summary>
        /// <typeparam name="T">泛型对象</typeparam>
        /// <param name="gridView">填充数据的WebControl.GridView</param>
        /// <param name="listObj">需要导出的泛型对象集合列表</param>
        public static void ExportExcelByWeb<T>(GridView gridView, IList<T> listObj)
        {
            ExportExcelByWeb<T>(gridView, listObj, "", "");
        }

        /// <summary>
        /// 导出WebControl.GridView数据到Excel，隐藏的数据列不导出
        /// </summary>
        /// <typeparam name="T">泛型对象</typeparam>
        /// <param name="gridView">填充数据的WebControl.GridView</param>
        /// <param name="listObj">需要导出的泛型对象集合列表</param>
        /// <param name="strHeaderText">导出Excel的标题，默认为“Excel数据列表”</param>
        /// <param name="strFileName">导出的Excel文件名称，默认为“ExportExcelFile”</param>
        public static void ExportExcelByWeb<T>(GridView gridView, IList<T> listObj, string strHeaderText, string strFileName)
        {
            if (string.IsNullOrEmpty(strHeaderText)) strHeaderText = "Excel数据列表";
            if (string.IsNullOrEmpty(strFileName)) strFileName = "ExportExcelFile";
            DataTable dt = null;//ConvertTypeUtil.ConvertListEntityToDataTable<T>(listObj);
            //实现将数据表中的数据填充到根据gridview的col属性解析后的DataTable中
            DataTable dtExportData = new DataTable();
            Dictionary<string, string> cols = new Dictionary<string, string>();
            foreach (object tcol in gridView.Columns)
            {
                if (tcol is System.Web.UI.WebControls.TemplateField)
                {
                    System.Web.UI.WebControls.TemplateField tempcol = tcol as System.Web.UI.WebControls.TemplateField;
                    if (tempcol.Visible && tempcol.SortExpression.Length > 0)
                    {
                        cols.Add(tempcol.SortExpression, tempcol.HeaderText);
                        dtExportData.Columns.Add(tempcol.SortExpression);
                    }
                }
                else if (tcol is BoundField)
                {
                    BoundField BoundFieldLogID = tcol as BoundField;
                    string bb = BoundFieldLogID.DataField;
                }
            }

            //实现根据GridView中的col属性进行数据列筛选，并且另存到筛选后的DataTable
            foreach (DataRow dr in dt.Rows)
            {
                DataRow drExportData = dtExportData.NewRow();
                foreach (KeyValuePair<string, string> keyValue in cols)
                {
                    drExportData[keyValue.Key] = dr[keyValue.Key];
                }
                dtExportData.Rows.Add(drExportData);
            }
            ExportByWeb(dtExportData, strFileName, strHeaderText);
        }
        #endregion

        #region GenerateExcelTemplate:生成excel数据模板
        /// <summary>
        /// 生成模版文件
        /// </summary>
        /// <param name="filePath">原始模版文件路径</param>
        /// <param name="dtSourceArr">Tabname为sheet名，namespace为表头</param>
        /// <returns></returns>
        public static MemoryStream GenerateExcelTemplate(string filePath, List<DataTable> dtSourceArr)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();

            using (FileStream fileStream = new FileStream(filePath, FileMode.Open, FileAccess.ReadWrite))
            {
                workbook = new HSSFWorkbook(fileStream);
            }
            #region 右击文件 属性信息
            {
                DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                dsi.Company = "";//公司
                workbook.DocumentSummaryInformation = dsi;

                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                si.Author = ""; //填加xls文件作者信息
                si.ApplicationName = ""; //填加xls文件创建程序信息
                si.LastAuthor = ""; //填加xls文件最后保存者信息
                si.Comments = ""; //填加xls文件作者信息
                si.Title = ""; //填加xls文件标题信息
                si.Subject = "";//填加文件主题信息
                si.CreateDateTime = DateTime.Now;
                workbook.SummaryInformation = si;
            }
            #endregion

            HSSFCellStyle dateStyle = workbook.CreateCellStyle();
            HSSFDataFormat format = workbook.CreateDataFormat();
            //dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
            for (int  ti=0 ;ti< dtSourceArr.Count;ti++)
            {
               var table=dtSourceArr[ti];
                
                HSSFSheet sheet = workbook.CreateSheet(table.TableName);
                workbook.SetSheetOrder(table.TableName, ti);

                //取得列宽
                int[] arrColWidth = new int[table.Columns.Count];
                foreach (DataColumn item in table.Columns)
                {                                               /*gb2312*/
                    arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
                }
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    for (int j = 0; j < table.Columns.Count; j++)
                    {
                        int intTemp = Encoding.GetEncoding(936).GetBytes(table.Rows[i][j].ToString()).Length;
                        if (intTemp > arrColWidth[j])
                        {
                            arrColWidth[j] = intTemp;
                        }
                    }
                }

                int rowIndex = 0;

                foreach (DataRow row in table.Rows)
                {
                    #region 新建表，填充表头，填充列头，样式
                    if (rowIndex == 65535 || rowIndex == 0)
                    {
                        if (rowIndex != 0)
                        {
                            sheet = workbook.CreateSheet();
                        }

                        #region 表头及样式
                        {
                            HSSFRow headerRow = sheet.CreateRow(0);
                            headerRow.HeightInPoints = 25;
                            headerRow.CreateCell(0).SetCellValue(table.Namespace);

                            HSSFCellStyle headStyle = workbook.CreateCellStyle();
                            headStyle.Alignment = CellHorizontalAlignment.CENTER;
                            HSSFFont font = workbook.CreateFont();
                            font.FontHeightInPoints = 20;
                            font.Boldweight = 700;
                            headStyle.SetFont(font);

                            headerRow.GetCell(0).CellStyle = headStyle;

                            sheet.AddMergedRegion(new Region(0, 0, 0, table.Columns.Count - 1));
                            headerRow.Dispose();
                        }
                        #endregion


                        #region 列头及样式
                        {
                            HSSFRow headerRow = sheet.CreateRow(1);


                            HSSFCellStyle headStyle = workbook.CreateCellStyle();
                            headStyle.Alignment = CellHorizontalAlignment.CENTER;
                            headStyle.BorderBottom = CellBorderType.THIN;
                            headStyle.BorderLeft = CellBorderType.THIN;
                            headStyle.BorderRight = CellBorderType.THIN;
                            headStyle.BorderTop = CellBorderType.THIN;
                            HSSFFont font = workbook.CreateFont();
                            font.FontHeightInPoints = 10;
                            font.Boldweight = 700;
                            headStyle.SetFont(font);


                            foreach (DataColumn column in table.Columns)
                            {
                                headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                                headerRow.GetCell(column.Ordinal).CellStyle = headStyle;

                                //设置列宽
                                sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);

                            }
                            headerRow.Dispose();
                        }
                        #endregion

                        rowIndex = 2;
                    }
                    #endregion


                    #region 填充内容
                    foreach (DataColumn column in table.Columns)
                    {
                        HSSFRow dataRow = sheet.CreateRow(rowIndex);
                        HSSFCell newCell = dataRow.CreateCell(column.Ordinal);

                        newCell.CellStyle.BorderBottom = CellBorderType.THIN;
                        newCell.CellStyle.BorderLeft = CellBorderType.THIN;
                        newCell.CellStyle.BorderRight = CellBorderType.THIN;
                        newCell.CellStyle.BorderTop = CellBorderType.THIN;

                        string drValue = row[column].ToString();

                        switch (column.DataType.ToString())
                        {
                            case "System.String"://字符串类型
                                newCell.SetCellValue(drValue);
                                break;
                            case "System.DateTime"://日期类型
                                DateTime dateV;
                                DateTime.TryParse(drValue, out dateV);
                                newCell.SetCellValue(dateV);

                                newCell.CellStyle = dateStyle;//格式化显示
                                break;
                            case "System.Boolean"://布尔型
                                bool boolV = false;
                                bool.TryParse(drValue, out boolV);
                                newCell.SetCellValue(boolV);
                                break;
                            case "System.Int16"://整型
                            case "System.Int32":
                            case "System.Int64":
                            case "System.Byte":
                                int intV = 0;
                                int.TryParse(drValue, out intV);
                                newCell.SetCellValue(intV);
                                break;
                            case "System.Decimal"://浮点型
                            case "System.Double":
                                double doubV = 0;
                                double.TryParse(drValue, out doubV);
                                newCell.SetCellValue(doubV);
                                break;
                            case "System.DBNull"://空值处理
                                newCell.SetCellValue("");
                                break;
                            default:
                                newCell.SetCellValue("");
                                break;
                        }

                    }
                    #endregion

                    rowIndex++;
                }
            }


            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;

                //sheet.Dispose();
                workbook.Dispose();
                return ms;
            }
        }
        #endregion
    }
}