JSP中将查询结果导出为excel

转自洋葱头的BLOG   原文：http://blog.sina.com.cn/u/3f6efc4a0100063o

  如何将JSP中将查询结果导出为Excel，其实可以利用jakarta提供的POI接口将查询结果导出到excel。POI接口是jakarta组织的一个子项目，它包括POIFS，HSSF，HWSF，HPSF，HSLF，目前比较成熟的是HSSF，它是一组操作微软的excel文档的API，现在到达3.0版本，已经能够支持将图片插入到excel里面。下面简要的介绍一下它的用法，在这里http://apache.justdn.org/jakarta/poi/dev/bin/可以下载到它的最新版，下载下来以后，将它解压，并将其中的poi-3.0-alpha1-20050704.jar，poi-contrib-3.0-alpha1-20050704.jar，poi-scratchpad-3.0-alpha1-20050704.jar加入到你的开发环境的classpath中（比如JBUILDER可以点击工具》configure jdks》add……）。在解压的目录中，有一个docs目录是一些文档，其中里面有两个目录特别有用，一个是hssf目录，一个是apidocs目录。我从它的英文文档中摘出一些例子，放在一个java文件里面，加上简单的注释，你可以下载这个java文件下来玩玩。下面是java文件的一部分。

java 代码

import java.io.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.*;
import org.apache.poi.hssf.usermodel.contrib.*;
import javax.imageio.ImageIO;

