如何将页面中查询出的结果输出到 Excel 中？在线刷新狂等！！！

我覺得導出成csv文件合適一些，excel文件的話要寫組件的。
csv文件以\r\n換行，","換列
在php中通常是這樣的，而且還可以導入到mysql中

Web应用导出Excel报表的简单实现

    在Web应用中，很多数据经常要导出成Excel文档。用专门的生成真正的Excel文档的方式比较复杂，不太好用。
所以经常用一种简单的方式来实现，即将报表保存为HTML格式，然后用Excel打开。

实现方式：
    第一步,用JSP实现HTML版本的报表
    第二步,在该JSP页面头部设置response的ContentType为Excel格式
        <% response.setContentType("application/vnd.ms-excel;charset=GBK"); %>

http://www.tot.name/show/3/7/20050314150037.htm

Thanks: Cheney2Apollo(迷惘的程序人生) , infowain(infowain)

以前用过Excel,但不是很熟悉.在http://jakarta.apache.org/poi下栽poi-bin-2.5.1-20040804.zip的压缩包,解压后把poi-2.5.1-final-20040804.jar放在WEB-INF/lib下.
利用POI创建Excel工作簿.HSSFWorkbook(工作簿) HSSFSheet(工作表) HSSFROW(行) HSSFCell(单元格).要是楼主不急着要的话,我倒是可以写一点程序给楼主做参考.

<%@ page contentType="text/html";charset="gb2312"%>
<%@ page import="java.io.*,org.apache.poi.hssf.usemodel.*"%><%
   HSSWorkbook workbook=new HSSFWorkbook();
   HSSFSheet sheet=workbook.createSheet("我的工作簿");   HSSFRow row=sheet.createRow((short)0);//第一行
   HSSFCell cell=row.createCell((short)0);
   cell.setEncoding(HSSFCell.ENCODING_UTF_16);
   cell.setCellValue("作者");
   cell=row.createCell((short)1);
   cell.setEncoding(HSSFCell.ENCODING_UTF_16);
   cell.setCellValue("编辑");   row=sheet.createRow((short)1);//第二行
   cell=row.createCell((short)0);
   cell.setEncoding(HSSFCell.ENCODING_UTF_16);
   cell.setCellValue("张三");
   cell=row.createCell((short)1);
   cell.setEncoding(HSSFCell.ENCODING_UTF_16);
   cell.setCellValue("李四");   String filename=appilcation.getRealPath("/")+"test.xls";
   FileOutputStream fOut=new FileOutputStream(filename);
   workbook.write(fOut);
   fOut.flush();
   fOut.close;   out.println("excel文件生成,存放在<br>"+filename);
%>这里只能抛砖引玉了,其他的要楼主自己想办法了.如果要读取Excel文件,也可以给个参考~(呵呵~才学Struts不久,就发现爱上她了~~~)

To:sxy0713(沈骏) :thanks! 不过我还是希望您你说的详细一点儿...

