BaiXiaoExportDeskClient/DataUpload/ExcelUtil.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System.IO;

namespace DataUpload
{
    public class ExcelUtil
    {
        /// <summary>
        /// DataTable导出到Excel文件
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="headerTextDic">头文本,如不传默认使用DataTable.Columns填充</param>
        /// <param name="fileName">文件名</param>
        public static void Export(DataTable dtSource, Dictionary<string, string> headerTextDic, string fileName)
        {
            if ((headerTextDic == null || headerTextDic.Count==0) && dtSource.Columns.Count > 0) 
            {
                headerTextDic = new Dictionary<string, string>();
                foreach (DataColumn dc in dtSource.Columns) 
                {
                    headerTextDic.Add(dc.ColumnName, dc.ColumnName);
                }
            }
            using (var ms = Export(dtSource, headerTextDic, fileName, false))
            {
                using (var fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
                {
                    var data = ms.ToArray();
                    fs.Write(data, 0, data.Length);
                    fs.Flush();
                }
            }
        }


        /// <summary>
        /// DataTable导出到Excel文件
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="headerTextDic">头文本,如不传默认使用DataTable.Columns填充</param>
        /// <param name="fileName">文件名</param>
        public static void ExportSimple(DataTable dtSource, string[] headerTextDic, string fileName)
        {
            if(dtSource.Columns.Count<headerTextDic.Length) return;
            Dictionary<string, string> ht = new Dictionary<string, string>();
            for (int i=0;i<headerTextDic.Length;i++) 
            {
                ht.Add(dtSource.Columns[i].ColumnName, headerTextDic[i]);
            }
            Export(dtSource, ht, fileName);
        }

        #region private
        /// <summary>
        /// DataTable导出到Excel的MemoryStream
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="headerTextDic">头文本</param>
        /// <param name="fileName">文件名</param>
        /// <param name="isShowTitle">是否显示表头</param>
        /// <returns></returns>
        private static MemoryStream Export(DataTable dtSource, Dictionary<string, string> headerTextDic, string fileName, bool isShowTitle)
        {
            var workbook = new HSSFWorkbook();
            var sheet = workbook.CreateSheet() as HSSFSheet;

            SetSummaryInformation(workbook);

            var dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
            var format = workbook.CreateDataFormat() as HSSFDataFormat;
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

            var arrColWidth = GetColWidth(dtSource, headerTextDic);

            var rowIndex = 0;


            var colHeaderStyle = GetColHeaderStyle(workbook);
            var contentDataStyle = GetContentDataStyle(workbook);

            foreach (DataRow row in dtSource.Rows)
            {
                if (rowIndex == 65536 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet() as HSSFSheet;
                    }
                    SetDataTitle(headerTextDic, fileName, isShowTitle, workbook, sheet);
                    SetColHeader(dtSource, headerTextDic, isShowTitle, workbook, sheet, arrColWidth, colHeaderStyle);
                    if (isShowTitle)
                    {
                        rowIndex = 2;
                    }
                    else
                    {
                        rowIndex = 1;
                    }
                }

                var dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
                SetContentData(dtSource, headerTextDic, dateStyle, row, dataRow, workbook, contentDataStyle);

                rowIndex++;
            }
            using (var ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                return ms;
            }
        }

        /// <summary>
        /// 右击文件/属性信息
        /// </summary>
        /// <param name="workbook"></param>
        private static void SetSummaryInformation(HSSFWorkbook workbook)
        {
            var dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "NPOI";
            workbook.DocumentSummaryInformation = dsi;

            var si = PropertySetFactory.CreateSummaryInformation();
            si.Author = "文件作者信息"; //填加xls文件作者信息
            si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
            si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
            si.Comments = "RDIFramework.NET，基于.NET的快速信息化系统开发、整合框架，给用户和开发者最佳的.Net框架部署方案。http://www.cnblogs.com/huyong"; //填加xls文件作者信息
            si.Title = "标题信息"; //填加xls文件标题信息
            si.Subject = "主题信息";//填加文件主题信息
            si.CreateDateTime = DateTime.Now;
            workbook.SummaryInformation = si;
        }

        /// <summary>
        /// 取得列宽
        /// </summary>
        /// <param name="dtSource"></param>
        /// <param name="headerTextDic"></param>
        /// <returns></returns>
        private static int[] GetColWidth(DataTable dtSource, Dictionary<string, string> headerTextDic)
        {
            var arrColWidth = new int[headerTextDic.Count];
            var index = 0;
            foreach (var item in headerTextDic)
            {
                arrColWidth[index] = Encoding.GetEncoding(936).GetBytes(item.Value).Length;
                index++;
            }


            index = 0;
            foreach (var item in headerTextDic)
            {
                for (var i = 0; i < dtSource.Rows.Count; i++)
                {
                    for (var j = 0; j < dtSource.Columns.Count; j++)
                    {
                        if (item.Key.ToLower() == dtSource.Columns[j].ColumnName.ToLower())
                        {
                            var intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                            if (intTemp > arrColWidth[index])
                            {
                                arrColWidth[index] = intTemp;
                            }
                            break;
                        }
                    }
                }
                index++;
            }

            return arrColWidth;
        }

