java 下载图片到excel表格里
excelUtils
import java.io.IOException;
import java.io.InputStream;
import java.net.HttpURLConnection;
import java.net.MalformedURLException;
import java.net.URL;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.collections.MapUtils;
import org.apache.commons.io.IOUtils;
import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Picture;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelUtils extends BaseController {
private static Logger logger = Logger.getLogger(ExcelUtils.class);
/**
* 样式列表
*/
private static Map<String, CellStyle> styles;
/**
* createExcel
* @param assetHeadTemp 表头字段
* @param assetNameTemp 表头字段名称
* @param dataList 数据list
* @param fileName 文件名称
* @param response
*/
public static void createExcel(String title,String[] assetHeadTemp, String[] assetNameTemp, List<Map<String, Object>> dataList,
String fileName, HttpServletResponse response) {
InputStream inputStream = null;
HttpURLConnection conn = null;
try {
Workbook wb = new SXSSFWorkbook();
// XSSFWorkbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("Sheet1");
Row row;
Cell cell;
// 图片字节数组
byte[] imgByte = null;
// excel样式
CellStyle style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);// 水平
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直
styles = createStyles(wb);
// 输出表头
row = sheet.createRow(0);
row.setHeightInPoints(30);
cell = row.createCell(0);
cell.setCellValue(title);
cell.setCellStyle(styles.get("title"));
sheet.addMergedRegion(new CellRangeAddress(row.getRowNum(),
row.getRowNum(), row.getRowNum(), assetHeadTemp.length-1));
// 输出表头字段
row = sheet.createRow(1);
row.setHeightInPoints(16);
for (int i = 0; i < assetHeadTemp.length; i++) {
cell = row.createCell(i);
cell.setCellValue(assetHeadTemp[i]);
cell.setCellStyle(styles.get("header"));
}
// 输出内容
int rowIndex = 2;
for (Map<String, Object> map : dataList) {
row = sheet.createRow(rowIndex++);
// 设置行高
row.setHeightInPoints((short) 75);
for (int i = 0; i < assetNameTemp.length; i++) {
cell = row.createCell(i);
// 设置列宽
sheet.setColumnWidth(i, 238 * 23);
// 输出图片到第1列 到 第六列
int titleLength = (int) map.get("titleCount");
if (i >= titleLength && i < (titleLength+ MapUtils.getInteger(map, "imageCount",0))) {
imgByte = null;
if (map.get(assetNameTemp[i]) == null) {
continue;
}
if (!StringUtils.isBlank(map.get(assetNameTemp[i]).toString())) {
// 输出图片
URL url = new URL(map.get(assetNameTemp[i]).toString());
conn = (HttpURLConnection) url.openConnection();
// 设置超时间为3秒
conn.setConnectTimeout(3 * 1000);
// 防止屏蔽程序抓取而返回403错误
conn.setRequestProperty("User-Agent",
"Mozilla/4.0 (compatible; MSIE 5.0; Windows NT; DigExt)");
// 得到输入流
try{
inputStream = conn.getInputStream();
imgByte = IOUtils.toByteArray(inputStream);
String str = new String(imgByte);
if (!StringUtils.isEmpty(str) && !str.contains("文件已丢失或不存在")) {
// 图片转化为字节数组
if (imgByte != null) {
// 图片存在即输出图片
int addPicture = wb.addPicture(imgByte, wb.PICTURE_TYPE_JPEG);
Drawing drawing = sheet.createDrawingPatriarch();
CreationHelper helper = wb.getCreationHelper();
ClientAnchor anchor = helper.createClientAnchor();
anchor.setRow1(rowIndex - 1);
anchor.setCol1(i);
// 指定我想要的长宽
double standardWidth = 150;
double standardHeight = 100;
// 计算单元格的长宽
double cellWidth = sheet.getColumnWidthInPixels(cell.getColumnIndex());
double cellHeight = cell.getRow().getHeightInPoints() / 72 * 96;
// 计算需要的长宽比例的系数
double a = standardWidth / cellWidth;
double b = standardHeight / cellHeight;
Picture picture = drawing.createPicture(anchor, addPicture);
picture.resize(a, b);
}
} else {
cell.setCellValue("");
cell.setCellStyle(styles.get("data"));
}
}catch (Exception e){
cell.setCellValue("");
cell.setCellStyle(styles.get("data"));
logger.error(e.getMessage(),e);
}
} else {
cell.setCellValue("");
cell.setCellStyle(styles.get("data"));
}
} else {
String defaultDataFormat = "@";
CellStyle style2 = styles.get("data");
// 输出文字
Object object = map.get(assetNameTemp[i]);
String objectTitle = assetHeadTemp[i];
if(objectTitle.contains("时间")&& !objectTitle.contains("已用时间")){
String dateString = null;
if(object != null && object != ""){
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
dateString = sdf.format(object);
}
cell.setCellValue(dateString != null ? dateString : "");
DataFormat createDataFormat = wb.createDataFormat();
style2.setDataFormat(createDataFormat.getFormat("yyyy-MM-dd HH:mm:ss"));
}else{
style2.setDataFormat(wb.createDataFormat().getFormat(defaultDataFormat));
cell.setCellValue(object != null ? object.toString() : "");
}
cell.setCellStyle(style2);
}
}
}
// 响应客户端
writeExcel(wb, fileName, response);
} catch (MalformedURLException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}finally {
try {
if(inputStream!=null){
inputStream.close();
}
if(conn!=null){
conn.disconnect();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* 输出文件到客户端
* @param wb
* @param fileName
* @param response
* @throws IOException
*/
public static void writeExcel(Workbook wb, String fileName, HttpServletResponse response) throws IOException {
if (response == null) {
throw new NullPointerException("the HttpServletResponse is null");
}
if (StringUtils.isEmpty(fileName)) {
fileName = DateUtils.getDate("yyyyMMddHHmmss") + ".xlsx";
}
response.reset();
response.setContentType("application/octet-stream; charset=utf-8");
response.setHeader("Content-Disposition", "attachment; filename=" + EncodeUtils.encodeUrl(fileName));
// 输出Excel文件
wb.write(response.getOutputStream());
wb.close();
}
/**
* 创建表格样式
* @param wb 工作薄对象
* @return 样式列表
*/
private static Map<String, CellStyle> createStyles(Workbook wb) {
Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
CellStyle style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
Font titleFont = wb.createFont();
titleFont.setFontName("Arial");
titleFont.setFontHeightInPoints((short) 16);
titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
style.setFont(titleFont);
styles.put("title", style);
style = wb.createCellStyle();
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
Font dataFont = wb.createFont();
dataFont.setFontName("Arial");
dataFont.setFontHeightInPoints((short) 10);
style.setFont(dataFont);
styles.put("data", style);
style = wb.createCellStyle();
style.cloneStyleFrom(styles.get("data"));
style.setAlignment(CellStyle.ALIGN_LEFT);
styles.put("data1", style);
style = wb.createCellStyle();
style.cloneStyleFrom(styles.get("data"));
style.setAlignment(CellStyle.ALIGN_CENTER);
styles.put("data2", style);
style = wb.createCellStyle();
style.cloneStyleFrom(styles.get("data"));
style.setAlignment(CellStyle.ALIGN_RIGHT);
styles.put("data3", style);
style = wb.createCellStyle();
style.cloneStyleFrom(styles.get("data"));
// style.setWrapText(true);
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
Font headerFont = wb.createFont();
headerFont.setFontName("Arial");
headerFont.setFontHeightInPoints((short) 10);
headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
headerFont.setColor(IndexedColors.WHITE.getIndex());
style.setFont(headerFont);
styles.put("header", style);
return styles;
}
}
业务代码：
//将数据放入map类型的集合中
List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();
// 往dataList里放数据
for(){
// 、、、、 普通的业务数据
// 如果导出的有图片的话
hashMap.put("imageUrl1", imageUrl1);
hashMap.put("imageUrl2", imageUrl2);
hashMap.put("titleCount", (assetHeadTemp.length-2)); // 除图片外的title长度
hashMap.put("imageCount", 2);// 图片个数
dataList.add(hashMap);
// 条件判断------------------------------------------
// 如果导出的没有图片的话
hashMap.put("titleCount", (assetHeadTemp.length)); //
dataList.add(hashMap);
}
ExcelUtils.createExcel("数据下载",assetHeadTemp, assetNameTemp, dataList, "数据" + DateUtils.getDate("yyyyMMddHHmmss") + ".xlsx", response);
2719
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
