需求描述:
用户点击OrderItemsToExcel.jsp后,直接弹出下载保存对话框,下载到本地保存。
约束条件:
只能用JSP实现并且不要调用java Bean,所有的代码都在jsp里完成,并且下载的文件名一定是中文名称。
遇到的问题:
1、 文件名乱码
2、生成的excel文件中文内容乱码
程序运行的环境:
系统运行在weblogic5.1.0, jdk1.2.2, AIX 4.3.3,oracle817数据库
因为是编码问题所以分析与编码有关的设置:
数据库编码UTF-8
JSP文件本身是UTF-8编码
页面输出的charset也是UTF-8
AIX的环境变量 LANG=Zh_CN
在网上找了很多关于java系统的编码问题,也知道从jsp到页面显示过程中要涉及几个编码转换的过程,JSP----->java,java----->class,从数据库---java,从web服务器到浏览器,但是网上的一些方法不能用,因为我们使用的是JDK1.2.2, weblogic5.1 版本都很低,不支持新方法如:URLEncoder.encode() 只支持一个参数,不支持两个参数。现在想起来可能是网上的给出的解决方法太多把自己给搞乱了,看看我的原代码其实很简单,网上的东西只能作为参考,因为不同的运行环境可能会有不同的结果。比如说jsp参数pageEncoding不是所有的web服务器都支持的。
下面是代码,把数据库信息省略
<%
String sorefitem = request.getParameter( " SOREFITEM " );
sorefitem = " 24326 " ;
String sheetname = " productsitems " ;
String excelsql = " select oi.sorefitem,po.psuppliercompany suppliername,oi.pname productname,oi.ivendpartnumber productcode,oi.oqty quantity,oi.osellprice price,oi.pricecurrefitem currency,po.ponumber "
+ " from ecorders.orderitems oi,ecorders.purchaseorders po "
+ " where (oi.ostatus>3 or oi.ostatus=3 and oi.osubstatus=4) AND oi.sorefitem= " + sorefitem
+ " AND oi.sorefitem=po.sorefitem and oi.osellerid=po.psupplierid " ;
int col_num = 0 ;
java.sql.Statement stm = null ;
java.sql.ResultSet rs = null ;
java.sql.Connection conn = null ; try ... {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e)
... { System.out.println ("no exist driver");
System.exit(0);
}
try
... {
response.reset();
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setHeader("Content-Disposition" ,"attachment;filename="+new String("导出采购商明细报表Excel.xls".getBytes(),"iso-8859-1"));
OutputStream os = response.getOutputStream();//将 WritableWorkbook 写入到输出流
jxl.write.WritableWorkbook wwb = jxl.Workbook.createWorkbook(os); //创建Excel工作表
jxl.write.WritableSheet ws = wwb.createSheet("sheet_1",0);
col_num = 7;
jxl.write.Label label_head;
label_head = new jxl.write.Label(0,0,"请购单号");
ws.addCell(label_head);
label_head = new jxl.write.Label(1,0,"供应商名");
ws.addCell(label_head);
label_head = new jxl.write.Label(2,0,"产品规格型号");
ws.addCell(label_head);
label_head = new jxl.write.Label(3,0,"产品编码");
ws.addCell(label_head);
label_head = new jxl.write.Label(4,0,"数量");
ws.addCell(label_head);
label_head = new jxl.write.Label(5,0,"单价");
ws.addCell(label_head);
label_head = new jxl.write.Label(6,0,"定单号");
ws.addCell(label_head);//结果集中的数据添加到excel中
jxl.write.Number label_num;
jxl.write.Label label_str;
jxl.write.WritableCellFormat floatFormat;
int numrow = 1;
System.out.println(System.currentTimeMillis()+" welcome to hehe!!!!!!!!!!!");
conn = java.sql.DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.180:1521:DB","system","manager");
stm = conn.createStatement();
rs = stm.executeQuery(excelsql);
while(rs.next())
...{
System.out.println(System.currentTimeMillis()+" welcome to while!!!!!!!!!");
label_num = new jxl.write.Number(0,numrow,rs.getInt("sorefitem"));
ws.addCell(label_num);
label_str = new jxl.write.Label(1,numrow,rs.getString("suppliername"));
ws.addCell(label_str);
label_str = new jxl.write.Label(2,numrow,rs.getString("productname"));
ws.addCell(label_str);
label_str = new jxl.write.Label(3,numrow,rs.getString("productcode"));
ws.addCell(label_str);
floatFormat = new jxl.write.WritableCellFormat (jxl.write.NumberFormats.FLOAT);
label_num = new jxl.write.Number(4,numrow,rs.getDouble("quantity"),floatFormat);
ws.addCell(label_num);
label_num = new jxl.write.Number(5,numrow,rs.getDouble("price"),floatFormat);
ws.addCell(label_num);
label_str = new jxl.write.Label(6,numrow,rs.getString("ponumber"));
ws.addCell(label_str);
numrow ++;
}
wwb.write();
wwb.close();
os.flush();
os.close();
}
catch (java.sql.SQLException sqle) ... {
System.out.println("sql error");
}
catch (Exception e) ... {
System.out.println("catch error ------");
e.printStackTrace();
}
finally ... {
System.out.println(System.currentTimeMillis()+ "welcome to finally!!!!!!!");
if(stm != null)
try...{
stm.close();
}catch(Exception fe) ...{ }
if(conn != null)
try...{
conn.close();
}
catch(Exception fe2)...{ }
}
%>
Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=1671508