在浏览器中显示 Excel 工作表? [英] Display Excel sheet in the Browser?
本文介绍了在浏览器中显示 Excel 工作表?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我需要显示新生成的 excel(来自使用 Apache POI 的表格)在网络浏览器中(无论是什么，Firefox，Opera 或 IE).我已经用
I need to display the 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
contentType="application/vnd.ms-excel"
但我不明白.
这是我的代码片段:
<%@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>
我在 Tomcat 6.0.26 中遇到此异常:
I'm getting this exception with 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?
提前谢谢.
推荐答案
您尝试这样做的方式没有任何意义.您不能像那样将 HTML 与 Excel 混合使用.最好创建一个 servlet 而不是 JSP 页面，让这个 servlet 只输出 Excel 文件而不输出其他任何内容.
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.
像这样:
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();
}
}
这篇关于在浏览器中显示 Excel 工作表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
