Aspose------导出Excel

代码:

/// <summary>
/// 导出Excel
/// </summary>
/// <typeparam name="T">泛型类</typeparam>
/// <param name="list">数据列</param>
/// <param name="row">插入行索引</param>
/// <param name="column">插入列索引</param>
public static void ExportToExcel<T>(List<T> list,int row, int column)
{
　　//模板地址
　　var template = Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, "Temp", "A_Login.xlsx");

　　Workbook book = new Workbook(template);
　　Worksheet sheet = book.Worksheets[0];
　　//设置筛选
　　sheet.AutoFilter.SetRange(0, 0, 12);
            
　　Cells cell = sheet.Cells;

　　for (var i = 0; i < list.Count; i++)
　　{
　　　　cell.InsertRows(row + 1,1);
　　}

　　int rowIndex = 0;
　　int colIndex = column;

　　Type type = typeof(T);
　　PropertyInfo []propertyInfo = type.GetProperties();
　　foreach (var pi in propertyInfo)
　　{
　　　　rowIndex = row;
　　　　foreach (var li in list)
　　　　{
　　　　　　var value = pi.GetValue(li);
　　　　　　cell[rowIndex, colIndex].PutValue(value);
　　　　　　rowIndex++;
　　　　}
　　　　colIndex++;
　　}

　　var path = Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, "Temp", "成品.xlsx");
　　book.Save(path);
}

 

/// <summary>
        /// 导出合并单元格的Excel
        /// </summary>
        /// <typeparam name="T">泛型类</typeparam>
        /// <param name="list">需要合并的数据</param>
        public void ExportToMergeExcel<T>(List<T> list)
        {
            Workbook book = new Workbook();
            Worksheet sheet = book.Worksheets[0];
            Cells cell = sheet.Cells;
            Type type = typeof(T);

            int rowIndex = 0;
            int colIndex = 0;
            PropertyInfo[] property = type.GetProperties();
            //合并的第一个单元格数据
            var name = "";
            //是否取出第一个单元格数据
            var isFirst = true;
            //记录需要合并的行数
            var num = 1;
            List<int> mergeIndex = new List<int>();
            List<int> mergeCount = new List<int>();

            foreach (var pi in property)
            {
                rowIndex = 0;
                cell[rowIndex, colIndex].PutValue(pi.Name);

                foreach (var li in list)
                {
                    ++rowIndex;
                    var value = pi.GetValue(li);
                    cell[rowIndex, colIndex].PutValue(value);

                    if (pi.Name == "Component")
                    {
                        if (isFirst == true)
                        {
                            isFirst = false;
                            continue;
                        }

                        var v = value.ToString();
                        if (v == name)
                        {
                            num++;
                            if (rowIndex == list.Count())
                            {
                                if (num > 1)
                                {
                                    mergeIndex.Add(rowIndex - num + 1);
                                    mergeCount.Add(num);
                                }
                            }
                        }
                        else
                        {
                            name = v;
                            mergeIndex.Add(rowIndex - num);
                            mergeCount.Add(num);
                            num = 1;
                        }
                    }
                    if (pi.Name == "SubComponent")
                    {
                        if (value == null)
                        {
                            cell.Merge(rowIndex, 0, 1, 9);
                        }
                    }
                }
                colIndex++;
            }
            
            //合并
            for (var i = 0; i < mergeIndex.Count(); i++)
            {
                var f = mergeIndex[i];
                var r = mergeCount[i];
                cell.Merge(f, 0, r, 1);
            }

            SetCellsStyle(cell);

            //设置筛选行
            sheet.AutoFilter.SetRange(0, 0, 12);

            //自动行列宽
            sheet.AutoFitColumns();
            sheet.AutoFitRows();

            var path = Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, "Temp", "成品nck.xlsx");
            book.Save(path);
        }

        /// <summary>
        /// 设置整行/整列样式
        /// </summary>
        /// <param name="cells"></param>
        private void SetCellsStyle(Cells cells)
        {
            //设置样式
            Style style1 = new Style();
            //style.Font.Size = 12;
            StyleFlag flag1 = new StyleFlag();
            flag1.VerticalAlignment = true;
            flag1.Font = true;
            cells.ApplyColumnStyle(0, style1, flag1);
            //cell.Columns[0].ApplyStyle(style1, flag1);

            Style style2 = new Style();
            style2.Font.Size = 12;
            style2.Pattern = BackgroundType.Solid;
            style2.ForegroundColor = Color.FromArgb(122, 136, 145, 240);
            style2.Font.IsBold = true;
            style2.HorizontalAlignment = TextAlignmentType.Left;
            StyleFlag flag2 = new StyleFlag();
            flag2.Font = true;
            flag2.All = true;
            //flag2.HorizontalAlignment = true;
            cells.ApplyRowStyle(0, style2, flag2);
        }

        private void SetCellStyle(Cell cell, int fontSize, bool isBold = false, bool isItalic = false, List<int> argb = null)
        {
            Style style = new Style();
            style.Font.Size = fontSize;
            style.Font.IsBold = isBold;
            style.Font.IsItalic = isItalic;
            style.HorizontalAlignment = TextAlignmentType.Left;
            style.Pattern = BackgroundType.Solid;
            style.ForegroundColor = Color.FromArgb(argb[0], argb[1], argb[2], argb[3]);
            cell.SetStyle(style, true);
        }

 

需要合并的条目

private List<MergeItem> MergeEfficientItem(List<ProjectItem> list)
        {
            PropertyInfo[] propertyInfo = typeof(MergeItem).GetProperties();
            //var data = _ProjectItemRepository.AsQueryable().where(p => p.ProjectCode == projectCode);
            var data = list;
            var level1List = data.Where(p => p.ItemLevel == "Level1").ToList();
            var level2List = data.Where(p => p.ItemLevel == "Level2").ToList();
            var level3List = data.Where(p => p.ItemLevel == "Level3").ToList();
            var datalist = new List<MergeItem>();

            //一级条目
            foreach (var li in level1List)
            {
                var pitem = new MergeItem();
                pitem.Component = li.Component;
                datalist.Add(pitem);

                //三级条目
                var citem = level3List.Where(p => p.Component == li.Component).OrderBy(p => p.Seq);
                var subitem = new List<MergeItem>();
                foreach (var cli in citem)
                {
                    var sitem = new MergeItem();
                    sitem.Component = cli.SubComponent;
                    sitem.SubComponent = cli.Description;
                    sitem.Unit = cli.Unit;
                    sitem.Remark = cli.Remark;
                    sitem.FillInstruct = cli.FillInstruct;

                    if (subitem.Any(p => p.Component == cli.SubComponent && p.SubComponent == cli.Description))
                    {
                        sitem = subitem.Where(p => p.Component == cli.SubComponent && p.SubComponent == cli.Description).FirstOrDefault();
                    }
                    else
                    {
                        subitem.Add(sitem);
                    }

                    foreach (var pi in propertyInfo)
                    {
                        foreach (var c in citem)
                        {
                            var trim = c.Scenario.Replace(" ", "");
                            if (pi.Name == trim && c.Description == sitem.SubComponent)
                            {
                                pi.SetValue(sitem, c.UnitCost);
                            }
                        }
                    }
                }
                datalist.AddRange(subitem);
            }
            _ExcelUtil.ExportToMergeExcel<MergeItem>(datalist);
            
            return datalist;
        }