MTP2.0_CSharp/Muchinfo.Common/Fx4.0/Muchinfo.ChartClient/MuchInfo.Chart/MuchInfo.Chart.WPF/Helpers/ExportHelper.cs

using Microsoft.Win32;
using MuchInfo.Chart.Data.EnumTypes;
using MuchInfo.Chart.Data.Interfaces;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.IO;
using System.Linq;

namespace MuchInfo.Chart.WPF.Helpers
{
    public delegate void ExportCompletedEventHandler(string message, string caption);
    public class ExportHelper
    {
        private string _sheetName;
        private List<ILineDataPoint> _dataPoints;
        private int _minUnit;
        private BackgroundWorker _backgroundWorker;
        private string _defaultName;

        public ExportCompletedEventHandler OnExportCompleted;

        public EventHandler OnStartExport;

        public void ExportToExcel(string goodsName, CycleType cycleType, List<ILineDataPoint> dataPoints, int minUnit)
        {
            if (string.IsNullOrWhiteSpace(goodsName)) return;
            if (dataPoints == null || !dataPoints.Any()) return;

            _minUnit = minUnit;
            _dataPoints = dataPoints;

            var startDate = dataPoints[0].Date.ToString("yyyyMMdd HHmm");
            var endDate = dataPoints[dataPoints.Count - 1].Date.ToString("yyyyMMdd HHmm");
            _sheetName = string.Format(LanguageManager.FindResource(LanguageConst.WorkSheetName), goodsName, GetCycleString(cycleType));
            _defaultName = _sheetName + "--(" + startDate + "-" + endDate + ")";
            //保存到文件
            var dialog = new SaveFileDialog
            {
                FileName = _defaultName,
                DefaultExt = ".xls",
                Filter = "Excel WorkSheet(.xls)|*.xls",
            };

            _backgroundWorker = new BackgroundWorker() { WorkerSupportsCancellation = true };
            _backgroundWorker.DoWork += BackgroundWorker_DoWork;
            _backgroundWorker.RunWorkerCompleted += BackgroundWorker_RunWorkerCompleted;
            var result = dialog.ShowDialog();
            if (OnStartExport != null)
            {
                OnStartExport(null, null);
            }

            if (result == true)
            {
                if (_backgroundWorker.IsBusy)
                {
                    _backgroundWorker.CancelAsync();
                }
                else
                {
                    _backgroundWorker.RunWorkerAsync(dialog.FileName);
                }
            }
            else
            {
                if (OnExportCompleted != null)
                {
                    //message为空，只不弹出信息
                    OnExportCompleted(string.Empty, string.Empty);
                }
            }
        }

        public void Dispose()
        {
            _dataPoints = null;
            if (_backgroundWorker != null)
            {
                _backgroundWorker.Dispose();
                _backgroundWorker = null;
            }
        }

        private void BackgroundWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            var caption = LanguageManager.FindResource(LanguageConst.ExportCaption);
            var message = LanguageManager.FindResource(LanguageConst.ExportSuccess);
            if (e.Error != null)
                message = LanguageManager.FindResource(LanguageConst.ExportFailed);
            else if (e.Cancelled)
                message = LanguageManager.FindResource(LanguageConst.ExportCancelled);

            if (OnExportCompleted != null)
            {
                OnExportCompleted(message + "[" + _sheetName + "]", caption);
            }

            this.Dispose();
        }

        private void BackgroundWorker_DoWork(object sender, DoWorkEventArgs e)
        {
            var fileName = e.Argument as string;
            if (string.IsNullOrWhiteSpace(fileName)) return;
            if (_dataPoints == null || !_dataPoints.Any()) return;

            //OfficeExport(fileName);
            NPOIExport(fileName);
        }

        /// <summary>
        /// Gets the cycle string.
        /// </summary>
        /// <param name="cycleType">Type of the cycle.</param>
        /// <returns>System.String.</returns>
        private string GetCycleString(CycleType cycleType)
        {
            switch (cycleType)
            {
                case CycleType.TimeSharing:
                    return LanguageManager.FindResource(LanguageConst.TimeSpanControl_TimeSharingLine);
                case CycleType.Tik:
                    return LanguageManager.FindResource(LanguageConst.TimeSpanControl_InstantLine);
                default:
                    return cycleType.ToString();
            }
        }

