public static class NPOIExcelHelper    {        /// <summary>           /// DataTable导出到Excel文件           /// </summary>           /// <param name="dtSource">源DataTable</param>           /// <param name="strHeaderText">表头文本</param>           /// <param name="strFileName">保存位置</param>        /// <param name="strSheetName">工作表名称</param>        /// <Author>柳永法 http://www.yongfa365.com/ 2010-5-8 22:21:41</Author>           public static void Export(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName, string[] oldColumnNames, string[] newColumnNames)        {            if (strSheetName == "")            {                strSheetName = "Sheet";            }            using (MemoryStream ms = Export(dtSource, strHeaderText, strSheetName, oldColumnNames, newColumnNames))            {                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))                {                    byte[] data =http://www.mamicode.com/ ms.ToArray();                    fs.Write(data, 0, data.Length);                    fs.Flush();                }            }        }        /// <summary>           /// DataTable导出到Excel的MemoryStream           /// </summary>           /// <param name="dtSource">源DataTable</param>           /// <param name="strHeaderText">表头文本</param>           /// <param name="strSheetName">工作表名称</param>        /// <Author>柳永法 http://www.yongfa365.com/ 2010-5-8 22:21:41</Author>           public static MemoryStream Export(DataTable dtSource, string strHeaderText, string strSheetName, string[] oldColumnNames, string[] newColumnNames)        {            if (oldColumnNames.Length != newColumnNames.Length)            {                return new MemoryStream();            }            HSSFWorkbook workbook = new HSSFWorkbook();            //HSSFSheet sheet = workbook.CreateSheet();// workbook.CreateSheet();            ISheet sheet = workbook.CreateSheet(strSheetName);            #region 右击文件 属性信息            {                DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();                dsi.Company = "http://www.rongzi.com/";                workbook.DocumentSummaryInformation = dsi;                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();                if (HttpContext.Current.Session["realname"] != null)                {                    si.Author = HttpContext.Current.Session["realname"].ToString();                }                else                {                    if (HttpContext.Current.Session["username"] != null)                    {                        si.Author = HttpContext.Current.Session["username"].ToString();                    }                }                                       //填加xls文件作者信息                   si.ApplicationName = "东方融资网";            //填加xls文件创建程序信息                   si.LastAuthor = "融管系统";           //填加xls文件最后保存者信息                   si.Comments = "融管系统自建文件";      //填加xls文件作者信息                   si.Title = strHeaderText;               //填加xls文件标题信息                   si.Subject = strHeaderText;              //填加文件主题信息                   si.CreateDateTime = DateTime.Now;                workbook.SummaryInformation = si;            }            #endregion            ICellStyle dateStyle = workbook.CreateCellStyle();            IDataFormat format = workbook.CreateDataFormat();            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");            #region 取得列宽            int[] arrColWidth = new int[oldColumnNames.Length];            for (int i = 0; i < oldColumnNames.Length; i++)            {                arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(newColumnNames[i]).Length;            }            /*            foreach (DataColumn item in dtSource.Columns)            {                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 < oldColumnNames.Length; j++)                {                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][oldColumnNames[j]].ToString()).Length;                    if (intTemp > arrColWidth[j])                    {                        arrColWidth[j] = intTemp;                    }                }                /*                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;                    }                }                 * */            }            #endregion            int rowIndex = 0;            foreach (DataRow row in dtSource.Rows)            {                #region 新建表，填充表头，填充列头，样式                if (rowIndex == 65535 || rowIndex == 0)                {                    if (rowIndex != 0)                    {                        sheet = workbook.CreateSheet(strSheetName + ((int)rowIndex / 65535).ToString());                    }                    #region 表头及样式                    {                        IRow headerRow = sheet.CreateRow(0);                        headerRow.HeightInPoints = 25;                        headerRow.CreateCell(0).SetCellValue(strHeaderText);                        ICellStyle headStyle = workbook.CreateCellStyle();                        headStyle.Alignment = HorizontalAlignment.CENTER;                        IFont 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));                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));                    }                    #endregion                    #region 列头及样式                    {                        //HSSFRow headerRow = sheet.CreateRow(1);                        IRow headerRow = sheet.CreateRow(1);                        ICellStyle headStyle = workbook.CreateCellStyle();                        headStyle.Alignment = HorizontalAlignment.CENTER;                        IFont font = workbook.CreateFont();                        font.FontHeightInPoints = 10;                        font.Boldweight = 700;                        headStyle.SetFont(font);                        for (int i = 0; i < oldColumnNames.Length; i++)                        {                            headerRow.CreateCell(i).SetCellValue(newColumnNames[i]);                            headerRow.GetCell(i).CellStyle = headStyle;                            //设置列宽                            sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 256);                        }                        /*                        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);                        }                         * */                    }                    #endregion                    rowIndex = 2;                }                #endregion                #region 填充内容                IRow dataRow = sheet.CreateRow(rowIndex);                //foreach (DataColumn column in dtSource.Columns)                for (int i = 0; i < oldColumnNames.Length; i++)                {                    ICell newCell = dataRow.CreateCell(i);                    string drValue = row[oldColumnNames[i]].ToString();                    switch (dtSource.Columns[oldColumnNames[i]].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();                sheet = null;                workbook = null;                //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet                   return ms;            }        }        /// <summary>           /// WEB导出DataTable到Excel           /// </summary>           /// <param name="dtSource">源DataTable</param>           /// <param name="strHeaderText">表头文本</param>           /// <param name="strFileName">文件名</param>           /// <Author>柳永法 http://www.yongfa365.com/ 2010-5-8 22:21:41</Author>           public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName)        {            ExportByWeb(dtSource, strHeaderText, strFileName, "sheet");        }        /// <summary>        /// WEB导出DataTable到Excel        /// </summary>        /// <param name="dtSource">源DataTable</param>        /// <param name="strHeaderText">表头文本</param>        /// <param name="strFileName">输出文件名，包含扩展名</param>        /// <param name="oldColumnNames">要导出的DataTable列数组</param>        /// <param name="newColumnNames">导出后的对应列名</param>        public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName, string[] oldColumnNames, string[] newColumnNames)        {            ExportByWeb(dtSource, strHeaderText, strFileName, "sheet", oldColumnNames, newColumnNames);        }        /// <summary>        /// WEB导出DataTable到Excel        /// </summary>        /// <param name="dtSource">源DataTable</param>        /// <param name="strHeaderText">表头文本</param>        /// <param name="strFileName">输出文件名</param>        /// <param name="strSheetName">工作表名称</param>        public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName)        {            HttpContext curContext = HttpContext.Current;            // 设置编码和附件格式               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));            //生成列            string columns = "";            for (int i = 0; i < dtSource.Columns.Count; i++)            {                if (i > 0)                {                    columns += ",";                }                columns += dtSource.Columns[i].ColumnName;            }            curContext.Response.BinaryWrite(Export(dtSource, strHeaderText, strSheetName, columns.Split(‘,‘), columns.Split(‘,‘)).GetBuffer());            curContext.Response.End();        }        /// <summary>        /// 导出DataTable到Excel        /// </summary>        /// <param name="dtSource">要导出的DataTable</param>        /// <param name="strHeaderText">标题文字</param>        /// <param name="strFileName">文件名，包含扩展名</param>        /// <param name="strSheetName">工作表名</param>        /// <param name="oldColumnNames">要导出的DataTable列数组</param>        /// <param name="newColumnNames">导出后的对应列名</param>        public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName, string[] oldColumnNames, string[] newColumnNames)        {            HttpContext curContext = HttpContext.Current;            // 设置编码和附件格式               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, strSheetName, oldColumnNames, newColumnNames).GetBuffer());            curContext.Response.End();        }        /// <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);            }            ISheet sheet = hssfworkbook.GetSheetAt(0);            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();            IRow headerRow = sheet.GetRow(0);            int cellCount = headerRow.LastCellNum;            for (int j = 0; j < cellCount; j++)            {                ICell cell = headerRow.GetCell(j);                dt.Columns.Add(cell.ToString());            }            for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)            {                IRow 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();                }                dt.Rows.Add(dataRow);            }            return dt;        }        /// <summary>        /// 从Excel中获取数据到DataTable        /// </summary>        /// <param name="strFileName">Excel文件全路径(服务器路径)</param>        /// <param name="SheetName">要获取数据的工作表名称</param>        /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>        /// <returns></returns>        public static DataTable RenderDataTableFromExcel(string strFileName, string SheetName, int HeaderRowIndex)        {            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))            {                IWorkbook workbook = new HSSFWorkbook(file);                return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex);            }        }        /// <summary>        /// 从Excel中获取数据到DataTable        /// </summary>        /// <param name="strFileName">Excel文件全路径(服务器路径)</param>        /// <param name="SheetIndex">要获取数据的工作表序号(从0开始)</param>        /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>        /// <returns></returns>        public static DataTable RenderDataTableFromExcel(string strFileName, int SheetIndex, int HeaderRowIndex)        {            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))            {                IWorkbook workbook = new HSSFWorkbook(file);                string SheetName = workbook.GetSheetName(SheetIndex);                return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex);            }        }        /// <summary>        /// 从Excel中获取数据到DataTable        /// </summary>        /// <param name="ExcelFileStream">Excel文件流</param>        /// <param name="SheetName">要获取数据的工作表名称</param>        /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>        /// <returns></returns>        public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, string SheetName, int HeaderRowIndex)        {            IWorkbook workbook = new HSSFWorkbook(ExcelFileStream);            ExcelFileStream.Close();            return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex);        }        /// <summary>        /// 从Excel中获取数据到DataTable        /// </summary>        /// <param name="ExcelFileStream">Excel文件流</param>        /// <param name="SheetIndex">要获取数据的工作表序号(从0开始)</param>        /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>        /// <returns></returns>        public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, int SheetIndex, int HeaderRowIndex)        {            IWorkbook workbook = new HSSFWorkbook(ExcelFileStream);            ExcelFileStream.Close();            string SheetName = workbook.GetSheetName(SheetIndex);            return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex);        }        /// <summary>        /// 从Excel中获取数据到DataTable        /// </summary>        /// <param name="workbook">要处理的工作薄</param>        /// <param name="SheetName">要获取数据的工作表名称</param>        /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>        /// <returns></returns>        public static DataTable RenderDataTableFromExcel(IWorkbook workbook, string SheetName, int HeaderRowIndex)        {            ISheet sheet = workbook.GetSheet(SheetName);            DataTable table = new DataTable();            try            {                IRow 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;                #region 循环各行各列,写入数据到DataTable                for (int i = (sheet.FirstRowNum + 1); i < sheet.LastRowNum; i++)                {                    IRow row = sheet.GetRow(i);                    DataRow dataRow = table.NewRow();                    for (int j = row.FirstCellNum; j < cellCount; j++)                    {                        ICell cell = row.GetCell(j);                        if (cell == null)                        {                            dataRow[j] = null;                        }                        else                        {                            //dataRow[j] = cell.ToString();                            switch (cell.CellType)                            {                                case CellType.BLANK:                                    dataRow[j] = null;                                    break;                                case CellType.BOOLEAN:                                    dataRow[j] = cell.BooleanCellValue;                                    break;                                case CellType.NUMERIC:                                    dataRow[j] = cell.ToString();                                    break;                                case CellType.STRING:                                    dataRow[j] = cell.StringCellValue;                                    break;                                case CellType.ERROR:                                    dataRow[j] = cell.ErrorCellValue;                                    break;                                case CellType.FORMULA:                                default:                                    dataRow[j] = "=" + cell.CellFormula;                                    break;                            }                        }                    }                    table.Rows.Add(dataRow);                    //dataRow[j] = row.GetCell(j).ToString();                }                #endregion            }            catch (System.Exception ex)            {                table.Clear();                table.Columns.Clear();                table.Columns.Add("出错了");                DataRow dr = table.NewRow();                dr[0] = ex.Message;                table.Rows.Add(dr);                return table;            }            finally            {                //sheet.Dispose();                workbook = null;                sheet = null;            }            #region 清除最后的空行            for (int i = table.Rows.Count - 1; i > 0; i--)            {                bool isnull = true;                for (int j = 0; j < table.Columns.Count; j++)                {                    if (table.Rows[i][j] != null)                    {                        if (table.Rows[i][j].ToString() != "")                        {                            isnull = false;                            break;                        }                    }                }                if (isnull)                {                    table.Rows[i].Delete();                }            }            #endregion            return table;        }    }