本文介绍了一个使用Java实现从数据库获取数据并将其导出到EXCEL文件的示例。该示例包括了从前端页面触发导出请求，通过控制器调用服务端API获取数据，再利用POI库将数据转换成EXCEL格式并返回给前端的过程。
在项目中做个把数据导出成EXCEL。今天整理一下。需要导入poi-3.11-beta2.jar
第一步：前台页面jsp：list_idenReceive.jsp
<%@page import="com.crowntech.wstss.model.enums.PriceTime"%>
<%@page import="com.crowntech.wstss.model.enums.PackageType"%>
<%@page import="com.crowntech.wstss.model.enums.SendType"%>
<%@page import="com.crowntech.wstss.model.enums.BindType"%>
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib prefix="f" uri="/wstss/java/tld/el_function.tld" %>
<%@ taglib prefix="h" uri="/wstss/java/tld/html.tld" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort()
+ path + "/";
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>识别码申领</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta http-equiv="pragma" content="text/html, charset=utf-8"/>
<jsp:include page="../common/head.jsp" />
</head>
<body>
<h1 class="h1" >位置：首页<em> > </em>识别码管理<em> > </em>识别码申领</h1>
<table id="dataview"></table>
<div id="tb">
<a href="javascript:void(0);" class="easyui-linkbutton" iconCls="icon-add" plain="true" onclick="apply();">申领</a>
</div>
<div id="dlg" class="easyui-dialog" style="width:1010px;height:600px;padding:10px;"
closed="true" buttons="#dlg-buttons">
<div class="ftitle"></div>
<form id="editForm" name="form1" method="post" novalidate>
<div id="tb_s">
<label>绑定方式:</label>
<input type="radio" name="bindType" id="" onclick="showButton()" value="<%=BindType.PROBIND.getCode() %>" checked="checked" style="margin-left: 20px;" />预绑定商品
<input type="radio" name="bindType" id="" onclick="hideButton()" value="<%=BindType.AFTBIND.getCode() %>" style="margin-left: 20px;" />后绑定商品
<a id="chooseProduct" href="javascript:void(0);" class="easyui-linkbutton" iconCls="icon-add" plain="true" onclick="addProduct();" style="margin-left: 40px;">选择商品</a>
</div>
<div align="right"> </div>
<div class="fitem" id="proInfo">
<label>商品名称:</label>
<input name="name" id="productName" class="easyui-validatebox" readonly="readonly"/>
<label>规格型号:</label>
<input name="specification" id="specification" class="easyui-validatebox" readonly="readonly"/>
<label>品牌:</label>
<input name="brand" id="brand" class="easyui-validatebox" readonly="readonly"/>
</div>
<div class="fitem">
<label>包装规格:</label>
<h:select id="packingSize" name="packingSize" map="${applicationScope.PackageType}" style="width: 220px;" headerKey="-1" headerValue="--请选择--" onchange="count();" />
</div>
<table class="easyui-datagrid" style="width:98%;height:100px;" >
<thead>
<tr>
<th data-options="field:'code',width:440,align:'center'">包装层次</th>
<th data-options="field:'name',width:440,align:'center'">单包装数量</th>
</tr>
</thead>
<tbody>
<tr>
<td style="align:'left'">单品</td>
<td style="align:'right'"><input id="numPa" style="width: 20px" readonly="readonly"/></td>
</tr>
</tbody>
</table>
<div class="fitem">
<label>价格期限:</label>
<c:forEach items="${prices }" var="p" varStatus="status">
<c:choose>
<c:when test="${status.index==0 }">
<input name="priceTerm" type="radio" checked="checked" style="width: 50px" _price="${p.price}" _time="${p.time}" value="<%-- ${p.price}元/${p.amount }个/ --%>${p.time }" onclick="st(this);" />${p.price }元/${p.amount }个/${p.time }年
</c:when>
<c:otherwise>
<input name="priceTerm" type="radio" style="width: 50px" _price="${p.price}" _time="${p.time}" value="<%-- ${p.price}元/${p.amount }个/ --%>${p.time }" onclick="st(this);" />${p.price }元/${p.amount }个/${p.time }年
</c:otherwise>
</c:choose>
</c:forEach>
</div>
<input type="hidden" name="price" id="price" />
<input type="hidden" name="time" id="time" />
<div class="fitem">
<label>包装说明:</label>
<input name="packingState" id="packingState" class="easyui-validatebox" />
<label>包装数量:</label>
<input name="number" id="number" onblur="count()" class="easyui-validatebox" data-options="required:true,validType:['rangeInt[1,500]']" />
</div>
<div class="fitem">
<label>防伪码数量:</label>
<input name="QRCodeCount" id="security" class="easyui-validatebox" readonly="readonly"/>
<label>金额:</label>
<input name="money" id="money" class="easyui-validatebox" readonly="readonly"/>
</div>
<div class="fitem">
<label>配送方式:</label>
<input name="delivery" id="delivery1" type="radio" style="width: 100px" checked="checked" value="<%=SendType.GET.getCode() %>"/><%=SendType.GET.getName() %>
<input name="delivery" id="delivery2" type="radio" style="width: 100px" value="<%=SendType.SEND.getCode() %>" /><%=SendType.SEND.getName() %>
</div>
<div class="fitem">
<label>备注:</label>
<input name="remark" id="remark" class="easyui-validatebox" />
</div>
<input type="hidden" name="productId" id="productId"/>
</form>
</div>
<div id="dlg-buttons">
<a href="javascript:void(0)" class="easyui-linkbutton c6" iconCls="icon-ok" onclick="saveApply()" style="width:90px">申请</a>
<a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-cancel" onclick="javascript:$('#dlg').dialog('close')" style="width:90px">取消</a>
</div>
<div id="add_s"></div>
<script type="text/javascript">
$('#dataview').datagrid({
toolbar: '#tb',
url:'<%=basePath%>receiveController/getByQuery',
columns:[[
{field:'id',title:'申领流水号',align:'center',width:$(this).width()*0.14},
{field:'number',title:'套贴数量',align:'center',width:$(this).width()*0.05},
{field:'packingSize',title:'包装大小(瓶)',align:'center',width:$(this).width()*0.1},
{field:'name',title:'商品名',align:'center',width:$(this).width()*0.2},
{field:'priceTerm',title:'价格期限(年)',align:'center',width:$(this).width()*0.1},
{field:'money',title:'金额(元)',align:'center',width:$(this).width()*0.15},
{field:'ri',title:'操作',align:'center',width:$(this).width()*0.15,
formatter: function(value,row,index){
var htmlTxt = "";
htmlTxt += "<input type='button' class='easyui-linkbutton' value='导出EXCEL' onclick='getexcel("+row.id+")'/>";
return htmlTxt;
}
}
]],
pagination:true,
pageSize:5,
pageList:[5,10,20,50,100],
striped : true,
height:510
});
var save_url;
//打开申领表窗口
function apply(){
$('#dlg').dialog('open').dialog('setTitle','申领识别码');
$('#editForm').form('clear');
save_url = '<%=basePath%>receiveController/apply';
}
//申请
function saveApply(){
if(save_url){
if($("#editForm").form('validate')){
var params = $("#editForm").serializeObject();
$.ajax({
url: save_url,
cache:false,
data: params,
success:function(data)
{
if (data)
{
if(data.flag)
{
$('#dlg').dialog('close');
$("#dataview").datagrid('load');
}else{
errorMsg({message:data.data});
$('#dlg').dialog('close');
}
}
}
});
}
}
}
//添加商品
function addProduct(){
$('#add_s').dialog("open");
}
//添加商品
$('#add_s').dialog({
title: '商品信息',
href: '<%=basePath%>idenApply_No/chooseProduct',
width:'900',
height:'420',
cache: false,
modal: true,
closed: true,
buttons: [{
text:'确定',
iconCls:'icon-ok',
handler:function(data){
var sr = $('#dataview_s').datagrid("getSelected");
if(sr){
$("#editForm").form('load',sr);
$("#productId").val(sr.id);
$('#add_s').dialog('close');
}else{
errorMsg('错误提示','你还没有选择一个商品！');
}
}
},{
text:'取消',
iconCls:'icon-cancel',
handler:function(){
$('#add_s').dialog('close');
}
}]
});
function st(_this){
var price = $(_this).attr("_price");
var time = $(_this).attr("_time");
$("#price").val(price);
$("#time").val(time);
count();
}
function count(){
var price,amount,number,tatolcount,money;
price = $("#price").val();
amount = $("#packingSize").val();
number = $("#number").val();
tatolcount = amount*number;
money = tatolcount*price;
if(tatolcount){
$("#security").val(tatolcount);
}else{
$("#security").val("");
}
if(money){
$("#money").val(money.toFixed(2));
}else{
$("#money").val("");
}
if(amount==-1){
$("#numPa").val("");
}else{
$("#numPa").val(amount);
}
}
//导出EXCEL
function getexcel(id){
$.messager.confirm('确认','确认导出EXCEL？',function(r){
if (r){
var r_url = "<%=basePath%>loginstics-excel/confirm?id="+id;
document.location.href = r_url;
}
});
}
function showButton(){
$('#chooseProduct').show();
}
function hideButton(){
$('#chooseProduct').hide();
$("#productName").val("");
$("#specification").val("");
$("#brand").val("");
$("#productId").val("");
}
</script>
</body>
</html>
第二步：控制器，LogisticsCodeExcelController
package com.crowntech.wstss.web.controller;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.client.RestTemplate;
import org.springframework.web.servlet.ModelAndView;
import org.springframework.web.servlet.view.document.AbstractExcelView;
import com.crowntech.wstss.model.code.LogisticCodeExcelModel;
import com.crowntech.wstss.model.common.ResponseBean;
import com.crowntech.wstss.web.common.AjaxResult;
import com.crowntech.wstss.web.common.BaseController;
import com.crowntech.wstss.web.common.ViewExcel;
import com.crowntech.wstss.web.utils.ClassUtils;
import com.crowntech.wstss.web.utils.LogisticsCodeExcelUntil;
/**
* @author echosoft2 导出EXCEL--物流码
*/
@Controller
@RequestMapping("/loginstics-excel")
public class LogisticsCodeExcelController extends BaseController {
@Autowired
private RestTemplate restTemplate;
// 获得服务端url
String url = this.apiUrl.concat("/loginstics-excels");
InputStream excelStream;
private String fileName;
/**
* 导出物流吗信息
*
* @param model
* @return AjaxResult
* @throws Exception
*/
@RequestMapping("/confirm")
public ModelAndView confirm(String id,ModelMap model) throws Exception {
final AjaxResult result = new AjaxResult(false);
String URL = this.url + "/" + id;
ResponseBean<?> responseBean = restTemplate.getForObject(URL,
ResponseBean.class);
if (responseBean != null) {
if (responseBean.getCode() == NumberUtils.INTEGER_ZERO) {
List<LogisticCodeExcelModel> listCode =getList((List<LinkedHashMap<String, Object>>) responseBean.getData());
model.put("listCode", listCode);
model.put("fileName", "statis-"+ LogisticsCodeExcelUntil.getNowDate()+".xls");
} else {
result.setData(responseBean.getData());
}
}
return new ModelAndView(new ViewExcel(),model);
}
private List<LogisticCodeExcelModel> getList(List<LinkedHashMap<String, Object>> codeList) throws Exception {
List<LogisticCodeExcelModel> list = null;
if (codeList != null) {
list = new ArrayList<LogisticCodeExcelModel>();
for (LinkedHashMap<String, Object> map : codeList) {
LogisticCodeExcelModel model = new LogisticCodeExcelModel();
ClassUtils.linkedHashMapToBean(map, model);
list.add(model);
}
}
return list;
}
/**
* linkedHashMap转换为javabean
*
* @param hashMap
* @param object
* @throws Exception
*/
public static void linkedHashMapToBean(LinkedHashMap<String, Object> hashMap, Object object) throws Exception
{
if (hashMap != null && !hashMap.isEmpty() && object != null)
{
final Class<?> classz = object.getClass();
final Field[] fields = classz.getDeclaredFields();
for (final Field field : fields)
{
final String fieldName = field.getName();
Object value = hashMap.get(fieldName);
if (value != null)
{
final Class<?> fieldType = field.getType();
final Method m = classz.getMethod("set" + ClassUtils.getMethodName(fieldName), fieldType);
// TODO 请注意，此处需要自行添加相关类型的转换（Integer和String等基本类型不需要转换）
if (fieldType.equals(Date.class))
{
value = new Date(Long.parseLong(value.toString()));
}
m.invoke(object, value);
}
}
}
else
{
object = null;
}
}
}
第三步：拼接EXCEL.LogisticsCodeExcelUntil.java
public class LogisticsCodeExcelUntil {
public static HSSFWorkbook getLogisticsCode(List<LogisticCodeExcelModel> listCode){
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("物流码信息");
//创建表头
HSSFRow row = sheet.createRow(0);// 创建第一行
ExcelTital depoTital = new ExcelTital();
HSSFCell cell = null;
for (int i = 0; i < depoTital.getTITAL_List().size(); i++) {
cell = row.createCell(i);
cell.setCellValue(new HSSFRichTextString(depoTital.getTITAL_List().get(i)));
}
for (int i = 0; i < listCode.size(); i++) {
LogisticCodeExcelModel code = listCode.get(i);
row = sheet.createRow(i + 1);// 创建第i+1行
cell = row.createCell(0);// 大物流码序号
cell.setCellValue(code.getBigindex());
cell = row.createCell(1);// 大物流码
cell.setCellValue(code.getBigCode());
cell = row.createCell(2);// 小物流码序号
cell.setCellValue(code.getSmallindex());
cell = row.createCell(3);// 小物流码
cell.setCellValue(code.getSmallCode());
cell =row.createCell(4); //二维码
cell.setCellValue(code.getSmallqrcode());
}
return workbook;
}
public static String getNowDate(){
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
return format.format(new java.util.Date());
}
}
/**
* @author echosoft2
* 表头信息
*/
class ExcelTital{
private final static String bigCode ="大物流码";
private final static String smallCode ="小物流码";
private final static String bigindex="大物流码序号";
private final static String smallindex="小物流码序号";
private final static String smallqrcode="二维码";
private ArrayList<String> TITAL_List = new ArrayList<String>();
public ExcelTital() {
TITAL_List.add(bigindex);
TITAL_List.add(bigCode);
TITAL_List.add(smallindex);
TITAL_List.add(smallCode);
TITAL_List.add(smallqrcode);
}
public ArrayList<String> getTITAL_List() {
return TITAL_List;
}
}
扫一扫
558
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