        /// <summary>
        /// 导出excel文件
        /// </summary>
        /// <param name="fileName">Name of the file.</param>
        private void NPOIExport(string fileName)
        {
            var sw = File.OpenWrite(fileName);
            var workbook = new XSSFWorkbook();
            try
            {
                //设置表名长度为32
                var length = _sheetName.Length;
                var sheetName = _sheetName.Substring(0, length > 32 ? 32 : length);
                var sheet = workbook.CreateSheet(sheetName);
                var dataPoints = _dataPoints.OrderByDescending(z => z.Date).ToList();
                var upper = dataPoints.Count;

                //构建第一行
                var font1 = workbook.CreateFont();
                font1.Color = HSSFColor.RoyalBlue.Index;
                font1.Boldweight = (short)FontBoldWeight.Bold;
                font1.FontHeightInPoints = 14;
                var style1 = workbook.CreateCellStyle();
                style1.FillForegroundColor = HSSFColor.LightTurquoise.Index;
                style1.FillPattern = FillPattern.SolidForeground;
                style1.Alignment = HorizontalAlignment.Center;
                style1.VerticalAlignment = VerticalAlignment.Top;
                style1.SetFont(font1);

                var row1 = sheet.CreateRow(0);
                row1.Height = 2 * 256;
                var cell1 = row1.CreateCell(0);
                cell1.SetCellValue(_defaultName);
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 4));
                cell1.CellStyle = style1;

                //构建第二行
                var font2 = workbook.CreateFont();
                font2.Color = HSSFColor.RoyalBlue.Index;
                font2.Boldweight = (short)FontBoldWeight.Bold;
                font2.FontHeightInPoints = 10;
                var style2 = workbook.CreateCellStyle();
                style2.FillForegroundColor = HSSFColor.LightYellow.Index;
                style2.FillPattern = FillPattern.SolidForeground;
                style2.Alignment = HorizontalAlignment.Center;
                style2.SetFont(font2);

                var row2 = sheet.CreateRow(1);
                var cell21 = row2.CreateCell(0);
                cell21.SetCellValue(LanguageManager.FindResource(LanguageConst.ExportDate));
                var cell22 = row2.CreateCell(1);
                cell22.SetCellValue(LanguageManager.FindResource(LanguageConst.Indicator_QuoteBoard_Open));
                var cell23 = row2.CreateCell(2);
                cell23.SetCellValue(LanguageManager.FindResource(LanguageConst.Indicator_QuoteBoard_High));
                var cell24 = row2.CreateCell(3);
                cell24.SetCellValue(LanguageManager.FindResource(LanguageConst.Indicator_QuoteBoard_Low));
                var cell25 = row2.CreateCell(4);
                cell25.SetCellValue(LanguageManager.FindResource(LanguageConst.Indicator_QuoteBoard_Close));

                cell21.CellStyle = style2;
                cell22.CellStyle = style2;
                cell23.CellStyle = style2;
                cell24.CellStyle = style2;
                cell25.CellStyle = style2;

                sheet.SetColumnWidth(0, 30 * 256);
                sheet.SetColumnWidth(1, 20 * 256);
                sheet.SetColumnWidth(2, 20 * 256);
                sheet.SetColumnWidth(3, 20 * 256);
                sheet.SetColumnWidth(4, 20 * 256);

                //var font3 = workbook.CreateFont();
                //font3.FontHeightInPoints = 10;
                //var style3 = workbook.CreateCellStyle();
                //style3.Alignment = HorizontalAlignment.Center;
                //style3.SetFont(font3);

                //填充数据
                for (int i = 0; i < upper; i++)
                {
                    var point = dataPoints[i] as IOHLCDataPoint;
                    if (point == null) continue;
                    var row = sheet.CreateRow(2 + i);

                    var cell31 = row.CreateCell(0);
                    cell31.SetCellValue(point.Date.ToString());
                    var cell32 = row.CreateCell(1);
                    cell32.SetCellValue(TextFormatting.FormatNumber(point.Open, _minUnit, false));
                    var cell33 = row.CreateCell(2);
                    cell33.SetCellValue(TextFormatting.FormatNumber(point.High, _minUnit, false));
                    var cell34 = row.CreateCell(3);
                    cell34.SetCellValue(TextFormatting.FormatNumber(point.Low, _minUnit, false));
                    var cell35 = row.CreateCell(4);
                    cell35.SetCellValue(TextFormatting.FormatNumber(point.Close, _minUnit, false));

                    //cell31.CellStyle = style3;
                    //cell32.CellStyle = style3;
                    //cell33.CellStyle = style3;
                    //cell34.CellStyle = style3;
                    //cell35.CellStyle = style3;
                }

