.net - 将 Excel 范围/工作表导出为格式化文本文件

Question

我的任务是为我们的财务部门创建一个可重复使用的流程，以将我们的工资单上传到州 (WI) 进行报告。我需要创建一些在 Excel 中获取工作表或范围并创建特定格式文本文件的内容。

格式

第 1 列 - 静态数字，永不改变，位置 1-10

第 2 列 - 在运行时为季度/年填充的动态参数，位置 11-13

第 3 列 - SSN，无连字符或空格，
从 A 列填充，位置 14-22

第 4 列 - 姓氏，填写自
B 列，在 10 处截断，左侧
对齐并用空格填充，位置
23-32

第 5 列 - 名字，从 C 填写，
在 8 处截断，左对齐并填充
带坯料，位置 33-40

第 6 栏 - 总工资/季度，
从 D 填充，去除所有格式，
右对齐零填充，位置
41-49

第 7 列 - 静态代码，从不
更改，位置 50-51

第 8 栏 - BLANKS，填空，
位置 52-80

我假设有 3 个选项:

VBA

.NET

SQL

我首先探索了 .NET 方法，但我找不到合适的文档来帮助我前进。我仍然喜欢这个，但我离题了。

接下来，我有一些 VBA 可以将工作表转储为固定宽度的文本。我目前正在追求这一点，这最终导致了我的实际问题。

如何在 Excel 中转换文本范围？我是否需要将它复制到另一张纸上，然后使用必要的格式化功能传递该数据以运行我的 Dump to text 例程？我目前计划为每一列设置一个函数，但我无法弄清楚如何进行下一步。我在 Office 编程和开发方面相当新，所以任何见解都将不胜感激。

SQL 选项将是我的退路，因为我过去曾从 SQL 中进行过类似的导出。我只是更喜欢其他两个关于“我不想负责运行这个”的原则。

提前感谢您提供的任何时间。

Answer 1

使用 VBA 似乎是我的方法。这使您可以编写一个处理所有各种格式选项的宏，并且应该足够简单，让您的财务人员可以自行运行。

你说你需要一些可以在 Excel 中获取工作表或范围的东西。第一列永远不会改变，因此我们可以将其存储在宏中，第 3-7 列来自电子表格，第 8 列只是空白。这使得第 2 列(季度/年度为 QYY)成为一个问题。如果在工作簿中的某处指定了季度/年份(例如，存储在单元格中，作为工作表名称，作为工作簿标题的一部分)，那么我们可以直接读入它。否则您将需要找到一些指定季度的方法/year 当宏运行时(例如弹出一个对话框并要求用户输入它)

一些简单的代码(我们稍后会考虑如何调用它):

Sub ProduceStatePayrollReportFile(rngPayrollData As Range, strCompanyNo As String, _
    strQuarterYear As String, strRecordCode As String, strOutputFile As String)

参数相当明显:保存数据的范围、第 1 列的公司编号、第 2 列的季度/年、第 7 列的固定代码以及我们要将结果输出到的文件

' Store the file handle for the output file
Dim fnOutPayrollReport As Integer
' Store each line of the output file
Dim strPayrollReportLine As String
' Use to work through each row in the range
Dim indexRow As Integer

要在 VBA 中输出到文件，我们需要获取文件句柄，因此我们需要一个变量来存储它。我们将在报告行字符串中构建报告的每一行，并使用行索引来处理范围

' Store the raw SSN, last name, first name and wages data
Dim strRawSSN As String
Dim strRawLastName As String
Dim strRawFirstName As String
Dim strRawWages As String
Dim currencyRawWages As Currency

' Store the corrected SSN, last name, first name and wages data
Dim strCleanSSN As String
Dim strCleanLastName As String
Dim strCleanFirstName As String
Dim strCleanWages As String

这些变量集分别存储来自工作表的原始数据和要输出到文件的清理数据。将它们命名为“raw”和“clean”可以更容易地发现您不小心输出原始数据而不是清理数据的错误。我们需要将原始工资从字符串值更改为数值以帮助格式化

' Open up the output file
fnOutPayrollReport = FreeFile()
Open strOutputFile For Output As #fnOutPayrollReport

FreeFile() 获取下一个可用的文件句柄，我们用它来链接到文件

' Work through each row in the range
For indexRow = 1 To rngPayrollData.Rows.Count
    ' Reset the output report line to be empty
    strPayrollReportLine = ""
    ' Add the company number to the report line (assumption: already correctly formatted)
    strPayrollReportLine = strPayrollReportLine & strCompanyNo
    ' Add in the quarter/year (assumption: already correctly formatted)
    strPayrollReportLine = strPayrollReportLine & strQuarterYear

