OrderSystem/Framework/Bowin.Common/Office/AsposeExcelHelper.cs

using Aspose.Cells;
using Aspose.Cells.Drawing;
using NPOI.SS.Formula.Functions;
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

namespace Bowin.Common.Office
{
    public class AsposeExcelHelper
    {

        private List<IGrouping<Func<Workbook, Style>, AsposeExcelCellStyle>> _cellStyleListField;
        private List<IGrouping<Func<Workbook, Style>, AsposeExcelCellStyle>> _cellStyleList
        {
            get
            {
                if (_cellStyleListField == null)
                {
                    _cellStyleListField = new List<IGrouping<Func<Workbook, Style>, AsposeExcelCellStyle>>();
                }
                return _cellStyleListField;
            }
            set
            {
                _cellStyleListField = value;
            }
        }
        private static Regex NumberRegex
        {
            get
            {
                return new Regex("^-?[0-9]+(\\.[0-9]*[1-9]){0,1}$");
            }
        }
        private static Regex DecimalRegex
        {
            get
            {
                return new Regex("^-?[0-9]+(\\.[0-9]*){0,1}$");
            }
        }

        public void PresetCellStyle(List<AsposeExcelCellStyle> cellStyleList)
        {
            var styleGroup = cellStyleList.GroupBy(x => x.StyleFunc).ToList();

            this._cellStyleList = styleGroup;
        }

        public MemoryStream ExportNoTemplate(List<CellItem> cellItems, List<ListItem> listItems)
        {
            return ExportNoTemplate(cellItems, listItems, new List<PictureItem>());
        }
        public MemoryStream ExportNoTemplate(List<CellItem> cellItems, List<ListItem> listItems, List<PictureItem> pictureItems)
        {
            return ExportNoTemplate(cellItems, listItems, pictureItems, new List<CellArea>());
        }
        public MemoryStream ExportNoTemplate(List<CellItem> cellItems, List<ListItem> listItems, List<PictureItem> pictureItems, List<CellArea> mergeList)
        {
            return ExportNoTemplate(new List<PageItem>
            {
                new AsposePageItem
                {
                    CellItemList = cellItems,
                    ListItemList = listItems,
                    PictureItemList = pictureItems,
                    MergeList = mergeList
                }
            });
        }
        public MemoryStream ExportNoTemplate(List<PageItem> pageItems)
        {
            Workbook wk;
            wk = new Workbook();
            var result = ExportByWorkbook(wk, pageItems);

            return result;
        }

        public MemoryStream ExportToTemplate(Stream templateFileStream, List<CellItem> cellItems, List<ListItem> listItems)
        {
            return ExportToTemplate(templateFileStream, cellItems, listItems, new List<PictureItem>());
        }
        public MemoryStream ExportToTemplate(Stream templateFileStream, List<CellItem> cellItems, List<ListItem> listItems, List<PictureItem> pictureItems)
        {
            return ExportToTemplate(templateFileStream, cellItems, listItems, pictureItems, new List<CellArea>());
        }
        public MemoryStream ExportToTemplate(Stream templateFileStream, List<CellItem> cellItems, List<ListItem> listItems, List<PictureItem> pictureItems, List<CellArea> mergeList)
        {
            return ExportToTemplate(templateFileStream, new List<PageItem>
            {
                new AsposePageItem
                {
                    CellItemList = cellItems,
                    ListItemList = listItems,
                    PictureItemList = pictureItems,
                    MergeList = mergeList
                }
            });
        }
        public MemoryStream ExportToTemplate(Stream templateFileStream, List<PageItem> pageItems, bool? isAutoFitRows = null, bool? isAutoFitColumns = null)
        {
            Workbook wk;
            wk = new Workbook(templateFileStream);
            var result = ExportByWorkbook(wk, pageItems, isAutoFitRows, isAutoFitColumns);
            templateFileStream.Close();

            return result;
        }
        private MemoryStream ExportByWorkbook(Workbook wk, List<PageItem> pageItems, bool? isAutoFitRows = null, bool? isAutoFitColumns = null)
        {
            Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);

