package com.hx.util;
|
|
import java.io.*;
|
import java.text.DateFormat;
|
import java.text.SimpleDateFormat;
|
import java.util.*;
|
|
import com.hx.exception.TipsException;
|
import org.apache.poi.POIXMLDocument;
|
import org.apache.poi.hssf.usermodel.*;
|
import org.apache.poi.openxml4j.opc.OPCPackage;
|
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
|
import org.apache.poi.ss.usermodel.Cell;
|
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.usermodel.XSSFWorkbook;
|
import org.springframework.web.multipart.MultipartFile;
|
|
|
/**
|
*
|
* @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);
|
// ===============================================================
|
for (int i = 0; i < headList.length; i++) {
|
|
// 在索引0的位置创建单元格（左上端）
|
HSSFCell cell = row.createCell(i);
|
// 定义单元格为字符串类型
|
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
|
// 在单元格中输入一些内容
|
cell.setCellValue(headList[i]);
|
}
|
// ===============================================================
|
if (dataList != null) {
|
for (int n = 0; n < dataList.size(); n++) {
|
// 在索引1的位置创建行
|
HSSFRow row_value = sheet.createRow(n + 1);
|
Map<String, Object> dataMap = dataList.get(n);
|
// ===============================================================
|
for (int i = 0; i < fieldList.length; i++) {
|
// 在索引0的位置创建单元格（左上端）
|
HSSFCell 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", ".xls");
|
try{
|
// 创建新的Excel 工作簿
|
HSSFWorkbook workbook = new HSSFWorkbook();
|
|
// 在Excel工作簿中建一工作表，其名为缺省值
|
// 如要新建一名为"效益指标"的工作表，其语句为：
|
// HSSFSheet sheet = workbook.createSheet("效益指标");
|
HSSFSheet sheet = workbook.createSheet();
|
// 在索引0的位置创建行（最顶端的行）
|
HSSFRow row = sheet.createRow(0);
|
// ===============================================================
|
for (int i = 0; i < headList.length; i++) {
|
|
// 在索引0的位置创建单元格（左上端）
|
HSSFCell cell = row.createCell(i);
|
// 定义单元格为字符串类型
|
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
|
// 在单元格中输入一些内容
|
cell.setCellValue(headList[i]);
|
}
|
// ===============================================================
|
if (dataList != null) {
|
for (int n = 0; n < dataList.size(); n++) {
|
// 在索引1的位置创建行
|
HSSFRow row_value = sheet.createRow(n + 1);
|
Map<String, Object> dataMap = dataList.get(n);
|
// ===============================================================
|
for (int i = 0; i < fieldList.length; i++) {
|
// 在索引0的位置创建单元格（左上端）
|
HSSFCell 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(HSSFCellStyle.VERTICAL_CENTER);
|
boderStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
|
|
// 在Excel工作簿中建一工作表，其名为缺省值
|
// 如要新建一名为"效益指标"的工作表，其语句为：
|
// HSSFSheet sheet = workbook.createSheet("效益指标");
|
HSSFSheet sheet = workbook.createSheet();
|
// 在索引0的位置创建行（最顶端的行）
|
HSSFRow row = sheet.createRow(0);
|
// ===============================================================
|
for (int i = 0; i < headList.length; i++) {
|
//高度
|
row.setHeight(height.shortValue());
|
sheet.setColumnWidth(i,width);
|
// 在索引0的位置创建单元格（左上端）
|
HSSFCell cell = row.createCell(i);
|
// 定义单元格为字符串类型
|
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
|
// 在单元格中输入一些内容
|
cell.setCellValue(headList[i]);
|
cell.setCellStyle(boderStyle);
|
}
|
// ===============================================================
|
if (dataList != null) {
|
for (int n = 0; n < dataList.size(); n++) {
|
// 在索引1的位置创建行
|
HSSFRow row_value = sheet.createRow(n + 1);
|
row_value.setHeight(height.shortValue());
|
Map<String, Object> dataMap = dataList.get(n);
|
// ===============================================================
|
for (int i = 0; i < fieldList.length; i++) {
|
// 在索引0的位置创建单元格（左上端）
|
sheet.setColumnWidth(i,width);
|
HSSFCell 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>> 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() == 0) {
|
// arrayString[i] = new Double(cell.getNumericCellValue()).toString();
|
if (HSSFCell.CELL_TYPE_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(HSSFCell.CELL_TYPE_STRING == cell.getCellType()){
|
arrayString.add(cell.getStringCellValue());
|
//arrayString[i] =cell.getStringCellValue();
|
}else if(HSSFCell.CELL_TYPE_FORMULA==cell.getCellType()){
|
arrayString.add(cell.getCellFormula());
|
//arrayString[i] =cell.getCellFormula();
|
}else if(HSSFCell.CELL_TYPE_NUMERIC== cell.getCellType()){
|
HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
|
arrayString.add(dataFormatter.formatCellValue(cell));
|
//arrayString[i] =dataFormatter.formatCellValue(cell);
|
}
|
}
|
}
|
} else if(cell.getCellType() == Cell.CELL_TYPE_BLANK){
|
arrayString.add("");
|
//arrayString[i] = "";
|
} else { // 如果EXCEL表格中的数据类型为字符串型
|
arrayString.add(cell.getStringCellValue().trim());
|
//arrayString[i] = cell.getStringCellValue().trim();
|
}
|
}
|
list.add(arrayString);
|
}
|
}
|
}
|
return list;
|
}
|
|
|
/**判断excel的版本*/
|
public static Workbook create(InputStream inp) throws Exception {
|
if (!inp.markSupported()) {
|
inp = new PushbackInputStream(inp, 8);
|
}
|
if (POIFSFileSystem.hasPOIFSHeader(inp)) {
|
return new HSSFWorkbook(inp);
|
}
|
if (POIXMLDocument.hasOOXMLHeader(inp)) {
|
return new XSSFWorkbook(OPCPackage.open(inp));
|
}
|
throw new IllegalArgumentException("你的excel版本目前poi解析不了");
|
}
|
|
/**读取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()));
|
|
// 遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
|
for(int s=0;s<book.getNumberOfSheets();s++) {
|
Sheet hssfsheet = book.getSheetAt(s);
|
int col = 0;
|
// 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数 去除标题
|
for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
|
Row 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++) {
|
Cell cell = hssfrow.getCell(i);
|
if (cell == null) {
|
arrayString.add("");
|
} else if (cell.getCellType() == 0) {
|
// arrayString[i] = new Double(cell.getNumericCellValue()).toString();
|
if (HSSFCell.CELL_TYPE_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(HSSFCell.CELL_TYPE_STRING == cell.getCellType()){
|
arrayString.add(cell.getStringCellValue());
|
//arrayString[i] =cell.getStringCellValue();
|
}else if(HSSFCell.CELL_TYPE_FORMULA==cell.getCellType()){
|
arrayString.add(cell.getCellFormula());
|
//arrayString[i] =cell.getCellFormula();
|
}else if(HSSFCell.CELL_TYPE_NUMERIC== cell.getCellType()){
|
HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
|
arrayString.add(dataFormatter.formatCellValue(cell));
|
//arrayString[i] =dataFormatter.formatCellValue(cell);
|
}
|
}
|
}
|
} else if(cell.getCellType() == Cell.CELL_TYPE_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;
|
}
|
|
private static String getCellVal(Cell cell) {
|
if (null == cell) {
|
return "";
|
}
|
switch (cell.getCellType()) {
|
// 数字
|
case HSSFCell.CELL_TYPE_NUMERIC:
|
// 日期格式的处理
|
if (HSSFDateUtil.isCellDateFormatted(cell)) {
|
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
|
return sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
|
}
|
return String.valueOf(cell.getNumericCellValue());
|
// 字符串
|
case HSSFCell.CELL_TYPE_STRING:
|
return cell.getStringCellValue();
|
// 公式
|
case HSSFCell.CELL_TYPE_FORMULA:
|
return cell.getCellFormula();
|
// 空白
|
case HSSFCell.CELL_TYPE_BLANK:
|
return "";
|
case HSSFCell.CELL_TYPE_BOOLEAN:
|
return cell.getBooleanCellValue() + "";
|
// 错误类型
|
case HSSFCell.CELL_TYPE_ERROR:
|
return cell.getErrorCellValue() + "";
|
default:
|
break;
|
}
|
return "";
|
}
|
|
}
|
