jxl方式解析Excel文件_JAVA_编程开发_程序员俱乐部

最近在做excel的导入导出功能，写了个比较通用的方法，贴上来记录一下、、

工具包用的是韩国棒子写的JavaExcel(jxl)，配置比较简单，对中文的支持也不错，就是不支持07或以上的excel版本，原作者貌似也没有打算出新版本的意思，这个比较纠结。

相关的jar包是 jxl.jar，网上到处都是、

1. 读取excel文件

   class="java" name="code">/**
   	 * 读取excel文件
   	 * @param file excel文件
   	 * @param headerConvert 转换头
   	 * @return 记录列表
   	 * @throws Exception
   	 */
   	public static List<Map<String, Object>> import2Excel(File file, Map<String, String> headerConvert) throws Exception {
   		InputStream is = null;
   		Workbook workbook = null;
   		//记录list
   		List<Map<String, Object>> list=new ArrayList<Map<String,Object>>();
   		try {
   			is = new FileInputStream(file);
   			workbook = Workbook.getWorkbook(is);
   			Sheet sheet = workbook.getSheet(0);
   			int column = sheet.getColumns();
   			int row = sheet.getRows();
   			//转换map
   			Map<String, Object> convertMap = new HashMap<String, Object>();
   			// 记录
   			Map<String, Object> recordMap = new HashMap<String, Object>();
   			// 读取头
   			for (int i = 0; i < column; i++) {
   				Cell cell = sheet.getCell(i, 0);
   				String temp = cell.getContents();
   				convertMap.put("" + i, headerConvert.get(temp));
   			}
   				for (int i = 1; i < row; i++) {
   					for (int j = 0; j < column; j++) {
   					Cell cell = sheet.getCell(j, i);
   					Object content = new Object();
   					if (cell.getType().equals(CellType.LABEL)) {
   						content = cell.getContents();
   					} else if (cell.getType().equals(CellType.NUMBER)) {
   						NumberCell numberCell = (NumberCell) cell;
   						content = numberCell.getValue();
   					} else if (cell.getType().equals(CellType.DATE)) {
   						DateCell dateCell = (DateCell) cell;
   						content = dateCell.getDate();
   					}
   					recordMap.put(""+convertMap.get(""+j), content);
   				}
   				list.add(recordMap);
   			}
   			// 操作完成时，关闭对象，释放占用的内存空间
   			workbook.close();
   			is.close();
   			return list;
   		} catch (Exception e) {
   			e.printStackTrace(System.out);
   		} finally {
   			if (is != null) {
   				is.close();
   			}
   		}
   		return list;
   	}

   ?
2. 创建excel

?

public static void exportExcel(Map<String,String> title,List<Map<String,Object>> dataSet, String filename,String sheetname)throws Exception{
		// 得到项目的根目录
		String path = ServletActionContext.getServletContext().getRealPath("/");
		// 在服务器创建临时文件
		File file = new File(path + "temp");
		if(!file.isDirectory()){
			file.mkdirs();
		}
		file = new File(file.getPath()+File.separator+filename);
		// 创建工作表
		WritableWorkbook workbook = null;
		try {
			 workbook = Workbook.createWorkbook(file);
		} catch (Exception e) {
			e.printStackTrace();
		}
		//获取工作簿
		WritableSheet sheet = workbook.createSheet(sheetname, 0);
		WritableFont font0 = new WritableFont(WritableFont.TIMES, 13,
				WritableFont.NO_BOLD);
		WritableFont font = new WritableFont(WritableFont.TIMES, 13,
				WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
				Colour.RED);
		WritableCellFormat format0 = new WritableCellFormat(font0);
		WritableCellFormat format = new WritableCellFormat(font);
		// 水平居中
		format.setAlignment(Alignment.CENTRE);
		format0.setAlignment(Alignment.CENTRE);
		//写表头
		int col = 0;
		for(Map.Entry<String,String> entry : title.entrySet()){
			Label label = new Label(col, 0, entry.getValue(), format0);
			sheet.addCell(label);
			col++;
		}	
		
		int row = 1;
		for(Map<String,Object> data : dataSet){
			int _col = 0;
			for(Map.Entry<String,String> entry : title.entrySet()){
				Label label = new Label(_col, row, StringUtil.filterNull(data.get(entry.getKey())), format0);
				sheet.addCell(label);
				_col++;
			}
			row++;
		}
		// 保存
		workbook.write();
		workbook.close();
		//输出到前台
		HttpServletResponse response = ServletActionContext.getResponse();
		response.setContentType("application/vnd.ms-excel");
		response.setHeader("Content-disposition", "attachment; filename="
				+ java.net.URLEncoder.encode(filename, "UTF-8"));
		// 创建excel文件的输入输出流
		InputStream is = new FileInputStream(file);
		OutputStream os = response.getOutputStream(); 
		byte[] buf= new byte[1024];
		int size = is.read(buf);
		while (size > 0) {
			os.write(buf, 0, size);
			size = is.read(buf);
		}
		is.close();
		os.close();
		file.delete();
	}
大概就这样吧、挺简单的，不过不支持07还得想想别的办法，毕竟03是个过时产品了、、以后维护也不怎么方便、、、

?