java读写excel2003

1、jxl读取excel文件，很多时候我们只是把预先处理好的数据写入excel中，做相应的处理。读excel比较复杂，况且我们可以把需要处理的数据按照特定的格式存入txt中，然后解析。此处列举一个读取excel20003的方法。读取之前，项目需要引入jxl.jar

/** * jxl读取excle2003文档 * @param strpath * @return */public String readExcel2003(String strpath) {Workbook book = null;File file = new File(strpath);if(!file.exists()){System.out.println(" the file not exist :"+strpath);}try {book = Workbook.getWorkbook(file);//book.getNumberOfSheets();//获取表单的个数jxl.Sheet st = book.getSheet(0);// 读取第一个sheetint rowNum = st.getRows();// 读取行数int columnNum = st.getColumns();// 读取列数//循环处理表格中每个单元for (int i = 1; i <= rowNum; i++) {for (int j = 0; j < columnNum; j++) {//表格中的行和列是从0开始的Cell cell = st.getCell(j, i);System.out.println(cell.toString());}}book.close();} catch (Exception e) {e.printStackTrace();} finally {if (book != null) {book.close();}}return "";}

 

2、用jxl写excel文件，对于写文件比较常用，写excel一般分为两步，第一步先把需要的数据按照特定的格式写入book对象中，第二部，就是把对应的文件传入jsp页面供用户下载。

在写excel中会涉及一个格式化和合并单元格的问题，需要读者自己细心处理

/** * 写excel2003 * @return */public String writeExcel2003() {try {String path = "/usr/local/tmpdata";File tempDir = new File(path);if (!tempDir.exists()) {tempDir.mkdirs();}String[] datekeys = { "2011-12-09", "2011-12-16" };String fileName = "name.xls";// 导出的名字File xlsFile = new File(tempDir.getAbsolutePath(), fileName);// 新建文档WritableWorkbook book = Workbook.createWorkbook(xlsFile);// excel写入文档// 新建一页WritableSheet booksheet = book.createSheet("第一页", 0);WritableFont font = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,Colour.BLACK);WritableCellFormat cellFormat = new WritableCellFormat(font);// cellFormat.setWrap(true);//自动换行cellFormat.setAlignment(Alignment.CENTRE);// 居中//首行一般需要合并单元格写表头，此处合并第一行的前三列，从(0,0)到(3,0)第一行第一列，到第一行第三列//下面四个参数，前两个是第一个单元格的坐标点，后两个是第二个的。booksheet.mergeCells(0, 0, 3, 0);booksheet.addCell(new Label(0, 0, "你的表头", cellFormat));// cellFormat是可以设置的，通过font对象设置字体大小``int clonum = 0, rownum = 1;booksheet.setColumnView(clonum, 16);booksheet.addCell(new Label(clonum++, rownum, "日期", cellFormat));booksheet.setColumnView(clonum, 16);booksheet.addCell(new Label(clonum++, rownum, "标签1", cellFormat));booksheet.setColumnView(clonum, 16);booksheet.addCell(new Label(clonum++, rownum, "标签2", cellFormat));for (int j = 0; j < datekeys.length; j++) {clonum = 0;++rownum;booksheet.addCell(new Label(clonum++, rownum, datekeys[j], cellFormat));// 日期booksheet.addCell(new Label(clonum++, rownum, "数据1", cellFormat));//Label处理字符串类型booksheet.addCell(new jxl.write.Number(clonum++, rownum, 10, cellFormat));//jxl.write.Number处理数值型}book.write();book.close();} catch (Exception e) {e.printStackTrace();}return "";}

完成以上步骤，只是把数据正确的写入book中，我们平时做的时候还需要把excel传入前段jsp页面，然后输出，这个需要用到一个jsp页面

<%@ page language="java" contentType="text/html;charset=gb2312"%><%@ page import="java.io.*"%><%String s = (String)request.getAttribute("file");File t_file = new java.io.File(s);if (!t_file.exists()){%><script language="JavaScript">alert("文件已经不存在!");history.go(-1);</script><%return;}InputStream in = new FileInputStream (t_file); if(in != null){String fs = t_file.getName();int l=(int)t_file.length();response.reset();if(s.toLowerCase().endsWith(".xls")){response.setContentType("application/vnd.ms-excel");}else if(s.toLowerCase().endsWith(".txt")){response.setContentType("application/octet-stream");}response.setHeader("Content-Disposition", "attachment;filename=\"" +new String(fs.getBytes("gb2312"),"ISO8859-1") + "\"");byte[] b = new byte[l]; int len = 0; while((len=in.read(b))>0){OutputStream os = response.getOutputStream();os.write(b,0,len);os.flush();os.close();} in.close(); }%>