注意日期格式如果是以string类型的方式存到数据库的导出时要转换一次，直接导出格式不对
因为导出excel表格用的是get方式传参，所以如果需要对导出的数据用中文模糊查询，此时 用get传参会出现中文乱码
解决办法：
前端对需要传的中文参数进行一次编码 urlencoder.encode(传参,“utf-8”);
后台需要再次解码：urldecoder.decode(接收的参数,“utf-8”);
@requestmapping(value = "outputexcel", method = requestmethod.get)@responsebodypublic void outputexcel( httpservletresponse response,string officeid,string sonid,string nameorphone,string begintime, string endtime,string option) { string nstring = ""; try { if (nameorphone != null && nameorphone != "") { //对前端传的参数解码 nstring = urldecoder.decode(nameorphone,"utf-8"); } } catch (unsupportedencodingexception e2) { // todo auto-generated catch block e2.printstacktrace(); } response.reset(); //设置浏览器下载的格式，并以当前时间的毫秒数命名 response.setheader("content-disposition", "attachment;filename=" + system.currenttimemillis() + ".xls"); response.setcontenttype("application/msexcel"); list<purchasesum> list = purchaseservice.selectpcsum(officeid, sonid, nstring, begintime, endtime, option); if (list == null && list.isempty()) { throw new nullpointerexception("导出数据源为空"); } hssfworkbook wb = new hssfworkbook(); hssfsheet sheet = wb.createsheet("sheet0"); hssfrow rows; hssfcell cells; //设置表格第一行的列名 // 获得表格第一行 rows = sheet.createrow(0); // 根据需要给第一行每一列设置标题 cells = rows.createcell(0); cells.setcellvalue("客户姓名"); cells = rows.createcell(1); cells.setcellvalue("客户电话"); cells = rows.createcell(2); cells.setcellvalue("下单日期"); cells = rows.createcell(3); cells.setcellvalue("订单号"); cells = rows.createcell(4); cells.setcellvalue("所属分公司"); cells = rows.createcell(5); cells.setcellvalue("签单人"); cells = rows.createcell(6); cells.setcellvalue("品名"); cells = rows.createcell(7); cells.setcellvalue("型号"); cells = rows.createcell(8); cells.setcellvalue("颜色"); cells = rows.createcell(9); cells.setcellvalue("尺寸"); cells = rows.createcell(10); cells.setcellvalue("材质"); cells = rows.createcell(11); cells.setcellvalue("已采购数量(件)"); cells = rows.createcell(12); cells.setcellvalue("采购单价"); cells = rows.createcell(13); cells.setcellvalue("采购总价"); cells = rows.createcell(14); cells.setcellvalue("已出库(件)"); //循环数据库查出来的数据集，对应每一列赋值 //此处list.size()本不应该-1，因为同事在list集合里追加了另一条数据，导致报错故将其去除 for (int i = 0; i < list.size()-1; i++) { rows = sheet.createrow(i + 1); cells = rows.createcell(0); cells.setcellvalue(list.get(i).getcustomername()); cells = rows.createcell(1); cells.setcellvalue(list.get(i).getphone()); //对日期格式进行转换 cells = rows.createcell(2); string datestring = list.get(i).getplaceordertime().tostring(); date date = null; try { date = new simpledateformat("eee mmm dd hh:mm:ss z yyyy", locale.uk).parse(datestring); } catch (parseexception e1) { // todo auto-generated catch block e1.printstacktrace(); } simpledateformat sdf = new simpledateformat("yyyy-mm-dd"); cells.setcellvalue(sdf.format(date)); cells = rows.createcell(3); cells.setcellvalue(list.get(i).getordernumber()); cells = rows.createcell(4); cells.setcellvalue(list.get(i).getofficename()); cells = rows.createcell(5); cells.setcellvalue(list.get(i).getusername()); cells = rows.createcell(6); cells.setcellvalue(list.get(i).getproductname()); cells = rows.createcell(7); cells.setcellvalue(list.get(i).gettype()); cells = rows.createcell(8); cells.setcellvalue(list.get(i).getcolor()); cells = rows.createcell(9); cells.setcellvalue(list.get(i).getsize()); cells = rows.createcell(10); cells.setcellvalue(list.get(i).gettexture()); cells = rows.createcell(11); cells.setcellvalue(list.get(i).getpurchasednumber()); cells = rows.createcell(12); cells.setcellvalue(list.get(i).getpurchaseprice()); cells = rows.createcell(13); cells.setcellvalue(list.get(i).getpurchasepricesun()); cells = rows.createcell(14); cells.setcellvalue(list.get(i).getoutlibrarynumber()); } try { outputstream ostream = response.getoutputstream(); wb.write(ostream); ostream.flush(); } catch (filenotfoundexception e1) { // todo auto-generated catch block e1.printstacktrace(); } catch (ioexception e) { // todo auto-generated catch block e.printstacktrace(); } }
以上就是java实现数据库中查询出的数据转存成excel表的详细内容。