/**
 * 登录态校验的拦截器
 * @author c1panx
 * @date 2014-10-22 下午4:42:56
 */
@Component
public class LoginInterceptor extends HandlerInterceptorAdapter {

	private static final Logger logger = Logger.getLogger(LoginInterceptor.class);

	@Value("${member.center.base.url}")
	private String baseURL;
	
	private String loginURL;
	
	@Autowired
	private InterceptService interceptService;
	@Autowired
	private CookieService cookieService;
	
	private String[] filterURLArray = null;
	
	@PostConstruct
	public void init(){
		loginURL = baseURL + "login.xhtml";
		// 配置的页面，不需进行拦截
		filterURLArray = new String[]{
				"/login.xhtml",	// 登录页及登录接口
				"/login/flow.xhtml",	// 弹出层登录
				"/checkUser.xhtml",	// 判断用户名是否被注册过
				"/sendsms.xhtml",	// 短信发送
				
				"/register.xhtml",	// 注册页及注册接口
				"/register/flow.xhtml",	// 弹出层注册
				"/register.json",	// ajax退出
				"/register/toactivate.xhtml", // 邮箱注册成功后的提示页，可重发激活邮件
				"/register/resend.xhtml",	// 重新发送邮件
				"/register/activate.xhtml",	// 邮件激活地址
				
				"/authenticate.xhtml",	// 认证接口
				
				"/retrieve/pwd.xhtml",	// 密码找回
				"/retrieve/bymobile.xhtml",	
				"/retrieve/byemail.xhtml",	
				"/retrieve/byemailpwd.xhtml",//密码邮件找回发送邮件
				"/retrieve/toactivate.xhtml",//密码找回邮件激活地址，发送后跳转的提示页
				"/retrieve/password.xhtml",	// 邮件中密码重置连接
				
				//"/validateCode.xhtml",	// 图片验证码
				"/validate-code.xhtml",	// 图片验证码
				"/live800/trust.xhtml",	// live800用户基本信息
				"/login.json",	// ajax退出
				"/logout.json",	// ajax退出
		};
	}
	
	@Override
	public boolean preHandle(HttpServletRequest request,
			HttpServletResponse response, Object handler) throws Exception {
		
		this.displayClientInfo(request);
		
		interceptService.intercept(request);
		
		String path = request.getServletPath();
		if (this.filterPages(path)) {
			logger.info("不需被拦截：" + path);
			return true;
		}

		HttpSession session = request.getSession();
		Long sessionUserID = Tools.getUserId(session);
		Long cookieUserID = this.getUserIDInCookie(request);
		
		if(null == sessionUserID){
			// session中无用户信息，则校验cookie内容
			if (cookieService.checkToken(request)) {
				session.setAttribute(Constants.Session_User_ID, cookieUserID);
				return true;
			}
			response.sendRedirect(loginURL);
		} else {
			// session中有用户信息，cookie中无用户信息
			if(null == cookieUserID){
				response.sendRedirect(loginURL);
			}
			// 判断用户ID是否一致，一致不做处理
			if (sessionUserID.longValue() == cookieUserID.longValue()) {
				return true;
			}else{
				// 不一致时，判断cookie有效性
				if (cookieService.checkToken(request) == false) {
					// cookie有效，但用户ID不一致，跳转至登录页
					response.sendRedirect(loginURL);
				}
				// cookie有效，重新覆盖session中userID
				session.setAttribute(Constants.Session_User_ID, cookieUserID);
			}
		}
		return true;
	}
	
	/**
	 * 显示客户端请求信息，必要时可入库分析<br>
	 * @param request
	 */
	private void displayClientInfo(HttpServletRequest request){
		logger.info("----------------------------------------------------");
		logger.info("display request info: ");
		
		// 获得用户请求的URI
		logger.info("RequestURI: " + request.getRequestURI());
		logger.info("RequestURL: " + request.getRequestURL());
		logger.info("ContextPath: " + request.getContextPath());
		logger.info("ServletPath: " + request.getServletPath());
		logger.info("AuthType: " + request.getAuthType());
		logger.info("LocalAddr: " + request.getLocalAddr());
		logger.info("LocalName: " + request.getLocalName());
		logger.info("Method: " + request.getMethod());
		logger.info("PathInfo: " + request.getPathInfo());
		logger.info("PathTranslated: " + request.getPathTranslated());

		logger.info("basepath: " + Tools.getContextPath(request));
		
		// TODO
		String ip = IPUtil.getRequestIP(request);
		logger.info("clinet IP: " + ip);
		logger.info("----------------------------------------------------");
	}
	
	/**
	 * 过滤不需拦截的页面<br>
	 * 静态资源不存在拦截问题
	 * @param currentURL
	 * @return
	 * true: 页面被过滤掉 <br>
	 * false: 页面需验证有效性
	 */
	private boolean filterPages(String currentURL) {
		logger.info("被截取的地址：" + currentURL);
		if (StringUtils.isBlank(currentURL)) {
			return true;
		}
		if (null == filterURLArray) {
			return true;
		}
		for (String url : filterURLArray) {
			if (currentURL.equals(url)) {
				return true;
			}
		}
		return false;
	}
	
	/**
	 * cookie中的用户ID
	 * @param request
	 * @return
	 */
	private Long getUserIDInCookie(HttpServletRequest request) {
		Map<String, Cookie> cookieMap = cookieService.getCookieMap(request);
		String uid = cookieService.getCookieValue(Constants.COOKIE_UID, cookieMap);
		
		if (StringUtils.isBlank(uid)) {
			return null;
		}
		try {
			return Long.valueOf(uid);
		} catch (Exception e) {
			logger.error(e.getMessage(), e);
			return null;
		}
	}
	
}

Sub deleteBlankRows()
    ' http://club.excelhome.net/thread-314006-1-1.html
    ' Deletes the entire row within the selection if the ENTIRE row contains no data.
    ' 删除所选区域内空行
    ' We use Long in case they have over 32,767 rows selected.
    ' 我们用长整型以免出错
    Dim i As Long
    
    ' 需做清理的sheet索引
    Dim sheetIndex As Integer
    sheetIndex = 1
    
    'We turn off calculation and screenupdating to speed up the macro. 关掉自动重算和屏幕刷新以提高速度
    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    
    Dim endRowIndex As Long
    endRowIndex = Sheets(sheetIndex).UsedRange.rows.Count
    
    'We work backwards because we are deleting rows. 由于要删除行，所以要FOR NEXT要倒着来
    For i = endRowIndex To 1 Step -1
        If WorksheetFunction.CountA(Sheets(sheetIndex).rows(i)) = 0 Then
            Sheets(sheetIndex).rows(i).EntireRow.Delete
        End If
    Next i

        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With
End Sub

' 按指定列查询，若存在满足条件的单元格，则删除单元格所有行，且下面单元格上移
Sub clearRows()
    ' 需做清理的sheet索引
    Dim sheetIndex As Integer
    sheetIndex = 1
    
    ' 定义查找列开始、结束行索引位置
    Dim startRowIndex As Long, endRowIndex As Long
    startRowIndex = 1
    ' endRowIndex = Sheets(sheetIndex).[A65536].End(xlUp).row '指定列的有效行数
    endRowIndex = Sheets(sheetIndex).UsedRange.rows.Count ' 显示数据的最大行数
    
    Dim sColLetter As String, lookup_value As String
    sColLetter = "A"
    lookup_value = "1"
    
    ' 定义开始、结束单元格式位置
    Dim startCellAddress As String, endCellAddress As String
    endCellAddress = sColLetter + Str(endRowIndex) ' 指定列的最后一个单元格
    endCellAddress = Replace(endCellAddress, " ", "")
        
    Do
        startCellAddress = sColLetter + Str(startRowIndex)
        startCellAddress = Replace(startCellAddress, " ", "")
        
        Set newLookupRange = Range(startCellAddress + ":" + endCellAddress)
        ' Set matchcell = newLookupRange.Find(what:=lookup_value)
        Set matchcell = newLookupRange.Find(what:=lookup_value, LookAt:=xlWhole)
        
        If matchcell Is Nothing Then
            GoTo exitLoop
        End If
        
        startRowIndex = matchcell.row
        
        rows(matchcell.row & ":" & matchcell.row).Delete Shift:=xlUp
        
    Loop While 1 = 1
    
    Exit Sub
    
exitLoop:

End Sub

' ======================================================================================================
' 所有范围内查询，若存在满足条件的单元格，则删除单元格所有行，且下面单元格上移
Sub clearRows()
    ' 需做清理的sheet索引
    Dim sheetIndex As Integer
    sheetIndex = 1
    
    ' 定义查找列开始、结束行索引位置
    Dim endRowIndex As Long
    endRowIndex = Sheets(sheetIndex).UsedRange.rows.Count
    
    Dim lookup_value As String
    lookup_value = "t1"
    
    ' 定义开始、结束单元格式位置
    Dim startCellAddress As String, endCellAddress As String
    startCellAddress = "A1"
    
    ' 显示数据的最后一个单元格，用于指定所有区域内的查询删除
    endCellAddress = ColLetter(Sheets(sheetIndex).UsedRange.Columns.Count) + Str(endRowIndex)
    endCellAddress = Replace(endCellAddress, " ", "")
    
    Set lookupRange = Range(startCellAddress + ":" + endCellAddress)
    
    Do
        Set matchcell = lookupRange.Find(what:=lookup_value, LookAt:=xlWhole)
        
        If matchcell Is Nothing Then
            GoTo exitLoop
        End If
        
        rows(matchcell.row & ":" & matchcell.row).Delete Shift:=xlUp
    Loop While 1 = 1
    
    Exit Sub
    
