创建 读取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 blocke1.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;}