网站离开河蟹国了!

经过这几年的折腾,2010年已经将之前托管的服务器退了,不再继续续租下去,阿拉下载的将来目前还没有明确的定位,目前暂时作为我的个人博客来维持下来。

每天将不定期的来更新一些我的日常砸碎资料。

解决Excel 2016无法直接打开文件的问题

新安装的OFFICE 2016发现从邮件中、微信中都无法直接打开Excel文件,查询了解决方法有2个,大家可以试试看。

1、修改注册表,位置:计算机\HKEY_CLASSES_ROOT\Excel.Sheet.12\shell\Open\command,在最后添加一下“%1”的参数,如果原来有其他参数“DDE”之类的删除,最后的结果为

"C:\Program Files\Microsoft Office\Root\Office16\EXCEL.EXE" "%1"

2、在选项-->高级中取消“忽略使用动态数据交换(DDE)的其他应用程序”前的勾选。

Windows远程桌面连接出现CredSSP的解决方法

今天远程桌面的时候失败了,出现以下提示"CredSSP加密Oracle”错误

于是上网找解决办法,经过测试,该方法是可行的。

首先,在控制台中输入regedit,打开注册表

接着按照以下目录依次展开HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\Policies\System\,接着在System目录下创建两级文件夹,也就是新建项,CredSSP\Parameters,

然后再Parameters下右键-》新建DWORD 32位,修改名称AllowEncryptionOracle。

双击修改值为2.

之后去试一下远程连接桌面,应该就可以了。

RDP 优化操作 微软远程桌面 开启显卡加速、60FPS、USB设备重定向

有一说一,Windows自带的远程桌面服务(RDP)非常优秀,不考虑云服务(可以自己搭建)的情况下,在表现上可以胜过任意一款第三方远程控制软件(包括且不限于VNC、Teamviwer……),毕竟是原生功能。

RDP本身是可以无显卡运行的,显示远程桌面的时候并不调用显卡,可以做一些基本的管理操作。最近研究了一下怎样将提升RDP的性能,开启显卡加速(OpenGL,DX支持),提高传输帧率(默认30fps,设置为60fps),以及USB设备重定向(将客户端的USB设备挂载到远程主机,不需要USB Network Gateway)

Continue reading "RDP 优化操作 微软远程桌面 开启显卡加速、60FPS、USB设备重定向"

Windows系统下的免费SFTP服务器软件

FreeSSHD SFTP Server
其实是有2个软件,一个是freeSSHd,一个是freeFTPd。
freeSSHd 的 sftp,所有用户只有一个 sftp 的主目录。
freeFTPd 的 sftp,可以给不同的用户设定不同的目录。

freeSSHd 可以设置系统账号、公钥、密码的登录方式,不像 xlight 只能在密码、公钥里二选一。
freeFTPd 可以设置系统账号、密码、匿名的登录方式。

Continue reading "Windows系统下的免费SFTP服务器软件"

执行yum update出现Cannot allocate memory错误

CentOS 6在进行yum update后出现如下错误,

Error in POSTIN scriptlet in rpm package libstdc++-4.4.7-23.el6.x86_64
error: Couldn't fork %post(libstdc++-4.4.7-23.el6.x86_64): Cannot allocate memory
  Updating   : pcre-7.8-7.el6.x86_64                                                                                                                                                                                                     5/239
Error in POSTIN scriptlet in rpm package pcre-7.8-7.el6.x86_64
error: Couldn't fork %post(pcre-7.8-7.el6.x86_64): Cannot allocate memory
  Updating   : grep-2.20-6.el6.x86_64                                                                                                                                                                                                    6/239
Error in POSTIN scriptlet in rpm package grep-2.20-6.el6.x86_64
error: Couldn't fork %post(grep-2.20-6.el6.x86_64): Cannot allocate memory
  Updating   : cpio-2.10-13.el6.x86_64                                                                                                                                                                                                   7/239
Error in POSTIN scriptlet in rpm package cpio-2.10-13.el6.x86_64
error: Couldn't fork %post(cpio-2.10-13.el6.x86_64): Cannot allocate memory
  Updating   : sysvinit-tools-2.87-6.dsf.el6.x86_64                                                                                                                                                                                      8/239
  Updating   : tcp_wrappers-libs-7.6-58.el6.x86_64                                                                                                                                                                                       9/239
Error in POSTIN scriptlet in rpm package tcp_wrappers-libs-7.6-58.el6.x86_64
error: Couldn't fork %post(tcp_wrappers-libs-7.6-58.el6.x86_64): Cannot allocate memory
  Updating   : 1:findutils-4.4.2-9.el6.x86_64                                                                                                                                                                                           10/239