                workbook.Write(sw);
                sw.Close();
            }
            finally
            {
                sw.Close();
            }
        }

        ///// <summary>
        /////  必须安装office才能使用-放弃
        ///// </summary>
        ///// <param name="fileName">Name of the file.</param>
        //private void OfficeExport(string fileName)
        //{
        //    var xApp = new Microsoft.Office.Interop.Excel.Application();
        //    //创建工作簿（WorkBook：即Excel文件主体本身） 不能使用 new 来实例对象，不然会异常
        //    var xBook = xApp.Workbooks.Add(Missing.Value);

        //    try
        //    {
        //        //创建工作表（Worksheet：工作表，即Excel里的子表sheet）
        //        var worksheet =
        //            (Worksheet)xBook.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);

        //        //设置表名长度为32
        //        var length = _sheetName.Length;
        //        worksheet.Name = _sheetName.Substring(0, length > 32 ? 32 : length);
        //        var dataPoints = _dataPoints.OrderByDescending(z => z.Date).ToList();
        //        var upper = dataPoints.Count;

        //        //构建第一行
        //        var row1 = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[1, 5]];
        //        row1.MergeCells = true;
        //        row1.Font.Size = 14;
        //        row1.Font.Bold = true;
        //        row1.Font.Color = Color.FromArgb(0, 120, 255);
        //        row1.ColumnWidth = 100;
        //        row1.RowHeight = 25;
        //        row1.Cells.Interior.Color = Color.FromArgb(205, 255, 255);
        //        row1.HorizontalAlignment = XlHAlign.xlHAlignCenter;
        //        worksheet.Cells[1, 1] = _defaultName;

        //        //构建第二行
        //        var row2 = worksheet.Range[worksheet.Cells[2, 1], worksheet.Cells[2, 5]];
        //        row2.HorizontalAlignment = XlHAlign.xlHAlignCenter;
        //        row2.Cells.Interior.Color = Color.FromArgb(255, 255, 139);
        //        row2.Font.Size = 10;
        //        row2.Font.Bold = true;
        //        row2.Font.Color = Color.FromArgb(0, 120, 255);
        //        worksheet.Cells[2, 1] = LanguageManager.FindResource(LanguageConst.ExportDate);
        //        worksheet.Cells[2, 2] = LanguageManager.FindResource(LanguageConst.Indicator_QuoteBoard_Open);
        //        worksheet.Cells[2, 3] = LanguageManager.FindResource(LanguageConst.Indicator_QuoteBoard_High);
        //        worksheet.Cells[2, 4] = LanguageManager.FindResource(LanguageConst.Indicator_QuoteBoard_Low);
        //        worksheet.Cells[2, 5] = LanguageManager.FindResource(LanguageConst.Indicator_QuoteBoard_Close);
        //        worksheet.Columns[1].ColumnWidth = 20;
        //        worksheet.Columns[2].ColumnWidth = 20;
        //        worksheet.Columns[3].ColumnWidth = 20;
        //        worksheet.Columns[4].ColumnWidth = 20;
        //        worksheet.Columns[5].ColumnWidth = 20;

        //        //填充数据
        //        for (int i = 0; i < upper; i++)
        //        {
        //            var point = dataPoints[i] as IOHLCDataPoint;
        //            if (point == null) continue;
        //            var row3 = worksheet.Range[worksheet.Cells[i + 3, 1], worksheet.Cells[i + 3, 5]];
        //            row3.Font.Size = 10;

        //            worksheet.Cells[i + 3, 1] = point.Date;
        //            worksheet.Cells[i + 3, 2] = TextFormatting.FormatNumber(point.Open, _minUnit, false);
        //            worksheet.Cells[i + 3, 3] = TextFormatting.FormatNumber(point.High, _minUnit, false);
        //            worksheet.Cells[i + 3, 4] = TextFormatting.FormatNumber(point.Low, _minUnit, false);
        //            worksheet.Cells[i + 3, 5] = TextFormatting.FormatNumber(point.Close, _minUnit, false);
        //        }

        //        xBook.SaveAs(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
        //            XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
        //            Missing.Value);
        //    }
        //    finally
        //    {
        //        xBook.Close(Missing.Value, Missing.Value, Missing.Value);
        //        xApp.Workbooks.Close();
        //        xApp.Quit();

        //        // 强制回收 Excel 进程
        //        int geneID = System.GC.GetGeneration(xApp);
        //        xBook = null;
        //        xApp = null;
        //        System.GC.Collect(geneID);
        //        GC.Collect();
        //    }
        //}
    }
}