/**
   * 統計Excelファイル書く
   * @param fileName folderName+fileName
   * @param value List
   * @throws IOException
   */
  public static void writeCountExcelFile(String fileName, List value, String data, String deptName) throws
   IOException{
   CountResultForm countResultForm = null;
   try {        //workbook作成
        HSSFWorkbook wb = new HSSFWorkbook();        //sheet作成
        HSSFSheet sheet = wb.createSheet();
        wb.setSheetName(0, "DataCount");
        //準備完了        //様式作成
        //タイトル文字様式
        HSSFFont font_Header = wb.createFont();
        font_Header.setFontName("DataCount");
        font_Header.setFontHeightInPoints( (short) 12);
        HSSFCellStyle cellStyle_Header = wb.createCellStyle();
        cellStyle_Header.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle_Header.setFont(font_Header);
        //普通行様式
        HSSFCellStyle cellStyle_Normal = wb.createCellStyle();
        cellStyle_Normal.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle_Normal.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle_Normal.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle_Normal.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle_Normal.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //ヘッダ様式
        HSSFCellStyle cellStyle_Column = wb.createCellStyle();
        cellStyle_Column.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle_Column.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        cellStyle_Column.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle_Column.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle_Column.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle_Column.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle_Column.setFillPattern(HSSFCellStyle.BIG_SPOTS);
        cellStyle_Column.setFillBackgroundColor( (short) WHITE.index);
        cellStyle_Column.setFillForegroundColor(GREY_25_PERCENT.index);
        //データ行様式
        HSSFCellStyle cellStyle_Cell = wb.createCellStyle();
        cellStyle_Cell.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle_Cell.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        cellStyle_Cell.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle_Cell.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle_Cell.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle_Cell.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle_Cell.setWrapText(true);
        //様式完了
        //ヘッダを設定する。
        HSSFRow row = sheet.createRow( (short) 0);
        HSSFCell cell = row.createCell( (short) 0);
        cell.setEncoding(HSSFWorkbook.ENCODING_UTF_16);
        cell.setCellValue(data + deptName + "出勤統計表");
        sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) 8));
        cell.setCellStyle(cellStyle_Header);
        //ヘッダ部完了
        //行信息
        row = sheet.createRow( (short) 1);
        setCreateCell(row, (short) 0, "名前", cellStyle_Normal);
        setCreateCell(row, (short) 1, "出勤日数", cellStyle_Normal);
        setCreateCell(row, (short) 2, "出勤総時間", cellStyle_Normal);
        setCreateCell(row, (short) 3, "遅刻回数", cellStyle_Normal);
        setCreateCell(row, (short) 4, "早退回数", cellStyle_Normal);
        setCreateCell(row, (short) 5, "残業時間", cellStyle_Normal);
        setCreateCell(row, (short) 6, "残業回数", cellStyle_Normal);
        setCreateCell(row, (short) 7, "残業日数週末", cellStyle_Normal);
        setCreateCell(row, (short) 8, "缺勤日数", cellStyle_Normal);

        //表数据
        int size = value.size();
        for (int mIndex = 2; mIndex < size + 2; mIndex++) {
         countResultForm = (CountResultForm)value.get(mIndex - 2);
         row = sheet.createRow( (short) mIndex);

         //名前
         setCreateCell(row, (short) 0, countResultForm.getEmployeeName(), cellStyle_Normal);            //出勤日数
         setCreateCell(row, (short) 1, countResultForm.getWorkDays(), cellStyle_Normal);            //出勤総時間
         setCreateCell(row, (short) 2, countResultForm.getAttendanceTimes(), cellStyle_Normal);            //遅刻回数
         setCreateCell(row, (short) 3, countResultForm.getLateDays(), cellStyle_Normal);

            //早退回数
            setCreateCell(row, (short) 4, countResultForm.getExitEarlyDays(), cellStyle_Normal);            //残業時間
            setCreateCell(row, (short) 5, countResultForm.getRestTimes(), cellStyle_Normal);

            //残業回数
            setCreateCell(row, (short) 6, countResultForm.getRestDays(), cellStyle_Normal);

            //残業日数週末
            setCreateCell(row, (short) 7, countResultForm.getAddWeekCnt(), cellStyle_Normal);

            //缺勤日数
            setCreateCell(row, (short) 8, countResultForm.getNoWorkDays(), cellStyle_Normal);
        }
        //全局
        sheet.setHorizontallyCenter(true);
        for (int kIndex = 0; kIndex < 9; kIndex++) {
          sheet.setColumnWidth( (short) kIndex, (short) 3000);
        }
        sheet.setMargin(HSSFSheet.BottomMargin, (double) 0.5);
        sheet.setMargin(HSSFSheet.LeftMargin, (double) 0.1);
        sheet.setMargin(HSSFSheet.RightMargin, (double) 0.1);
        sheet.setMargin(HSSFSheet.TopMargin, (double) 0.5);
        //入力完了        //新規出力文件流
        FileOutputStream fOut = new FileOutputStream(fileName);
        // 保存する。
        wb.write(fOut);
        fOut.flush();
        //作成完了、ファイルを閉じる。
        fOut.close();
        System.out.println("ファイル作成完了！");      }
      catch (Exception e) {
        System.out.println(" xlCreate() : " + e);
      }
    }

楼主兄弟忙,中午给你讨论
[email protected]