            var templateSheetCount = wk.Worksheets.Count;
            for (int i = templateSheetCount; i < pageItems.Count; i+= templateSheetCount)
            {
                for (int index = 0; index < templateSheetCount; index++)
                {
                    wk.Worksheets.AddCopy(index); //.CopyTo(wk, "Sheet" + (i + 1).ToString(), true, true);
                }
            }

            int sheetIndex = 0;
            pageItems.ForEach(x =>
            {
                int addRows = 0;

                Dictionary<int?, int?> keyValues = new Dictionary<int?, int?>();

                var cellItemStyleList = x.CellItemList.Where(w => w is AsposeCellItem).Where(w => ((AsposeCellItem)w).Style != null).GroupBy(w => ((AsposeCellItem)w).Style)
                    .SelectMany(w => w.Select(v => new AsposeCellStyle(v.Col, v.Row, w.Key.Invoke(wk)))).ToList();
                var listItemStyleList = x.ListItemList.Where(w => w is AsposeListItem).SelectMany(w => ((AsposeListItem)w).StyleList.Select(v => new
                {
                    ListItem = w,
                    v.RowIndex,
                    v.ColumnIndex,
                    v.StyleFunc
                })).Where(w => w.StyleFunc != null).GroupBy(w => w.StyleFunc).SelectMany(w => w.Select(v => new { v.ListItem, v.ColumnIndex, v.RowIndex, CellStyle = w.Key.Invoke(wk) })).ToList();

                Worksheet sheet = wk.Worksheets[sheetIndex];
                if (!string.IsNullOrEmpty(x.Name))
                {
                    sheet.Name = x.Name;
                }
                foreach (var columnWith in x.ColumnWidthList)
                {
                    sheet.Cells.SetColumnWidth(columnWith.Key, columnWith.Value);
                }
                if (x is AsposePageItem)
                {
                    var insertColumnList = ((AsposePageItem)x).InsertColumnItemList;

                    insertColumnList.OrderByDescending(x => x.ColumnIndex).ToList()
                    .ForEach(x => {
                        if (x.InsertCount > 0)
                        {
                            sheet.Cells.InsertColumns(x.ColumnIndex, x.InsertCount);
                        }
                    });
                }

                foreach (var cellItem in x.CellItemList)
                {
                    var cell = sheet.Cells[cellItem.Row, cellItem.Col];
                    if (!string.IsNullOrEmpty(cellItem.Value))
                    {
                        if (cell.GetStyle().Number != 49 && cellItem.Value.Length < 10 && NumberRegex.IsMatch(cellItem.Value))
                        {
                            cell.PutValue(Convert.ToDecimal(cellItem.Value));
                        }
                        else
                        {
                            cell.PutValue(cellItem.Value);
                        }
                    }

                    //if (cellItem is AsposeCellItem)
                    //{
                    //    var customerStyleFunc = ((AsposeCellItem)cellItem).Style;
                    //    if (customerStyleFunc != null)
                    //    {
                    //        cell.SetStyle(customerStyleFunc.Invoke(wk));
                    //    }
                    //}
                    //var style = cell.GetStyle();
                    //if (cell.GetHeightOfValue() > sheet.Cells.GetRowHeight(cell.Row))
                    //{
                    //    style.IsTextWrapped = true;
                    //    sheet.AutoFitRow(cell.Row);
                    //}
                    //cell.SetStyle(style);
                    //cell.GetStyle().IsTextWrapped = true;
                }
                /*if (x is AsposePageItem)
                {
                    foreach (var merge in ((AsposePageItem)x).MergeList)
                    {
                        sheet.Cells.Merge(merge.StartRow, merge.StartColumn, (merge.EndRow - merge.StartRow + 1), (merge.EndColumn - merge.StartColumn + 1));
                    }
                }*/
                foreach (var styleItem in cellItemStyleList)
                {
                    var cell = sheet.Cells[styleItem.RowIndex.Value, styleItem.ColIndex.Value];
                    cell.SetStyle(styleItem.cellStyle);
                }
                foreach (var cellItem in x.CellItemList)
                {
                    var cell = sheet.Cells[cellItem.Row, cellItem.Col];
                    var style = cell.GetStyle();
                    if (cell.GetHeightOfValue() > sheet.Cells.GetRowHeight(cell.Row))
                    {
                        style.IsTextWrapped = true;
                        cell.SetStyle(style);
                        sheet.AutoFitRow(cell.Row);
                    }
                    else
                    {
                        cell.SetStyle(style);
                    }
                }
                foreach (var listItem in x.ListItemList)
                {
                    var customerStyleList = listItemStyleList.Where(x => x.ListItem == listItem).ToList();

                    int startRow = listItem.StartRow + addRows;
                    int curRow = startRow;
                    if (!(listItem is AsposeListItem) || ((AsposeListItem)listItem).IsInsertRow == true)
                    {
                        for (int i = 1; i < listItem.Values.Length; i++)
                        {
                            sheet.Cells.InsertRow(startRow + i);
                            addRows++;
                        }
                    }
                    for (int itemIndex = 0; itemIndex < listItem.Values.Length; itemIndex++)
                    {
                        var item = listItem.Values[itemIndex];
                        var row = sheet.Cells.GetRow(curRow);
                        for (int i = 0; i < item.Length; i++)
                        {
                            var col = listItem.StartColumn + i;
                            if (col > listItem.EndColumn)
                            {
                                break;
                            }
                            var value = item[i];
                            if (value != null && sheet.Cells[curRow, col].GetStyle().Number != 49 && NumberRegex.IsMatch(value))
                            {
                                sheet.Cells[curRow, col].PutValue(Convert.ToDecimal(value));
                            }
                            else
                            {
                                sheet.Cells[curRow, col].PutValue(value);
                            }
                        }
                        curRow++;
                    }
                    if (listItem is AsposeListItem)
                    {
                        var mergeList = ((AsposeListItem)listItem).MergeList;
                        if (mergeList != null)
                        {
                            mergeList = mergeList.OrderBy(x => x.StartRow).ThenBy(x => x.StartColumn).ToList();
                            foreach (var merge in mergeList)
                            {
                                sheet.Cells.Merge(startRow + merge.StartRow, merge.StartColumn, (merge.EndRow - merge.StartRow + 1), (merge.EndColumn - merge.StartColumn + 1));
                            }
                        }
                        if (x is AsposePageItem)
                        {
                            if (((AsposeListItem)listItem).AutoHeightRowAndPx != null)
                            {
                                foreach (var i in ((AsposeListItem)listItem).AutoHeightRowAndPx)
                                {
                                    keyValues.Add(i.Key, i.Value);
                                }
                            }

                        }
                    }
                    foreach (var style in customerStyleList)
                    {
                        var row = sheet.Cells.GetRow(startRow + style.RowIndex);
                        var col = listItem.StartColumn + style.ColumnIndex;
                        var cell = row.GetCellOrNull(col);
                        if (cell == null)
                        {
                            sheet.Cells[startRow + style.RowIndex, col].PutValue("");
                            cell = row.GetCellOrNull(col);
                        }
                        cell.SetStyle(style.CellStyle);
                    }
                    
                    for (int itemIndex = 0; itemIndex < listItem.Values.Length; itemIndex++)
                    {
                        var item = listItem.Values[itemIndex];
                        var row = sheet.Cells.GetRow(startRow + itemIndex);
                        for (int i = 0; i < item.Length; i++)
                        {
                            var col = listItem.StartColumn + i;
                            if (col > listItem.EndColumn)
                            {
                                break;
                            }
                            var cell = row.GetCellOrNull(col);
                            
                            if (cell.GetHeightOfValue() > sheet.Cells.GetRowHeight(cell.Row))
                            {
                                var style = cell.GetStyle();
                                style.IsTextWrapped = true;
                                cell.SetStyle(style);
                                sheet.AutoFitRow(cell.Row);
                            }
                        }
                    }
                }
                if (x.PictureItemList != null)
                {
                    foreach (var pictureItem in x.PictureItemList)
                    {
                        int startRow = pictureItem.StartRow + addRows;
                        int iIndex = sheet.Pictures.Add(pictureItem.StartRow , pictureItem.StartColumn, pictureItem.EndRow , pictureItem.EndColumn, pictureItem.Picture);
                      /*  Aspose.Cells.Drawing.Picture pic = sheet.Pictures[iIndex];
                        pic.Placement = Aspose.Cells.Drawing.PlacementType.FreeFloating;
                        pic.Height = 130;
                        pic.Width = 100;*/
                    }
                }

                if (x is AsposePageItem)
                {
                    foreach (var columnSequence in ((AsposePageItem)x).ColumnSequenceList)
                    {
                        var area = CellArea.CreateCellArea(columnSequence.ColumnName, columnSequence.ColumnName);
                        var validate = sheet.Validations[sheet.Validations.Add(area)];
                        validate.Type = ValidationType.List;
                        validate.Operator = OperatorType.None;
                        validate.InCellDropDown = true;
                        validate.Formula1 = string.Join(',', columnSequence.SequenceList);
                        validate.ShowError = true;
                        validate.AlertStyle = ValidationAlertType.Stop;
                        validate.ErrorTitle = "错误";
                        validate.ErrorMessage = "请选择列表中的拖轮。";
                    }

                    foreach (var formular in ((AsposePageItem)x).FormularList)
                    {
                        var cell = sheet.Cells[formular.Row, formular.Col];
                        if (!string.IsNullOrEmpty(formular.Value))
                        {
                            cell.Formula = "=" + formular.Value;
                        }
                    }
                }

                if (x is AsposePageItem)
                {
                    var headerList = ((AsposePageItem)x).HeaderFormaterList;
                    var footerList = ((AsposePageItem)x).FooterFormaterList;

                    headerList.ForEach(header => {
                        if (header != null)
                        {
                            sheet.PageSetup.SetHeader((int)header.Section, header.Formater.Invoke(sheet.PageSetup.GetHeader((int)header.Section)));
                        }
                    });
                    footerList.ForEach(footer => {
                        if (footer != null)
                        {
                            sheet.PageSetup.SetFooter((int)footer.Section, footer.Formater.Invoke(sheet.PageSetup.GetFooter((int)footer.Section)));
                        }
                    });

                    foreach (var merge in ((AsposePageItem)x).MergeList)
                    {
                        sheet.Cells.Merge(merge.StartRow, merge.StartColumn, (merge.EndRow - merge.StartRow + 1), (merge.EndColumn - merge.StartColumn + 1));
                    }
                }
                //sheet.AutoFitRows();
                if (isAutoFitRows.HasValue && isAutoFitRows.Value == true)
                {
                    sheet.AutoFitRows();
                }
                if (isAutoFitColumns.HasValue && isAutoFitColumns.Value == true)
                {
                    sheet.AutoFitColumns();
                }
                foreach (var k in keyValues)
                {
                    if (k.Key.HasValue)
                    {
                        int colCount = sheet.Cells.MaxColumn;        //获取表页的最大行数
                        sheet.AutoFitRow(k.Key.Value - 1, 0, colCount);
                        if (k.Value != 0)
                            sheet.Cells.SetRowHeightPixel(k.Key.Value - 1, sheet.Cells.GetRowHeightPixel(k.Key.Value - 1) + k.Value.Value);
                    }
                }
                sheetIndex++;
            });

