'***************************************************************************
'* MODULE NAME: HzToPy
'* AUTHOR & DATE: tt.t
'* 03 Apirl 2007
'* DESCRIPTION: 将中文字符串转换为拼音,就这些~
'* 有汉字得到拼音其实并不是我很关心的一个问题,只是发现已经公开'* 的方法有很大的缺陷,但WORD却做得很好,因此才尝试解决这个问题。
'* 过程比我预期的要曲折的多,主要是VBA实在是一种很受限制的语言。
'* 不过好在有Google和Olldbg,难题也仅仅是如何找到绕过限制的途径,
'* 终于在5个小时内搞定了一切~
'* 时间比我预计的长了很多,因为我实在是不了解VBA,也不很熟悉OLE:"(
'* 不过好在一切都解决了~~终于从VBA小白成长了一些。
'* 其实VBA也是很强大的~
'*
'* Theory:废话了好多还是说说原理吧,虽然不是每个人都很关心~
'* WORD的拼音向导能够将汉字转成拼音全是倚仗微软拼音的帮助,'* 微软拼音2.0以上版本都提供了汉字到拼音的转换功能。
'* 微软拼音MSIME.China类中的IFELanguage接口具体实现了转换功能'* 不过MSIME.China中没有提供IDispatch接口,VBA的CreateObject 不支持
'* 调用这样的类,因此我们只好手工调用。CoCreateInstance可以创建类
'* 并获取IFELanguage接口,但我们无法直接调用,因为VBA不知道如何调用
'* IFELanguage接口的Method。这里困扰了我好久,原本希望能向其他语言那样
'* 声明接口结构,但VBA并不支持。万般无奈下只好在OLE相关DLL中寻找,期待能'* 找到代理函数简介调用接口的Method。呵呵~功夫不负苦心人终于在OLEAUT32中'* 找到了DispCallfunc。Google了一下,果然是我需要的。接口知道了,如何调用也
'* 清楚了,剩下的问题就是如何取得转换后的结果。IFELanguage.GetMorphResult会将
'* 转换的结果存在一个叫做tagMORRSLT的结构中,并返回指向tagMORRSLT 的指针。
'* 新问题又来了,VBA不支持指针...sigh,为什么其他语言很容易实现的功能VBA用起来
'* 就这么烦呢~幸好VBA读取内存的限制也好突破,只需调用ntdll的RtlMoveMemory。
'* 好了~一切限制都已解除,HzToPy终于正常工作了~~
'* 说起来一切顺理成章,可是寻找解决方法的过程真的很痛苦,不过VBA经验值大涨也算有所收获。
'* 下面就让代码来说话吧。
'*
'* Memo: 改成类了,加入了拼音间加入分隔符和去掉注音的功能,请参照“模
块1”中的例子,用起来很简单:)
'* 更正了一个错误,redim时vba数组默认起始搞错了
'*
'*************************************************************************** Option Explicit
Public Enum PhoneticNotation
pnDefault = 0
pnNoNotation = 1
End Enum
Private Type GUID
Data1 As Long
Data2 As Integer
Data3 As Integer
Data4(0 To 7) As Byte
End Type
Private Type TinyMORRSLT
dwSize As Long
pwchOutput As Long
cchOutput As Integer
End Type
Private Declare Sub MoveMemory Lib "kernel32" Alias "RtlMoveMemory" _
(Destination As Any, Source As Any, ByVal Length As Long)
Private Declare Function CoCreateInstance Lib "ole32" ( _
rclsid As GUID, ByVal pUnkOuter As Long, _
ByVal dwClsContext As Long, riid As GUID, _
ByRef ppv As Long) As Long
Private Declare Function DispCallFunc Lib "oleaut32" _
(ByVal pvInstance As Long, ByVal oVft As Long, _
ByVal cc As Long, ByVal vtReturn As Integer, _
ByVal cActuals As Long, prgvt As Integer, _
prgpvarg As Long, pvargResult As Variant) As Long
Private Declare Sub CoTaskMemFree Lib "ole32" (pv As Long)
Dim MSIME_GUID As GUID 'MSIME's GUID
Dim IFELanguage_GUID As GUID 'IFELanguage's GUID
Dim IFELanguage As Long 'Pointer to IFELanguage interface
Dim sNotation1
Dim sNotation2
Dim dNotation
Dim pvSeperator As String
Dim pvUseSeperator As Boolean
Dim pvInitialOnly As Boolean
Dim pvOnlyOneChar As Boolean
Private Sub InitalArray()
sNotation1 = Array("ü", "á", "?", "à", "y", "?", "t", "a", "?", "í", "?", "ì", "?", "?", "?", _
"?", "?", "ú", "?", "ù", "?", "?", "?", "?", "?", " ", "ā", "ā", " ", "ɡ")
sNotation2 = Array("a1", "a2", "a3", "a4", "e1", "e2", "e3", "e4", "i1", "i2", "i3", "i4", "o1", "o2", "o3", _
"o4", "u1", "u2", "u3", "u4", "v1", "v2", "v3", "v4", "v", "m2", "n2", "n4", "n2", "g")
dNotation = Array("a", "a", "a", "a", "e", "e", "e", "e", "i", "i", "i", "i", "o", "o", "o", _
"o", "u", "u", "u", "u", "v", "v", "v", "v", "v", "m", "n", "n", "n", "g") End Sub
Private Sub GenGUID()
InitalArray
'MSIME.China GUID = "{E4288337-873B-11D1-BAA0-00AA00BBB8C0}"
With MSIME_GUID
.Data1 = &HE4288337
.Data2 = &H873B
.Data3 = &H11D1
.Data4(0) = &HBA
.Data4(1) = &HA0
.Data4(2) = &H0
.Data4(3) = &HAA
.Data4(4) = &H0
.Data4(5) = &HBB
.Data4(6) = &HB8
.Data4(7) = &HC0
End With
'IFELanguage GUID = "{019F7152-E6DB-11d0-83C3-00C04FDDB82E}"
With IFELanguage_GUID
.Data1 = &H19F7152
.Data2 = &HE6DB
.Data3 = &H11D0
.Data4(0) = &H83
.Data4(1) = &HC3
.Data4(2) = &H0
.Data4(3) = &HC0
.Data4(4) = &H4F
.Data4(5) = &HDD
.Data4(6) = &HB8
.Data4(7) = &H2E
End With
End Sub
Private Sub IFELanguage_Open()
Dim ret As Variant
DispCallFunc IFELanguage, 4, 4, vbLong, 0, 0, 0, ret
DispCallFunc IFELanguage, 12, 4, vbLong, 0, 0, 0, ret
End Sub
Private Sub IFELanguage_Close()
Dim ret As Variant
If IFELanguage = 0 Then Exit Sub
DispCallFunc IFELanguage, 8, 4, vbLong, 0, 0, 0, ret
DispCallFunc IFELanguage, 16, 4, vbLong, 0, 0, 0, ret
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''' Subroutine: GetPinYin
'''
''' Purpose: 返回汉字的拼音
'''
''' Arguments: HzStr - 待转换的拼音
'''
'''
''' Date Developer Action
''' --------------------------------------------------------------------------
''' 02 April 2007 tt.t 更正ReDim Py时的错误
'''
Private Function IFELanguage_GetMorphResult(HzStr As String) As String Dim ret As Variant
Dim pArgs(0 To 5) As Long
Dim vt(0 To 5) As Integer
Dim Args(0 To 5) As Long
Dim ResultPtr As Long
Dim TinyM As TinyMORRSLT
Dim py() As Byte
Dim i As Integer
IFELanguage_GetMorphResult = ""
If IFELanguage = 0 Then Exit Function
Args(0) = &H30000
Args(1) = &H40000100
Args(2) = Len(HzStr)
Args(3) = StrPtr(HzStr)
Args(4) = 0
Args(5) = VarPtr(ResultPtr)
For i = 0 To 5
vt(i) = vbLong
pArgs(i) = VarPtr(Args(i)) - 8
Next
DispCallFunc IFELanguage, 20, 4, vbLong, 6, vt(0), pArgs(0), ret
MoveMemory TinyM, ByVal ResultPtr, 4 * 3
If https://www.sodocs.net/doc/b58961961.html,hOutput > 0 Then
ReDim py(0 To https://www.sodocs.net/doc/b58961961.html,hOutput * 2 - 1)
MoveMemory py(0), ByVal TinyM.pwchOutput, https://www.sodocs.net/doc/b58961961.html,hOutput * 2
IFELanguage_GetMorphResult = py
End If
CoTaskMemFree (ResultPtr)
End Function
Private Function GetInitial(py As String) As String
Dim Char1 As String
Dim Char2 As String
Char1 = Left(py, 1)
Char2 = Mid(py, 2, 1)
GetInitial = Char1
If Not pvOnlyOneChar Then
Select Case Char1
Case "z", "c", "s"
If Char2 = "h" Then GetInitial = GetInitial + Char2
End Select
End If
End Function
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''' Subroutine: GetPinYin
'''
''' Purpose: 返回汉字的拼音
'''
''' Arguments: HzStr - 待转换的拼音
'''
'''
''' Date Developer Action
''' --------------------------------------------------------------------------
''' 02 April 2007 tt.t Create
'''
Public Function GetPinYin(HzStr As String) As String
Dim i As Integer
Dim tmpStr As String
GetPinYin = ""
If HzStr <> "" Then
If pvUseSeperator Or pvInitialOnly Then
For i = 1 To Len(HzStr)
tmpStr = IFELanguage_GetMorphResult(Mid(HzStr, i, 1))
If tmpStr <> "" Then
If pvInitialOnly Then
GetPinYin = GetPinYin & GetInitial(tmpStr) & pvSeperator
Else
GetPinYin = GetPinYin & tmpStr & pvSeperator
End If
End If
Next
If Len(GetPinYin) > 0 Then GetPinYin = Left(GetPinYin, Len(GetPinYin) - 1) Else
GetPinYin = IFELanguage_GetMorphResult(HzStr)
End If
End If
End Function
Public Function AdjustPhoneticNotation(Hz As String, pn As PhoneticNotation) As String
Dim i As Integer
AdjustPhoneticNotation = Hz
'未进行优化
Select Case pn
Case pnNoNotation
For i = LBound(dNotation) To UBound(dNotation)
AdjustPhoneticNotation = Replace(AdjustPhoneticNotation, sNotation1(i), dNotation(i))
Next
For i = LBound(dNotation) To UBound(dNotation)
AdjustPhoneticNotation = Replace(AdjustPhoneticNotation, sNotation2(i), dNotation(i))
Next
End Select
End Function
Private Sub Class_Initialize()
IFELanguage = 0
InitalArray
InitialOnly = False
GenGUID
If CoCreateInstance(MSIME_GUID, 0, 1, _
IFELanguage_GUID, IFELanguage) = 0 Then
IFELanguage_Open
pvUseSeperator = False
pvSeperator = " "
Else
Err.Raise "OLE error!!"
End If
End Sub
Private Sub Class_Terminate()
If IFELanguage <> 0 Then IFELanguage_Close
End Sub
Property Get Seperator() As String
Seperator = pvSeperator
End Property
Property Let Seperator(Value As String)
pvSeperator = Value
End Property
Property Get UseSeperator() As Boolean
UseSeperator = pvUseSeperator
End Property
Property Let UseSeperator(V alue As Boolean)
pvUseSeperator = Value
End Property
Property Get InitialOnly() As Boolean
UseSeperator = pvInitialOnly
End Property
Property Let InitialOnly(Value As Boolean)
pvInitialOnly = Value
End Property
Property Get OnlyOneChar() As Boolean
UseSeperator = pvOnlyOneChar
End Property
Property Let OnlyOneChar(Value As Boolean)
pvOnlyOneChar = Value
End Property
Public Function HzToPy(Hz As String, Optional Sep As String = "", Optional ShowNotation As Boolean = True, Optional ShowInitialOnly As Boolean, Optional ShowOnlyOneChar As Boolean = True) As String
Dim hp As HZ2PY
Set hp = New HZ2PY '创建类
If Sep <> "" Then
hp.Seperator = Sep
https://www.sodocs.net/doc/b58961961.html,eSeperator = True
End If
hp.InitialOnly = ShowInitialOnly
hp.OnlyOneChar = ShowOnlyOneChar
HzToPy = hp.GetPinYin(Hz)
If Not ShowNotation Then HzToPy = hp.AdjustPhoneticNotation(HzToPy, pnNoNotation) Set hp = Nothing '释放类
End Function
在e x c e l中把汉字转换成拼音只取首字母修订 版 IBMT standardization office【IBMT5AB-IBMT08-IBMT2C-ZZT18】
打开Excel->工具->宏->Viaual Basic编辑器 在弹出来的窗口中对着VBAproject点右键->插入->模块下面会出现一个名为"模块1",点击 在右边的空白栏中粘贴以下内容: Function pinyin(p As String) As String i = Asc(p) Select Case i Case -20319 To -20284: pinyin = "A" Case -20283 To -19776: pinyin = "B" Case -19775 To -19219: pinyin = "C"
Case -19218 To -18711: pinyin = "D" Case -18710 To -18527: pinyin = "E" Case -18526 To -18240: pinyin = "F" Case -18239 To -17923: pinyin = "G" Case -17922 To -17418: pinyin = "H" Case -17417 To -16475: pinyin = "J" Case -16474 To -16213: pinyin = "K" Case -16212 To -15641: pinyin = "L" Case -15640 To -15166: pinyin = "M"
Case -15165 To -14923: pinyin = "N" Case -14922 To -14915: pinyin = "O" Case -14914 To -14631: pinyin = "P" Case -14630 To -14150: pinyin = "Q" Case -14149 To -14091: pinyin = "R" Case -14090 To -13319: pinyin = "S" Case -13318 To -12839: pinyin = "T" Case -12838 To -12557: pinyin = "W" Case -12556 To -11848: pinyin = "X" Case -11847 To -11056: pinyin = "Y"
公司要建DataBase,为了让老外们看明白,所以要吧中文店名全部转换成拼音。 5000多家,搞得头好晕,所以请教了下高手,发现了VBA的一个小技巧,分享给大家, 希望大家有用哦::P 具体方法如下: 打开Excel->Tools->Macro(宏)->Viaual Basic编辑器 点击插入->Module(模块) 然后会出现一个Module1,空白栏中粘贴以下内容: Option Explicit Option Compare Text Public Function HZQP(expression As String, Optional delimiter As String = " ", Optional limit As Integer = -1) As String 'expression ' 必需的?包含要转换的字符串? 'delimiter ' 可选的。用于标识分隔拼音的字符串字符。如果忽略,则使用空格字符(" ")作为分隔符。如果delimiter是一个长度为零的字符串,则返回仅包含一个元素,即完整的expression字符串。 'limit ' 可选的。要返回拼音字符数,–1表示返回所有的拼音字符数,1表示返回拼音首字母。 Dim STR As String, arrWord(400) As String Dim i As Integer, j As Integer arrWord(1) = "吖a" arrWord(2) = "哎ai" arrWord(3) = "腤an" arrWord(4) = "肮ang" arrWord(5) = "凹ao" arrWord(6) = "八ba" arrWord(7) = "挀bai" arrWord(8) = "扳ban" arrWord(9) = "邦bang" arrWord(10) = "勹bao" arrWord(11) = "陂bei" arrWord(12) = "奔ben"
Option Explicit Option Compare Text Public Function HZQP(expression As String, Optional delimiter As String = " ", Optional limit As Integer = -1) As String 'expression ' 必需的?包含要转换的字符串? 'delimiter ' 可选的。用于标识分隔拼音的字符串字符。如果忽略,则使用空格字符(" ")作为分隔符。如果delimiter是一个长度为零的字符串,则返回仅包含一个元素,即完整的 expression字符串。 'limit ' 可选的。要返回拼音字符数,–1表示返回所有的拼音字符数,1表示返回拼音首字母。 Dim STR As String, arrWord(400) As String Dim i As Integer, j As Integer arrWord(1) = "吖a" arrWord(2) = "哎ai" arrWord(3) = "腤an" arrWord(4) = "肮ang" arrWord(5) = "凹ao" arrWord(6) = "八ba" arrWord(7) = "挀bai" arrWord(8) = "扳ban" arrWord(9) = "邦bang" arrWord(10) = "勹bao" arrWord(11) = "陂bei" arrWord(12) = "奔ben" arrWord(13) = "崩beng" arrWord(14) = "皀bi" arrWord(15) = "边bian" arrWord(16) = "杓biao" arrWord(17) = "憋bie" arrWord(18) = "邠bin" arrWord(19) = "仌bing" arrWord(20) = "拨bo" arrWord(21) = "峬bu" arrWord(22) = "嚓ca" arrWord(23) = "猜cai" arrWord(24) = "飡can" arrWord(25) = "仓cang" arrWord(26) = "撡cao" arrWord(27) = "冊ce" arrWord(28) = "嵾cen" arrWord(29) = "噌ceng" arrWord(30) = "*cha"
打开Excel->工具->宏->Viaual Basic编辑器 在弹出来的窗口中对着VBAproject点右键->插入->模块下面会出现一个名为"模块1",点击 在右边的空白栏中粘贴以下内容: Function pinyin(p As String) As String i = Asc(p) Select Case i Case -20319 To -20284: pinyin = "A" Case -20283 To -19776: pinyin = "B" Case -19775 To -19219: pinyin = "C" Case -19218 To -18711: pinyin = "D" Case -18710 To -18527: pinyin = "E" Case -18526 To -18240: pinyin = "F" Case -18239 To -17923: pinyin = "G" Case -17922 To -17418: pinyin = "H" Case -17417 To -16475: pinyin = "J" Case -16474 To -16213: pinyin = "K" Case -16212 To -15641: pinyin = "L" Case -15640 To -15166: pinyin = "M" Case -15165 To -14923: pinyin = "N" Case -14922 To -14915: pinyin = "O" Case -14914 To -14631: pinyin = "P"
把汉字转换成拼音 录入学生资料时需要输入姓名的汉字拼音,但怎样才能把姓名转换成拼音呢? 1、先把EXCEL中的学生姓名复制,再打开WORD,然后右键“选择性粘贴”,选择“无格式文 本”后确定。 2、再全选刚才学生的姓名,点击WORD中的“格式”菜单栏,找到“中文版式”里面的“拼音指 南”,(图1),可以把其中的“字号”由“5”设置大一些,如设置为“20”等后,然后“确定”,一次转换不成,可以再次转换,要注意一次可能转换不了,要多次才行。转换后效果,如(图2)。 3、全选所有的文字,右键选择“切换域代码”,出现如(图3)的文字,点击“编辑”菜单栏,找 到“查找(替换)”,切换到“替换”,在“查找内容”处输入:[)]*[)](注意是半角状态下的符号。),在“替换为”处输入:一个半角状态下的空格,然后点击下面的“高级”,在“使用通配符”打上“√”,最后点击“全部替换”。 4、接着用同样的方法,在“查找内容”处输入:EQ*[(]*[(](注意是半角状态下的符号。), 在“替换为”处输入:EQ ,(注意:EQ后还有一个半角状态下的空格)(图4),然后“全部替换”。效果如(图5),然后右键再选择“切换域代码”,剩下拼音。 5、全选所有拼音,复制后,新建一个EXCEL文档,按右键“选择性粘贴”,即可把汉字转换 成拼音。 图1 图2 图3
6、复制刚才的拼音到EXCEL 里,在EXCEL 中选择“编辑”,找到“定位”,点击“定位条件”,在里面选择“空值”后“确定”,对着其中已经选择的空格,右键“删除”,选择“下方单元格上移”后确定,即可把拼音复制到对应的姓名右边。 图4 图5 图6
原理,使用Dictionary技术 1.添加索引 2.遍历词典 <% Set d = CreateObject("Scripting.Dictionary") d.add "a",-20319 d.add "ai",-20317 d.add "an",-20304 d.add "ang",-20295 d.add "ao",-20292 d.add "ba",-20283 d.add "bai",-20265 d.add "ban",-20257 d.add "bang",-20242 d.add "bao",-20230 d.add "bei",-20051 d.add "ben",-20036 d.add "beng",-20032 d.add "bi",-20026 d.add "bian",-20002 d.add "biao",-19990 d.add "bie",-19986 d.add "bin",-19982 d.add "bing",-19976 d.add "bo",-19805 d.add "bu",-19784 d.add "ca",-19775 d.add "cai",-19774 d.add "can",-19763 d.add "cang",-19756 d.add "cao",-19751 d.add "ce",-19746 d.add "ceng",-19741 d.add "cha",-19739 d.add "chai",-19728 d.add "chan",-19725 d.add "chang",-19715 d.add "chao",-19540 d.add "che",-19531 d.add "chen",-19525 d.add "cheng",-19515 d.add "chi",-19500 d.add "chong",-19484
第一部分附录3 汉字转换为拼音字头原理 GB 2312中对所收汉字进行了“分区”处理,每区含有94个汉字/符号。这种表示方式也称为区位码。 1)01-09区为特殊符号。 2)16-55区为一级汉字,按拼音排序。 3)56-87区为二级汉字,按部首/笔画排序。 4)10-15区及88-94区则未有编码。 在使用GB2312的程序中,通常采用EUC储存方法,以便兼容于ASCII。浏览器编码表上的“GB2312”,通常都是指“EUC-CN”表示法。 每个汉字及符号以两个字节来表示。第一个字节称为“高位字节”(也称“区字节)”,第二个字节称为“低位字节”(也称“位字节”)。 “高位字节”使用了0xA1-0xF7(把01-87区的区号加上0xA0),“低位字节”使用了0xA1-0xFE(把01-94加上0xA0)。由于一级汉字从16区起始,汉字区的“高位字节”的范围是0xB0-0xF7,“低位字节”的范围是0xA1-0xFE,占用的码位是72*94=6768。其中有5个空位是D7FA-D7FE。 例如“啊”字在大多数程序中,会以两个字节,0xB0(第一个字节)0xA1(第二个字节)储存。区位码=区字节+位字节(与区位码对比: 0xB0=0xA0+16,0xA1=0xA0+1)。 设计: 用一个数组存放国标一级汉字不同读音的起始区位码:如 static final int[] secPosValueList = { 1601, 1637, 1833, 2078, 2274, 2302, 2433, 2594, 2787, 3106, 3212, 3472, 3635, 3722, 3730, 3858, 4027, 4086, 4390, 4558, 4684, 4925, 5249, 9999}; 一个数据存放存放国标一级汉字不同读音的起始区位码对应读音 static final char[] firstLetter = { 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'w', 'x', 'y', 'z'}; 处理方法:
在弹出来的窗口中对着VBAproject点右键->插入->模块下面会出现一个名为"模块1",点击 在右边的空白栏中粘贴以下内容: Function pinyin(p As String) As String i = Asc(p) Select Case i Case -20319 To -20284: pinyin = "A" Case -20283 To -19776: pinyin = "B" Case -19775 To -19219: pinyin = "C" Case -19218 To -18711: pinyin = "D" Case -18710 To -18527: pinyin = "E" Case -18526 To -18240: pinyin = "F" Case -18239 To -17923: pinyin = "G" Case -17922 To -17418: pinyin = "H" Case -17417 To -16475: pinyin = "J" Case -16474 To -16213: pinyin = "K"
Case -16212 To -15641: pinyin = "L" Case -15640 To -15166: pinyin = "M" Case -15165 To -14923: pinyin = "N" Case -14922 To -14915: pinyin = "O" Case -14914 To -14631: pinyin = "P" Case -14630 To -14150: pinyin = "Q" Case -14149 To -14091: pinyin = "R" Case -14090 To -13319: pinyin = "S" Case -13318 To -12839: pinyin = "T" Case -12838 To -12557: pinyin = "W" Case -12556 To -11848: pinyin = "X" Case -11847 To -11056: pinyin = "Y" Case -11055 To -2050: pinyin = "Z" Case Else: pinyin = p End Select End Function Function getpy(str)
Excel自定义函数方法: 1.启动Excel 2003(其它版本请仿照操作),打开相应的工作表; 2.执行“工具→宏→Visual Basic编辑器”命令(或者直接按“Alt+F11”组合键),进入Visual Basic编辑状态; 3.执行“插入→模块”命令,插入一个新模块。再双击插入的模块,进入模块代码编辑状态; 4.将代码输入其中; 5.代码输入完成后,关闭Visual Basic编辑窗口,返回Excel编辑状态; 6.自定义函数就可以用了,如:选中D2单元格,输入公式:=getpy(A2)。 有极个别的汉字不能转换,手动就可以了!很方便了。 Function pinyin(p As String) As String i = Asc(p) Select Case i Case -20319 To -20318: pinyin = "a " Case -20317 To -20305: pinyin = "ai " Case -20304 To -20296: pinyin = "an " Case -20295 To -20293: pinyin = "ang " Case -20292 To -20284: pinyin = "ao " Case -20283 To -20266: pinyin = "ba " Case -20265 To -20258: pinyin = "bai " Case -20257 To -20243: pinyin = "ban " Case -20242 To -20231: pinyin = "bang " Case -20230 To -20052: pinyin = "bao " Case -20051 To -20037: pinyin = "bei " Case -20036 To -20033: pinyin = "ben " Case -20032 To -20027: pinyin = "beng " Case -20026 To -20003: pinyin = "bi " Case -20002 To -19991: pinyin = "bian " Case -19990 To -19987: pinyin = "biao " Case -19986 To -19983: pinyin = "bie " Case -19982 To -19977: pinyin = "bin " Case -19976 To -19806: pinyin = "bing " Case -19805 To -19785: pinyin = "bo " Case -19784 To -19776: pinyin = "bu " Case -19775 To -19775: pinyin = "ca " Case -19774 To -19764: pinyin = "cai " Case -19763 To -19757: pinyin = "can " Case -19756 To -19752: pinyin = "cang " Case -19751 To -19747: pinyin = "cao " Case -19746 To -19742: pinyin = "ce " Case -19741 To -19740: pinyin = "ceng " Case -19739 To -19729: pinyin = "cha " Case -19728 To -19726: pinyin = "chai "
Excel中将汉字转化为拼音(完整拼音)版 1.打开Excel->工具->宏->Viaual Basic编辑器 2.在弹出来的窗口中对着VBAproject点右键->插入->模块 3.下面会出现一个名为"模块1",点击 4.在右边的空白栏中粘贴以下内容: Function pinyin(p As String) As String i = Asc(p) Select Case i Case -20319 To -20318: pinyin = "a " Case -20317 To -20305: pinyin = "ai " Case -20304 To -20296: pinyin = "an " Case -20295 To -20293: pinyin = "ang " Case -20292 To -20284: pinyin = "ao " Case -20283 To -20266: pinyin = "ba " Case -20265 To -20258: pinyin = "bai " Case -20257 To -20243: pinyin = "ban " Case -20242 To -20231: pinyin = "bang " Case -20230 To -20052: pinyin = "bao " Case -20051 To -20037: pinyin = "bei " Case -20036 To -20033: pinyin = "ben " Case -20032 To -20027: pinyin = "beng " Case -20026 To -20003: pinyin = "bi " Case -20002 To -19991: pinyin = "bian " Case -19990 To -19987: pinyin = "biao " Case -19986 To -19983: pinyin = "bie " Case -19982 To -19977: pinyin = "bin " Case -19976 To -19806: pinyin = "bing " Case -19805 To -19785: pinyin = "bo " Case -19784 To -19776: pinyin = "bu " Case -19775 To -19775: pinyin = "ca " Case -19774 To -19764: pinyin = "cai " Case -19763 To -19757: pinyin = "can " Case -19756 To -19752: pinyin = "cang " Case -19751 To -19747: pinyin = "cao " Case -19746 To -19742: pinyin = "ce " Case -19741 To -19740: pinyin = "ceng " Case -19739 To -19729: pinyin = "cha " Case -19728 To -19726: pinyin = "chai " Case -19725 To -19716: pinyin = "chan " Case -19715 To -19541: pinyin = "chang " Case -19540 To -19532: pinyin = "chao " Case -19531 To -19526: pinyin = "che "
Java中文字转拼音工具类 2020-03-24 09:49:09 有时,我们需要将中文汉字转为拼音存储,例如用于检索时,直接输入拼音或者首字母也能检索出我们想要的结果,这时我们便需要将汉字转拼音 ?1、首先我们在 pom.xml 文件中引入需要的 maven 依赖 com.belerweb pinyin4j 2.5.1 ?2、汉字转拼音工具类代码如下 Pinyin4jUtil.java: import net.sourceforge.pinyin4j.PinyinHelper; import net.sourceforge.pinyin4j.format.HanyuPinyinCaseType; import net.sourceforge.pinyin4j.format.HanyuPinyinOutputFormat; import net.sourceforge.pinyin4j.format.HanyuPinyinToneType; import net.sourceforge.pinyin4j.format.HanyuPinyinVCharType; import net.sourceforge.pinyin4j.format.exception.BadHanyuPinyinOutputFormatComb ination; /** –中文转化为拼音工具类 –@author lizhou –*/ public class Pinyin4jUtil { /** ?获得汉语拼音首字母大写 ?@param chines 汉字 ?@return */ public static String getAlpha2UpperCase(String chines) { String pinyinNam e = "“; char[] nameChar = chines.toCharArray(); HanyuPinyinOutputFormat defaultFormat = new HanyuPinyinOutputFormat(); defaultFormat.setCaseType(HanyuPinyinCaseType.UPPERCASE); defaultFormat.setToneType(HanyuPinyinToneType.WITHOUT_T ONE); for (int i = 0; i < nameChar.length; i++) { if (nameChar[i] > 128) { try { pinyinName += PinyinHelper.toHanyuPinyinStringArray(nameChar[i], defaultFormat)[0].charAt(0); } catch (BadHanyuPinyinOutputFormatCombination e) { System.out.println(”获得汉语拼音首字母异常:}" + e.getMessage()); } } else { pinyinName += nameChar[i]; } } return pinyinName; } /**
Excel自定义函数法: 1.启动Excel 2003(其它版本请仿照操作),打开相应的工作表; 2.执行“工具→宏→Visual Basic编辑器”命令(或者直接按“Alt+F11”组合键),进入Visual Basic编辑状态; 3.执行“插入→模块”命令,插入一个新模块。再双击插入的模块,进入模块代码编辑状态; 4.将代码输入其中; 5.代码输入完成后,关闭Visual Basic编辑窗口,返回Excel编辑状态; 6.自定义函数就可以用了,如:选中D2单元格,输入公式:=getpy(A2)。 有极个别的汉字不能转换,手动就可以了!很便了。 Function pinyin(p As String) As String i = Asc(p) Select Case i Case -20319 To -20318: pinyin = "a " Case -20317 To -20305: pinyin = "ai " Case -20304 To -20296: pinyin = "an " Case -20295 To -20293: pinyin = "ang " Case -20292 To -20284: pinyin = "ao " Case -20283 To -20266: pinyin = "ba " Case -20265 To -20258: pinyin = "bai " Case -20257 To -20243: pinyin = "ban " Case -20242 To -20231: pinyin = "bang " Case -20230 To -20052: pinyin = "bao " Case -20051 To -20037: pinyin = "bei " Case -20036 To -20033: pinyin = "ben " Case -20032 To -20027: pinyin = "beng " Case -20026 To -20003: pinyin = "bi " Case -20002 To -19991: pinyin = "bian " Case -19990 To -19987: pinyin = "biao " Case -19986 To -19983: pinyin = "bie " Case -19982 To -19977: pinyin = "bin " Case -19976 To -19806: pinyin = "bing " Case -19805 To -19785: pinyin = "bo " Case -19784 To -19776: pinyin = "bu " Case -19775 To -19775: pinyin = "ca " Case -19774 To -19764: pinyin = "cai " Case -19763 To -19757: pinyin = "can " Case -19756 To -19752: pinyin = "cang " Case -19751 To -19747: pinyin = "cao " Case -19746 To -19742: pinyin = "ce " Case -19741 To -19740: pinyin = "ceng " Case -19739 To -19729: pinyin = "cha " Case -19728 To -19726: pinyin = "chai "
///
用公式将汉字转换为拼音的第一个字母 有时需要在Excel中将大量姓名或名称转换为拼音首字母的简写形式,例如将“周伯通”转换为“ZBT”。用定义名称和公式的方法可以轻松实现转换,在Windows XP中,以Excel 2003为例方法如下: 1.定义名称: 单击菜单“插入→名称→定义”,在弹出的对话框中“在当前工作簿中的名称”下的文本框中输入一个名称,如“py”,在“引用位置”下输入下列代码后确定: ={"","吖","八","嚓","咑","鵽","发","猤","铪","夻","咔","垃","嘸","旀", "噢","妑","七","囕","仨","他","屲","夕","丫","帀";"","A","B","C","D","E", "F","G","H","J","K","L","M","N","O","P","Q","R","S","T","W","X","Y"," Z"} 2.假如名单在A2:A100区域中,在B2单元格中输入公式: =LOOKUP(LEFT(A2,1),py)&LOOKUP(MID(A2,2,1),py)&LOOKUP(MID(A2,3,1), py)&LOOKUP(MID(A2,4,1),py) 然后拖动填充柄向下填充公式即可。
说明: 1.定义的名称为一个数组,以分号为界分为两行,第一行为汉字,第二行为与第一行汉字对应的拼音第一个大写字母。如果是Windows 7或Vista,则需要将定义的名称改为: ={"","吖","八","攃","咑","鵽","发","旮","哈","丌","咔","垃","妈","乸", "噢","帊","七","冄","仨","他","屲","夕","丫","帀";"","A","B","C","D","E", "F","G","H","J","K","L","M","N","O","P","Q","R","S","T","W","X","Y"," Z"} 2.公式用LOOKUP函数将每个汉字转换成拼音的第一个大写字母。其原理是:Excel中汉字(文本)之间也可以进行比较;汉字在Excel中的排列顺序与拼音顺序相同,排在前面的“值”小于排在其后的。上述定义名称的数组中,每个汉字都是所有拼音首字母相同的汉字中的第一个,例如“八”在Excel中排在其他所有拼音首字母为“B”的前面,因而其“值”最小。因此,LOOKUP函数在查找某个不在数组中的汉字,如“伯”字时,会在数组的第一行中查找小于它的最大值“八”,然后返回最后一行同列的值“B”。 另外,由于在Windows XP与Windows 7、Vista中Excel对汉字的排列顺序有所不同,故定义的名称内容也不同。如果在Windows 7、Vista中使用XP中定义的数组,LOOKUP函数第二个参数中的值未按升序排列,可能无法返回正确的结果。
excel 表格将汉字名字,转换为汉语拼音 excel 表格将汉字名字,转换为汉语拼音,除婵、婷、妍、倩、奕、嫦、冼、钰、炜、泉、楠、佼等部分字不能转换外,大部可以用此方法转换的。 打开excel 添加个模块 Function PinYin(Hz As String) Dim PinMa As String Dim MyPinMa As Variant Dim Temp As Integer, i As Integer, j As Integer PinMa = "a,20319,ai,20317,an,20304,ang,20295,ao,20292," PinMa = PinMa & "ba,20283,bai,20265,ban,20257,bang,20242,bao,20230,bei,20051,ben,20036,beng,200 32,bi,20026,bian,20002,biao,19990,bie,19986,bin,19982,bing,19976,bo,19805,bu,19 784," PinMa = PinMa & "ca,19775,cai,19774,can,19763,cang,19756,cao,19751,ce,19746,ceng,19741,cha,1973 9,chai,19728,chan,19725,chang,19715,chao,19540,che,19531,chen,19525,cheng,19515 ,chi,19500,chong,19484,chou,19479,chu,19467,chuai,19289,chuan,19288,chuang,1928 1,chui,19275,chun,19270,chuo,19263,ci,19261,cong,19249,cou,19243,cu,19242,cuan, 19238,cui,19235,cun,19227,cuo,19224," PinMa = PinMa & "da,19218,dai,19212,dan,19038,dang,19023,dao,19018,de,19006,deng,19003,di,18996 ,dian,18977,diao,18961,die,18952,ding,18783,diu,18774,dong,18773,dou,18763,du,1 8756,duan,18741,dui,18735,dun,18731,duo,18722," PinMa = PinMa & "e,18710,en,18697,er,18696," PinMa = PinMa & "fa,18526,fan,18518,fang,18501,fei,18490,fen,18478,feng,18463,fo,18448,fou,1844 7,fu,18446," PinMa = PinMa & "ga,18239,gai,18237,gan,18231,gang,18220,gao,18211,ge,18201,gei,18184,gen,18183 ,geng,18181,gong,18012,gou,17997,gu,17988,gua,17970,guai,17964,guan,17961,guang ,17950,gui,17947,gun,17931,guo,17928," PinMa = PinMa & "ha,17922,hai,17759,han,17752,hang,17733,hao,17730,he,17721,hei,17703,hen,17701
把下面的所有容复制,粘贴到步骤4中的空白处Function pinyin(p As String) As String i = Asc(p) Select Case i Case -20319 To -20318: pinyin = "a " Case -20317 To -20305: pinyin = "ai " Case -20304 To -20296: pinyin = "an " Case -20295 To -20293: pinyin = "ang " Case -20292 To -20284: pinyin = "ao " Case -20283 To -20266: pinyin = "ba " Case -20265 To -20258: pinyin = "bai " Case -20257 To -20243: pinyin = "ban " Case -20242 To -20231: pinyin = "bang " Case -20230 To -20052: pinyin = "bao " Case -20051 To -20037: pinyin = "bei " Case -20036 To -20033: pinyin = "ben " Case -20032 To -20027: pinyin = "beng " Case -20026 To -20003: pinyin = "bi " Case -20002 To -19991: pinyin = "bian " Case -19990 To -19987: pinyin = "biao " Case -19986 To -19983: pinyin = "bie " Case -19982 To -19977: pinyin = "bin "
--创建函数 IF OBJECT_ID('[fn_GetPinyin]')IS NOT NULL DROP FUNCTION [fn_GetPinyin] GO create function [dbo].[fn_GetPinyin](@words nvarchar(2000)) returns varchar(8000) as begin declare @word nchar(1) declare @pinyin varchar(8000) declare @i int declare @words_len int declare @unicode int set @i = 1 set @words =ltrim(rtrim(@words)) set @words_len =len(@words) while(@i <= @words_len)--循环取字符 begin set @word =substring(@words, @i, 1) set @unicode =unicode(@word) set @pinyin =ISNULL(@pinyin +SPACE(1),'')+ (case when unicode(@word)between 19968 and 19968+20901 then (select top 1 py from( select'a'as py,N'厑'as word union all select'ai',N'靉' union all select'an',N'黯' union all select'ang',N'醠' union all select'ao',N'驁' union all select'ba',N'欛' union all select'bai',N'瓸'--韛兡瓸 union all select'ban',N'瓣' union all select'bang',N'鎊' union all select'bao',N'鑤' union all select'bei',N'鐾' union all select'ben',N'輽' union all select'beng',N'鏰' union all select'bi',N'鼊' union all select'bian',N'變' union all select'biao',N'鰾' union all select'bie',N'彆' union all select'bin',N'鬢' union all select'bing',N'靐'