package com.sjdf.erp.common.utils;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.HttpURLConnection;
import java.net.URL;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;
import javax.servlet.ServletOutputStream;
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.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Font;
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.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;
import com.sjdf.erp.common.annotation.ExcelDesc;
import com.sjdf.erp.common.annotation.IsDictionary;
import com.sjdf.erp.common.constant.CommonPlatformConstant;
import com.sjdf.erp.common.constant.ConstBusiness;
import com.sjdf.erp.common.constant.purchase.Msg;
import com.sjdf.erp.common.dictionary.bean.sys.PlatformConstant;
import com.sjdf.erp.common.dictionary.cache.ConfigManager;
import com.sjdf.erp.common.net.HttpSocket;
import com.sjdf.erp.common.vo.Message;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
public class ExcelUtils {
private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtils.class);
public static final String XLSX = ".xlsx";
public static final String XLS=".xls";
public static final int IMG_HEIGTH = 30; // 图片高度显示像素
public static final int IMG_WIDTH = 30; // 图片宽度显示像素
/**
* 解析Excel文件（.xls和.xlsx都支持）
* @param file Excel的File对象
* @return 解析后的JSONArray对象
* @throws Exception
*/
public static Message readExcel(File file) throws Exception {
if (file == null || file.getName() == null) {
throw new NullPointerException(Msg.UPLOAD_FILE_NULL);
}
String fileName = file.getName().toLowerCase();
Workbook book = null;
if (fileName.endsWith(XLSX)) {
book = new XSSFWorkbook(file);
} else if (fileName.endsWith(XLS)) {
POIFSFileSystem poifsFileSystem = new POIFSFileSystem(new FileInputStream(file));
book = new HSSFWorkbook(poifsFileSystem);
}
if (book != null) {
Message message = read(book);
book.close();
return message;
}
return Message.createMessage(Msg.FILE_MUST_BE_XLS_OR_XLSX, Collections.emptyList());
}
/**
* 解析Excel文件（.xls和.xlsx都支持）
* @param file Excel的MultipartFile 对象
* @return 解析后的JSONArray对象
* @throws Exception
*/
public static Message readExcel(MultipartFile file)throws Exception {
if (file == null || file.getOriginalFilename() == null) {
return Message.createMessage(Msg.UPLOAD_FILE_NULL, Collections.emptyList());
}
Workbook book = null;
String name = file.getOriginalFilename().toLowerCase();
if (name.endsWith(XLSX)) {
book =new XSSFWorkbook(file.getInputStream());
} else if (name.endsWith(XLS)) {
book = new HSSFWorkbook(file.getInputStream());
}
if (book != null) {
Message message = read(book);
book.close();
return message;
}
return Message.createMessage(Msg.FILE_MUST_BE_XLS_OR_XLSX, Collections.emptyList());
}
/**
* 读取不定列excel
* @param file
* @return List<List<String>>
* @throws Exception
*/
public static Message readIndeterminateExcel(MultipartFile file)throws Exception {
if (file == null || file.getOriginalFilename() == null) {
return Message.createMessage(Msg.UPLOAD_FILE_NULL, Collections.emptyList());
}
Workbook book = null;
String name = file.getOriginalFilename().toLowerCase();
if (name.endsWith(XLSX)) {
book =new XSSFWorkbook(file.getInputStream());
} else if (name.endsWith(XLS)) {
book = new HSSFWorkbook(file.getInputStream());
}
if (book != null) {
Message message = readIndeterminate(book);
book.close();
return message;
}
return Message.createMessage(Msg.FILE_MUST_BE_XLS_OR_XLSX, Collections.emptyList());
}
private static Message readIndeterminate(Workbook book) throws IOException {
Sheet sheet = book.getSheetAt(0);
int rowEnd = sheet.getLastRowNum(); // 尾行下标
Row firstRow = sheet.getRow(0);
if (firstRow == null) {
return Message.createMessage(Msg.EXCEL_ERROR, Collections.emptyList());
}
List<List<String>> data = new ArrayList<>();
for(int i = 0; i <= rowEnd ; i++) {
Row eachRow = sheet.getRow(i);
if(eachRow == null) {
continue;
}
int cellEnd = eachRow.getLastCellNum();
List<String> rowData = new ArrayList<>();
data.add(rowData);
for (int k = 0; k < cellEnd; k++) {
String val = null;
try {
val = getValue(eachRow.getCell(k));
} catch (Exception e) {
LOGGER.error(i + "行" + k + "列获取值错误", e);
}
rowData.add(val);
}
}
Message message = Message.createMessage();
message.setReturnData(data);
return message;
}
/**
* 获取excel 表头
* @param file Excel的MultipartFile 对象
* @return 返回解析json
* @throws IOException
*/
public static Message getExcelHead(MultipartFile file) throws IOException {
if (file == null || file.getOriginalFilename() == null) {
return Message.createMessage(Msg.UPLOAD_FILE_NULL, Collections.emptyList());
}
Workbook book = null;
String name = file.getOriginalFilename().toLowerCase();
if (name.endsWith(XLSX)) {
book =new XSSFWorkbook(file.getInputStream());
} else if (name.endsWith(XLS)) {
book = new HSSFWorkbook(file.getInputStream());
}
if (book != null) {
Sheet sheetAt = book.getSheetAt(0);
int firstRowNum = sheetAt.getFirstRowNum();
Row row = sheetAt.getRow(firstRowNum);
if (row == null) {
return Message.createMessage(Msg.EXCEL_ERROR, Collections.emptyList());
}
int cellStart = row.getFirstCellNum();
int cellEnd = row.getLastCellNum();
JSONObject obj = new JSONObject();
Integer num = 0;
for (int j = cellStart; j < cellEnd; j++) {
// 表头遇到空格跳过
String val = getValue(row.getCell(j));
if (val == null || val.trim().length() == 0) {
continue;
}
obj.put(num, val);
num += 1;
}
book.close();
if (obj.isEmpty()) {
return Message.createMessage(Msg.EXCEL_ERROR, Collections.emptyList());
}
Message message = Message.createMessage();
message.setReturnData(obj);
return message;
}
return Message.createMessage(Msg.FILE_MUST_BE_XLS_OR_XLSX, Collections.emptyList());
}
/**
* 解析数据
* @param sheet 表格sheet对象
* @param book 用于流关闭
* @return excel表转换后的JSONArray对象
* @throws IOException
*/
private static Message read(Workbook book) throws IOException {
Sheet sheet = book.getSheetAt(0);
int rowStart = sheet.getFirstRowNum(); // 首行下标
int rowEnd = sheet.getLastRowNum(); // 尾行下标
// 获取第一行JSON对象键
Row firstRow = sheet.getRow(rowStart);
if (firstRow == null) {
return Message.createMessage(Msg.EXCEL_ERROR, Collections.emptyList());
}
int cellStart = firstRow.getFirstCellNum();
int cellEnd = firstRow.getLastCellNum();
Map<Integer, String> keyMap = new HashMap<Integer, String>();
for (int j = cellStart; j < cellEnd; j++) {
// 表头遇到空格停止解析
String val = getValue(firstRow.getCell(j));
if (val == null || val.trim().length() == 0) {
cellEnd = j;
break;
}
keyMap.put(j,val);
}
if (PlatformUtils.isEmpty(keyMap)) {
return Message.createMessage(Msg.EXCEL_ERROR, Collections.emptyList());
}
// 获取每行JSON对象的值
JSONArray array = new JSONArray();
// 如果首行与尾行相同，表明只有一行，返回表头数据
if (rowStart == rowEnd) {
JSONObject object = new JSONObject();
for (int i : keyMap.keySet()) {
object.put(keyMap.get(i), "");
}
array.add(object);
Message message = Message.createMessage();
message.setReturnData(array);
return message;
}
for(int i = rowStart+1; i <= rowEnd ; i++) {
Row eachRow = sheet.getRow(i);
JSONObject obj = new JSONObject();
StringBuffer sb = new StringBuffer();
for (int k = cellStart; k < cellEnd; k++) {
if (eachRow != null) {
Cell cell = eachRow.getCell(k);
String val = getValue(cell);
sb.append(val); // 所有数据添加到里面，用于判断该行是否为空
obj.put(keyMap.get(k),val);
}
}
if (sb.toString().length() > 0) {
array.add(obj);
}
}
Message message = Message.createMessage();
message.setReturnData(array);
return message;
}
/**
* 获取每个单元格的数据
* @param cell 单元格对象
* @param isKey 是否为键：true-是，false-不是。 如果解析Json键，值为空时报错；如果不是Json键，值为空不报错
* @return 该单元格数据
* @throws IOException
*/
private static String getValue(Cell cell) throws IOException {
// 空白或空
if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK ) {
return "";
}
// 0. 数字 类型
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
return df.format(date);
}
cell.setCellType(Cell.CELL_TYPE_STRING);
String val = cell.getStringCellValue()+"";
return getEValue(val);
}
// 1. String类型
if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
String val = cell.getStringCellValue();
if (val == null || val.trim().length() == 0) {
return "";
}
return PlatformUtils.xssFilter(val.trim());
}
// 2. 公式 CELL_TYPE_FORMULA
if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
try {
double cellValue = cell.getNumericCellValue();
return String.valueOf(cellValue);
} catch (Exception e) {
return "0.00";
}
}
// 4. 布尔值 CELL_TYPE_BOOLEAN
if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
return cell.getBooleanCellValue()+"";
}
// 5. 错误 CELL_TYPE_ERROR
return "";
}
public static String getEValue(String s){
// 非科学技术方法，直接去掉
if (!s.contains("E")) {
return s;
}
BigDecimal b = null;
try {
b = new BigDecimal(s);
} catch (Exception e) {
return s;
}
int len = Integer.valueOf(s.split("E")[1]);
if (len < 0) {
// 小数情况（如：5.0000000000000001E-4）保留10位，去掉末尾的0
return b.setScale(10, BigDecimal.ROUND_HALF_UP).stripTrailingZeros().toString();
} else {
// 其他情况：5.236E8
String num = s.split("E")[0];
if (len > 20) { // len 大于20位的话，数字过大，直接返回字符串
return s;
}
num = num + "00000000000000000000";
// 小数点向后移动 len 位，不够添0
return num.replace(".", "").substring(0, num.split("\\.")[0].length() + len);
}
}
/**
* 解析Excel，获取List<Bean>
* @param c Bean对象类型
* @param file 上传文件Excel
* @return 数据合法的List<Bean>（前提是Bean属性做了字段验证）
* @throws Exception 模版不正确异常
*/
@SuppressWarnings("unchecked")
public static <T> Message getBeanList(Class<T> c, MultipartFile file) {
try {
// 解析上传文件为JsonArray
JSONArray arr = null;
Message msg = readExcel(file);
if (msg.hasErrorMessage()) {
return msg;
}
arr = (JSONArray) msg.getReturnData();
// 解析List<Bean>
List<T> list = new ArrayList<T>();
for (int i = 0; i < arr.size(); i++) {
JSONObject obj = (JSONObject) arr.get(i);
Message msg1 = getBean(c, obj);
if (msg1.hasErrorMessage()) {
return msg1;
} else {
list.add((T) msg1.getReturnData());
}
}
Message msg2 = Message.createMessage();
msg2.setReturnData(list);
return msg2;
} catch (Exception e) {
LOGGER.error(e.getMessage(), e);
return Message.createMessage(e.getMessage(), Collections.emptyList());
}
}
/**
* 获取单个Bean
* @param c
* @param obj
* @return
* @throws Exception
*/
private static <T> Message getBean(Class<T> c, JSONObject obj) throws Exception{
T t = c.newInstance();
List<Field> fields = ASMUtils.getDeclaredFieldsRecursionSupper(c);
for (int i = 0; i < fields.size(); i++) {
Field field = fields.get(i);
String fieldName = field.getName();
// 获取ExcleDesc注解属性
ExcelDesc excelDesc = field.getAnnotation(ExcelDesc.class);
if (excelDesc != null) {
String cname = excelDesc.cname();
if (cname == null || cname.trim().length() == 0) {
throw new NullPointerException(String.format("the field '%s' without cname value.", fieldName));
}
// 判断是否必须为必须字段，判断cname
String val = null;
if (obj.has(cname)) {
val = obj.getString(cname);
} else {
if (excelDesc.isRequired()) {
return Message.createMessage(Msg.EXCEL_ERROR);
} else {
continue;
}
}
// 判断字段是否为空，判断iskey
if (!PlatformUtils.hasText(val)) {
if (excelDesc.isKey()) {
return Message.createMessage(Msg.XXX_DATA_CAN_NOT_BE_NULL, cname);
}
// 非必须字段为空情况不继续解析
continue;
}
// 获取具体值
field.setAccessible(true);
// 获取IsDictionary注解属性
IsDictionary dict = field.getAnnotation(IsDictionary.class);
if (dict != null) {
int intVal = ConfigManager.getInstance().getAttrByName(dict.clazz(), dict.filed(), val);
field.set(t,intVal);
continue;
}
// 其余情况根据类型赋值
String fieldClassName = field.getType().getSimpleName();
try {
if ("String".equalsIgnoreCase(fieldClassName)) {
field.set(t, val);
} else if ("boolean".equalsIgnoreCase(fieldClassName)) {
field.set(t, obj.getBoolean(cname));
} else if ("int".equalsIgnoreCase(fieldClassName) || "Integer".equals(fieldClassName)) {
field.set(t, obj.getInt(cname));
} else if ("double".equalsIgnoreCase(fieldClassName)) {
field.set(t, obj.getDouble(cname));
} else if ("long".equalsIgnoreCase(fieldClassName)) {
field.set(t, obj.getLong(cname));
} else if ("BigDecimal".equalsIgnoreCase(fieldClassName)) {
field.set(t, new BigDecimal(val));
}
} catch (Exception e) {
LOGGER.error(e.getMessage(), e);
return Message.createMessage(Msg.XXX_DATA_ERROR, cname);
}
}
}
Message message = Message.createMessage();
message.setReturnData(t);
return message;
}
/**
* Excel导出
* @param title 导出Excel文件名称
* @param rowList 第一个List为表头，其余行为表数据
* @param resp HttpServletResponse 对象。不要用注入的resp，用controler方法中声明的resp。否则会去寻找jsp
* @throws IOException
*/
public static void writeExcel(String title,List<List<Object>> rowList,HttpServletResponse resp) throws IOException{
if (resp == null) {
throw new NullPointerException("the HttpServletResponse is null");
}
SXSSFWorkbook book = warpSingleWorkbook(title, rowList);
// 响应客户端
String filename = new String(title.getBytes("UTF-8"), "ISO-8859-1");
resp.reset();
resp.setHeader("Content-disposition", "attachment; filename=" + filename +XLS);
resp.setContentType("application/vnd.ms-excel;charset=UTF-8");
// 输出Excel文件
ServletOutputStream outputStream = resp.getOutputStream();
book.write(outputStream);
book.close();
outputStream.close();
}
/**
* Excel导出设置Workbook
* @param title 导出Excel文件名称
* @param rowList 第一个List为表头，其余行为表数据
* @throws IOException
*/
public static SXSSFWorkbook warpSingleWorkbook(String title,List<List<Object>> rowList) throws IOException {
String filename = title;
if (!PlatformUtils.hasText(title)) {
filename = new SimpleDateFormat("yyMMddHHmmss").format(new Date());
}
if (rowList == null || rowList.isEmpty()) {
throw new NullPointerException("the row list is null");
}
SXSSFWorkbook book = new SXSSFWorkbook();
// 创建表
setWorkBookData(book, filename, rowList);
return book;
}
private static void setWorkBookData(SXSSFWorkbook book, String sheetName, List<List<Object>> rowList){
Sheet sheet = book.createSheet(sheetName);
Drawing patriarch = sheet.createDrawingPatriarch();
// 设置表头样式
CellStyle style = book.createCellStyle();
// 设置居左
style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
// 检测表头数据（表头不允许数据为空）
List<Object> head = rowList.get(0);
for (Object key : head) {
if (!PlatformUtils.hasText(key.toString())) {
throw new NullPointerException("there is a blank exist head row");
}
}
// 写数据
int size = rowList.get(0).size();
for (int i = 0; i < rowList.size(); i++) {
List<Object> row = rowList.get(i);
if (row == null || row.isEmpty()) {
throw new NullPointerException("the "+(i+1)+"th row is null");
}
if (size != row.size()) {
throw new IllegalArgumentException("the cell number of "+(i+1)+"th row is different form the first");
}
Row sr = sheet.createRow(i);
for (int j = 0; j < row.size(); j++) {
if (row.get(j) != null && row.get(j) instanceof URL) {
URL url = (URL)row.get(j);
sr.setHeight((short)(IMG_HEIGTH * IMG_WIDTH));
drawPictureIntoExcel(book, patriarch, i, j, url);
} else {
setExcelValue(sr.createCell(j), row.get(j), style);
}
}
}
}
/**
* 导出多页Excel
* @param sheetMap key为每个页的名称，value为表头行+数据行
* @return
* @throws IOException
*/
public static SXSSFWorkbook warpSingleWorkbook(Map<String, List<List<Object>>> sheetMap) throws IOException {
SXSSFWorkbook book = new SXSSFWorkbook();
for (String key : sheetMap.keySet()) {
String sheetName = Pattern.compile("[[/*#]]").matcher(key).replaceAll("");
setWorkBookData(book, sheetName, sheetMap.get(key));
}
return book;
}
/**
* Excel导出设置Workbook（表头自动换行）
* @param title 导出Excel文件名称
* @param rowList 第一个List为表头，其余行为表数据
* @throws IOException
*/
public static SXSSFWorkbook getSingleWorkbook(String title,List<List<Object>> rowList) throws IOException {
String filename = title;
if (!PlatformUtils.hasText(title)) {
filename = new SimpleDateFormat("yyMMddHHmmss").format(new Date());
}
if (rowList == null || rowList.isEmpty()) {
throw new NullPointerException("the row list is null");
}
SXSSFWorkbook book = new SXSSFWorkbook();
// 创建表
Sheet sheet = book.createSheet(filename);
Font headFont = book.createFont();
Drawing patriarch = sheet.createDrawingPatriarch();
// 设置表头样式
XSSFCellStyle style = (XSSFCellStyle)book.createCellStyle();
CellStyle headStyle = book.createCellStyle();
// 设置居左
style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平居中
headStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//设置垂直居中
headStyle.setWrapText(true);// 自动换行
headFont.setBoldweight(Font.BOLDWEIGHT_BOLD);// 加粗
headStyle.setFont(headFont);
// 检测表头数据（表头不允许数据为空）
List<Object> head = rowList.get(0);
Row headRow = sheet.createRow(0);
for (int i = 0; i < head.size(); i++) {
if (!PlatformUtils.hasText(head.get(i).toString())) {
book.close();
throw new NullPointerException("there is a blank exist head row");
}
setExcelValue(headRow.createCell(i), head.get(i), headStyle);
}
// 写数据
style.setWrapText(true);
createBodyData(rowList, book, sheet, style, patriarch, false);
return book;
}
/**
* Excel写网络图片
* @param wb
* @param patriarch
* @param rowIndex
* @param url
*/
private static void drawPictureIntoExcel(SXSSFWorkbook wb,Drawing patriarch,int rowIndex, int cloumIndex, URL url){
// rowIndex代表当前行
try(InputStream is = url.openStream(); ByteArrayOutputStream swapStream = new ByteArrayOutputStream();) {
byte[] buff = new byte[100];
int rc = 0;
while ((rc = is.read(buff, 0, 100)) > 0) {
swapStream.write(buff, 0, rc);
}
// 设置图片位置
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, cloumIndex, rowIndex, cloumIndex+1, rowIndex+1);
anchor.setAnchorType(0);
patriarch.createPicture(anchor, wb.addPicture(swapStream.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
swapStream.close();
} catch (Exception e) {
LOGGER.error("图片请求失败：" + url.toString());
}
}
/**
* Excel导出
* @param title 导出Excel文件名称
* @param sheets 包含多个模块的sheets也就是多个rowList,rowList 第一个List为表头，其余行为表数据
* object 是一个list<Object>
* @param resp HttpServletResponse 对象
* @throws IOException
*/
public static void writeExcels(String title, List<List<Object>> sheets, HttpServletResponse resp) throws IOException {
if (resp == null) {
throw new NullPointerException("the HttpServletResponse is null");
}
String filename = title;
if (!PlatformUtils.hasText(filename)) {
filename = new SimpleDateFormat("yyMMddHHmmss").format(new Date());
}
if (sheets == null || sheets.isEmpty()) {
throw new NullPointerException("the row list is null");
}
// 创建表
HSSFWorkbook book = wrapMultWorkbook(filename, sheets);
// 响应客户端
filename = new String(filename.getBytes("UTF-8"), "ISO-8859-1");
resp.reset();
resp.setHeader("Content-disposition", "attachment; filename=" + filename +XLS);
resp.setContentType("application/vnd.ms-excel;charset=UTF-8");
// 输出Excel文件
book.write(resp.getOutputStream());
book.close();
}
/**
* Excel导出到项目目录下
* @param title 导出Excel文件名称
* @param sheets 包含多个模块的sheets也就是多个rowList,rowList 第一个List为表头，其余行为表数据
* object 是一个list<Object>
* @param filePath
* @param resp HttpServletResponse 对象
* @throws IOException
*/
public static String writeExcelInWeb(String title, List<List<Object>> sheets, String filePath) throws IOException {
String filename = title;
if (!PlatformUtils.hasText(filename)) {
filename = new SimpleDateFormat("yyMMddHHmmss").format(new Date());
}
if (sheets == null || sheets.isEmpty()) {
throw new NullPointerException("the row list is null");
}
// 创建表
String fileName = filename +".xls";
HSSFWorkbook book = wrapMultWorkbook(filename, sheets);
FileOutputStream stream = new FileOutputStream(filePath +"/" + fileName);
book.write(stream);
book.close();
return fileName;
}
/**
* 将数据写入多个Excel sheet中
* @param title 标题
* @param sheets sheet
* @param book Excel对象
* @throws IOException
*/
public static HSSFWorkbook wrapMultWorkbook(String title, List<List<Object>> sheets) throws IOException {
HSSFWorkbook book = new HSSFWorkbook();
for (int p = 0; p < sheets.size(); p++) {
HSSFSheet sheet = book.createSheet(title+"_"+p);
sheet.setDefaultRowHeightInPoints(CommonPlatformConstant.INT_15);
// 检测表头数据（表头不允许数据为空）
@SuppressWarnings("unchecked")
List<String> head = (List<String>) sheets.get(p).get(0);
for (Object key : head) {
if (!PlatformUtils.hasText(key.toString())) {
book.close();
throw new NullPointerException("there is a blank exist head row");
}
}
// 写数据
@SuppressWarnings("unchecked")
List<Object> data = (List<Object>) sheets.get(p).get(0);
int size = data.size();
for (int i = 0; i < sheets.get(p).size(); i++) {
@SuppressWarnings("unchecked")
List<Object> row = (List<Object>) sheets.get(p).get(i);
if (row == null || row.isEmpty()) {
book.close();
throw new NullPointerException("the "+(i+1)+"th row is null");
}
if (size != row.size()) {
book.close();
throw new IllegalArgumentException("the cell number of "+(i+1)+"th row is different form the first");
}
HSSFRow sr = sheet.createRow(i);
for (int j = 0; j < row.size(); j++) {
if (row.get(j) == null) {
sr.createCell(j).setCellValue("");
} else {
Object value = row.get(j);
if (value instanceof Integer) {
HSSFCell cell = sr.createCell(j);
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(Double.valueOf(value.toString()));
} else if (value instanceof BigDecimal) {
HSSFCell cell = sr.createCell(j);
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(((BigDecimal)value).setScale(CommonPlatformConstant.INT_3, RoundingMode.HALF_UP).doubleValue());
} else {
sr.createCell(j).setCellValue(value.toString());
}
}
}
}
for (int i = 0; i < head.size(); i++) {
sheet.autoSizeColumn(i);
}
}
return book;
}
/**
* 设置Excel浮点数可做金额等数据统计
* @param cell 单元格类
* @param value 传入的值
*/
public static void setExcelValue(Cell cell, Object value, CellStyle style){
cell.setCellStyle(style);
// 写数据
if (value == null) {
cell.setCellValue("");
}else {
if (value instanceof Integer || value instanceof Long) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(Long.valueOf(value.toString()));
} else if (value instanceof BigDecimal) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(((BigDecimal)value).setScale(CommonPlatformConstant.INT_3, RoundingMode.HALF_UP).doubleValue());
} else {
cell.setCellValue(value.toString());
}
}
}
/**
* 设置Excel表体数据
* @param rowList 表数据 ： index 为0 为表头
* @param hasHead 是否包含表头
*/
public static void createBodyData(List<List<Object>> rowList, SXSSFWorkbook book, Sheet sheet, XSSFCellStyle style, Drawing patriarch, Boolean hasHead) throws IOException{
int size = rowList.get(0).size();
int startNum = 1;
if(hasHead) {
startNum = 0;
}
for (int i = startNum; i < rowList.size(); i++) {
List<Object> row = rowList.get(i);
if (row == null || row.isEmpty()) {
book.close();
throw new NullPointerException("the "+(i+1)+"th row is null");
}
if (size != row.size()) {
book.close();
throw new IllegalArgumentException("the cell number of "+(i+1)+"th row is different form the first");
}
Row sr = sheet.createRow(i);
for (int j = 0; j < row.size(); j++) {
if (patriarch != null && row.get(j) != null && row.get(j) instanceof URL) {
URL url = (URL)row.get(j);
sr.setHeight((short)(IMG_HEIGTH * IMG_WIDTH));
drawPictureIntoExcel(book, patriarch, i, j, url);
} else {
setExcelValue(sr.createCell(j), row.get(j), style);
}
}
}
}
/**
* Excel单行设置值一般用于合并单元格
* @param row 行对象
*/
public static void writeSingleRowExcel(Row row, List<Object> rowList, CellStyle style){
if (row == null) {
throw new NullPointerException("the HSSFRow is null");
}
if (PlatformUtils.isEmpty(rowList)) {
return;
}
// 设置行数据
for (int i = 0; i < rowList.size(); i++) {
setExcelValue(row.createCell(i), rowList.get(i), style);
}
}
/**
* 将Excel转化为输入流
* @param book Excel对象
* @return 输入流
* @throws IOException
*/
public static InputStream sxssfWorkbookToInputStream(SXSSFWorkbook book) throws IOException {
ByteArrayInputStream bais = null;
ByteArrayOutputStream baos = new ByteArrayOutputStream();
book.write(baos);
bais = new ByteArrayInputStream(baos.toByteArray());
baos.close();
book.close();
return bais;
}
/**
* 上传临时文件
* @param url
* @param fileName 文件名
* @param is 文件输入流
* @return 文件返回路径
* @throws Exception
*/
public static String uploadTempFile(String url, String fileName, InputStream is) throws Exception {
HttpSocket socket = new HttpSocket();
socket.setUrl(url);
// 为了能使用到华为存储的自动删除功能， 将临时文件全部放到comId=1的目录下
socket.addParameter(ConstBusiness.COM_ID, "1");
socket.addParameter("savePath", ConfigManager.getInstance().getValue(PlatformConstant.class, PlatformConstant.PICTURE_SERVER_PATH));
socket.addAttachment(fileName, fileName, "text/plain", is);
socket.doPostMultipart();
is.close();
JSONObject result = JSONObject.fromObject(socket.getResponseData());
if (result.getInt("code") > 0) {
throw new RuntimeException(result.getString("msg"));
}
return result.getString("msg");
}
/**
* Excel导出设置Workbook（包含下载图片）
* @param title 导出Excel文件名称
* @param rowList 第一个List为表头，其余行为表数据
* @param downLoadPic 是否下载图片 （如果要下载图片，图片的信息放Excel维度的第一列）
* @throws IOException
*/
public static HSSFWorkbook wrapSingleWorkbook(String title,List<List<Object>> rowList, Boolean downLoadPic) throws IOException {
String filename = title;
if (!PlatformUtils.hasText(title)) {
filename = new SimpleDateFormat("yyMMddHHmmss").format(new Date());
}
if (rowList == null || rowList.isEmpty()) {
throw new NullPointerException("the row list is null");
}
HSSFWorkbook book = new HSSFWorkbook();
// 创建表
HSSFSheet sheet = book.createSheet(filename);
// 设置单元格默认宽度为15个字符
sheet.setDefaultColumnWidth(15);
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
// 设置表头样式
HSSFCellStyle style = book.createCellStyle();
// 设置居左
style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
// 这种自动换行
style.setWrapText(true);
// 检测表头数据（表头不允许数据为空）
List<Object> head = rowList.get(0);
for (Object key : head) {
if (!PlatformUtils.hasText(key.toString())) {
book.close();
throw new NullPointerException("there is a blank exist head row");
}
}
// 写数据
int size = rowList.get(0).size();
for (int i = 0; i < rowList.size(); i++) {
List<Object> row = rowList.get(i);
if (row == null || row.isEmpty()) {
book.close();
throw new NullPointerException("the "+(i+1)+"th row is null");
}
if (size != row.size()) {
book.close();
throw new IllegalArgumentException("the cell number of "+(i+1)+"th row is different form the first");
}
HSSFRow sr = sheet.createRow(i);
for (int j = 0; j < row.size(); j++) {
// 在每行的第一个单元格插入图片
if (downLoadPic && i > 0 && j == 0) {
sr.setHeight((short) (800));
drawPictureIntoExcel(book, patriarch, i, row.get(0).toString());
} else {
HSSFCell cell = sr.createCell(j);
setExcelValue(cell, row.get(j), style);
}
}
}
return book;
}
/**将图片写入excel
* @param wb
* @param patriarch
* @param rowIndex 当前行数
* @param pictureUrl 图片链接
*
*/
private static void drawPictureIntoExcel(HSSFWorkbook wb, HSSFPatriarch patriarch, int rowIndex, String pictureUrl) {
try {
if (PlatformUtils.hasText(pictureUrl)) {
URL url = new URL(pictureUrl);
// 打开链接
HttpURLConnection conn = (HttpURLConnection) url.openConnection();
// 设置请求方式为"GET"
conn.setRequestMethod("GET");
// 超时响应时间为5秒
conn.setConnectTimeout(5 * 1000);
// 通过输入流获取图片数据
InputStream inStream = conn.getInputStream();
// 得到图片的二进制数据，以二进制封装得到数据
byte[] data = readInputStream(inStream);
// anchor主要用于设置图片的位置
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 0, rowIndex, (short) 1, rowIndex+1);
// Sets the anchor type （图片在单元格的位置）
// 0 = Move and size with Cells, 2 = Move but don't size with
// cells, 3 = Don't move or size with cells.
anchor.setAnchorType(3);
patriarch.createPicture(anchor, wb.addPicture(data, HSSFWorkbook.PICTURE_TYPE_JPEG));
}
} catch (IOException e) {
LOGGER.error(e.getMessage(), e);
} catch (Exception e) {
LOGGER.error(e.getMessage(), e);
}
}
private static byte[] readInputStream(InputStream inStream) throws Exception {
ByteArrayOutputStream outStream = new ByteArrayOutputStream();
// 创建一个Buffer字符串
byte[] buffer = new byte[1024];
// 每次读取的字符串长度，如果为-1，代表全部读取完毕
int len = 0;
// 使用一个输入流从buffer里把数据读取出来
while ((len = inStream.read(buffer)) != -1) {
// 用输出流往buffer里写入数据，中间参数代表从哪个位置开始读，len代表读取的长度
outStream.write(buffer, 0, len);
}
// 关闭输入流
inStream.close();
// 把outStream里的数据写入内存
return outStream.toByteArray();
}
}