import java.io.*;
import jxl.*;
import jxl.write.*;
import jxl.format.*;
import java.util.*;
import java.awt.Color;public class Test{
  public static void writeExcel(OutputStream os) throws Exception {
     jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(os);
     //新建一个工作表
     jxl.write.WritableSheet ws = wwb.createSheet("TestSheet1", 0);
     jxl.write.Label labelC = new jxl.write.Label(0, 0, "我爱中国");
     //加入要写的内容
     ws.addCell(labelC);
     //格式控制
     jxl.write.WritableFont wfc = new jxl.write.WritableFont(WritableFont.ARIAL,10, WritableFont.BOLD, false,
     UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.GREEN);
     jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(wfc);
     wcfFC.setBackground(jxl.format.Colour.RED);
     //准备写入的坐标 (9.0) 坐标 (行，列)
     labelC = new jxl.write.Label(9, 0, "中国爱我",wcfFC);
     ws.addCell(labelC);
     //写入Exel工作表
     wwb.write();
     //关闭Excel工作薄对象
     wwb.close();
  }  public static void main(String[] args)throws Exception{
     File f=new File("kk.xls");
     f.createNewFile();
     writeExcel(new FileOutputStream(f));
  }
} /* JSP 页面调用<%@page import="com.jagie.test.Test" %>
<%
   response.reset();
   response.setContentType("application/vnd.ms-excel");
   Test.writeExcel(response.getOutputStream());
%>*/这里用到了 JXL 这个包,你可以上网找一下.不行的话我可以发给你.
上面只是一个简单的例子,我想做做修改就可以满足你的需要了.

要这么麻烦？是不是可以把生成的sql语句传递过去，然后根据这个sql生成csv文件呢？原来在asp里面做过，需要fso文件支持，现在用java不知道怎么搞了

直接发给客户端Html文件是最好的,性能上比用jxl,poi高多了. 因为excell能读懂Html文件. 象infowain(infowain)说的..
但我想借这个地方问个问题: 怎样采用Mvc模式, 用扩展的jtl来生成Html文件呢,,听说freeer是可以通过模板文件, 加上Model(数据). 生成一个Html文件, 不过没具体看过,但我想用Jstl来做,,有谁知道怎么搞么??

建议用Ireport工具。简单、好用

用Apache的POI还有一个专门处理Excel的JXL的库都可以很方便的实现对Excel文档的处理

我试验过的<% response.setContentType("application/vnd.ms-excel;charset=GBK"); %>这个，不是很好用，因为这样分页的内容不能访问出来，而且页面上其他因素的东西也会生长到表里，这样给白痴用户他会用不懂，有时候考虑问题不能光站在实现功能上，而且要站在客户角度上去考虑，有时候客户没有你想的那么聪明。

谢谢：chuan122345() 和 laobu(老补)，不过我现在还是没有自己的思路...

Core Servlets and JavaServer Pages™: Volume 1: Core Technologies, 2nd Edition12.3 Conditionally Generating Excel SpreadsheetsIn most cases in which you generate non-HTML content with JSP, you know the content type in advance. In those cases, the contentType attribute of the page directive is appropriate: it requires no explicit Java syntax and can appear anywhere in the page.Occasionally, however, you may want to build the same content, but change the listed content type depending on the situation. For example, many word processing and desktop publishing systems can import HTML pages. So, you could arrange to have the page come up either in the publishing system or in the browser, depending on the content type you send. Similarly, Microsoft Excel can import tables that are represented in HTML with the TABLE tag. This capability suggests a simple method of returning either HTML or Excel content, depending on which the user prefers: just use an HTML table and set the content type to application/vnd.ms-excel only if the user requests the results in Excel.Unfortunately, this approach brings to light a small deficiency in the page directive: attribute values cannot be computed at runtime, nor can page directives be conditionally inserted as can template text. So, the following attempt results in Excel content regardless of the result of the checkUserRequest method.<% boolean usingExcel = checkUserRequest(request); %>
<% if (usingExcel) { %>
<%@ page contentType="application/vnd.ms-excel" %>
<% } %>Fortunately, there is a simple solution to the problem of conditionally setting the content type: just use scriptlets and the normal servlet approach of response.setContentType, as in the following snippet:<%
String format = request.getParameter("format");
if ((format != null) && (format.equals("excel"))) {
  response.setContentType("application/vnd.ms-excel");
}
%>For example, we once worked on a project that displayed financial (budget) information to authorized users. The data could be displayed in a table in a regular Web page if the user merely wanted to review it, or it could be placed into an Excel spreadsheet if the user wanted to put it into a report. When we first joined the project, there were two entirely separate pieces of code for each task. We changed it to build the same HTML table either way and to merely change the content type. Voila!Listing 12.3 shows a page that uses this approach; Figures 12-5 and 12-6 show the results. In a real application, of course, the data would almost certainly come from a database. We use static values here for simplicity, but see Chapter 17 (Accessing Databases with JDBC) for information on talking to relational databases from servlets and JSP pages.Listing 12.3 ApplesAndOranges.jsp (continued)
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<TITLE>Comparing Apples and Oranges</TITLE>
<LINK REL=STYLESHEET
      HREF="JSP-Styles.css"
      TYPE="text/css">
