Struts2使用JXL导入/导出数据

Excel导入

(1)创建文件输入流获取要导入的Excel文件。

(2)简历WookBook接收输入流。

(3)读取sheet，获取表格数据，

(4)插入数据库。

   

private File myfile;
 	private String myfileFileName;
 	private String myfileContentType;
 	/**
	 * 使用excel批量导入专项风险评估问题及答案
	 */
	public void batchImport(){
		int count = 0 ;
		Map<String,Object> map = new HashMap<String,Object>();
		if(myfile != null){
			String houzui = myfileFileName.substring(myfileFileName.lastIndexOf("."));
			if(".xls".equals(houzui)){//如过文件格式是exce格式的
				 int rows = 0; //表单行数  
				 int cols = 0;
				 Sheet sh = null; //表单对象  
			     InputStream inputStream = null;  
			     Workbook book = null;  
			     try {
					inputStream = new FileInputStream(myfile);
					book = Workbook.getWorkbook(inputStream);  
		            sh = book.getSheet(0);  
		            rows = sh.getRows();
		            cols = sh.getColumns();
		            for(int i = 2;i < rows ; i++){
		            	List<Object> params = new ArrayList<Object>();
		            	StringBuilder sql = new StringBuilder("insert into doctors (");
		            	for(int j = 0; j < cols ; j++){
		            		Cell cell = sh.getCell(j, i);
		            		String content = cell.getContents();
		            		if(content != null && !"".equals(content)){//内容不空
		            			Cell title = sh.getCell(j,1);
		            			if(params.size() == 0){
		            				sql.append(title.getContents());
		            			}else{
		            				sql.append(","+title.getContents());
		            			}
		            			params.add(content);
		            		}
		            	}
		            	sql.append(") values(");
		            	for(int k = 0 ; k < params.size() ; k++){
		            		if( k == params.size()-1){
		            			sql.append("?)");
		            		}else{
		            			sql.append("?,");
		            		}
		            	}
		            	System.out.println(sql.toString());
		            	if(params.size()>0)
		            	count += dao.execute(sql.toString(), "project", params.toArray());
		            	
		            }
		            if(count > 0){
			            map.put("msg", "导入成功");
						map.put("success", true);
		            }else{
		            	map.put("msg", "表格数据位空");
						map.put("success", true);
		            }
				} catch (FileNotFoundException e) {
					e.printStackTrace();
				} catch (BiffException e) {
					e.printStackTrace();
				} catch (IOException e) {
					e.printStackTrace();
				}  
		            
			        
			}else{
				map.put("msg", "文件格式不正确");
				map.put("success", false);
			}
		}else{
			map.put("msg", "请选择上传的文件");
			map.put("success", false);
		}
		try {
			super.writeJson(map);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

 

(2)       Excel导出

struts2.xml配置

<!--说明返回结果类型是stream,同时给予输出文件名进行设置-->

<result name="export"  type="stream">
       <param name="contentType">application/vnd.ms-excel</param>
       <param name="inputName">inputStream</param>
       <param name="contentDisposition">attachment;filename="record.xls"</param>
       <param name="bufferSize">4096</param>
    </result>

建立输出流，创建一个生成Excel的WritableWorkbook，创建一个像Sheet写数据的WritableSheet。

       /**

        * 创建工作薄标题

        * @param titles excel标题

        * @param fileName excel名称

        */

       os = newFileOutputStream(file);

                     try{

                            wbk= Workbook.createWorkbook(os);

                            ws= wbk.createSheet("sheet1", 0);

                            try{

                                   //单元格标题

                                   for(int i = 0; i < titles.length; i++) {

                                          Labellabel = new Label(i, 0, titles[i]);

                                          ws.addCell(label);

                                   }

       /**

        * 创建工作薄内容

        * @param cols 列数

        * @param rows 行数

* @param content 内容

        */

       public staticvoid createContent(int cols,int rows,String content){

              try {

                     //创建一个行列数为多少的表格

                     Labellabel = new Label(cols, rows, content);

                     ws.addCell(label);//将这个表格添加的Excel中

              }catch (RowsExceededException e) {

                     //TODO Auto-generated catch block

                     e.printStackTrace();

              }catch (WriteException e) {

                     //TODO Auto-generated catch block

                     e.printStackTrace();

              }

       }

       /**

        * 关闭工作薄和输出流

        */

       publicstatic  void close(){

              try {

                     if(wbk!=null){

                            wbk.write();

                            wbk.close();

                     }

                     if(os!=null)

                            os.close();

              }catch (WriteException e) {

                     e.printStackTrace();

              }catch (IOException e) {

                     e.printStackTrace();

              }

       }

}