JW/EMIS.Utility/AsposeExcelHelper.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Aspose.Cells;
using System.Data;
using System.IO;
using System.Text.RegularExpressions;
using System.Drawing;

namespace EMIS.Utility
{
    public class AsposeCellStyle
    {
        public int RowIndex;
        public int ColIndex;
        public Style cellStyle;

        public AsposeCellStyle(int aColIndex, int aRowIndex, Style aCellStyle)
        {
            this.RowIndex = aRowIndex;
            this.ColIndex = aColIndex;
            this.cellStyle = aCellStyle;
        }

    }

    public class AsposeExcelCellStyle
    {
        /// <summary>
        /// 应用样式的列序号，从0开始
        /// </summary>
        public int ColumnIndex { get; set; }
        /// <summary>
        /// 应用样式的行序号，从0开始
        /// </summary>
        public int RowIndex { get; set; }

        public Func<Workbook, Style> StyleFunc { get; set; }
    }

    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}$");
            }
        }

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

            this._cellStyleList = styleGroup;
        }

        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)
        {
            //Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
            Workbook wk;
            wk = new Workbook(templateFileStream);

            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;

                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(y => y.ColumnIndex).ToList()
                    .ForEach(y =>
                    {
                        if (y.InsertCount > 0)
                        {
                            sheet.Cells.InsertColumns(y.ColumnIndex, y.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 && 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, styleItem.ColIndex];
                    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;
                        sheet.AutoFitRow(cell.Row);
                    }
                    cell.SetStyle(style);
                }
                foreach (var listItem in x.ListItemList)
                {
                    var customerStyleList = listItemStyleList.Where(y => y.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(y => y.StartRow).ThenBy(y => y.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));
                            }
                        }
                    }
                    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;
                                sheet.AutoFitRow(cell.Row);
                                cell.SetStyle(style);
                            }
                        }
                    }
                }
                if (x.PictureItemList != null)
                {
                    foreach (var pictureItem in x.PictureItemList)
                    {
                        int startRow = pictureItem.StartRow + addRows;
                        sheet.Pictures.Add(pictureItem.StartRow + addRows, pictureItem.StartColumn, pictureItem.EndRow + addRows, pictureItem.EndColumn, pictureItem.Picture);
                    }
                }
                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)));
                        }
                    });
                }
                //sheet.AutoFitRows();
                sheetIndex++;
            });

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

        private Workbook Export(DataTable dtSource, string[] CloumnList, string filePath)
        {
            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 (NumberRegex.IsMatch(dtSource.Rows[i][j].ToString()))
                            {
                                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)
        {
            string fileName = Guid.NewGuid().ToString() + ".xls";

            var workbook = this.Export(dtSource, CloumnList, filePath);
            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, string filePath)
        {
            var workbook = this.Export(dtSource, CloumnList, filePath);
            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 static class AsposeExcelHelperExtensions
    {
        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;
        }
    }

    public class ExcelToPDF
    {
        public static MemoryStream Export(Stream excelStream)
        {
            //Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);

            Workbook wb = new Workbook(excelStream, new LoadOptions(LoadFormat.Auto));
            var pdfStream = new MemoryStream();
            wb.Save(pdfStream, new PdfSaveOptions(SaveFormat.Pdf)
            {
            });
            return pdfStream;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="excelStream"></param>
        /// <param name="filePath">相对路径</param>
        /// <returns></returns>
        public static string ExportToFile(MemoryStream excelStream, string filePath)
        {
            //Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
            var pdfFileName = Guid.NewGuid().ToString() + ".pdf";
            var fullPath = filePath + (!filePath.EndsWith("/") ? "/" : "") + pdfFileName;
            var physicalFullPath = System.Web.HttpContext.Current.Server.MapPath(fullPath);

            Workbook wb = new Workbook(excelStream, new LoadOptions(LoadFormat.Auto));
            wb.Save(physicalFullPath, SaveFormat.Pdf);
            return fullPath;
        }
    }
}