package com.safone.util;
import java.awt.image.BufferedImage;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
* Excel导入导出
* @Author: huangdemin
* @Date: 2020/3/3 9:47
*/
public class ExcelUtil {
/**
* 导出多个sheet的excel
* @param name
* @param mapList
* @param response
* @param <T>
*/
public static <T> void exportMultisheetExcel(String name, List<Map> mapList, HttpServletResponse response) {
BufferedOutputStream bos = null;
try {
String fileName = name + ".xlsx";
bos = getBufferedOutputStream(fileName, response);
doExport(mapList, bos);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (bos != null) {
bos.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* 从excel中读内容
* @param filePath
* @param sheetIndex
* @return
*/
public static List<Map<String, String>> readExcel(String filePath, Integer sheetIndex) {
List<Map<String, String>> dataList = new ArrayList<>();
Workbook wb = ExcelUtil.createWorkBook(filePath);
if (wb != null) {
Sheet sheet = wb.getSheetAt(sheetIndex);
int maxRownum = sheet.getPhysicalNumberOfRows();
Row firstRow = sheet.getRow(0);
int maxColnum = firstRow.getPhysicalNumberOfCells();
String columns[] = new String[maxColnum];
for (int i = 0; i < maxRownum; i++) {
Map<String, String> map = null;
if (i > 0) {
map = new LinkedHashMap<>();
firstRow = sheet.getRow(i);
}
if (firstRow != null) {
String cellData = null;
for (int j = 0; j < maxColnum; j++) {
cellData = (String) ExcelUtil.getCellFormatValue(firstRow.getCell(j));
if (i == 0) {
columns[j] = cellData;
} else {
map.put(columns[j], cellData);
}
}
} else {
break;
}
if (i > 0) {
dataList.add(map);
}
}
}
return dataList;
}
private static BufferedOutputStream getBufferedOutputStream(String fileName, HttpServletResponse response) throws Exception {
response.setContentType("application/x-msdownload");
response.setHeader("Content-Disposition", "attachment;filename="
+ new String(fileName.getBytes("gb2312"), "ISO8859-1"));
return new BufferedOutputStream(response.getOutputStream());
}
private static <T> void doExport(List<Map> mapList, OutputStream outputStream) {
int maxBuff = 100;
// 创建excel工作文本,100表示默认允许保存在内存中的行数
HSSFWorkbook wb = new HSSFWorkbook();
try {
for (int i = 0; i < mapList.size(); i++) {
Map map = mapList.get(i);
String[] headers = (String[]) map.get("headers");
Collection<T> dataList = (Collection<T>) map.get("dataList");
String fileName = (String) map.get("fileName");
createSheet(wb, null, headers, dataList, fileName, maxBuff);
}
if (outputStream != null) {
wb.write(outputStream);
}
} catch (Exception ex) {
ex.printStackTrace();
}
}
private static <T> void createSheet(HSSFWorkbook wb, String[] exportFields, String[] headers, Collection<T> dataList, String fileName, int maxBuff) throws NoSuchFieldException, IllegalAccessException, IOException {
HSSFSheet sh = wb.createSheet(fileName);
CellStyle style = wb.createCellStyle();
CellStyle style2 = wb.createCellStyle();
//创建表头
Font font = wb.createFont();
font.setFontName("微软雅黑");
font.setFontHeightInPoints((short) 11);//设置字体大小
style.setFont(font);//选择需要用到的字体格式
style.setFillForegroundColor(HSSFColor.YELLOW.index);// 设置背景色
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
style2.setFont(font);//选择需要用到的字体格式
style2.setFillForegroundColor(HSSFColor.WHITE.index);// 设置背景色
style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //垂直居中
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平向下居中
style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
style2.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
style2.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
HSSFRow headerRow = sh.createRow(0); //表头
int headerSize = headers.length;
for (int cellnum = 0; cellnum < headerSize; cellnum++) {
Cell cell = headerRow.createCell(cellnum);
cell.setCellStyle(style);
sh.setColumnWidth(cellnum, 4000);
cell.setCellValue(headers[cellnum]);
}
int rownum = 0;
Iterator<T> iterator = dataList.iterator();
while (iterator.hasNext()) {
T data = iterator.next();
HSSFRow row=sh.createRow(rownum + 1);
row.setHeight((short)2500);
Field[] fields = getExportFields(data.getClass(), exportFields);
for (int cellnum = 0; cellnum < headerSize; cellnum++) {
sh.setColumnWidth(cellnum, 30*256);
HSSFCell cell = row.createCell(cellnum);
cell.setCellStyle(style2);
Field field = fields[cellnum];
try {
setData(field, data, field.getName(), cell);
} catch (Exception e) {
e.printStackTrace();
}
}
rownum = sh.getLastRowNum();
}
}
private static <T> void doExport(String[] headers, String[] exportFields, Collection<T> dataList,
String fileName, OutputStream outputStream) {
int maxBuff = 100;
// 创建excel工作文本,100表示默认允许保存在内存中的行数
HSSFWorkbook wb=new HSSFWorkbook( );
try {
createSheet(wb, exportFields, headers, dataList, fileName, maxBuff);
if (outputStream != null) {
wb.write(outputStream);
}
} catch (Exception ex) {
ex.printStackTrace();
}
}
/**
* 获取单条数据的属性
*
* @param object
* @param property
* @param <T>
* @return
* @throws NoSuchFieldException
* @throws IllegalAccessException
*/
private static <T> Field getDataField(T object, String property) throws NoSuchFieldException, IllegalAccessException {
Field dataField;
if (property.contains(".")) {
String p = property.substring(0, property.indexOf("."));
dataField = object.getClass().getDeclaredField(p);
return dataField;
} else {
dataField = object.getClass().getDeclaredField(property);
}
return dataField;
}
private static Field[] getExportFields(Class<?> targetClass, String[] exportFieldNames) {
Field[] fields = null;
if (exportFieldNames == null || exportFieldNames.length < 1) {
fields = targetClass.getDeclaredFields();
} else {
fields = new Field[exportFieldNames.length];
for (int i = 0; i < exportFieldNames.length; i++) {
try {
fields[i] = targetClass.getDeclaredField(exportFieldNames[i]);
} catch (Exception e) {
try {
fields[i] = targetClass.getSuperclass().getDeclaredField(exportFieldNames[i]);
} catch (Exception e1) {
throw new IllegalArgumentException("无法获取导出字段", e);
}
}
}
}
return fields;
}
/**
* 根据属性设置对应的属性值
*
* @param dataField 属性
* @param object 数据对象
* @param property 表头的属性映射
* @param cell 单元格
* @param <T>
* @return
* @throws IOException
* @throws IllegalAccessException
* @throws NoSuchFieldException
* @throws SecurityException
* @throws NoSuchMethodException
*/
private static <T> void setData(Field dataField, T object, String property, HSSFCell cell) throws IOException{
InputStream inputstreamByBase64=null;
try {
dataField.setAccessible(true); //允许访问private属性
Object val = dataField.get(object); //获取属性值
HSSFSheet sh = cell.getSheet(); //获取excel工作区
CellStyle style = cell.getCellStyle(); //获取单元格样式
int cellnum = cell.getColumnIndex();
if (val != null) {
if (dataField.getType().toString().endsWith("String")) {
String fieldName = dataField.getName();
if (fieldName.endsWith("Base64")) {//
fieldName = fieldName.replaceFirst(fieldName.substring(0, 1), fieldName.substring(0, 1).toUpperCase());
dataField.setAccessible(true);
Method m = object.getClass().getMethod("get" + fieldName);
String base64 = (String) m.invoke(object);//获取Base64编码
inputstreamByBase64 = Base64Util.getInputstreamByBase64(base64);
HSSFPatriarch patriarch = cell.getSheet().createDrawingPatriarch();
HSSFWorkbook wb = cell.getSheet().getWorkbook();
insertImage(wb,patriarch,getImageData(ImageIO.read(inputstreamByBase64)),cell.getRowIndex(),cellnum,1);
}else{//如果是文字那么就直接设置字符串的值
cell.setCellValue((String) val);
}
} else if (dataField.getType().toString().endsWith("Integer") || dataField.getType().toString().endsWith("int")) {
cell.setCellValue((Integer) val);
} else if (dataField.getType().toString().endsWith("Long") || dataField.getType().toString().endsWith("long")) {
cell.setCellValue(val.toString());
} else if (dataField.getType().toString().endsWith("Double") || dataField.getType().toString().endsWith("double")) {
cell.setCellValue((Double) val);
} else if (dataField.getType().toString().endsWith("Date")) {
DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
cell.setCellValue(format.format((Date) val));
} else if (dataField.getType().toString().endsWith("List")) {
List list1 = (List) val;
int size = list1.size();
for (int i = 0; i < size; i++) {
//加1是因为要去掉点号
int start = property.indexOf(dataField.getName()) + dataField.getName().length() + 1;
String tempProperty = property.substring(start, property.length());
Field field = getDataField(list1.get(i), tempProperty);
HSSFCell tempCell = cell;
if (i > 0) {
int rowNum = cell.getRowIndex() + i;
HSSFRow row = sh.getRow(rowNum);
if (row == null) {//另起一行
row = sh.createRow(rowNum);
//合并之前的空白单元格(在这里需要在header中按照顺序把list类型的字段放到最后,方便显示和合并单元格)
for (int j = 0; j < cell.getColumnIndex(); j++) {
sh.addMergedRegion(new CellRangeAddress(cell.getRowIndex(), cell.getRowIndex() + size - 1, j, j));
Cell c = row.createCell(j);
c.setCellStyle(style);
}
}
tempCell = row.createCell(cellnum);
tempCell.setCellStyle(style);
}
//递归传参到单元格并获取偏移量(这里获取到的偏移量都是第二层后list的偏移量)
setData(field, list1.get(i), tempProperty, tempCell);
}
} else {
if (property.contains(".")) {
String p = property.substring(property.indexOf(".") + 1, property.length());
Field field = getDataField(val, p);
setData(field, val, p, cell);
} else {
cell.setCellValue(val.toString());
}
}
}
} catch (Exception e) {
e.printStackTrace();
}finally {
if (inputstreamByBase64!=null) {
inputstreamByBase64.close();
}
}
}
private static Workbook createWorkBook(String filePath) {
Workbook wb = null;
if (filePath == null) {
return null;
}
String extString = filePath.substring(filePath.lastIndexOf("."));
InputStream is = null;
try {
is = new FileInputStream(filePath);
if (".xls".equals(extString)) {
return wb = new HSSFWorkbook(is);
} else if (".xlsx".equals(extString)) {
return wb = new XSSFWorkbook(is);
} else {
return wb;
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return wb;
}
/**
* 将字段转为相应的格式
* @param cell
* @return
*/
private static Object getCellFormatValue(Cell cell) {
Object cellValue = null;
if (cell != null) {
//判断cell类型
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC: {
cellValue = String.valueOf(cell.getNumericCellValue());
break;
}
case Cell.CELL_TYPE_FORMULA: {
if (DateUtil.isCellDateFormatted(cell)) {
cellValue = cell.getDateCellValue();转换为日期格式YYYY-mm-dd
} else {
cellValue = String.valueOf(cell.getNumericCellValue()); //数字
}
break;
}
case Cell.CELL_TYPE_STRING: {
cellValue = cell.getRichStringCellValue().getString();
break;
}
default:
cellValue = "";
}
} else {
cellValue = "";
}
return cellValue;
}
public static void insertImage(HSSFWorkbook wb,HSSFPatriarch pa,byte[] data,int row,int column,int index){
int x1=150; //距离
int y1=0;
int x2=x1+750;
int y2=255;
//* 前四个参数是控制图片在单元格的位置,分别是图片距离单元格left,top,right,bottom的像素距离
//* 后四个参数,前连个表示图片左上角所在的cellNum和 rowNum,后天个参数对应的表示图片右下角所在的cellNum和 rowNum
HSSFClientAnchor anchor = new HSSFClientAnchor(x1,y1,x2,y2,(short)column,row,(short)column,row);
anchor.setAnchorType(2);
pa.createPicture(anchor , wb.addPicture(data,HSSFWorkbook.PICTURE_TYPE_JPEG));
}
//从图片里面得到字节数组
public static byte[] getImageData(BufferedImage bi){
try{
ByteArrayOutputStream bout=new ByteArrayOutputStream();
ImageIO.write(bi,"PNG",bout);
return bout.toByteArray();
}catch(Exception exe){
exe.printStackTrace();
return null;
}
}
}
package com.safone.util;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.nio.file.StandardOpenOption;
import org.apache.commons.lang.StringUtils;
import com.sun.org.apache.xerces.internal.impl.dv.util.Base64;
import sun.misc.BASE64Decoder;
public class Base64Util {
/**
* 本地图片转base64编码
*
* @param filePath 文件图片所在路径
*
* @return base64编码
*/
public static String imageFilePathToBase64(String filePath) throws Exception{
if(StringUtils.isBlank(filePath)){
return null;
}
String encode="";
try{
byte[] bytes = Files.readAllBytes(Paths.get(filePath));
encode = Base64.encode(bytes);
}catch (Exception e){
throw e;
}
return encode;
}
/**
* base64编码转成图片文件
*
* @param base64 图片的base64编码
* @param filePath 图片文件的保存路径
*
* @return
* @throws Exception
*/
public static String decryptByBase64(String base64, String filePath) throws Exception{
if (base64 == null && filePath == null) {
return "生成文件失败,请给出相应的数据。";
}
try {
Files.write(Paths.get(filePath),Base64.decode(base64), StandardOpenOption.CREATE);
} catch (IOException e) {
throw e;
}
return "指定路径下生成文件成功!";
}
public static InputStream getInputstreamByBase64(String fileBase64String){
InputStream input = null;
BufferedOutputStream bos = null;
FileOutputStream fos = null;
File file = null;
try {
BASE64Decoder decoder = new BASE64Decoder();
byte[] bytes = decoder.decodeBuffer(fileBase64String);
input = new ByteArrayInputStream(bytes);
return input;
}
catch (Exception e) {
e.printStackTrace();
// TODO: handle exception
}
return input;
}
}