DataTable to Excel（使用NPOI、EPPlus将数据表中的数据读取到excel格式内存中）

        /// <summary>
        /// DataTable to Excel（将数据表中的数据读取到excel格式内存中）
        /// </summary>
        /// <param name="dataTable">数据表</param>
        /// <param name="excelType">excel格式</param>
        /// <param name="sheetName">excel工作表名称</param>
        /// <returns>内存流数据</returns>
        public static Stream DataTableToExcel(DataTable dataTable, string excelType = ".xlsx", string sheetName = "sheet1")
        {
            Stream stream;
            try
            {
                //根据excel文件类型创建excel数据结构
                switch (excelType)
                {
                    case ".xlsx":
                        stream = DataTableToExcelXlsx(dataTable, sheetName);
                        break;
                    case ".xls":
                        stream = DataTableToExcelXls(dataTable, sheetName);
                        break;
                    default:
                        stream = null;
                        break;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return stream;
        }

2007版本使用EPPlus创建excel内存数据

        /// <summary>
        /// DataTable to Excel2007（将数据表中的数据读取到excel格式内存中）
        /// </summary>
        /// <param name="dataTable">数据表</param>
        /// <param name="sheetName">excel工作表名称</param>
        /// <returns>内存流数据</returns>
        public static Stream DataTableToExcelXlsx(DataTable dataTable, string sheetName)
        {
            try
            {
                using (ExcelPackage pck = new ExcelPackage())
                {
                    ExcelWorksheet ws = pck.Workbook.Worksheets.Add(sheetName);
                    ws.Cells["A1"].LoadFromDataTable(dataTable, true);
                    MemoryStream ms = new MemoryStream();
                    pck.SaveAs(ms);
                    ms.Flush();
                    ms.Position = 0;//指定当前流的位置从0开始
                    return ms;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

97-2003版本使用NPOI创建excel内存数据

        /// <summary>
        /// DataTable to Excel97-2003（将数据表中的数据读取到excel格式内存中）
        /// </summary>
        /// <param name="dataTable">数据表</param>
        /// <param name="sheetName">excel工作表名称</param>
        /// <returns>内存流数据</returns>
        public static Stream DataTableToExcelXls(DataTable dataTable, string sheetName)
        {
            try
            {
                const int startIndex = 0;
                var fields = dataTable.Columns;
                //创建excel数据结构
                var workbook = new HSSFWorkbook();
                //创建excel工作表
                var sheet = workbook.CreateSheet(sheetName);
                sheet.DefaultRowHeight = 200 * 20;
                #region 创建标题行
                var row = sheet.CreateRow(startIndex);
                var headStyle = GetHeadStyle(workbook);
                foreach (DataColumn column in dataTable.Columns)
                {
                    var cellIndex = fields.IndexOf(column) + startIndex;
                    var cell = row.CreateCell(cellIndex);
                    cell.SetCellValue(column.ColumnName);
                    cell.CellStyle = headStyle;
                    sheet.AutoSizeColumn(cellIndex);
                }
                #endregion
                #region 创建数据行
                int rowIndex = 0;
                foreach (DataRow dataRow in dataTable.Rows)
                {
                    row = sheet.CreateRow(rowIndex + 1);
                    foreach (DataColumn column in dataTable.Columns)
                    {
                        var cellIndex = fields.IndexOf(column) + startIndex;
                        var dataStyle = GetDataStyle(workbook);
                        var cell = row.CreateCell(cellIndex);
                        cell.CellStyle = dataStyle;
                        var value = dataRow[column.ColumnName];
                        switch ((value ?? string.Empty).GetType().Name.ToLower())
                        {
                            case "int32":
                            case "int64":
                            case "decimal":
                                dataStyle.Alignment = HorizontalAlignment.RIGHT;
                                cell.SetCellValue(ZConvert.To<double>(value, 0));
                                break;
                            default:
                                cell.CellStyle.Alignment = HorizontalAlignment.LEFT;
                                cell.SetCellValue(ZConvert.ToString(value));
                                break;
                        }
                    }
                    rowIndex++;
                }
                #endregion
                #region 将数据写到内存数据流
                MemoryStream ms = new MemoryStream();
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;//指定当前流的位置从0开始

                workbook = null;
                sheet = null;
                row = null;
                #endregion
                return ms;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

 

        /// <summary>
        /// 设置excel表头样式
        /// </summary>
        /// <param name="workbook"></param>
        /// <returns></returns>
        private static ICellStyle GetHeadStyle(HSSFWorkbook workbook)
        {
            //表头样式
            var headStyle = workbook.CreateCellStyle();
            headStyle.Alignment = HorizontalAlignment.LEFT;//居中对齐
            //表头单元格背景色
            headStyle.FillForegroundColor = HSSFColor.LIGHT_GREEN.index;
            headStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
            //表头单元格边框
            headStyle.BorderTop = BorderStyle.THIN;
            headStyle.TopBorderColor = HSSFColor.BLACK.index;
            headStyle.BorderRight = BorderStyle.THIN;
            headStyle.RightBorderColor = HSSFColor.BLACK.index;
            headStyle.BorderBottom = BorderStyle.THIN;
            headStyle.BottomBorderColor = HSSFColor.BLACK.index;
            headStyle.BorderLeft = BorderStyle.THIN;
            headStyle.LeftBorderColor = HSSFColor.BLACK.index;
            //表头字体设置
            var font = workbook.CreateFont();
            font.FontHeightInPoints = 12;//字号
            font.Boldweight = 600;//加粗
            //font.Color = HSSFColor.WHITE.index;//颜色
            headStyle.SetFont(font);

            return headStyle;
        }

 

        /// <summary>
        /// 设置excel数据行样式
        /// </summary>
        /// <param name="workbook"></param>
        /// <returns></returns>
        private static ICellStyle GetDataStyle(HSSFWorkbook workbook)
        {
            //数据样式
            var dataStyle = workbook.CreateCellStyle();
            dataStyle.Alignment = HorizontalAlignment.LEFT;//左对齐
            //数据单元格的边框
            dataStyle.BorderTop = BorderStyle.THIN;
            dataStyle.TopBorderColor = HSSFColor.BLACK.index;
            dataStyle.BorderRight = BorderStyle.THIN;
            dataStyle.RightBorderColor = HSSFColor.BLACK.index;
            dataStyle.BorderBottom = BorderStyle.THIN;
            dataStyle.BottomBorderColor = HSSFColor.BLACK.index;
            dataStyle.BorderLeft = BorderStyle.THIN;
            dataStyle.LeftBorderColor = HSSFColor.BLACK.index;
            //数据的字体
            var datafont = workbook.CreateFont();
            datafont.FontHeightInPoints = 11;//字号
            dataStyle.SetFont(datafont);

            return dataStyle;
        }