C# 构建树形数据

一.不是父子级关系的数据，是自己拼接的父子级关系。如：部门下的人员数据。图1

Id	Name	ParentId	ParentName
UserId	UserName	DefaultDeptId	DefaultDeptName
xxx	张三	yyy	设计部

效果图：

步骤： 1. 先查询出图1的数据

            2.对数据进行分组，以父级id 和父级名称进行分组。

 var rootData = users.GroupBy(u => new { u.ParentId, u.ParentName }).Select(u => new MonthlyReportTreeRecord
            {
                ParentId = "",
                Id = u.Key.ParentId,
                Name = u.Key.ParentName,
                Amount = u.Sum(p => p.Amount),
                FinishNum = u.Sum(p => p.FinishNum),
                UnFinishNum = u.Sum(p => p.UnFinishNum),
                Rate = Convert.ToDouble(u.Sum(x => x.Amount) == 0 ? 0 : (Convert.ToDouble(u.Sum(x => x.FinishNum)) / Convert.ToDouble(u.Sum(x => x.Amount)))).ToString("P"),
                ChildData = u.Select(p => new MonthlyReportTreeRecord
                {
                    Id = p.Id,
                    ParentId = p.ParentId,
                    Name = p.Name,
                    Amount = p.Amount,
                    FinishNum = p.FinishNum,
                    UnFinishNum = p.UnFinishNum,
                    Rate = Convert.ToDouble(p.Amount == 0 ? 0 : (Convert.ToDouble(p.FinishNum) / Convert.ToDouble(p.Amount))).ToString("P")
                }).ToList()
            }).ToList();  

这样就可以得出树形效果。

2.导出树形数据EXCEL

 对产生的rootData 数据，进行处理。放在一个List<MonthlyReportTreeRecord>

使用递归去处理数据

   private void WraptGetChildData(MonthlyReportTreeRecord parentData, List<MonthlyReportTreeRecord> allData)
        {
            if (parentData.ChildData != null && parentData.ChildData.Any())
            {
                allData.AddRange(parentData.ChildData);
                parentData.ChildData.ForEach(p =>
                {
                    WraptGetChildData(p, allData);
                });
            }
        }

在原方法中调用

 var allData = new List<MonthlyReportTreeRecord>();
                list.ForEach(p =>
                {
                    allData.Add(p);
                    WraptGetChildData(p, allData);
                });

则得到的allData 就是处理好的数据。

把数据转成DataTable 数据。

使用NOPI导出

namespace Common
{
    /// <summary>
    /// NPOI导出excel帮助类
    /// </summary>
    public class NPOIHelper
    {
        public readonly IWorkbook workbook;
        public readonly ISheet sheet;
        private readonly int _type;

        public NPOIHelper(string sheetName, int type = 0)
        {
            if (type == 0)
            {
                workbook = new HSSFWorkbook();
            }
            else
            {
                workbook = new XSSFWorkbook();
            }
            _type = type;
            sheet = workbook.CreateSheet(sheetName);
        }

        /// <summary>
        /// 创建excel头部行
        /// </summary>
        /// <param name="headers">列头数据</param>
        /// <param name="rowIndex">所属行</param>
        public void CreateExcelHeadRow(List<ExcelHeader> headers, int rowIndex)
        {
            var headStyle = ExcelHelper.GetHeadCellStyle(workbook);
            var headerRow = sheet.GetRow(rowIndex);
            if (headerRow == null)
            {
                headerRow = ExcelHelpers.CreateRow(sheet, rowIndex, 20);
            }
            var maxRow = headers.Max(p => p.MergeRowCount);
            for (int i = 1; i <= maxRow; i++)
            {
                ExcelHelpers.CreateRow(sheet, rowIndex + i, 20);
            }
            headers.ForEach(p =>
            {
                var cell = headerRow.CreateCell(p.ColumnIndex);
                //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex + p.MergeRowCount, p.ColumnIndex, p.ColumnIndex + p.MergeColumnCount));
                cell.SetCellValue(p.FieldName);
                cell.CellStyle = headStyle;

            });

            //设置列宽自动宽度
            for (int i = 0; i < headers.Count; i++)
            {
                sheet.AutoSizeColumn(i);
            }
        }

