java 导出数据到excel 之 WritableWorkbook
所需jar文件为 jxl.jar 可以在此获取: http://www.uushare.com/user/sudyguo/files/2209594
这种方法导入excel效率较高,可批量导出; 更多更详细的用法,可以看: http://pcedu.pconline.com.cn/empolder/gj/java/0608/853669_1.html
实例:
- 此段代码用于生成excel文件
- */
- WritableWorkbook book= Workbook.createWorkbook(new File(application.getRealPath("/")+"price.xls"));
- //生成名为"第一页"的工作表,参数0表示这是第一页
- WritableSheet sheet=book.createSheet("第一页",0);
- //在Label对象的构造子中指名单元格位置是第一列第一行(0,0)
- WritableFont font1= new WritableFont(WritableFont.TIMES,14,WritableFont.BOLD);
- WritableCellFormat format1=new WritableCellFormat(font1);
- format1.setAlignment(jxl.format.Alignment.CENTRE);//设置为居中
- sheet.setColumnView(1,20);//设置第1列宽度,6cm左右
- sheet.mergeCells(0,0,7,0);
- Label label=new Label(0,0,"统计报表",format1);
- //将定义好的单元格添加到工作表中
- sheet.addCell(label);
- //将定义好的单元格添加到工作表中
- WritableFont font2=new WritableFont(WritableFont.createFont("楷体_GB2312"),10,WritableFont.NO_BOLD );
- WritableCellFormat format2=new WritableCellFormat(font2);
- format2.setAlignment(jxl.format.Alignment.CENTRE);//设置为居中
- //设置边框
- format2.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.NONE);
- label=new Label(0,2,"编号",format2);
- sheet.addCell(label);
- label=new Label(1,2,"采集日期",format2);
- sheet.addCell(label);
- label=new Label(2,2,"采集点",format2);
- sheet.addCell(label);
- label=new Label(3,2,"品种",format2);
- sheet.addCell(label);
- label=new Label(4,2,"种类",format2);
- sheet.addCell(label);
- label=new Label(5,2,"品种说明",format2);
- sheet.addCell(label);
- label=new Label(6,2,"最高价",format2);
- sheet.addCell(label);
- label=new Label(7,2,"最低价",format2);
- sheet.addCell(label);
- label=new Label(8,2,"平均价",format2);
- sheet.addCell(label);
- label=new Label(9,2,"状态",format2);
- sheet.addCell(label);
- /*生成一个保存数字的单元格
- 必须使用Number的完整包路径,否则有语法歧义
- 单元格位置是第二列,第一行,值为789.123
- jxl.write.Number number = new jxl.write.Number(2,0,789.123);
- sheet.addCell(number); */
- RowSet rs_info =null;
- Row row_info =null;
- DecimalFormat df_price=new java.text.DecimalFormat("#0.0");
- int count_zs=0;//信息提交总数
- int count_yx=0;//信息有效数
- String format="";
- where="";
- jxl.write.Number number=null;
- for (int i = 0; i <rs_price.getRowCount(); i++)
- {
- row_price=rs_price.getRow(i);
- number = new jxl.write.Number(0,i+3,i+1,format2);
- sheet.addCell(number);
- label=new Label(1,i+3,row_price.getFieldValue("collection_date"),format2);
- sheet.addCell(label);
- label=new Label(2,i+3,row_price.getFieldValue("point_name"),format2);
- sheet.addCell(label);
- label=new Label(3,i+3,row_price.getFieldValue("type_parent_name"),format2);
- sheet.addCell(label);
- label=new Label(4,i+3,row_price.getFieldValue("type_name"),format2);
- sheet.addCell(label);
- label=new Label(5,i+3, row_price.getFieldValue("type_desc"),format2);
- sheet.addCell(label);
- number=new jxl.write.Number(6,i+3,44.9 ,format2);
- sheet.addCell(number);
- number=new jxl.write.Number(7,i+3, 33 ,format2);
- sheet.addCell(number);
- number=new jxl.write.Number(8,i+3, Double.parseDouble(row_price.getFieldValue("price_avg").toString()) ,format2);
- sheet.addCell(number);
- label=new Label(9,i+3, row_price.getFieldValue("state") ,format2);
- sheet.addCell(label);
- }
- book.write();
- book.close();
- SmartUpload su = new SmartUpload();// 新建一个SmartUpload对象
- su.initialize(pageContext);// 初始化
- /* 设定contentDisposition为null以禁止浏览器自动打开文件,
- 保证点击链接后是下载文件。若不设定,则下载的文件扩展名为
- doc时,浏览器将自动用word打开它。扩展名为pdf时,
- 浏览器将用acrobat打开。*/
- su.setContentDisposition(null);
- // 下载文件
- su.downloadFile("price.xls");
- out.clear();
- out = pageContext.pushBody();
- File f=new File(application.getRealPath("/")+"price.xls");
- // f.delete();
- flag=false;
- }else{
- response.sendRedirect("./price_auditing.jsp");
- }
- }
- catch (Exception e)
- {
- System.out.println("错误:"+e);
- session.setAttribute(Tools.ERROR_INFO,"服务器内部错误!");
- session.setAttribute(Tools.DEST_URL,"price/type_excel.jsp"); //这里要使用绝对地址
- response.sendRedirect(Tools.ERROR_PAGE); //转到错误提醒页面
- }
- %>
读取方法:
- try {
- Workbook book = Workbook.getWorkbook(new File(" test.xls "));
- int num = book.getNumberOfSheets();
- for (int a = 0; a < num; a++) {
- // 获得第a个工作表对象
- Sheet sheet = book.getSheet(a);
- int columnum = sheet.getColumns(); // 得到列数
- int rownum = sheet.getRows(); // 得到行数
- System.out.println(columnum);
- System.out.println(rownum);
- for (int i = 0; i < rownum; i++) // 循环进行读写
- {
- for (int j = 0; j < columnum; j++) {
- Cell cell1 = sheet.getCell(j, i);
- String result = cell1.getContents();
- System.out.print(result);
- System.out.print(" \t ");
- }
- System.out.println();
- }
- }
- book.close();
- } catch (Exception e) {
- System.out.println(e);
- }