Java 使用POI实现execl的导入导出数据实践

原创
2013/08/21 13:19
阅读数 3K

该篇文章使用poi3.5的版本

需要导入包如下

execl导入

/**
	 * execl 数据导入
	 * @author 小涂
	 * @title : import_excel
	 * @date Aug 21, 2013 1:04:42 PM
	 * @return String
	 */
	public static String import_excel(){
		String id=null;
		String name=null;
		String  sex=null;
		String  Dormitory=null;
		String Sept=null;

		Workbook workbook = null;
		int k=0;
		int flag = 0;   //指示指针所访问的位置
		String path="C:/Users/X/Desktop/212.xls";//获取文件的路径
		try {
			InputStream is = new FileInputStream(path);
			HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);

			// 循环工作表Sheet
			for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
				HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
				if (hssfSheet == null) {
					continue;
				}
				// 循环行Row
				for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
					HSSFRow hssfRow = hssfSheet.getRow(rowNum);
					if (hssfRow == null) {
						continue;
					}
					// 循环列Cell
					// "学号","姓名","性别","寝室号","所在系"};
					for (int cellNum = 0; cellNum <=4; cellNum++) {
						HSSFCell xh = hssfRow.getCell(cellNum);
						if (xh == null) {
							continue;
						}
						System.out.print(getValue(xh)+"\t");
					}
					System.out.print("\n");
				}
			}
		}catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}

		return  null;
	}

	
    /**
     * 得到Excel表中的值
     * 
     * @param hssfCell
     *            Excel中的每一个格子
     * @return Excel中每一个格子中的值
     */
    @SuppressWarnings("static-access")
    private static String getValue(HSSFCell hssfCell) {
        if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
            // 返回布尔类型的值
            return String.valueOf(hssfCell.getBooleanCellValue());
        } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
            // 返回数值类型的值
            return String.valueOf(hssfCell.getNumericCellValue());
        } else {
            // 返回字符串类型的值
            return String.valueOf(hssfCell.getStringCellValue());
        }
    }

导出execl 文件

/**
	 * 导出execl 文件
	 * @author 小涂
	 * @title : extport
	 * @date Aug 21, 2013 12:27:38 PM
	 * @return String
	 */

	public String extport(){
		List studentList=new ArrayList<Student>();//学生LIst

		for(int i=0;i<10;i++)
		{   Student student=new Student();//学生对象
		student.setStudentId("200908110"+i);
		student.setStudentName("杨波"+i);
		student.setStudentSex("男");
		student.setStudentDormitory("14-20"+i);
		student.setStudentSept("软件工程系");
		studentList.add(student);
		}

		String []tableHeader={"学号","姓名","性别","寝室号","所在系"};

		short cellNumber=(short)tableHeader.length;//表的列数
		HSSFWorkbook workbook = new HSSFWorkbook();   //创建一个excel
		HSSFCell cell = null;                                    //Excel的列
		HSSFRow row = null;                                      //Excel的行
		HSSFCellStyle style = workbook.createCellStyle();        //设置表头的类型
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		HSSFCellStyle style1 = workbook.createCellStyle();       //设置数据类型
		style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		HSSFFont font = workbook.createFont();                 //设置字体
		HSSFSheet sheet = workbook.createSheet("sheet1");        //创建一个sheet
		HSSFHeader header = sheet.getHeader();//设置sheet的头
		try {              
			if(studentList.size() < 1 ){
				header.setCenter("查无资料");
			}else{
				header.setCenter("学生表");
				row = sheet.createRow(0);
				row.setHeight((short)400);
				for(int k = 0;k < cellNumber;k++){
					cell = row.createCell(k);//创建第0行第k列
					cell.setCellValue(tableHeader[k]);//设置第0行第k列的值
					sheet.setColumnWidth(k,8000);//设置列的宽度
					font.setColor(HSSFFont.COLOR_NORMAL);      // 设置单元格字体的颜色.
					font.setFontHeight((short)350); //设置单元字体高度
					style1.setFont(font);//设置字体风格
					cell.setCellStyle(style1);
				}

				for(int i = 0 ;i < studentList.size() ;i++){                            
					Student student1 = (Student)studentList.get(i);//获取student对象
					row = sheet.createRow((short) (i + 1));//创建第i+1行
					row.setHeight((short)400);//设置行高

					if(student1.getStudentId() != null){
						cell = row.createCell(0);//创建第i+1行第0列
						cell.setCellValue(student1.getStudentId());//设置第i+1行第0列的值
						cell.setCellStyle(style);//设置风格
					}
					if(student1.getStudentName() != null){
						cell = row.createCell(1); //创建第i+1行第1列

						cell.setCellValue(student1.getStudentName());//设置第i+1行第1列的值

						cell.setCellStyle(style); //设置风格
					}
					//由于下面的和上面的基本相同,就不加注释了
					if(student1.getStudentSex() != null){
						cell = row.createCell(2);
						cell.setCellValue(student1.getStudentSex());
						cell.setCellStyle(style);
					}
					if(student1.getStudentDormitory()!= null){
						cell = row.createCell(3);
						cell.setCellValue(student1.getStudentDormitory());
						cell.setCellStyle(style);
					}
					if(student1.getStudentSept() != null){
						cell = row.createCell(4);
						cell.setCellValue(student1.getStudentSept());
						cell.setCellStyle(style);
					}

				}

			}

		} catch (Exception e) {
			e.printStackTrace();
		}

		// HttpServletResponse response = null;//创建一个HttpServletResponse对象
		OutputStream out = null;
		try {
			out = new FileOutputStream(new File("C:/Users/X/Desktop/212.xls"));
			//response = ServletActionContext.getResponse();//初始化HttpServletResponse对象
	        //out = response.getOutputStream();//
		} catch (FileNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try {

//			 String headerStr ="student学生";
//			 headerStr =new String(headerStr.getBytes("gb2312"), "ISO8859-1");//headerString为中文时转码
//			 response.setHeader("Content-disposition","attachment; filename="+    headerStr+".xls");//filename是下载的xls的名,建议最好用英文
//			 response.setContentType("application/msexcel;charset=UTF-8");//设置类型
//			 response.setHeader("Pragma","No-cache");//设置头
//			 response.setHeader("Cache-Control","no-cache");//设置头
//			 response.setDateHeader("Expires", 0);//设置日期头
			
			workbook.write(out);
			out.flush();
			workbook.write(out);
		} catch (IOException e) {
			e.printStackTrace();
		}finally{
			try{
				if(out!=null){
					out.close();
				}
			}catch(IOException e){
				e.printStackTrace();
			}
		}
		return null;
	}

如果你是在web应用是使用请将Response 和out 的代码打开

测试结果

导出文件

导入数据

展开阅读全文
打赏
1
21 收藏
分享
加载中
这个写的不错哦
2016/12/20 21:10
回复
举报
更多评论
打赏
1 评论
21 收藏
1
分享
返回顶部
顶部