poi导出excel 并处理插入网络图片 范例 处理文件下载中文乱码

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

package com.inborn.inshop.controller.product;import com.inborn.inshop.common.util.DateUtils;
import com.inborn.inshop.model.Goods;
import com.inborn.inshop.service.GoodsService;
import com.inborn.inshop.vo.ProductExportVo;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigInteger;
import java.net.URL;
import java.net.URLConnection;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;/**
* Created by Administrator on 2017/5/2 0002.
*/
@Controller
@RequestMapping(value = "/product")
public class ProductExportController {
private static Logger logger = LoggerFactory.getLogger(ProductController.class);
@Resource
private GoodsService goodsService;
// 显示的导出表的标题
private String title="商品列表";
// 导出表的列名
private String[] rowName={"商品编号","商品图片","商品分类","商品品牌","商品描述","预估价值","鉴定价值","出售价格","寄卖方式","出售方式","当前状态","创建时间"};
/**
* 商品导出
* @param info
* @param catName
* @param brandCnName
*/
@RequestMapping(value = "/exportList")
@ResponseBody
public void exportList(HttpServletRequest request,
HttpServletResponse response, Goods info, String catName, String brandCnName) {
Map searchMap = new HashMap(); if (info.getId() != null) {
searchMap.put("id", info.getId());
// searchMap.put("id",Long.parseLong("132554300037214"));
}
if (StringUtils.isNotBlank(info.getGoodsName())) {
searchMap.put("goodsName", "%" + info.getGoodsName() + "%");
}
if (StringUtils.isNotBlank(info.getSellerDesc())) {
searchMap.put("sellerDesc", "%" + info.getSellerDesc() + "%");
} if (info.getGoodsStatus() != null) {
searchMap.put("goodsStatus", info.getGoodsStatus());
}
if (StringUtils.isNotBlank(catName)) {
searchMap.put("catName", "%" + catName + "%");
}
if (StringUtils.isNotBlank(brandCnName)) {
searchMap.put("brandCnName", "%" + brandCnName + "%");
}
if (info.getConsignWay() != null) {
searchMap.put("consignWay", info.getConsignWay());
} // PageHelper.startPage(curPage, pageSize);
List<ProductExportVo> dataList = goodsService.querytProdListByMap2(searchMap);
// 使用流将数据导出
OutputStream out = null;
try {
// 防止中文乱码
String fileName= toUtf8String(DateUtils.dateToString(new Date(),"yyyy-MM-dd")+title+".xls");
String headStr = "attachment; filename="+fileName;
// + new String((title+".xls").getBytes("utf-8"), "utf-8") + "\"";
response.setContentType("APPLICATION/OCTET-STREAM");
response.setHeader("Content-Disposition", headStr);
response.setCharacterEncoding("utf-8");
out = response.getOutputStream();
HSSFWorkbook workbook = new HSSFWorkbook(); // 创建工作簿对象
HSSFSheet sheet = workbook.createSheet(title); // 创建工作表
sheet.setColumnWidth(0, 20 * 256);
sheet.setColumnWidth(1, 30 * 256);
sheet.setColumnWidth(2, 25 * 256);
sheet.setColumnWidth(3, 25 * 256);
sheet.setColumnWidth(4, 50 * 256);
sheet.setColumnWidth(5, 25 * 256);
sheet.setColumnWidth(6, 25 * 256);
sheet.setColumnWidth(7, 25 * 256);
sheet.setColumnWidth(8, 25 * 256);
sheet.setColumnWidth(9, 25 * 256);
sheet.setColumnWidth(10, 25 * 256);
sheet.setColumnWidth(11, 30 * 256);
// 产生表格标题行
// 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,dataList.get(0).length-1 , 0,
* (rowName.length-1)));
*/// 合并单元格
// sheet.addMergedRegion(new CellRangeAddress(0, 1, 0,11));// 列行
// cellTiltle.setCellStyle(style);
// cellTiltle.setCellValue(title); // 定义所需列数
int columnNum = rowName.length;
HSSFRow hearderRow = sheet.createRow(0); // 在索引0的位置创建行(最顶端的行开始的第一行)
hearderRow.setHeight((short) (30 * 20));
// 将列头设置到sheet的单元格中
for (int n = 0; n < columnNum; n++) {
HSSFCell cellRowName = hearderRow.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++) {
ProductExportVo obj= (ProductExportVo)dataList.get(i);// 遍历每个对象
HSSFRow row = sheet.createRow(i + 1);// 创建所需的行数（从第二行开始写数据）
row.setHeight((short) (135 * 20)); String id=obj.getId()+"";
HSSFCell cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(id);
cell.setCellStyle(style); // 设置单元格样式
String imgeUrl=obj.getSquareImage();
if(StringUtils.isNotBlank(imgeUrl)){
// 创建一个图片区域
try {
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
HSSFClientAnchor anchor;
// 加载图片
anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 1,i+1, (short) 1, i+1);
anchor.setAnchorType(0);
patriarch.createPicture(anchor, loadPicture(imgeUrl, workbook));
}catch (Exception e){
logger.info("下载图片资源失败"+e.toString());
}
}else{
cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(imgeUrl);
cell.setCellStyle(style); // 设置单元格样式
} cell = row.createCell(2, HSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(style); // 设置单元格样式
if(StringUtils.isNotBlank(obj.getCatName())){
String catNameValue=obj.getCatName();
cell.setCellValue(catNameValue);
} cell = row.createCell(3, HSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(style); // 设置单元格样式
if(StringUtils.isNotBlank(obj.getBrandCnName())){
String brandCnNameValue=obj.getBrandCnName();
cell.setCellValue(brandCnNameValue);
} cell = row.createCell(4, HSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(style); // 设置单元格样式
if(StringUtils.isNotBlank(obj.getSellerDesc())){
String sellerDescValue=obj.getSellerDesc();
cell.setCellValue(sellerDescValue);
} cell = row.createCell(5, HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellStyle(style); // 设置单元格样式
if(obj.getEstimateMallPrice()!=null){
HSSFCellStyle cellStyle =this.getColumnTopStyle(workbook);
Long estimateMallPriceValue=obj.getEstimateMallPrice();
HSSFDataFormat format= workbook.createDataFormat();
cellStyle.setDataFormat(format.getFormat("¥#,##0"));
cell.setCellValue(estimateMallPriceValue);
cell.setCellStyle(cellStyle); // 设置单元格样式
} cell = row.createCell(6, HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellStyle(style); // 设置单元格样式
if(obj.getMktPrice()!=null){
Long mktPriceValue=obj.getMktPrice();
HSSFCellStyle cellStyle =this.getColumnTopStyle(workbook);
HSSFDataFormat format= workbook.createDataFormat();
cellStyle.setDataFormat(format.getFormat("¥#,##0"));
cell.setCellValue(mktPriceValue);
cell.setCellStyle(cellStyle); // 设置单元格样式
} cell = row.createCell(7, HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellStyle(style); // 设置单元格样式
if(obj.getMallPrice()!=null){
Long mallPriceValue=obj.getMallPrice();
HSSFCellStyle cellStyle =this.getColumnTopStyle(workbook);
HSSFDataFormat format= workbook.createDataFormat();
cellStyle.setDataFormat(format.getFormat("¥#,##0"));
cell.setCellValue(mallPriceValue);
cell.setCellStyle(cellStyle); // 设置单元格样式
} cell = row.createCell(8, HSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(style); // 设置单元格样式
if(obj.getConsignWay()!=null){
Integer consignWayValue=obj.getConsignWay();
if(consignWayValue==1){
cell.setCellValue("平台自营");
}
if(consignWayValue==2){
cell.setCellValue("邮寄");
}
if(consignWayValue==3){
cell.setCellValue("非邮寄");
}
}
cell = row.createCell(9, HSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(style); // 设置单元格样式
if(obj.getActivityType()!=null){
Integer activityTypeValue=obj.getActivityType(); if(activityTypeValue==0){
cell.setCellValue("一口价");
}
if(activityTypeValue==1){
cell.setCellValue("限时抢购");
}
if(activityTypeValue==2){
cell.setCellValue("寄拍");
} } cell = row.createCell(10, HSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(style); // 设置单元格样式
if(obj.getGoodsStatus()!=null){
Integer goodsStatusValue=obj.getGoodsStatus();
if(goodsStatusValue==1){
cell.setCellValue("不符合");
}
if(goodsStatusValue==10){
cell.setCellValue("待审核");
}
if(goodsStatusValue==20){
cell.setCellValue("待确认");
}
if(goodsStatusValue==30){
cell.setCellValue("待付费");
}
if(goodsStatusValue==40){
cell.setCellValue("待鉴定");
}
if(goodsStatusValue==50){
cell.setCellValue("待上架");
}
if(goodsStatusValue==60){
cell.setCellValue("出售中");
}
if(goodsStatusValue==70){
cell.setCellValue("已出售");
}
if(goodsStatusValue==71){
cell.setCellValue("线下售出");
}
if(goodsStatusValue==80){
cell.setCellValue("已下架");
}
if(goodsStatusValue==90){
cell.setCellValue("待退回");
}
if(goodsStatusValue==91){
cell.setCellValue("退回中");
}
if(goodsStatusValue==92){
cell.setCellValue("已退回");
}
if(goodsStatusValue==93){
cell.setCellValue("已取消");
}
} cell = row.createCell(11);
cell.setCellStyle(style); // 设置单元格样式
if(obj.getCreateTime()!=null){
Date createTimeValue=obj.getCreateTime(); // 给单元格 设置值
cell.setCellValue(createTimeValue); // 第一行第一列 插入日期
// 定义Cell格式
HSSFCellStyle columnTopStyle2 = this.getColumnTopStyle(workbook);// 获取列头样式对象
CreationHelper creationHelper = workbook.getCreationHelper();
columnTopStyle2.setDataFormat(
creationHelper.createDataFormat().getFormat("yyyy-MM-dd hh:mm:ss")
);
cell.setCellStyle(columnTopStyle2); // 设置单元格样式
}
}
if (workbook != null) {
try {
workbook.write(out);
out.flush();
} catch (IOException e) {
logger.info(e.toString());
}
} } catch (Exception e) {
logger.info(e.toString());
} finally {
if(out!=null){
try {
out.close();
}catch (IOException e){
logger.info(e.toString());
}
}
}
}
/*
* 列数据信息单元格样式
*/
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(true);
// 设置水平对齐的样式为居中对齐;
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
return style;
} /*
* 列头单元格样式
*/
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;
} /**
*
* @param imageUrl
* @param wb
* @return
* @throws IOException
*/
private static int loadPicture(String imageUrl, HSSFWorkbook wb) throws IOException {
int pictureIndex;
if(StringUtils.indexOf(imageUrl,"x-oss-process")<0){
imageUrl = imageUrl+"?x-oss-process=style/ss200";
}
URL url = new URL(imageUrl);
ByteArrayOutputStream bos = null;
try {
URLConnection conn = url.openConnection();
InputStream in = conn.getInputStream();
bos = new ByteArrayOutputStream();
int c;
while ((c = in.read()) != -1)
bos.write(c);
pictureIndex = wb.addPicture(bos.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG);
} finally {
if (bos != null)
bos.close();
}
return pictureIndex;
}
public static String toUtf8String(String s){
StringBuffer sb = new StringBuffer();
for (int i=0;i<s.length();i++){
char c = s.charAt(i);
if (c >= 0 && c <= 255){sb.append(c);}
else{
byte[] b;
try { b = Character.toString(c).getBytes("utf-8");}
catch (Exception ex) {
System.out.println(ex);
b = new byte[0];
}
for (int j = 0; j < b.length; j++) {
int k = b[j];
if (k < 0) k += 256;
sb.append("%" + Integer.toHexString(k).toUpperCase());
}
}
}
return sb.toString();
}
}maven依赖包

<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.9</version>
</dependency>结果图：