            MemoryStream file = new MemoryStream();
            wk.Save(file, SaveFormat.Xlsx);
            file.Flush();
            return file;
        }

        private Workbook Export(DataTable dtSource, string[] CloumnList, int[] stringColumnList = null)
        {
            Workbook workbook = new Workbook();

            Worksheet sheet1 = workbook.Worksheets[0];
            //默认宽度-高度
            //sheet1.DefaultColumnWidth = 1 * 15;

            Style style = workbook.CreateStyle();
            style.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.FromArgb(0, 0, 0));
            style.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.FromArgb(0, 0, 0));
            style.SetBorder(BorderType.RightBorder, CellBorderType.Thin, Color.FromArgb(0, 0, 0));
            style.SetBorder(BorderType.TopBorder, CellBorderType.Thin, Color.FromArgb(0, 0, 0));
            //居中
            style.HorizontalAlignment = TextAlignmentType.Center;
            style.VerticalAlignment = TextAlignmentType.Center;
            //设置字体
            style.Font.Size = 12;
            style.Font.IsBold = true;

            var customerStyleGroupList = _cellStyleList
                .Select(x => new { Style = x.Key.Invoke(workbook), CellList = x.Select(w => new { w.RowIndex, w.ColumnIndex }).ToList() })
                .ToList();

            if (CloumnList.Length > 0)
            {
                for (int i = 0; i < CloumnList.Length; i++)
                {
                    Cell cell = sheet1.Cells[0, i];
                    cell.PutValue(CloumnList[i]);
                    sheet1.Cells.SetColumnWidth(i, 15);
                }
            }

            if (dtSource.Rows.Count > 0)
            {
                for (int i = 0; i < dtSource.Rows.Count; i++)
                {
                    sheet1.Cells.InsertRow(i + 1);
                    var rowsub = sheet1.Cells.Rows[i + 1];

                    for (int j = 0; j < dtSource.Columns.Count; j++)
                    {
                        var cell = sheet1.Cells[i + 1, j];
                        if (dtSource.Rows[i][j].ToString() != "" && dtSource.Rows[i][j] != null)
                        {
                            if (dtSource.Rows[i][j].ToString().StartsWith("="))
                            {
                                cell.Formula = dtSource.Rows[i][j].ToString();
                            }
                            else
                            {
                                if (DecimalRegex.IsMatch(dtSource.Rows[i][j].ToString()) && cell.GetStyle().Number != 49)
                                {
                                    if (stringColumnList != null && stringColumnList.Contains(j))
                                    {
                                        cell.PutValue(dtSource.Rows[i][j].ToString());
                                    }
                                    else
                                    {
                                        cell.PutValue(dtSource.Rows[i][j].ToString(), true, false);//如果是值类型、那么以原格式输出
                                    }
                                }
                                else
                                {
                                    cell.PutValue(dtSource.Rows[i][j].ToString());
                                }
                            }
                        }
                        else
                        {
                            cell.PutValue(dtSource.Rows[i][j] as string);
                        }

                        var customCellStyle = customerStyleGroupList.FirstOrDefault(x => x.CellList.Any(w => w.ColumnIndex == j && w.RowIndex == i));
                        if (customCellStyle != null)
                        {
                            cell.SetStyle(customCellStyle.Style);
                        }
                    }
                }
            }

            for (int i = 0; i < CloumnList.Length; i++)
            {
                Cell cell = sheet1.Cells[0, i];
                var customCellStyle = customerStyleGroupList.FirstOrDefault(x => x.CellList.Any(w => w.ColumnIndex == i && w.RowIndex == -1));
                if (customCellStyle != null)
                {
                    cell.SetStyle(customCellStyle.Style);
                }
                else
                {
                    cell.SetStyle(style);
                }
            }

            return workbook;
        }

        /// <summary>
        /// 导出
        /// </summary>
        /// <param name="dtSource">数据源</param>
        /// <param name="CloumnList">中文列名列表</param>
        /// <param name="HeaderText">表头</param>
        /// <param name="SavePath">保存路径</param>
        public string ExportToFile(DataTable dtSource, string[] CloumnList, string filePath, int[] stringColumnList = null)
        {
            string fileName = Guid.NewGuid().ToString() + ".xls";

            var workbook = this.Export(dtSource, CloumnList, stringColumnList);
            var fileFullPath = System.IO.Path.Combine(filePath, fileName);

            workbook.Save(fileFullPath);
            return fileName;
        }

        /// <summary>
        /// 导出
        /// </summary>
        /// <param name="dtSource">数据源</param>
        /// <param name="CloumnList">中文列名列表</param>
        /// <param name="HeaderText">表头</param>
        /// <param name="SavePath">保存路径</param>
        public MemoryStream ExportToStream(DataTable dtSource, string[] CloumnList, int[] stringColumnList = null)
        {
            var workbook = this.Export(dtSource, CloumnList, stringColumnList);
            var stream = workbook.SaveToStream();
            return stream;
        }

        /// <summary>
        /// 将指定路径的文件，写入table中，带列标题，返回该DataTable
        /// </summary>
        /// <param name="FilePath"></param>
        /// <returns></returns>
        public DataTable Import(string filePath)
        {
            var stream = File.Open(filePath, FileMode.Open);

            return Import(stream);
        }

        /// <summary>
        /// 将指定路径的文件，写入table中，带列标题，返回该DataTable
        /// </summary>
        /// <param name="FilePath"></param>
        /// <returns></returns>
        public DataTable Import(Stream fileStream, int firstRow = 0, int firstColumn = 0)
        {
            Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
            DataTable dt = new DataTable();
            Workbook wk = new Workbook(fileStream);
            Worksheet sheet = wk.Worksheets[0];

            int rowCount = sheet.Cells.MaxDataRow + 1;
            int columnCount = sheet.Cells.Rows[firstRow].LastDataCell.Column + 1;
            dt = sheet.Cells.ExportDataTable(firstRow, firstColumn, rowCount, columnCount, true);
            sheet = null;
            wk = null;

            return dt;
        }
        public DataTable ImportAsString(Stream fileStream, int firstRow = 0, int firstColumn = 0)
        {
            Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
            DataTable dt = new DataTable();
            Workbook wk = new Workbook(fileStream);
            Worksheet sheet = wk.Worksheets[0];

            int rowCount = sheet.Cells.MaxDataRow + 1;
            int columnCount = sheet.Cells.Rows[firstRow].LastDataCell.Column + 1;
            dt = sheet.Cells.ExportDataTableAsString(firstRow, firstColumn, rowCount, columnCount, true);
            sheet = null;
            wk = null;

            return dt;
        }
        public string RepeatColumn(Stream fileStream, int firstRow = 0)
        {
            Workbook wk = new Workbook(fileStream);
            Worksheet sheet = wk.Worksheets[0];

            List<string> columnList = new List<string>();
            var columns = sheet.Cells.Rows[firstRow];
            for (int i = 0; i <= columns.LastDataCell.Column; i++)
            {
                columnList.Add(columns.GetCellOrNull(i).Value?.ToString());
            }
            sheet = null;
            wk = null;

            return string.Join(",", columnList.GroupBy(g => g).Where(s => s.Count() > 1).Select(s => s.Key).ToList());
        }
    }

    public static class AsposeExcelHelperExtensions
    {
        public static string ToExcelColumnName(this int index)
        {
            if (index < 0) { throw new Exception("列号必须是从0开始的整数"); }
            List<string> chars = new List<string>();
            do
            {
                if (chars.Count > 0) index--;
                chars.Insert(0, ((char)(index % 26 + (int)'A')).ToString());
                index = (int)((index - index % 26) / 26);
            } while (index > 0);
            return String.Join(string.Empty, chars.ToArray());
        }

        public static string ToCellName(this AsposeExcelCellPosition position)
        {
            var index = position.ColumnIndex;
            if (position.RowIndex < 0) { throw new Exception("行号必须是从0开始的整数"); }
            return index.ToExcelColumnName() + (position.RowIndex + 1).ToString();
        }

        public static string ToExcelFile(this MemoryStream stream, string physicalPath)
        {
            var fileName = Guid.NewGuid().ToString() + ".xlsx";
            var excelFile = new FileStream(Path.Combine(physicalPath, fileName), FileMode.OpenOrCreate, FileAccess.ReadWrite);
            stream.WriteTo(excelFile);
            excelFile.Flush();
            excelFile.Close();

            return fileName;
        }
    }
}