package com.stylefeng.guns.modular.system.util;
|
|
import org.apache.poi.hssf.usermodel.*;
|
import org.apache.poi.ss.usermodel.*;
|
import org.apache.poi.ss.usermodel.DateUtil;
|
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
|
import org.springframework.stereotype.Component;
|
import org.springframework.web.multipart.MultipartFile;
|
|
import java.io.IOException;
|
import java.io.InputStream;
|
import java.util.ArrayList;
|
import java.util.Date;
|
import java.util.List;
|
|
/**
|
* 定义Excel工具类
|
*/
|
@Component
|
public class ExcelUtil {
|
|
|
public List<List<List<String>>> upload(MultipartFile file){
|
InputStream inputStream = null;
|
try {
|
inputStream = file.getInputStream();
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
//获取文件名
|
String fileName=file.getOriginalFilename();
|
List<List<List<String>>> list = null;
|
if(validateExcel(fileName)) {
|
// 根据版本选择创建Workbook的方式
|
Workbook wb = null;
|
// 根据文件名判断文件是2003版本还是2007版本
|
if (isExcel2007(fileName)) {
|
try {
|
wb = new XSSFWorkbook(inputStream);
|
} catch (IOException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
} else {
|
try {
|
wb = new HSSFWorkbook(inputStream);
|
} catch (IOException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
}
|
int num = wb.getNumberOfSheets();//获取页数
|
list = new ArrayList<>();
|
for (int i = 0; i < num; i++) {
|
List<List<String>> sheetList = new ArrayList<>();
|
//获取每一页对象
|
Sheet sheet = wb.getSheetAt(i);
|
// 得到Excel的行数
|
int totalRows = sheet.getPhysicalNumberOfRows();
|
for (int j = 0; j < totalRows; j++) {
|
if (j == 0) {
|
continue;// 标题行
|
}
|
Row row = sheet.getRow(j);// 获取索引为i的行数据
|
if(null == row){
|
continue;
|
}
|
int index = sheet.getRow(0).getPhysicalNumberOfCells();//获取标题的列数用于遍历
|
List<String> strings = new ArrayList<>();
|
int in = 0;//用于遍历单元格判断该行是否全为空值
|
for (int k = 0; k < index; k++) {//遍历获取每个单元格的数据
|
String str = null;
|
Cell cell = row.getCell(k);
|
if(cell == null) {
|
str = "";
|
in++;
|
}else {
|
switch (cell.getCellType()) {//判断数据类型取值
|
case NUMERIC :
|
if (DateUtil.isCellDateFormatted(cell)) {
|
Date theDate = cell.getDateCellValue();
|
str = String.valueOf(theDate.getTime());
|
}else{
|
String string = String.valueOf(cell.getNumericCellValue());
|
str = string.substring(0, string.indexOf("."));
|
}
|
|
break;
|
case STRING :
|
str = cell.getStringCellValue();
|
if(str == null) {
|
str = "";
|
in++;
|
}
|
break;
|
case _NONE :
|
System.err.println("_NONE");
|
break;
|
case FORMULA :
|
System.err.println("FORMULA");
|
break;
|
case BLANK :
|
str = cell.getStringCellValue();
|
if(str.equals("")) {
|
str = "";
|
in++;
|
}
|
break;
|
case BOOLEAN :
|
System.err.println("BOOLEAN");
|
break;
|
case ERROR :
|
System.err.println("ERROR");
|
break;
|
default:
|
break;
|
}
|
}
|
strings.add(String.valueOf(str).trim());
|
}
|
|
if(in != index) {//判断如果每个单元格都为null则不需要添加到集合中
|
sheetList.add(strings);
|
}
|
}
|
list.add(sheetList);
|
}
|
|
|
}
|
|
return list;
|
}
|
|
|
|
// @描述：是否是2003的excel，返回true是2003
|
public static boolean isExcel2003(String filePath) {
|
return filePath.matches("^.+\\.(?i)(xls)$");
|
}
|
|
|
//@描述：是否是2007的excel，返回true是2007
|
public static boolean isExcel2007(String filePath) {
|
return filePath.matches("^.+\\.(?i)(xlsx)$");
|
}
|
|
|
/**
|
* 验证EXCEL文件
|
* @param filePath
|
* @return
|
*/
|
public static boolean validateExcel(String filePath){
|
if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))){
|
return false;
|
}
|
return true;
|
}
|
|
|
/**
|
* 将数据写入Excel中
|
* @param titles 标题
|
* @param datas 数据
|
* @return
|
*/
|
public HSSFWorkbook writeDataToExcel(List<List<String>> titles, List<List<List<String>>> datas) {
|
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
|
|
for(int i = 0; i < titles.size(); i++){
|
HSSFSheet hssfSheet = hssfWorkbook.createSheet();
|
hssfSheet.setColumnWidth(0, 6 * 256);
|
hssfSheet.setDefaultRowHeightInPoints(20f);
|
|
HSSFRow hssfRow = hssfSheet.createRow(0);//设置第一行数据（标题）
|
HSSFCellStyle style = hssfWorkbook.createCellStyle();
|
HSSFFont font = hssfWorkbook.createFont();
|
font.setBold(true);
|
style.setFont(font);
|
style.setAlignment(HorizontalAlignment.CENTER);
|
for (int l = 0; l < titles.get(i).size(); l++) {
|
HSSFCell hssfCell = hssfRow.createCell(l);
|
hssfCell.setCellType(CellType.STRING);//设置表格类型
|
hssfCell.setCellValue(titles.get(i).get(l));
|
hssfCell.setCellStyle(style);
|
if(l > 0) {
|
hssfSheet.setColumnWidth(l , 20 * 256);
|
}
|
|
}
|
|
//将数据添加到表格中
|
List<String> data = null;
|
for (int l = 0; l < datas.get(i).size(); l++) {
|
hssfRow = hssfSheet.createRow(l + 1);
|
data = datas.get(i).get(l);
|
for (int j = 0; j < data.size(); j++) {
|
HSSFCell hssfCell = hssfRow.createCell(j);
|
hssfCell.setCellType(CellType.STRING);//设置表格类型
|
hssfCell.setCellValue(data.get(j));
|
}
|
}
|
}
|
return hssfWorkbook;
|
}
|
}
|
