方法1 最简单的方法使用Displaytag标签
1、需先下载对应的jar包解压后将
displaytag-1.1.jar、displaytag-export-poi-1.1.jar、displaytag-portlet-1.1.jar放入lib下
alternative.css、displaytag.css、screen.css放入文件里,此处是放在了displaytag_css
2、在web.xml中注册
<filter>
<filter-name>ResponseOverrideFilter</filter-name>
<filter-class>
org.displaytag.filter.ResponseOverrideFilter
</filter-class>
</filter>
<filter-mapping>
<filter-name>ResponseOverrideFilter</filter-name>
<url-pattern>*.do</url-pattern>
</filter-mapping>
<filter-mapping>
<filter-name>ResponseOverrideFilter</filter-name>
<url-pattern>*.jsp</url-pattern>
</filter-mapping>
3、页面中引入
<%@ taglib uri="http://displaytag.sf.net" prefix="display"%>
<%@ taglib uri="http://displaytag.sf.net/el" prefix="display-el"%>
<LINK href="./displaytag_css/alternative.css" type=text/css rel=stylesheet><!-- 标题控制样式 -->
<LINK href="./displaytag_css/displaytag.css" type=text/css rel=stylesheet><!-- 分页标签 -->
<LINK href="./displaytag_css/screen.css" type=text/css rel=stylesheet><!-- 分页标签 -->
<display:table name="create_list" export="true" class="mars" style="width: 60%; border-spacing:1px; border-collapse:1px;" >
<display:setProperty name="export.pdf" value="false" />
<display:setProperty name="export.xml" value="false" />
</display:table>
其中name="create_list"的create_list是action中经过查询数据库返回的list结果集后用request保存的域值
方法2 使用javaexcel(jxl)实现
1、需先下载对应的jar包下载地址:http://www.andykhan.com/jexcelapi/jexcelapi_2_6_3.tar.gz
解压后将jxl.jar放入lib下
2、public ActionForward down_excel(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws Exception {
try {
String fileName = "test";
response.setContentType("aplication/msexcel"); //设置文件类型
response.setHeader("Content-disposition","attachment; filename=" + fileName + ".xls");
WritableWorkbook book = Workbook.createWorkbook(response.getOutputStream()); //创建文件
WritableSheet sheet = book.createSheet("sheet", 0); //创建工作薄
jxl.write.WritableFont titleFont = new jxl.write.WritableFont(WritableFont.ARIAL, 12, WritableFont.BOLD, false,UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.DARK_BLUE);
jxl.write.WritableCellFormat titleCellFormat = new jxl.write.WritableCellFormat(titleFont);
//设置表头
String[] titles = new String[]{ "手机号码", "订购栏目", "栏目价格", "订购退订时间", "发送时间", "订购来源" , "计费情况","业务状态" };
int columnLength[] = { 20, 30, 10, 30, 20, 15, 15,15 };
for (int i = 0; i < titles.length; i++) {
sheet.setColumnView(i, columnLength[i]); //设置列的宽度
Label label = new Label(i, 0, titles[i], titleCellFormat);
sheet.addCell(label);
}
//动态填充数据
Vector vector = this.getDownExcelDao().excel();
for (int i = 0; i < vector.size(); i++) {
String[] sdata = (String[]) vector.elementAt(i);
for (int j = 0; j < sdata.length; j++) {
Label wlabel1 = new Label(j, i, sdata[j]);
sheet.addCell(wlabel1);
}
}
book.write();
book.close();
} catch (Exception e) {
e.printStackTrace();
}
return null ;
}
3、实现层(JdbcDaoSupport)
public Vector excel(){
try{
Vector vect = new Vector();
StringBuffer sb = new StringBuffer();
sb.append( " SELECT u.telephone ,i.ITEM_NAME, i.ITEM_PRICE ,t.input_date,u.send_date,t.state,t.account_state,t.dispose_state ");
sb.append( " FROM user_temp u ,temp_item t ,ITEM_INF i where u.id = t.id and t.item_id = i.ITEM_ID");
Connection cnn = this.getJdbcTemplate().getDataSource().getConnection();
Statement st = cnn.createStatement();
ResultSet rs = st.executeQuery( sb.toString());
int collength = 0;
while (rs.next()) {
collength = rs.getMetaData().getColumnCount();
String[] rowdata = new String[collength];
for (int k = 1; k <= collength; k++) {
rowdata[k - 1] = rs.getString(k);
}
vect.add(rowdata);
}
rs.close();
st.close();
cnn.close();
return vect ;
}catch (Exception e) {
e.printStackTrace();
return null ;
}
}
4、页面调用
<a href="DownAction?method=down_excel">Excel</a>
如此即完成了使用jxl生成excel的例子
/*以下是需注意比较的地方
1:下载时如果出现文件下载 安全警告原因在于生成的文件名带有中文需转码
将action中fileName处如下转码(和一般转码不同特别注意顺序)
fileName = new String( fileName.getBytes("gbk"),"ISO-8859-1");
2:此例子是根据返回的Vector填充数据的,
该方法若一个结果集包含的数据过多时会出现溢出异常( the exceptino: java heap space)
若采用返回list集合来填充,需变动2处 >>1:action中从Vector vector = this.getDownExcelDao().excel();
开始修改如下:
List list = this.getDownExcelDao().excel();
for (int i = 0; i < list.size(); i++) {
String[] sdata = new String[]{bean.getTelephone(),bean.getITEM_NAME(),bean.getITEM_PRICE(),bean.getInput_date(),bean.getSend_date(),bean.getDispose_state(),bean.getAccount_state(),bean.getState() };
for (int j = 0; j < sdata.length; j++) {
Label wlabel1 = new Label(j, i, sdata[j]);
sheet.addCell(wlabel1);
}
}
>>2:改动实现层的excel()方法如下
List user_dg_item_list = this.getJdbcTemplate().queryForList( sb.tiString);
List excel_list = new ArrayList();
if ( user_dg_item_list !=null && user_dg_item_list.size() > 0 ){
Iterator it = user_dg_item_list.iterator();
while( it.hasNext() ){
Map map = (Map) it.next();
//DownExcelBean 是封装数据的实体类VO(根据对应字段建立即可)
DownExcelBean bean = new DownExcelBean();
bean.setId( Integer.toString( Integer.parseInt( map.get("id").toString())) );
bean.setITEM_ID( Integer.toString( Integer.parseInt( map.get("ITEM_ID").toString())) );
bean.setTelephone( map.get("telephone").toString());
bean.setITEM_NAME( map.get("ITEM_NAME").toString());
bean.setITEM_PRICE( Double.toString( Double.parseDouble( map.get("ITEM_PRICE").toString())) );
bean.setInput_date( map.get("input_date").toString() );
bean.setSend_date( map.get("send_date").toString() );
excel_list.add( bean);
}
}
方法3 使用poi实现
1、需先下载对应的jar包解压后将poi.jar放入lib下
2、在down_excel.jsp中写入
<%@ page contentType="text/html;charset=UTf-8"%>
<%@ page import="java.io.*,org.apache.poi.hssf.usermodel.*"%>
<%
HSSFWorkbook workbook = new HSSFWorkbook(); //创建新的Excel工作薄
HSSFSheet sheet = workbook.createSheet("JSP"); //在Excel工作薄中建工作表，名为缺省
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); //在索引1的位置创建行（最顶端的行）
cell = row.createCell((short) 0); //在索引0的位置创建单元格（左上端）
cell.setEncoding(HSSFCell.ENCODING_UTF_16); //定义单元格为字符串类型
cell.setCellValue("张三"); //在单元格输入一些内容
cell = row.createCell((short) 1);
cell.setEncoding(HSSFCell.ENCODING_UTF_16); //定义单元格为字符串类型
cell.setCellValue("李四");
String filename = application.getRealPath("/") + "text.xls"; //filename是工作薄的存放位置，存放在当前应用程序的根目录下
FileOutputStream fOut = new FileOutputStream(filename); //新建输出文件流
workbook.write(fOut); //把相应的Excel工作薄存盘
fOut.flush();
fOut.close(); //操作结束，关闭文件
out.println("excel文件已经生成，存放在<BR>" + filename);
%>
如此,就完成了用不同的方法实现动态生成excel数据并下载使用的功能;以上3种方法各有所长,不过本人还是觉得用javaExcel比较好用
1920
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
