如何將JSP中將查詢結果導出為Excel，其實可以利用jakarta提供的POI接口將查詢結果導出到excel。POI接口是jakarta組織的一個子項目，它包括POIFS，HSSF，HWSF，HPSF，HSLF，目前比較成熟的是HSSF，它是一組操作微軟的excel文檔的API，現在到達3.0版本，已經能夠支持將圖片插入到excel里面。java 代碼

import java.io.*;   

1. import org.apache.poi.hssf.usermodel.*;   
2. import org.apache.poi.hssf.util.*;   
3. import org.apache.poi.hssf.usermodel.contrib.*;   
4. import javax.imageio.ImageIO;   
5.   
6. public class Main {   
7.     public Main() {   
8.     }   
9.     //演示如何創建一個工作簿   
10.     //用這種方法創建的工作簿有問題，用Excel打開以后發現下面Tab沒有選中   
11.     public static void ex1() {   
12.         try {   
13.             org.apache.poi.hssf.usermodel.HSSFWorkbook wb = new org.apache.poi.   
14.                 hssf.usermodel.HSSFWorkbook();   
15.             java.io.FileOutputStream fileOut = new java.io.FileOutputStream(   
16.                 "ex1.xls");   
17.             wb.write(fileOut);   
18.             fileOut.close();   
19.         }   
20.         catch (Exception eee) {   
21.             eee.printStackTrace();   
22.         }   
23.     }   
24.     //演示如何創建一個工作表   
25.     public static void ex2() {   
26.         try {   
27.             org.apache.poi.hssf.usermodel.HSSFWorkbook wb = new org.apache.poi.   
28.                 hssf.usermodel.HSSFWorkbook();   
29.             org.apache.poi.hssf.usermodel.HSSFSheet sheet1 = wb.createSheet(   
30.                 "new sheet");   
31.             org.apache.poi.hssf.usermodel.HSSFSheet sheet2 = wb.createSheet(   
32.                 "second sheet");   
33.             java.io.FileOutputStream fileOut = new java.io.FileOutputStream(   
34.                 "ex2.xls");   
35.             wb.write(fileOut);   
36.             fileOut.close();   
37.         }   
38.         catch (Exception eee) {   
39.             eee.printStackTrace();   
40.         }   
41.     }   
42.     //演示如何創建一個單元格   
43.     public static void ex3() {   
44.         try {   
45.             org.apache.poi.hssf.usermodel.HSSFWorkbook wb = new org.apache.poi.   
46.                 hssf.usermodel.HSSFWorkbook();   
47.             org.apache.poi.hssf.usermodel.HSSFSheet sheet = wb.createSheet(   
48.                 "new sheet");   
49.             // Create a row and put some cells in it. Rows are 0 based.   
50.             org.apache.poi.hssf.usermodel.HSSFRow row = sheet.createRow( (short)   
51.                 0);   
52.             // Create a cell and put a value in it.   
53.             org.apache.poi.hssf.usermodel.HSSFCell cell = row.createCell( (short)   
54.                 0);   
55.             cell.setCellValue(1);   
56.             // Or do it on one line.   
57.             row.createCell( (short) 1).setCellValue(1.2);   
58.             row.createCell( (short) 2).setCellValue("This is a string");   
59.             row.createCell( (short) 3).setCellValue(true);   
60.             // Write the output to a file   
61.             java.io.FileOutputStream fileOut = new java.io.FileOutputStream(   
62.                 "ex3.xls");   
63.             wb.write(fileOut);   
64.             fileOut.close();   
65.         }   
66.         catch (Exception eee) {   
67.             eee.printStackTrace();   
68.         }   
69.     }   
70.     //演示如何操作自選圖形   
71.     public static void ex27() {   
72.         try {   
73.             HSSFWorkbook wb = new HSSFWorkbook();   
74.             HSSFSheet sheet = wb.createSheet("new sheet");   
75.             HSSFPatriarch patriarch = sheet.createDrawingPatriarch();   
76.             HSSFClientAnchor a = new HSSFClientAnchor(0, 0, 1023, 255,   
77.                 (short) 1, 0, (short) 1, 0);   
78.             HSSFSimpleShape shape1 = patriarch.createSimpleShape(a);   
79.             shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);   
80.             HSSFTextbox textbox1 = patriarch.createTextbox(   
81.                 new HSSFClientAnchor(0, 0, 0, 0, (short) 1, 1, (short) 2, 2));   
82.             textbox1.setString(new HSSFRichTextString("This is a test"));   
83.             HSSFFont font = wb.createFont();   
84.             font.setItalic(true);   
85.             font.setUnderline(HSSFFont.U_DOUBLE);   
86.             HSSFRichTextString string = new HSSFRichTextString("Woo!!!");   
87.             string.applyFont(2, 5, font);   
88.             textbox1.setString(string);   
89.             // Create a shape group.   
90.             HSSFShapeGroup group = patriarch.createGroup(   
91.                 new HSSFClientAnchor(0, 0, 900, 200, (short) 2, 2, (short) 2, 2));   
92.             // Create a couple of lines in the group.   
93.             shape1 = group.createShape(new HSSFChildAnchor(3, 3, 500, 500));   
94.             shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);   
95.             ( (HSSFChildAnchor) shape1.getAnchor()).setAnchor( (short) 3, 3,   
96.                 500, 500);   
97.             HSSFSimpleShape shape2 = group.createShape(new HSSFChildAnchor( (short)   
98.                 1, 200, 400, 600));   
99.             shape2.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);   
100.             group.setCoordinates(10, 10, 20, 20); // top-left, bottom-right   
101.             FileOutputStream fileOut = new FileOutputStream("ex27.xls");   
102.             wb.write(fileOut);   
103.             fileOut.close();   
104.         }   
105.         catch (Exception eee) {   
106.             eee.printStackTrace();   
107.         }   
108.     }   
109.     //演示如何設定outline   
110.     public static void ex28() {   
111.         try {   
112.             HSSFWorkbook wb = new HSSFWorkbook();   
113.             HSSFSheet sheet1 = wb.createSheet("new sheet");   
114.             sheet1.groupRow(5, 14);   
115.             sheet1.groupRow(7, 14);   
116.             sheet1.groupRow(16, 19);   
117.             sheet1.groupColumn( (short) 4, (short) 7);   
118.             sheet1.groupColumn( (short) 9, (short) 12);   
119.             sheet1.groupColumn( (short) 10, (short) 11);   
120.             FileOutputStream fileOut = new FileOutputStream("ex28.xls");   
121.             wb.write(fileOut);   
122.             fileOut.close();   
123.         }   
124.         catch (Exception eee) {   
125.             eee.printStackTrace();   
126.         }   
127.     }   
128.     //演示如何設定outline   
129.     public static void ex29() {   
130.         try {   
131.             HSSFWorkbook wb = new HSSFWorkbook();   
132.             HSSFSheet sheet = wb.createSheet("new sheet");   
133.             HSSFPatriarch patriarch = sheet.createDrawingPatriarch();   
134.             HSSFClientAnchor a = new HSSFClientAnchor(0, 0, 1023, 255,   
135.                 (short) 1, 0, (short) 1, 0);   
136.             HSSFShapeGroup group = patriarch.createGroup(a);   
137.             group.setCoordinates(0, 0, 80 * 4, 12 * 23);   
138.             float verticalPointsPerPixel = a.getAnchorHeightInPoints(sheet) /   
139.                 (float) Math.abs(group.getY2() - group.getY1());   
140.             EscherGraphics g = new EscherGraphics(group, wb,   
141.                                                   java.awt.Color.black,   
142.                                                   verticalPointsPerPixel);   
143.             EscherGraphics2d g2d = new EscherGraphics2d(g);   
144.             //drawChemicalStructure( g2d );   
145.             FileOutputStream fileOut = new FileOutputStream("ex29.xls");   
146.             wb.write(fileOut);   
147.             fileOut.close();   
148.         }   
149.         catch (Exception eee) {   
150.             eee.printStackTrace();   
151.         }   
152.     }   
153.     //演示如何處理圖片   
154.     public static void ex30() {   
155.         try {   
156.             HSSFWorkbook wb = new HSSFWorkbook();   
157.             HSSFSheet sheet = wb.createSheet("new sheet");   
158.             HSSFPatriarch patriarch = sheet.createDrawingPatriarch();   
159.             HSSFClientAnchor anchor;   
160.             anchor = new HSSFClientAnchor(0, 0, 0, 255, (short) 2, 2, (short) 4,   
161.                                           7);   
162.             anchor.setAnchorType(2);   
163.             patriarch.createPicture(anchor,   
164.                                     loadPicture(   
165.                 "1.jpg",   
166.                wb));   
167.             FileOutputStream fileOut = new FileOutputStream("ex30.xls");   
168.             wb.write(fileOut);   
169.             fileOut.close();   
170.         }   
171.         catch (Exception eee) {   
172.             eee.printStackTrace();   
173.         }   
174.     }   
175.     private static int loadPicture(String filePath, HSSFWorkbook wb) {   
176.         int result = 0;   
177.         try {   
178.             ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();   
179.             java.awt.image.BufferedImage bufferImg = ImageIO.read(new File(   
180.                 filePath));   
181.             ImageIO.write(bufferImg,"jpg",byteArrayOut);   
182.             result = wb.addPicture(byteArrayOut.toByteArray(),HSSFWorkbook.PICTURE_TYPE_JPEG);   
183.         }   
184.         catch (Exception e) {   
185.             e.printStackTrace();   
186.         }   
187.         return result;   
188.     }   
189. }  

