获取Excel单元格内容并将其放入格式化的.txt文件中 [英] Take Excel cell content and place it into a formatted .txt file

问题描述

所以我有一个Excel文档，它本质上是一个表.

为此，我们将使用以下代码: 虚拟数据

我想要做的是从这些单元格中获取值并将它们放入.txt文件中.但是，文本文件具有某些必需的格式，并且在电子表格的每个条目"(行)中遵循一种模式.

例如

***********
**THIS IS A FANCY TEXT FILE**
***********
<Start of entry text>
Predefined text here (Entry 1): $A1
Predefined text here:           $B1,$C1
Predefined text here:           $D1,$E1,$F1
Predefined text here: $G1,$H1
Predefined text here: $I1
Predefined text here: $J1,$K1,$L1
Predefined text here: $M1,$N1
<End of entry text>

<Start of entry text>
"As Above"
<End of entry text>
etc, etc

此外，如果可能的话，将所有值排在一起也是有益的. IE. 就像前三行一样，而不是后四行.

我希望能够运行一个脚本，然后该脚本将输出带有一些预定义数据的.txt文件，然后输出表格中每个条目的单元格相关值.我假设我需要某种"For Each"循环，并且我必须定义许多变量，我本质上并不是真正的编码人员，而在绝对需要时才涉猎. >

非常感谢您可以提供的任何帮助.

尽管我也对潜在的Powershell解决方案持开放态度，但我认为VBA可能是最受欢迎的选择.

• XViper

---

好吧，所以我研究了一些Powershell选项，并认为我想采用这种方法.

我设法使用Import-XLS函数使其正常工作.但是我不确定如何将输出结果转换为.txt文件.

