package com.timespark.core.util;
import org.apache.poi.hssf.usermodel.*;
import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.*;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* Author:szy
* Date:2019/6/11
* Description:
*/
public class ExcelUtils {
static final short borderpx = 1;
static final short IMAGETYPE = 250;
/**
* 导出excel表格
* @param head
* @param body
* @return
*/
public static HSSFWorkbook expExcel(List<String> head, List<List<String>> body) {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("Sheet1");
HSSFRow row = sheet.createRow(0);
HSSFCell cell= null;
HSSFCellStyle cellStyle = workbook.createCellStyle();
setBorderStyle(cellStyle, borderpx);
cellStyle.setFont(setFontStyle(workbook, "黑体", (short) 14));
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
sheet.createFreezePane(0,1,0,1);
for (int i = 0; i<head.size(); i++) {
cell = row.createCell(i);
cell.setCellValue(head.get(i));
cell.setCellStyle(cellStyle);
}
HSSFCellStyle cellStyle2 = workbook.createCellStyle();
setBorderStyle(cellStyle2, borderpx);
cellStyle2.setFont(setFontStyle(workbook, "宋体", (short) 12));
cellStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
for (int i = 0; i < body.size(); i++) {
row = sheet.createRow(i + 1);
List<String> paramList = body.get(i);
for (int p = 0; p < paramList.size(); p++) {
cell = row.createCell(p);
cell.setCellValue(paramList.get(p));
cell.setCellStyle(cellStyle2);
}
}
for (int i = 0, isize = head.size(); i < isize; i++) {
sheet.autoSizeColumn(i);
}
return workbook;
}
/**
* 导出图片Excel的方法
*
* @param head
* @param body
* @param outFile
*/
public static void expExcelImage(List<String> head, List<List<String>> body,String outFile) {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("Sheet1");
HSSFRow row = sheet.createRow(0);
HSSFCell cell= null;
HSSFCellStyle cellStyle = workbook.createCellStyle();
setBorderStyle(cellStyle, borderpx);
cellStyle.setFont(setFontStyle(workbook, "黑体", (short) 14));
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平居中
// cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
sheet.createFreezePane(0,1,0,1);
for (int i = 0; i<head.size(); i++) {
cell = row.createCell(i);
cell.setCellValue(head.get(i));
cell.setCellStyle(cellStyle);
}
BufferedImage bufferImg = null;
FileOutputStream fileOut = null;
try {
HSSFCellStyle cellStyle2 = workbook.createCellStyle();
setBorderStyle(cellStyle2, borderpx);
cellStyle2.setFont(setFontStyle(workbook, "宋体", (short) 12));
cellStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平居中
cellStyle2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
bufferImg = ImageIO.read(new File("C:/Users/胡祥德/Desktop/image/7.jpg"));
ImageIO.write(bufferImg, "jpg", byteArrayOut);
// 画图的顶级管理器,一个sheet只能获取一个(一定要注意这点)
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
// // anchor主要用于设置图片的属性
// HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 250, (short) 1, 1+i*10, (short) 5, 8+i*10);
// // 插入图片
// patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
for (int i = 0; i < body.size(); i++) {
row = sheet.createRow(i + 1);
//设置行的高度
row.setHeightInPoints(6*20);
List<String> paramList = body.get(i);
for (int p = 0; p < paramList.size(); p++) {
if (p == paramList.size() - 1) { //最后一列插入图片
int lastCell = paramList.size() - 1;
System.err.println(lastCell);
cell = row.createCell(p);
// cell.setCellValue(paramList.get(p));
cell.setCellStyle(cellStyle2);
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 300, 250, (short) lastCell, i+1, (short)(lastCell+1) , i+1);
patriarch.createPicture(anchor, workbook.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
}else {
cell = row.createCell(p);
cell.setCellValue(paramList.get(p));
cell.setCellStyle(cellStyle2);
}
}
}
for (int i = 0, isize = head.size(); i < isize; i++) {
sheet.autoSizeColumn(i);
}
//设置最后一列的宽度
sheet.setColumnWidth(head.size()-1, 50 * 256);
fileOut = new FileOutputStream(outFile);
// 写入excel文件
workbook.write(fileOut);
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 工具类测试代码
*
*
* @param args
*/
public static void main(String[] args) {
String outFile = "E:/test.xls";
List<String> head = new ArrayList<>();
head.add("序号");
head.add("车牌");
head.add("图片");
List<List<String>> body = new ArrayList<>();
for (int i = 0; i <3 ; i++) {
List<String> list = new ArrayList<>();
list.add(i + "");
list.add("京A11111");
list.add(null);
body.add(list);
}
ExcelUtils.expExcelImage(head,body,outFile);
}
/**
* 文件输出
* @author LiuYang
* @param workbook 填充好的workbook
* @param path 存放的位置
*/
public static void outFile(HSSFWorkbook workbook, String path, HttpServletResponse response) {
SimpleDateFormat fdate=new SimpleDateFormat("yyyyMMdd-HH点mm分");
path = path.substring(0, path.lastIndexOf(".")) + fdate.format(new Date()) + path.substring(path.lastIndexOf("."));
OutputStream os=null;
File file = null;
try {
file = new File(path);
String filename = file.getName();
os = new FileOutputStream(file);
response.addHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(filename, "UTF-8"));
os= new BufferedOutputStream(response.getOutputStream());
response.setContentType("application/vnd.ms-excel;charset=utf-8");
System.out.println(file);
workbook.write(os);
} catch (FileNotFoundException e1) {
e1.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
try {
os.flush();
os.close();
System.gc();
System.out.println(file.delete());
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 设置字体样式
* @author LiuYang
* @param workbook 工作簿
* @param name 字体类型
* @param height 字体大小
* @return HSSFFont
*/
private static HSSFFont setFontStyle(HSSFWorkbook workbook, String name, short height) {
HSSFFont font = workbook.createFont();
font.setFontHeightInPoints(height);
font.setFontName(name);
return font;
}
/**
* 设置单元格样式
* @author LiuYang
* @param cellStyle 工作簿
* @param border border样式
*/
private static void setBorderStyle(HSSFCellStyle cellStyle, short border) {
cellStyle.setBorderBottom(border); // 下边框
cellStyle.setBorderLeft(border);// 左边框
cellStyle.setBorderTop(border);// 上边框
cellStyle.setBorderRight(border);// 右边框
}
/**
* 设置图片的单元格样式
* @param cellStyle 工作簿
* @param TopAndBborder 上边框 下边框 样式
* @param RandLBorder 左边框 右边框 样式
*/
private static void setBorderStyle0(HSSFCellStyle cellStyle, short TopAndBborder,short RandLBorder) {
cellStyle.setBorderBottom(TopAndBborder); // 下边框
cellStyle.setBorderTop(TopAndBborder);// 上边框
cellStyle.setBorderLeft(RandLBorder);// 左边框
cellStyle.setBorderRight(RandLBorder);// 右边框
}
}