Excel催化剂开源第50波-Excel与PowerBIDeskTop互通互联之第四篇

        public static void OutputDataDic(string cnnString, string dbName, string fileName)
        {

            string shtName = "数据字典_" + fileName;
            shtName = shtName.Length > 31 ? shtName.Substring(0, 31) : shtName;
            Excel.Worksheet wht;
            try
            {
                wht = Common.ExcelApp.ActiveWorkbook.Worksheets[shtName];
                Common.ExcelApp.DisplayAlerts = false;
                wht.Delete();
                Common.ExcelApp.DisplayAlerts = true;
            }
            catch (Exception)
            {

            }
            wht = Common.ExcelApp.Worksheets.Add();
            wht.Name = shtName;
            var vstoSht = Globals.Factory.GetVstoObject(wht);

            ListObject listMeasures = vstoSht.Controls.AddListObject(vstoSht.Range["A1"], "度量值表_" + fileName);
            ListObject listDims = vstoSht.Controls.AddListObject(vstoSht.Range["C1"], "维度表_" + fileName);
            ListObject listRelationShips = vstoSht.Controls.AddListObject(vstoSht.Range["E1"], "关系表_" + fileName);

            using (AMO.Server server = new AMO.Server())
            {
                server.Connect(cnnString);
                AMO.Database pbidDb = server.Databases[dbName];

                ////获取度量值
                GetMeasuresTable(pbidDb, listMeasures);
                //获取列字段
                GetDimsTable(pbidDb, listDims);
                ////获取关系
                GetRelationsTable(pbidDb, listRelationShips);

            };
            vstoSht.Activate();
        }

        private static DataSet.DataSet1.MeasuresOfLevel1200DataTable GetMeasuresTableOfLevel1200(AMO.Database tabularDb)
        {
            TOM.Model model = tabularDb.Model;
            DataSet.DataSet1.MeasuresOfLevel1200DataTable measuresTable = new DataSet.DataSet1.MeasuresOfLevel1200DataTable();
            foreach (TOM.Table table in model.Tables)
            {
                foreach (TOM.Measure measure in table.Measures)
                {
                    DataSet.DataSet1.MeasuresOfLevel1200Row dr = measuresTable.NewMeasuresOfLevel1200Row();
                    dr.Measure = $"[{measure.Name}]";
                    dr.TableName = table.Name;
                    dr.DisplayFolder = measure.DisplayFolder;
                    dr.Description = measure.Description;
                    dr.FormatString = measure.FormatString;
                    dr.MeasureExpression = measure.Expression;
                    dr.Visible = !measure.IsHidden;
                    dr.DataType = measure.DataType.ToString();
                    measuresTable.AddMeasuresOfLevel1200Row(dr);
                }
            }

            return measuresTable;
        }

 private static DataTable GetDimsTableOfLevel1200(AMO.Database tabularDb)
        {
            TOM.Model model = tabularDb.Model;
            DataSet.DataSet1.ColumnNamesDataTable dtColumns = new DataSet.DataSet1.ColumnNamesDataTable();
            foreach (TOM.Table table in model.Tables)
            {
                foreach (TOM.Column col in table.Columns)
                {
                    if (!col.Name.Contains("RowNumber"))
                    {
                        TOM.CalculatedColumn calCol = col as TOM.CalculatedColumn;
                        DataSet.DataSet1.ColumnNamesRow dr = dtColumns.NewColumnNamesRow();
                        dr.TableName = table.Name;
                        dr.FieldName = col.Name;
                        dr.FieldType = col.DataType.ToString();
                        if (calCol != null)
                        {
                            dr.DaxExpression = calCol.Expression;
                        }

                        dr.Description = col.Description;
                        dr.Visible = !col.IsHidden;
                        dtColumns.AddColumnNamesRow(dr);
                    }

                }
            }
            return dtColumns.Cast<DataSet.DataSet1.ColumnNamesRow>().Where(s => !s.TableName.StartsWith("LocalDateTable"))
                                                            .Where(s => !s.TableName.StartsWith("DateTableTemplate"))
                                                            .CopyToDataTable<DataSet.DataSet1.ColumnNamesRow>();
        }

        private static DataTable GetRelationsTableOfLevel1200(AMO.Database tabularDb)
        {
            TOM.Model model = tabularDb.Model;
            DataSet.DataSet1.关系表DataTable dt = new DataSet.DataSet1.关系表DataTable();
            foreach (TOM.Relationship relationship in model.Relationships)
            {
                TOM.SingleColumnRelationship singleRelationShip = relationship as TOM.SingleColumnRelationship;
                DataSet.DataSet1.关系表Row dr = dt.New关系表Row();
                dr.ToTable = relationship.ToTable.Name;
                dr.FromTable = relationship.FromTable.Name;
                dr.IsActive = relationship.IsActive;
                if (singleRelationShip != null)
                {
                    dr.FromColumn = singleRelationShip.FromColumn.Name;
                    dr.ToColumn = singleRelationShip.ToColumn.Name;
                    dr.FromCardinality = singleRelationShip.FromCardinality.ToString();
                    dr.ToCardinality = singleRelationShip.ToCardinality.ToString();
                }
                dt.Add关系表Row(dr);
            }
            return dt.Cast<DataSet.DataSet1.关系表Row>().Where(s => !s.ToTable.StartsWith("LocalDateTable")).CopyToDataTable<DataSet.DataSet1.关系表Row>();
        }