博客
关于我
强烈建议你试试无所不能的chatGPT,快点击我
Java 使用POI实现execl的导入导出数据实践
阅读量:6329 次
发布时间:2019-06-22

本文共 6051 字,大约阅读时间需要 20 分钟。

  hot3.png

该篇文章使用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
();//学生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 的代码打开

测试结果

导出文件

导入数据

转载于:https://my.oschina.net/hermer/blog/155780

你可能感兴趣的文章