Error in POSTIN scriptlet in rpm package 1:findutils-4.4.2-9.el6.x86_64
error: Couldn't fork %post(findutils-1:4.4.2-9.el6.x86_64): Cannot allocate memory
  Updating   : sqlite-3.6.20-1.el6_7.2.x86_64                                                                                                                                                                                           11/239
Error in POSTIN scriptlet in rpm package sqlite-3.6.20-1.el6_7.2.x86_64
error: Couldn't fork %post(sqlite-3.6.20-1.el6_7.2.x86_64): Cannot allocate memory
  Updating   : nss-softokn-3.14.3-23.3.el6_8.x86_64                                                                                                                                                                                     12/239
Error in POSTIN scriptlet in rpm package nss-softokn-3.14.3-23.3.el6_8.x86_64
error: Couldn't fork %post(nss-softokn-3.14.3-23.3.el6_8.x86_64): Cannot allocate memory
  Updating   : libtasn1-2.3-6.el6_5.x86_64                                                                                                                                                                                              13/239
Error in POSTIN scriptlet in rpm package libtasn1-2.3-6.el6_5.x86_64
error: Couldn't fork %post(libtasn1-2.3-6.el6_5.x86_64): Cannot allocate memory
  Updating   : p11-kit-0.18.5-2.el6_5.2.x86_64                                                                                                                                                                                          14/239
Error in POSTIN scriptlet in rpm package p11-kit-0.18.5-2.el6_5.2.x86_64
error: Couldn't fork %post(p11-kit-0.18.5-2.el6_5.2.x86_64): Cannot allocate memory
  Updating   : p11-kit-trust-0.18.5-2.el6_5.2.x86_64                                                                                                                                                                                    15/239
Error in POSTIN scriptlet in rpm package p11-kit-trust-0.18.5-2.el6_5.2.x86_64
error: Couldn't fork %post(p11-kit-trust-0.18.5-2.el6_5.2.x86_64): Cannot allocate memory
  Updating   : less-436-13.el6.x86_64                                                                                                                                                                                                   16/239
  Updating   : gmp-4.3.1-13.el6.x86_64                                                                                                                                                                                                  17/239
Error in POSTIN scriptlet in rpm package gmp-4.3.1-13.el6.x86_64
error: Couldn't fork %post(gmp-4.3.1-13.el6.x86_64): Cannot allocate memory
  Updating   : device-mapper-persistent-data-0.6.2-0.2.rc7.el6.x86_64                                                                                                                                                                   18/239
  Updating   : 1:make-3.81-23.el6.x86_64                                                                                                                                                                                                19/239
Error in POSTIN scriptlet in rpm package 1:make-3.81-23.el6.x86_64
error: Couldn't fork %post(make-1:3.81-23.el6.x86_64): Cannot allocate memory
  Updating   : gdbm-1.8.0-39.el6.x86_64                                                                                                                                                                                                 20/239
Error in POSTIN scriptlet in rpm package gdbm-1.8.0-39.el6.x86_64
error: Couldn't fork %post(gdbm-1.8.0-39.el6.x86_64): Cannot allocate memory
  Updating   : plymouth-core-libs-0.8.3-29.el6.centos.x86_64                                                                                                                                                                            21/239
Segmentation fault

找了一圈后,找到结局方案,具体如下:
编辑 /etc/sysctl.conf ,改vm.overcommit_memory=1,然后sysctl -p 使配置文件生效

vi /etc/sysctl.conf

修改/添加

vm.overcommit_memory=1

Esc 退出 :wq 保存
然后sysctl -p 使配置文件生效

CentOS 6下域名yum命令报错YumRepo Error: All mirror URLs are not using

CentOS6已经在2020年11月30日停止维护了。centos官方停止了对centos6的所有更新,并且下架了包括官方所有的centos6源,目前阿里、163、清华等centos6源已无法使用。

CentoS 6 停止维护更新日期:2020年11月30日

CentOS 7 停止维护更新日期:2024年6月

CentOS 8 停止维护更新日期:2029年5月

1、输入下面命令,切换到root用户

su root

2、备份配置文件,防止以后出错后不好修复

mv /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.backup

3、执行下面命令,新建CentOS-Base.repo配置,复制粘贴如下命令

vim /etc/yum.repos.d/CentOS-Base.repo
[centos-office]
name=centos-office
failovermethod=priority
baseurl=https://vault.centos.org/6.10/os/x86_64/
gpgcheck=1
gpgkey=https://vault.centos.org/6.10/os/x86_64/RPM-GPG-KEY-CentOS-6

4、按Ecs键后:wq保存并退出

5、正常啦~

使用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 #删除两个临时文件