Java操作Jxl实现数据交互。三部曲——《第三篇》

2020-01-16 16:04:20来源：博客园阅读 ()

Java操作Jxl实现数据交互。三部曲——《第三篇》

Java操作Jxl实现上传文本文件实现转PDF格式在线预览。

本文实现背景Web项目：前台用的框架是Easyui+Bootstrap结合使用，需要引入相应的Js、Css文件。页面：Jsp、拦截请求：Servlet、逻辑处理：ClassBean、数据库：SQLserver。

注意：Bean中操作SQL语句进行处理是公司内部方法，可替换为其它方法自行扩展！主要看代码逻辑业务处理！

首先我们看见的是Easyui中的datagrid中的数网格。

接下来进行创建一个xsl文本格式数据文件

我们任意点击条数据，点击附件这是弹出一个窗口让我们进行附件上传，只能上传文本数据格式！

点击保存附件上传成功！该附件信息文件名称及文件路径以保存在数据库中，查询该人员的相关信息及附件信息，此时此刻可查看附件信息

点击查看及可把xls数据格式在线转码为PDF格式数据进行展示！(注：在线转码与预览需要一些本地配置，请关注我下一篇博客！)

jsp代码：

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<%@page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import="com.pantech.base.common.tools.MyTools"%>
<%@ page import="com.pantech.base.common.tools.PublicTools"%>
<%@ page import="com.pantech.src.develop.Logs.*"%>
<%@ page import="com.pantech.base.common.tools.*"%>
<%@ page import="com.pantech.src.develop.store.user.*"%>
<%@ page import="com.pantech.src.develop.manage.workremind.WorkRemind"%>
<%@ page import="java.util.Vector"%>
<%@ page import="java.util.*"%>
<%@ page import="java.text.*"%>
<%@ page import="com.pantech.base.common.db.DBSource"%>

<html>
<head>


    
    <title>导入文件转为FDF格式在线预览</title>
    <link rel="stylesheet" type="text/css" href="<%=request.getContextPath()%>/css/themes/icon.css">
    <link rel="stylesheet" type="text/css" href="<%=request.getContextPath()%>/css/themes/default/easyui.css">
    <link charset="utf-8" rel="stylesheet" href="<%=request.getContextPath()%>/css/bootstrap.css"/>
    <link charset="utf-8" rel="stylesheet" href="<%=request.getContextPath()%>/css/naber.css"/>
    <link charset="utf-8" rel="stylesheet" href="<%=request.getContextPath()%>/css/font-awesome/css/font-awesome.css">
    <link charset="utf-8" rel="stylesheet" href="<%=request.getContextPath()%>/css/SMS-index.css"/>
    <link charset="utf-8" rel="stylesheet" href="<%=request.getContextPath()%>/css/listPage.css"/>
    <script type="text/javascript" src="<%=request.getContextPath()%>/script/JQueryUI/jquery.min.js"></script>
    <script type="text/javascript" src="<%=request.getContextPath()%>/script/JQueryUI/jquery.easyui.min.js"></script>
    <script type="text/javascript" src="<%=request.getContextPath()%>/script/JQueryUI/locale/easyui-lang-zh_CN.js"></script>
    <script charset="utf-8" src="<%=request.getContextPath()%>/script/bootstrap.min.js"></script>
    <script charset="utf-8" src="<%=request.getContextPath()%>/script/layer/layer.js"></script>
    <script type="text/javascript" src="<%=request.getContextPath()%>/script/common/clientScript.js"></script> 
    <script type="text/javascript" src="<%=request.getContextPath()%>/script/common/publicScript.js"></script>
        <script type="text/javascript" src="<%=request.getContextPath()%>/script/ajaxfileupload.js"></script>
        <script type="text/javascript" src="<%=request.getContextPath()%>/script/common/publicScript.js"></script>
        <script type="text/javascript" src="<%=request.getContextPath()%>/script/FlexPaper/flexpaper_handlers.js"></script>
          <script type="text/javascript" src="<%=request.getContextPath()%>/script/FlexPaper/flexpaper.js"></script>
        <style type="text/css">
            #divPageMask2{background-color:#D2E0F2; filter:alpha(opacity=90);left:0px;top:0px;z-index:100;}
            #divPageMask3{background-color:#D2E0F2; filter:alpha(opacity=90);left:0px;top:0px;z-index:100;}
            #win td{height:30px;}
        </style>
    </head>
<body class="easyui-layout" >
    <!-- 页面学生列表区 -->
    <div id="tableDIV" class="list-table table-responsive" style="height:600px;width: 900px">
        <table id="list" width="100%"></table>
    </div>

    <!-- 上传附件弹窗 -->
    <div id=jxdialog style="overflow: hidden;">
        <div id="north" region="north" title="教学管理">
            <table id="tb">
                <tr>
                    <td><a href="#" id="newfjsc" class="easyui-linkbutton"plain="true" iconcls="icon-new" onClick="doToolbar(this.id);"title="">上传附件</a></td>
                    <td><a href="#" id="delfjxx" class="easyui-linkbutton"plain="true" iconcls="icon-cancel" onClick="doToolbar(this.id);"title="">删除</a></td>
                </tr>
            </table>
        </div>
        <div region="center">
            <table id="jxbzList"></table>
        </div>
    </div>


    <!-- 附件上传 -->
    <div id="jxbzInfo" style="overflow:hidden;">
        <form id="form1" method='post'>
            <table style="width:100%;" class="tablestyle">
                <tr>
                    <td class="titlestyle">附件上传</td>
                    <td>
                        <input type="file" name="file1" id="icJXBZ_UPLOAD" style="width:280px;" />
                    </td>
                </tr>
            </table>
            <div style="display: none;">
                <input type="hidden" id="active" name="active"/>
            </div> 
        </form>
    </div>


    <!-- 遮罩层 -->
    <div id="ic_flexPaperZhezhao" style="position:absolute; z-index:99999; width:100%; height:100%; background-image:url('<%=request.getContextPath()%>/images/course/pic5.png'); display:none;  text-align:center;" onclick="$('#ic_flexPaperDiv').hide();">
            <font color="white" style="position:relative; top:45%; left:0;">文件预览加载中<br/><img src="<%=request.getContextPath()%>/images/course/loading.gif" /></font>
    </div>
            
    <!-- 文件预览 flexpaper -->
    <div id="ic_flexPaperDiv" style="position:absolute; z-index:99999; width:100%; height:100%; background-image:url('<%=request.getContextPath()%>/images/course/pic5.png'); display:none;" onclick="$('#ic_flexPaperDiv').hide();">
        <div id="documentViewer" class="flexpaper_viewer" style="width:90%;height:90%; position:relative; top:5%; left:5%;">
        </div>
    </div>        
        
