解铃还须系铃人：再谈CSV丢零现象

上文中主要谈到如何输出XML的格式，使得Excel在读解数据是不要自作聪明，从而使得用户得到需要的显示格式。

 

文末提到，有人用PL/SQL输出真正的Excel文件。笔者也试过，对于小批量数据还可接受，对于大量数据会出现服务器瓶颈，效率变差，用户难以接受。

除此之外，也有用Java API来解决此类问题的。比如使用 “Apache POI - the Java API for Microsoft Documents”。

其URL：  http://poi.apache.org/

事例： ttp://viralpatel.net/blogs/java-read-write-excel-file-apache-poi/

对此Java API笔者尚未验证，不敢妄加评论。

 

本文将从另一个角度来探讨CSV在Excel中丢零问题。常言说的好，解铃还须系铃人。我们可以借助Excel的内部功能，使用VBA来处理这一问题。

Excel提供了一个ADO Object，QueryTables。通过它，我们可把外部数据读入Excel表单。

（有关详情可参考微软文件：http://msdn.microsoft.com/en-us/library/office/ff837764.aspx）

 

• 基本思路

 这里假设我们已经读入了一个CSV文件，如果我们这时在“Visual Basic for Applications"（VBA）之下执行以下代码（按下F11可呼出VBA，然后，拷贝，粘帖，执行），

我们就可以用ActiveSheet.QueryTables.Add方法再次装入这个CSV文件，所不同的是，这段代码指定了每个列使用文本格式，而不是让Excel自作聪明地设定类型：

   Sub Reform_CSV()
   Dim AllTextFormat(255) As Integer
   Dim i As Long
   For i = 0 To 255
      AllTextFormat(i) = xlTextFormat  ' =2, Array to indicate column's data type
   Next i

    'Clear all cells in the sheet
    Cells.Clear
    
    ' Reload the CSV file
    MyFile = "text;" & ActiveWorkbook.FullName
    
    With ActiveSheet.QueryTables.Add(Connection:=MyFile, Destination:=Range("$A$1"))
       .TextFileCommaDelimiter = True
       .TextFileColumnDataTypes = AllTextFormat
       .Refresh
    End With
    
End Sub

         .TextFileColumnDataTypes = AllTextFormat

 而AllTextFormat是一个所有元素都为2（即Text）的数组。

   

• 具体实现

Q: 好，这办法不错哦。可，总不能让我每次都贴一段代码到VBA编辑器啊。

A: 当然不用。我们可以把这段程序保存到文件中，每次执行它就成。

Q: 不对啊，CSV不能记忆VBA代码的。再说了，做个Excel文件，在读入CSV文件前先打开这个Excel，然后选择执行其中的代码？这不忒麻烦了点？

A: 是的。我们可以请一个叫做PERSONAL.XLSB的文件出山啊。每次Excel启动时必先打开此文件。

 

PERSONAL.XLSB存在于一个叫做XLSTART的文件夹里。顾名思义，EXCEL在Start时要访问的文件夹。

这其实是一个保存个人宏定义的文件，即个人宏工作簿。你可以把自己常用的宏集中在一起保管，而不是分散在不同的Excel文件之中。

可惜，很多用户不喜欢Excel每次打开这么一个东东，网上常问的问题是如何禁止它。答案是找到它并干掉它。

这里，我们要反其道而行之。探讨如何设置它。

 

如何创建个人宏工作簿，详情可参考以下URL：

http://wenku.baidu.com/view/b7b1887802768e9951e738c2.html

 

因为Windows版本和Office版本很多，可以结合下列vbs代码，检测当前使用的EXCEL默认的PERSONAL.XLSB所在的文件夹。

 

'vbscript to detect the location of PESONAL.XLSB file
'Name: Detect_Personal_XLSB.vbs
'by ZPF, 2013/09/18

Option Explicit

Dim objFileSys, computername, username, tgtFile, msgText, choice, objExcel

' Excel StartupPath check
Set objExcel = CreateObject("Excel.Application")
tgtFile = objExcel.StartupPath 
Set objExcel = Nothing

Set objFileSys = CreateObject("Scripting.FileSystemObject")

if objFileSys.FileExists(tgtFile & "\PERSONAL.XLSB") = False Then
    msgText = "PERSONAL.XLSB is not found."& vbCr 
    msgText = msgText & "But, it should be located in: " & vbCr
    msgText = msgText & tgtFile & vbCr
    msgText = msgText & vbCr
    MsgBox msgText,vbInformation,"Message"
else
    msgText = "PERSONAL.XLSB is found."& vbCr 
    msgText = msgText & "It is located in: " & vbCr
    msgText = msgText & tgtFile & vbCr
    msgText = msgText & vbCr
    MsgBox msgText,vbInformation,"Message"
end if

如果你已经打开了你的PERSONAL.XLSB文件，那么，在VBA编辑器上，加入第一段代码，即“Sub Reform_CSV()”那一段。

这段代码应该位于该文件的"标准模块"的"模块1"之中。

 

这样你就有了可以调用的模块。下一步就是把这个模块添加到Excel的弹出式菜单上。

这需要在PERSONAL.XLSB的ThisWorkbook上键入以下代码。

Private Sub workbook_open()
 
    Dim cmdBr As CommandBar
    Dim cmdBtn As CommandBarButton
 
    'get command bar 
    Set cmdBr = Application.CommandBars("cell")

    'Create an Item in Command bar
    Set cmdBtn = cmdBr.Controls.Add(msoControlButton, , , , True)
 
    'Add properties to the item
     With cmdBtn
       'We need a line above the Item
        .BeginGroup = True
       'Procedure to be called out
        .OnAction = "Reform_Csv"
       'Display name of the Item
        .Caption = "Reform CSV"
     End With
End Sub

然后，保存这个PERSONAL.XLSB文件；关闭EXCEL，再启动EXCEL，在任意单元（Cell）上按右按键，在弹出菜单的最底部，你会发现“Reform CSV”这个新菜单项目。

 

• 安装包下载

如果你觉得这都麻烦，那就干脆下载一个安装包吧。

 http://download.csdn.net/detail/zhangpingfanyahoocom/6286533

 

不能下载？试试这个。

 https://skydrive.live.com/?cid=906664A6A59FC0DD&id=906664A6A59FC0DD%211023