java 在浏览器中显示 Excel 工作表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3511179/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Display Excel sheet in the Browser?
提问by Venkat
I need to displaythe newly generated excel(from tables using Apache POI) in a web browser(whatever it is, Firefox, Opera or IE). I've created the JSP file with
我需要在网络浏览器（无论是 Firefox、Opera 还是 IE）中显示新生成的 excel（来自使用 Apache POI 的表格）。我已经创建了 JSP 文件
contentType="application/vnd.ms-excel"
contentType="application/vnd.ms-excel"
But I'm not getting it.
但我不明白。
Here's my code snippet :
这是我的代码片段：
<%@page session="true" contentType="application/vnd.ms-excel" pageEncoding="UTF-8"%>
<%@page import="org.apache.poi.ss.usermodel.CellStyle"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@page import="org.apache.poi.ss.usermodel.CreationHelper"%>
<%@page import="org.apache.poi.hssf.usermodel.HSSFCell"%>
<%@page import="org.apache.poi.hssf.usermodel.HSSFRow"%>
<%@page import="org.apache.poi.hssf.usermodel.HSSFWorkbook"%>
<%@page import="org.apache.poi.hssf.usermodel.HSSFSheet"%>
<html>
<head>
<%!
int r=0;
HSSFWorkbook book;
HSSFSheet sheet;
HSSFRow row;
CreationHelper createHelper = book.getCreationHelper();
Connection conn;
Statement stmt;
ResultSet rs;
%>
<title>Report</title>
<%
book = new HSSFWorkbook();
sheet = book.createSheet("Report");
%>
</head>
<body>
<%
try {
// Header of the Excel File
row = sheet.createRow(r);
row.createCell(0).setCellValue("Visit ID");
row.createCell(1).setCellValue("Carrier Name");
row.createCell(2).setCellValue("Phone Number");
row.createCell(3).setCellValue("Patient Name");
row.createCell(4).setCellValue("Subscriber ID");
row.createCell(5).setCellValue("Subscriber Name");
row.createCell(6).setCellValue("Chart Number");
row.createCell(7).setCellValue("Date Of Birth");
row.createCell(8).setCellValue("Subscriber Employer");
row.createCell(9).setCellValue("Service Date");
row.createCell(10).setCellValue("Provider Name");
row.createCell(11).setCellValue("CPT Code");
row.createCell(12).setCellValue("Aging Date");
row.createCell(13).setCellValue("Total");
row.createCell(14).setCellValue("Follow Up Notes");
row.createCell(15).setCellValue("Internal Status Code");
CellStyle cellStyle = book.createCellStyle();
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("MM/dd/yyyy"));
Statement stNotes;
ResultSet rsNotes;
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/maintain", "root", "root");
stmt = conn.createStatement();
stNotes = conn.createStatement();
rs = stmt.executeQuery("SELECT b.VisitID, b.CarrierName, b.PhoneNum, b.PatientName, "
+ "b.SubscriberID, b.SubscriberName, b.ChartNum, b.DoB, b.SubscriberEmp, "
+ "b.ServiceDate, b.ProviderName, b.CPTCode, b.BillingDate, b.BalanceAmt "
+ "FROM billing b INNER JOIN followup f ON b.VisitID = f.VisitID GROUP BY VisitID");
while(rs.next()) {
r++;
row = sheet.createRow(r);
row.createCell(0).setCellValue(rs.getString("VisitID"));
row.createCell(1).setCellValue(rs.getString("CarrierName"));
row.createCell(2).setCellValue(rs.getString("PhoneNum"));
row.createCell(3).setCellValue(rs.getString("PatientName"));
row.createCell(4).setCellValue(rs.getString("SubscriberID"));
row.createCell(5).setCellValue(rs.getString("SubscriberName"));
row.createCell(6).setCellValue(rs.getString("ChartNum"));
row.createCell(7).setCellValue(rs.getString("DoB"));
row.createCell(8).setCellValue(rs.getString("SubscriberEmp"));
row.createCell(9).setCellValue(rs.getString("ServiceDate"));
row.createCell(9).setCellStyle(cellStyle);
row.createCell(10).setCellValue(rs.getString("ProviderName"));
row.createCell(11).setCellValue(rs.getString("CPTCode"));
row.createCell(12).setCellValue(rs.getString("BillingDate"));
row.createCell(12).setCellStyle(cellStyle);
row.createCell(13).setCellValue(rs.getString("BalanceAmt"));
rsNotes = stNotes.executeQuery("SELECT Date, InternalStatusCode, FollowUpNote "
+ "FROM followup WHERE VisitID='" + rs.getString("VisitID") + "' ORDER BY Date");
while(rsNotes.next()) {
row.createCell(14).setCellValue(rsNotes.getString("Date") + " - " + rsNotes.getString("FollowUpNote"));
row.createCell(15).setCellValue(rs.getString("VisitID"));
}
}
}
catch(ClassNotFoundException cnf) {
out.print("<br> Error : MySQL Driver not found. <br>");
}
catch(Exception ex) {
out.print("Error : <br>" + ex);
}
%>
</body>
</html>
I'm getting this exception with Tomcat 6.0.26 :
我在 Tomcat 6.0.26 中遇到此异常：
exception
org.apache.jasper.JasperException: java.lang.NullPointerException
org.apache.jasper.servlet.JspServletWrapper.getServlet(JspServletWrapper.java:156)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:329)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:313)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:260)
javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:393)
root cause
java.lang.NullPointerException
org.apache.jsp.GetReport_jsp.<init>(GetReport_jsp.java:29)
sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
java.lang.reflect.Constructor.newInstance(Constructor.java:513)
java.lang.Class.newInstance0(Class.java:355)
java.lang.Class.newInstance(Class.java:308)
org.apache.jasper.servlet.JspServletWrapper.getServlet(JspServletWrapper.java:145)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:329)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:313)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:260)
javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:393)
Did I miss something or anything wrong?Can anybody help me to get rid of this problem?
我错过了什么或有什么不对吗？有人能帮我解决这个问题吗？
Thanx in advance.
提前谢谢。
回答by Erich Kitzmueller
The way you are trying to do it doesn't make any sense. You can't mix HTML with Excel like that. Better create a servlet instead of a JSP page and let this servlet output only the Excel file and nothing else.
您尝试这样做的方式没有任何意义。您不能像那样将 HTML 与 Excel 混合使用。最好创建一个 servlet 而不是 JSP 页面，让这个 servlet 只输出 Excel 文件而不输出其他任何内容。
Something like this:
像这样的东西：
import java.io.*;
import javax.servlet.http.*;
import javax.servlet.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
// ... plus all the other libs you need
public class ExcelServlet extends HttpServlet {
public void doGet (HttpServletRequest req,
HttpServletResponse res)
throws ServletException, IOException
{
HSSFWorkbook book;
// ...
// fill the book
// ...
res.setContentType("application/vnd.ms-excel");
book.write(res.getOutputStream());
res.getOutputStream().close();
}
}
回答by amal
HSSFWorkbook book;
HSSFSheet sheet;
HSSFRow row;
CreationHelper createHelper = book.getCreationHelper();
You are using the book object before initializing it.
您在初始化之前使用 book 对象。
回答by duffymo
I'd recommend not doing it this way.
我建议不要这样做。
Scriptlet code in JSPs is simply wrong.
JSP 中的 Scriptlet 代码完全是错误的。
Putting database access in a page like this isn't good, either.
将数据库访问放在这样的页面中也不好。
A better approach would be Spring MVC and its JExcelView.
更好的方法是 Spring MVC 及其 JExcelView。
回答by matt b
Here's a tip on how to debug JSP exceptions:
这里有一个关于如何调试 JSP 异常的提示：
in the stacktrace, this class
在堆栈跟踪中，这个类
java.lang.NullPointerException
org.apache.jsp.GetReport_jsp.<init>(GetReport_jsp.java:29)
is the generated java class for your GetReport.jsp. You can look in the $TOMCAT_HOME/work/<enginename>/<hostname>/<appname>folder to see the actual code generated, and see what exactly is at line 29.
是为您的 GetReport.jsp 生成的 java 类。您可以查看$TOMCAT_HOME/work/<enginename>/<hostname>/<appname>文件夹以查看生成的实际代码，并查看第 29 行的确切内容。
回答by A. Masson
Take a look at WorkbookTag project from SourceForge which does exactly what you need: HTML worksheet rendering using Apache POI... WorkbookTag
查看 SourceForge 的 WorkbookTag 项目，它完全满足您的需求：HTML worksheet rendering using Apache POI... WorkbookTag