</body>
<script type="text/javascript">
    var XSBH='';
    var uploadPath='<%=MyTools.getProp(request, "Base.JYWDStandardUpLoadPath")%>';//上传路径
    var filePreview='<%=MyTools.getProp(request, "Base.JYWDStandardFilePreview")%>';//预览路径
    var iUSERCODE="<%=MyTools.getSessionUserCode(request)%>";   //获得用户登录code    

    $(document).ready(function(){
        loadGrid();
        loadDialog();
    });
    
    
    //dialog窗口
    function loadDialog() {
        //上传附件弹窗
        $('#jxbzInfo').dialog({   
            width: 480,//宽度设置   
            height: 120,//高度设置 
            modal:true,
            closed: true,   
            cache: false, 
            draggable:false,//是否可移动dialog框设置
            toolbar:[{
                //保存编辑
                text:'保存',
                iconCls:'icon-save',
                handler:function(){
                    //传入save值进入doToolbar方法，用于保存
                    doToolbar('savefjxx');
                }
            }],
            //打开事件
            onOpen:function(data){},
            //读取事件
            onLoad:function(data){},
            //关闭事件
            onClose:function(data){
                //上传附件表单元素数据
                var file = $('#icJXBZ_UPLOAD');
                file.after(file.clone().val(""));
                file.remove(); 
                jxloadGrid(Maindata[0].学生编号);  //调用读取附件页面信息
                
            }
        });
        
        //上传教学附件
        $('#jxdialog').dialog({   
            width: 800,//宽度设置   
            title:'教学管理计划',
            height: 480,//高度设置 
            modal:true,
            closed: true,  
            cache: false, 
            draggable:false,//是否可移动dialog框设置
            //打开事件
            onOpen:function(data){},
            //读取事件
            onLoad:function(data){},
            //关闭事件
            onClose:function(data){
                iKeyCode=XSBH;                
            }
        });
        
    }


    //上传附件信息    
    function jxloadGrid(zy) {
        XSBH=zy;
        loadGridfjsc(zy);
        $('#jxdialog').dialog('open');
    }



    //工具按钮
    function doToolbar(iToolbar){
        //判断获取参数为new，执行新建附件操作
        if(iToolbar == 'newfjsc'){
            $('#jxbzInfo').dialog('setTitle', '新建教学附件信息');
            $('#jxbzInfo').dialog('open');        //打开dialog
        }
    
        //执行保存附件信息操作
        if(iToolbar == 'savefjxx'){ 
            if($('#icJXBZ_UPLOAD').val()==''){
                alertMsg("请选择上传文件");
                return;
            }
        
            var fileTypePhoto='<%=MyTools.getProp(request, "[FileTypePhoto]")%>';  //图片
            var fileTypeTxt='<%=MyTools.getProp(request, "[FileTypeTxt]")%>';    //文本
            var fileSuffix=$("#icJXBZ_UPLOAD").val().split('.')[$("#icJXBZ_UPLOAD").val().split('.').length-1]; //获取文件后缀名
        
             if (fileTypeTxt.indexOf(fileSuffix)!=-1) {
                checkFile();//新建
            }else {
                alertMsg("只能上传文本格式的文件");
                return;
            }
            
        }

        //删除附件信息操作
        if(iToolbar=="delfjxx"){
            if(iKeyCode==""){
                alertMsg("请先选择一行数据");
                return;
             }
             ConfirmMsg("删除后，将无法恢复，是否继续？","delRec();","");
        }
        
        
    }

    
    //删除附件信息方法
    function delRec(){
        $.ajax({
        type:'post',
        url:"<%=request.getContextPath()%>/ImportTextBean_Servlet",
        data:"active=deleteRow&FJXXBH=" + iKeyCode,
        dataType:'json',
            success:function(datas){
                if(datas[0].msg=="删除成功"){
                    showMsg(datas[0].msg);
                     jxloadGrid(XSBH);  //调用读取附件页面信息
                }
                
            }
        });   
    }



