POI Excel导入导出

一.导出到Excel

   基本思路：点击导出后生成临时.xls文件，返回文件名，供用户下载，下载完后删除文件

   带查询的导出(前端EasyUI)，如下为导出界面图

  

  下面为导出按钮绑定的函数：

var exportCondition={};//导出条件
//导出功能
function outputData(){
	$.ajax({
        type: "POST",
        url: path+"/main/inputAndOutput/output",
        data: exportCondition,
        success: function (fileName) {
            var downUrl = path+"/main/inputAndOutput/download?fileName=" + fileName;
            window.location = downUrl;
        }
    });
}

   //查询功能
   function search(){
     //按条件进行查询数据，首先我们得到数据的值
     //得到用户输入的参数，取值有几种方式：$("#id").combobox('getValue'), $("#id").datebox('getValue'), $("#id").val()
       //字段增加search_前缀字符，避免传递如URL这样的Request关键字冲突
       var queryData = {
           search_type: $("#search_type").combobox('getValue'),
           search_address: $("#search_address").combotree("tree").tree("getSelected")!=null?$("#search_address").combotree("tree").tree("getSelected").id:"",
           search_name: $("#search_name").textbox('getValue'),
           search_year: $("#search_year").textbox('getValue'),
           search_publicType: $("#search_publicType").textbox('getValue'),
           search_publicName: $("#search_publicName").textbox('getValue'),
           search_layout: $("#search_layout").combobox('getValue'),
           search_status: $("#search_status").combobox('getValue')
       }
       //将值传递给
       initGrid(queryData);
       
       //将查询条件传递给导出
       exportCondition = queryData;
   }

 

后台：生成.xls文件，返回文件名

