Option Explicit

Sub DumpAllSheets_WidthsAndAlign()
    Dim oDoc As Object, oSheets As Object, oOutSheet As Object
    Dim oSrcSheet As Object, oCursor As Object
    Dim sOutName As String
    Dim sheetIndex As Long, sheetCount As Long
    Dim nLastCol As Long, nLastRow As Long
    Dim i As Long, r As Long
    Dim rowOffset As Long, widthRow As Long, alignRow As Long
    Dim oCol As Object, oCell As Object
    Dim w100mm As Long
    Dim widthMM As Double, widthInch As Double
    Dim alignFlag As Long, colJustify As Long
    Dim outCol As Long

    ' Get current document
    oDoc = ThisComponent
    If Not oDoc.supportsService("com.sun.star.sheet.SpreadsheetDocument") Then
        MsgBox "Active document is not a Calc spreadsheet."
        Exit Sub
    End If

    oSheets = oDoc.Sheets
    sOutName = "ColWidths"

    ' Recreate output sheet at the far right
    If oSheets.hasByName(sOutName) Then
        oSheets.removeByName(sOutName)
    End If
    oSheets.insertNewByName(sOutName, oSheets.getCount())
    oOutSheet = oSheets.getByName(sOutName)

    sheetCount = oSheets.getCount()
    rowOffset = 0

    ' Loop over all sheets except ColWidths
    For sheetIndex = 0 To sheetCount - 1
        oSrcSheet = oSheets.getByIndex(sheetIndex)
        If oSrcSheet.Name <> sOutName Then

            ' Determine used area on this sheet
            oCursor = oSrcSheet.createCursor()
            oCursor.gotoEndOfUsedArea(True)
            nLastCol = oCursor.RangeAddress.EndColumn
            nLastRow = oCursor.RangeAddress.EndRow

            If nLastCol >= 0 Then
                widthRow = rowOffset          ' widths go here
                alignRow = rowOffset + 1      ' flags go here

                ' First cell in width row = "<SheetName>.csv"
                oOutSheet.getCellByPosition(0, widthRow).String = oSrcSheet.Name & ".csv"

                ' For each column on this sheet
                For i = 0 To nLastCol
                    ' Width in inches (Width is 1/100 mm)
                    oCol = oSrcSheet.Columns.getByIndex(i)
                    w100mm = oCol.Width
                    widthMM = w100mm / 100.0
                    widthInch = widthMM / 25.4

                    ' Alignment flag: check column format, then data cells
                    alignFlag = 0
                    colJustify = oCol.HoriJustify

                    If colJustify = com.sun.star.table.CellHoriJustify.CENTER Then
                        alignFlag = 1
                    Else
                        ' Scan data rows only (skip header row 0)
                        For r = 1 To nLastRow
                            oCell = oSrcSheet.getCellByPosition(i, r)
                            If oCell.Type <> com.sun.star.table.CellContentType.EMPTY Then
                                If oCell.HoriJustify = _
                                   com.sun.star.table.CellHoriJustify.CENTER Then
                                    alignFlag = 1
                                Else
                                    alignFlag = 0
                                End If
                                Exit For
                            End If
                        Next r
                    End If

                    ' Output into ColWidths: columns start at 1 (B)
                    outCol = i + 1
                    oOutSheet.getCellByPosition(outCol, widthRow).Value = widthInch
                    oOutSheet.getCellByPosition(outCol, alignRow).Value = alignFlag
                Next i

                ' Move down two rows for the next sheet
                rowOffset = rowOffset + 2
            End If
        End If
    Next sheetIndex
End Sub