POI Excel导入导出

一.导出到Excel

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

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

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

<pre name="code" class="javascript" style="color: rgb(75, 75, 75); line-height: 19.2px;">//导出条件

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时简单判断了(这样是不行的!)