namespace ExcelTest{    using System;    using System.Collections.Generic;    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 NPOI.SS.UserModel;    using NPOI.SS.Util;    public class ExcelHelper    {        /// <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 =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>        public static MemoryStream Export(DataTable dtSource, string strHeaderText)        {            HSSFWorkbook workbook = new HSSFWorkbook();            HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;            #region 右击文件 属性信息            {                DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();                dsi.Company = "tewuapple";                workbook.DocumentSummaryInformation = dsi;                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();                si.Author = "sunzq"; //填加xls文件作者信息                si.ApplicationName = "NPOI测试程序"; //填加xls文件创建程序信息                si.LastAuthor = "孙自强"; //填加xls文件最后保存者信息                si.Comments = "说明信息"; //填加xls文件作者信息                si.Title = "NPOI测试"; //填加xls文件标题信息                si.Subject = "NPOI测试Demo";//填加文件主题信息                si.CreateDateTime = DateTime.Now;                workbook.SummaryInformation = si;            }            #endregion            HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;            HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");            //取得列宽            int[] arrColWidth = new int[dtSource.Columns.Count];            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 < 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() as HSSFSheet;                    }                    #region 表头及样式                    {                        HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;                        headerRow.HeightInPoints = 25;                        headerRow.CreateCell(0).SetCellValue(strHeaderText);                        HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;                        headStyle.Alignment = HorizontalAlignment.CENTER;                        HSSFFont font = workbook.CreateFont() as HSSFFont;                        font.FontHeightInPoints = 20;                        font.Boldweight = 700;                        headStyle.SetFont(font);                        headerRow.GetCell(0).CellStyle = headStyle;                        sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));                    }                    #endregion                    #region 列头及样式                    {                        HSSFRow headerRow = sheet.CreateRow(1) as HSSFRow;                        HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;                        headStyle.Alignment = HorizontalAlignment.CENTER;                        HSSFFont font = workbook.CreateFont() as HSSFFont;                        font.FontHeightInPoints = 10;                        font.Boldweight = 700;                        headStyle.IsLocked = true;                        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);                        }                        sheet.CreateFreezePane(0, 2, 0, dtSource.Columns.Count - 1);                    }                    #endregion                    rowIndex = 2;                }                #endregion                #region 填充内容                HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;                foreach (DataColumn column in dtSource.Columns)                {                    HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell;                    string drValue =http://www.mamicode.com/ 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;                return ms;            }        }        /// <summary>        /// 用于Web导出        /// </summary>        /// <param name="dtSource">源DataTable</param>        /// <param name="strHeaderText">表头文本</param>        /// <param name="strFileName">文件名</param>        public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName)        {            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).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);            }            HSSFSheet sheet = hssfworkbook.GetSheetAt(0) as HSSFSheet;            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();            HSSFRow headerRow = sheet.GetRow(0) as HSSFRow;            int cellCount = headerRow.LastCellNum;            for (int j = 0; j < cellCount; j++)            {                HSSFCell cell = headerRow.GetCell(j) as HSSFCell;                dt.Columns.Add(cell.ToString());            }            for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)            {                HSSFRow row = sheet.GetRow(i) as HSSFRow;                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;        }    }}