</HEAD><BODY>
<CENTER>
<H2>Comparing Apples and Oranges</H2>
<%
String format = request.getParameter("format");
if ((format != null) && (format.equals("excel"))) {
  response.setContentType("application/vnd.ms-excel");
}
%>
<TABLE BORDER=1>
  <TR><TH></TH>         <TH>Apples<TH>Oranges
  <TR><TH>First Quarter <TD>2307  <TD>4706
  <TR><TH>Second Quarter<TD>2982  <TD>5104
  <TR><TH>Third Quarter <TD>3011  <TD>5220
  <TR><TH>Fourth Quarter<TD>3055  <TD>5287
</TABLE>
</CENTER></BODY></HTML>

楼主如果想用简单的方法还是用<%@ page contentType="application/vnd.ms-excel" %>吧，把下面的代码存为JSP文件就可以了。
注意：输入的时候要把<tab>换成真正的tab键，JSP生成Excel文件的时候是用tab键来分隔每一列、用回车来分隔每一行的First<tab>Last<tab>Email Address
Marty<tab>Hall<tab>[email protected]
Larry<tab>Brown<tab>[email protected]
Steve<tab>Balmer<tab>[email protected]
Scott<tab>McNealy<tab>[email protected]
<%@ page contentType="application/vnd.ms-excel" %>而且还要注意一点，尽量把<%@ page contentType="application/vnd.ms-excel" %>放在最后一行，不然生成的Excel上面会有一行空行

一个简单的方法,用javascript做的:
<script language="JavaScript" type="text/JavaScript">
function ck()
{
   var newWin = window.open("print");
   newWin.document.write(mytable.innerHTML);
}
function AutomateExcel()
{
   var oXL = new ActiveXObject("Excel.Application");
   oXL.DisplayAlerts = false
   var oWB = oXL.Workbooks.Add();
   var oSheet = oWB.ActiveSheet;
   var hang = document.all.outtable.rows.length;
   var lie = document.all.outtable.rows(1).cells.length;
   for (i=0;i<hang;i++)
   {
    for (j=0;j<lie;j++)
    {
      oSheet.Cells(i+1,j+1).value = document.all.outtable.rows(i).cells(j).innerText; //outtable为你要导为excel的Table的ID
    }    }
   oXL.Visible = true;
   oXL.UserControl = true;
}
</script>然后在结果处加上:
<input type="button" name="outprint" onclick="ck();" value="导出打印表格" class="button" style="width:80px;">
<div id="mytable">
查询的结果
.....
</div>这个的功能比较简单

Thanks yangyijieyyj
不过我改如何的将 input 与我的excel联系起来呢？

input 只是调用了js脚本，注意： onclick="ck();" 自己多试一下

调试易

如何将页面中查询出的结果输出到 Excel 中？在线刷新狂等！！！

解决方案 »

如何将页面中查询出的结果输出到 Excel 中？ 在线刷新狂等！！！

解决方案 »

如何将页面中查询出的结果输出到 Excel 中？在线刷新狂等！！！