@RequestMapping(value="output",method=RequestMethod.POST)
    @ResponseBody
    public String output(HttpServletRequest request,HttpServletResponse response){
        Map<String, Object> param = new HashMap<String, Object>();
        List<YellowPagesResourceModel> list = new ArrayList<YellowPagesResourceModel>();
        String fileName="";
        try {
            
            //获取查询条件
            param = getQueryParam(request);
            
            list = this.yellowpageResService.QueryAllForGridData(param);
            if(list.size()>0){
                //创建webbook,对应一个Excel文件
                HSSFWorkbook wb = new HSSFWorkbook();
                
                //设置表头及样式
                HSSFSheet sheet = defineHeader(wb);
                
                //填充数据
                writeData(list, sheet);
                
                //数据写入文件
                fileName=writeToFile(wb);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return fileName;
    }

/**
     * 设置表头和样式
     * @param wb
     * @return
     */
    private HSSFSheet defineHeader(HSSFWorkbook wb) {
        //添加sheet,对应Excel文件中sheet
        HSSFSheet sheet = wb.createSheet("黄页资源(一)");
        
        //创建表头
        HSSFRow row = sheet.createRow(0);
        
        //创建单元格，设置表头值
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中格式
        
        HSSFCell cell = row.createCell(0);
        cell.setCellValue("序号");
        cell.setCellStyle(style);
        
        cell = row.createCell(1);
        cell.setCellValue("所属地市");
        cell.setCellStyle(style);
        
        cell = row.createCell(2);
        cell.setCellValue("地区");
        cell.setCellStyle(style);
        
        cell = row.createCell(3);
        cell.setCellValue("年份");
        cell.setCellStyle(style);
        
        cell = row.createCell(4);
        cell.setCellValue("书名");
        cell.setCellStyle(style);
        
        cell = row.createCell(5);
        cell.setCellValue("刊式代码");
        cell.setCellStyle(style);
        
        cell = row.createCell(6);
        cell.setCellValue("刊式名称");
        cell.setCellStyle(style);
        
        cell = row.createCell(7);
        cell.setCellValue("刊式尺寸");
        cell.setCellStyle(style);
        
        cell = row.createCell(8);
        cell.setCellValue("价格");
        cell.setCellStyle(style);
        
        cell = row.createCell(9);
        cell.setCellValue("版面");
        cell.setCellStyle(style);
        return sheet;
    }

    /**
     * 写入数据到excel
     * @param list
     * @param sheet
     */
    private void writeData(List<YellowPagesResourceModel> list, HSSFSheet sheet) {
        HSSFRow row = null;
        //写入数据
        YellowPagesResourceModel model = null;
        for(int i=0;i<list.size();i++){
            row = sheet.createRow(i+1);
            model = list.get(i);
            row.createCell(0).setCellValue(i+1);
            row.createCell(1).setCellValue(model.getAddress());
            row.createCell(2).setCellValue(model.getAddressStr());
            row.createCell(3).setCellValue(model.getPagesYear());
            row.createCell(4).setCellValue(model.getPagesName());
            row.createCell(5).setCellValue(model.getPublicCode());
            row.createCell(6).setCellValue(model.getPublicName());
            row.createCell(7).setCellValue(model.getPublicType());
            row.createCell(8).setCellValue(model.getPrice().toString());
            row.createCell(9).setCellValue(model.getLayout()==1?"普通版面":"特殊版面");
        }
    }

    /**
     * 数据写入磁盘文件
     * @param wb
     * @throws IOException
     * @throws FileNotFoundException
     */
    private String writeToFile(HSSFWorkbook wb) throws IOException,
            FileNotFoundException {
        // 读取配置文件获取实际保存路径
        Properties props = PropertiesLoaderUtils.loadAllProperties("otherCfg.properties");
        //实际保存路径
        String saveDir = props.getProperty("PathToYellowPagesResFile_DEV");
        File fileDir = new File(saveDir.toString());
        if(!fileDir.exists()){
            fileDir.mkdirs();
        }
        String fileName = new java.text.SimpleDateFormat("yyyyMMddHHmmss").format(new Date()) + "-" +RandomUtils.nextInt();
        String filePath = saveDir+File.separator+fileName+".xls";
        FileOutputStream fout = new FileOutputStream(filePath);
        ByteArrayOutputStream ostream = new ByteArrayOutputStream();
        wb.write(ostream);
        fout.write(ostream.toByteArray());
        fout.flush();
        ostream.close();
        fout.close();
        return fileName+".xls";
    }

文件下载在此就不多提，注意的是在下载完后记得删除上面生成的.xls文件，其次为了解决乱码，如下设置response

/**
     * 解决附件下载名称乱码
     * @param request
     * @param response
     * @param params
     * @throws UnsupportedEncodingException
     */
    private void solveGarbled(HttpServletRequest request,
                                HttpServletResponse response,
                                Map<String, String> params
                                ) throws UnsupportedEncodingException{
        if (request.getHeader("User-Agent").toLowerCase().indexOf("msie") > -1){//IE浏览器
            response.setContentType("application/octet-stream");
            response.addHeader("Content-Disposition", "attachment;filename="+URLEncoder.encode(params.get("realFileName"), "iso-8859-1"));
        }else if (request.getHeader("User-Agent").toLowerCase().indexOf("firefox") > -1){//firefox浏览器
            response.setContentType("application/x-xls");
            response.addHeader("content-disposition", "attachment;filename=\"" + params.get("realFileName") + "\"");
        }else{//其他浏览器
            response.setContentType("application/x-xls");
            response.addHeader("content-disposition", "attachment;filename=" + params.get("realFileName"));
        }
    }

导出.xls文件如下：

  

 

二.xls导入数据到数据库

   基本思路:上传.xls,转换成.csv,读取数据，存入数据库

   导入对话框如下所示：

  

如下为代码:

 <div id="inputDlg" class="easyui-dialog" style="width:380px;height:220px;"
             data-options="buttons: '#inputDlg-buttons',closed:true,modal: true">
                 <form id="uploadForm"  method="post" enctype="multipart/form-data">  
                     <table cellpadding="8">
                        <tr class="fitem">
                                <td>
                                    <input id="uploadExcel" name="uploadExcel" class="easyui-filebox" style="width:250px;"
                                        data-options="prompt:'请选择.xls文件...'">  
                                </td>
                        </tr>
                        <tr class="fitem">
                             <td>
                                 <label>黄页类型:</label><input id="uploadType" name="uploadType" class="easyui-combobox"/>
                             </td>
                        </tr>
                     </table>
                </form> 
                <p style="color:red;font-size:12px;text-align:center;">请注意导入的Excel数据字段和
                    <a href="${pageContext.request.contextPath}/main/inputAndOutput/downloadTemplate">Excel模板</a>一致
                </p>
    </div>
    <div id="inputDlg-buttons">
            <a href="javascript:void(0)" class="easyui-linkbutton"
                data-options="iconCls:'icon-save'" onclick="uploadFile()">上传</a> 
           <a id="uploadBtn" href="javascript:void(0)" class="easyui-linkbutton"
                data-options="iconCls:'icon-cancel'"
                onclick="javascript:$('#inputDlg').dialog('close')">取消</a>
    </div>

//文件上传
function uploadFile(){
    //得到文件路径
    var filePath = $('#uploadExcel').filebox('getValue');
    if(filePath!=""){
        //对文件格式进行验证(简单验证)
        var d1=/\.[^\.]+$/.exec(filePath);
        if(d1==".xls"){
            $('#uploadForm').form('submit',{
                   url: path+'/main/inputAndOutput/upload',
                   success: function(data){
                       if (data){
                           $('#inputDlg').dialog('close');
                           $("#grid").datagrid('reload');
                       } else {
                           $.messager.alert('操作提示',"导入失败，请检查数据是否正确!",'error');
                       }
                   }
               });
        }else{
            $.messager.alert('温馨提示','请选择.xls文件!','warning');
        }
    }else{
        $.messager.alert('温馨提示','请选择.xls文件!','warning');
    }
}

后台：

  

    @RequestMapping(value="upload")
    @ResponseBody
    public String upload(HttpServletRequest request,HttpServletResponse response) throws IOException{
        String result=null;
        //文件上传到磁盘
        Map<String,String> map = uploadExcel(request);
        
        String fileName = map.get("fileName");
        Integer type =Integer.parseInt(map.get("type"));
        List<String> dataList = new ArrayList<String>();
        Properties props = PropertiesLoaderUtils.loadAllProperties("otherCfg.properties");
        String saveDir = props.getProperty("PathToYellowPagesResFile_DEV");
        String filePath = saveDir+File.separator+fileName;
        try {
            
            //.xls转换为.csv
            XLS2CSVmra xls2csv = new XLS2CSVmra(filePath+".xls", filePath+".csv");
            xls2csv.process();
            //删除.xls
            File file = new File(filePath+".xls");
            if(file.exists())file.delete();
            
            //获取.csv中数据
            File csvFile = new File(filePath+".csv"); 
            dataList = CSVUtils.importCsv(csvFile);
            
            //删除.csv
            csvFile.delete();
            for(String s:dataList){
                System.out.println(s);
            }
            
            if(dataList.size()>0){
                //.csv中数据转换为entity
                List<YellowPagesResource> instances = new ArrayList<YellowPagesResource>();
                instances = convertToEntity(type, dataList);
                //保存至数据库
                this.yellowpageResService.createOrModify(instances);
                result = "成功导入:"+instances.size()+"条数据.";
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

说明：

  (1).xls转换为.csv,由于.csv以一行数据的字符串并用“，”分隔存放数据，所以可以实现一行一行地读取数据

      apache官方的例子：实现.xls-->.csv和.xlsx--->.csv
　　XLS2CSV: http://www.docjar.com/html/api/org/apache/poi/hssf/eventusermodel/examples/XLS2CSVmra.java.html
　　XLSX2CSV: https://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/eventusermodel/XLSX2CSV.java

  (2)获取.csv的数据(注意设置编码集，否则容易乱码)

public static List<String> importCsv(File file){
        List<String> dataList=new ArrayList<String>();
        FileInputStream in = null;
        BufferedReader br=null;
        try { 
            in = new FileInputStream(file);
            br = new BufferedReader(new InputStreamReader(in, "GBK"));//设置编码集
            String line = ""; 
            while ((line = br.readLine()) != null) { 
                dataList.add(line);
            }
        }catch (Exception e) {
        }finally{
            if(br!=null){
                try {
                    br.close();
                    br=null;
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
 
        return dataList;
    }

(3).csv中数据转换为entity

    /**
     * 将从csv中获取的数据转换成Entity
     * @param type 资源类型
     * @param dataList
     */
    private List<YellowPagesResource> convertToEntity(Integer type, List<String> dataList) {
        String[] cells = null;
        List<YellowPagesResource> instances = new ArrayList<YellowPagesResource>();
        for(int i=0;i<dataList.size();i++){
            cells = dataList.get(i).replace("\"", "").split(",");
            if(cells.length<10){
                continue;
            }else{
                YellowPagesResource ypr = new YellowPagesResource();
                ypr.setPagesName(cells[4]);
                ypr.setPagesYear(cells[3]);
                ypr.setAddress(cells[1]);
                ypr.setPublicCode(cells[5]);
                ypr.setPublicName(cells[6]);
                ypr.setPublicType(cells[7]);
                ypr.setStatus(2);//未销售
                ypr.setCreator(SecurityUserHolder.getCurrentUser().getName());
                ypr.setProductId("402881ea4c5e43fd014c60660ffd0000");//这个暂时写死的
                ypr.setType(type);
                if("特殊版面".equals(cells[9])){
                    ypr.setLayout(2);
                }else if("普通版面".equals(cells[9])){
                    ypr.setLayout(1);
                }else{
                    continue;
                }
                ypr.setPrice(BigDecimal.valueOf(Double.parseDouble(cells[8])));
                instances.add(ypr);
            }
        }
        return instances;
    }

注：以上实例未给出验证上传的.xls数据格式是否合法，只是在转换为entity时简单判断了(这样是不行的!)