将Excel电子表格导出为固定宽度的文本文件？

我认为最接近原生Excel功能的是保存为|格式化文本(有空格限制)(*.prn)。它将自动确定宽度，并根据需要插入空格以垫到该宽度。

除此之外，你还需要一个宏或其他插件来让你做更多的事情。

如果你有Office Professional，你可以在Access中打开你的Excel文件，然后从Access导出。Access 会让您为导出的文件指定固定宽度的布局，并为您提供了极其细化的控制来指定这些宽度。

哇，我本来想自己问这个问题，但已经有人问了。所有Excel剪贴板的输出默认都是以制表符分隔的。这对于 “真正的 "纯文本输出来说是有点烦人的，当你有一个固定宽度的字体，但不一定支持制表符分隔符。

总之，我找到并修改了一个小的Excel Macro，它可以将当前选定的区域复制成一个简单的固定宽度的ASCII表格–像这样。

187712 201 37 0.18 2525 580 149 0.25 136829 137 43 0.31

这是宏程序的代码。如果你使用的是Excel 2007或更高版本，请确保在Excel选项中启用开发者选项卡。

Sub CopySelectionToClipboardAsText()

   ' requires a reference to "Windows Forms 2.0 Object Library"
   ' add it via Tools / References; if it does not appear in the list
   ' manually add it as the path C:\Windows\System32\FM20.dll

    Dim r As Long, c As Long
    Dim selectedrows As Integer, selectedcols As Integer

    Dim arr
    arr = ActiveSheet.UsedRange
    selectedrows = UBound(arr, 1)
    selectedcols = UBound(arr, 2)

    Dim temp As Integer
    Dim cellsize As Integer
    cellsize = 0
    For c = 1 To selectedcols
        temp = Len(CStr(Cells(1, c)))
        If temp > cellsize Then
            cellsize = temp
        End If
    Next c
    cellsize = cellsize + 1

    Dim line As String
    Dim output As String

    For r = 1 To selectedrows
        line = Space(selectedcols * cellsize)
        For c = 1 To selectedcols
            Mid(line, c * cellsize - cellsize + 1, cellsize) = Cells(r, c)
        Next c
        output = output + line + Chr(13) + Chr(10)
    Next r

    Dim MyData As MSForms.DataObject
    Set MyData = New DataObject
    MyData.SetText output
    MyData.PutInClipboard

    MsgBox "The current selection was formatted and copied to the clipboard"

End Sub

首先，将你的数据格式化为Courier New（或其他固定宽度的字体）。然后保存为.prn，你会得到真正的固定宽度。

扩充Jeff Atwood的答案，因为它不允许我在那里评论。

我修改了他的宏 将列宽设置为该列中最宽的单元格 并且每列都有自己的宽度。他的宏只找到第一行中最宽的单元格，然后将所有列的宽度设置为它。

Sub CopySelectionToClipboardAsText()

   ' requires a reference to "Windows Forms 2.0 Object Library"
   ' add it via Tools / References; if it does not appear in the list
   ' manually add it as the path C:\Windows\System32\FM20.dll

    Dim r As Long, c As Long, linesize As Long
    Dim selectedrows As Integer, selectedcols As Integer

    Dim arr
    arr = ActiveSheet.UsedRange
    selectedrows = UBound(arr, 1)
    selectedcols = UBound(arr, 2)
    ReDim CellSizes(1 To selectedcols, 2) As Integer

    Dim temp As Integer
    Dim cellsize As Integer
    linesize = 0
    For c = 1 To selectedcols
        cellsize = 0
        For r = 1 To selectedrows
            temp = Len(CStr(Cells(r, c)))
            If temp > cellsize Then
                cellsize = temp
            End If
        Next
        CellSizes(c, 0) = cellsize + 1
        CellSizes(c, 1) = linesize
        linesize = linesize + cellsize + 1
    Next c

    Dim line As String
    Dim output As String

    For r = 1 To selectedrows
        line = Space(linesize)
        For c = 1 To selectedcols
            Mid(line, CellSizes(c, 1) + 1, CellSizes(c, 0)) = Cells(r, c)
        Next c
        output = output + line + Chr(13) + Chr(10)
    Next r

    Dim MyData As MSForms.DataObject
    Set MyData = New DataObject
    MyData.SetText output
    MyData.PutInClipboard

    MsgBox "The current selection was formatted and copied to the clipboard"

End Sub

这是我的一个杀手锏。它也有几个选项。 http://www.sensefulsolutions.com/2010/10/format-text-as-table.html

它与Access一起工作，开箱即用。https://support.office.com/en-ie/article/export-data-to-a-text-file-f72dfc38-a8a0-4c5b-8c2c-bf2950814140#bmsteps 用这种方式我管理起来非常简单和快速 - 比用Excel好。在我的情况下，它是一个表的转换。