用powershell+excel行列转置三步走

阅读量：5009 次

发布时间：2019-06-12

本文共 4512 字，大约阅读时间需要 15 分钟。

本文重点讲解第一步，手动在excel表中输入公式，或者用powershell自动输入公式。

第二步，用powershell向excel中写入数据，略。

第三步，用powershell从excel中读取数据，略。

本人原创拙作，声明网上唯一。

搜索过的关键字：powershell excel 行列转置转换

搜索过的引擎：百度 bing

powershell 传教士原创文章 2014-07-12，2015-03-03，2016-07-17 修改。允许转载，但必须保留名字和出处，否则追究法律责任

---【第一章概述】---

用excel + powershell来数据处理，太方便。大型数据处理当然要用几种主流的数据库了，这不用多说。对于差不多少于一亿条的【中小型】数据处理，建议用excel+powershell来处理。优点如下：

1 excel是c代码的编译程序，性能没问题，要比powershell处理得快。64位win的话，用64位excel就更好了。

2 excel简单，会的人多。

3 excel可以输出各种统计图，表，打印。

4 每个excel单元格，可以看作是一个变量。首先设置单元格的格式，也就是变量类型是数值啊还是日期等。然后在变量中存东西。

5 每个excel公式可以看成是表达式，或者叫做存储过程。

6 每个excel文件（工作簿），可以认为是一个数据库。每个工作表可以看作是数据库中的表，表中含有的行是记录，列是字段。

7 只用powershell做存取，不做任何运算，运算都丢给excel公式！。用excel公式写具体的数据处理算法，如第三个单元格中的内容等于第一个单元格加第二个单元格。

8 excel实际上是可以连接数据源，也就是从数据库中存取常用的数据类型。

---【第二章人+键盘+鼠标+excel 手动实现行列转置教程】---

从excel2007开始， excel 支持的最大行数 = 1048576 excel 支持的最大列数 = 16384 所以excel行列转置最多支持16384 x 16384 这样方阵的转置。

首先讲解如何手动操作，设置转置：

1打开excel，在sheet1中，做好一个表，如图1

2在sheet2中，选中第一个格，输入公式=TRANSPOSE(Sheet1!A1:D11)，然后选中a1---d11,按f2，按ctrl+shift+enter

3修改第一个工作本中的内容，第二个工作本也变了，证明成功。

一般来讲，建议按照上述教程，用键盘鼠标，手动输入公式。而不是powershell输入公式。而powershell只做数据读写。

---【第三章用powershell向sheet1写入数据（略）】---

---【第四章用powershell向sheet2写入公式】---

一般来讲，建议按照上述教程，用键盘鼠标，手动输入公式。而不是powershell输入excel公式。而powershell只做数据读写。当然如果你非要用excel输入公式，那么请看：

# powershell 行列转置例子<#行列转置公式为：=TRANSPOSE(Sheet1!A1:XFD16384){
    =TRANSPOSE(Sheet1!A1:CV100)}{
    =TRANSPOSE(Sheet1!A1:KUR8000)}#>Write-Warning  '支持最大行列为 16384,行列可以不是正方形，但建议在6000 * 6000条以下，大内存可以8000 * 8000'$script:脚本存储路径 = Split-Path -Parent   $myinvocation.mycommand.path$Env:Path += ";$script:脚本存储路径;"#region main$excel文件 = 'a:\pscode\temp122\z转置测试002.xlsx'$excel对象 = New-Object -comobject Excel.Application$SheetName = "sheet2"$Excel工作簿 = $excel对象.Workbooks.Open($excel文件)$Excel工作表 = $Excel工作簿.Worksheets.Item($SheetName)$excel对象.Visible = $true#region  向sheet2写入公式# 一般来讲，建议按照上述教程，用键盘鼠标，手动输入公式。而不是powershell输入公式。而powershell只做数据读写。# 没找到直接写入公式的方法，暂时先用笨方法。[system.reflection.assembly]::loadwithpartialname("System.Windows.Forms") | Out-Null$范围1 = $Excel工作表.range("A1:CV100") [string]$temp0001 = '=TRANSPOSE{(}Sheet1!A1:CV100{)}'$范围1.select()#此为 powershell 传教士 原创文章 中配套的脚本 2014-07-12 #公式栏 sleep 1[System.Windows.Forms.SendKeys]::SendWait("{F3}") | Out-Null#粘贴sleep 1[System.Windows.Forms.SendKeys]::SendWait($temp0001) | Out-Nullsleep 1[System.Windows.Forms.SendKeys]::SendWait("^+{ENTER}") | Out-Null# 重新计算公式sleep 1[System.Windows.Forms.SendKeys]::SendWait("+{F9}") | Out-Nullsleep 1$excel对象.Visible = $false#powershell  行列 转置  转换  excel  powershell传教士  2014-07-12sleep 2$excel对象.save()sleep 2#endregion  向sheet2写入公式#region 向sheet1写入行列数据# 略，注意，写完保存#endregion 向sheet1写入行列数据#region  从sheet2读取值# 略，注意，过滤 “0”值#endregion  从sheet2读取值#region  扫尾Start-Sleep 2$Excel工作簿.close()Remove-Item  "$home\documents\resume.xlw" -ErrorAction:SilentlyContinueStart-Sleep 2$excel对象.save()sleep 2$excel对象.Quit()Start-Sleep 2[System.Runtime.InteropServices.Marshal]::ReleaseComObject($范围1)[System.Runtime.InteropServices.Marshal]::ReleaseComObject($Excel工作表)[System.Runtime.InteropServices.Marshal]::ReleaseComObject($Excel工作簿)[System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel对象)[System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel对象)Start-Sleep 2[System.GC]::Collect()[System.GC]::WaitForPendingFinalizers()Remove-Item  "$home\documents\resume.xlw" -ErrorAction:SilentlyContinueWrite-Host "------------end-------------"Write-Host "---脚本运行完毕后，请输入exit命令。或，关闭powershell窗口，后台excel.exe即可自动退出。---"exit 0#endregion  扫尾#endregion main