PowerShell上传文件到SharePoint子目录下

今天客户有一个需求,需要每天定时产生的报表文件上传SharePoint服务器上,网上找了资料,可以上传到相应的根目录下,就是不能上传到子目录下,测试了很多方法都不行,最后在下班的路上通过手机找到了相关资料,进行了一顿修改后,成功将文件放置到客户SharePoint的指定目录下。
原文连接(英文

如果Windows服务器上没有安装过Sharepoint,那就需要安装一个SharePoint Server 2016 Client Components SDK,安装之后需要确定两个DLL文件的对应位置,在执行PowerShell脚本的时需要用到的,具体可以参考我脚本。

#Add references to SharePoint client assemblies and authenticate to Office 365 site – required for CSOM
#Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
#Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
Add-Type -Path "C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.SharePoint.Client\v4.0_16.0.0.0__71e9bce111e9429c\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.SharePoint.Client.Runtime\v4.0_16.0.0.0__71e9bce111e9429c\Microsoft.SharePoint.Client.Runtime.dll"

#Specify tenant admin and site URL
$User = "jzh@one.com"
$SiteURL = "https://one.sharepoint.com/sites/Partners/"
$Folder = "D:\username\test\"
$DocLibName = "Documents"
$FolderName = "Analytics"
$Password = "P@ssword"  | ConvertTo-SecureString -AsPlainText -Force

#Bind to site collection
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Creds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($User,$Password)
$Context.Credentials = $Creds

#Retrieve list
$List = $Context.Web.Lists.GetByTitle($DocLibName)
$Context.Load($List.RootFolder)
$Context.ExecuteQuery()

#Retrieve folder
$FolderToBindTo = $List.RootFolder.Folders
$Context.Load($FolderToBindTo)
$Context.ExecuteQuery()
$FolderToUpload = $FolderToBindTo | Where {$_.Name -eq $FolderName}

#Upload file(s)
Foreach ($File in (dir $Folder -File))
{
    $FileCreationInfo = New-Object Microsoft.SharePoint.Client.FileCreationInformation
    $FileCreationInfo.Overwrite = $true
    $FileCreationInfo.Content = [System.IO.File]::ReadAllBytes($File.FullName)
    $FileCreationInfo.URL = $List.RootFolder.ServerRelativeUrl + "/" + $FolderName + "/" + $File.Name
    $UploadFile = $List.RootFolder.Files.Add($FileCreationInfo)
    $Context.Load($UploadFile)
    $Context.ExecuteQuery()
}

使用Powershell将多个csv文件转换为多个工作簿的Excel文件

从SQLSERVER数据库通过脚本将数据导出成为CSV文件后,就需要将数据合并为多个工作簿的Excel文件,具体代码如下,大家可以参阅一下。

$path="D:\TestFold" #target folder
cd $path;

$csvs = Get-ChildItem .\* -Include *.csv
$y=$csvs.Count
Write-Host "Detected the following CSV files: ($y)"
foreach ($csv in $csvs)
{
Write-Host "—"$csv.Name
}
$outputfilename = $(get-date -f yyyyMMdd) + "_" + "-data.xlsx" #creates file name with date/username
Write-Host Creating: $outputfilename
$excelapp = new-object -comobject Excel.Application
$excelapp.sheetsInNewWorkbook = $csvs.Count
$xlsx = $excelapp.Workbooks.Add()
$sheet=1

foreach ($csv in $csvs)
{
$row=1
$column=1
$worksheet = $xlsx.Worksheets.Item($sheet)
$worksheet.Name = $csv.Name
$file = (Get-Content $csv)
foreach($line in $file)
{
$linecontents=$line -split ',(?!\s*\w+")'
foreach($cell in $linecontents)
{
$worksheet.Cells.Item($row,$column) = $cell
$column++
}
$column=1
$row++
}
$sheet++
}
$output = $path + "\" + $outputfilename
$xlsx.SaveAs($output)
$excelapp.quit()
cd \ #returns to drive root

Powershell脚本在计划任务中处理产生的Excel文件失败的解决方法

PowerShell脚本在PowerShell ISE中执行一切都正常,但是将脚本加入到Windows的计划任务中就出现无法处理Excel文件的问题,最后在网上找到一个方法,在system32目录下创建Destop目录就可以解决相关问题,遇到这样问题的朋友可以试试。


(32位系统&64位系统)
C:\Windows\System32\config\systemprofile\Desktop

(64位系统)
C:\Windows\SysWOW64\config\systemprofile\Desktop

原文:
Interactive Excel permissions

Stumbled upon an interesting one today, where I had a legacy application that needs to open excel to generate some excel spreadsheets. But when this happened i got the usual error:

System.Runtime.InteropServices.COMException (0x800A03EC): Microsoft Office Excel cannot access the file 'c:\temp\test.xls'. There are several possible reasons:
• The file name or path does not exist.
• The file is being used by another program.
• The workbook you are trying to save has the same name as a currently open workbook.

For this example the application is running under an application pool account called ExcelAppPoolAct

Obviously the first step is to grant the correct permissions in component services. To do this:

  1. Navigation to Adminstrative Tools->Component Services->MyComputer->DCOM->Microsoft Excel Application
  2. Right Click on Microsoft Excel Application and select Properties
  3. Click the Security tab
  4. Change Launch and Activation Permissions to Customize
  5. Click Edit
  6. Add ExcelAppPoolAct
  7. Check Local Launch and Local Activation

Next we need to configure the systemprofile directory. You need to create a Desktop folder in one of the following paths, dependant on which version (32-bit or 64-bit) of Microsoft Office you are running.

Office Version Folder
32-bit C:\Windows\System32\config\ systemprofile\
64-bit C:\Windows\SysWOW64\config\systemprofile\

The next step is to configure the appropriate permissions to thefolders for the Application Pool account, ExcelAppPoolAct. Again the folder is dependent on your version (32-bit or 64-bit) of Microsoft Office.

Folder Required Permission
C:\Windows\System32\config\systemprofile\Desktop Read & Execute, List folder contents, Read
C:\Windows\System32\config\systemprofile\AppData\Roaming\Microsoft Modify, Read & Execute, List Folder Content, Read, Write
C:\Windows\System32\config\systemprofile\AppData\Local\Microsoft Modify, Read & Execute, List Folder Content, Read, Write
Folder Required Permission
C:\Windows\SysWOW64\config\systemprofile\Desktop Read & Execute, List folder contents, Read
C:\Windows\SysWOW64\config\systemprofile\AppData\Roaming\Microsoft Modify, Read & Execute, List Folder Content, Read, Write
C:\Windows\SysWOW64\config\systemprofile\AppData\Local\Microsoft Modify, Read & Execute, List Folder Content, Read, Write

I've got a nice piece of powershell that does all of this during installation.

PowerShell从远程连接SQLSERVER导出CSV文件后转Excel后发送到指定邮箱

经过几天的折腾,终于搞定了这个功能,同时也搞定了通过计划任务程序发送乱码的问题,大家可以按照以下脚本进行相关设置,这里需要补充一下,由于查询结果中某些字段会有回车符,需要做一下替换,我替换了2次,保证了文件在一行中。
REPLACE(replace(字段名称 ,CHAR(13),','),CHAR(10),',') as '字段显示名称'

PowerShell代码:

$Subject ="SubjectName-$((Get-Date).ToString("yyyyMMdd_HHmmss"))"
$Directory="设定文件生成的目录需带盘符"
$csvfilename="csv文件名-_$((Get-Date).ToString("yyyyMMdd_HHmmss")).csv"
$xlsxfilename="xlsx文件名-_$((Get-Date).ToString("yyyyMMdd_HHmmss")).xlsx"
$Sendfile="$Directory$xlsxfilename"
$Database = '数据库名称'
$Server = '数据库服务器地址'
$UserName = '数据库用户名'
$Password = '数据库密码'

#数据库查询脚本
$SqlQuery = "SQL查询脚本"
function Export_Excel {
# Accessing Data Base
$SqlConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Data Source=$Server;Initial Catalog=$Database;user id=$UserName;pwd=$Password"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$set = New-Object data.dataset
# Filling Dataset
$SqlAdapter.Fill($set)
# Consuming Data
$Table = $Set.Tables[0] 
$Table | Export-CSV -encoding utf8 -NoTypeInformation $Directory$csvfileName
#$Table
}

function To_Excel {
### Create a new Excel Workbook with one empty sheet
$excel = New-Object -ComObject excel.application 
$workbook = $excel.Workbooks.Add(1)
$worksheet = $workbook.worksheets.Item(1)

### Build the QueryTables.Add command
### QueryTables does the same as when clicking "Data ? From Text" in Excel
$TxtConnector = ("TEXT;" + "$Directory$csvfileName")
$Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1"))
$query = $worksheet.QueryTables.item($Connector.name)
#$delimiter = "\r\n" #Specify the delimiter used in the file
### Set the delimiter (, or ;) according to your regional settings
$query.TextFileOtherDelimiter = $Excel.Application.International(5)

### Set the format to delimited and text for every column
### A trick to create an array of 2s is used with the preceding comma
$query.TextFilePlatform = 65001
#$query.TextFileOtherDelimiter = $delimiter
$query.TextFileParseType  = 1
$query.TextFileColumnDataTypes = ,2 * $worksheet.Cells.Columns.Count
$query.AdjustColumnWidth = 1

### Execute & delete the import query
$query.Refresh()
$query.Delete()

### Save & close the Workbook as XLSX. Change the output extension for Excel 2003
$Workbook.SaveAs("$Directory$xlsxfilename",51)
$excel.Quit()
}

function SendMail {
#发邮件
$smtpServer = "smtp.126.com"
$smtpUser = "SMTP用户名"
$smtpPassword = "SMTP密码"
#$smtp.Send($mail)
$ss=ConvertTo-SecureString -String "$smtpPassword" -AsPlainText -force
$ss|Write-Host
$cre= New-Object System.Management.Automation.PSCredential("$smtpUser",$ss)
Send-MailMessage -to 收件人用逗号间隔 -from 自动邮件<发送邮箱地址> -Subject $subject -SmtpServer "$smtpServer" -Port 25 -Encoding UTF8 -Attachments $Sendfile -Credential $cre
}
Export_Excel
To_Excel
Start-Sleep -Seconds 10
SendMail
echo "remove-item -Force $csvfileName"
remove-item -Force $Directory$csvfileName,$Directory$xlsxfilename #删除两个临时文件

Powershell导出sqlserver数据保存为EXCEL文件并发送邮件脚本

$subject ="邮件主题-$((Get-Date).ToString("yyyyMMdd_hhmmss"))"   
$Database = '数据库名称'
$Server = '数据库地址'
$UserName = '数据库用户名'
$Password = '数据库密码'

#数据库查询脚本
$SqlQuery = "加入SQL查询脚本"
#function Export_Excel {
# Accessing Data Base
$SqlConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Data Source=$Server;Initial Catalog=$Database;user id=$UserName;pwd=$Password"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object data.dataset
# Filling Dataset
$SqlAdapter.Fill($DataSet)
$DataSetTable = $DataSet.Tables["Table"];

## ———- Working with Excel ———- ##

## – Create an Excel Application instance:
$xlsObj = New-Object -ComObject Excel.Application;

## – Create new Workbook and Sheet (Visible = 1 / 0 not visible)
$xlsObj.Visible = 0;
$xlsWb = $xlsobj.Workbooks.Add();
$xlsSh = $xlsWb.Worksheets.item(1);

## – Build the Excel column heading:
[Array] $getColumnNames = $DataSetTable.Columns | Select ColumnName;

## – Build column header:
[Int] $RowHeader = 1;
foreach ($ColH in $getColumnNames)
{
$xlsSh.Cells.item(1, $RowHeader).font.bold = $true;
$xlsSh.Cells.item(1, $RowHeader) = $ColH.ColumnName;
$RowHeader++;
};

## – Adding the data start in row 2 column 1:
[Int] $rowData = 2;
[Int] $colData = 1;

foreach ($rec in $DataSetTable.Rows)
{
foreach ($Coln in $getColumnNames)
{
## – Next line convert cell to be text only:
$xlsSh.Cells.NumberFormat = "@";

## – Populating columns:
$xlsSh.Cells.Item($rowData, $colData) = $rec.$($Coln.ColumnName).ToString();
$ColData++;
};
$rowData++; $ColData = 1;
};

## – Adjusting columns in the Excel sheet:
$xlsRng = $xlsSH.usedRange;
$xlsRng.EntireColumn.AutoFit();

## ———- Saving file and Terminating Excel Application ———- ##

## – Saving Excel file – if the file exist do delete then save
$xlsFile = "带有路径的文件名_$((Get-Date).ToString("yyyyMMdd_hhmmss")).xls";

if (Test-Path $xlsFile)
{
Remove-Item $xlsFile
$xlsObj.ActiveWorkbook.SaveAs($xlsFile);
}
else
{
$xlsObj.ActiveWorkbook.SaveAs($xlsFile);
};

## Quit Excel and Terminate Excel Application process:
$xlsObj.Quit(); (Get-Process Excel*) | foreach ($_) { $_.kill() };

## – End of Script – ##

function SendMail {
#发邮件
$smtpServer = "SMTP服务器地址"
$smtpUser = "SMTP用户名"
$smtpPassword = "SMTP密码"
#$smtp.Send($mail)
$ss=ConvertTo-SecureString -String "$smtpPassword" -AsPlainText -force
$ss|Write-Host
$cre= New-Object System.Management.Automation.PSCredential("$smtpUser",$ss)
Send-MailMessage -to aa@gmail.com,bb@gmail.com -from 发件人显示名称<cc@gmail.com> -Subject $subject -SmtpServer "$smtpServer" -Port 25 -Encoding UTF8 -Attachments $xlsFile  -Credential $cre
}
#Export_Excel
Start-Sleep -Seconds 30
SendMail
echo "remove-item -Force $xlsFile"
remove-item -Force $xlsFile  #删除当前生成的文件

Powershell导出sqlserver数据并发送邮件脚本

用Python创建的邮件服务比较麻烦,后来网上找到可以通过Powershell来连接数据库并导出SQL查询结果为CSV文件,并通过邮箱发送到指定邮箱。

$Date = Get-Date (get-date).AddDays(-1) -uformat "%Y%m%d"
$yDate = Get-Date (get-date).AddDays(-8) -uformat "%Y%m%d"
$fileName = "D:\aa-$Date.csv"
$yfileName = "D:\aa-$yDate.csv"
$Database = '数据库名'
$Server = '192.168.20.33'
$UserName = 'kf'
$Password = '********'
#数据库查询脚本
$SqlQuery = "查询语句"
function Export_Excel {
# Accessing Data Base
$SqlConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Data Source=$Server;Initial Catalog=$Database;user id=$UserName;pwd=$Password"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$set = New-Object data.dataset
# Filling Dataset
$SqlAdapter.Fill($set)
# Consuming Data
$Table = $Set.Tables[0]
$Table | Export-CSV $fileName
#$Table
}
function SendMail {
#发邮件
$smtpServer = "smtp.163.com"
$smtpUser = "dxlyw_2015"
$smtpPassword = "********"
#$smtp.Send($mail)
$ss=ConvertTo-SecureString -String "$smtpPassword" -AsPlainText -force
$ss|Write-Host
$cre= New-Object System.Management.Automation.PSCredential("$smtpUser",$ss)
Send-MailMessage -to "******@163.com" -from "dxlyw_2015@163.com" -Subject "Call Record" -SmtpServer "$smtpServer" -Credential $cre -Attachments $fileName
}
Export_Excel
Start-Sleep -Seconds 60
SendMail
echo "remove-item -Force $fileName"
remove-item -Force $fileName #删除7天前的文件

内网穿透NPS v0.26以上版本以及Win10下问题

OpenWRT固件中集成了NPS内网穿透功能,于是在一台VPS上安装了最新版本0.26.0的NPS,准备在本地WIN10本地和OpenWRT进行调试,都不能调试成功,分别出现如下错误:

OpenWRT下错误:
2020/01/31 13:57:00.809 [I] [npc.go:89]  the version of client is 0.25.1, the core version of client is 0.25.0
2020/01/31 13:57:01.488 [E] [control.go:231]  read tcp 192.168.1.6:56046->107.174.251.xxx:8024: read: connection reset by peer
2020/01/31 13:57:01.488 [E] [client.go:57]  The connection server failed and will be reconnected in five seconds, error read tcp 192.168.1.6:56046->107.174.251.xxx:8024: read: connection reset by peer
2020/01/31 13:57:07.148 [E] [control.go:231]  read tcp 192.168.1.6:56056->107.174.251.xxx:8024: read: connection reset by peer
2020/01/31 13:57:07.149 [E] [client.go:57]  The connection server failed and will be reconnected in five seconds, error read tcp 192.168.1.6:56056->107.174.251.xxx:8024: read: connection reset by peer
2020/01/31 13:57:12.796 [E] [control.go:231]  read tcp 192.168.1.6:56058->107.174.251.xxx:8024: read: connection reset by peer
2020/01/31 13:57:12.796 [E] [client.go:57]  The connection server failed and will be reconnected in five seconds, error read tcp 192.168.1.6:56058->107.174.251.xxx:8024: read: connection reset by peer

WIN10下错误:
2020/01/31 14:00:38.681 [I] [npc.go:173]  the version of client is 0.26.0, the core version of client is 0.26.0
2020/01/31 14:00:38.718 [I] [control.go:97]  Loading configuration file conf/npc.conf successfully
2020/01/31 14:00:39.728 [E] [control.go:111]  dial tcp 127.0.0.1:8024: connectex: No connection could be made because the target machine actively refused it.
2020/01/31 14:00:39.729 [I] [control.go:102]  Reconnecting...
2020/01/31 14:00:45.743 [E] [control.go:111]  dial tcp 127.0.0.1:8024: connectex: No connection could be made because the target machine actively refused it.
2020/01/31 14:00:45.745 [I] [control.go:102]  Reconnecting...

于是决定先解决WIN10下的问题,于是上github作者的issue那里去搜索一下,居然发现不能在PowerShell下运行,要在命令符下运行,于是在命令符下测试一通,成功了。

WIN10搞定了,接下来就是搞OpenWRT,还是在issue中查找,也找到了解决方法,那就是从0.26.0版本开始,NPS需要服务器端和客户端都要是0.26.0版本,否则就会出现连接失败的情况。

赶紧确认一下OpenWRT是什么版本,一看是0.25.1,OpenWRT中就不折腾了,直接将VPS中的NPS降级到0.25,做好相关配置后,再开启服务并确认一下,一切成功,在VPS中看到了客户端已经online。此致NPS内网穿透成功了。