java导出多列excel功能
/**
* 导出
*
* @return
* @throws IOException
*/
@RequestMapping(value = "/export", produces = "application/json; charset=utf-8")
public Map<String, Object> export(GoodParams params, HttpServletRequest request, HttpServletResponse response)
throws IOException {
// 要导出的查询语句
ShopInfos info = shopInfosService.findInfoByShopNum(params.getShopNum());
params.setShopGoodsTable(info.getShopGoodsTable());
if (StringUtil.areNotEmpty(params.getDiannei_classify2())) {
params.setStr(params.getDiannei_classify2().split(","));
}
List<ShopGoodsBean> list = snakeGoodsManageService.selectJDGoodsByParamsNotPage(params);
if (list.size() == 0) {
return getResultObjectData(ResultCode.FAILED, "暂无数据需要修改", "暂无数据需要修改");
}
try {
// excel标题
List<String> title = new ArrayList<String>();
title.add("店铺名称");
title.add("商品id");
title.add("商品标题");
title.add("isbn");
title.add("作者");
title.add("出版时间");
title.add("来源(1文轩2明文3博库)");
title.add("在售状态(1在售2仓库)");
title.add("外部编号");
title.add("sku_id");
title.add("运费模板id");
title.add("运费模板名称");
title.add("库存");
title.add("类目id");
title.add("出版社");
title.add("销售价");
// 生产文件名
String fileName = info.getShopName() + System.currentTimeMillis() + ".xls";
ExcelUtil.createExcel(request, response, list, fileName, title);
return null;
} catch (Exception e) {
return getResultObjectData(ResultCode.FAILED, "导出异常", "导出异常");
}
}
ExcelUtil.java
package com.mingwen.common.utils;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import com.mingwen.product.entity.taobao.ShopGoodsBean;
public class ExcelUtil {
public static void createExcel(HttpServletRequest request, HttpServletResponse response, List<ShopGoodsBean> list,
String fileName, List<String> title) {
try {
// 创建Excel的工作书册 Workbook,对应到一个excel文档
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFCellStyle style = workbook.createCellStyle();
// 生成一个字体
HSSFFont font = workbook.createFont();
// 字体增粗
// font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontHeightInPoints((short) 11);
style.setFont(font);
// 创建Excel的工作sheet,对应到一个excel文档的tab
HSSFSheet sheet = workbook.createSheet("sheet1");
// 创建Excel的sheet的一行 (表头)
HSSFRow row = sheet.createRow(0);
// 表头内容填充
for (int i = 0; i < title.size(); i++) {
// 设置excel每列宽度
sheet.setColumnWidth(i, 5000);
HSSFCell cell = row.createCell(i);
cell.setCellValue(title.get(i));
cell.setCellStyle(style);
}
// 创建内容行
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setWrapText(true);// 自动换行
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
for (int j = 0; j < list.size(); j++) {
try {
HSSFRow contentRow = sheet.createRow(j + 1);
for (int k = 0; k < title.size(); k++) {
HSSFCell cell = contentRow.createCell(k);
switch (k) {
case 0:
cell.setCellValue(list.get(j).getShopName());
break;
case 1:
cell.setCellValue(list.get(j).getNumberId());
break;
case 2:
cell.setCellValue(list.get(j).getTitle());
break;
case 3:
cell.setCellValue(list.get(j).getIsbn());
break;
case 4:
cell.setCellValue(list.get(j).getAuthor());
break;
case 5:
cell.setCellValue(list.get(j).getPublishDate());
break;
case 6:
cell.setCellValue(list.get(j).getDataFrom());
break;
case 7:
cell.setCellValue(list.get(j).getGoodState());
break;
case 8:
cell.setCellValue(list.get(j).getOuterId());
break;
case 9:
cell.setCellValue(list.get(j).getSkuId());
break;
case 10:
cell.setCellValue(list.get(j).getTemplate());
break;
case 11:
cell.setCellValue(list.get(j).getTemplateName());
break;
case 12:
cell.setCellValue(list.get(j).getStock());
break;
case 13:
cell.setCellValue(list.get(j).getCid());
break;
case 14:
cell.setCellValue(list.get(j).getPress());
break;
case 15:
cell.setCellValue(list.get(j).getSellingPrice());
break;
default:
break;
}
cell.setCellStyle(cellStyle);
}
} catch (Exception e) {
continue;
}
}
ByteArrayOutputStream os = new ByteArrayOutputStream();
try {
workbook.write(os);
} catch (IOException e) {
e.printStackTrace();
}
byte[] content = os.toByteArray();
InputStream is = new ByteArrayInputStream(content);
// 设置response参数，可以打开下载页面
response.reset();
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition",
"attachment;filename=" + new String((fileName + ".xls").getBytes("gb2312"), "iso-8859-1"));
ServletOutputStream out = response.getOutputStream();
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
try {
bis = new BufferedInputStream(is);
bos = new BufferedOutputStream(out);
byte[] buff = new byte[2048];
int bytesRead;
// Simple read/write loop.
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
} catch (final IOException e) {
throw e;
} finally {
if (bis != null)
bis.close();
if (bos != null)
bos.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
前台js:
window.location.href="/goodsManage/export?shopNum="+shopId+"&goodState="+goodState+"&goodsName="+goodsName+"&jd_sku="+jd_sku
+"&goodsCood="+goodsCood+"&goodsNumber="+goodsNumber+"&price1="+price1
+"&price2="+price2+"&jdCid="+jd_classify2+"&diannei_classify2="+diannei_classify2
+"&kucun2="+kucun2+"&kucun1="+kucun1+"&formatId="+glbs
+"&yunfei_template="+yunfei_template+"&pssx="+pssx+"&miaoshu_template="+miaoshu_template
+"&goodsType="+goodsType+"&huoyuan="+huoyuan+"&startTime="+startTime+"&endTime="+endTime;
不能用ajax，否则导出的文本在浏览器打不开