最近项目中要求技术实现导出商品订单的excel表格数据,功能虽然实现了,但是依旧两个小的技术点,没有实现
1,中文文件名称的编写,乱码后,数据是无法显示的.
2,条件查询时,Enum类型的参数没有传输到controller层.明天攻克以下吧..
使用maven构建项目的时候,需要使用pom.xml引入对应的jar包:
<!--poi导出excel开始-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.15</version>
</dependency>
<!--poi导出excel结束-->页面jsp实现.
<div class="col-md-3" style="padding-left: 15px;">
<button type="button" id="exportData" class="btn btn-search" style="width:45%">
导出数据
</button>
</div><label for="beginDate" class="col-md-4 control-label">开始日期</label>
<div class="col-md-8">
<input name="beginDate" id="beginDate" cssClass="form-control"
style="height: 34px;border-radius: 4px;"
οnclick="WdatePicker({dateFmt:'yyyy-MM-dd',readOnly:true,alwaysUseStartDate:true});"
placeholder="请选择下单日期"
value='<fmt:formatDate value="<%=new Date()%>" pattern="yyyy-MM-dd "/>'/>
</div>
</div>
<div class="col-md-3">
<label for="endDate" class="col-md-4 control-label">结束日期</label>
<div class="col-md-8">
<input name="endDate" id="endDate" cssClass="form-control"
style="height: 34px;border-radius: 4px;"
οnclick="WdatePicker({maxDate:'%y-%M-%d',readOnly:true,dateFmt:'yyyy-MM-dd',alwaysUseStartDate:true});"
placeholder="请选择下单日期"
value='<fmt:formatDate value="<%=new Date()%>" pattern="yyyy-MM-dd "/>'/>
</div>
</div>
<div class="col-md-3">
<label for="paymentType" class="col-md-4 control-label">支付方式</label>
<div class="col-md-8">
<select id="paymentType" name="paymentType" class="form-control">
<option value="">***请选择支付方式***</option>
<option value="icbc">第三方支付</option>
<option value="cash">现金</option>
<option value="onecard">食堂卡</option>
<option value="bankcard">银行卡</option>
</select>
</div>
</div>/*====================导出数据按钮开始============================*/
$("#exportData").click(function(){
var beginDate=$("#beginDate").val();
var endDate=$("#endDate").val();
var paymentType=$("#paymentType").val();
window.location.href="${ctxPath}/payment/rpt/exportData.html?beginDate="+beginDate+"endDate="+endDate+"+paymentType="+paymentType;
alert("下载完成");
});
/*====================导出数据按钮结束======================================*/controller层数据获得和封装数据:
/**
* 统计payment流水
*
* @return
* @throws Exception
*/
@RequestMapping("exportData")
@ResponseBody
public Map<String, Object> exportData(HttpServletRequest request, HttpServletResponse response, Date beginDate, Date endDate, Enums.PaymentType paymentType, OrderBy orderBy, Pagination pageable) throws Exception {
List<Long> storeIds = storeService.selectByUserId(this.getUser().getUserId());
Long storeId = null;
if (null != storeIds && storeIds.size() > 0) {
storeId = storeIds.get(0);
}
//默认当前用户的ID；
//单店模式暂时不用storeId去做查询条件
ResultList<Payment> paymentResultList = this.paymentService.select(null, beginDate, endDate, getCompanyId(), storeId, paymentType, null, orderBy, pageable);
Map<String, Object> map = SysStaticParam.resultMessage("获取信息成功！");
//获取到所有的数据信息
List<Payment> payments = paymentResultList.getData();
//导出开始
if (payments != null && payments.size() > 0) {
//导出文件的标题
/* String title = "abc.xls";*/
String title="订单明细表"+beginDate+"--"+endDate+".xls";
//设置表格标题行
String[] headers = new String[]{"订单编号", "支付方式", "支付金额", "支付时间"};
List<Object[]> dataList = new ArrayList<Object[]>();
Object[] objs = null;
for (Payment payment : payments) {//循环每一条数据
objs = new Object[headers.length];
objs[0] = payment.getOrderCode();//订单编号
objs[1] = payment.getPaymentType();//支付方式
objs[2] = payment.getPaymentPrice();//支付金额
objs[3] = payment.getPaymentDate();//支付时间
//数据添加到excel表格
dataList.add(objs);
}
//使用流将数据导出
OutputStream out = null;
try {
//防止中文乱码
/*String headStr = "attachment; filename=\"" + title + "\"";*/
/*String headStr = "attachment; filename=\"" + new String( title.getBytes("gb2312"), "ISO8859-1" ) + "\"";*/
String headStr = "attachment; filename=\"" + new String( title.getBytes("gbk"), "utf-8" ) + "\"";
response.setContentType("octets/stream");
response.setContentType("APPLICATION/OCTET-STREAM");
response.setHeader("Content-Disposition", headStr);
out = response.getOutputStream();
//ExportExcel ex = new ExportExcel(title, headers, dataList);//有标题
ExportExcelSeedBack ex = new ExportExcelSeedBack(title, headers, dataList);//没有标题
ex.export(out);
} catch (Exception e) {
e.printStackTrace();
}
map.put(SysStaticParam._RESULT, paymentResultList);
}
return map;
}package com.utp.pos.core.utils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
/**
* Created by 623383562@qq.com on 2018/1/7.
*/
public class ExportExcelSeedBack {
//显示的导出表的标题
private String title;
//导出表的列名
private String[] rowName ;
private List<Object[]> dataList = new ArrayList<Object[]>();
HttpServletResponse response;
//构造方法，传入要导出的数据
public ExportExcelSeedBack(String title,String[] rowName,List<Object[]> dataList){
this.dataList = dataList;
this.rowName = rowName;
this.title = title;
}
/*
* 导出数据
* */
public void export(OutputStream out) throws Exception{
try{
HSSFWorkbook workbook = new HSSFWorkbook(); // 创建工作簿对象
HSSFSheet sheet = workbook.createSheet(title); // 创建工作表
// 产生表格标题行
// HSSFRow rowm = sheet.createRow(0);
// HSSFCell cellTiltle = rowm.createCell(0);
//sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面 - 可扩展】
HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);//获取列头样式对象
HSSFCellStyle style = this.getStyle(workbook); //单元格样式对象
// sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowName.length-1)));//合并单元格
// cellTiltle.setCellStyle(columnTopStyle);
// cellTiltle.setCellValue(title);
// 定义所需列数
int columnNum = rowName.length;
HSSFRow rowRowName = sheet.createRow(0); // 在索引2的位置创建行(最顶端的行开始的第二行)
// 将列头设置到sheet的单元格中
for(int n=0;n<columnNum;n++){
HSSFCell cellRowName = rowRowName.createCell(n); //创建列头对应个数的单元格
cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); //设置列头单元格的数据类型
HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
cellRowName.setCellValue(text); //设置列头单元格的值
cellRowName.setCellStyle(columnTopStyle); //设置列头单元格样式
}
//将查询出的数据设置到sheet对应的单元格中
for(int i=0;i<dataList.size();i++){
Object[] obj = dataList.get(i);//遍历每个对象
HSSFRow row = sheet.createRow(i+1);//创建所需的行数（从第二行开始写数据）
for(int j=0; j<obj.length; j++){
HSSFCell cell = null; //设置单元格的数据类型
if(j == 0){
cell = row.createCell(j,HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(i+1);
}else{
cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
if(!"".equals(obj[j]) && obj[j] != null){
cell.setCellValue(obj[j].toString()); //设置单元格的值
}
}
cell.setCellStyle(style); //设置单元格样式
}
}
//让列宽随着导出的列长自动适应
for (int colNum = 0; colNum < columnNum; colNum++) {
int columnWidth = sheet.getColumnWidth(colNum) / 256;
for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
HSSFRow currentRow;
//当前行未被使用过
if (sheet.getRow(rowNum) == null) {
currentRow = sheet.createRow(rowNum);
} else {
currentRow = sheet.getRow(rowNum);
}
// if (currentRow.getCell(colNum) != null) {
// HSSFCell currentCell = currentRow.getCell(colNum);
// if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
// int length = currentCell.getStringCellValue().getBytes().length;
// if (columnWidth < length) {
// columnWidth = length;
// }
// }
// }
if (currentRow.getCell(colNum) != null) {
HSSFCell currentCell = currentRow.getCell(colNum);
if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
int length = 0;
try {
length = currentCell.getStringCellValue().getBytes().length;
} catch (Exception e) {
e.printStackTrace();
}
if (columnWidth < length) {
columnWidth = length;
}
}
}
}
if(colNum == 0){
sheet.setColumnWidth(colNum, (columnWidth-2) * 256);
}else{
sheet.setColumnWidth(colNum, (columnWidth+4) * 256);
}
}
if(workbook !=null){
try{
workbook.write(out);
}catch (IOException e) {
e.printStackTrace();
}
}
}catch(Exception e){
e.printStackTrace();
}
finally{
out.close();
}
System.err.println("数据源大小:"+dataList.size());
System.err.println("列数"+rowName.length);
System.err.println(title);
System.err.print("订单结束");
}
/*
* 列头单元格样式
*/
public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
// 设置字体
HSSFFont font = workbook.createFont();
//设置字体大小
font.setFontHeightInPoints((short)11);
//字体加粗
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//设置字体名字
font.setFontName("Courier New");
//设置样式;
HSSFCellStyle style = workbook.createCellStyle();
//设置底边框;
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//设置底边框颜色;
style.setBottomBorderColor(HSSFColor.BLACK.index);
//设置左边框;
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//设置左边框颜色;
style.setLeftBorderColor(HSSFColor.BLACK.index);
//设置右边框;
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
//设置右边框颜色;
style.setRightBorderColor(HSSFColor.BLACK.index);
//设置顶边框;
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
//设置顶边框颜色;
style.setTopBorderColor(HSSFColor.BLACK.index);
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(false);
//设置水平对齐的样式为居中对齐;
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
return style;
}
/*
* 列数据信息单元格样式
*/
public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
// 设置字体
HSSFFont font = workbook.createFont();
//设置字体大小
//font.setFontHeightInPoints((short)10);
//字体加粗
//font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//设置字体名字
font.setFontName("Courier New");
//设置样式;
HSSFCellStyle style = workbook.createCellStyle();
//设置底边框;
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//设置底边框颜色;
style.setBottomBorderColor(HSSFColor.BLACK.index);
//设置左边框;
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//设置左边框颜色;
style.setLeftBorderColor(HSSFColor.BLACK.index);
//设置右边框;
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
//设置右边框颜色;
style.setRightBorderColor(HSSFColor.BLACK.index);
//设置顶边框;
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
//设置顶边框颜色;
style.setTopBorderColor(HSSFColor.BLACK.index);
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(false);
//设置水平对齐的样式为居中对齐;
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
return style;
}
}注意:突然发现此工具类中很多方法已经过时了.但是不影响使用,就没有更改.
1月8日更新:
乱码问题;
//导出文件的标题,开始日期和结束日期的格式化
String bDate = beginDate==null ? "初始日期" : DateUtil.getDateFormat(beginDate);
String eDate = endDate==null ? "当前日期" : DateUtil.getDateFormat(endDate);
String title="订单明细表"+bDate+" 至 "+eDate+".xls"; //防止中文乱码
String headStr = "attachment; filename=\"" + new String( title.getBytes("gb2312"), "ISO8859-1" ) + "\"";
传参问题:
Enum类型的数据一样可以传参成功的.只是我个人的代码有问题.
看到windows.location.href=""页面.
window.location.href="${ctxPath}/payment/rpt/exportData.html?beginDate="+beginDate+"endDate="+endDate+"+paymentType="+paymentType;window.location.href="${ctxPath}/payment/rpt/exportData.html?beginDate="+beginDate+"&endDate="+endDate+"+&paymentType="+paymentType;
objs[2] = payment.getPaymentType().getValue();//支付方式先就这样了...
3373
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
