使用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天前的文件

一个Macrovision未知的服务FlexNet Licensing Service 64

在windows中发现一个不知名的服务——FlexNet Licensing Service 64,网上找了一下也没有具体的说明,找到文件所在目录,看了一下目录下的文件,发现是Tableau安装之后自动安装的服务,再搜索了一下Macrovision,发现这个工具背后也是一个监控的,类似苏州马丁流氓公司一样会给你来律师函的(传送门),赶紧停了服务,并且将相关文件删除了。

C:\Program Files\Common Files\Macrovision Shared\FlexNet Publisher\FNPLicensingService64.exe

Debian 9, Debian 10 以及Ubuntu 关闭IPv6

安装的debian 和 ubuntu,默认都会开启IPv6,但是很多服务商并不分配IPv6,因此大部分的时候我们需要将IPv6 关闭。主要有两种办法,一个是修改sysctl.conf, 或者是在/etc/sysctl.d 目录下创建一个.conf 文件

方法1:
编辑/etc/sysctl.conf 文件,在文件的最末尾添加下面的entry:

net.ipv6.conf.all.disable_ipv6 = 1

如果仅想关闭某一网卡的ipv6,比如说ens4, 那就可以添加下面的entry:

net.ipv6.conf.ens4.disable_ipv6 = 1

让命令生效:

sysctl -p

方法2:
在/etc/sysctl.d 目录下创建70-disable-ipv6.conf

nano /etc/sysctl.d/70-disable-ipv6.conf

添加下面的entry:

net.ipv6.conf.all.disable_ipv6 = 1

如果仅仅想关闭某一网卡,比如说ens4, 添加下面的entry:

net.ipv6.conf.ens4.disable_ipv6 = 1

立刻生效:

sysctl -p -f /etc/sysctl.d/70-disable-ipv6.conf

就是这么简单

小新Pro AMD版升级BOIS到F0EC27WW后未知设备(ACPI\VEN_LHK&DEV_2019)

联想小新Pro AMD版已经上市半年多了,依旧很香。
最近比较闲,看着官方的BOIS已经升级了好几次,我就想着更新一下吧,结果一个更新就出现了一个新的未知设备
ACPI\VEN_LHK&DEV_2019
Google了一圈也没有找到,结果还是去联想自己的论坛去看了一下,最后说是Lenovo Fn and Function Keys的驱动,可是在Pro 13的地方没有找到呀,最后在小新-15 2020(AMD平台:ARE版)找到的此驱动,下载安装后此问题顺利解决此未知设备。

V2RAY官方安装脚本失效后处理

有一台安装了V2RAY的服务器出现了故障,所以需要重新安装V2RAY,直接用官网的脚本

bash <(curl -L -s https://install.direct/go.sh)

进行安装发现无法安装,上github发现v2ray的作者已经不再维护这个项目了,项目转由V2fly进行维护开发,用v2fly的一键安装脚本发现无法安装,折腾了几个小时就放弃了,想着继续用v2ray脚本安装,在github中找到相关老版本的Tags,将在release目录下找到install-release.sh文件,将v2ray安装源文件定义到老版本就可以了。

源文件中的下载地址:

DOWNLOAD_LINK="https://github.com/v2fly/v2ray-core/releases/download/${NEW_VER}/v2ray-linux-${VDIS}.zip"

修改为老版本下载地址:

DOWNLOAD_LINK="https://github.com/v2fly/v2ray-core/releases/download/v4.23.4/v2ray-linux-64.zip"

修改完毕之后,就可以妥妥的安装老版本的v2ray了。