用EXCEL 解析集装箱装载EDI数据文件

1、用EXCEL 解析集装箱装载EDI数据文件0 引 言随着集装箱运输行业发展，并持续稳定增长，集装箱国际贸易进出口总额不断增加，随之而来的是作为国际贸易货物运输的主要方式之一的集装箱运输量的不断增加。为了抓住机遇并且迎接挑战，各航运公司加速发展集装箱运输船队，并逐步向大型化发展，为了应尽可能地提高集装箱运输中每个环节的效率。本文以此为契机，研究由口岸、代理、公司提供给船方的集装箱电子数据文件每个集装箱信息以标识符LOC+147+开始，一个集装箱的具体数据按LOC其中第1行至第8行为EDI的报头段，包括EDI的版本，船舶信息、码头信息及到离港信息，第9行至等28行为2个集装箱的完整信息，表示其中省

2、略了N个集装箱信息，第18421行至第18422行为文件结束段，本文有用的数据是第9行至第18420行的每个集装箱数据信息。每个集装箱数据信息从LOC+147 集装箱装载位置开始，包括积载位置（2）VBA代码编写：VBA编辑器可用快捷键Alt-F11打开，VBA界面分为工程资源管理器、属性窗口、代码窗口，代码窗口内是输入VBA代码的区域。EDI文件读入，EDI原文可通过用记事本打开，复制和粘帖到EDI;表的A;列，也可以用VBA代码打开文件来读取，用快捷键Alt-F11打开VBA编辑器，在代码区输入如下代码：Sub 读EDI文件()On Error Resume NextDim CNT() A

3、s StringDim TEMP As StringDim edi_filename As StringDim EDI As StringSheets(EDI).UsedRange.ClearContents 清除原来的数据；Application.FileDialog(msoFileDialogOpen).Filters.ClearApplication.FileDialog(msoFileDialogOpen).Filters.Add EDI文件|*.edi, *.ediApplication.FileDialog(msoFileDialogOpen).Filters.Add 文本文件|*

4、.txt, *.txtApplication.FileDialog(msoFileDialogOpen).Show 打开文件浏览窗口；edi_filename = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)If edi_filename ThenOpen edi_filename For Input As #1DoTEMP = Input #1, TEMPIf InStr(1, TEMP, ´, 1) = 0 ThenTEMP = TEMP + ´End IfEDI = EDI &

5、amp; TEMPLoop Until EOF(1)Close #1CNT() = Split(EDI, ´, -1)If i = UBound(CNT) ThenSheets(EDI).Range (A + CStr(i).Value = CNT(i) + ´Next iEnd SubEDI解析：EDI解析主要使用文本分割语句Split和标识符判断，具体代码如下：Sub 解析()Sheets(清单).Range(a2:M20000).ClearContentsDim Data() As StringDim 计数 As IntegerDim i As I

