excel导入导出

最近在一个项目中涉及到了excel导入导出，excel导出用的POI
导出excel其实就是创建一个工作表，

HSSFWorkbook wb = new HSSFWorkbook();

然后创建sheet

HSSFSheet sheet = wb.createSheet("数据信息");

通过sheet创建row

HSSFRow row = sheet.createRow( rowNumber++ ); 

通过row创建cell

HSSFCell cell = row.createCell((short) 0);

具体实现是：

public void exportExcel(){
		List<Yxjszm> dataset =  yxjszmManager.getListByBean(bean);

  HttpServletResponse response = (HttpServletResponse)
		ActionContext.getContext().get(org.apache.struts2.StrutsStatics.HTTP_RESPONSE);

		try {
			int rowNumber = 0;

			HSSFWorkbook wb = new HSSFWorkbook();
			/* 字体样式 */
			HSSFFont font = wb.createFont();
			font.setFontHeightInPoints((short) 12);
			font.setFontName("Arial"); 

			HSSFCellStyle style = wb.createCellStyle();//背景有颜色     
			style.setFont(font);   
			style.setBorderLeft(HSSFCellStyle.BORDER_THIN);  // 左边框
			style.setBorderTop(HSSFCellStyle.BORDER_THIN);   // 上边框
			style.setBorderRight(HSSFCellStyle.BORDER_THIN); // 右边框
			style.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 下边框   
			style.setFillPattern(HSSFCellStyle.BRICKS);   
			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);  // 位于中间
			style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);   
			style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); // 灰色
			style.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index); // 灰色
			style.setWrapText(true);  // 当文字长于该列的宽度时，自动把宽加长

			HSSFCellStyle style2 = wb.createCellStyle();   
			style2.setFont(font);   
			style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);   
			style2.setBorderTop(HSSFCellStyle.BORDER_THIN);   
			style2.setBorderRight(HSSFCellStyle.BORDER_THIN);   
			style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);   
			style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);   
			style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);   
			style2.setWrapText(true);    



			HSSFSheet sheet = wb.createSheet("数据信息");

			sheet.setColumnWidth((short) 0, (short) 6000); //默认设置宽度
			sheet.setColumnWidth((short) 1, (short) 6000); //默认设置宽度
			sheet.setColumnWidth((short) 2, (short) 4000); //默认设置宽度
			sheet.setColumnWidth((short) 3, (short) 4000); //默认设置宽度
			sheet.setColumnWidth((short) 4, (short) 4000); //默认设置宽度
			sheet.setColumnWidth((short) 5, (short) 4000); //默认设置宽度


			HSSFRow row = sheet.createRow( rowNumber++ ); 
			HSSFCell cell = row.createCell((short) 0);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("序号"); 
			cell.setCellStyle(style);  
            
			cell = row.createCell((short) 1);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue( "别名" );
			cell.setCellStyle(style);
			
			cell = row.createCell((short) 2);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue( "名Id" );
			cell.setCellStyle(style); 
			
			//sheet.addMergedRegion( new Region(0, (short) 1, 0, (short) 5 ) );
			cell = row.createCell((short) 3);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue( "名" );
			cell.setCellStyle(style); 

			cell = row.createCell((short) 4);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue( "码" );
			cell.setCellStyle(style);
			
			
			cell = row.createCell((short) 5);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue( "上下" );
			cell.setCellStyle(style);
			
			
			for (int i = 0; i < dataset.size(); ++i){

				Yxjszm zm = (Yxjszm) dataset.get(i);

				rowNumber = this.addRowExcelInfor(cell, sheet, row,  style2,zm, rowNumber) ;

			} 
			String title = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()); 
			response.setCharacterEncoding("UTF-8");
			response.setContentType("octets/stream");// 设置输出流
			response.addHeader("Content-Disposition", "attachment;filename="
					+ title + ".xls");// 向客户端输出xls文件

			OutputStream os=response.getOutputStream();
			//add workbook to OutpustStream
			wb.write(os);
			os.flush();
			os.close();

		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}


	} 

	private int addRowExcelInfor(HSSFCell cell , HSSFSheet sheet , HSSFRow row ,HSSFCellStyle style2, Yxjszm zm , int rowNumber){
		row = sheet.createRow( rowNumber++ );

		cell = row.createCell((short) 0);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue(zm.getSn() );
		cell.setCellStyle(style2); 
		
		cell = row.createCell((short) 1);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue(zm.getRunPostlineid());
		cell.setCellStyle(style2);
		
		cell = row.createCell((short) 2);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue( zm.getStationId()  );
		cell.setCellStyle(style2);

		cell = row.createCell((short) 3);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue( zm.getStationName()  );
		cell.setCellStyle(style2); 

		cell = row.createCell((short) 4);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue(zm.getSpell());
		cell.setCellStyle(style2); 
		

		cell = row.createCell((short) 5);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue(zm.getUpDown());
		cell.setCellStyle(style2);
		
		
		cell.setCellStyle(style2);
		
		return rowNumber;
	} 

导入excel到数据库用jxl读取excel：

public void upload() throws IOException, BiffException{
	    String targetDirectory =contextPvd.getAppRealPath("")+"/upload";
        String targetFileName = uploadFileName;
        File target = new File(targetDirectory, targetFileName);
        FileUtils.copyFile(upload, target);            
        setUploadFileName(target.getPath());//保存文件的存放路径
        Workbook rwb = null;
        Cell cell = null;
        //创建输入流
        InputStream stream = new FileInputStream(target);
        //获取Excel文件对象
        rwb = Workbook.getWorkbook(stream);
        //获取文件的指定工作表 默认的第一个
        Sheet sheet = rwb.getSheet(0);  
        //行数(表头的目录不需要，从1开始)
        PrintWriter out = contextPvd.getResponse().getWriter();
      //  StringBuffer script =new StringBuffer();
        for(int i=1; i<sheet.getRows(); i++){
        	Yxjszm zm=new Yxjszm();
        		  //取号
        		  cell = sheet.getCell(0,i);
        		  zm.setSn(Long.parseLong(cell.getContents()));
  
        		  //取路id
        		  cell = sheet.getCell(1,i);
        		  zm.setRunPostlineid(cell.getContents());
  
        		  //取id
        		  cell = sheet.getCell(2,i);
        		  zm.setStationId(cell.getContents());
        		  
        		  //取名
        		  cell = sheet.getCell(3,i);
        		  zm.setStationName(cell.getContents());
        		  
        		  //取码
        		  cell = sheet.getCell(4,i);
        		  zm.setSpell(cell.getContents());
        		  
        		//取上下行
        		  cell = sheet.getCell(5,i);
        		  zm.setUpDown(Long.parseLong(cell.getContents()));
        		 findList = yxjszmManager.findListByXlAndZm(zm.getRunPostlineid(), zm.getStationName());
        		  if(findList.size()>0)
        		  {
        			  log.info("此路("+zm.getRunPostlineid()+")对应站名("+zm.getStationName()+")已存在!");
        		  }
        		  else
        		  {
        			  yxjszmManager.save(zm);
        			  log.info("线路("+zm.getRunPostlineid()+")"+"站名("+zm.getStationName()+")添加成功!"); 
        			  out.write("<script>parent.editFlag("+i+",'1')</script>");
        			  out.flush();
        		  }
        }
        	out.close();
        	stream.close();
        	target.delete();
 
	}