试图使用PowerShell模拟Excel中的转换为数字功能

Question

我每天收到一个文件，我需要将数据插入然后处理并格式化它以获得有用的报告。我对PowerShell非常陌生，但我正在尝试将其自动化以节省一些时间，并边走边学。

我现在遇到的问题是列A的数值存储为文本。当手动格式化时，我通常会点击单元格旁边的弹出框，然后给出一个"转换为数字"的选项。如何使用PowerShell模拟这一点?我尝试了几种不同的方法，这些方法允许我格式化文本数字，但没有一种方法能够将其从文本转换为数字。另一种手动操作的方法是进入Data ->文本到列，但即使手动这样做似乎也不是合适的方式。

我需要这作为一个数字，而不是文本，因为在以后的过程中，它将在vlookup中使用。此外，这一列位于我收到的初始报告中，该报告是一个.xlsx文件，而不是我从数据库中提取的数据的一部分。

$date = (Get-Date).tostring("yyyy.MM.dd.")
$dbserver = "server"
$dbdatabase = "database"
$dbuser = "user"
$dbpass = "password"
$conn = New-Object system.data.sqlclient.sqlconnection
$conn.connectionstring = "server=$dbserver;database=$dbdatabase;user id=$dbuser;password=$dbpass;"
$query = @"
SELECT DISTINCT
ORS.Reference2 AS 'CDL OrderID',
ORS.PKID AS 'Sonic Tracking',
D.DisplayCode AS 'HUB',
ZRZ.RouteID AS 'Default Route',
ORS.ROUTEID AS 'Assigned Route',
CAST(ORS.CREATEDWHEN AS DATE) AS 'Order Created Date',
p2.createdwhen  AS 'RECEIVE Scan Date',
CASE WHEN p2.createdby like 'driver 9[0-9][0-9][0-9]' then 'Y'
ELSE ''    
END AS 'Scanned?',
ORS.POSTDATE AS 'Post Date',
ors.pod AS 'POD',
ORS.COMPLETEDTIME AS 'CompletedTime',
(STUFF((SELECT ', ', EC.ExceptionDesc, ' ( Added: ',  OSC.createdWhen, ' By: ',OSC.createdBy, ') ' 
FROM tblOrderRouteStops_StatusCodes OSC 
LEFT JOIN tblexceptioncodes ec on osc.statuscode = ec.exceptionid
WHERE OSC.ORSID = ORS.PKID 
ORDER BY osc.createdwhen
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, ' ') )
AS  'Status Code'     
FROM tblOrderRouteStops ORS
LEFT JOIN tblParcel P ON ORS.PKID = P.ORDERID and p.reference like '128%AP1'
Left Join tblParcel p2 on p.reference = p2.reference and p2.createdby like 'driver 9[0-9][0-9][0-9]' 
LEFT JOIN tblZones Z ON ORS.Zip = Z.Zip
LEFT JOIN tblDepots D ON Z.DepotID = D.DepotID
LEFT JOIN tblZonesRouteZones ZRZ ON Z.ZoneID = ZRZ.ZoneID AND ZRZ.ROUTELOOKUPID = 1
LEFT JOIN tblOrderRouteStops_StatusCodes OSC ON ORS.PKID = OSC.ORSID
LEFT JOIN tblExceptionCodes EC ON OSC.statusCode = EC.ExceptionID
WHERE 1=1

AND ORS.CUSTID = 3919
AND ORS.CREATEDWHEN > '2021-06-10'
AND ORS.REFERENCE2 <> ''

ORDER BY ORS.pkid DESC
"@
$sqlcmd = New-Object system.data.sqlclient.sqlcommand
$sqlcmd.CommandText = $query
$sqlcmd.Connection = $conn
$sqladpt = New-Object system.data.sqlclient.sqldataadapter
$sqladpt.selectcommand = $sqlcmd
$data = new-object system.data.dataset
$sqladpt.fill($data)
$dataset = $data.Tables[0] 

#$wb = 'C:UsersjthompsonDesktopCDL Daily' + $date + 'ReviewOrders.xlsx'
$wb = 'C:UsersjthompsonDesktopCDL Dailytest.xlsx'
$excel = new-object -ComObject excel.application
$excel.visible = $false
$excel.DisplayAlerts = $false
$wb = $excel.workbooks.open($wb)
$ws1 = $wb.worksheets.item(1)
$ws2 = $wb.worksheets.add()
$dataset | convertto-csv -notype -delimiter `t | clip
[void]$ws2.cells.item(1).pastespecial()
$ws2.usedrange.entirecolumn.autofit()
$ws1.UsedRange.horizontalalignment = -4131
$ws2.UsedRange.horizontalalignment = -4131
$ws2.columns.item(6).numberformat = "MM/dd/yyy"
$ws2.columns.item(7).numberformat = "MM/dd/yyy"
$ws2.columns.item(9).numberformat = "MM/dd/yyy"
$ws2.columns.item(1).numberformat = "0.000001"
//this will format it with the correct decimals, but does not change it from text to number  
$ws1.columns.item(1).numberformat = 0.000001

$wb.Save()
$wb.close()
$excel.Quit()
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel)
Remove-Variable excel

---

编辑显示单元格中数字的消息格式为文本

格式单元格对话框

澄清一下，在我使用numberFormat之后，在"格式化单元格"中对话框，它声明它们被格式化为6位小数。然而，他们仍然有绿色的三角形，和弹出的消息"此单元格中的数字格式为文本或前面有撇号"即使我手动格式化它，我发现将其从文本更改为数字的唯一方法是单击"转换为数字"。或"文本到列"。更改"格式单元格"中的格式似乎对这个没有影响。

不，它不是表，也没有链接到数据导入。我从客户那里收到这个报告，所以它没有连接或依赖项。

Answer 1

更新(2022年2月7日)，见此题:

根据您的Excel版本，您可能需要使用.Value2或.Value(10)而不是.Value:

$ws1.Columns.Item(1).Value2 = $ws1.Columns.Item(1).Value2

---

原始答:

与此类似，您可以使用:

$ws1.Columns.Item(1).Value = $ws1.Columns.Item(1).Value

注意，单元格的格式和它的基础值是两个独立的东西。更改NumberFormat(手动或编程)不会更改底层值。

Answer 2

NumberFormat是正确的属性-试试:

$excel = new-object -ComObject excel.application
$excel.visible = $false
$excel.DisplayAlerts = $false
$wb = $excel.workbooks.open('c:temptest.xlsx')
$ws1 = $wb.worksheets.item(1)
$ws1.Columns.Item(1).NumberFormat = "#,##0.000000"
$wb.Save()
$wb.close()
$excel.Quit()

我用一个非常简单的。xlsx文档:

进行了测试

A B
- -
1 A
2 B
3 C

在更改格式之前，我使用$ws1.Columns.Item(1).NumberFormat验证了以通用格式保存的数字。它在更改前返回General格式，保存并重新打开文件后返回Number格式。