使用Apache POI创建和下载使用ahref链接的excel文件
问题描述:
我有一个JSP页面上的Excel文件列表,这些文件以表格形式存储在数据库中。他们是超链接。我需要下载文件,只要用户点击一个特定的文件。使用Apache POI创建和下载使用ahref链接的excel文件
我对它有以下代码。
JSP页面
<html>
<head>
<title></title>
</head>
<body>
<%
List<String> encrypted = (List<String>)session.getAttribute("encryptedlist");
List<String> original = (List<String>)session.getAttribute("originallist");
List<String> decrypted = (List<String>)session.getAttribute("decryptedlist");
%>
<table>
<tr>
<td style="font-weight: bold">Original Files</td>
<td style="font-weight: bold">Encrypted Files</td>
<td style="font-weight: bold">Decrypted Files</td>
</tr>
<%for(int i=0;i<size;i++) {%>
<tr>
<td><a href="DownloadServlet1?file=<%=original.get(i)%>"><%=original.get(i)%></a></td>
<td><a href="DownloadServlet1?file=<%=encrypted.get(i)%>"><%=encrypted.get(i)%></a></td>
<td><a href="DownloadServlet2?file=<%=decrypted.get(i)%>"><%=decrypted.get(i)%></a></td>
</tr>
<%}%>
</table>
</body>
</html>
SERVLET
public class DownloadServlet1 extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String file = request.getParameter("file");
System.out.println(file);
int columns = getCount(file);
System.out.println(columns);
List<String> columnNames = getColumnNames(file,columns);
System.out.println(columnNames);
saveFile(columnNames,columns,file,response);
response.sendRedirect("Login.jsp");
}
protected int getCount(String file){
//String sql = "select * from " + "`" + file + "`";
int col=0;
try {
Connection con = DBOperations.getConnection();
Statement st = con.createStatement();
ResultSet rs = st.executeQuery("SELECT * FROM " + file);
System.out.println("SELECT * FROM " + file);
ResultSetMetaData md = rs.getMetaData();
col = md.getColumnCount();
}catch (Exception ex)
{
System.out.println("getcount" + ex);
}
return col;
}
protected List<String> getColumnNames(String file, int columns)
{
List<String> columnNames = new ArrayList<>();
try {
Connection con = DBOperations.getConnection();
Statement st = con.createStatement();
String sql = "Select * from " + file;
System.out.println("getcolumnnames " + sql);
ResultSet rs = st.executeQuery(sql);
System.out.println("SELECT * FROM " + file);
ResultSetMetaData md = rs.getMetaData();
for (int i = 1; i <= columns; i++){
String col_name = md.getColumnName(i);
columnNames.add(col_name);
//System.out.println(col_name);
}
}catch (Exception ex)
{
System.out.println("get column names"+ex);
}
return columnNames;
}
protected void saveFile(List<String> columnNames,int columns,String file,HttpServletResponse response)
{
Connection con = DBOperations.getConnection();
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment; filename="+file+".xls");
String excelFileName = file;
String sheetName = "Sheet1";//name o sheet
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet(sheetName) ;
//iterating r number of rows
for (int r=0;r < 1; r++)
{
XSSFRow row = sheet.createRow(r);
//iterating c number of columns
for (int c=0;c < columns; c++)
{
XSSFCell cell = row.createCell(c);
cell.setCellValue(columnNames.get(c));
}
}
try
{
String sql = "Select * from " + file;
System.out.println(sql);
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(sql);
int r=1;
while(rs.next())
{
XSSFRow row = sheet.createRow(r);
for(int c=1;c<columns;c++)
{
XSSFCell cell = row.createCell(c);
cell.setCellValue(rs.getString(c));
}
r=r+1;
}
}catch (Exception ex)
{
System.out.println(ex);
}
try {
FileOutputStream fileOut = new FileOutputStream(excelFileName);
//write this workbook to an Outputstream.
wb.write(fileOut);
fileOut.flush();
fileOut.close();
}catch (Exception ex)
{
System.out.println(ex);
}
}
}
利用getCount函数返回的列数从数据库的文件。 getColumnNames获取所有列的名称。
这两个函数返回正确的值,这表明在访问数据库表
的saveFile的功能应该下载Excel文件没有问题。
我得到这个错误在浏览器上的网页
The webpage at http://localhost:8084/DownloadServlet1?file=Hil_1566869_27_08_17_20_24_18 might be temporarily down or it may have moved permanently to a new web address.
可能是什么问题呢?
答
直接写入Excel文档到HTTP响应:
wb.write(response.getOutputStream());
有一个在本地保存在服务器上的Excel文件没有意义的。
另外,不要重定向到Login.jsp
,这可能是导致错误的原因。
我在删除Login.jsp和wb.write(response.getOutpurStream())后得到相同的错误; – user3508140