$xlsxPath = $args[0]
|
if (-not $xlsxPath) { throw 'missing xlsx path' }
|
|
function U([string[]]$hexes) {
|
return -join ($hexes | ForEach-Object { [char][Convert]::ToInt32($_, 16) })
|
}
|
|
$ownerName = U @('738B','53CC','9F99')
|
$supervisorName = U @('4E3B','7BA1')
|
$dealWord = U @('6210','4EA4')
|
$followWord = U @('8DDF','8FDB')
|
$companyPrefix = U @('672A','586B','5199','516C','53F8','FF08')
|
$rightParen = U @('FF09')
|
$sourceJd = U @('4EAC','4E1C')
|
$funnelDeal = (U @('0053','0037','0020','6210','4EA4','002F','6267','884C'))
|
$funnelContacted = (U @('0053','0032','0020','5DF2','8054','7CFB'))
|
$funnelNew = (U @('0053','0031','0020','65B0','7EBF','7D22'))
|
$statusDeal = U @('5DF2','6210','4EA4')
|
$statusNoDeal = U @('672A','6210','4EA4')
|
$starCopper = U @('94DC')
|
$labelCustomerNo = U @('539F','5BA2','6237','7F16','53F7','FF1A')
|
$labelCategory = U @('539F','8868','7C7B','522B','002F','610F','5411','FF1A')
|
$labelIndustry = U @('6240','5C5E','884C','4E1A','FF1A')
|
$labelPurpose = U @('7528','9014','FF1A')
|
$labelWebsite = U @('5BA2','6237','5B98','7F51','FF1A')
|
$labelPosition = U @('90E8','95E8','4E0E','804C','4F4D','FF1A')
|
$labelNeed = U @('5BA2','6237','9700','6C42','FF1A')
|
$labelSolution = U @('89E3','51B3','65B9','6848','FF1A')
|
$labelDealReason = U @('6210','4EA4','539F','56E0','FF1A')
|
$labelLoseReason = U @('672A','6210','4EA4','539F','56E0','FF1A')
|
$labelModel = U @('578B','53F7','FF1A')
|
$labelPrice = U @('4EF7','683C','FF1A')
|
$quoteType = U @('539F','8868','62A5','4EF7')
|
$approvalText = U @('5F85','786E','8BA4')
|
$quoteRemark = U @('4ECE','738B','53CC','9F99','9500','552E','8BB0','5F55','8868','5BFC','5165')
|
$followChannel = U @('539F','8868','5BFC','5165')
|
|
$api = 'http://127.0.0.1:8090'
|
$login = Invoke-RestMethod -Method Post -Uri "$api/api/login" -ContentType 'application/json; charset=utf-8' -Body '{"username":"admin","password":"123456"}'
|
$token = $login.token
|
$headers = @{ Authorization = "Bearer $token" }
|
|
$tmp = Join-Path $env:TEMP ('crm_xlsx_' + [guid]::NewGuid())
|
New-Item -ItemType Directory -Path $tmp | Out-Null
|
$zip = Join-Path $tmp 'book.zip'
|
$dir = Join-Path $tmp 'unzipped'
|
Copy-Item -LiteralPath $xlsxPath -Destination $zip
|
Expand-Archive -LiteralPath $zip -DestinationPath $dir -Force
|
|
[xml]$sstXml = Get-Content (Join-Path $dir 'xl\sharedStrings.xml') -Encoding UTF8
|
$shared = @()
|
foreach ($si in $sstXml.sst.si) {
|
$nodes = $si.SelectNodes('.//*[local-name()="t"]')
|
$texts = @()
|
foreach ($n in $nodes) { $texts += $n.InnerText }
|
$shared += ($texts -join '')
|
}
|
|
function Get-ColIndex($ref) {
|
$letters = ($ref -replace '[0-9]', '')
|
$n = 0
|
foreach ($ch in $letters.ToCharArray()) {
|
$n = $n * 26 + ([int][char]$ch - [int][char]'A' + 1)
|
}
|
return $n
|
}
|
|
function Get-CellText($cell) {
|
if ($cell.t -eq 's') {
|
$idx = [int]$cell.v
|
if ($idx -ge 0 -and $idx -lt $shared.Count) { return $shared[$idx] }
|
return ''
|
}
|
if ($cell.t -eq 'inlineStr') { return [string]$cell.is.t }
|
return [string]$cell.v
|
}
|
|
function Convert-DateText($value) {
|
$text = [string]$value
|
if (-not $text) { return '' }
|
$num = 0.0
|
if ([double]::TryParse($text, [ref]$num) -and $num -gt 30000) {
|
return ([datetime]'1899-12-30').AddDays([int]$num).ToString('yyyy-MM-dd')
|
}
|
if ($text -match '^(\d{1,2})\.(\d{1,2})$') {
|
return ('2025-{0:00}-{1:00}' -f [int]$matches[1], [int]$matches[2])
|
}
|
if ($text -match '^(\d{4})[./-](\d{1,2})[./-](\d{1,2})$') {
|
return ('{0}-{1:00}-{2:00}' -f [int]$matches[1], [int]$matches[2], [int]$matches[3])
|
}
|
return ''
|
}
|
|
function Post-Json($path, $payload) {
|
$json = $payload | ConvertTo-Json -Depth 10
|
return Invoke-RestMethod -Method Post -Uri "$api$path" -Headers $headers -ContentType 'application/json; charset=utf-8' -Body $json
|
}
|
|
[xml]$sx = Get-Content (Join-Path $dir 'xl\worksheets\sheet1.xml') -Encoding UTF8
|
$rows = @()
|
foreach ($row in @($sx.worksheet.sheetData.row | Select-Object -Skip 2 -First 10)) {
|
$vals = New-Object string[] 29
|
foreach ($cell in $row.c) {
|
$i = (Get-ColIndex $cell.r) - 1
|
if ($i -ge 0 -and $i -lt 29) { $vals[$i] = (Get-CellText $cell) }
|
}
|
$rows += ,$vals
|
}
|
|
$created = @()
|
$skipped = @()
|
foreach ($r in $rows) {
|
$name = ([string]$r[2]).Trim()
|
$company = ([string]$r[7]).Trim()
|
if (-not $name) { $name = $company }
|
if (-not $company) { $company = $companyPrefix + $name + $rightParen }
|
$firstDate = Convert-DateText $r[19]
|
if (-not $firstDate) { $firstDate = Convert-DateText $r[21] }
|
if (-not $firstDate) { $firstDate = '2025-09-01' }
|
$stage = [string]$r[18]
|
$isDeal = $stage -like "*$dealWord*"
|
$funnel = if ($isDeal) { $funnelDeal } elseif ($stage -like "*$followWord*") { $funnelContacted } else { $funnelNew }
|
$dealStatus = if ($isDeal) { $statusDeal } else { $statusNoDeal }
|
$remarkParts = @(
|
($labelCustomerNo + $r[1]),
|
($labelCategory + $r[0]),
|
($labelIndustry + $r[8]),
|
($labelPurpose + $r[9]),
|
($labelWebsite + $r[11]),
|
($labelPosition + $r[13]),
|
($labelNeed + $r[16]),
|
($labelSolution + $r[17]),
|
($labelDealReason + $r[27]),
|
($labelLoseReason + $r[28])
|
) | Where-Object { $_ -and ($_ -notmatch '[\uFF1A:]$') }
|
$payload = @{
|
user = $supervisorName
|
owner = $ownerName
|
firstReceiver = $ownerName
|
name = $name
|
company = $company
|
phone = [string]$r[3]
|
wechat = [string]$r[4]
|
platformAccount = [string]$r[1]
|
source = if ($r[10]) { [string]$r[10] } else { $sourceJd }
|
customerType = [string]$r[12]
|
scene = if ($r[15]) { [string]$r[15] } else { [string]$r[9] }
|
demand = if ($r[14]) { [string]$r[14] } else { [string]$r[16] }
|
params = $labelModel + $r[5] + '; ' + $labelPrice + $r[6]
|
intention = if ($r[0]) { [string]$r[0] } else { 'C' }
|
funnel = $funnel
|
dealStatus = $dealStatus
|
nextFollowupAt = ''
|
firstConsultAt = $firstDate
|
remark = ($remarkParts -join "`n")
|
}
|
try {
|
$customer = (Post-Json '/api/customers' $payload).customer
|
if ($r[5] -or $r[6]) {
|
Post-Json '/api/quotes' @{
|
customerId = $customer.id
|
user = $ownerName
|
model = [string]$r[5]
|
unitPrice = [string]$r[6]
|
totalPrice = [string]$r[6]
|
priceType = $quoteType
|
approval = $approvalText
|
remark = $quoteRemark
|
} | Out-Null
|
}
|
$followPairs = @( @(19,20), @(21,22), @(23,24), @(25,26) )
|
foreach ($pair in $followPairs) {
|
$at = Convert-DateText $r[$pair[0]]
|
$content = [string]$r[$pair[1]]
|
if ($content.Trim()) {
|
if (-not $at) { $at = $firstDate }
|
Post-Json '/api/followups' @{
|
customerId = $customer.id
|
user = $ownerName
|
at = $at
|
channel = $followChannel
|
funnel = $funnel
|
content = $content
|
nextFollowupAt = ''
|
effective = $true
|
} | Out-Null
|
}
|
}
|
if ($isDeal) {
|
Post-Json '/api/deal' @{
|
customerId = $customer.id
|
user = $ownerName
|
dealStatus = $statusDeal
|
dealAt = $firstDate
|
dealProduct = [string]$r[5]
|
dealQuantity = ''
|
dealUnitPrice = [string]$r[6]
|
dealTotalPrice = [string]$r[6]
|
revisitLevel = $starCopper
|
nextDealRevisit = ''
|
} | Out-Null
|
}
|
$created += "$($customer.id) $name / $company"
|
} catch {
|
$skipped += "$name / $company => $($_.Exception.Message)"
|
}
|
}
|
|
Remove-Item -LiteralPath $tmp -Recurse -Force
|
Write-Output "CREATED:$($created.Count)"
|
$created | ForEach-Object { Write-Output " $_" }
|
Write-Output "SKIPPED:$($skipped.Count)"
|
$skipped | ForEach-Object { Write-Output " $_" }
|
