操作 Excel 示例源码

import java.io.File;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

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

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

/**
 * 功能描述： 操作Excel。该源码没有经过验证，请慎用<p/>
 *
 * @Author：admin
 * @Date：Feb 6, 2009
 * @Time：9:56:12 AM
 * @Version 1.0
 *
 *
 */
public class OperateExcel {
 public void CreateXLS() throws Exception {

  WritableWorkbook book = null;
  try {

   // 创建文件
   book = Workbook.createWorkbook(new File("测试.xls"));

   // 生成名为“第一页”的工作表，参数0表示这是第一页

   WritableSheet sheet = book.createSheet("第一页", 0);

   // 在Label对象的构造子中指名单元格位置是第一列第一行(0,0) 以及单元格内容为test

   Label label = new Label(0, 0, "test");

   // 将定义好的单元格添加到工作表中
   sheet.addCell(label);

   // 写入数据并关闭文件
   jxl.write.Number number = new jxl.write.Number(1, 0, 789.123);

   sheet.addCell(number);
   book.write();

  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   book.close();
  }
 }

 @SuppressWarnings("deprecation")
 public void createExcel(HttpServletRequest request,
   HttpServletResponse response) throws Exception {
  Connection conn = null;
  ResultSet rs = null;
  String url = "jdbc:mysql://localhost:3306/studentinfo";
  String userName = "root";
  String password = "";
  String sql = "select * from tablename";
  try {
   Class.forName("com.mysql.jdbc.Driver");
   conn = (Connection) DriverManager.getConnection(url, userName,
     password);
   Statement stmt = conn.createStatement();
   rs = stmt.executeQuery(sql);

   // 新建Excel文件
   String filePath = request.getRealPath("aaa.xls");
   File myFilePath = new File(filePath);
   if (!myFilePath.exists())
    myFilePath.createNewFile();
   FileWriter resultFile = new FileWriter(myFilePath);
   // PrintWriter myFile = new PrintWriter(resultFile);
   resultFile.close();

   // 用JXL向新建的文件中添加内容
   OutputStream outf = new FileOutputStream(filePath);
   jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(outf);
   jxl.write.WritableSheet ws = wwb.createSheet("sheettest", 0);

   int i = 0;
   int j = 0;

   for (int k = 0; k < rs.getMetaData().getColumnCount(); k++) {
    ws.addCell(new Label(k, 0, rs.getMetaData()
      .getColumnName(k + 1)));
   }

   while (rs.next()) {
    System.out.println(rs.getMetaData().getColumnCount());
    for (int k = 0; k < rs.getMetaData().getColumnCount(); k++) {
     ws.addCell(new Label(k, j + i + 1, rs.getString(k + 1)));
    }

    i++;
   }
   wwb.write();
   wwb.close();
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   rs.close();
   conn.close();
  }
  response.sendRedirect("aaa.xls");
 }

 public void ReadXLS() {
  Workbook book = null;
  try {
   // 得到文件
   book = Workbook.getWorkbook(new File("测试.xls"));

   // 获得第一个工作表对象
   Sheet sheet = book.getSheet(0);

   // 得到第一列第一行的单元格
   Cell cell1 = sheet.getCell(0, 0);

   // 得到第一列第一行的单元格的内容
   String result = cell1.getContents();

   System.out.println(result);

  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   book.close();
  }
 }

 public void UpdateXLS() throws Exception {
  Workbook wb = null;
  WritableWorkbook book = null;
  try {
   // Excel获得文件
   wb = Workbook.getWorkbook(new File("测试.xls"));

   // 打开一个文件的副本，并且指定数据写回到原文件
   book = Workbook.createWorkbook(new File("测试.xls"), wb);

   // 添加一个工作表
   WritableSheet sheet = book.createSheet("第二页", 1);

   sheet.addCell(new Label(0, 0, "第二页的测试数据"));

  } catch (Exception e) {
   System.out.println(e);
  } finally {
   book.write();
   book.close();
  }
 }
}