        /// <summary>
        /// 表头及样式
        /// </summary>
        /// <param name="headerTextDic"></param>
        /// <param name="fileName"></param>
        /// <param name="isShowTitle"></param>
        /// <param name="workbook"></param>
        /// <param name="sheet"></param>
        private static void SetDataTitle(Dictionary<string, string> headerTextDic, string fileName, bool isShowTitle, HSSFWorkbook workbook, HSSFSheet sheet)
        {
            if (isShowTitle)
            {
                var headerRow = sheet.CreateRow(0) as HSSFRow;
                headerRow.HeightInPoints = 25;
                headerRow.CreateCell(0).SetCellValue(fileName);

                var headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
                headStyle.Alignment = HorizontalAlignment.Center;
                var font = workbook.CreateFont() as HSSFFont;
                font.FontHeightInPoints = 20;
                font.Boldweight = 700;
                headStyle.SetFont(font);
                headerRow.GetCell(0).CellStyle = headStyle;
                sheet.AddMergedRegion(new Region(0, 0, 0, headerTextDic.Count - 1));
            }
        }

        /// <summary>
        /// 列头數據
        /// </summary>
        /// <param name="dtSource"></param>
        /// <param name="headerTextDic"></param>
        /// <param name="isShowTitle"></param>
        /// <param name="workbook"></param>
        /// <param name="sheet"></param>
        /// <param name="arrColWidth"></param>
        private static void SetColHeader(DataTable dtSource, Dictionary<string, string> headerTextDic, bool isShowTitle, HSSFWorkbook workbook, HSSFSheet sheet, int[] arrColWidth, HSSFCellStyle colHeaderStyle)
        {
            HSSFRow headerRow = null;
            if (isShowTitle)
            {
                headerRow = sheet.CreateRow(1) as HSSFRow;
            }
            else
            {
                headerRow = sheet.CreateRow(0) as HSSFRow;
            }

            var index = 0;

            foreach (var item in headerTextDic)
            {
                foreach (DataColumn column in dtSource.Columns)
                {
                    if (column.ColumnName.ToLower() == item.Key.ToLower())
                    {
                        headerRow.CreateCell(index).SetCellValue(item.Value);
                        headerRow.GetCell(index).CellStyle = colHeaderStyle;

                        var colWidth = arrColWidth[index];
                        if (colWidth > 254)
                        {
                            colWidth = 254;
                        }

                        sheet.SetColumnWidth(index, (colWidth + 1) * 256);
                        index++;
                        break;
                    }
                }
            }
        }

        /// <summary>
        /// 填充内容
        /// </summary>
        /// <param name="dtSource"></param>
        /// <param name="headerTextDic"></param>
        /// <param name="dateStyle"></param>
        /// <param name="row"></param>
        /// <param name="dataRow"></param>
        private static void SetContentData(DataTable dtSource, Dictionary<string, string> headerTextDic, HSSFCellStyle dateStyle, DataRow row, HSSFRow dataRow, HSSFWorkbook workbook, HSSFCellStyle contentDataStyle)
        {
            var index = 0;
            foreach (var item in headerTextDic)
            {
                foreach (DataColumn column in dtSource.Columns)
                {
                    if (item.Key.ToLower() == column.ColumnName.ToLower())
                    {
                        var newCell = dataRow.CreateCell(index) as HSSFCell;
                        var 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);
                                if (drValue.Contains("上午 12:00:00"))
                                {
                                    drValue = dateV.ToString("yyyy/MM/dd");
                                }
                                else
                                {
                                    if (!string.IsNullOrEmpty(drValue))
                                    {
                                        drValue = dateV.ToString();
                                    }
                                }
                                newCell.SetCellValue(drValue);

                                newCell.CellStyle = dateStyle;//格式化显示
                                break;
                            case "System.Boolean"://布尔型
                                var boolV = false;
                                bool.TryParse(drValue, out boolV);
                                newCell.SetCellValue(boolV);
                                break;
                            case "System.Int16"://整型
                            case "System.Int32":
                            case "System.Int64":
                            case "System.Byte":
                                var 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;
                        }

                        dataRow.GetCell(index).CellStyle = contentDataStyle;

                        index++;
                        break;
                    }
                }
            }
        }

        #region 樣式

        /// <summary>
        /// 列头樣式
        /// </summary>
        /// <param name="workbook"></param>
        /// <returns></returns>
        private static HSSFCellStyle GetColHeaderStyle(HSSFWorkbook workbook)
        {
            var headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
            headStyle.Alignment = HorizontalAlignment.Center;
            var font = workbook.CreateFont() as HSSFFont;
            font.FontHeightInPoints = 10;
            font.Boldweight = 700;
            headStyle.SetFont(font);


            var palette = workbook.GetCustomPalette();
            palette.SetColorAtIndex((short)10, (byte)0, (byte)176, (byte)240);
            headStyle.FillForegroundColor = (short)10;
            headStyle.FillPattern = FillPattern.SolidForeground;

            headStyle.BorderBottom = BorderStyle.Thin;
            headStyle.BorderLeft = BorderStyle.Thin;
            headStyle.BorderTop = BorderStyle.Thin;
            headStyle.BorderRight = BorderStyle.Thin;
            return headStyle;

        }

        /// <summary>
        /// 填充内容樣式
        /// </summary>
        /// <param name="workbook"></param>
        /// <returns></returns>
        private static HSSFCellStyle GetContentDataStyle(HSSFWorkbook workbook)
        {
            var headStyle = workbook.CreateCellStyle() as HSSFCellStyle;

            headStyle.BorderBottom = BorderStyle.Thin;
            headStyle.BorderLeft = BorderStyle.Thin;
            headStyle.BorderTop = BorderStyle.Thin;
            headStyle.BorderRight = BorderStyle.Thin;
            return headStyle;
        }

        #endregion

        #endregion

    }
}