JXL 解析EXCEL

来源:互联网 发布:c#判断网络状态 编辑:程序博客网 时间:2024/03/29 10:01

jExcelAPI是一个韩国人写的java操作excel的工具,jExcelAPI对中文支持非常好,API是纯Java的,并不依赖Windows系统,即使运行在Linux下,它同样能够正确的处理Excel文件,另外需要说明的是,这套API对图形和图表的支持很有限,而且仅仅识别PNG格式.

 

搭建环境
将下载后的文件解包,得到jxl.jar,放入classpath.

 

基本操作

 

package com.WebExcel.dao;

import java.io.IOException;
import java.io.OutputStream;
import java.util.List;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class Excel {

    /**
     * 生成excel文件(文件标题栏与文件内容一定要对应)
     * @param os
     * @param title(excel文件标题栏)
     * @param lists(excel文件内容)
     * @throws IOException
     * @throws RowsExceededException
     * @throws WriteException
     */
 public static void writeExcel(OutputStream os, String[] title, List lists) throws IOException, RowsExceededException, WriteException  {
  // 创建可以写入的Excel工作薄(默认运行生成的文件在tomcat/bin下 )
  WritableWorkbook wwb = Workbook.createWorkbook(os);
  // 生成工作表,(name:First Sheet,参数0表示这是第一页)
  WritableSheet sheet = wwb.createSheet("First Sheet", 0);
  
        // 开始写入第一行(即标题栏)
        for (int i=0; i<title.length; i++) {
            // 用于写入文本内容到工作表中去
            Label label = null;
            // 在Label对象的构造中指明单元格位置(参数依次代表列数、行数、内容 )      
            label = new Label(i, 0, title[i]);
            // 将定义好的单元格添加到工作表中
            sheet.addCell(label);
        }

        // 开始写入内容
        for (int row=0; row<lists.size(); row++) {        
             // 获取一条(一行)记录
             List list = (List) lists.get(row);
             // 数据是文本时(用label写入到工作表中)
             for (int col=0; col<list.size(); col++) {                
               String listvalue = (String) list.get(col).toString(); 
               Label label = null;
               label = new Label(col, row+1, listvalue);
                     sheet.addCell(label);            
             }   
      } 
    
     /*
        生成一个保存数字的单元格,必须使用Number的完整包路径,否则有语法歧义,值为789.123
    jxl.write.Number number = new jxl.write.Number(col, row, 555.12541);
    sheet.addCell(number);
  */

        /*
                           生成一个保存日期的单元格,必须使用DateTime的完整包路径,否则有语法歧义,值为new Date()
          jxl.write.DateTime date = new jxl.write.DateTime(col, row, new java.util.Date());
          sheet.addCell(date);
         */

        // 写入数据
  wwb.write();
  // 关闭文件
  wwb.close();
  // 关闭输出流
  os.close();
 }

}

 

package com.WebExcel.dao;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;

import javax.servlet.http.HttpServletResponse;

public class Files {
 
 /**
  * 向客户端下载文件,弹出下载框.
  * 
  * @param response(HttpServletResponse)
  * @param file(需要下载的文件)
  * @param isDel(下载完成后是否删除该文件)
  * @throws IOException 
  */
 public static void exportFile(HttpServletResponse response, File file, boolean isDel) throws IOException {
  OutputStream out = null;
  InputStream in = null;
 
  // 获得文件名
  String filename = URLEncoder.encode(file.getName(), "UTF-8"); 
  // 定义输出类型(下载)
     response.setContentType("application/force-download"); 
     response.setHeader("Location", filename);
     // 定义输出文件头
  response.setHeader("Content-Disposition", "attachment;filename=" + filename); 
  out = response.getOutputStream();
  in = new FileInputStream(file.getPath());
    
  byte[] buffer = new byte[1024];
  int i = -1;
  while ((i = in.read(buffer)) != -1) {
    out.write(buffer, 0, i);
  }
  
  in.close();
  out.close();  
  
  if (isDel) {
   //删除文件,删除前关闭所有的Stream.
   file.delete();
  }
  
 }
}

 

package com.WebExcel.servlet;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.WebExcel.dao.Excel;
import com.WebExcel.dao.Files;

public class ReportExcel extends HttpServlet {

 /**
  * The doGet method of the servlet. <br>
  *
  * This method is called when a form has its tag value method equals to get.
  * 
  * @param request the request send by the client to the server
  * @param response the response send by the server to the client
  * @throws ServletException if an error occurred
  * @throws IOException if an error occurred
  */
 public void doGet(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {
  this.doPost(request, response); 
 }

 /**
  * The doPost method of the servlet. <br>
  *
  * This method is called when a form has its tag value method equals to post.
  * 
  * @param request the request send by the client to the server
  * @param response the response send by the server to the client
  * @throws ServletException if an error occurred
  * @throws IOException if an error occurred
  */
 public void doPost(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {

  // 创建当前日子
     Date date = new Date();
     // 格式化日期 
     SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmssSSS");
     // 格式化日期(产生文件名)
     String filename = sdf.format(date);
  
  // 创建文件
  File f = new File("D://" + filename + ".xls");
  f.createNewFile();
  
        //标题数组
  String title[] = {"编号","地市","大学编号","大学名称","总保网数","定制总数","退订总数","(2010-07-09至2010-07-09)总保网数","(2010-07-09至2010-07-09)定制总数","(2010-07-09至2010-07-09)退订总数"};
  
  //组成list
  //测试数字
  List<Integer> list1 = new ArrayList<Integer>();
  list1.add(1);
  list1.add(2);
  list1.add(3);

  //测试日期
  List<Date> list2 = new ArrayList<Date>();
  list2.add(new Date());
  list2.add(new Date());
  list2.add(new Date());
  
  //测试英文
  List<String> list3 = new ArrayList<String>();
  list3.add("a");
  list3.add("b");
  list3.add("c");
  
  //测试汉字
  List<String> list4 = new ArrayList<String>();
  list4.add("一");
  list4.add("二");
  list4.add("三");
  
  List<List> lists = new ArrayList<List>();
  lists.add(list1);
  lists.add(list2);
  lists.add(list3);
  lists.add(list4);
 
  // 生成excel文件(保存在服务器机上)
  try {
   Excel.writeExcel(new FileOutputStream(f), title, lists);
  } catch (Exception e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
  
  // 导出文件(下载到客户机上,并删除服务器机上的excel文件)
  Files.exportFile(response, f, true);
   
 }

}