33,彻底关闭Excel进程
.NET中使用Excel属于使用非托管资源,使用完成后一般都要用GC回收资源,但是,调用GC的位置不正确,Excel进程可能无法彻底关闭,如下面的代码:
static void OpenExcelTest(int j)
{
//Application excel = null;
excel = new Application();
excel.Visible = true;
excel.Workbooks.Open("d:\\A1000.xla");
Workbook wb = excel.Application.Workbooks.Open("d:\\Book1.xlsx");
Worksheet sh = wb.Worksheets.Item["Sheet1"];
object[,] ssss = sh.Range[sh.Cells[1.1], sh.Cells[, ]].Value2;
Console.WriteLine("opened excel no. {0}", j);
Console.ReadLine(); try
{
//尝试程序关闭Excel进程
wb.Close(false);
sh = null;
wb = null;
excel.Quit();
}
catch (Exception ex)
{
Console.WriteLine("用户已经手工结束了Excel进程,内部错误消息:{0}",ex.Message );
} int generation = System.GC.GetGeneration(excel); //No.1
//System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
//System.Runtime.InteropServices.Marshal.ReleaseComObject(sh);
//System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); excel = null;
//No.2
//GC.Collect(generation); Console.WriteLine("close excel no. {0}", j);
Console.ReadLine();
}
在上面的代码中,如果取消 No.1,No.2位置处的注释,方法结束后,Excel进程是无法结束的,解决办法,只需要把
GC.Collect();
这行代码写到方法之外即可。
Application excel = null;
这个Excel应用程序对象定义在方法内或者外都是可以的,哪怕定义一个静态变量,结果都没有影响。
34,给ZIP文件添加文件头
将很多零散的文件打包成ZIP文件方便使用,但是ZIP文件使用相关的工具很容易解压,并且无法判断该文件是指定的文件。可以给ZIP文件增加文件头内容,在文件头里面写入要验证的标记。采用这种方式,加了标记的ZIP文件仍然可以被ZIP工具打开,从而制作只有自己能用的ZIP文件。
/// <summary>
/// 创建文件包
/// </summary>
/// <param name="userName">打包文件的用户名</param>
/// <param name="savePath">保存的文件包路径</param>
/// <param name="files">文件清单</param>
/// <param name="businessID">业务ID</param>
/// <param name="pkgVersion">文件包版本号</param>
/// <returns>文件包全路径</returns>
public string CreatePackage(string userName, string savePath, List<string> files, string businessID,string pkgVersion="")
{
//DataPackages 存放导入导出项目文件包的目录,以此目录打包,最终压缩文件存放目录为用户指定路径下
//每次生成一个临时目录
string tempDir = DateTime.Now.ToString("yyyyMMddHHmmss");
string pkgDirRoot = System.IO.Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, "Packages");
string pkgDir = System.IO.Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, "Packages", businessID, tempDir);
string pkgSaveFile = System.IO.Path.Combine(savePath, "SODPackage_"+ tempDir + ".pkg");
string pkgSaveFileTemp = System.IO.Path.Combine(savePath,"SODPackage_"+ tempDir + ".tmp"); try
{
if (!System.IO.Directory.Exists(pkgDir))
System.IO.Directory.CreateDirectory(pkgDir);
StringBuilder sb = new StringBuilder();
//复制文件到压缩目录
foreach (string file in files)
{
int index = file.IndexOf(businessID);
if (index > )
{ //假设给出的文件路径中都包含了业务ID
string subStr = file.Substring(index + businessID.Length+);
string targetFile = System.IO.Path.Combine(pkgDir, subStr);
string targetDir = System.IO.Path.GetDirectoryName(targetFile);
if (!System.IO.Directory.Exists(targetDir))
System.IO.Directory.CreateDirectory(targetDir);
System.IO.File.Copy(file, targetFile);
sb.AppendLine(subStr);
}
}
//写一个清单文件
string infoFile = System.IO.Path.Combine(pkgDir, "info.txt");
string infoText = string.Format("RemoteServer={0}\r\nUserName={1}\r\nProjectID={2}\r\nProjectName={3}\r\nData Version={4}\r\nPackage Dir={5}\r\nPackage Files List:\r\n{6}",
remoteServer, userName,
businessID,projectName,
pkgVersion,
pkgDir, sb.ToString());
System.IO.File.AppendAllText(infoFile, infoText); System.IO.Compression.ZipFile.CreateFromDirectory(pkgDir, pkgSaveFileTemp);
//添加文件头,第一个整数字节,为文件头长度
using (var fs = System.IO.File.Create(pkgSaveFile))
{
System.IO.BinaryWriter bw = new System.IO.BinaryWriter(fs); string headStr = string.Format("XXXPackage&{0}&{1}&{2}", businessID, pkgVersion,userName);
var headData = System.Text.Encoding.UTF8.GetBytes(headStr);
bw.Write(headData.Length);
bw.Write(headData); var fileData = System.IO.File.ReadAllBytes(pkgSaveFileTemp);
bw.Write(fileData, , fileData.Length);
bw.Close();
fs.Close();
}
System.IO.File.Delete(pkgSaveFileTemp);
return pkgSaveFile;
}
catch (Exception ex)
{
throw new Exception("打包项目文件失败:"+ex.Message, ex);
}
}
创建文件包
这里用到了方法:
System.IO.Compression.ZipFile.CreateFromDirectory(pkgDir, pkgSaveFileTemp);
需要程序引用2个程序集文件,并且需要.NET 4.5以后才支持:
System.IO.Compression;
System.IO.Compression.ZipFile;
下面是解压的方法:
/// <summary>
/// 解压缩包文件,包括处理数据文件
/// </summary>
/// <param name="pkgFile">用户指定的包文件路径,会校验此文件格式</param>
/// <param name=" businessID">需验证的包文件相关的业务ID</param>
/// <param name="pkgVersion">需要验证的文件包版本</param>
/// <returns></returns>
public string UnZipPackage(string pkgFile, string businessID,string pkgVersion="")
{
if (!System.IO.File.Exists(pkgFile))
throw new Exception("指定的文件不存在:"+ pkgFile); string tempDir = DateTime.Now.ToString("yyyyMMddHHmmss");
string pkgDirRoot = System.IO.Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, "Packages");
string pkgDir = System.IO.Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, "Packages", tempDir);
string pkgFileTemp = System.IO.Path.Combine(System.IO.Path.GetTempPath(), System.DateTime.Now.Ticks.ToString()+ "_Package.tmp");
try
{
if (!System.IO.Directory.Exists(pkgDir))
System.IO.Directory.CreateDirectory(pkgDir); using (var fs = System.IO.File.OpenRead(pkgFile))
{
System.IO.BinaryReader br = new System.IO.BinaryReader(fs);
//校验文件头,第一个整数字节,为文件头长度
int headLenth = br.ReadInt32();
if (headLenth > )
throw new Exception("包文件格式不正确!");
byte[] headData = new byte[headLenth]; int count= br.Read(headData, , headData.Length);
string tempStr = System.Text.Encoding.UTF8.GetString(headData);
string[] tempArr = tempStr.Split('&');
if(tempArr.Length<= || tempArr[]!= "XXXPackage")
throw new Exception("包文件格式不正确!");
if (tempArr[] != businessID)
throw new Exception("不是此项目的数据包文件!\r\n(数据包业务ID="+tempArr[]+",目标ID="+businessID+")");
if (tempArr[] != pkgVersion)
throw new Exception("数据包版本号不正确!\r\n(数据包版本号=" + tempArr[] + ",目标版本号="+pkgVersion+")"); byte[] buffer = new byte[];
using (var fw = System.IO.File.OpenWrite(pkgFileTemp))
{
while (count > )
{
count = br.Read(buffer, , buffer.Length);
if(count>)
fw.Write(buffer, , count);
}
fw.Close();
}
br.Close();
fs.Close();
} System.IO.Compression.ZipFile.ExtractToDirectory(pkgFileTemp, pkgDir);
System.IO.File.Delete(pkgFileTemp); return pkgDir;
}
catch (Exception ex)
{
throw new Exception("解压缩项目文件失败:"+ex.Message, ex);
}
}
文件包验证和解压
有了这种方式,在解压文件之前就可以验证文件信息,这对于非常大的压缩文件很有用。
35,利用SOD框架,导出MySQL数据库表数据
MySQL可以通过 SELECT * INTO OUTFILE 的方式,将查询结果的数据导出到一个文本文件,然后再导入此文件的数据到另外一个MySQL数据库.
SOD框架提供了 DbContext 类型,继承它可以检查并创建使用到的所有表,然后用 ResolveAllEntitys() 方法即可获取所有使用的实体类。
例如:下面是导出库中包含指定产品的表的所有数据
void ExportMySQLData(string ProductID )
{
AdoHelper ado = MyDB.GetDBHelperByConnectionName("mySqlDb"); DbContext context = new MyDbContext(ado);
var entitys = context.ResolveAllEntitys();
StringBuilder sb = new StringBuilder();
foreach (EntityBase entity in entitys)
{
var tableName = entity.GetTableName();
var path = Path.Combine("D:\MySQLExportPath”, tableName + ".txt");
path = path.Replace(@"\", "/");
if (entity.PropertyNames.Contains("BusinessID"))
{
sb.AppendLine("SELECT * INTO OUTFILE ");
sb.AppendLine("'" + path + "'");
sb.AppendLine(" FIELDS TERMINATED BY '^`' ");
sb.AppendLine("OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY "); sb.AppendLine(" FROM ");
sb.AppendLine(tableName);
sb.AppendLine(" WHERE ");
sb.Append("ProductID='" + ProductID + "'");
}
}
}
导出数据
导入数据也比较简单,直接用AdoHelper对象的ExecuteNonQuery 方法执行文件导入的SQL语句即可,看下面代码:
void ImportMySQLData(List<string> fileList)
{
AdoHelper ado = MyDB.GetDBHelperByConnectionName("mySqlDb1");
ado.CommandTimeOut = ;
StringBuilder sb = new StringBuilder();
foreach (var file in fileList)
{
sb.Clear();
var tableName = Path.GetFileNameWithoutExtension(file);
var path = file.Replace(@"\", "/");
sb.AppendLine("LOAD DATA INFILE ");
sb.AppendLine("'" + path + "'");
sb.AppendLine(" Replace INTO TABLE ");
sb.AppendLine(tableName);
sb.AppendLine(" FIELDS TERMINATED BY '^`' ");
sb.AppendLine("OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY "); ado.ExecuteNonQuery(sb.ToString());
}
}
导入数据
36,使用Power Shell 打开Word并插入文字
Power Shell是很强的脚本语言,将下面的代码存为后缀名是 ps1 格式的文件,然后打开文件,点击运行,即可打开Word,并且插入两行文字。第一行是系统时间,第二行是计算机名字。
$word=new-object -ComObject "Word.Application"
$doc =$word.documents.Add()
$word.Visible = $true
$selection=$word.Selection
$selection.TypeText((Get-Date).ToString())
$selection.TypeParagraph() $os=Get-WmiObject -class win32_OperatingSystem
$selection.TypeText("Operating System Information for $($os.CSName)")
37,使用VBA删除Word文档中标记为灰色的文字,外加导出PDF功能
将下面代码写到VBA工程中,然后在宏上调用。
删除灰色文字(GRB 217, 217, 217),调用 RemoveSpecialWords 方法
导出PDF,调用 ExportPDF 方法:
Function ExportPDF() As String
Dim fileName As String
Dim fd As FileDialog, i As Integer Set fd = Application.FileDialog(msoFileDialogSaveAs)
With fd
For i = To .Filters.Count
If .Filters(i).Extensions = "*.pdf" Then Exit For
Next
.FilterIndex = i
If .Show = - Then
fileName = .SelectedItems()
MsgBox fileName
Else
ExportPDF = ""
Exit Function
End If
End With If fd.Filters(fd.FilterIndex).Extensions <> "*.pdf" Then
MsgBox "只能导出为PDF格式文件!", vbCritical, "导出报告"
ExportPDF = ""
Exit Function
End If If fileName <> "" Then
ActiveDocument.ExportAsFixedFormat OutputFileName:=fileName, _
ExportFormat:=wdExportFormatPDF, _
OpenAfterExport:=False, OptimizeFor:=wdExportOptimizeForPrint, Range:= _
wdExportAllDocument, From:=, To:=, Item:=wdExportDocumentContent, _
IncludeDocProps:=True, KeepIRM:=True, CreateBookmarks:= _
wdExportCreateNoBookmarks, DocStructureTags:=True, BitmapMissingFonts:= _
True, UseISO19005_1:=False MsgBox "导出成功!", vbInformation, "导出报告"
End If
ExportPDF = fileName
End Function '删除文档中所有的灰色文字,GRB:217,217,217
'
Sub RemoveSpecialWords()
Dim n As Integer ', Info As String With Selection.Find
.Parent.HomeKey wdStory
.ClearFormatting
.Font.Color = wdColorGray15 Do While .Execute
n = n +
'Info = Info & n & vbTab & .Parent & vbCrLf '提取找到的文本
.Parent.Delete '删除找到的文本 Loop End With
If n > Then MsgBox "删除标记文本数量 " & n & " 处!" End Sub Function GetSaveAsFileName(ByVal pdfFile As String) As String
Dim rptWordFile As String
If pdfFile <> "" Then
Dim fileNoExt As String fileNoExt = Left(pdfFile, Len(pdfFile) - ) ' .pdf
rptWordFile = fileNoExt + ".docm" Else
rptWordFile = ""
End If
GetSaveAsFileName = rptWordFile
End Function Sub ExportMyPDF()
'
' ExportMyPDF 宏
' 删除灰色文字,导出PDF文件
'
Dim pdfFile As String
RemoveSpecialWords
pdfFile = ExportPDF Dim rptWordFile As String
rptWordFile = GetSaveAsFileName(pdfFile)
If rptWordFile <> "" Then
ActiveDocument.SaveAs fileName:=rptWordFile
End If 'Documents.Close (False)
'Dim wordFile As String
'wordFile = GetSaveAsFileName("e:\doc\43887ad1-c504-490d-a6a2-dfa616164f9e.pdf")
'MsgBox wordFile End Sub
下面的方法查找到指定内容的段落并改变段落文字颜色做标记(可用于删除):
Sub 查找段落()
'
' 查找段落 宏
' 查找段落,删除段落
'
Dim s As String
Dim count As Integer
Dim currRange As Range
Dim currCctr As ContentControl
Dim title As String s = "要删除"
'先找到Word内容控件,然后判断值是否是0,如果是,置于整个自然段为灰色
For i = ActiveDocument.Paragraphs.count To Step -
Set currRange = ActiveDocument.Paragraphs(i).Range If InStr(currRange.text, s) > Then For c = To currRange.ContentControls.count
Set currCctr = currRange.ContentControls(c)
title = currCctr.title
If currCctr.Type = wdContentControlText Then
currCctr.Range.text = "123 测试内容"
End If
Next
'currRange.Delete
'不真正删除,只是先设置成灰色,便于报告导出的时候,再删除
currRange.Font.Color = wdColorGray05
count = count +
End If
Next
MsgBox "删除找到的段落 " & count & " 处!"
End Sub
38, 通过命令行打开一个Word文件
通过start命令即可:
start winword.exe "文件1.docx"