        /// <summary>
        /// 创建excel内容行
        /// </summary>
        /// <param name="table">数据源</param>
        /// <param name="startRowIndex">开始行</param>
        public virtual void CreateExcelContentRow(DataTable table, int startRowIndex)
        {
            var cellStyle = ExcelHelpers.GetContentCellStyle(workbook);
            for (int i = 0; i < table.Rows.Count; i++)
            {
                var row = ExcelHelpers.CreateRow(sheet, i + startRowIndex, 18);
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    var cell = row.CreateCell(j);
                    cell.SetCellValue(table.Rows[i][j].ToString());
                    cell.CellStyle = cellStyle;
                }
            }

            // 设置列宽自动宽度
            //for (int m = 0; m < table.Columns.Count; m++)
            //{
            //    sheet.AutoSizeColumn(m);
            //}

            SetColumnWidth(table);
        }

        public virtual void CreateTreeExcelContentRow(List<DataRow> parentRows, DataTable table, IEnumerable<string> ignoreColNames, ref int startRowIndex)
        {
            var cellStyle = ExcelHelpers.GetContentCellStyle(workbook);
            foreach (DataRow parentRow in parentRows)
            {
                var row = ExcelHelpers.CreateRow(sheet, startRowIndex, 18);
                var j = 0;
                foreach (DataColumn tableColumn in table.Columns)
                {
                    var columnName = tableColumn.ColumnName;
                    if (ignoreColNames.Contains(columnName))
                    {
                        continue;
                    }
                    var cell = row.CreateCell(j);
                    cell.SetCellValue(parentRow[columnName].ToString());
                    cell.CellStyle = cellStyle;
                    j++;
                }
                startRowIndex++;

                CreateChildTreeExcelContentRow(parentRow, table, ignoreColNames, ref startRowIndex);
            }

            // 设置列宽自动宽度
            for (int m = 0; m < table.Columns.Count; m++)
            {
                sheet.AutoSizeColumn(m);
            }

            SetColumnWidth(table);
        }

        public virtual void CreateChildTreeExcelContentRow(DataRow tableRow, DataTable table, IEnumerable<string> ignoreColNames
           , ref int rowIndex)
        {
            var cellStyle = ExcelHelpers.GetContentCellStyle(workbook);
            var childRows = table.AsEnumerable().Where(t => t["ParentID"].ToString() == tableRow["Id"].ToString()).ToList();

            int startRow = rowIndex;
            foreach (DataRow childRow in childRows)
            {
                var row = ExcelHelpers.CreateRow(sheet, rowIndex, 18);
                var j = 0;
                foreach (DataColumn tableColumn in table.Columns)
                {
                    var columnName = tableColumn.ColumnName;
                    if (ignoreColNames.Contains(columnName))
                    {
                        continue;
                    }
                    var cell = row.CreateCell(j);
                    cell.SetCellValue(childRow[columnName].ToString());
                    cell.CellStyle = cellStyle;
                    j++;
                }

                rowIndex++;
                CreateChildTreeExcelContentRow(childRow, table, ignoreColNames, ref rowIndex);
            }

            sheet.GroupRow(startRow, rowIndex);
            sheet.SetRowGroupCollapsed(startRow, true);
        }

        /// <summary>
        /// 设置宽度
        /// </summary>
        /// <param name="table"></param>
        public void SetColumnWidth(DataTable table)
        {
            //获取当前列的宽度，然后对比本列的长度，取最大值
            for (int columnNum = 0; columnNum <= table.Rows.Count; columnNum++)
            {
                int columnWidth = sheet.GetColumnWidth(columnNum) / 256;
                for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++)
                {
                    IRow currentRow;
                    //当前行未被使用过
                    if (sheet.GetRow(rowNum) == null)
                    {
                        currentRow = sheet.CreateRow(rowNum);
                    }
                    else
                    {
                        currentRow = sheet.GetRow(rowNum);
                    }

                    if (currentRow.GetCell(columnNum) != null)
                    {
                        ICell currentCell = currentRow.GetCell(columnNum);
                        int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
                        if (columnWidth < length)
                        {
                            columnWidth = length;
                        }
                    }
                }

                sheet.SetColumnWidth(columnNum, 15 * 500);
            }
        }

        /// <summary>
        /// 获取excel返回HttpResponseMessage
        /// </summary>
        /// <param name="workBook"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public HttpResponseMessage GetExcelHttpResponseMessage(string tableName, string extension = ".xls")
        {
            return ExcelHelpers.GetExcelHttpResponseMessage(workbook, tableName, extension);
        }

        /// <summary>
        /// 文件全路径
        /// </summary>
        /// <param name="fileName"></param>
        public void ExcelToFile(string fileName)
        {
            var dirPath = Path.GetDirectoryName(fileName);
            if (!string.IsNullOrEmpty(dirPath))
            {
                Directory.CreateDirec