public class Main {
    public Main() {
    }
    //演示如何创建一个工作簿
    //用这种方法创建的工作簿有问题，用Excel打开以后发现下面Tab没有选中
    public static void ex1() {
        try {
            org.apache.poi.hssf.usermodel.HSSFWorkbook wb = new org.apache.poi.
                hssf.usermodel.HSSFWorkbook();
            java.io.FileOutputStream fileOut = new java.io.FileOutputStream(
                "ex1.xls");
            wb.write(fileOut);
            fileOut.close();
        }
        catch (Exception eee) {
            eee.printStackTrace();
        }
    }
    //演示如何创建一个工作表
    public static void ex2() {
        try {
            org.apache.poi.hssf.usermodel.HSSFWorkbook wb = new org.apache.poi.
                hssf.usermodel.HSSFWorkbook();
            org.apache.poi.hssf.usermodel.HSSFSheet sheet1 = wb.createSheet(
                "new sheet");
            org.apache.poi.hssf.usermodel.HSSFSheet sheet2 = wb.createSheet(
                "second sheet");
            java.io.FileOutputStream fileOut = new java.io.FileOutputStream(
                "ex2.xls");
            wb.write(fileOut);
            fileOut.close();
        }
        catch (Exception eee) {
            eee.printStackTrace();
        }
    }
    //演示如何创建一个单元格
    public static void ex3() {
        try {
            org.apache.poi.hssf.usermodel.HSSFWorkbook wb = new org.apache.poi.
                hssf.usermodel.HSSFWorkbook();
            org.apache.poi.hssf.usermodel.HSSFSheet sheet = wb.createSheet(
                "new sheet");
            // Create a row and put some cells in it. Rows are 0 based.
            org.apache.poi.hssf.usermodel.HSSFRow row = sheet.createRow( (short)
                0);
            // Create a cell and put a value in it.
            org.apache.poi.hssf.usermodel.HSSFCell cell = row.createCell( (short)
                0);
            cell.setCellValue(1);
            // Or do it on one line.
            row.createCell( (short) 1).setCellValue(1.2);
            row.createCell( (short) 2).setCellValue("This is a string");
            row.createCell( (short) 3).setCellValue(true);
            // Write the output to a file
            java.io.FileOutputStream fileOut = new java.io.FileOutputStream(
                "ex3.xls");
            wb.write(fileOut);
            fileOut.close();
        }
        catch (Exception eee) {
            eee.printStackTrace();
        }
    }
    //演示如何操作自选图形
    public static void ex27() {
        try {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet("new sheet");
            HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
            HSSFClientAnchor a = new HSSFClientAnchor(0, 0, 1023, 255,
                (short) 1, 0, (short) 1, 0);
            HSSFSimpleShape shape1 = patriarch.createSimpleShape(a);
            shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);
            HSSFTextbox textbox1 = patriarch.createTextbox(
                new HSSFClientAnchor(0, 0, 0, 0, (short) 1, 1, (short) 2, 2));
            textbox1.setString(new HSSFRichTextString("This is a test"));
            HSSFFont font = wb.createFont();
            font.setItalic(true);
            font.setUnderline(HSSFFont.U_DOUBLE);
            HSSFRichTextString string = new HSSFRichTextString("Woo!!!");
            string.applyFont(2, 5, font);
            textbox1.setString(string);
            // Create a shape group.
            HSSFShapeGroup group = patriarch.createGroup(
                new HSSFClientAnchor(0, 0, 900, 200, (short) 2, 2, (short) 2, 2));
            // Create a couple of lines in the group.
            shape1 = group.createShape(new HSSFChildAnchor(3, 3, 500, 500));
            shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);
            ( (HSSFChildAnchor) shape1.getAnchor()).setAnchor( (short) 3, 3,
                500, 500);
            HSSFSimpleShape shape2 = group.createShape(new HSSFChildAnchor( (short)
                1, 200, 400, 600));
            shape2.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);
            group.setCoordinates(10, 10, 20, 20); // top-left, bottom-right
            FileOutputStream fileOut = new FileOutputStream("ex27.xls");
            wb.write(fileOut);
            fileOut.close();
        }
        catch (Exception eee) {
            eee.printStackTrace();
        }
    }
    //演示如何设定outline
    public static void ex28() {
        try {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet1 = wb.createSheet("new sheet");
            sheet1.groupRow(5, 14);
            sheet1.groupRow(7, 14);
            sheet1.groupRow(16, 19);
            sheet1.groupColumn( (short) 4, (short) 7);
            sheet1.groupColumn( (short) 9, (short) 12);
            sheet1.groupColumn( (short) 10, (short) 11);
            FileOutputStream fileOut = new FileOutputStream("ex28.xls");
            wb.write(fileOut);
            fileOut.close();
        }
        catch (Exception eee) {
            eee.printStackTrace();
        }
    }
    //演示如何设定outline
    public static void ex29() {
        try {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet("new sheet");
            HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
            HSSFClientAnchor a = new HSSFClientAnchor(0, 0, 1023, 255,
                (short) 1, 0, (short) 1, 0);
            HSSFShapeGroup group = patriarch.createGroup(a);
            group.setCoordinates(0, 0, 80 * 4, 12 * 23);
            float verticalPointsPerPixel = a.getAnchorHeightInPoints(sheet) /
                (float) Math.abs(group.getY2() - group.getY1());
            EscherGraphics g = new EscherGraphics(group, wb,
                                                  java.awt.Color.black,
                                                  verticalPointsPerPixel);
            EscherGraphics2d g2d = new EscherGraphics2d(g);
            //drawChemicalStructure( g2d );
            FileOutputStream fileOut = new FileOutputStream("ex29.xls");
            wb.write(fileOut);
            fileOut.close();
        }
        catch (Exception eee) {
            eee.printStackTrace();
        }
    }
    //演示如何处理图片
    public static void ex30() {
        try {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet("new sheet");
            HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
            HSSFClientAnchor anchor;
            anchor = new HSSFClientAnchor(0, 0, 0, 255, (short) 2, 2, (short) 4,
                                          7);
            anchor.setAnchorType(2);
            patriarch.createPicture(anchor,
                                    loadPicture(
                "1.jpg",
               wb));
            FileOutputStream fileOut = new FileOutputStream("ex30.xls");
            wb.write(fileOut);
            fileOut.close();
        }
        catch (Exception eee) {
            eee.printStackTrace();
        }
    }
    private static int loadPicture(String filePath, HSSFWorkbook wb) {
        int result = 0;
        try {
            ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
            java.awt.image.BufferedImage bufferImg = ImageIO.read(new File(
                filePath));
            ImageIO.write(bufferImg,"jpg",byteArrayOut);
            result = wb.addPicture(byteArrayOut.toByteArray(),HSSFWorkbook.PICTURE_TYPE_JPEG);
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }
}

好好学习一下这些例子，基本上就能利用java对excel进行操作了。
　　那么JSP中查询页面导出到excel只要在服务器端利用HSSF形成xls文件，然后让用户来下载这些文件就行了。

分享到：

Hibernate包作用详解 | native2ascii的使用

• 2007-02-12 10:29
• 浏览 5011
• 评论(0)
• 论坛回复 / 浏览 (0 / 6001)
• 分类:企业架构
• 查看更多