WORD表格数据填充的宏（VBA）

Sub PasteToCells()
'
' PasteToCells 宏
'
    Dim TargetRange As Range
    Dim oTargCell As Cell

    If Selection.Cells.Count = 0 Then
        'Quit if no cells in selection
        MsgBox "No cells selected", vbCritical
        Exit Sub
    End If
    On Error Resume Next
    Set TargetRange = Selection.Range
    For Each oTargCell In Selection.Cells
        oTargCell.Range.Paste
    Next oTargCell
    TargetRange.Select

    Selection.EscapeKey
    Selection.EscapeKey
   
End Sub

Sub PasteToCellsStart()
    Dim TargetRange As Range
    Dim oTargCell As Cell
    Dim PasteRange As Range

    If Selection.Cells.Count = 0 Then
        'Quit if no cells in selection
        MsgBox "No cells selected", vbCritical
        Exit Sub
    End If
    On Error Resume Next
    Set TargetRange = Selection.Range
    For Each oTargCell In Selection.Cells
        Set PasteRange = oTargCell.Range
        PasteRange.Collapse wdCollapseStart
        PasteRange.Paste
    Next oTargCell
    TargetRange.Select
End Sub

Sub PasteToCellsEnd()
    Dim TargetRange As Range
    Dim oTargCell As Cell
    Dim PasteRange As Range

    If Selection.Cells.Count = 0 Then
        'Quit if no cells in selection
        MsgBox "No cells selected", vbCritical
        Exit Sub
    End If
    On Error Resume Next
    Set TargetRange = Selection.Range
    For Each oTargCell In Selection.Cells
        Set PasteRange = oTargCell.Range.Characters.Last
        PasteRange.Collapse wdCollapseStart
        PasteRange.Paste
    Next oTargCell
    TargetRange.Select
End Sub

两个Excel宏，方便统计数据

第一个，将选中的单元格中的数据合并到第一个单元格中，并清除其他单元格

第二个，将选中的单元格中的数据相加，结果到第一个单元格中，并清除其他单元格

Sub LinkCells()
    Dim cell As Object
    Dim count As Long
    Dim str As String
    str = ""
    count = 0
    For Each cell In Selection
        If str = "" Then
            str = str & cell.Value
        Else
            str = str & "," & cell.Value
        End If
        cell.Value = ""
    Next cell
    
    Cells(Selection.Row, Selection.Column).Value = str
End Sub

Sub AddCells()
    Dim cell As Object
    Dim count As Long
    Dim str As Integer
    str = 0
    count = 0
    For Each cell In Selection
       str = str + CInt(cell.Value)
       cell.Value = ""
    Next cell
    
    Cells(Selection.Row, Selection.Column).Value = str
End Sub

mysql支持多表联合更新，真是方便

 

update student,statistic set student.score1=statistic.score1 where student.paperid=statistic.paperid and student.score1 is NULL

用存储过程检查用户登录

BEGIN
    DECLARE studentName,iip VARCHAR(16);
    DECLARE iContestId,LoginFlag,iPaperId,iMinutes INT;
    DECLARE stime datetime;
    DECLARE ntime datetime DEFAULT NOW();
    DECLARE bGameOver bit;

    select starttime,minutes into stime,iMinutes from contests where current=1;
    if stime-ntime>300 THEN
        SELECT '离考试开始时间超过5分钟，不允许用户登录！' as msg;
    else
        select stuname,contests.testid,student.login,student.starttime,paperid,gameover,ipadd into studentName,iContestId,LoginFlag,stime,iPaperId,bGameOver,iip from student,contests where student.stuid=studentid and student.password=passwd and student.contestid=contests.testid and contests.current=1;
        if (iContestId is null) THEN
            select '用户不存在或者密码错误！' as msg;
        ELSEIF bGameOver THEN
            SELECT '您已结束考试，不能再次进入考试！' as msg;
        ELSEIF LoginFlag=1 and ip<>iip THEN
            SELECT '用户已经登录考试，不能重复登录！' as msg;
        ELSEIF iPaperId is null THEN
            SELECT '试卷不存在!' as msg;
        ELSEIF stime is null THEN
            #改变用户登录标志为已登录
            #记录该用户的考试开始时间
            update student set login=1,starttime=ntime,ipadd=ip where student.stuid=studentid and contestid=iContestId;
            #返回登录成功信息，学生姓名，考试编号，试卷编号，考试开始时间，考试时长等信息
            select 'success' as msg,studentName as sname,iContestId as testid,iPaperId as paperid,ntime as starttime,iMinutes as minutes;
        ELSE
            #改变用户登录标志为已登录
            #考生换机，扣除已使用时间，开始考试时间不变
            update student set login=1,ipadd=ip where student.stuid=studentid and contestid=iContestId;
            #返回登录成功信息，学生姓名，考试编号，试卷编号，考试开始时间，剩余考试时长等信息
            select 'success' as msg,studentName as sname,iContestId as testid,iPaperId as paperid,stime as starttime,iMinutes as minutes;
        END IF;
    END IF;
