时间戳

首页 > 解决方案 > 尝试使用 PowerShell 模拟 Excel 中的转换为数字函数

sql-server - 尝试使用 PowerShell 模拟 Excel 中的转换为数字函数

问题描述

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

我现在遇到的问题是 A 列的数值存储为文本。在手动格式化时，我通常会单击单元格旁边的弹出窗口，然后提供“转换为数字”选项。如何使用 PowerShell 进行模拟？我尝试了几种不同的方法来格式化文本编号，但没有一个能够将其从文本转换为数字。手动执行此操作的另一种方法是转到数据 -> 文本到列，但即使手动执行此操作似乎也不合适。

我需要将其作为数字而不是文本，因为在此过程的后期它将在 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:\Users\jthompson\Desktop\CDL Daily\' + $date + 'ReviewOrders.xlsx'
$wb = 'C:\Users\jthompson\Desktop\CDL Daily\test.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 位小数。但是，它们仍然有绿色三角形，并且弹出消息“此单元格中的数字被格式化为文本或前面带有撇号”。即使我手动格式化它，我发现将其从文本更改为数字的唯一方法是单击“转换为数字”或“文本到列”。更改“格式单元格”中的格式似乎对此没有影响。

不，它不是表格，也没有链接到数据导入。我从客户那里收到此报告，因此它没有连接或依赖关系。

标签： sql-serverexcelpowershell

解决方案

更新（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（手动或以编程方式）不会更改基础值。

推荐阅读