//加载datagrid主页面信息
function loadGrid(){
    $('#list').datagrid({
        url: '<%=request.getContextPath()%>/ImportTextBean_Servlet',
        queryParams: {"active":"loadElective"},
        loadMsg : "信息加载中请稍后!",//载入时信息
        nowrap: false,//当数据长度超出列宽时将会自动截取
        showFooter:true,
        rownumbers: true,
        animate:true,
        striped : true,//隔行变色
        pageSize : 10,//每页记录数
        singleSelect : true,//单选模式
        pageNumber : 20,//当前页码
        pagination:true,
        fit:true,
        fitColumns: true,//设置边距
        columns:[[
            {field:'姓名',title:'姓名',width:fillsize(0.2)},
            {field:'所在区域',title:'所在区域',width:fillsize(0.2)},
            {field:'col3',title:'附件',width:fillsize(0.1),
                formatter:function(value,rec,index){
                    return "<input type='button' style='cursor:pointer;' value='[附件]' onclick='jxloadGrid(\""+rec.编号+"\");'>&nbsp;"; 
            }}
        ]],
        onClickRow:function(rowIndex, rowData){
            row=rowData;
        },
        onLoadSuccess: function(data){
            iKeyCode='';
        },
        onLoadError:function(none){
            
        }
    });
    

}





/* 学生个人附件信息列表数据 */
function loadGridfjsc(zy){
    $('#jxbzList').datagrid({
        url:'<%=request.getContextPath()%>/ImportTextBean_Servlet',
        queryParams:{"active":"queryList","XSBH" : zy}, 
        loadMsg : "信息加载中请稍侯!", //载入时信息
        title:'教学计划信息列表',
        width : '785px', //宽度       
        height : '500px',
        nowrap : false, //截取当数据长度超出列宽时会自动截取
        fitColumns : true, //自适应列宽防止出现水平滚动
        striped : true, //隔行变色
        pagination : true, //分页
        showFooter:true, //显示页脚信息
        pageSize : 10, //每页记录数
        singleSelect : true, //单选模式 这里是指只能选一行不能多选
        pageNumber :1, //当前页码
        rownumbers:true,
        columns:[[
            //field为读取数据的数据名，title为显示的数据名，width宽度设置，align数字在表格中显示的位置
            {field:'编号',title:'教学计划编号',hidden:true},
            {field:'文件名',title:'文件名称',width:fillsize(0.12),align:'center'},
            {field:'创建人',title:'上传人',width:fillsize(0.15),align:'center'},
            {field:'创建时间',title:'上传时间',width:fillsize(0.15),align:'center'},
            {field:'col3',title:'附件',width:fillsize(0.15),align:'center',
                formatter:function(value,rec,index){
                    return "<input type='button' style='cursor:pointer;' value='[查看]' onclick='decideFile(\""+rec.文件路径+"\",\""+rec.预览路径+"\",\""+index+"\");'>" 
            }}
        ]],
        //单击某行时触发
        onClickRow:function(rowIndex,rowData){
            //主键赋值
            iKeyCode = rowData.编号;
            curRowData = rowData;
        },
        //加载成功后触发
        onLoadSuccess: function(data){
            iKeyCode = '';
            curRowData = '';
            Maindata=data;
        }
        
    });
};

    
    
    /**检查上传文件*/
    function checkFile(){
        var filePath = $("#icJXBZ_UPLOAD").val();
       
        var pattern = /^[^\s]*$/;
        var fname = filePath.substring(filePath.lastIndexOf("\\")+1, filePath.length);
        if(!pattern.test(fname)){//匹配文件名
            alertMsg('文件名中不能有空格!',0);
            return;
        }
        
        var arr = filePath.split('\\');
        var fileName = arr[arr.length-1];
        if(filePath!=""){
            uploadFile('icJXBZ_UPLOAD');
        } 
    }
        
    
    //上传附件请求方法
    function uploadFile(fileId){
            $.ajaxFileUpload({
                url:'<%=request.getContextPath()%>/ImportTextBean_Servlet?active=uploadify&XSBH='+XSBH, //服务器地址
                secureuri:false,
                /*type:"post", */
                fileElementId:fileId,//文件选择框的id属性
                dataType: 'text',//服务器返回的格式，可以是json
                success: function (data){//相当于java中try语句块的用法
                    if(data == '上传文件成功'){
                        //提示信息
                        showMsg('上传文件成功');
                        loadGridfjsc(XSBH);
                        $('#jxbzInfo').dialog('close');
                    }else{
                        $('#jxbzInfo').dialog('close'); 
                        loadGridfjsc(JXSKJHMX_BH);
                        alertMsg(data);
                    }
                    
                }
           }); 
       }
                
        
        

    //预览的是图片还是文本进行处理
    function decideFile(path, previewpath,index){
        curRowData=Maindata.rows[index];
        preview(path, previewpath);//文本
    }
         

    //显示文本文件预览
    function preview(path, previewpath){
        $('#ic_flexPaperZhezhao').show();
        var swfPath =filePreview+previewpath;
        $.ajax({
           type: 'POST',
           url: '<%=request.getContextPath()%>/ImportTextBean_Servlet',   
           data: 'active=fileToSwf&filePath='+path, 
           //dataType: 'json',    
           success: function(datas){
                if(datas != '') {
                       var json = eval("("+datas+")");
                       //判断文件是否转换成功
                       if(json[0].MSG == '转换成功'){ 
                           $('#documentViewer').FlexPaperViewer({
                               config : {
                                   //需要使用FlexPaper打开的文档
                                SWFFile : escape(swfPath), 
                                //初始化缩放比例
                                Scale : 1.0,    
                                //缩放样式,其他可选值包括：easenone，easeout，linear，easeoutquad
                                ZoomTransition : 'easeout',  
                                //从一个缩放比例变为另外一个缩放比例需要花费的时间，该参数值应该为0或更大
                                ZoomTime : 0.5,  
                                //缩放比例之间间隔
                                ZoomInterval : 0.2, 
                                //初始化时自适应页面
                                FitPageOnLoad : false,
                                 //初始化时自适应页面宽度  
                                FitWidthOnLoad : false, 
                                 //当设置为true时，单击全屏按钮会打开一个FlexPaper最大化的新窗口而不是全屏，当由于flash播放器因为安全而禁止全屏，而使用flexpaper作为独立的flash播放器的时候设置为true是个优先选择
                                FullScreenAsMaxWindow : false,
                                //当设置为true时，展示文档时不会加载完整个文档，而是逐步加载，但是需要将文档中转化为9以上的版本（使用pdf2swf的时候使用-T 9标签）
                                ProgressiveLoading : false, 
                                //设置最小的缩放比例
                                MinZoomSize : 0.5,  
                                //设置最大的缩放比例        
                                MaxZoomSize : 3,            
                                //设置为true时，单击搜索所有符合条件的地方高亮显示
                                SearchMatchAll : false,    
                                //设置启动模式如“Portrait”或“TowPage” 
                                InitViewMode : 'Portrait',  
                                RenderingOrder : 'flash',
                                StartAtPage : '',
                                //工具栏上是否显示样式选择框
                                ViewModeToolsVisible : true, 
                                //工具栏上时候显示缩放工具
                                ZoomToolsVisible : true,  
                                //工具栏上是否显示导航工具   
                                NavToolsVisible : true,  
                                //工具栏上是否显示光标工具    
                                CursorToolsVisible : true,   
                                //工具栏上是否显示搜索工具
                                SearchToolsVisible : false,  
                                WMode : 'window',
                                //设置地区（语言）
                                localeChain: 'zh_CN'  
                            }
                           });
                           
                        $('#ic_flexPaperZhezhao').hide();
                        $('#ic_flexPaperDiv').show();
                       }else if(json[0].MSG == '转换失败'){
                           //判断serverMsg值
                           if(json[0].serverMsg == 'openoffice服务未启动'){
                               alertMsg('openoffice服务已重新启动，点击确定后可重新预览！');
                           }else if(json[0].serverMsg == 'openofficeBat文件不存在'){
                               alertMsg('openoffice服务启动文件不存在，如有疑问请联系管理员！');
                           }else{
                               alertMsg('文件暂时无法预览，如有疑问请联系管理员！');
                           }
                           $('#ic_flexPaperZhezhao').hide();
                       }
                   }
            }
        });            
    }

