A4WaterUtilities.DataTools.ExportTable C# (CSharp) Method

ExportTable() private method

private ExportTable ( Microsoft.Office.Interop.Excel ExcelWbk, IMxDocument MxDoc, IStandaloneTable StandTable, IProgressDialog2 &progressDialog, IStepProgressor &stepProgressor, ITrackCancel &trackCancel ) : bool
ExcelWbk Microsoft.Office.Interop.Excel
MxDoc IMxDocument
StandTable IStandaloneTable
progressDialog IProgressDialog2
stepProgressor IStepProgressor
trackCancel ITrackCancel
return bool
        private bool ExportTable(Excel.Workbook ExcelWbk, IMxDocument MxDoc, IStandaloneTable StandTable, ref IProgressDialog2 progressDialog, ref IStepProgressor stepProgressor, ref ITrackCancel trackCancel)
        {
            ITableProperties TableProperties = null;
            IEnumTableProperties EnumTableProperties = null;
            ITableProperty3 TableProperty = null;
            ITableCharacteristics TableCharacteristics = null;
            ITableSelection TabSel = null;
            IDisplayTable DisplayTable = null;
            ITable Table = null;
            IRow TabRow = null;
            IObjectClass ObjectClass = null;
            ISubtypes Subtypes = null;
            ITableFields TableFields = null;

            ICursor Cursor = null;
            IField CurField = null;
            IDomain Domain = null;
            ICodedValueDomain CodedValueDomain = null;

            object missing = null;
            object sheet = null;
            Microsoft.Office.Interop.Excel.Style style = null;

            try
            {
                bool UseDescriptions;
                int subtype;
                string SheetName;

                int Col = 0;
                int Row = 0;
                int i;
                int j;

                missing = System.Reflection.Missing.Value;

                sheet = ExcelWbk.Sheets[ExcelWbk.Sheets.Count];
                Excel.Worksheet ExcelSheet;
                Excel.Range ExcelRange;

                //Add new Excel worksheet
                ExcelSheet = (Excel.Worksheet)ExcelWbk.Sheets.Add(missing, sheet, missing, missing);
                //style = ExcelWbk.Styles.Add("Style1");
                //style.NumberFormat = "@";
                //style.Font.Name = "Arial";
                //style.Font.Bold = True
                //style.Font.Size = 12;
                //style.Interior.Pattern = Microsoft.Office.Interop.Excel.XlPattern.xlPattern Solid
                //style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlighLe ft

                SheetName = StandTable.Name;
                if (SheetName.Length > 30)
                {
                    SheetName = SheetName.Substring(0, 30);
                }
                ExcelSheet.Name = SheetName;

                //Determine whether to use descriptions or codes for domains
                UseDescriptions = true;
                TableProperties = MxDoc.TableProperties;
                EnumTableProperties = (IEnumTableProperties)TableProperties;
                EnumTableProperties.Reset();
                TableProperty = (ITableProperty3)EnumTableProperties.Next();

                while (TableProperty != null)
                {
                    if (TableProperty.StandaloneTable != null)
                    {
                        if (TableProperty.StandaloneTable.Equals(StandTable))
                        {
                            TableCharacteristics = (ITableCharacteristics)TableProperty;
                            UseDescriptions = TableCharacteristics.ShowCodedValueDomainDescriptions;
                            break;
                        }
                    }
                    TableProperty = (ITableProperty3)EnumTableProperties.Next();
                }
                TabSel = (ITableSelection)StandTable;

                DisplayTable = (IDisplayTable)StandTable;
                Table = (ITable)DisplayTable.DisplayTable;

                //Get subtype info
                ObjectClass = Table as IObjectClass;
                Subtypes = ObjectClass as ISubtypes;

                //Get TableFields so later we can determine whether that field is visible
                TableFields = (ITableFields)StandTable;

                //loop through each field and write column headings
                Row = 1;
                for (j = 0; j < TableFields.FieldCount; j++)
                {
                    CurField = TableFields.get_Field(j);

                    //skip blob and geometry fields
                    if ((CurField.Type != esriFieldType.esriFieldTypeBlob) && (CurField.Type != esriFieldType.esriFieldTypeGeometry))
                    {
                        Col += 1;
                        //Write field alias name as Excel column header
                        ExcelSheet.Cells[Row, Col] = TableFields.get_FieldInfo(j).Alias;
                        if (CurField.Type == esriFieldType.esriFieldTypeString)
                            ExcelSheet.get_Range(ExcelSheet.Cells[1, Col], ExcelSheet.Cells[65535, Col]).EntireColumn.NumberFormat = "@";

                    }
                }

                //Get all selected records for this table (use IDisplayTable to get any joined data)
                DisplayTable.DisplaySelectionSet.Search(null, true, out Cursor);

                //subtype = Subtypes.DefaultSubtypeCode;

                //For each selected record

                TabRow = Cursor.NextRow();
                //stepProgressor.Step();
                //     progressDialog.Description = A4LGSharedFunctions.Localizer.GetString("ExportAsset") + stepProgressor.Position + A4LGSharedFunctions.Localizer.GetString("Of") + MxDoc.FocusMap.SelectionCount.ToString() + ".";

                while (TabRow != null)
                {
                    Row += 1;

                    if (Subtypes != null && Subtypes.HasSubtype == true &&
                       (TabRow.get_Value(Subtypes.SubtypeFieldIndex) != null))
                    {
                        subtype = Convert.ToInt32(TabRow.get_Value(Subtypes.SubtypeFieldIndex));
                    }
                    else
                    {
                        subtype = -99999;
                    }

                    //For each column
                    Col = 0;
                    for (j = 0; j < TableFields.FieldCount; j++)
                    {
                        CurField = TableFields.get_Field(j);

                        //skip blob and geometry fields in data also
                        if ((CurField.Type != esriFieldType.esriFieldTypeBlob) && (CurField.Type != esriFieldType.esriFieldTypeGeometry))
                        {
                            Col += 1;
                            ExcelSheet.Cells[Row, Col] = TabRow.get_Value(j);

                            if (UseDescriptions == true && subtype == -99999)
                            {
                                Domain = CurField.Domain;
                                if (Domain != null)
                                {
                                    if (Domain.Type == esriDomainType.esriDTCodedValue)
                                    {
                                        CodedValueDomain = (ICodedValueDomain)CurField.Domain;
                                        for (i = 0; i < CodedValueDomain.CodeCount; i++)
                                        {
                                            if ((CodedValueDomain.get_Value(i)).ToString() == (TabRow.get_Value(j)).ToString())
                                            {
                                                //System.Diagnostics.Debug.Print(CodedValueDomain.get_Name(0).ToString());
                                                ExcelSheet.Cells[Row, Col] = CodedValueDomain.get_Name(i);
                                                i = CodedValueDomain.CodeCount;
                                            }
                                        }
                                    }
                                }
                            }
                            else if (UseDescriptions == true && subtype != -99999)
                            {
                                if (Subtypes.SubtypeFieldIndex == j)
                                {
                                    ExcelSheet.Cells[Row, Col] = Subtypes.get_SubtypeName(subtype);
                                }
                                else
                                {
                                    Domain = Subtypes.get_Domain(subtype, CurField.Name);
                                    if ((Domain != null) && (Domain.Type == esriDomainType.esriDTCodedValue))
                                    {
                                        CodedValueDomain = (ICodedValueDomain)Domain;
                                        for (i = 0; i < CodedValueDomain.CodeCount; i++)
                                        {
                                            if ((CodedValueDomain.get_Value(i)).ToString() == (TabRow.get_Value(j)).ToString())
                                            {
                                                //System.Diagnostics.Debug.Print(CodedValueDomain.get_Name(0).ToString());
                                                ExcelSheet.Cells[Row, Col] = CodedValueDomain.get_Name(i);
                                                i = CodedValueDomain.CodeCount;
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                    stepProgressor.Step();
                    progressDialog.Description = A4LGSharedFunctions.Localizer.GetString("ExportAsset") + stepProgressor.Position + A4LGSharedFunctions.Localizer.GetString("Of") + MxDoc.FocusMap.SelectionCount.ToString() + ".";
                    if (!trackCancel.Continue())
                    {
                        return false;
                    }
                    TabRow = Cursor.NextRow();
                }

                //Hide Columns
                Col = 0;
                for (j = 0; j < TableFields.FieldCount; j++)
                {
                    CurField = TableFields.get_Field(j);

                    //skip blob and geometry fields in data also
                    if ((CurField.Type != esriFieldType.esriFieldTypeBlob) && (CurField.Type != esriFieldType.esriFieldTypeGeometry))
                    {
                        Col += 1;
                        //Autofit
                        ExcelRange = ExcelSheet.get_Range(ExcelSheet.Cells[1, Col], ExcelSheet.Cells[Row, Col]);
                        ExcelRange.EntireColumn.AutoFit();

                        //Hide column if invisible in ArcMap
                        if (TableFields.get_FieldInfo(j).Visible == false)
                        {
                            ExcelRange = ExcelSheet.get_Range(ExcelSheet.Cells[1, Col], ExcelSheet.Cells[Row, Col]);
                            ExcelRange.EntireColumn.Hidden = true;
                        }
                    }
                }
                return true;
            }
            catch
            {
                MessageBox.Show(A4LGSharedFunctions.Localizer.GetString("ExportXLError"));
                return true;
            }
            finally
            {
                TableProperties = null;
                EnumTableProperties = null;
                TableProperty = null;
                TableCharacteristics = null;
                TabSel = null;
                DisplayTable = null;
                Table = null;
                TabRow = null;
                ObjectClass = null;
                Subtypes = null;
                TableFields = null;

                if (Cursor != null)
                    Marshal.ReleaseComObject(Cursor);
                Cursor = null;
                CurField = null;
                Domain = null;
                CodedValueDomain = null;

                missing = null;
                sheet = null;
                style = null;

            }
        }