java中利用poi創建和讀取excel文件

    前面做了個項目，要求利用java動態讀取數據庫中的數據到excel文件供客戶下載，同時把客戶上傳的excel文件讀入到數據庫中。

    查了些資料，最后用的是poi-2.5.1-final-20040804.jar，完全可以滿足要求。下面以一段程序為例，說明poi的用法:

/*
 * Created on 2006-8-4
 *
 * TODO To change the template for this generated file go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
package com.crm.web.action;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Calendar;
import java.util.Vector;

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

/*這一塊導入相關類*/

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/*這一塊導入相關類*/
import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;

import com.crm.dao.CompanyAccountsDAO;
import com.crm.dao.CustomerDAO;
import com.crm.dao.CustomerLinkmanDAO;
import com.crm.dao.MarketOpportunityDAO;
import com.crm.dao.MembersDAO;
import com.crm.dao.OrderDAO;
import com.crm.dao.QuotePriceDAO;
import com.crm.dao.ScheduleDAO;
import com.crm.dto.CustomerDTO;
import com.crm.web.form.CustomerForm;

/**
 * @author new
 *
 * TODO To change the template for this generated type comment go to Window -
 * Preferences - Java - Code Style - Code Templates
 */
public class CustomerAction extends Action {
 public ActionForward execute(ActionMapping mapping, ActionForm form,
   HttpServletRequest request, HttpServletResponse response) {
  CustomerForm customerForm = (CustomerForm) form;
  if (customerForm.getOperType() != null
    && customerForm.getOperType().equals("exportCustomer")) {
   /* 導出客戶 */
   String companyID = (String) request.getSession().getValue(
     "companyID");
   CustomerDAO customerDAO = new CustomerDAO();
   String accountsType = (String) request.getSession().getValue(
     "accountsType");
   String accounts = (String) request.getSession()
     .getValue("accounts");
   String department = (String) request.getSession().getValue(
     "department");
   Vector customerList = new Vector();
   if (accountsType.equals("1")) {
    customerList = ((Vector) customerDAO.findEmployeeCustomer(
      Integer.parseInt(accounts), "0"));
   } else if (accountsType.equals("2")) {
    customerList = ((Vector) customerDAO.findDepartmentCustomer(
      Integer.parseInt(companyID), Integer
        .parseInt(department), "0"));
   } else if (accountsType.equals("3")) {
    customerList = ((Vector) customerDAO.findCompanyCustomer(
      Integer.parseInt(companyID), "0"));
   }
   try {
    // 創建新的Excel 工作簿
    HSSFWorkbook workbook = new HSSFWorkbook();
    // 在Excel工作簿中建一工作表，其名為缺省值
    // 如要新建一名為"效益指標"的工作表，其語句為：
    // HSSFSheet sheet = workbook.createSheet("效益指標");

/*或者不提供參數，創建一個無名工作表*/
    HSSFSheet sheet = workbook.createSheet();
    // 在索引0的位置創建行（最頂端的行）
    HSSFRow row = sheet.createRow((short) 0);
    //在索引0的位置創建單元格（左上端）
    HSSFCell cell;
    //= row.createCell((short) 0);
    // 定義單元格為字符串類型
    //cell.setCellType(HSSFCell.CELL_TYPE_STRING);
    //設置cell編碼解決中文高位字節截斷
    //cell.setEncoding(HSSFCell.ENCODING_UTF_16);
    // 在單元格中輸入一些內容
    int i = 0;
    cell = row.createCell((short) i++);
    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
    cell.setEncoding(HSSFCell.ENCODING_UTF_16);
    cell.setCellValue("客戶信息ID（customerInformationID）");
    cell = row.createCell((short) i++);
    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
    cell.setEncoding(HSSFCell.ENCODING_UTF_16);
    cell.setCellValue("公司名稱（我的公司）companyName");
    cell = row.createCell((short) i++);
    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
    cell.setEncoding(HSSFCell.ENCODING_UTF_16);
    cell.setCellValue("客戶名稱customerName");
    cell = row.createCell((short) i++);
    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
    cell.setEncoding(HSSFCell.ENCODING_UTF_16);
    cell.setCellValue("客戶簡稱nameForShort");
    cell = row.createCell((short) i++);
    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
    cell.setEncoding(HSSFCell.ENCODING_UTF_16);
    cell.setCellValue("公司地址companyAddress");

   // 下面是不斷從數據庫中讀數據到sheet中

      for (int j = 0; j < customerList.size(); j++) {
     CustomerDTO customerDTO = (CustomerDTO) customerList.get(j);
     i = 0;
     row = sheet.createRow((short) j + 1);
     cell = row.createCell((short) i++);
     cell.setCellType(HSSFCell.CELL_TYPE_STRING);
     cell.setEncoding(HSSFCell.ENCODING_UTF_16);
     cell.setCellValue(customerDTO.getCustomerInformationID());
     cell = row.createCell((short) i++);
     cell.setCellType(HSSFCell.CELL_TYPE_STRING);
     cell.setEncoding(HSSFCell.ENCODING_UTF_16);
     cell.setCellValue(customerDTO.getCompanyName());
     cell = row.createCell((short) i++);
     cell.setCellType(HSSFCell.CELL_TYPE_STRING);
     cell.setEncoding(HSSFCell.ENCODING_UTF_16);
     cell.setCellValue(customerDTO.getCustomerName());
     
    }
    // 新建一輸出文件流
    String outputFile = request.getRealPath("/")
      + "customer\\excel\\" + accounts + ".xls";
    /* 如果文件存在則刪除它 */
    File oldFile = new File(outputFile);
    if (oldFile.exists()) {
     oldFile.delete();
    }
    FileOutputStream fOut = new FileOutputStream(outputFile);
    // 把相應的Excel 工作簿存盤
    workbook.write(fOut);
    fOut.flush();
    // 操作結束，關閉文件
    fOut.close();
    customerForm.setExcelFilePath(outputFile);
    System.out.println("文件生成...");

   } catch (Exception e) {
    System.out.println("已運行 xlCreate() : " + e);
   }
   return mapping.findForward("exportCustomerSuccess");
  } else if (customerForm.getOperType() != null
    && customerForm.getOperType().equals("importCustomer")) {

/*下面是從excel中讀取數據的例子*/
   CustomerDAO customerDAO = new CustomerDAO();
   String accounts = (String) request.getSession()
     .getValue("accounts");

   String fileToBeRead = request.getRealPath("/")
     + "customer\\excel\\upload_" + accounts + ".xls";
   try {
    HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(
      fileToBeRead));
    HSSFSheet sheet = workbook.getSheetAt(0);
    /* 讀取文件中的信息，并把數據存入數據庫 */
    for (int j = 1;; j++) {
     HSSFRow row = sheet.getRow(j);
     if (row != null) {
      CustomerDTO customerDTO = new CustomerDTO();
      HSSFCell cell;
      int i = 0;
      /* 跳過第一列的customerInformationID */
      i++;
      cell = row.getCell((short) i++);
      customerDTO.setCompanyName(cell.getStringCellValue());
      cell = row.getCell((short) i++);
      customerDTO.setCustomerName(cell.getStringCellValue());
      cell = row.getCell((short) i++);
      customerDTO.setNameForShort(cell.getStringCellValue());
      cell = row.getCell((short) i++);
      customerDTO
        .setCompanyAddress(cell.getStringCellValue());
      cell = row.getCell((short) i++);
      customerDTO.setCountry(cell.getStringCellValue());
      cell = row.getCell((short) i++);
      customerDTO.setProvince(cell.getStringCellValue());
      cell = row.getCell((short) i++);
      customerDTO.setCity(cell.getStringCellValue());
      cell = row.getCell((short) i++);
      customerDTO.setPostalcode(cell.getStringCellValue());
      cell = row.getCell((short) i++);
      customerDTO.setCompanyHomePage(cell
        .getStringCellValue());
      
      customerDAO.insertCustomer(customerDTO);
     } else {
      break;
     }
    }
   } catch (Exception e) {
    e.printStackTrace();
   }

   /* 設置返回目標 */
   String companyID = (String) request.getSession().getValue(
     "companyID");

   String accountsType = (String) request.getSession().getValue(
     "accountsType");

   String department = (String) request.getSession().getValue(
     "department");
   CompanyAccountsDAO companyAccountsDAO = new CompanyAccountsDAO();
   if (accountsType.equals("1")) {
    customerForm.setCustomerList((Vector) customerDAO
      .findEmployeeCustomer(Integer.parseInt(accounts), "0"));
   } else if (accountsType.equals("2")) {
    customerForm.setCustomerList((Vector) customerDAO
      .findDepartmentCustomer(Integer.parseInt(companyID),
        Integer.parseInt(department), "0"));
    customerForm.setAccountsList((Vector) companyAccountsDAO
      .findDepartmentAccounts(companyID, department));
   } else if (accountsType.equals("3")) {
    customerForm.setCustomerList((Vector) customerDAO
      .findCompanyCustomer(Integer.parseInt(companyID), "0"));
    customerForm.setAccountsList((Vector) companyAccountsDAO
      .findAllAccounts(companyID));
   }
   return mapping.findForward("allCustomer");
  }

  return mapping.findForward("");
 }
}