</script>
</html>

Servlet代码：

package or.og.jxldemo;

import java.io.IOException;
import java.net.URLDecoder;
import java.sql.SQLException;
import java.util.Vector;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;

import com.pantech.base.common.exception.WrongSQLException;
import com.pantech.base.common.tools.JsonUtil;
import com.pantech.base.common.tools.MyTools;
import com.pantech.base.common.tools.TraceLog;

import net.sf.json.JSONArray;

/**
 * Servlet implementation class ImportTextBean_Servlet
 */
public class ImportTextBean_Servlet extends HttpServlet {

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doPost(request, response);
    }

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //设置字符编码为UTF-8
        request.setCharacterEncoding("UTF-8");
        response.setContentType("text/html;charset=UTF-8");
        
        String active = MyTools.StrFiltr(request.getParameter("active"));// 拿取前台的active值
        int pageNum = MyTools.parseInt(request.getParameter("page"));    //获得页面page参数 分页
        int pageSize = MyTools.parseInt(request.getParameter("rows"));    //获得页面rows参数 分页
        
        Vector jsonV = null;//返回结果集
        JSONArray jal = null;//返回json对象
        ImportTextBean bean = new ImportTextBean(request);
        this.getFormData(request, bean); //获取SUBMIT提交时的参数（AJAX适用）
        System.out.println("active:--"+active);

        
        
        //读取学生信息
        if("loadElective".equalsIgnoreCase(active)){  
            try {
                jsonV = bean.loadElective(pageNum, pageSize);
                
                jal = (JSONArray)jsonV.get(2);
                response.getWriter().write("{\"total\":" + MyTools.StrFiltr(jsonV.get(0)) + ",\"rows\":" + jal.toString() + "}");//返回前台datagrid需要的数据格式
                TraceLog.Trace("{\"total\":" + MyTools.StrFiltr(jsonV.get(0)) + ",\"rows\":" + jal.toString() + "}");
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    
        
        //查询附件信息表
        if("queryList".equalsIgnoreCase(active)) {
            String XSBH =MyTools.StrFiltr(request.getParameter("XSBH")); //学生编号
            try {
                jsonV = bean.queryList(XSBH);
                jal = (JSONArray)jsonV.get(2);//转化为JSON数据格式
                response.getWriter().write(jal.toString());//返回数据到前台
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        
        
        
        
        //上传附件请求方法
        if("uploadify".equalsIgnoreCase(active)){
            String XSBH =MyTools.StrFiltr(request.getParameter("XSBH")); //学生编号
            String savePath = "";
            savePath=MyTools.getProp(request, "Base.JYWDStandardUpLoadPath");
            String savetestPath = MyTools.getProp(request, "Base.testPathFile");
    
            System.out.println("--上传路径："+savePath+"--文档类型：");
            DiskFileItemFactory fac = new DiskFileItemFactory();  
            ServletFileUpload upload = new ServletFileUpload(fac);  
            upload.setHeaderEncoding("utf-8");  
               
            bean.uploadifyFile(savePath, request, response); //调用创建文件方法
            if (bean.getMSG().equals("保存成功")) {
                try {
                    bean.UploadToDB(request,XSBH);
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                } catch (WrongSQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }    //调用插入数据方法
                response.getWriter().write(bean.getMSG());
            }else {
                response.getWriter().write(bean.getMSG());
            }
        }
        
        
        
        
        //将word等文件转换成swf格式
        if("fileToSwf".equalsIgnoreCase(active)){ 
            String path = MyTools.StrFiltr(request.getParameter("filePath"));  //文件路径
            String filePath =path;
            Office2SWF o2s = new Office2SWF(request, filePath);
            boolean flag = o2s.conver();
            
            if(flag){
                jal = JsonUtil.addJsonParams(jal,"MSG", "转换成功");
            }else{
                jal = JsonUtil.addJsonParams(jal,"MSG", "转换失败");
                if("openoffice服务未启动".equalsIgnoreCase(o2s.getMSG()) || "openofficeBat文件不存在".equalsIgnoreCase(o2s.getMSG())){
                    jal = JsonUtil.addJsonParams(jal, "serverMsg", o2s.getMSG());
                }
            }
            response.getWriter().write(jal.toString());
        }
        
        
        
        //删除附件信息表
        if("deleteRow".equalsIgnoreCase(active)) {
            String FJXXBH = MyTools.StrFiltr(request.getParameter("FJXXBH"));  //附件信息编号
            
            try {
                //调用删除操作
                bean.deleteRow(FJXXBH);
                //返回操作信息
                jal=JsonUtil.addJsonParams(jal, "msg", bean.getMSG());
                response.getWriter().write(jal.toString());
            } catch (SQLException e) {
                e.printStackTrace();
                jal = JsonUtil.addJsonParams(jal, "msg", "无法获取数据<br>"+bean.getMSG());
                response.getWriter().write(jal.toString());
            } catch (WrongSQLException e) {
                e.printStackTrace();
            }         
        }
        
        
        
    }
    
    
    
    /**
    * 从界面没获取参数
    * @date 
    * @author:yeq
    * @param request
    * @param MajorSetBean
    */
    private void getFormData(HttpServletRequest request, ImportTextBean bean){
        bean.setUSERCODE(MyTools.getSessionUserCode(request));   //USERCODE
    }
    

}

Bean代码：

package or.og.jxldemo;

import java.io.BufferedInputStream;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Vector;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;

import com.pantech.base.common.db.DBSource;
import com.pantech.base.common.exception.WrongSQLException;
import com.pantech.base.common.tools.MyTools;

public class ImportTextBean {
    

    private HttpServletRequest request;
    private String USERCODE;//用户编号
    private DBSource db;
    
    
    private String    GX_FJXXBH;//附件信息编号
    private String    GX_FJXXWJM;//附件信息文件名
    private String    GX_FJXXTYPT;//附件信息文件类型
    private String    GX_FJXXWJLJ;//附件信息文件路径
    private String    GX_FJXXYLLJ;//附件信息预览路径
    private String MSG;  //提示信息
    /**
     * 构造函数
     * @param request
     */
    public ImportTextBean(HttpServletRequest request) {
        this.request = request;
        this.db = new DBSource(request);
    }
    
    
    
    //读取学生信息
    public Vector loadElective(int pageNum,int pageSize) throws SQLException {
        DBSource dbSource = new DBSource(request);
        Vector vector = null;
        String sql = "";
        
        sql="SELECT * FROM [student].[dbo].[V_基础信息_学生信息表] ";
        vector = dbSource.getConttexJONSArr(sql, pageNum, pageSize);
        return vector;
    }
    
    
    
    //查询附件信息表
    public Vector queryList(String XSBH) throws SQLException {
        String sql="";
        Vector vec = null; // 结果集
        sql="SELECT [编号],[学生编号],[文件名],[文件类型],[文件路径],[预览路径],[创建人],convert(nvarchar(19),创建时间,21) as 创建时间,[状态]   " + 
            "FROM [V_学生管理_附件信息表]  where 学生编号 ='" + MyTools.fixSql(XSBH)+"'";
        vec = db.getConttexJONSArr(sql, 0, 0);
        return vec;
    }
    
    
    
    
    
    
    
    //Uploadify上传文件
    public void uploadifyFile(String savePath, HttpServletRequest request, HttpServletResponse response){
        File f1 = new File(savePath);  
        //当文件夹不存在时创建
        if (!f1.exists()) {  
            f1.mkdirs();  
        }  
        DiskFileItemFactory fac = new DiskFileItemFactory();  
        ServletFileUpload upload = new ServletFileUpload(fac);  
        upload.setHeaderEncoding("utf-8");  
        List fileList = null;  
        try {  
            fileList = upload.parseRequest(request);  
            Iterator<FileItem> it = fileList.iterator();  
         
            String name = "";   //文件名
            String extName = "";  //文件后缀名
            int dian=0;//点所在的位置
            
            while (it.hasNext()) {  
                FileItem item = it.next();  
                if (!item.isFormField()) {  
                    name = item.getName();
                    String fileName = name;
                    long size = item.getSize();  
                    String type = item.getContentType();  
                    System.out.println(size + " " + type);  
                    if (name == null || name.trim().equals("")) {  
                        continue;  
                    }  
                    //扩展名格式    
                    if (name.lastIndexOf(".") >= 0) {  
                        extName = name.substring(name.lastIndexOf("."));  //获取文件格式
                    }  
                    File file = null;
                    String filePath = "";  //文件路径
                    String tempFilePath = ""; //文件临时路径
                    //SimpleDateFormat form = new SimpleDateFormat("HH-mm-ss-SS");
                    long ct =System.currentTimeMillis(); //获得当前系统时间毫秒，毫秒数其实就是自1970年1月1日0时起的毫秒数
                    Date newdate=new Date();//给图片加时间为防止页面图片不刷新
                    //String temp1=form.format(newdate);
                    do {  
                        //生成文件名  
                        //name = temp1.replaceAll("-", "").toUpperCase();
                        name=String.valueOf(ct)+"-"+this.getUSERCODE();  //获得毫秒数生成文件名加上当前登入人
                        filePath = savePath +""+ name+extName ;
                        tempFilePath =  name+""+extName;
                        dian=tempFilePath.indexOf(".");
                        file = new File(filePath);  
                    } while (file.exists());
                    File saveFile = new File(filePath);  
                    item.write(saveFile);
                    
                  //判断如果文件是txt文件的话，根据不同编码修改编码为UTF-8，以免预览出现乱码
                    if(".txt".equalsIgnoreCase(extName)){
                        String code = judgeCharset(new File(filePath));//判断文件编码
                        //System.out.println("++++++++++++++++++++++++++++++上传文件字符集为："+code);
                        if(!"UTF-8".equalsIgnoreCase(code)){
                            BufferedWriter bw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(new File(filePath)),"UTF-8")); 
                            bw.write(new String(item.getString(code).getBytes("UTF8"),"UTF-8"));
                            bw.flush();
                            bw.close();
                        }
                    }
                    this.setGX_FJXXTYPT(extName);  //附件信息文件类型,执行获得附件类型字段方法
                    this.setGX_FJXXWJM(fileName);  //文件名
                    this.setGX_FJXXWJLJ(filePath);  //文件完整路径
                    this.setGX_FJXXYLLJ(tempFilePath.substring(0, dian)+".swf"); //文件临时路径
                    System.out.println(extName+":"+fileName+"&&"+filePath);
                }
                this.setMSG("保存成功");
            }  
            
        } catch(Exception e){
                this.setMSG("保存失败");
            e.printStackTrace();
        }
    }
    
    

    //往数据库插入
    public void UploadToDB(HttpServletRequest req, String XSBH) throws SQLException, WrongSQLException{
        DBSource db = new DBSource(req);  //声明数据库连接对象
        SimpleDateFormat form = new SimpleDateFormat("HH-mm-ss-SS");
        Date newdate=new Date();//给图片加时间为防止页面图片不刷新
        String sql="";
            String temp1=form.format(newdate);
            System.err.println("SQL:"+temp1);
                   
                sql = "insert into [V_学生管理_附件信息表] ([编号],[学生编号],[文件名],[文件类型],[文件路径],[预览路径],[创建人],[创建时间],[状态]) values ("+
                        "newid(),"+    //编号    
                        "'"+MyTools.fixSql(XSBH)+"',"+ //学生编号    
                        "'"+MyTools.fixSql(this.getGX_FJXXWJM())+"',"+    //文件名    
                        "'"+MyTools.fixSql(this.getGX_FJXXTYPT())+"',"+    //文件类型
                        "'"+MyTools.fixSql(this.getGX_FJXXWJLJ())+"',"+        //文件路径
                        "'"+MyTools.fixSql(this.getGX_FJXXYLLJ())+"',"+        //预览路径
                        "'post',"+                                            //USERCODE
                        "getDate(),'1'"+                                    //创建时间
                        ")";    
                if(db.executeInsertOrUpdate(sql)){
                    this.setMSG("上传文件成功");
                }else{
                    this.setMSG("上传文件失败");
                }    
        
    }
    
    
    

    //判断文件编码
    public String judgeCharset(File file) {
        String charset = "GBK";
        byte [] first3Bytes = new byte[3];
        try {
            boolean checked = false;
            BufferedInputStream bis = new BufferedInputStream(new FileInputStream(file));
            bis.mark(0);
            int read = bis.read(first3Bytes, 0, 3);
            if (read == -1) return charset;
            if (first3Bytes[0] == (byte)0xFF && first3Bytes[1] == (byte)0xFE) {
                charset = "UTF-16LE";
                checked = true;
            }else if(first3Bytes[0] == (byte)0xFE && first3Bytes[1] == (byte)0xFF) {
                charset = "UTF-16BE";
                checked = true;
            }else if(first3Bytes[0] == (byte)0xEF && first3Bytes[1] == (byte)0xBB && first3Bytes[2] == (byte)0xBF) {
                charset = "UTF-8";
                checked = true;
            }
            bis.reset();
            
            if (!checked) {
                while ((read = bis.read()) != -1) {
                    if (read >= 0xF0)
                        break;
                    if (0x80<=read && read <= 0xBF) //单独出现BF以下的，也算是GBK
                        break;
                    if (0xC0<=read && read <= 0xDF) {
                        read = bis.read();
                        if (0x80<= read && read <= 0xBF)//双字节 (0xC0 - 0xDF) (0x80 - 0xBF),也可能在GB编码内
                            continue;
                        else
                            break;
                    } else if (0xE0 <= read && read <= 0xEF) {//也有可能出错，但是几率较小
                        read = bis.read();
                    if (0x80<= read && read <= 0xBF) {
                        read = bis.read();
                        if (0x80<= read && read <= 0xBF) {
                            charset = "UTF-8";
                            break;
                        } else
                            break;
                    } else
                        break;
                    }
                }
            }
            bis.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        
        return charset;
    }
        
        
        
        
        //删除附件信息表
        public void deleteRow(String FJXXBH)throws SQLException, WrongSQLException{
            String sql = "";
            Vector vec = null;
            
            //查询文件路径
            String fileName = "";
            sql = "select [文件路径] from [V_学生管理_附件信息表]  where 编号='" + MyTools.fixSql(FJXXBH) + "'";
            vec = db.GetContextVector(sql);
            if(vec.size()>0 && vec!=null)
                fileName = MyTools.fixSql(MyTools.StrFiltr(vec.get(0)));
            
            sql = "delete from [V_学生管理_附件信息表] where 编号='" + MyTools.fixSql(FJXXBH) + "' ";
            
            if(db.executeInsertOrUpdate(sql)){
                if(fileName.length()>0){
                    //截取文件名
                    fileName = fileName.substring(fileName.lastIndexOf("/")+1, fileName.lastIndexOf("."));
                    //删除相关文件
                    deleteFile(fileName);
                }
                
                this.setMSG("删除成功");//成功设置消息为<删除成功>
            }else{
                this.setMSG("删除失败");//失败设置消息为<删除成功>
            }
        }
        
        
        
    //删除相关文件    
    public boolean deleteFile(String fileName){
        boolean result = true;
        
        //获取配置路径
        String url = MyTools.getProp(request, "Base.JYWDStandardUpLoadPath");
        //删除该教学标准信息的上传文件及pwf文件
        File folder = new File(url);
        File temp = null;
        File[] filelist = folder.listFiles();//列出文件里所有的文件
        int loc = 0;
        for(int i=0; i<filelist.length; i++){//对这些文件进行循环遍历
            temp = filelist[i];
            loc = temp.getName().indexOf(fileName);//获取文件名字符的位置
            if(loc!=-1){//去掉后缀，如果文件名为该文件名的话就删除
                temp.delete();//删除文件
            }
        }
        
        return result;
    }
        
    
    
    
    
    
    public String getGX_FJXXBH() {
        return GX_FJXXBH;
    }

    public void setGX_FJXXBH(String gX_FJXXBH) {
        GX_FJXXBH = gX_FJXXBH;
    }

    public String getGX_FJXXWJM() {
        return GX_FJXXWJM;
    }

    public void setGX_FJXXWJM(String gX_FJXXWJM) {
        GX_FJXXWJM = gX_FJXXWJM;
    }

    public String getGX_FJXXTYPT() {
        return GX_FJXXTYPT;
    }

    public void setGX_FJXXTYPT(String gX_FJXXTYPT) {
        GX_FJXXTYPT = gX_FJXXTYPT;
    }

    public String getGX_FJXXWJLJ() {
        return GX_FJXXWJLJ;
    }

    public void setGX_FJXXWJLJ(String gX_FJXXWJLJ) {
        GX_FJXXWJLJ = gX_FJXXWJLJ;
    }

    public String getGX_FJXXYLLJ() {
        return GX_FJXXYLLJ;
    }

    public void setGX_FJXXYLLJ(String gX_FJXXYLLJ) {
        GX_FJXXYLLJ = gX_FJXXYLLJ;
    }

    public String getUSERCODE() {
        return USERCODE;
    }

    public void setUSERCODE(String uSERCODE) {
        USERCODE = uSERCODE;
    }


    public String getMSG() {
        return MSG;
    }

    public void setMSG(String mSG) {
        MSG = mSG;
    }
    
    
}

转换PDF代码：（注：此方法在Servlet层调用进行处理）

package or.og.jxldemo;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;

import javax.servlet.http.HttpServletRequest;

import com.artofsolving.jodconverter.DocumentConverter;
import com.artofsolving.jodconverter.openoffice.connection.OpenOfficeConnection;
import com.artofsolving.jodconverter.openoffice.connection.SocketOpenOfficeConnection;
import com.artofsolving.jodconverter.openoffice.converter.OpenOfficeDocumentConverter;
import com.pantech.base.common.tools.MyTools;
  

  
/** 
 * doc docx格式转换 
 *  
 * @author Administrator 
 *  
 */  
public class Office2SWF {  
    private static final int environment = 1;// 环境 1：windows 2:linux  
    // (只涉及pdf2swf路径问题)  
    private String fileString;  
    private String outputPath = "";//输入路径 ，如果不设置就输出在默认的位置  
    private String fileName;  
    private File pdfFile;  
    private File swfFile;  
    private File docFile;
    private String MSG;
    
    private HttpServletRequest request;
    
    public Office2SWF(HttpServletRequest request, String fileString) {
        this.request = request;
        init(fileString);  
    }  
   
    /** 
     * 重新设置file 
     *  
     * @param fileString 
     */  
    public void setFile(String fileString) {  
        init(fileString);  
    }  
  
    /** 
     * 初始化 
     *  
     * @param fileString 
     */  
    private void init(String fileString) {  
        this.fileString = fileString;  
        fileName = fileString.substring(0, fileString.lastIndexOf("."));  
        docFile = new File(fileString);  
        pdfFile = new File(fileName + ".pdf");  
        swfFile = new File(fileName + ".swf");  
    } 
    
    /** 
     * 转为PDF 
     *  
     * @param file 
     */  
    private void doc2pdf() throws Exception {  
        if (docFile.exists()) {  
            if (!pdfFile.exists()) {  
                try {
                    OpenOfficeConnection connection = new SocketOpenOfficeConnection("127.0.0.1", 8100);  
                    connection.connect();  
                    DocumentConverter converter = new OpenOfficeDocumentConverter(connection);  
                    converter.convert(docFile, pdfFile);  
                    // close the connection  
                    connection.disconnect();  
                    System.out.println("****pdf转换成功，PDF输出：" + pdfFile.getPath() + "****");  
                } catch (java.net.ConnectException e) {  
                    e.printStackTrace();  
                    System.out.println("****swf转换器异常，openoffice服务未启动！****");
                    Runtime rt = Runtime.getRuntime();
                    String batPath = MyTools.getProp(request, "Base.openofficeBatPath");
                    java.io.File file = new java.io.File(batPath);  
                    // 检查BAT文件是否存在  
                    if (file.isFile() && file.exists()) {
                         rt.exec(batPath);
                         this.setMSG("openoffice服务未启动");
                    }else{
                        this.setMSG("openofficeBat文件不存在");
                    }
                   
                    throw e;  
                } catch (com.artofsolving.jodconverter.openoffice.connection.OpenOfficeException e) {  
                    e.printStackTrace();  
                    System.out.println("****swf转换器异常，读取转换文件失败****");  
                    throw e;  
                } catch (Exception e) {  
                    e.printStackTrace();  
                    throw e;  
                }  
            } else {  
                System.out.println("****已经转换为pdf，不需要再进行转化****");  
            }  
        } else {  
            System.out.println("****swf转换器异常，需要转换的文档不存在，无法转换****");  
        }  
    }
    
    /**
     * 转换成 swf
     */
    private void pdf2swf(boolean flag) throws Exception {
        Runtime r = Runtime.getRuntime();
        if (!swfFile.exists()) {
            if (pdfFile.exists()) {
                if (environment == 1) {// windows环境处理
                    try {
                        String swfToolsPath = MyTools.getProp(request, "Base.swfToolsPath");
                        Process p = r.exec(swfToolsPath + " "+ pdfFile.getPath() + " -o "+ swfFile.getPath() + " -T 9 -s languagedir=C:/xpdf/xpdf-chinese-simplified");
                        System.out.print(loadStream(p.getInputStream()));
                        System.err.print(loadStream(p.getErrorStream()));
                        System.out.print(loadStream(p.getInputStream()));
                        System.out.println("****swf转换成功，文件输出："    + swfFile.getPath() + "****");
                        if (pdfFile.exists() && flag) { //pdf文件存在并且源文件不是PDF格式
                            pdfFile.delete();
                        }

                    } catch (IOException e) {
                        e.printStackTrace();
                        throw e;
                    }
                } else if (environment == 2) {// linux环境处理
                    try {
                        Process p = r.exec("pdf2swf " + pdfFile.getPath() + " -o " + swfFile.getPath() + " -T 9 -s languagedir=C:/xpdf/xpdf-chinese-simplified");
                        System.out.print(loadStream(p.getInputStream()));
                        System.err.print(loadStream(p.getErrorStream()));
                        System.err.println("****swf转换成功，文件输出：" + swfFile.getPath() + "****");
                        if (pdfFile.exists()) {
                            pdfFile.delete();
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                        throw e;
                    }
                }
            } else {
                System.out.println("****pdf不存在,无法转换****");
            }
        } else {
            System.out.println("****swf已经存在不需要转换****");
        }
    }

    static String loadStream(InputStream in) throws IOException {
        int ptr = 0;
        in = new BufferedInputStream(in);
        StringBuffer buffer = new StringBuffer();

        while ((ptr = in.read()) != -1) {
            buffer.append((char) ptr);
        }

        return buffer.toString();
    }

    /**
     * 转换主方法
     */
    public boolean conver() {
        boolean flag = false;
        if (swfFile.exists()) {
            System.out.println("****swf转换器开始工作，该文件已经转换为swf****");
            return true;
        }

        if (environment == 1) {
            System.out.println("****swf转换器开始工作，当前设置运行环境windows****");
        } else {
            System.out.println("****swf转换器开始工作，当前设置运行环境linux****");
        }
        try {
            if (!pdfFile.exists()) {
                flag = true;
                doc2pdf();
                pdf2swf(flag);
            }else{
                pdf2swf(flag);
            }
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }

        if (swfFile.exists()) {
            return true;
        } else {
            return false;
        }
    }

    /**
     * 返回文件路径
     * 
     * @param s
     */
    public String getswfPath() {
        if (swfFile.exists()) {
            String tempString = swfFile.getPath();
            tempString = tempString.replaceAll("\\\\", "/");
            return tempString;
        } else {
            return "";
        }
    }

    /**
     * 设置输出路径
     */
    public void setOutputPath(String outputPath) {
        this.outputPath = outputPath;
        if (!outputPath.equals("")) {
            String realName = fileName.substring(fileName.lastIndexOf("/"),
                    fileName.lastIndexOf("."));
            if (outputPath.charAt(outputPath.length()) == '/') {
                swfFile = new File(outputPath + realName + ".swf");
            } else {
                swfFile = new File(outputPath + realName + ".swf");
            }
        }
    }
    
    /**
     *  Get&&Set方法
     */
    public String getFileString() {
        return fileString;
    }

    public void setFileString(String fileString) {
        this.fileString = fileString;
    }

    public String getFileName() {
        return fileName;
    }

    public void setFileName(String fileName) {
        this.fileName = fileName;
    }

    public File getPdfFile() {
        return pdfFile;
    }

    public void setPdfFile(File pdfFile) {
        this.pdfFile = pdfFile;
    }

    public File getSwfFile() {
        return swfFile;
    }

    public void setSwfFile(File swfFile) {
        this.swfFile = swfFile;
    }

    public File getDocFile() {
        return docFile;
    }

    public void setDocFile(File docFile) {
        this.docFile = docFile;
    }

    public String getOutputPath() {
        return outputPath;
    }
    
    public String getMSG() {
        return MSG;
    }

    public void setMSG(String msg) {
        MSG = msg;
    }

}