Import-XLS '.\DummyData.xlsx' | Foreach-Object{
Write-host "Predefined data:" `t $_."Value 1"
Write-host "Predata:" `t`t`t $_."Value 2"

哪个给我这个.

Predefined data:     AA
Predata:             11
Predefined data:     BB
Predata:             BB11
Predefined data:     CC
Predata:             CC11
Predefined data:     DD
Predata:             DD11
Predefined data:     EE
Predata:             EE11
Predefined data:     FF
Predata:             FF11
Predefined data:     GG
Predata:             GG11
Predefined data:     HH
Predata:             HH11

我知道我不能使用Write-Host进行输出，但是如何收集"所有数据，然后将其最后输出"ForEach"到.txt?

我可以全部输出吗?还是我需要一次输出每一行?

谢谢！

---

好吧...所以我尝试了更多的东西.

$OutFile = ".\OutTest$(get-date -Format dd-MM).txt"

$Content = Import-XLS '.\DummyData.xlsx'

$Content | foreach-object{
Write-Output "Text1" $_."Value 1"
Write-Output "Text2" $_."Value 2"

} | Out-File $OutFile

这似乎可行，但是问题是，现在一切都换了一行.

所以我明白了:

Text1
AA
Text2
11
Text1
BB
Text2
BB11

我需要能够像设置Write-Host一样格式化/排版文本. 这可能吗?

值1"需要与文本1"在同一行

我可能还需要做

在某些行上显示文本1:$ Value1，$ Value2，$ Value3".

再次感谢！

解决方案

使用 Windows PowerShell -f 格式运算符，请参见 about_Operators 帮助主题，例如Get-Help 'about_Operators' -ShowWindow:

-f Format operator
      Formats strings by using the format method of string 
      objects. Enter the format string on the left side of the operator 
      and the objects to be formatted on the right side of the operator.


         C:\PS> "{0} {1,-10} {2:N}" -f 1,"hello",[math]::pi
         1 hello      3.14

有关更多信息，请参见 String.Format方法和复合格式.

示例:

Get-ChildItem | ForEach-Object { Write-Output $("{0},{1}" -f "Text1", $_."Name") }
###                                                       ↑

示例输出:

Text1,Downloaded
Text1,SF
Text1,SO
Text1,SU
Text1,addF7.ps1

编辑以回答扩展的问题:那么，我将如何编写代码以排除编写Null值的输出?您可以使用 Split(Char(), StringSplitOptions)表单，

输出

Item x:1,,3,,
Item x:1,3

So I have an Excel document, which is essentially a table.

For the purposes of this, we will use this: Dummy Data

What I'm wanting to do is take the values from these cells and place them into a .txt file. However the text file has some required formatting, and follows a pattern per 'entry' (row) of the spreadsheet.

E.g.

***********
**THIS IS A FANCY TEXT FILE**
***********
<Start of entry text>
Predefined text here (Entry 1): $A1
Predefined text here:           $B1,$C1
Predefined text here:           $D1,$E1,$F1
Predefined text here: $G1,$H1
Predefined text here: $I1
Predefined text here: $J1,$K1,$L1
Predefined text here: $M1,$N1
<End of entry text>

<Start of entry text>
"As Above"
<End of entry text>
etc, etc

Also, if possible it would be beneficial to have all the values lined together. ie. Like the first three lines are, as opposed to the last 4.

I'd like to be able to run a script that will then output a .txt file with some predefined data, followed by the relevant values of the cells for each entry in the table. I'm assuming I'll need some kind of 'For Each' loop, and I'll have to define the many variables, I'm just not really a coder by nature, and only dabble when I absolutely have to.

Thanks so much in advance for any help you can give.

I'm assuming VBA might be the most popular option, although I'm open to a potential powershell solution as well.

• XViper

---

Ok, so I've looked into some powershell options and think I would like to take that approach.

I've managed to get it working using an Import-XLS Function. However I'm not sure how to get the output to a .txt file.

Import-XLS '.\DummyData.xlsx' | Foreach-Object{
Write-host "Predefined data:" `t $_."Value 1"
Write-host "Predata:" `t`t`t $_."Value 2"

Which gives me this.

Predefined data:     AA
Predata:             11
Predefined data:     BB
Predata:             BB11
Predefined data:     CC
Predata:             CC11
Predefined data:     DD
Predata:             DD11
Predefined data:     EE
Predata:             EE11
Predefined data:     FF
Predata:             FF11
Predefined data:     GG
Predata:             GG11
Predefined data:     HH
Predata:             HH11

I know I can't use Write-Host to output, but how can I 'collect' all that data, and then have it output 'ForEach' to a .txt at the end?

Can I output it all collectively? or do I need to output each line one at a time?

Thanks!

---

Ok... so I've tried some more stuff.

$OutFile = ".\OutTest$(get-date -Format dd-MM).txt"

$Content = Import-XLS '.\DummyData.xlsx'

$Content | foreach-object{
Write-Output "Text1" $_."Value 1"
Write-Output "Text2" $_."Value 2"

} | Out-File $OutFile

So this appears to work, however the problem is, everything is on a new line now.

So I get this:

Text1
AA
Text2
11
Text1
BB
Text2
BB11

I need to be able to format/layout the text like I am able to using Write-Host. Is this possible?

"Value 1" NEEDS to be on the same line as "Text1"

I may also need to do

"Text1: $Value1, $Value2, $Value3" on some lines.

Thanks again!

解决方案

Format your line as desired using Windows PowerShell -f Format operator, see about_Operators help topic, e.g. Get-Help 'about_Operators' -ShowWindow:

-f Format operator
      Formats strings by using the format method of string 
      objects. Enter the format string on the left side of the operator 
      and the objects to be formatted on the right side of the operator.


         C:\PS> "{0} {1,-10} {2:N}" -f 1,"hello",[math]::pi
         1 hello      3.14

For more information, see the String.Format method and Composite Formatting.

Example:

Get-ChildItem | ForEach-Object { Write-Output $("{0},{1}" -f "Text1", $_."Name") }
###                                                       ↑

Sample output:

Text1,Downloaded
Text1,SF
Text1,SO
Text1,SU
Text1,addF7.ps1

Edit to answer extending question: So, how would I go about writing my code so as to exclude writing the Output for Null values? You could use Split(Char(), StringSplitOptions) form of String.Split Method from .NET framework and join the result back into a single string as follows (although maybe there are smarter straightforward methods):

$strng = "{0}:{1},{2},{3},{4},{5}" -f "Item x", 1, $null, 3, $null, $null
$strng
$strng.Split(",",[System.StringSplitOptions]::RemoveEmptyEntries) -join ","

Output

Item x:1,,3,,
Item x:1,3

这篇关于获取Excel单元格内容并将其放入格式化的.txt文件中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！