exitLoop:

End Sub

' 根据列字母返回列索引
Function ColIndex(colName As String) As Integer
    On Error GoTo Errorhandler
    
    ColIndex = Range(colName & "1").Column
    
    Exit Function
    
Errorhandler:
    MsgBox "Error encountered, please re‐enter "
End Function

' 注意：文件夹路径需以"\"结尾

' 复制文件夹
Sub copyFolderSub()
    Dim sourcePath As String, destinationPath As String
    sourcePath = "D:\temp\vbamerge\"
    destinationPath = "D:\temp\vbamerge2\"
    Call copyFolderFn(sourcePath, destinationPath)
End Sub

' 复制文件夹
Private Function copyFolderFn(sourcePath As String, destinationPath As String)
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    If Dir(destinationPath, 16) = Empty Then
        ' 文件夹不存在，需创建
        ' 方法一： VBA创建
        'VBA.MkDir destinationPath
        
        ' 方法二： FileSystemObject
        fso.CreateFolder destinationPath
    End If
    
    Set f = fso.GetFolder(sourcePath)
    
    ' 复制单个文件
    Set fc = f.Files
    For Each f1 In fc
        ' 可再设置条件复制 eg:
        ' excel文件： Right(f1.Name, 4) = "xlsx" or Right(f1.Name, 3) = "xls"
        FileCopy f1.path, destinationPath & f1.Name
    Next
    
    ' 复制子文件夹
    Set fcfolders = f.SubFolders
    ' MsgBox fcFolders.Count
    For Each ff In fcfolders
        Call copyFolderFn(sourcePath & ff.Name & "\", destinationPath & ff.Name & "\")
    Next
End Function

<?xml version="1.0" encoding="UTF-8"?>  <!-- Log4J Configuration for online -->
<!DOCTYPE log4j:configuration PUBLIC "-//APACHE//DTD LOG4J 1.2//EN" "log4j.dtd">
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
	<!-- Appenders -->
	<appender name="console" class="org.apache.log4j.ConsoleAppender">
		<param name="Target" value="System.out" />
		<layout class="org.apache.log4j.PatternLayout">
			<param name="ConversionPattern" value="[%X{LocalHost}][%p] %-d{yyyy-MM-dd HH\:mm\:ss.sss} [%t] [%c.%M\:%L]  %m%n " />
		</layout>
	</appender>
	<!-- Appenders -->
	<appender name="appAppender" class="org.apache.log4j.DailyRollingFileAppender">
		<param name="File" value="/mnt/ntfs/member/center.log" />
		<param name="Encoding" value="UTF-8" />
		<param name="Append" value="true" />
		<param name="DatePattern" value="'.'yyyy-MM-dd" />
		<layout class="org.apache.log4j.PatternLayout">
			<param name="ConversionPattern" value="[%X{LocalHost}][%p] %-d{yyyy-MM-dd HH\:mm\:ss.sss} [%t] [%c.%M\:%L]  %m%n " />
		</layout>
	</appender>

	<appender name="errorAppender" class="org.apache.log4j.DailyRollingFileAppender">
		<param name="File" value="/mnt/ntfs/member/center.error.log" />
		<param name="Encoding" value="UTF-8" />
		<param name="Append" value="true" />
		<param name="DatePattern" value="'.'yyyy-MM-dd" />
		<layout class="org.apache.log4j.PatternLayout">
			<param name="ConversionPattern" value="[%X{LocalHost}][%p] %-d{yyyy-MM-dd HH\:mm\:ss.sss} [%t] [%c.%M\:%L]  %m%n " />
		</layout>
		<filter class="org.apache.log4j.varia.LevelRangeFilter">
			<param name="LevelMax" value="ERROR" />
			<param name="LevelMin" value="ERROR" />
		</filter>
	</appender>

	<!-- Spring Loggers -->
	<logger name="org.springframework">
		<level value="WARN" />
	</logger>

	<logger name="org.springframework.web">
		<level value="WARN" />
	</logger>

	<!-- Root Logger -->
	<root>
		<priority value="INFO" />
<!-- 		<priority value="DEBUG" /> -->
		<appender-ref ref="console" />
		<appender-ref ref="appAppender" />
		<appender-ref ref="errorAppender" />
	</root>

</log4j:configuration>

JSP页头：
<%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding="utf-8"%>
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ page isELIgnored="false" %>

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://java.sun.com/xml/ns/j2ee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	version="2.4"
	xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">

 	<context-param>
		<param-name>webAppRootKey</param-name>
		<param-value>member.center.root </param-value>
	</context-param>
	
	<context-param>
      <param-name>log4jConfigLocation</param-name>
      <param-value>classpath:log4j.xml</param-value>
    </context-param>
    
	<!-- Spring的配置文件 -->
	<context-param>
		<param-name>contextConfigLocation</param-name>
		<param-value>classpath:applicationContext.xml</param-value>
	</context-param>
	
	<!-- 日志配置文件 -->
    <listener>
      <listener-class>org.springframework.web.util.Log4jConfigListener</listener-class>
    </listener>
	<!-- 容器监听器 -->
	<listener>
		<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
	</listener>
	
	<!-- Spring默认的Servlet -->
	<servlet>  
    	<servlet-name>dispatcherservlet</servlet-name>  
    	<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class> 
    	<init-param>  
        	<param-name>contextConfigLocation</param-name>  
        	<param-value>classpath:applicationContext-mvc.xml</param-value>  
    	</init-param> 
    	<load-on-startup>1</load-on-startup>  
	</servlet>  
	
	<!-- Servlet映射 -->
	<servlet-mapping>  
    	<servlet-name>dispatcherservlet</servlet-name>  
    	<url-pattern>*.xhtml</url-pattern>  
	</servlet-mapping>
	<servlet-mapping>  
    	<servlet-name>dispatcherservlet</servlet-name>  
    	<url-pattern>*.json</url-pattern>  
	</servlet-mapping>  

	<!-- 编码格式 -->
	<filter>
        <filter-name>encodingFilter</filter-name>
        <filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
        <init-param>
            <param-name>encoding</param-name>
            <param-value>UTF-8</param-value>
        </init-param>
         <init-param>
      		<param-name>forceEncoding</param-name>
      		<param-value>true</param-value>
    	</init-param>
    </filter>
    <filter-mapping>
        <filter-name>encodingFilter</filter-name>
        <url-pattern>/*</url-pattern>
    </filter-mapping>

<!--     <session-config> -->
<!--     	<session-timeout>30</session-timeout> -->
<!--     </session-config> -->
    
    <!-- 欢迎页 -->
	<welcome-file-list>
		<welcome-file>index.jsp</welcome-file>
		<welcome-file>login.xhtml</welcome-file>
		<welcome-file>index.xhtml</welcome-file>
	</welcome-file-list>

<!-- jsp-config 用来说明包含的页面默认按照什么编码格式包含。web-app 标签的version必须是2.4的 -->
	<jsp-config>
		<jsp-property-group>
			<description>Special property group for JSP Configuration JSP example.</description>
			<display-name>JSPConfiguration</display-name>
			<url-pattern>*.jsp</url-pattern>
			<el-ignored>true</el-ignored>
			<page-encoding>UTF-8</page-encoding>
			<scripting-invalid>false</scripting-invalid>
			<include-prelude></include-prelude>
			<include-coda></include-coda>

			<description>Special property group for JSP Configuration JSP example.</description>
			<display-name>JSPConfiguration</display-name>
			<url-pattern>*.html</url-pattern>
			<el-ignored>true</el-ignored>
			<page-encoding>UTF-8</page-encoding>
			<scripting-invalid>false</scripting-invalid>
			<include-prelude></include-prelude>
			<include-coda></include-coda>
		</jsp-property-group>
	</jsp-config>
</web-app>


或在引用的JSP文件头加入：
<%@ page language="java" contentType="text/html; charset=UTF-8"  pageEncoding="UTF-8"%>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.cignacmb.member</groupId>
  <artifactId>center</artifactId>
  <packaging>war</packaging>
  <version>1.0.0</version>
  <name>service Maven Webapp</name>
  <url>http://maven.apache.org</url>
  
  <properties>
  	<spring.version>3.1.0.RELEASE</spring.version>
  </properties>
  
  <dependencies>
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>4.8.1</version>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>log4j</groupId>
			<artifactId>log4j</artifactId>
			<version>1.2.14</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-test</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-aop</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-aspects</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-orm</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-web</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-core</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-beans</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-webmvc</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>commons-dbcp</groupId>
			<artifactId>commons-dbcp</artifactId>
			<version>1.4</version>
		</dependency>
		<dependency>
			<groupId>org.aspectj</groupId>
			<artifactId>aspectjweaver</artifactId>
			<version>1.7.0</version>
		</dependency>
		<dependency>
			<groupId>org.mybatis</groupId>
			<artifactId>mybatis-spring</artifactId>
			<version>1.2.0</version>
		</dependency>
		<dependency>
			<groupId>org.mybatis</groupId>
			<artifactId>mybatis</artifactId>
			<version>3.2.2</version>
		</dependency>

		<dependency>
		  <groupId>com.thoughtworks.xstream</groupId>
		  <artifactId>xstream</artifactId>
		  <version>1.4.3</version>
		</dependency>

		<!-- offer by mike -->
		<dependency>
			<groupId>commons-beanutils</groupId>
			<artifactId>commons-beanutils</artifactId>
			<version>1.7.0</version>
		</dependency>
		<dependency>
			<groupId>org.apache.commons</groupId>
			<artifactId>commons-lang3</artifactId>
			<version>3.3.2</version>
		</dependency>
		<dependency>
			<groupId>commons-io</groupId>
			<artifactId>commons-io</artifactId>
			<version>2.4</version>
		</dependency>
		<dependency>
			<groupId>javax.servlet</groupId>
			<artifactId>servlet-api</artifactId>
			<version>2.4</version>
			<scope>provided</scope>
		</dependency>
		<dependency>
			<groupId>org.aspectj</groupId>
			<artifactId>aspectjrt</artifactId>
			<version>1.6.8</version>
		</dependency>
		<dependency>
			<groupId>cglib</groupId>
			<artifactId>cglib-nodep</artifactId>
			<version>2.1_3</version>
		</dependency>
		<dependency>
			<groupId>commons-pool</groupId>
			<artifactId>commons-pool</artifactId>
			<version>1.5.6</version>
		</dependency>
		<dependency>
			<groupId>javax.servlet</groupId>
			<artifactId>jstl</artifactId>
			<version>1.2</version>
		</dependency>
		<dependency>
			<groupId>taglibs</groupId>
			<artifactId>standard</artifactId>
			<version>1.1.2</version>
		</dependency>
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>slf4j-simple</artifactId>
			<version>1.6.2</version>
		</dependency>
		<dependency>
			<groupId>org.codehaus.jackson</groupId>
			<artifactId>jackson-core-lgpl</artifactId>
			<version>1.8.1</version>
		</dependency>
		<dependency>
			<groupId>org.codehaus.jackson</groupId>
			<artifactId>jackson-mapper-lgpl</artifactId>
			<version>1.8.1</version>
		</dependency>
		<dependency>
			<groupId>javax.annotation</groupId>
			<artifactId>jsr250-api</artifactId>
			<version>1.0</version>
		</dependency>
		<dependency>
			<groupId>net.sf.json-lib</groupId>
			<artifactId>json-lib</artifactId>
			<version>2.4</version>
			<classifier>jdk15</classifier>
		</dependency>
		<dependency>
			<groupId>commons-logging</groupId>
			<artifactId>commons-logging</artifactId>
			<version>1.1.1</version>
		</dependency>

		<dependency>
			<groupId>com.fasterxml.jackson.core</groupId>
			<artifactId>jackson-databind</artifactId>
			<version>2.4.0</version>
		</dependency>
		<dependency>
			<groupId>com.microsoft.sqlserver</groupId>
			<artifactId>sqljdbc4</artifactId>
			<version>4.2.0</version>
		</dependency>

		<dependency>
			<groupId>com.oracle</groupId>
			<artifactId>ojdbc6</artifactId>
			<version>11.2.0.3.0</version>
		</dependency>
		<!-- memcached client -->
		<dependency>
			<groupId>com.danga</groupId>
			<artifactId>java-memcached</artifactId>
			<version>2.6.6</version>
			<type>jar</type>
		</dependency>
		
		<dependency>
			<groupId>com.google.code.gson</groupId>
			<artifactId>gson</artifactId>
			<version>2.2.4</version>
		</dependency>
		<dependency>
			<groupId>org.apache.httpcomponents</groupId>
			<artifactId>httpclient</artifactId>
			<version>4.1.2</version>
		</dependency>
		
		<!-- 理赔  client-->
		<dependency>
		  <groupId>org.apache.axis</groupId>
		  <artifactId>axis</artifactId>
		  <version>1.4</version>
		</dependency>
		
		<dependency>
		  <groupId>wsdl4j</groupId>
		  <artifactId>wsdl4j</artifactId>
		  <version>1.6.3</version>
		</dependency>
		<dependency>
		    <groupId>javax.mail</groupId>
		    <artifactId>mail</artifactId>
		    <version>1.4</version>
		</dependency>
		<dependency>
		    <groupId>javax.xml</groupId>
		    <artifactId>jaxrpc</artifactId>
		    <version>1.0</version>
		</dependency>
		<dependency>
           <groupId>com.cignacmb</groupId>
           <artifactId>cigna-cmb-esb-client</artifactId>
           <version>1.0.3</version>
       </dependency>
		
		<dependency>
			<groupId>javax.mail</groupId>
			<artifactId>mail</artifactId>
			<version>1.4.1</version>
		</dependency>
		 <dependency>
			<groupId>org.freemarker</groupId>
			<artifactId>freemarker</artifactId>
			<version>2.3.16</version>
		 </dependency>

		 <dependency>
		 	<groupId>commons-codec</groupId>
		 	<artifactId>commons-codec</artifactId>
		 	<version>1.10</version>
		 </dependency>
		 <dependency>
		 	<groupId>com.google.code</groupId>
		 	<artifactId>kaptcha</artifactId>
		 	<version>2.3.2</version>
		 </dependency>
  </dependencies>
  
  <build>
    <finalName>member</finalName>
	<plugins>
		<plugin>
			<groupId>org.apache.maven.plugins</groupId>
			<artifactId>maven-compiler-plugin</artifactId>
			<version>2.1</version>
			<configuration>
				<source>1.6</source>
				<target>1.6</target>
				<encoding>UTF-8</encoding>
			</configuration>
		</plugin>
		<plugin>
			<artifactId>maven-resources-plugin</artifactId>
			<version>2.4.1</version>
			<configuration>
				<encoding>UTF-8</encoding>
			</configuration>
		</plugin>
	</plugins>
  </build>
</project>

// JUnitTest
/**
 * @author c1panx
 */
@RunWith(SpringJUnit4ClassRunner.class)
@TransactionConfiguration(transactionManager = "transactionManager", defaultRollback = false)
@ContextConfiguration({"classpath:applicationContext-test.xml"})
public class AbstractJUnitTest extends AbstractTransactionalJUnit4SpringContextTests  {

}

// db-config.properties
// SQLServer
jdbc.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
jdbc.url=jdbc:sqlserver://VMDBSQ01-TST\\PHDBSQLG01;DatabaseName=quote_cn
jdbc.username=LG
jdbc.password=abcde123.

// Oracle
jdbc.driverClassName=oracle.jdbc.driver.OracleDriver
jdbc.url=jdbc:oracle:thin:@168.140.161.54:1522:ecoms
jdbc.username=test
jdbc.password=test

// sqlit
jdbc.driverClassName=org.sqlite.JDBC
jdbc.url=jdbc:sqlite:test.db3
jdbc.username=
jdbc.password=

// applicationContext-test.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xmlns:p="http://www.springframework.org/schema/p"
	xmlns:tx="http://www.springframework.org/schema/tx" xmlns:aop="http://www.springframework.org/schema/aop" xmlns:util="http://www.springframework.org/schema/util"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
	http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd
	http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd 
	http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.0.xsd 
	http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.0.xsd"
	default-autowire="byName">
	
	<context:property-placeholder location="classpath:system-config/dev.properties, classpath:db-config.properties" ignore-unresolvable="true" />

	<!-- <context:annotation-config /> -->
	<!-- 自动扫描Bean -->
	<context:component-scan base-package="com.cignacmb.member.service" >
		<context:exclude-filter type="annotation" expression="org.springframework.stereotype.Controller" />
	</context:component-scan>

	<!--启动spring注解功能 -->
	<tx:annotation-driven transaction-manager="transactionManager" />

	<bean id="member_dataSource" class="org.apache.commons.dbcp.BasicDataSource"
		destroy-method="close">
		<property name="driverClassName" value="${jdbc.driverClassName}" />
		<property name="url" value="${jdbc.url}" />
		<property name="username" value="${jdbc.username}" />
		<property name="password" value="${jdbc.password}" />
		<property name="maxActive" value="100" />
		<property name="maxWait" value="1000" />
		<property name="poolPreparedStatements" value="false" />
		<property name="defaultAutoCommit" value="false" />
	</bean>

	<!--把mybatis SqlSessionFactory的创建交由spring管理 -->
	<bean id="sqlSessionFactoryBeanName" class="org.mybatis.spring.SqlSessionFactoryBean">
		<property name="dataSource" ref="member_dataSource" />
		<property name="configLocation" value="classpath:mybatis-config.xml" />
	</bean>

	<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
		<property name="basePackage"
			value="com.cignacmb.member.service.dao" />
	</bean>

	<!-- Local Transaction Management (txManager) -->
	<bean id="transactionManager"
		class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
		<property name="dataSource" ref="member_dataSource" />
	</bean>
	
	<bean id="freeMarker" class="org.springframework.web.servlet.view.freemarker.FreeMarkerConfigurer">  
    	<property name="templateLoaderPath" value="classpath:template"/><!--指定模板文件目录-->   
	    <property name="freemarkerSettings"><!-- 设置FreeMarker环境属性-->   
	      <props>  
<!-- 	          <prop key="template_update_delay">1800</prop> -->
	          <prop key="default_encoding">UTF-8</prop>
	          <prop key="locale">zh_CN</prop>
	      </props>  
	    </property>  
   </bean>
</beans>

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
	<settings>
		<!-- changes from the defaults -->
		<setting name="lazyLoadingEnabled" value="false" />
		<!-- JdbcType enumeration. Most common are: NULL, VARCHAR and OTHER -->
		<setting name="jdbcTypeForNull" value="NULL" />
		<setting name="logImpl" value="LOG4j" />
	</settings>
</configuration>

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xmlns:p="http://www.springframework.org/schema/p"
	xmlns:tx="http://www.springframework.org/schema/tx" xmlns:aop="http://www.springframework.org/schema/aop" xmlns:util="http://www.springframework.org/schema/util"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.1.xsd
	http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.1.xsd
	http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.1.xsd 
	http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.1.xsd 
	http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.1.xsd"
	default-autowire="byName">
	
	<context:property-placeholder location="classpath:system-config/${config_env}.properties" ignore-unresolvable="true" />

	<!-- 自动扫描Bean -->
	<context:component-scan base-package="com.cignacmb.member.center" >
		<context:exclude-filter type="annotation" expression="org.springframework.stereotype.Controller" />
	</context:component-scan>

	<!--启动spring注解功能 -->
	<tx:annotation-driven transaction-manager="transactionManager" />
	
	<!-- 强制使用CGLIB代理 -->
	<aop:aspectj-autoproxy proxy-target-class="true" />

	<!-- 配置数据源 -->
	<bean id="member_dataSource"
		class="org.springframework.jndi.JndiObjectFactoryBean">
		<property name="jndiName">
            <value>java:comp/env/jdbc/member_jndi</value>
        </property>
	</bean>

	<!--把mybatis SqlSessionFactory的创建交由spring管理 -->
	<bean id="sqlSessionFactoryBeanName" class="org.mybatis.spring.SqlSessionFactoryBean">
		<property name="dataSource" ref="member_dataSource" />
		<property name="configLocation" value="classpath:mybatis-config.xml" />
	</bean>

	<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
		<property name="basePackage" value="com.cignacmb.member.center.dao" />
	</bean>

	<!-- Local Transaction Management (txManager) -->
	<bean id="transactionManager"
		class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
		<property name="dataSource" ref="member_dataSource" />
	</bean>
	
	<bean id="freeMarker" class="org.springframework.web.servlet.view.freemarker.FreeMarkerConfigurer">  
    	<property name="templateLoaderPath" value="classpath:template"/><!--指定模板文件目录-->
	    <property name="freemarkerSettings"><!-- 设置FreeMarker环境属性-->
	      <props>  
<!-- 	          <prop key="template_update_delay">1800</prop> -->
	          <prop key="default_encoding">UTF-8</prop>
	          <prop key="locale">zh_CN</prop>
	      </props>  
	    </property>  
   </bean>

	<bean id="captchaProducer" class="com.google.code.kaptcha.impl.DefaultKaptcha">
		<property name="config">
			<bean class="com.google.code.kaptcha.util.Config">
				<constructor-arg>
					<props>
						<prop key="kaptcha.border">no</prop>
						<prop key="kaptcha.image.width">80</prop>
						<prop key="kaptcha.image.height">36</prop>
						<prop key="kaptcha.textproducer.char.length">4</prop>
						<prop key="kaptcha.textproducer.font.size">20</prop>
						<prop key="kaptcha.textproducer.font.names">宋体,楷体,微软雅黑</prop>
						<prop key="kaptcha.textproducer.font.color">23,133,12</prop>
						<prop key="kaptcha.background.clear.from">255,255,255</prop>
						<prop key="kaptcha.background.clear.to">255,255,255</prop>
						<prop key="kaptcha.textproducer.char.string">1234567890</prop>
						<prop key="kaptcha.obscurificator.impl">com.cignacmb.member.center.validatecode.MemberWaterRipple</prop>
						<prop key="kaptcha.noise.color">23,133,12</prop>
						<prop key="kaptcha.noise.impl">com.cignacmb.member.center.validatecode.MemberNoise</prop>
						<prop key="kaptcha.word.impl">com.cignacmb.member.center.validatecode.MemberWordRenderer</prop>
					</props>
				</constructor-arg>
			</bean>
		</property>
	</bean>
</beans>

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xmlns:p="http://www.springframework.org/schema/p"
	xmlns:tx="http://www.springframework.org/schema/tx" xmlns:aop="http://www.springframework.org/schema/aop" xmlns:util="http://www.springframework.org/schema/util"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.1.xsd
	http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.1.xsd
	http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.1.xsd 
	http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.1.xsd 
	http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.1.xsd"
	default-autowire="byName">
	
	<!-- 启用spring mvc 注解 -->
    <context:annotation-config />
    
	<!-- 解析配置文件 -->
	<context:property-placeholder location="classpath:system-config/${config_env}.properties" ignore-unresolvable="true" />
    
	<!-- 自动扫描Bean -->
	<context:component-scan base-package="com.cignacmb.member.center">
		<context:include-filter type="annotation" expression="org.springframework.stereotype.Controller" /> 
        <context:exclude-filter type="annotation" expression="org.springframework.stereotype.Service" />
	</context:component-scan>
	
     <!-- 处理在类级别上的@RequestMapping注解，增加了拦截器-->  
    <bean  
        class="org.springframework.web.servlet.mvc.annotation.DefaultAnnotationHandlerMapping" >  
        <property name="interceptors">  
           <!-- 多个拦截器,顺序执行 -->  
           <list>  
              <ref bean="loginInterceptor"/>  
           </list>  
        </property>  
    </bean>
    
    <!-- 处理方法级别上的@RequestMapping注解-->  
    <bean class="org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter" >  
        <property name="messageConverters">    
            <util:list id="beanList">
            	<!-- 将返回对象转换为json -->
                <ref bean="mappingJacksonHttpMessageConverter"/>    
            </util:list>    
        </property>   
    </bean>
	
	<!-- mvc的视图扭转 -->
	<bean id="viewResolver"	class="org.springframework.web.servlet.view.InternalResourceViewResolver" p:prefix="/WEB-INF/jsp/" p:suffix=".jsp" />
	
	<!-- 为了处理返回的JSON数据的编码，默认是ISO-88859-1的，这里把它设置为UTF-8,解决有乱码的情况 -->
  	<bean id="mappingJacksonHttpMessageConverter"  class="org.springframework.http.converter.json.MappingJacksonHttpMessageConverter">
 		<property name="supportedMediaTypes">    
            <list>
               <value>text/html;charset=UTF-8</value>
               <value>application/json</value>
               <value>text/plain;charset=UTF-8</value>
<!--                <value>application/x-javascript</value> -->
            </list>
        </property>
    </bean>

</beans>

Sub mergeFiles()
    Dim sourcePath As String
    sourcePath = "D:\temp\vbamerge\"
    
    Call mergeFilesFn(sourcePath)
    
End Sub

' 将指定目录下的所有文件，包手子目录下的文件，合并至当前执行宏的第1张sheet中
' 均只合并文件中的第1张sheet
Private Function mergeFilesFn(sourcePath As String, Optional rowIndex As Long = 1)
    ' 列字母索引
    Dim sColLetter As String
    Dim columnss As Integer
    
    Dim fs, f, f1, fc, s, rowss
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(sourcePath) 'Directory of excel files will be merge
    Set fc = f.Files
          
    ' 文件
    For Each f1 In fc
        If Right(f1.Name, 4) = "xlsx" Or Right(f1.Name, 3) = "xls" Then
            Workbooks.Open (f1.path)
            rowss = Workbooks(f1.Name).Sheets(1).Range("A65536").End(xlUp).row
            ' columnss = Workbooks(f1.Name).Sheets(1).Columns.Count ' sheet最大列数
            columnss = Workbooks(f1.Name).Sheets(1).UsedRange.Columns.Count ' 显示数据的最大列数
            
            sColLetter = ColLetter(columnss)
            ' MsgBox sColLetter & " = " + CStr(columnss)
              
            ' Workbooks(f1.Name).Sheets(1).Range("A1:z" & CStr(rowss)).Copy
            Workbooks(f1.Name).Sheets(1).Range("A1:" & sColLetter & CStr(rowss)).Copy
            Workbooks(1).Activate
            'Workbooks(1).Sheets(1).Range("A" & CStr(rowIndex) & ":z" & CStr(rowIndex + rowss)).Select
            Workbooks(1).Sheets(1).Range("A" & CStr(rowIndex) & ":" & sColLetter & CStr(rowIndex + rowss)).Select
            Workbooks(1).Sheets(1).Paste
            Application.CutCopyMode = False
              
            rowIndex = rowIndex + rowss
            Workbooks(f1.Name).Close savechanges:=False
        End If
    Next
    
    ' 子目录
    Set subfc = f.SubFolders
    For Each ff In subfc
        Call mergeFilesFn(sourcePath & ff.Name & "\", rowIndex)
    Next

End Function

' 获取列字母
Function ColLetter(ColNumber As Integer) As String
    On Error GoTo Errorhandler
    ColLetter = Left(Cells(1, ColNumber).Address(0, 0), 1 - (ColNumber > 26))
    Exit Function
    
Errorhandler:
    MsgBox "Error encountered, please re‐enter "
End Function

' FindNext有个致命的Bug，那就是它只能用于Sub过程里面，不能用于Function过程里面。
Sub testFindNext()    
    Dim lookup_vector As Range, result_vector As Range
    Dim lookup_value As String, FirstAddress As String
    Dim LISTAGG As String, ch As String
    
    ch = ", "
    
    lookup_value = Sheet1.Range("A3").Value
    Set lookup_vector = Range("B1:B111")
    Set result_vector = Range("C1:C111")
    
    With lookup_vector
        Set matchcell = .Find(what:=lookup_value)
        
        If Not matchcell Is Nothing Then
            FirstAddress = matchcell.Address
              
            Do
                If LISTAGG <> "" Then
                    LISTAGG = LISTAGG + ch
                End If
                LISTAGG = LISTAGG + Cells(matchcell.row, result_vector.Column)

                Set matchcell = .FindNext(after:=matchcell)

            Loop Until (matchcell Is Nothing) Or (matchcell.Address = FirstAddress)
        End If
    End With
    MsgBox LISTAGG
End Sub

' 2015-03-19
' 参照LOOKUP和VLOOKUP参数设置；
' 参考lookup参数设置
' lookup_value 第一个矢量中搜索到的值
' lookup_vector 是一个仅包含一列的区域
' result_vector 是一个仅包含一列的区域
' Optional ch As String = ","：合并后的分割符，可自定义，默认为英文逗号（”,”）
' 【后续处理】可选参数的容错处理
Function LISTAGG(lookup_value As String, lookup_vector As Range, Optional result_vector As Range, Optional ch As String = ", ", Optional removal As Boolean = True)
    If result_vector Is Nothing Then
        result_vector = lookup_vector
    End If
    
    ' 定义查找列字母索引
    Dim lookupColLetter As String
    lookupColLetter = ColLetter(lookup_vector.Column)
         
    ' 定义查找列开始、结束行索引位置
    Dim startRowIndex As Long, endRowIndex As Long
    startRowIndex = lookup_vector.Row
    endRowIndex = lookup_vector.Rows.Count
        
    ' 定义开始、结束单元格式位置
    Dim startCellAddress As String, endCellAddress As String
        
    endCellAddress = lookupColLetter + Str(endRowIndex)
    endCellAddress = Replace(endCellAddress, " ", "")
        
    ' 定义返回结果集合
    Dim resultCollection As Collection
    Set resultCollection = New Collection
      
    ' 定义查找到的单元格内容
    Dim resultCellValue As String
      
    Dim lastAdrress As String
    lastAdrress = ""
    Do
        cCount = cCount + 1
        startCellAddress = lookupColLetter + Str(startRowIndex)
        startCellAddress = Replace(startCellAddress, " ", "")
        
        Set newLookupRange = Range(startCellAddress + ":" + endCellAddress)
        ' Set matchcell = newLookupRange.Find(what:=lookup_value)
        Set matchcell = newLookupRange.Find(what:=lookup_value, LookAt:=xlWhole)
            
        If matchcell Is Nothing Or lastAdrress = matchcell.Address Then
            GoTo exitLoop
        End If
          
        lastAdrress = matchcell.Address
        resultCellValue = Cells(matchcell.Row, result_vector.Column)
        
        If removal Then
            ' 去重
            If Contains(resultCollection, resultCellValue) = False Then
                resultCollection.Add resultCellValue
            End If
        End If
          
        startRowIndex = matchcell.Row
    Loop While 1 = 1
        
exitLoop:
    Dim i As Long ' 遍历索引
    For i = 1 To resultCollection.Count
        If LISTAGG <> "" Then
            LISTAGG = LISTAGG + ch
        End If
            
        LISTAGG = LISTAGG + resultCollection.Item(i)
    Next i
        
End Function
  
Private Function Contains(coll As Collection, v As String) As Boolean
    Dim i As Long
      
    Contains = False
    For i = 1 To coll.Count
        If v = coll.Item(i) Then
            Contains = True
            GoTo exitfor
        End If
    Next i
exitfor:
      
End Function
    
' 获取列字母
Function ColLetter(ColNumber As Integer) As String
    On Error GoTo Errorhandler
    ColLetter = Left(Cells(1, ColNumber).Address(0, 0), 1 - (ColNumber > 26))
    Exit Function
    
Errorhandler:
    MsgBox "Error encountered, please re‐enter "
End Function

配置环境变量%CXF_HOME%=D:/ebook/cxf/apache-cxf-2.7.13
PATH后加上;%CXF_HOME%/bin

也可直接CD到bin目录

示例：
wsdl2java -encoding UTF-8 -p com.cignacmb.member.service.wsclient.eservice -frontend jaxws21 http://10.140.5.69/eservice/services/customerlogService?wsdl

wsdl2java用法：
wsdl2java -p com -d src -all  aa.wsdl
-encoding 指定生成代码的编码格式
-p  指定其wsdl的命名空间，也就是要生成代码的包名:
-d  指定要产生代码所在目录
-client 生成客户端测试web service的代码
-server 生成服务器启动web  service的代码
-impl 生成web service的实现代码
-ant  生成build.xml文件
-all 生成所有开始端点代码：types,service proxy,,service interface, server mainline, client mainline, implementation object, and an Ant build.xml file.

-- 查询用户表下所有指定排序规则的表名及其字段清单：
select b.name, a.*
  from syscolumns a, SysObjects b
 where a.id = b.id
   and b.xtype = 'U'
   and a.collation = 'Chinese_PRC_CI_AS'
 order by b.name;

-- 查询所有表名、字段名、类型、长度
select o.name, c.name, t.name, c.length
  from syscolumns c
 inner join systypes t
    on c.xtype = t.xtype
 inner join sysobjects o
    on c.id = o.id
 where o.xtype = 'u'
 order by o.name, c.name, t.name;

-- 修改指定字段排序规则：
ALTER TABLE individual ALTER COLUMN c_ssn nvarchar(255) COLLATE Latin1_General_CI_AS;


-- 批量更新字段排序规则
-- 更新时需指定字段类型，查询条件和生成的脚本需对应上
select o.name,
       c.name,
       t.name,
       c.length,
       c.collation,
       'ALTER TABLE ' + o.name + ' ALTER COLUMN ' + c.name + ' nvarchar(' +
       cast(c.length as varchar) + ') COLLATE Latin1_General_CI_AS;' as tSQL
  from syscolumns c
 inner join systypes t      on c.xtype = t.xtype
 inner join sysobjects o    on c.id = o.id
 where o.xtype = 'u'
   and c.collation = 'Chinese_PRC_CI_AS'
   and t.name = 'nvarchar'
 order by o.name, c.name, t.name;

Option Explicit

Dim SQLSheetNameStartWith As String
Dim VOUCHERSheetName As String

Private Sub init()
    SQLSheetNameStartWith = "【SQL】"
    VOUCHERSheetName = "【SQL】VOUCHER_DETAIL"
    
End Sub

Sub benefit()
'
' benefit Macro
' 我的权益
'

'
' insert into member_voucher (VOUCHER_ID, VOUCHER_NAME, VOUCHER_START_DATE, VOUCHER_END_DATE, VOUCHER_DESCRIPTION, VOUCHER_DETAIL, VOUCHER_AMOUNT, VOUCHER_RECEIPT_AMOUNT, OPERATOR, MAKEDATE)
' values (12, '携程10012元现金卷_12', to_date('06-01-2015', 'dd-mm-yyyy'), to_date('31-01-2015', 'dd-mm-yyyy'), '携程10012元现金卷_12_携程10012元现金卷_12_携程10012元现金卷_12', '携程10012元现金卷_12_携程10012元现金卷_12_携程10012元现金卷_12_携程10012元现金卷_12_携程10012元现金卷_12_携程10012元现金卷_12_携程10012元现金卷_12_携程10012元现金卷_12_携程10012元现金卷_12_携程10012元现金卷_12_携程10012元现金卷_12_携程10012元现金卷_12_携程10012元现金卷_12_携程10012元现金卷_12_携程10012元现金卷_12', 7, 4, 0, to_date('06-01-2015 09:27:25', 'dd-mm-yyyy hh24:mi:ss'));

    Call init
    
    Dim i As Integer, index As Integer
    Dim sqlSheetName As String
    
    ' member_voucher fields
    Dim VOUCHER_ID As String
    Dim VOUCHER_NAME As String
    Dim VOUCHER_START_DATE As String
    Dim VOUCHER_END_DATE As String
    Dim VOUCHER_DESCRIPTION As String
    Dim VOUCHER_DETAIL As String
    Dim VOUCHER_AMOUNT As String
    Dim VOUCHER_RECEIPT_AMOUNT As String
    Dim OPERATOR As String
    Dim MAKEDATE As String
    
    Dim localSheets As Sheets
    Set localSheets = ActiveWorkbook.Worksheets
    
    For i = 1 To localSheets.Count
        
        'Call alert(Format(Date, "yyyy/mm/dd hh:mm:ss.000"))

        VOUCHER_RECEIPT_AMOUNT = "0"
        OPERATOR = "0"
        MAKEDATE = "sysdate"
        
        If InStr(1, localSheets(i).Name, SQLSheetNameStartWith) = 0 Then
            ' 不是SQL sheet时，才生成对应的SQL sheet
            sqlSheetName = SQLSheetNameStartWith + localSheets(i).Name
            localSheets.Add(after:=localSheets(localSheets.Count)).Name = sqlSheetName
            ' 激活新建的工作表
            ActiveWorkbook.Worksheets(sqlSheetName).Select
            
            VOUCHER_ID = localSheets(i).Cells(2, 2).Value
            VOUCHER_NAME = localSheets(i).Cells(2, 3).Value
            
            VOUCHER_START_DATE = localSheets(i).Cells(2, 6).Value
            index = InStr(VOUCHER_START_DATE, "至")
            VOUCHER_END_DATE = Mid(VOUCHER_START_DATE, index + 1, Len(VOUCHER_START_DATE))
            VOUCHER_START_DATE = Mid(VOUCHER_START_DATE, 1, index - 1)
            
            VOUCHER_DESCRIPTION = ActiveWorkbook.Worksheets(VOUCHERSheetName).Cells(i + 1, 1)
            VOUCHER_DETAIL = ActiveWorkbook.Worksheets(VOUCHERSheetName).Cells(i + 1, 2)
            
            VOUCHER_AMOUNT = localSheets(i).[A65536].End(xlUp).Row - 1
            
            Dim sql As String
            sql = "insert into member_voucher (VOUCHER_ID, VOUCHER_NAME, VOUCHER_START_DATE, VOUCHER_END_DATE, VOUCHER_DESCRIPTION, VOUCHER_DETAIL, VOUCHER_AMOUNT, VOUCHER_RECEIPT_AMOUNT, OPERATOR, MAKEDATE)"
            sql = sql + "values ("
            sql = sql + VOUCHER_ID + ", "
            sql = sql + "'" + VOUCHER_NAME + "', "
            sql = sql + "date '" + VOUCHER_START_DATE + "', "
            sql = sql + "date '" + VOUCHER_END_DATE + "', "
            sql = sql + "'" + VOUCHER_DESCRIPTION + "', "
            sql = sql + "'" + VOUCHER_DETAIL + "', "
            sql = sql + VOUCHER_AMOUNT + ", "
            sql = sql + VOUCHER_RECEIPT_AMOUNT + ", "
            sql = sql + "'" + OPERATOR + "', "
            sql = sql + MAKEDATE
            sql = sql + ");"
            ActiveWorkbook.Worksheets(sqlSheetName).Cells(1, 1) = sql
            
            For index = 2 To localSheets(i).[A65536].End(xlUp).Row
                ' insert into member_voucher_detail (VOUCHER_ID, VOUCHER_CODE, MAKEDATE)
                ' values (10, '05830cd3b71547b3908d7356422ec430', to_date('06-01-2015', 'dd-mm-yyyy'))
                Dim VOUCHER_CODE As String
                VOUCHER_CODE = localSheets(i).Cells(index, 8).Value
                
                sql = "insert into member_voucher_detail (VOUCHER_ID, VOUCHER_CODE, MAKEDATE)"
                sql = sql + "values ("
                sql = sql + VOUCHER_ID + ", "
                sql = sql + "'" + VOUCHER_CODE + "', "
                sql = sql + "sysdate"
                sql = sql + ");"
                ActiveWorkbook.Worksheets(sqlSheetName).Cells(index, 1) = sql
            Next index
            
        End If
        
    Next
    
    Call alert("完成。。。")

End Sub

Private Sub alert(msg As String)
    MsgBox msg, 0, "title"
End Sub

// http://blog.csdn.net/qwlovedzm/article/details/6676525
// EasyUI 验证框使用方法:
/*****************************************************
missingMessage：未填写时显示的信息
validType：验证类型见下示例
invalidMessage：无效的数据类型时显示的信息
required="true" 必填项
class="easyui-validatebox" 文本验证
class="easyui-numberbox" 数字验证
*****************************************************/

// validator返回false时给出提示，如：
<input class="easyui-textbox" data-options="required:true,validType:['isBlank']">
// 若有多个校验时，可用数组表示，如：validType:['email','length[0,20]']


/**
 * validatebox扩展
 */
$.extend($.fn.validatebox.defaults.rules, {
	isInteger : {
		validator: function (value, param) {
            return /^\d+$/.test(value);
        },
        message: '请输入整数'
	},

	//增加去掉文本输入框前后空格扩展校验
	minLength: {
	    validator: function(value, param){
	        return $.trim(value).length >= param[0];
	    },
	    message: '该输入项为必输项'
	},
	
	length: {
	    validator: function(value, param){
	        var len=$.trim(value).length; 
	        return len>=param[0]&&len<=param[1]; 
	    },
        message:"输入长度必须介于{0}和{1}." 
	},
	
	isPositiveInteger : {
		validator: function (value, param) {
            return /^[1-9]\d*$/.test(value);
        },
        message: '请输入正整数'
	},
	
	numInteger: {
	    validator: function(value, param){
	        var len=$.trim(value).length; 
	        return len>=param[0]&&len<=param[1]; 
	    },
        message:"必须为{0}位整数." 
	},
	betweenValue: {
	    validator: function(value, param){
	        return value>=param[0]&&value<=param[1]; 
	    },
        message:"最大记录数只能为{0}至{1}的整数." 
	},
	emails: {
		validator: function(value, param){
			if(null == value || value == undefined || "" == value.trim() || "" == value){
				return true;
			}
			var reg = /^([a-zA-Z0-9]+[_|\_|\.]?)*[a-zA-Z0-9]+@([a-zA-Z0-9]+[_|\_|\.]?)*[a-zA-Z0-9]+\.[a-zA-Z]{2,3}$/;
			var mail = value.split(",");
			var valid = true;
			for(var i=0 ;i < mail.length;i++){
				if("" != mail[i] ){
					valid=reg.test(mail[i]);
					if(! valid){
						break ;
					}
				}
			}
			return valid;
	    },
        message:"请输入有效电子邮件,多邮箱请用英文“,”隔开" 
	}

});

/**
 * 自定义datagrid编辑器
 * LG mis中的range编辑框：在同一单元格中显示两个文本框录入
 */
$.extend($.fn.datagrid.defaults.editors, {
	rangeEditor : {
		init : function(container, options) {
			var $editorContainer = $('<div/>');
			var $input = $('<input class="easyui-validatebox" type="text">').width(30);
			$editorContainer.append($input.clone()).append($("<span> - </span>")).append($input.clone());
			$editorContainer.appendTo(container);
			$editorContainer.find("input").validatebox({
				required: true,
				validType: "isInteger"
			});
			return $editorContainer;
		},
		getValue : function(target) {
			var inputArray = $(target).find("input");
			return $(inputArray[0]).val() + "-" + $(inputArray[1]).val();
		},
		setValue : function(target, value) {
			var inputArray = $(target).find("input");
			if(null == value || $.trim(value) == ""){
				$(inputArray[0]).val("");
				$(inputArray[1]).val("");
			}else{
				var index = value.indexOf("-");
				if(index < 0){
					$(inputArray[0]).val(value);
					$(inputArray[1]).val("");
				}else{
					$(inputArray[0]).val(value.substring(0, index));
					$(inputArray[1]).val(value.substring(index + 1));
				}
			}
		},
		resize : function(target, width) {
			if ($.boxModel == true) {
				$(target).width(width - ($(target).outerWidth() - $(target).width()) - 10);
			} else {
				$(target).width(width - 10);
			}
		}
	}
});

参考Oracle的列转行函数（LISTAGG()）命名；功能类似；
版本1：
/**
 * rng As Range：选中的区域
 * Optional ch As String = ","：合并后的分割符，可自定义，默认为英文逗号（”,”）
 */
Public Function LISTAGG(rng As Range, Optional ch As String = ",")
    Dim i As Long
    Dim rows As Long
    rows = rng.rows.Count '选定区域总行数
    
    Set DataRange = rng.CurrentRegion
    For i = 1 To rows
        If i > 1 Then
            LISTAGG = LISTAGG + ch
        End If
        LISTAGG = LISTAGG + DataRange(i, 1)
    Next i
End Function

调用示例
=listagg(C3:C20)，按英文逗号合并C3至C20间的内容；
=listagg(C3:C20, " ")，按指定的空格合并C3至C20间的内容；

================================================================================
版本2：
参照LOOKUP和VLOOKUP参数设置；
' 参考lookup参数设置
' lookup_value 第一个矢量中搜索到的值
' lookup_vector 是一个仅包含一列的区域
' result_vector 是一个仅包含一列的区域
' Optional ch As String = ","：合并后的分割符，可自定义，默认为英文逗号（”,”）
' 【注意项】
' 1、最好指定明确的区域，如H2:H235，若指定为H:H，则效率会很慢
Public Function LISTAGG(lookup_value As String, lookup_vector As Range, Optional result_vector As Range, Optional ch As String = ",")
    
    Dim lookup_vector_len As Long, result_vector_len As Long
    lookup_vector_len = lookup_vector.rows.Count '查询区域总行数
    result_vector_len = result_vector.rows.Count '结果区域总行数
    
    Dim i As Long
    'Set lookDataRange = lookup_vector.CurrentRegion
    'Set resultDataRange = result_vector.CurrentRegion
    
    LISTAGG = ""
    ' 方法一
    'For i = 1 To lookup_vector_len
    '    If lookup_value = Cells(lookup_vector.Row + i - 1, lookup_vector.Column) Then
    '        If LISTAGG <> "" Then
    '            LISTAGG = LISTAGG + ch
    '        End If
    '        LISTAGG = LISTAGG + Cells(result_vector.Row + i - 1, result_vector.Column)
    '    End If
    'Next i

    ' 方法二，此方法本质还是Range
    'For Each c In lookDataRange
    '    If lookup_value = c.Value Then
    '        If LISTAGG <> "" Then
    '            LISTAGG = LISTAGG + ch
    '        End If
    '        LISTAGG = LISTAGG + Cells(result_vector.Row + c.Row - 1, result_vector.Column)
    '    End If
    'Next c
    
    ' 方法三
    Dim lookupColLetter As String
    lookupColLetter = ColLetter(lookup_vector.Column)
     
    Dim startRowIndex As Long, endRowIndex As Long
    startRowIndex = lookup_vector.Row
    endRowIndex = lookup_vector.Rows.Count
    
    Dim startCellAddress As String, endCellAddress As String
    
    endCellAddress = lookupColLetter + Str(endRowIndex)
    endCellAddress = Replace(endCellAddress, " ", "")
    
    Do
        startCellAddress = lookupColLetter + Str(startRowIndex)
        startCellAddress = Replace(startCellAddress, " ", "")
    
        Set newLookupRange = Range(startCellAddress + ":" + endCellAddress)
        Set matchcell = newLookupRange.Find(what:=lookup_value)
        
        If matchcell Is Nothing Then
            GoTo exitLoop
        End If
        
        If LISTAGG <> "" Then
            LISTAGG = LISTAGG + ch
        End If
        
        LISTAGG = LISTAGG + Cells(matchcell.Row, result_vector.Column)
        startRowIndex = matchcell.Row + 1
    Loop While 1 = 1
    
exitLoop:
    
    If LISTAGG = "" Then
        LISTAGG = "test 123456"
    End If
End Function

' 获取列字母
Function ColLetter(ColNumber As Integer) As String
    On Error GoTo Errorhandler
    ColLetter = Left(Cells(1, ColNumber).Address(0, 0), 1 - (ColNumber > 26))
    Exit Function

Errorhandler:
    MsgBox "Error encountered, please re‐enter "
End Function

【问题】
http://blog.sina.com.cn/s/blog_6b8f217e0100uv0q.html
FindNext有个致命的Bug，它只能用于Sub过程里面，不能用于Function过程里面。
后来我改为Sub过程试试，竟然调试通过。于是，我上网查询一下是否已经有人遇到类似问题，果然是FindNext函数的问题，不能用于Function。还有人说不能用于带参数的Sub过程，本人没有验证。
重要提示，我所说的不能用于Function，是如果用户直接在工作表中使用该自定义函数，那么FindNext不工作，返回错误值。如果在代码窗口，创建一个Sub，在Sub里面调用自定义函数，可以返回正确结果。

源码：
/**
 * DES加密解密
 * 2014-12-9  下午1:29:01
 */
public class DESUtils {
	
	private static Logger logger = Logger.getLogger(DESUtils.class);

	private static final String DES_KEY = "badc";
	
	private static Key key = null;
	private static Cipher cipher;
	
	static {
		try {
			KeyGenerator _generator = KeyGenerator.getInstance("DES");
			SecureRandom secureRandom = SecureRandom.getInstance("SHA1PRNG" );
			secureRandom.setSeed(DES_KEY.getBytes());
			_generator.init(secureRandom);
			
			key = _generator.generateKey();  
	        
	        cipher = Cipher.getInstance("DES");  
		} catch (NoSuchAlgorithmException e) {
			logger.error("DESUtils NoSuchAlgorithmException", e);
		} catch (NoSuchPaddingException e) {
			logger.error("DESUtils NoSuchPaddingException", e);
		}
	}
	
	/**
	 * @param text
	 * @return
	 * @throws BusinessException  
	 */
	public static String encrypt(String text) throws BusinessException {
		try {
			cipher.init(Cipher.ENCRYPT_MODE,key);
			byte[] byteFina = cipher.doFinal(text.getBytes());
			
			return null == byteFina ? null : new Base64().encodeAsString(byteFina);
		} catch (InvalidKeyException e) {
			throw new BusinessException(e);
		} catch (IllegalBlockSizeException e) {
			throw new BusinessException(e);
		} catch (BadPaddingException e) {
			throw new BusinessException(e);
		}
	}
	
	/**
	 * @param text
	 * @return
	 * @throws BusinessException
	  */
	public static String decrypt(String text) throws BusinessException {
		try {
			cipher.init(Cipher.DECRYPT_MODE,key);
			byte[] byteMi = new Base64().decode(text);
			byte[] byteMing = cipher.doFinal(byteMi);
			return null == byteMing ? null : new String(byteMing);
		} catch (InvalidKeyException e) {
			throw new BusinessException(e);
		} catch (IllegalBlockSizeException e) {
			throw new BusinessException(e);
		} catch (BadPaddingException e) {
			throw new BusinessException(e);
		}
	}

}

maven依赖（用apache的Base64替换sun jdk里的）：
		 <dependency>
		 	<groupId>commons-codec</groupId>
		 	<artifactId>commons-codec</artifactId>
		 	<version>1.10</version>
		 </dependency>

测试代码：
public class DESUtilsTest extends TestCase {

	public void testEncrypt() throws BusinessException {
		String str = "fdaeaf554629";

		long start = System.currentTimeMillis();
		String mi = DESUtils.encrypt(str);
		System.out.println("mi = " + mi);
		long end = System.currentTimeMillis();
		System.out.println("@> dif = " + (end - start));
		
		System.out.println("ming = " + DESUtils.decrypt(mi));
		System.out.println("@> dif = " + (System.currentTimeMillis() - end));
	}
}

$.extend({
	/**
	 * 判断是否String对象
	 */
	isString: function(str){
		return (typeof str=='string')&&str.constructor==String;
	},
	/**
	 * 判断是否JSON对象
	 */
	isJSON: function(obj){
		var isjson = typeof(obj) == "object" && Object.prototype.toString.call(obj).toLowerCase() == "[object object]" && !obj.length;    
		return isjson;
	},
	// JQuery type
	isJQuery : function(obj){
		return obj instanceof jQuery;
	},
	// positive integer
	isInteger : function(value){
		// /^(\+?)(\-?)(\d+)$/
		if(/^[1-9]+[0-9]*]*$/.test($.trim(value))) return true;
		else return false;
	},
	// Date Type
	isDate : function(date){
		return date instanceof Date;
	}
});

$.extend({
	/**
	 * 将URL中的参数转换成JSON对象
	 */
	urlJSON:function(){
		var paramters = window.location.search.substr(1);
		if(null != paramters && undefined != paramters && '' != paramters){
			paramters = paramters.replace(/&/g,"\",\"");
			paramters = paramters.replace(/=/g,"\":\"");
			paramters = '{"'+paramters+'"}';
		    return eval("("+paramters+")");
		}
		return {};
		
	},
	/**
	 * 千分位显示数据，小数部分不做千分位显示
	 * num：原数据
	 * decimal：四舍五入为指定小数位数的数字
	 */
	formatThousandths: function(num, decimal){
		if(null == num ||""== num) return "";
		if($.isString(num)) num = parseFloat(num);
		if(null == decimal || decimal == undefined || decimal < 0){
			decimal = 0;
		}
		num = num.toFixed(decimal) + '';
		var array = num.split(".");
		switch (array.length) {
		case 1:
			return num.replace(/\d{1,3}(?=(\d{3})+(\.\d*)?$)/g, '$&,');
		case 2:
			return array[0].replace(/\d{1,3}(?=(\d{3})+(\.\d*)?$)/g, '$&,') + "." + array[1];
		default:
			return num;
			break;
		}
	}
});

$(document).ready(function(){
	/**
	 * 在数组中搜索指定的值。 indexOf返回第一个匹配项的索引；如果找不到指定的值，则为 -1。
	 * array1.indexOf(searchElement[, fromIndex])，fromIndex 可选。 用于开始搜索的数组索引。 如果省略 fromIndex，则从索引 0 处开始搜索。
	 * https://msdn.microsoft.com/zh-cn/library/ie/ff679977(v=vs.94).aspx
	 * 但低版本浏览器，如IE6、IE7等，Array不支持indexOf方法，需扩展。此处的扩展不支持fromIndex设置
	 */
	if(!Array.prototype.indexOf){
		Array.prototype.indexOf = function(val){
		   var value = this;
		   for(var i =0; i < value.length; i++){
		      if(value[i] == val) return i;
		   }
		   return -1;
		};
	}
});

/**
* 去掉字符两端的空白字符
* 功能同$.trim(string) 
*/
String.prototype.trim = function() {
	return this.replace(/^\s*(.*?)[\s\n]*$/g, '');
};

/** 
 * 判断是否手机号
 */
String.prototype.validMobile = function(){
	if(null == this || this == '') return false;
	var reg = /^((13)|(14)|(15)|(17)|(18))\d{9}$/;
	return reg.test(this);
};

String.prototype.validCompanyTel = function() {
	var reg = /^\d{3,5}-\d{7,9}|\d{3,5}-\d{7,9}-\d{1,6}$/;
	return reg.test(this);
};

String.prototype.validEmail = function() {
	var reg = /^(.)+@([a-zA-Z0-9_-])+((\.[a-zA-Z0-9_-]{2,3}){1,2})$/;
    return reg.test(this);
};

/**
* 反转字符串<br>
* eg: 'abc'.reverse() -> 'cba'
*/
String.prototype.reverse = function() {
	if (!this) return '';
	var a = (this + '').split('');
	a.reverse();
	return a.join('');
};

/**
* 返回替换后的字符串<br>
*/
String.prototype.replaceAll  = function(s1,s2){  
	return this.replace(new RegExp(s1,"gm"),s2);   //这里的gm是固定的，g可能表示global，m可能表示multiple。
};

/**
* 字符串转日期函数
* 支持格式：
* 1、xxxx-xx-xx xx:xx:xx
* 2、xxxx.xx.xx xx:xx:xx
* 3、xxxx年xx月xx日 xx时xx分xx秒
* 4、xxxx/xx/xx xx:xx:xx
*/
String.prototype.toDate = function(){
	var regExp1 = /^\d{4}-\d{1,2}-\d{1,2}( \d{1,2}:\d{1,2}:\d{1,2})?$/; 
	var regExp2 = /^\d{4}\.\d{1,2}\.\d{1,2}( \d{1,2}:\d{1,2}:\d{1,2})?$/; 
	var regExp3 = /^\d{4}年\d{1,2}月\d{1,2}日( \d{1,2}时\d{1,2}分\d{1,2}秒)?$/;
	var regExp4 = /^\d{4}[\/]\d{1,2}[\/]\d{1,2}( \d{1,2}:\d{1,2}:\d{1,2})?$/; 
	
	var dateString = this;
	if (regExp1.test(this)) {
	} else if (regExp2.test(this)) {
		dateString = this.replace(/\./g, "-");
	} else if (regExp3.test(this)) {
		dateString = this.replace("年", "-").replace("月", "-").replace("日", "").replace("时", ":").replace("分", ":").replace("秒", "");
	} else if (regExp4.test(this)){
		var temp_array = this.split("/");
		if(null != temp_array && temp_array.length > 0){
			var temp_str = '';
			for ( var int = 0; int < temp_array.length - 1; int++) {
				temp_str = temp_str + temp_array[int] + '-';
			}
			temp_str = temp_str + temp_array[temp_array.length - 1];
			dateString = temp_str;
		}
	}else{
		var msg = '字符串格式不支持转换为日期对像';
		alert(msg);
		throw msg;
	}
	var date_time = dateString.split(" ");
	var date_part = date_time[0].split("-");
	var time_part = (date_time.length > 1 ? date_time[1].split(":") : "");
	if (time_part == "") {
		return new Date(date_part[0], date_part[1] - 1, date_part[2]);
	} else {
		return new Date(date_part[0], date_part[1] - 1, date_part[2],
				time_part[0], time_part[1], time_part[2]);
	}
};

/** 
 * 字符串true or false转boolean型，其他字符串返回原值
 * 不区分大小写
 */  
String.prototype.convertToBoolean = function() {  
	// (v.toLocaleLowerCase() == "true" || v.toLocaleLowerCase() == "false") ? v.toLocaleLowerCase() == "true" : v;
    return (this.toLocaleLowerCase() == "true" || this.toLocaleLowerCase() == "false") ? this.toLocaleLowerCase() == "true" : this;
}; 

Date.prototype.addDays = function(d) {
	this.setDate(this.getDate() + d);
	return this;
};
Date.prototype.addWeeks = function(w) {
	this.addDays(w * 7);
	return this;
};
Date.prototype.addMonths = function(m) {
	var d = this.getDate();
	this.setMonth(this.getMonth() + m);
	if (this.getDate() < d)
		this.setDate(0);
	return this;
};
Date.prototype.addYears = function(y) {
	var m = this.getMonth();
	this.setFullYear(this.getFullYear() + y);
	if (m < this.getMonth()) {
		this.setDate(0);
	}
	return this;
};
/** 日期月份的第一天 */
Date.prototype.firstDate = function(){
	this.setDate(1);
	return this;
};
/** 日期月份的最后一天 */
Date.prototype.lastDate = function(){
	this.setMonth(this.getMonth() + 1);
	this.setDate(0);
	return this;
};

/**
* 日期间隔
* 默认与当前日期计算日差
* endTime - this
* interval可取值：
* s - 秒
* m - 分
* h - 时
* d - 日
* w - 周
* M - 月
* y - 年
*/
Date.prototype.diff = function(endTime, interval) {
	if(null == interval || interval == undefined) return this.dateDiff(endTime, 'd');
	if(null == endTime || endTime == undefined) return this.dateDiff(new Date(), interval);
	// 只有一个参数，且为间隔单位
	if(endTime instanceof Date == false) return this.dateDiff(new Date(), endTime);
	switch (interval) {
	case "s": // 计算秒差
		return parseInt((endTime - this) / 1000);
	case "m": // 计算分差
		return parseInt((endTime - this) / 60000);
	case "h": // 计算时差
		return parseInt((endTime - this) / 3600000);
	case "d": // 计算日差
		return parseInt((endTime - this) / 86400000);
	case "w": // 计算周差
		return parseInt((endTime - this) / (86400000 * 7));
	case "M": // 计算月差
		return (endTime.getMonth() + 1)
				+ ((endTime.getFullYear() - this.getFullYear()) * 12)
				- (this.getMonth() + 1);
	case "y": // 计算年差
		return endTime.getFullYear() - this.getFullYear();
	default: // 输入有誤
		return undefined;
	}
};

/**
 * 对Date的扩展，将 Date 转化为指定格式的String
 * 月(M)、日(d)、12小时(h)、24小时(H)、分(m)、秒(s)、周(E)、季度(q) 可以用 1-2 个占位符
 * 年(y)可以用 1-4 个占位符，毫秒(S)只能用 1 个占位符(是 1-3 位的数字)
 * eg:
 * (new Date()).format("yyyy-MM-dd hh:mm:ss.S") ==> 2006-07-02 08:09:04.423
 * (new Date()).format("yyyy-MM-dd E HH:mm:ss") ==> 2009-03-10 二 20:09:04
 * (new Date()).format("yyyy-MM-dd EE hh:mm:ss") ==> 2009-03-10 周二 08:09:04
 * (new Date()).format("yyyy-MM-dd EEE hh:mm:ss") ==> 2009-03-10 星期二 08:09:04
 * (new Date()).format("yyyy-M-d h:m:s.S") ==> 2006-7-2 8:9:4.18
 */
Date.prototype.format=function(fmt) {
	if(null == fmt || fmt == undefined){
		fmt = "yyyy-MM-dd";
	}
	var o = {
		"M+" : this.getMonth() + 1, // 月份
		"d+" : this.getDate(), // 日
		"h+" : this.getHours() % 12 == 0 ? 12 : this.getHours() % 12, // 小时
		"H+" : this.getHours(), // 小时
		"m+" : this.getMinutes(), // 分
		"s+" : this.getSeconds(), // 秒
		"q+" : Math.floor((this.getMonth() + 3) / 3), // 季度
		"S" : this.getMilliseconds() // 毫秒
	};           
	var week = {
		"0" : "/u65e5",
		"1" : "/u4e00",
		"2" : "/u4e8c",
		"3" : "/u4e09",
		"4" : "/u56db",
		"5" : "/u4e94",
		"6" : "/u516d"
	};           
    if(/(y+)/.test(fmt)){
        fmt=fmt.replace(RegExp.$1, (this.getFullYear()+"").substr(4 - RegExp.$1.length));
    }
    if(/(E+)/.test(fmt)){
        fmt=fmt.replace(RegExp.$1, ((RegExp.$1.length>1) ? (RegExp.$1.length>2 ? "/u661f/u671f" : "/u5468") : "")+week[this.getDay()+""]);
    }
    for(var k in o){
        if(new RegExp("("+ k +")").test(fmt)){
            fmt = fmt.replace(RegExp.$1, (RegExp.$1.length==1) ? (o[k]) : (("00"+ o[k]).substr((""+ o[k]).length)));
        }
    }
    return fmt;
};