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