在处理每一行的循环中，我们首先清除输出字符串，然后添加第 1 列和第 2 列的值

' Get the raw SSN data, clean it and append to the report line
strRawSSN = rngPayrollData.Cells(indexRow, 1)
strCleanSSN = cleanFromRawSSN(strRawSSN)
strPayrollReportLine = strPayrollReportLine & strCleanSSN

.Cells(indexRow, 1) 部分仅表示 indexRow 指定的行中范围的最左侧列。如果范围从 A 列开始(不一定是这种情况)，那么这仅表示 A。我们稍后需要自己编写 cleanFromRawSSN 函数

' Get the raw last and first names, clean them and append them
strRawLastName = rngPayrollData.Cells(indexRow, 2)
strCleanLastName = Format(Left$(strRawLastName, 10), "!@@@@@@@@@@")
strPayrollReportLine = strPayrollReportLine & strCleanLastName

strRawFirstName = rngPayrollData.Cells(indexRow, 3)
strCleanFirstName = Format(Left$(strRawFirstName, 8), "!@@@@@@@@")
strPayrollReportLine = strPayrollReportLine & strCleanFirstName

Left$(string, length) 将字符串截断为给定的长度。格式图片 !@@@@@@@@@@ 将字符串格式化为正好十个字符长，左对齐(! 表示左对齐)并用空格填充

' Read in the wages data, convert to numeric data, lose the decimal, clean it and append it
strRawWages = rngPayrollData.Cells(indexRow, 4)
currencyRawWages = CCur(strRawWages)
currencyRawWages = currencyRawWages * 100
strCleanWages = Format(currencyRawWages, "000000000")
strPayrollReportLine = strPayrollReportLine & strCleanWages

我们将其转换为货币，以便乘以 100 将美分值移动到小数点左侧。这使得使用 Format 生成正确值变得更加容易。这不会为工资 >= 1000 万美元产生正确的输出，但这是用于报告的文件格式的限制。格式图片中的 0 用 0 填充就足够了

' Append the fixed code for column 7 and the spaces for column 8
strPayrollReportLine = strPayrollReportLine & strRecordCode
strPayrollReportLine = strPayrollReportLine & CStr(String(29, " "))

' Output the line to the file
Print #fnOutPayrollReport, strPayrollReportLine

String(number, char) 函数生成一个 Variant，其中包含指定 number 的 char 序列。 CStr 将 Variant 转换为字符串。 Print # 语句输出到文件，没有任何额外的格式

Next indexRow

' Close the file
Close #fnOutPayrollReport

End Sub

循环到范围内的下一行并重复。当我们处理完所有行后，关闭文件并结束宏

我们仍然需要两件事:一个 cleanFromRawSSN 函数和一种使用相关数据调用宏的方法。

Function cleanFromRawSSN(strRawSSN As String) As String

' Used to index the raw SSN so we can process it one character at a time
Dim indexRawChar As Integer

' Set the return string to be empty
cleanFromRawSSN = ""

' Loop through the raw data and extract the correct characters
For indexRawChar = 1 To Len(strRawSSN)
    ' Check for hyphen
    If (Mid$(strRawSSN, indexRawChar, 1) = "-") Then
        ' do nothing
    ' Check for space
    ElseIf (Mid$(strRawSSN, indexRawChar, 1) = " ") Then
        ' do nothing
    Else
        ' Output character
        cleanFromRawSSN = cleanFromRawSSN & Mid$(strRawSSN, indexRawChar, 1)
    End If
Next indexRawChar

' Check for correct length and return empty string if incorrect
If (Len(cleanFromRawSSN) <> 9) Then
    cleanFromRawSSN = ""
End If

End Function

Len 返回字符串的长度， Mid$(string, start, length) 从 length 开始返回 string 字符，从 start 开始。此功能可以改进，因为它目前不检查非数字数据

调用宏:

Sub CallPayrollReport()

ProduceStatePayrollReportFile Application.Selection, "1234560007", "109", "01", "C:\payroll109.txt"

End Sub

这是最简单的调用方式。范围是用户在事件工作簿中的事件工作表上选择的任何内容，其他值是硬编码的。用户应该选择他们想要输出到文件的范围，然后转到 Tools > Macro > Run 并选择 CallPayrollReport 。为此，宏要么需要成为包含数据的工作簿的一部分，要么位于用户调用宏之前已加载的不同工作簿中。

在生成每个季度的报告之前，有人需要更改季度/年度的硬编码值。如前所述，如果季度/年度已经存储在工作簿中的某处，那么最好阅读它而不是对其进行硬编码

希望这是有道理的，有一定的用处