package com.hx.util;
|
|
import com.hx.exception.TipsException;
|
import com.monitorjbl.xlsx.StreamingReader;
|
import org.apache.poi.hssf.usermodel.*;
|
import org.apache.poi.openxml4j.exceptions.OLE2NotOfficeXmlFileException;
|
import org.apache.poi.ss.usermodel.*;
|
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
|
import org.apache.poi.xssf.usermodel.XSSFCell;
|
import org.apache.poi.xssf.usermodel.XSSFRow;
|
import org.apache.poi.xssf.usermodel.XSSFSheet;
|
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
|
import org.springframework.web.multipart.MultipartFile;
|
|
import javax.imageio.ImageIO;
|
import java.awt.image.BufferedImage;
|
import java.io.*;
|
import java.text.DateFormat;
|
import java.text.SimpleDateFormat;
|
import java.util.ArrayList;
|
import java.util.Date;
|
import java.util.List;
|
import java.util.Map;
|
|
|
/**
|
*
|
* @author hjr
|
*/
|
public final class ExcelUtil {
|
|
/**
|
* @param excelName
|
* 文件名称
|
* @param outPath
|
* 保存路径
|
* @param headList
|
* Excel文件Head标题集合
|
* @param fieldList
|
* Excel文件Field标题集合 根据field来寻找位置填充表格
|
* @param dataList
|
* Excel文件数据内容部分
|
* @throws Exception
|
*/
|
public static String createExcel(String outPath, String excelName,
|
String[] headList, String[] fieldList,
|
List<Map<String, Object>> dataList) throws Exception {
|
|
String filePath = null;
|
// 创建新的Excel 工作簿
|
HSSFWorkbook workbook = new HSSFWorkbook();
|
|
// 在Excel工作簿中建一工作表，其名为缺省值
|
// 如要新建一名为"效益指标"的工作表，其语句为：
|
// HSSFSheet sheet = workbook.createSheet("效益指标");
|
HSSFSheet sheet = workbook.createSheet();
|
// 在索引0的位置创建行（最顶端的行）
|
HSSFRow row = sheet.createRow(0);
|
// ===============================================================
|
|
HSSFCell cell;
|
for (int i = 0; i < headList.length; i++) {
|
// 在索引0的位置创建单元格（左上端）
|
cell = row.createCell(i);
|
// 定义单元格为字符串类型
|
//cell.setCellType(HSSFCell.CELL_TYPE_STRING);
|
// 在单元格中输入一些内容
|
cell.setCellValue(headList[i]);
|
}
|
// ===============================================================
|
if (dataList != null) {
|
HSSFRow row_value;
|
Map<String, Object> dataMap;
|
for (int n = 0; n < dataList.size(); n++) {
|
// 在索引1的位置创建行
|
row_value = sheet.createRow(n + 1);
|
dataMap = dataList.get(n);
|
// ===============================================================
|
for (int i = 0; i < fieldList.length; i++) {
|
// 在索引0的位置创建单元格（左上端）
|
cell = row_value.createCell(i);
|
// 定义单元格为字符串类型
|
//cell.setCellType(HSSFCell.CELL_TYPE_STRING);
|
// 在单元格中输入一些内容
|
cell.setCellValue(objToString(dataMap.get(fieldList[i])));
|
}
|
// ===============================================================
|
}
|
}
|
|
// 新建一输出文件流
|
File file = SimpleTool.createFile(outPath, excelName);
|
FileOutputStream fOut = new FileOutputStream(file);
|
// 把相应的Excel 工作簿存盘
|
workbook.write(fOut);
|
fOut.flush();
|
// 操作结束，关闭文件
|
fOut.close();
|
|
if(outPath.endsWith("/")){
|
filePath = outPath + excelName;
|
}else{
|
filePath = outPath +"/"+ excelName;
|
}
|
return filePath;
|
}
|
|
/**生成临时文件
|
* @param headList
|
* Excel文件Head标题集合
|
* @param fieldList
|
* Excel文件Field标题集合 根据field来寻找位置填充表格
|
* @param dataList
|
* Excel文件数据内容部分
|
* @throws Exception
|
*/
|
public static File createExcel(String[] headList, String[] fieldList,
|
List<Map<String, Object>> dataList) throws Exception {
|
File file = File.createTempFile("temp", ".xlsx");
|
try{
|
// 创建新的Excel 工作簿
|
HSSFWorkbook workbook = new HSSFWorkbook();
|
|
// 在Excel工作簿中建一工作表，其名为缺省值
|
// 如要新建一名为"效益指标"的工作表，其语句为：
|
// HSSFSheet sheet = workbook.createSheet("效益指标");
|
HSSFSheet sheet = workbook.createSheet();
|
// 在索引0的位置创建行（最顶端的行）
|
HSSFRow row = sheet.createRow(0);
|
// ===============================================================
|
HSSFCell cell;
|
for (int i = 0; i < headList.length; i++) {
|
// 在索引0的位置创建单元格（左上端）
|
cell = row.createCell(i);
|
// 定义单元格为字符串类型
|
//cell.setCellType(HSSFCell.CELL_TYPE_STRING);
|
// 在单元格中输入一些内容
|
cell.setCellValue(headList[i]);
|
}
|
// ===============================================================
|
if (dataList != null) {
|
HSSFRow row_value;
|
Map<String, Object> dataMap;
|
for (int n = 0; n < dataList.size(); n++) {
|
// 在索引1的位置创建行
|
row_value = sheet.createRow(n + 1);
|
dataMap = dataList.get(n);
|
// ===============================================================
|
for (int i = 0; i < fieldList.length; i++) {
|
// 在索引0的位置创建单元格（左上端）
|
cell = row_value.createCell(i);
|
// 定义单元格为字符串类型
|
//cell.setCellType(HSSFCell.CELL_TYPE_STRING);
|
// 在单元格中输入一些内容
|
cell.setCellValue(objToString(dataMap.get(fieldList[i])));
|
}
|
// ===============================================================
|
}
|
}
|
|
// 新建一输出文件流
|
FileOutputStream fOut = new FileOutputStream(file);
|
// 把相应的Excel 工作簿存盘
|
workbook.write(fOut);
|
fOut.flush();
|
// 操作结束，关闭文件
|
fOut.close();
|
}catch (Exception e){
|
|
}finally {
|
file.deleteOnExit();
|
}
|
return file;
|
}
|
|
/**生成临时文件
|
* @param headList
|
* Excel文件Head标题集合
|
* @param fieldList
|
* Excel文件Field标题集合 根据field来寻找位置填充表格
|
* @param dataList
|
* Excel文件数据内容部分
|
* @throws Exception
|
*/
|
public static File createExcel(String[] headList, String[] fieldList, List<Map<String, Object>> dataList
|
,Integer height,Integer width) throws Exception {
|
File file = File.createTempFile("temp", ".xls");
|
try{
|
|
if(height == null){
|
height = 450;
|
}
|
if(width == null){
|
width = 5000;
|
}
|
// 创建新的Excel 工作簿
|
HSSFWorkbook workbook = new HSSFWorkbook();
|
|
//合并的单元格样式
|
HSSFCellStyle boderStyle = workbook.createCellStyle();
|
//垂直居中
|
boderStyle.setVerticalAlignment(VerticalAlignment.CENTER);
|
boderStyle.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
|
|
// 在Excel工作簿中建一工作表，其名为缺省值
|
// 如要新建一名为"效益指标"的工作表，其语句为：
|
// HSSFSheet sheet = workbook.createSheet("效益指标");
|
HSSFSheet sheet = workbook.createSheet();
|
// 在索引0的位置创建行（最顶端的行）
|
HSSFRow row = sheet.createRow(0);
|
// ===============================================================
|
HSSFCell cell;
|
for (int i = 0; i < headList.length; i++) {
|
//高度
|
row.setHeight(height.shortValue());
|
sheet.setColumnWidth(i,width);
|
// 在索引0的位置创建单元格（左上端）
|
cell = row.createCell(i);
|
// 定义单元格为字符串类型
|
//cell.setCellType(HSSFCell.CELL_TYPE_STRING);
|
// 在单元格中输入一些内容
|
cell.setCellValue(headList[i]);
|
cell.setCellStyle(boderStyle);
|
}
|
// ===============================================================
|
if (dataList != null) {
|
HSSFRow row_value;
|
Map<String, Object> dataMap;
|
for (int n = 0; n < dataList.size(); n++) {
|
// 在索引1的位置创建行
|
row_value = sheet.createRow(n + 1);
|
row_value.setHeight(height.shortValue());
|
dataMap = dataList.get(n);
|
// ===============================================================
|
for (int i = 0; i < fieldList.length; i++) {
|
// 在索引0的位置创建单元格（左上端）
|
sheet.setColumnWidth(i,width);
|
cell = row_value.createCell(i);
|
// 定义单元格为字符串类型
|
//cell.setCellType(HSSFCell.CELL_TYPE_STRING);
|
// 在单元格中输入一些内容
|
cell.setCellValue(objToString(dataMap.get(fieldList[i])));
|
cell.setCellStyle(boderStyle);
|
}
|
// ===============================================================
|
}
|
}
|
|
// 新建一输出文件流
|
FileOutputStream fOut = new FileOutputStream(file);
|
// 把相应的Excel 工作簿存盘
|
workbook.write(fOut);
|
fOut.flush();
|
// 操作结束，关闭文件
|
fOut.close();
|
}catch (Exception e){
|
|
}finally {
|
file.deleteOnExit();
|
}
|
return file;
|
}
|
|
/**无限制行数生成ecxel，生成临时文件
|
* @param headList
|
* Excel文件Head标题集合
|
* @param fieldList
|
* Excel文件Field标题集合 根据field来寻找位置填充表格
|
* @param dataList
|
* Excel文件数据内容部分
|
* @param height 单元格高度，默认450
|
* @param width 单元格宽度，默认5000
|
* @throws Exception
|
*/
|
public static File createXSSExcel(String[] headList, String[] fieldList, List<Map<String, Object>> dataList
|
,Integer height,Integer width) throws Exception {
|
File file = File.createTempFile("temp", ".xls");
|
try{
|
|
if(height == null){
|
height = 450;
|
}
|
if(width == null){
|
width = 5000;
|
}
|
// 创建新的Excel 工作簿
|
//XSSFWorkbook workbook = new XSSFWorkbook();
|
SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
|
|
//合并的单元格样式
|
CellStyle boderStyle = workbook.createCellStyle();
|
//垂直居中
|
boderStyle.setVerticalAlignment(VerticalAlignment.CENTER);
|
boderStyle.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
|
|
// 在Excel工作簿中建一工作表，其名为缺省值
|
// 如要新建一名为"效益指标"的工作表，其语句为：
|
// HSSFSheet sheet = workbook.createSheet("效益指标");
|
Sheet sheet = workbook.createSheet();
|
// 在索引0的位置创建行（最顶端的行）
|
Row row = sheet.createRow(0);
|
// ===============================================================
|
Cell cell;
|
for (int i = 0; i < headList.length; i++) {
|
//高度
|
row.setHeight(height.shortValue());
|
sheet.setColumnWidth(i,width);
|
// 在索引0的位置创建单元格（左上端）
|
cell = row.createCell(i);
|
// 定义单元格为字符串类型
|
//cell.setCellType(HSSFCell.CELL_TYPE_STRING);
|
// 在单元格中输入一些内容
|
cell.setCellValue(headList[i]);
|
cell.setCellStyle(boderStyle);
|
}
|
// ===============================================================
|
if (dataList != null) {
|
Row row_value;
|
Map<String, Object> dataMap;
|
for (int n = 0; n < dataList.size(); n++) {
|
// 在索引1的位置创建行
|
row_value = sheet.createRow(n + 1);
|
row_value.setHeight(height.shortValue());
|
dataMap = dataList.get(n);
|
// ===============================================================
|
for (int i = 0; i < fieldList.length; i++) {
|
// 在索引0的位置创建单元格（左上端）
|
sheet.setColumnWidth(i,width);
|
cell = row_value.createCell(i);
|
// 定义单元格为字符串类型
|
//cell.setCellType(HSSFCell.CELL_TYPE_STRING);
|
// 在单元格中输入一些内容
|
cell.setCellValue(objToString(dataMap.get(fieldList[i])));
|
cell.setCellStyle(boderStyle);
|
}
|
// ===============================================================
|
}
|
}
|
|
// 新建一输出文件流
|
FileOutputStream fOut = new FileOutputStream(file);
|
// 把相应的Excel 工作簿存盘
|
workbook.write(fOut);
|
fOut.flush();
|
// 操作结束，关闭文件
|
fOut.close();
|
}catch (Exception e){
|
|
}finally {
|
file.deleteOnExit();
|
}
|
return file;
|
}
|
|
|
private static String objToString(Object obj) {
|
if (obj == null) {
|
return "";
|
} else {
|
if (obj instanceof String) {
|
return (String) obj;
|
} else if (obj instanceof Date) {
|
return null;// DateUtil.dateToString((Date)
|
// obj,DateUtil.DATESTYLE_SHORT_EX);
|
} else {
|
return obj.toString();
|
}
|
}
|
}
|
|
/**
|
* 读取Excel数据
|
* @param file
|
* @param header
|
* @return
|
* @throws Exception
|
*/
|
public static List<List<String>> readExcelData(MultipartFile file, boolean header) throws Exception {
|
|
String fileName = file.getOriginalFilename();
|
if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
|
throw new TipsException("上传文件格式不正确");
|
}
|
|
//判断不同格式处理方法不同
|
if(fileName.matches("^.+\\.(?i)(xls)$")){
|
//xls格式使用HSSF
|
return readExcelByeFileData(file, header);
|
}else{
|
//xlsx格式使用XSSF
|
return readExcelByeFileDataToXSSF(file, header);
|
}
|
}
|
|
/**
|
* 读取 Excel文件内容
|
*
|
* @param file
|
* @param header 是否包括表头
|
* @return
|
* @throws Exception
|
*/
|
public static List<List<String>> readExcelByeFileData(MultipartFile file, boolean header) throws Exception {
|
|
String fileName = file.getOriginalFilename();
|
if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
|
throw new TipsException("上传文件格式不正确");
|
}
|
|
// 结果集
|
List<List<String>> list = new ArrayList<>();
|
|
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file.getInputStream());
|
|
// 遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
|
for(int s=0;s<hssfworkbook.getNumberOfSheets();s++) {
|
HSSFSheet hssfsheet = hssfworkbook.getSheetAt(s);
|
int col = 0;
|
// 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数 去除标题
|
for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
|
HSSFRow hssfrow = hssfsheet.getRow(j);
|
if(hssfrow!=null){
|
if(j == 0) {
|
col = hssfrow.getPhysicalNumberOfCells();
|
if(!header) {
|
//不包括表头
|
continue;
|
}
|
}
|
// 单行数据
|
List<String> arrayString = new ArrayList<>();
|
for (int i = 0; i < col; i++) {
|
HSSFCell cell = hssfrow.getCell(i);
|
if (cell == null) {
|
arrayString.add("");
|
} else if (cell.getCellType() == CellType.NUMERIC) {
|
// arrayString[i] = new Double(cell.getNumericCellValue()).toString();
|
if (CellType.NUMERIC == cell.getCellType()) {
|
short format = cell.getCellStyle().getDataFormat();
|
if(format == 14 || format == 31 || format == 57 || format == 58){
|
//日期（中文时间格式的）
|
Date d = cell.getDateCellValue();
|
DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
|
// DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
|
arrayString.add(formater.format(d));
|
//arrayString[i] = formater.format(d);
|
}else if (HSSFDateUtil.isCellDateFormatted(cell)) {
|
Date d = cell.getDateCellValue();
|
//DateFormat formater = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒");
|
DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
|
arrayString.add(formater.format(d));
|
//arrayString[i] = formater.format(d);
|
} else {
|
if(CellType.STRING == cell.getCellType()){
|
arrayString.add(cell.getStringCellValue());
|
//arrayString[i] =cell.getStringCellValue();
|
}else if(CellType.FORMULA==cell.getCellType()){
|
arrayString.add(cell.getCellFormula());
|
//arrayString[i] =cell.getCellFormula();
|
}else if(CellType.NUMERIC== cell.getCellType()){
|
HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
|
arrayString.add(dataFormatter.formatCellValue(cell));
|
//arrayString[i] =dataFormatter.formatCellValue(cell);
|
}
|
}
|
}
|
} else if(cell.getCellType() == CellType.BLANK){
|
arrayString.add("");
|
//arrayString[i] = "";
|
} else { // 如果EXCEL表格中的数据类型为字符串型
|
if(cell.getCellType() != CellType.BOOLEAN){
|
arrayString.add(cell.getStringCellValue().trim());
|
}else{
|
arrayString.add(cell.getBooleanCellValue() ? "TRUE" : "FALSE");
|
}
|
//arrayString[i] = cell.getStringCellValue().trim();
|
}
|
}
|
list.add(arrayString);
|
}
|
}
|
}
|
return list;
|
}
|
|
/**
|
* 读取 Excel文件内容
|
*
|
* @param file
|
* @param header 是否包括表头
|
* @return
|
* @throws Exception
|
*/
|
public static List<List<String>> readExcelByeFileDataToXSSF(MultipartFile file, boolean header) throws Exception {
|
|
String fileName = file.getOriginalFilename();
|
if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
|
throw new TipsException("上传文件格式不正确");
|
}
|
|
// 结果集
|
List<List<String>> list = new ArrayList<>();
|
|
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file.getInputStream());
|
|
// 遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
|
for(int s=0;s<xssfWorkbook.getNumberOfSheets();s++) {
|
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(s);
|
int col = 0;
|
// 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数 去除标题
|
for (int j = 0; j < xssfSheet.getPhysicalNumberOfRows(); j++) {
|
XSSFRow xssfrow = xssfSheet.getRow(j);
|
if(xssfrow!=null){
|
if(j == 0) {
|
col = xssfrow.getPhysicalNumberOfCells();
|
if(!header) {
|
//不包括表头
|
continue;
|
}
|
}
|
// 单行数据
|
List<String> arrayString = new ArrayList<>();
|
for (int i = 0; i < col; i++) {
|
XSSFCell cell = xssfrow.getCell(i);
|
if (cell == null) {
|
arrayString.add("");
|
} else if (cell.getCellType() == CellType.NUMERIC) {
|
// arrayString[i] = new Double(cell.getNumericCellValue()).toString();
|
if (CellType.NUMERIC == cell.getCellType()) {
|
short format = cell.getCellStyle().getDataFormat();
|
if(format == 14 || format == 31 || format == 57 || format == 58){
|
//日期（中文时间格式的）
|
Date d = cell.getDateCellValue();
|
DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
|
// DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
|
arrayString.add(formater.format(d));
|
//arrayString[i] = formater.format(d);
|
}else if (HSSFDateUtil.isCellDateFormatted(cell)) {
|
Date d = cell.getDateCellValue();
|
//DateFormat formater = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒");
|
DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
|
arrayString.add(formater.format(d));
|
//arrayString[i] = formater.format(d);
|
} else {
|
if(CellType.STRING == cell.getCellType()){
|
arrayString.add(cell.getStringCellValue());
|
//arrayString[i] =cell.getStringCellValue();
|
}else if(CellType.FORMULA==cell.getCellType()){
|
arrayString.add(cell.getCellFormula());
|
//arrayString[i] =cell.getCellFormula();
|
}else if(CellType.NUMERIC== cell.getCellType()){
|
HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
|
arrayString.add(dataFormatter.formatCellValue(cell));
|
//arrayString[i] =dataFormatter.formatCellValue(cell);
|
}
|
}
|
}
|
} else if(cell.getCellType() == CellType.BLANK){
|
arrayString.add("");
|
//arrayString[i] = "";
|
} else { // 如果EXCEL表格中的数据类型为字符串型
|
if(cell.getCellType() != CellType.BOOLEAN){
|
arrayString.add(cell.getStringCellValue().trim());
|
}else{
|
arrayString.add(cell.getBooleanCellValue() ? "TRUE" : "FALSE");
|
}
|
|
|
//arrayString[i] = cell.getStringCellValue().trim();
|
}
|
}
|
list.add(arrayString);
|
}
|
}
|
}
|
return list;
|
}
|
|
|
/**判断excel的版本*/
|
public static Workbook create(InputStream inp) throws IOException {
|
//这样写 excel 能兼容03和07
|
return WorkbookFactory.create(inp);
|
}
|
|
/**读取excel文件，兼容2003和2007
|
* 通过流读取Excel文件
|
* @return
|
* @throws Exception
|
*/
|
public static List<List<String>> getExcelDataCompatible(File file,boolean header) throws Exception {
|
try {
|
|
String fileName = file.getName();
|
if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
|
throw new TipsException("上传文件格式不正确");
|
}
|
|
// 结果集
|
List<List<String>> list = new ArrayList<>();
|
Workbook book = create(new BufferedInputStream(new FileInputStream(file)));
|
|
// 遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
|
Sheet hssfsheet;
|
Row hssfrow;
|
List<String> arrayString;
|
Cell cell;
|
short format;
|
Date d;
|
DateFormat formater;
|
HSSFDataFormatter dataFormatter;
|
for(int s=0;s<book.getNumberOfSheets();s++) {
|
hssfsheet = book.getSheetAt(s);
|
int col = 0;
|
// 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数 去除标题
|
for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
|
hssfrow = hssfsheet.getRow(j);
|
if(hssfrow!=null){
|
if(j == 0) {
|
col = hssfrow.getPhysicalNumberOfCells();
|
if(!header) {
|
//不包括表头
|
continue;
|
}
|
}
|
// 单行数据
|
arrayString = new ArrayList<>();
|
for (int i = 0; i < col; i++) {
|
cell = hssfrow.getCell(i);
|
if (cell == null) {
|
arrayString.add("");
|
} else if (cell.getCellType() == CellType.NUMERIC) {
|
// arrayString[i] = new Double(cell.getNumericCellValue()).toString();
|
if (CellType.NUMERIC == cell.getCellType()) {
|
format = cell.getCellStyle().getDataFormat();
|
if(format == 14 || format == 31 || format == 57 || format == 58){
|
//日期（中文时间格式的）
|
d = cell.getDateCellValue();
|
formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
|
// DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
|
arrayString.add(formater.format(d));
|
//arrayString[i] = formater.format(d);
|
}else if (HSSFDateUtil.isCellDateFormatted(cell)) {
|
d = cell.getDateCellValue();
|
//DateFormat formater = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒");
|
formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
|
arrayString.add(formater.format(d));
|
//arrayString[i] = formater.format(d);
|
} else {
|
if(CellType.STRING == cell.getCellType()){
|
arrayString.add(cell.getStringCellValue());
|
//arrayString[i] =cell.getStringCellValue();
|
}else if(CellType.FORMULA==cell.getCellType()){
|
arrayString.add(cell.getCellFormula());
|
//arrayString[i] =cell.getCellFormula();
|
}else if(CellType.NUMERIC== cell.getCellType()){
|
dataFormatter = new HSSFDataFormatter();
|
arrayString.add(dataFormatter.formatCellValue(cell));
|
//arrayString[i] =dataFormatter.formatCellValue(cell);
|
}
|
}
|
}
|
} else if(cell.getCellType() == CellType.BLANK){
|
arrayString.add("");
|
//arrayString[i] = "";
|
} else { // 如果EXCEL表格中的数据类型为字符串型
|
arrayString.add(cell.getStringCellValue().trim());
|
//arrayString[i] = cell.getStringCellValue().trim();
|
}
|
}
|
list.add(arrayString);
|
}
|
}
|
}
|
return list;
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
return null;
|
}
|
|
/**读取excel文件，兼容2003和2007
|
* 通过流读取Excel文件
|
* @return
|
* @throws Exception
|
*/
|
public static List<List<String>> getExcelDataCompatible(MultipartFile file,boolean header) throws Exception {
|
try {
|
|
String fileName = file.getOriginalFilename();
|
if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
|
throw new TipsException("上传文件格式不正确");
|
}
|
|
// 结果集
|
List<List<String>> list = new ArrayList<>();
|
Workbook book = create(new BufferedInputStream(file.getInputStream()));
|
|
Sheet hssfsheet;
|
Row hssfrow;
|
List<String> arrayString;
|
Cell cell;
|
short format;
|
Date d;
|
DateFormat formater;
|
HSSFDataFormatter dataFormatter;
|
// 遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
|
for(int s=0;s<book.getNumberOfSheets();s++) {
|
hssfsheet = book.getSheetAt(s);
|
int col = 0;
|
// 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数 去除标题
|
for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
|
hssfrow = hssfsheet.getRow(j);
|
if(hssfrow!=null){
|
if(j == 0) {
|
col = hssfrow.getPhysicalNumberOfCells();
|
if(!header) {
|
//不包括表头
|
continue;
|
}
|
}
|
// 单行数据
|
arrayString = new ArrayList<>();
|
for (int i = 0; i < col; i++) {
|
cell = hssfrow.getCell(i);
|
if (cell == null) {
|
arrayString.add("");
|
} else if (cell.getCellType() == CellType.NUMERIC) {
|
// arrayString[i] = new Double(cell.getNumericCellValue()).toString();
|
if (CellType.NUMERIC == cell.getCellType()) {
|
format = cell.getCellStyle().getDataFormat();
|
if(format == 14 || format == 31 || format == 57 || format == 58){
|
//日期（中文时间格式的）
|
d = cell.getDateCellValue();
|
formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
|
// DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
|
arrayString.add(formater.format(d));
|
//arrayString[i] = formater.format(d);
|
}else if (HSSFDateUtil.isCellDateFormatted(cell)) {
|
d = cell.getDateCellValue();
|
//DateFormat formater = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒");
|
formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
|
arrayString.add(formater.format(d));
|
//arrayString[i] = formater.format(d);
|
} else {
|
if(CellType.STRING == cell.getCellType()){
|
arrayString.add(cell.getStringCellValue());
|
//arrayString[i] =cell.getStringCellValue();
|
}else if(CellType.FORMULA==cell.getCellType()){
|
arrayString.add(cell.getCellFormula());
|
//arrayString[i] =cell.getCellFormula();
|
}else if(CellType.NUMERIC== cell.getCellType()){
|
dataFormatter = new HSSFDataFormatter();
|
arrayString.add(dataFormatter.formatCellValue(cell));
|
//arrayString[i] =dataFormatter.formatCellValue(cell);
|
}
|
}
|
}
|
} else if(cell.getCellType() == CellType.BLANK){
|
arrayString.add("");
|
//arrayString[i] = "";
|
} else { // 如果EXCEL表格中的数据类型为字符串型
|
arrayString.add(cell.getStringCellValue().trim());
|
//arrayString[i] = cell.getStringCellValue().trim();
|
}
|
}
|
list.add(arrayString);
|
}
|
}
|
}
|
return list;
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
return null;
|
}
|
|
/**读取excel文件，兼容2003和2007
|
* 通过流读取Excel文件
|
* @return
|
* @throws Exception
|
*/
|
public static List<List<String>> getExcelDataCompatible(InputStream inputStream,boolean header) throws Exception {
|
try {
|
// 结果集
|
List<List<String>> list = new ArrayList<>();
|
Workbook book = create(new BufferedInputStream(inputStream));
|
|
Sheet hssfsheet;
|
Row hssfrow;
|
List<String> arrayString;
|
Cell cell;
|
short format;
|
Date d;
|
DateFormat formater;
|
HSSFDataFormatter dataFormatter;
|
// 遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
|
for(int s=0;s<book.getNumberOfSheets();s++) {
|
hssfsheet = book.getSheetAt(s);
|
int col = 0;
|
// 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数 去除标题
|
for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
|
hssfrow = hssfsheet.getRow(j);
|
if(hssfrow!=null){
|
if(j == 0) {
|
col = hssfrow.getPhysicalNumberOfCells();
|
if(!header) {
|
//不包括表头
|
continue;
|
}
|
}
|
// 单行数据
|
arrayString = new ArrayList<>();
|
for (int i = 0; i < col; i++) {
|
cell = hssfrow.getCell(i);
|
if (cell == null) {
|
arrayString.add("");
|
} else if (cell.getCellType() == CellType.NUMERIC) {
|
// arrayString[i] = new Double(cell.getNumericCellValue()).toString();
|
if (CellType.NUMERIC == cell.getCellType()) {
|
format = cell.getCellStyle().getDataFormat();
|
if(format == 14 || format == 31 || format == 57 || format == 58){
|
//日期（中文时间格式的）
|
d = cell.getDateCellValue();
|
formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
|
// DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
|
arrayString.add(formater.format(d));
|
//arrayString[i] = formater.format(d);
|
}else if (HSSFDateUtil.isCellDateFormatted(cell)) {
|
d = cell.getDateCellValue();
|
//DateFormat formater = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒");
|
formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
|
arrayString.add(formater.format(d));
|
//arrayString[i] = formater.format(d);
|
} else {
|
if(CellType.STRING == cell.getCellType()){
|
arrayString.add(cell.getStringCellValue());
|
//arrayString[i] =cell.getStringCellValue();
|
}else if(CellType.FORMULA==cell.getCellType()){
|
arrayString.add(cell.getCellFormula());
|
//arrayString[i] =cell.getCellFormula();
|
}else if(CellType.NUMERIC== cell.getCellType()){
|
dataFormatter = new HSSFDataFormatter();
|
arrayString.add(dataFormatter.formatCellValue(cell));
|
//arrayString[i] =dataFormatter.formatCellValue(cell);
|
}
|
}
|
}
|
} else if(cell.getCellType() == CellType.BLANK){
|
arrayString.add("");
|
//arrayString[i] = "";
|
} else { // 如果EXCEL表格中的数据类型为字符串型
|
arrayString.add(cell.getStringCellValue().trim());
|
//arrayString[i] = cell.getStringCellValue().trim();
|
}
|
}
|
list.add(arrayString);
|
}
|
}
|
}
|
return list;
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
return null;
|
}
|
|
/**
|
* 新版读取Excel，只支持2007以上版本，也就是xslx格式
|
* 支持大数据量
|
* @param file 文件
|
* @return 数据
|
*/
|
public static List<List<String>> readExcelData(File file){
|
if(!isExcel(file)){
|
throw new TipsException("请上传excel的文件格式！");
|
}
|
List<List<String>> listData = new ArrayList<>();
|
try{
|
//rowCacheSize 缓存到内存中的行数(默认是10)
|
//bufferSize 读取资源时，缓存到内存的字节大小(默认是1024)
|
//open InputStream或者XLSX格式的File(必须)
|
Workbook book = StreamingReader.builder()
|
.rowCacheSize(100)
|
.bufferSize(10240)
|
.open(new FileInputStream(file));
|
listData =readhandle(book);
|
}catch (OLE2NotOfficeXmlFileException ex){
|
ex.printStackTrace();
|
throw new RuntimeException("excel版本不为2007及以上");
|
} catch (Exception e){
|
e.printStackTrace();
|
throw new RuntimeException(e.getMessage());
|
}
|
return listData;
|
}
|
|
/**
|
* 新版读取Excel，只支持2007以上版本，也就是xslx格式
|
* 支持大数据量
|
* @param file 文件
|
* @return 数据
|
*/
|
public static List<List<String>> readExcelData(MultipartFile file){
|
if(!isExcel(file)){
|
throw new TipsException("请上传excel的文件格式！");
|
}
|
List<List<String>> listData = new ArrayList<>();
|
try{
|
//rowCacheSize 缓存到内存中的行数(默认是10)
|
//bufferSize 读取资源时，缓存到内存的字节大小(默认是1024)
|
//open InputStream或者XLSX格式的File(必须)
|
Workbook book = StreamingReader.builder()
|
.rowCacheSize(100)
|
.bufferSize(10240)
|
.open(new BufferedInputStream(file.getInputStream()));
|
|
listData =readhandle(book);
|
}catch (OLE2NotOfficeXmlFileException ex){
|
ex.printStackTrace();
|
throw new RuntimeException("excel版本不为2007及以上");
|
} catch (Exception e){
|
e.printStackTrace();
|
throw new RuntimeException(e.getMessage());
|
}
|
return listData;
|
}
|
|
/**
|
* 新版读取Excel，只支持2007以上版本，也就是xslx格式
|
* 支持大数据量
|
* @param file 文件
|
* @return 数据
|
*/
|
public static List<List<String>> readExcelData(InputStream file){
|
List<List<String>> listData = new ArrayList<>();
|
try{
|
//rowCacheSize 缓存到内存中的行数(默认是10)
|
//bufferSize 读取资源时，缓存到内存的字节大小(默认是1024)
|
//open InputStream或者XLSX格式的File(必须)
|
Workbook book = StreamingReader.builder()
|
.rowCacheSize(100)
|
.bufferSize(10240)
|
.open(file);
|
|
listData =readhandle(book);
|
}catch (OLE2NotOfficeXmlFileException ex){
|
ex.printStackTrace();
|
throw new RuntimeException("excel版本不为2007及以上");
|
}catch (Exception e){
|
e.printStackTrace();
|
throw new RuntimeException(e.getMessage());
|
}
|
return listData;
|
}
|
|
/**处理数据*/
|
public static List<List<String>> readhandle(Workbook book){
|
List<List<String>> listData = new ArrayList<>();
|
|
//是否存在数据
|
boolean isData;
|
List<String> arrayString;
|
short format;
|
Date d;
|
DateFormat formater;
|
Sheet sheet;
|
Cell cell;
|
//遍历所有的sheet
|
for(int i=0;i<book.getNumberOfSheets();i++) {
|
sheet = book.getSheetAt(i);
|
//列数
|
Integer arrange = null;
|
//遍历所有的行
|
for (Row row : sheet) {
|
if(row == null){
|
continue;
|
}
|
isData = false;
|
arrayString = new ArrayList<>();
|
if(arrange == null){
|
arrange = row.getPhysicalNumberOfCells();
|
}
|
//遍历所有的列
|
for (int j = 0;j<arrange;j++) {
|
cell = row.getCell(j);
|
if (cell == null) {
|
arrayString.add("");
|
}else{
|
if (cell.getCellType() == CellType.NUMERIC) {
|
isData = true;
|
format = cell.getCellStyle().getDataFormat();
|
if (format == 14 || format == 31 || format == 57 || format == 58) {
|
//日期（中文时间格式的）
|
d = cell.getDateCellValue();
|
formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
|
arrayString.add(formater.format(d));
|
} else if (HSSFDateUtil.isCellDateFormatted(cell)) {
|
d = cell.getDateCellValue();
|
formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
|
arrayString.add(formater.format(d));
|
} else {
|
if (CellType.STRING == cell.getCellType()) {
|
arrayString.add(cell.getStringCellValue());
|
} else if (CellType.FORMULA == cell.getCellType()) {
|
arrayString.add(cell.getCellFormula());
|
} else {
|
arrayString.add(cell.getStringCellValue().trim());
|
}
|
}
|
} else if (cell.getCellType() == CellType.BLANK) {
|
arrayString.add("");
|
} else { // 如果EXCEL表格中的数据类型为字符串型
|
isData = true;
|
arrayString.add(cell.getStringCellValue().trim());
|
}
|
}
|
}
|
if (isData) {
|
listData.add(arrayString);
|
}
|
}
|
}
|
return listData;
|
}
|
|
|
/**
|
* p判断是否excel文件
|
* @param file
|
* @return
|
*/
|
public static boolean isExcel(MultipartFile file){
|
return isExcel(file.getOriginalFilename());
|
}
|
|
|
/**
|
* p判断是否excel文件
|
* @param file
|
* @return
|
*/
|
public static boolean isExcel(File file){
|
return isExcel(file.getName());
|
}
|
|
|
/**判断文件格式是不是excel*/
|
public static boolean isExcel(String fileName){
|
if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
|
return false;
|
}
|
return true;
|
}
|
|
public static File createExcelByImg(String[] headList, String[] fieldList, List<Map<String, Object>> dataList, Integer height, Integer width) throws Exception {
|
File file = File.createTempFile("temp", ".xls");
|
FileOutputStream fileOut = null;
|
BufferedImage bufferImg = null;
|
|
try {
|
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
|
if (height == null) {
|
height = 450;
|
}
|
|
if (width == null) {
|
width = 1000;
|
}
|
|
HSSFWorkbook workbook = new HSSFWorkbook();
|
HSSFCellStyle boderStyle = workbook.createCellStyle();
|
boderStyle.setVerticalAlignment(VerticalAlignment.CENTER);
|
boderStyle.setAlignment(HorizontalAlignment.CENTER);
|
HSSFSheet sheet = workbook.createSheet();
|
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
|
HSSFRow row = sheet.createRow(0);
|
|
HSSFCell anchor;
|
for(int i = 0; i < headList.length; ++i) {
|
row.setHeight(height.shortValue());
|
sheet.setColumnWidth(i, width);
|
anchor = row.createCell(i);
|
anchor.setCellValue(headList[i]);
|
anchor.setCellStyle(boderStyle);
|
}
|
|
HSSFRow row_value = null;
|
anchor = null;
|
HSSFCell cell = null;
|
if (dataList != null) {
|
for(int n = 0; n < dataList.size(); ++n) {
|
row_value = sheet.createRow(n + 1);
|
row_value.setHeight(height.shortValue());
|
Map<String, Object> dataMap = (Map)dataList.get(n);
|
|
for(int i = 0; i < fieldList.length; ++i) {
|
sheet.setColumnWidth(i, width);
|
cell = row_value.createCell(i);
|
Object value = dataMap.get(fieldList[i]);
|
if (value != null && "class java.io.File".equals(value.getClass().toString())) {
|
File file2 = (File)value;
|
if (file2 == null) {
|
cell.setCellValue("");
|
} else {
|
bufferImg = ImageIO.read(file2);
|
ImageIO.write(bufferImg, "jpg", byteArrayOut);
|
HSSFClientAnchor anchor1 = new HSSFClientAnchor(0, 0, 1023, 255, (short)i, n + 1, (short)i, n + 1);
|
anchor1.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
|
patriarch.createPicture(anchor1, workbook.addPicture(byteArrayOut.toByteArray(), 5));
|
}
|
} else {
|
cell.setCellValue(objToString(dataMap.get(fieldList[i])));
|
cell.setCellStyle(boderStyle);
|
}
|
}
|
}
|
}
|
|
FileOutputStream fOut = new FileOutputStream(file);
|
workbook.write(fOut);
|
fOut.flush();
|
fOut.close();
|
} catch (Exception var25) {
|
var25.printStackTrace();
|
} finally {
|
file.deleteOnExit();
|
}
|
|
return file;
|
}
|
|
}
|