6、nteger计数 = 0Sheets(EDI).Range(B1).Formula = =COUNTA(A:A)For i = 1 To Val(Sheets(EDI).Range(B1).Value) - 1Data () = Split(Sheets(EDI).Range(A + CStr(i).Value, +, -1)If UBound(Data) = 2 ThenIf (Data (1) = 20 And Data (0) = TDT) Or (Data(1) = 20 And Data(0) = Chr(10) + TDT) ThenSheets(清单).Range(o3).Val

7、ue = Data(2)If UBound(Split(Data(4), :, -1) = 3 ThenSheets(清单).Range(o4).Value = Split(Data(4), :, -1)(3)End IfEnd IfIf (Data(1) = 9 And Data(0) = LOC) Or (Data(1) = 9 And Data(0) = Chr(10) + LOC) Or (Data(1) = 5 And Data(0) = LOC) Or (Data(1) = 5 And Data(0) = Chr(10) + LOC) ThenSheets(清单).Range(o5

8、).Value = Left(Data(2), 5)End If´LOC+147If (Data(1) = 147 And Data(0) = LOC) Or (Data(1) = 147 And Data(0) = Chr(10) + LOC) Then计数 = 计数 + 1 增加一行Sheets(清单).Range(A + CStr(计数 + 1).Value = Left(Data(2), 7)箱位If Val(Left(Sheets(清单).Range(A + CStr(计数 + 1).Value, Len(Sheets(清单).Range(A + CStr(计数

9、+ 1).Value) - 4) Mod 2 = 0 ThenSheets(清单).Range(C + CStr(计数 + 1).Value = 40 40英寸ElseSheets(清单).Range(C + CStr(计数 + 1).Value = 20 20英尺End IfEnd If´EQD+CNIf (Data(1) = CN And Data(0) = EQD) Or (Data(1) = CN And Data(0) = Chr(10) + EQD) ThenSheets(清单).Range(B + CStr(计数 + 1).Value = Data(2) 箱号

10、If Data(6) = 4´ Or Data(6) = 4 ThenSheets(清单).Range(G + CStr(计数 + 1).Value = E 空箱ElseSheets(清单).Range(G + CStr(计数 + 1).Value = F 重箱End IfEnd If´MEA+WTIf (Data(0) = MEA And Data(1) = WT) Or (Data(0) = Chr(10) + MEA And Data(1) = WT) OR (Data(0) = MEA And Data(1) = VGM) Or (Data(0)

11、 = Chr(10) + MEA And Data(1) = VGM)ThenData(3) = Replace(Data(3), KGM:, )Data(3) = Replace(Data(3), ´, )Sheets(清单).Range(D + CStr(计数 + 1).Value = Data(3) 箱重End If´LOC+6If (Data(0) = LOC And Data(1) = 6 Or Data(1) = 9) Or (Data(0) = Chr(10) + LOC And Data(1) = 6 Or Data(1) = 9) Th

12、enIf Len(Data(2) 6 ThenData(2) = Left(Data(2), 5)End IfSheets(清单).Range(E + CStr(计数 + 1).Value = Replace(Data(2), ´, ) 装货港End If´LOC+12If (Data(0) = LOC And Data(1) = 12 Or Data(1) = 11) Or (Data(0) = Chr(10) + LOC And Data(1) = 12 Or Data(1) = 11) ThenIf Len(Data(2) 6 ThenData(2

13、) = Left(Data(2), 5)End IfSheets(清单).Range(F + CStr(计数 + 1).Value = Replace(Data(2), ´, ) 卸货港End If´DGS+IMDIf Sheets(清单).Range(H + CStr(计数 + 1).Value ThenIf (Data(0) = DGS And Data(1) = IMD) Or (Data(0) = Chr(10) + DGS) And Data(1) = IMD) ThenSheets(清单).Range(H + CStr(计数 + 1).Val

14、ue = Sheets(清单).Range(i + CStr(计数 + 1).Value = Sheets(清单).Range(i + CStr(计数 + 1).Value + + Replace(Replace(Data(2), ., ), :, )If UBound(Data) 2 ThenSheets(清单).Range(j + CStr(计数 + 1).Value = Sheets(清单).Range(j + CStr(计数 + 1).Value + + Replace(Data(3), ´, )End IfEnd IfEnd If´TMP+2I

15、f (Data(0) = TMP And Data(1) = 2) Or (Data(0) = Chr(10) + TMP And Data(1) = 2) ThenSheets(清单).Range(K + CStr(计数 + 1).Value = Sheets(清单).Range(L + CStr(计数 + 1).Value = Left(Data(2), Len(Data(2) - 5)Sheets(清单).Range(M + CStr(计数 + 1).Value = Right(Replace(Data(2), ´, ), 3)End IfEnd IfNext iEn

16、d Sub数据汇总：从解析的集装箱清单中分类汇总出各种数据，按装卸港分别统计，包括20、40的重箱、空箱、危险品、冷藏箱的数量和重量主要使用Microsoft ActiveX Data Objects (ADO)对象和SQL语句进行查询统计，部分代码如下：Dim connection, SQL$ ´定义数据库连接和SQL语句Set connection = CreateObject(adodb.connection) ´创建数据库连接Set Recordset = CreateObject(adodb.recordset) ´创建一个

17、数据集保存数据connection.Open Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source= & ThisWorkbook.FullName20´ FULL 汇总SQL = SELECT COUNT(POL) FROM WHERE POD=´ + POD + ´ AND SlZE= 20 AND E=´F´ 统计20数量Set Recordset = connection.Exec

18、ute(SQL)Sheets(汇总表;).Datas(4, 4).Value = Recordset.Fields(0).ValueSQL = SELECT SUM(WT) FROM WHERE POD=´ + POD+ ´ AND SlZE= 20 AND E=´F´ ´统计20重量Set Recordset = connection.Execute(SQL)Sheets(汇总表;).Datas(4, 5).Value = Recordset.Fields(0).Value / 1000&am

19、p;acute;40 FULL 汇总SQL = SELECT COUNT(POL) FROM WHERE POD=´ + POD + ´ AND SlZE= 40 AND E=´F´ ´统计40数量Set Recordset = connection.Execute(SQL)Sheets(汇总表;).Datas(4, 6).Value = Recordset.Fields(0).ValueSQL = SELECT SUM(WT) FROM WHERE POD=´ + POD

20、+ ´ AND SlZE= 40 AND E=´F´ ´统计40重量Set Recordset = connection.Execute(SQL)Sheets(汇总表;).Datas(4, 7).Value = Recordset.Fields(0).Value / 1000用上述方法分别统计空箱、危险品、和冷藏箱，完成SUMMARY的表格输出。4 功能使用：冷藏箱、危险品制作如果要制作冷藏箱清单，按装货港、卸货港分别分类统计制作，可插入新的工作表，命名为RF，表格可按体系文件格式设计成如图4-1的式样。在VBA代

21、码区界面输入如下代码，即可自动生成一份按要求的冷藏箱记录清单：Sub RF_list()On Error Resume NextDim POL As StringDim POD As StringDim 数量 As IntegerDim 计次 As IntegerDim 条件 As BooleanPOL = POD = POL = InputBox(请输入POL:, 信息输入)POD = InputBox(请输入POD:, 信息输入)数量= 0For 计次= 1 To Sheets(清单).Range (O2).Value条件 = FalseIf Sheets(清单).Range (K + C

22、Str(计次+ 1).Value = And POL And POD = Then条件 = Sheets(清单).Range (E + CStr(i计次+ 1).Value = POLEnd IfIf Sheets(清单).Range (K + CStr(i计次+ 1).Value = And POD And POL = Then条件 = Sheets(清单).Range (F + CStr(i计次+ 1).Value = PODEnd IfIf Sheets(清单).Range (K + CStr(i计次+ 1).Value = And POD And POL Then条件 = Sheets

23、(清单).Range (F + CStr(i计次+ 1).Value = POD And Sheets(清单).Range (E + CStr(i计次+ 1).Value = POLEnd IfIf Sheets(清单).Range (K + CStr(i计次+ 1).Value = And POD = And POL = Then条件 = TrueEnd IfIf 条件 Then数量=数量+ 1Sheets(RF).Range (B + CStr(数量 + 5).Value = Right(0 & Sheets(清单).Range (A + CStr(i计次+ 1).Value, 6) & Chr(47)Sheets(RF).Range (C + CStr(数量 + 5).Value = Sheets(清单).Range (B + CStr(i计次+ 1).Value + Chr(47)Sheets(RF).Range (D + CStr(数量 + 5).Value = Sheets(清单).Range (E + CStr(i计次+ 1).Value + Chr(47)Sheets(RF).Range (E + CStr(数量 + 5).Value = Sheets(清