END

 

小插曲：

一开始的时候存储过程写错了，红色部分写成了student.stuid=stuid ,结果记录的总是最后一个人的登录IP和登录时间，然后当用户重新登录时，因为IP不一样，总是登录不到考试系统中去

计算考试剩余时间

        String start = (String) session.getAttribute("StartTime");
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date date = null;
        try {
            date = format.parse(start);
        } catch (ParseException e) {
            throw new ServletException(e);
        }

        Calendar c = Calendar.getInstance();

        long escape = (c.getTimeInMillis() - date.getTime()) / (1000 * 60);
        long remain = (Integer) session.getAttribute("Minutes") - escape;

mysql存储过程中的一些问题

 

1、必须给数据库用户super权限，否则无法执行存储过程。用root用户创建的存储过程，其他用户在调用时必须在数据库url上加上参数，比较完整的url如下：

jdbc:mysql://10.28.79.2:3306/cbbt?noAccessToProcedureBodies=true

2、在存储过程中的select中使用in子句，以及select返回多条记录的时候的处理方法

BEGIN
    DEClare qid,qtype,qscore int;
    DECLARE stopFlag int DEFAULT 0;
    DECLARE qtitle,qoa,qob,qoc,qod,qans varchar(1000);
    DECLARE qnum int DEFAULT 1;
   

#定义游标

    DECLARE Que_ResultSet CURSOR FOR select tid,title,optiona,optionb,optionc,optiond,type,answer,score,type from problems WHERE FIND_IN_SET(tid,ids) ORDER BY tid;
    DECLARE CONTINUE HANDLER FOR NOT FOUND set stopFlag=1;

    set stopFlag=0;

    OPEN Que_ResultSet;

    REPEAT
        FETCH Que_ResultSet INTO qid,qtitle,qoa,qob,qoc,qod,qtype,qans,qscore,qtype;
        IF NOT stopFlag then  #没有这行的话，最后一条记录会重复两次
            BEGIN
                if qtype=1 or qtype=2 THEN
                    insert into panswer(paperid,problemid,answer,score,type) values(pid,qnum,qans,qscore,qtype);
                ELSE
                    insert into panswer(paperid,problemid,answer,score,type) values(pid,qnum,qoa,qscore,qtype);
                end IF;
                set qnum=qnum+1;
            END;   
        end if;
    UNTIL stopFlag = 1 END REPEAT;

    CLOSE Que_ResultSet;

    select tid,title,optiona,optionb,optionc,optiond,type from problems WHERE FIND_IN_SET(tid,ids) ORDER BY tid;
END

在线考试系统

最近做了一个非常简单的在线考试系统：JSP+Servlet+mysql+ajax+存储过程，把一些遇到的问题发到这里记录一下

java中调用mysql存储过程

 

有参数的情况

CallableStatement cstmt=conn.prepareCall("{call hasContest(?)}");
cstmt.registerOutParameter(1, Types.BOOLEAN);
cstmt.execute();
boolean b=cstmt.getBoolean(1);

 

无参数的情况

CallableStatement cstmt=conn.prepareCall("{call hasContest}");
ResultSet rs=cstmt.executeQuery();
rs.next();
return rs.getBoolean(1);

 

给定的数据库用户必须具有执行的权限，同时在数据的url中要添加参数，如下所示

jdbc:mysql://10.28.79.2:3306/cbbt?noAccessToProcedureBodies=true