Output zero width columns (was: About saving column blanks)

[FreeVB]

hi wqweto:
I hope that the column width is less than 200 and it will not be saved. Now it is actually a blank column that is saved.
Is there a way to solve this? Thank you!

`Public Function Save2ExcelB(sFile As String, mGrid As MSHFlexGrid, lCols As Integer, lRows As Long) As Boolean
Dim oStyle() As cBiff12CellStyle
Dim lIdx As Long
Dim lRow As Long
Dim dblTimer As Single
Dim baBuffer() As Byte
Dim i As Integer

On Error GoTo EH
dblTimer = Timer
ReDim oStyle(0 To lCols) As cBiff12CellStyle
For lIdx = 0 To lCols - 1
    Set oStyle(lIdx) = New cBiff12CellStyle
    With oStyle(lIdx)
        .FontName = "宋体"
        .FontSize = 12 + lIdx
        .Bold = False
    End With
Next
oStyle(0).Bold = True
oStyle(0).HorAlign = ucsHalCenter
oStyle(0).VertAlign = ucsValMiddle

With New cBiff12Writer
    '--- note: Excel's Biff12 clipboard reader cannot handle shared-strings table
    .Init lCols, False, , "out"
    For i = 0 To lCols - 1 ' MSHFlexGrid1.Cols - 1
        .ColWidth(i) = mGrid.ColWidth(i) * 3
    Next i
    Dim j As Long
    
    For lRow = 0 To lRows - 1
        For lIdx = 0 To .ColCount - 1
            'I hope that the column width is less than 200 and it will not be saved. Now it is actually a blank column that is saved.
            If mGrid.ColWidth(lIdx) > 200 Then
                .AddRow lRow, IIf(lRow = 18, 1000, 326)
                .AddStringCell lIdx, mGrid.TextMatrix(lRow, lIdx), oStyle(0)
            End If
        Next
    Next
   
    .Flush
    .SaveToFile baBuffer
    WriteBinaryFile sFile, baBuffer
    
    'GetForegroundWindow()：在窗体中可以使用 me.hWnd，在模块中就要使用这个了。
    If OpenClipboard(GetForegroundWindow()) = 0 Then Err.Raise vbObjectError, , "打开剪切板错误"
    Call EmptyClipboard
    SetTextData "Biff12 Explorer"
    SetBinaryData AddFormat("Biff12"), baBuffer
    Call CloseClipboard
End With
Save2ExcelB = True
'MsgBox "文件保存在： " & vbCrLf & vbCrLf & sFile & vbCrLf & vbCrLf & "耗时：" & Format$(Timer - dblTimer, "0.000") & "秒", 64
Exit Function

EH:
MsgBox Error & Err.Source, vbCritical, "Save2ExcelB"
End Function`

Biff12Writer.zip

[wqweto]

@FeeVB There is an explicit test If mGrid.ColWidth(lIdx) > 200 Then which skips setting data on these smaller columns.

What exactly do you want to solve?

[FreeVB]

I want is to skip these columns and not have blank columns.

[wqweto]

I could help you but this has nothing to do with any issue in the original repo. This is a very basic programming task at novice level.

You already have variable j declared -- use it as a column index param to AddStringCell like this

            .AddStringCell j, mGrid.TextMatrix(lRow, lIdx), oStyle(0)
            j = j + 1

and don't forget to set it to zero on next lRow iteration like this

For lRow = 0 To lRows - 1
    j = 0
    For lIdx = 0 ...

Please post only relevent issues with the repo here.

[FreeVB]

thank you!
Is your question below related to issue?

Invalid when setting column width equal to 0, because Sub pvBeginSheet () limits this setting.

'For i = 0 To lCols - 1
.ColWidth(i) = IIf(mGrid.ColWidth(i) < 10, 0, mGrid.ColWidth(i) * 3)
Next i'

'Private Sub pvBeginSheet(oPart As cBiff12Part)
Dim uWsProp As UcsBiff12BrtWsPropType
Dim uWsDim As UcsBiff12UncheckedRfXType
Dim uColInfo As UcsBiff12BrtColInfoType
Dim lIdx As Long

........
If lIdx < m_lColCount Then
.Output ucsBrtBeginColInfos
For lIdx = lIdx To m_lColCount - 1
If m_aColWidth(lIdx) <> 0 Then**'Because set up the exit column width here is equal to 0.**
uColInfo.m_colFirst = lIdx
uColInfo.m_colLast = lIdx
'Set column width
uColInfo.m_colDx = m_aColWidth(lIdx)
.OutputBrtColInfo uColInfo
End If
Next
.........

[wqweto]

I'm sorry but I have no idea what are you talking about. As a final resort you can just send a PR with the fix of the issue if there is one as I don't see a problem.

'Because set up the exit column width here is equal to 0.

. . . does not ring a bell here.

ColWidth(x) is equal to 0 by default (when it is not explicitly set) which means that this column will use the default column width that is set for the sheet with the BrtWsFmtInfo record e.g. we are using &H120 for cchDefColWidth in cBiff12Writer class.

What you want to do (skipping a column when copying data from a flex grid) is outside the scope of this repo and is doable in "client code" using this project.

[FreeVB]

I am describing the problem, I mean MSHFlexGrid1 has 6 columns, where column 2 and column 4 are equal to 0: MSHFlexGrid1.ColWidth(2) = 0, MSHFlexGrid1.ColWidth(4) = 0.
So after keeping EXCEL, shouldn't the column width be 0? Actually not: he is a default width.

[wqweto]

Doh, I see what the problem is now -- yes, you cannot set ColWidth to zero to hide the column as this will be interpreted as "use default width".

This might get fixed eventually in some future release. Until then you can try using 1 for width instead of 0. (Not sure if -1 will be good idea though.)

[wqweto]

I'll close this issue when the fix is available.

10x