创建 读取excel文件 添加/更新内容

//创建excel并写入信息

public void create(String savePath) throws IOException, RowsExceededException, WriteException{
		createFolder(savePath);
		String fileName = savePath + "/test.xls";
		File file = new File(fileName);
		WritableWorkbook wwb = Workbook.createWorkbook(file);
		WritableSheet ws = wwb.createSheet("sheet 1",0);
		ws.addCell(new Label(0,0,"apiName"));
		ws.addCell(new Label(1,0,"selectedTimes"));
                wwb.write();
		wwb.close();
	}

 

//添加信息或更新信息
public void addOrUpdate(String savePath) throws BiffException, IOException, WriteException{
		 // 创建只读的 Excel 工作薄的对象
		 File source = new File(savePath + "/test.xls");
		 Workbook rw = Workbook.getWorkbook(source);
		 Sheet sheet = rw.getSheet(0);
		//获取Sheet表中所包含的总列数
		int columns = sheet.getColumns();
		//获取Sheet表中所包含的总行数
		int rows = sheet.getRows();
		 // 创建可写入的 Excel 工作薄对象
		 File dest = new File(savePath + "/test.xls");
		 //将源excel文件复制到目的excel文件
		 WritableWorkbook  wwb = Workbook.createWorkbook(dest, rw);
		 // 读取第一张工作表
		 WritableSheet ws = wwb.getSheet(0);
		 //添加新的内容
		 ws.addCell(new Label(0,rows,"map"));
		 ws.addCell(new Label(1,rows,"2"));
		 /*用来Upadate
		 // 获得第一个单元格对象
		 WritableCell wc = ws.getWritableCell(0, 0);

		 // 判断单元格的类型 , 做出相应的转化
		 if(wc.getType() == CellType.LABEL)
		 {
		   Label l = (Label)wc;
		   l.setString("The value has been modified.");
		 }
		 */
		 // 写入 Excel 对象
		 wwb.write();
		 // 关闭可写入的 Excel 对象
		 wwb.close();
		 // 关闭只读的 Excel 对象
		 rw.close();

		
	}

 

 

//创建文件夹
/*
	 * 创建存储excel文件的文件夹
	 */
	public static void createFolder(String path){
		File folder = new File(path);
		if(!(folder.exists()) && !(folder.isDirectory())){
			boolean createOk = folder.mkdirs();
			if(createOk){
				System.out.println("ok:创建文件夹成功！" );
			}else{
				System.out.println("err:创建文件夹失败！ " );   
			}
		}
	}

 

//默认Label写入String型，下面是写入各种类型，如数字等
//1. 添加 Label 对象
 jxl.write.Label labelC = new jxl.write.Label(0, 0, "This is a Label cell");
 ws.addCell(labelC);
 // 添加带有字型 Formatting 的对象
 jxl.write.WritableFont wf = new jxl.write.WritableFont(WritableFont.TIMES, 18,
 WritableFont.BOLD, true);
 jxl.write.WritableCellFormat wcfF = new jxl.write.WritableCellFormat(wf);
 jxl.write.Label labelCF = new jxl.write.Label(1, 0, "This is a Label Cell", wcfF);
 ws.addCell(labelCF);
 // 添加带有字体颜色 Formatting 的对象
 jxl.write.WritableFont wfc = new jxl.write.WritableFont(WritableFont.ARIAL, 10,
 WritableFont.NO_BOLD, false,
 UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.RED);
 jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(wfc);
 jxl.write.Label labelCFC = new jxl.write.Label(1, 0, "This is a Label Cell", wcfFC);
 ws.addCell(labelCF);
 //2. 添加 Number 对象
 jxl.write.Number labelN = new jxl.write.Number(0, 1, 3.1415926);
 ws.addCell(labelN);
 // 添加带有 formatting 的 Number 对象
 jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#.##");
 jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);
 jxl.write.Number labelNF = new jxl.write.Number(1, 1, 3.1415926, wcfN);
 ws.addCell(labelNF);
 //3. 添加 Boolean 对象
 jxl.write.Boolean labelB = new jxl.write.Boolean(0, 2, false);
 ws.addCell(labelB);
 //4. 添加 DateTime 对象
 jxl.write.DateTime labelDT = new jxl.write.DateTime(0, 3, new java.util.Date());
 ws.addCell(labelDT);
 // 添加带有 formatting 的 DateFormat 对象
 jxl.write.DateFormat df = new jxl.write.DateFormat("dd MM yyyy hh:mm:ss");
 jxl.write.WritableCellFormat wcfDF = new jxl.write.WritableCellFormat(df);
 jxl.write.DateTime labelDTF = new jxl.write.DateTime(1, 3, new java.util.Date(),
 wcfDF);
 ws.addCell(labelDTF);

 

//读取excel
	/*new
	 * 获取上传的excel教师信息文件，并进行解析，将各项内容存储到数据库中
	 */
	public List<Teacher> getTeachers(String filePath){
		
		InputStream is = null;
		try {
			is = new FileInputStream(filePath);
		} catch (FileNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try {
			Workbook wb = Workbook.getWorkbook(is);
			Sheet sheet = wb.getSheet(0); // 第几张表格从零开始
			if (sheet != null) {
				// 获取表格总列数
				int rsColumns = sheet.getColumns();
				// 获取表格总行数
				int rsRows = sheet.getRows();
				teacherList = new ArrayList();
				for (int rowNum = 1; rowNum < rsRows; rowNum++) {
					Cell[] cells = sheet.getRow(rowNum);
					for (int i = 0; i < rsColumns && i < cells.length; i++) {// 列数
						System.out.println(getExcelColumnLabel(i)
								+ "============" + cells[i].getContents());
						String columnLabel =  getExcelColumnLabel(i);
						String content = cells[i].getContents();
						if(columnLabel.equals("A")){
							no = content;
						}
						if(columnLabel.equals("B")){
							name = content;
						}
						if(columnLabel.equals("C")){
							password = content;
						}
						if(columnLabel.equals("D")){
							if(content.equals("男")){
								gender = "1";
							}else{
								gender = "2";
							}
						}
						if(columnLabel.equals("E")){
							telpone = content;
						}
						if(columnLabel.equals("F")){
							qq = content;
						}
						if(columnLabel.equals("G")){
							title = content;
						}
						if(columnLabel.equals("H")){
							permission = content;
						}
					}
					Teacher teacher = new Teacher();
					teacher.setNo(no);
					teacher.setName(name);
					teacher.setPassword(password);
					teacher.setGender(gender);
					teacher.setTelpone(telpone);
					teacher.setQq(qq);
					teacher.setTitle(title);
					teacher.setPermission(permission);
					teacherList.add(teacher);
				}
			}
			wb.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return teacherList;
	}
/**
	 * 获取excel对应列的字母
	 * 
	 * @author 
	 * @param num
	 * @return
	 */
	public static String getExcelColumnLabel(int num) {
		String temp = "";
		double i = Math.floor(Math.log(25.0 * (num) / 26.0 + 1) / Math.log(26)) + 1;
		if (i > 1) {
			double sub = num - 26 * (Math.pow(26, i - 1) - 1) / 25;
			for (double j = i; j > 0; j--) {
				temp = temp + (char) (sub / Math.pow(26, j - 1) + 65);
				sub = sub % Math.pow(26, j - 1);
			}
		} else {
			temp = temp + (char) (num + 65);
		}
		return temp;
	}