|
package com.cloud.user.utils;
|
|
import java.io.*;
|
import java.text.SimpleDateFormat;
|
import java.util.*;
|
|
import com.cloud.model.sys.BbEmployee;
|
import org.apache.log4j.Logger;
|
import org.apache.poi.POIXMLDocumentPart;
|
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
|
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
|
import org.apache.poi.ss.usermodel.*;
|
import org.apache.poi.xssf.usermodel.*;
|
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
|
import org.springframework.web.multipart.MultipartFile;
|
|
|
public class ExcelImportUtil {
|
//定义日志打印 和文件常量后缀名
|
private static Logger logger = Logger.getLogger(ExcelImportUtil.class);
|
private final static String xls = "xls";
|
private final static String xlsx = "xlsx";
|
|
/**
|
* 读入excel文件，解析后返回
|
* @param file
|
* @throws IOException
|
*/
|
public static List<String[]> readExcel(MultipartFile file,Integer startRow) throws IOException{
|
//检查文件
|
boolean checkFile = checkFile(file);
|
if(checkFile){
|
//获得Workbook工作薄对象
|
Workbook workbook = getWorkBook(file);
|
HSSFWorkbook hsBook = new HSSFWorkbook();
|
XSSFWorkbook xsBook = new XSSFWorkbook();
|
//创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
|
List<String[]> list = new ArrayList<String[]>();
|
if(workbook != null){
|
for(int sheetNum = 0;sheetNum < workbook.getNumberOfSheets();sheetNum++){
|
//获得当前sheet工作表
|
Sheet sheet = workbook.getSheetAt(sheetNum);
|
if(sheet == null){
|
continue;
|
}
|
//获得当前sheet的开始行
|
int firstRowNum = sheet.getFirstRowNum();
|
//获得当前sheet的结束行
|
int lastRowNum = sheet.getLastRowNum();
|
//循环除了第一行的所有行
|
for(int rowNum = firstRowNum+startRow;rowNum <= lastRowNum;rowNum++){
|
//获得当前行
|
Row row = sheet.getRow(rowNum);
|
if(row == null){
|
continue;
|
}
|
//获得当前行的开始列
|
int firstCellNum = row.getFirstCellNum();
|
//获得当前行的列数
|
int lastCellNum = row.getPhysicalNumberOfCells();
|
String[] cells = new String[row.getPhysicalNumberOfCells()];
|
//循环当前行
|
for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++){
|
Cell cell = row.getCell(cellNum);
|
cells[cellNum] = getCellValue(cell);
|
}
|
|
list.add(cells);
|
}
|
}
|
//((InputStream) workbook).close();
|
}
|
return list;
|
}else{
|
return null;
|
}
|
}
|
|
/**
|
* 读入excel文件，解析后返回
|
* @param file
|
* @throws IOException
|
*/
|
public List<BbEmployee> readExcelBbEmployee(MultipartFile file, Integer startRow) throws IOException{
|
//检查文件
|
boolean checkFile = checkFile(file);
|
if(checkFile){
|
//获得Workbook工作薄对象
|
Workbook workbook = getWorkBook(file);
|
//创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
|
List<BbEmployee> list = new ArrayList<BbEmployee>();
|
if(workbook != null){
|
for(int sheetNum = 0;sheetNum < workbook.getNumberOfSheets();sheetNum++){
|
//获得当前sheet工作表
|
Sheet sheet = workbook.getSheetAt(sheetNum);
|
if(sheet == null){
|
continue;
|
}
|
//获得当前sheet的开始行
|
int firstRowNum = sheet.getFirstRowNum();
|
//获得当前sheet的结束行
|
int lastRowNum = sheet.getLastRowNum();
|
//循环除了第一行的所有行
|
for(int rowNum = firstRowNum+startRow;rowNum <= lastRowNum;rowNum++){
|
//获得当前行
|
Row row = sheet.getRow(rowNum);
|
if(row == null){
|
continue;
|
}
|
|
BbEmployee bbEmployee = new BbEmployee();
|
//姓名
|
Cell cellName = row.getCell(0);
|
bbEmployee.setName(getCellValue(cellName));
|
//分库ID
|
Cell cellOrgId = row.getCell(1);
|
bbEmployee.setOrgId(Long.parseLong(getCellValue(cellOrgId)));
|
//组织部门ID
|
Cell cellOfficeId = row.getCell(2);
|
bbEmployee.setOfficeId(Long.parseLong(getCellValue(cellOfficeId)));
|
//身份证号
|
Cell cellCardId = row.getCell(3);
|
bbEmployee.setCardId(getCellValue(cellCardId));
|
//性别
|
Cell cellGender = row.getCell(4);
|
bbEmployee.setGender(getCellValue(cellGender));
|
//民族
|
Cell cellNation = row.getCell(5);
|
bbEmployee.setNation(getCellValue(cellNation));
|
//手机号
|
Cell cellPhone = row.getCell(6);
|
bbEmployee.setPhone(getCellValue(cellPhone));
|
//人员类型
|
Cell cellType = row.getCell(7);
|
bbEmployee.setType(getCellValue(cellType));
|
|
list.add(bbEmployee);
|
}
|
}
|
//((InputStream) workbook).close();
|
}
|
return list;
|
}else{
|
return null;
|
}
|
}
|
|
//判断文件是否为.xls或者.xlsx文件
|
public static boolean checkFile(MultipartFile file) throws IOException{
|
boolean flag=true;
|
//判断文件是否存在
|
if(null == file){
|
flag=false;
|
logger.error("文件不存在！");
|
throw new FileNotFoundException("文件不存在！");
|
}
|
//获得文件名
|
String fileName = file.getOriginalFilename();
|
//判断文件是否是excel文件
|
if(!fileName.endsWith(xls) && !fileName.endsWith(xlsx)){
|
flag=false;
|
logger.error(fileName + "不是excel文件");
|
throw new IOException(fileName + "不是excel文件");
|
}
|
return flag;
|
}
|
|
public static Workbook getWorkBook(MultipartFile file) {
|
//获得文件名
|
String fileName = file.getOriginalFilename();
|
//创建Workbook工作薄对象，表示整个excel
|
Workbook workbook = null;
|
try {
|
//获取excel文件的io流
|
InputStream is = file.getInputStream();
|
//根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
|
if(fileName.endsWith(xls)){
|
//2003
|
workbook = new HSSFWorkbook(is);
|
}else if(fileName.endsWith(xlsx)){
|
//2007
|
workbook = new XSSFWorkbook(is);
|
/* XSSFWorkbook sheet = new XSSFWorkbook(is);
|
XSSFSheet sheet2 = sheet.getSheetAt(0);
|
Map<String, PictureData> map = getSheetPictrues(sheet2);
|
byte[] bytes = map.get(1+"").getData();
|
String faces = FaceUtil.extractFeature(bytes,null);
|
System.out.println(5555);*/
|
}
|
} catch (IOException e) {
|
logger.info(e.getMessage());
|
}
|
return workbook;
|
}
|
|
//获取 XSSFWorkbook 对象
|
public static XSSFWorkbook getXSSFWorkbook(MultipartFile file) {
|
//获得文件名
|
String fileName = file.getOriginalFilename();
|
//创建Workbook工作薄对象，表示整个excel
|
XSSFWorkbook workbook = null;
|
try {
|
//获取excel文件的io流
|
InputStream is = file.getInputStream();
|
//根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
|
workbook = new XSSFWorkbook(is);
|
|
} catch (IOException e) {
|
logger.info(e.getMessage());
|
}
|
return workbook;
|
}
|
|
public static String getCellValue(Cell cell){
|
String cellValue = "";
|
if(cell == null){
|
return cellValue;
|
}
|
//把数字当成String来读，避免出现1读成1.0的情况
|
if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
|
|
//判断是否是日期类型 如果是日期类型 处理成日期类型
|
if (HSSFDateUtil.isCellDateFormatted(cell)) {
|
Date d = cell.getDateCellValue();
|
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
|
cellValue=sdf.format(d);
|
return cellValue;
|
}else{
|
cell.setCellType(Cell.CELL_TYPE_STRING);
|
}
|
|
}
|
//判断数据的类型
|
switch (cell.getCellType()){
|
case Cell.CELL_TYPE_NUMERIC: //数字
|
cellValue = String.valueOf(cell.getNumericCellValue());
|
break;
|
case Cell.CELL_TYPE_STRING: //字符串
|
cellValue = String.valueOf(cell.getStringCellValue());
|
break;
|
case Cell.CELL_TYPE_BOOLEAN: //Boolean
|
cellValue = String.valueOf(cell.getBooleanCellValue());
|
break;
|
case Cell.CELL_TYPE_FORMULA: //公式
|
cellValue = String.valueOf(cell.getCellFormula());
|
break;
|
case Cell.CELL_TYPE_BLANK: //空值
|
cellValue = "";
|
break;
|
case Cell.CELL_TYPE_ERROR: //故障
|
cellValue = "非法字符";
|
break;
|
default:
|
cellValue = "未知类型";
|
break;
|
}
|
return cellValue;
|
}
|
|
/**
|
* . 获取Excel2007图片
|
*
|
* @param sheet 当前sheet对象
|
* @return Map key:图片单元格索引（0_1_1）String，value:图片流PictureData
|
*/
|
public static Map<String, PictureData> getSheetPictrues(XSSFSheet sheet) {
|
Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();
|
for (POIXMLDocumentPart dr : sheet.getRelations()) {
|
if (dr instanceof XSSFDrawing) {
|
XSSFDrawing drawing = (XSSFDrawing) dr;
|
List<XSSFShape> shapes = drawing.getShapes();
|
for (XSSFShape shape : shapes) {
|
if (shape instanceof XSSFPicture) {
|
XSSFPicture pic = (XSSFPicture) shape;
|
XSSFClientAnchor anchor = pic.getPreferredSize();
|
CTMarker ctMarker = anchor.getFrom();
|
String picIndex = (ctMarker.getRow()-3)+"";
|
sheetIndexPicMap.put(picIndex, pic.getPictureData());
|
}
|
}
|
}
|
}
|
return sheetIndexPicMap;
|
}
|
/**
|
2 * 根据byte数组，生成文件
|
3 */
|
public static void getFile(byte[] bfile, String filePath,String fileName) {
|
BufferedOutputStream bos = null;
|
FileOutputStream fos = null;
|
File file = null;
|
try {
|
File dir = new File(filePath);
|
if(!dir.exists()&&!dir.isDirectory()){//判断文件目录是否存在
|
dir.mkdirs();
|
}
|
file = new File(filePath+"\\"+fileName);
|
fos = new FileOutputStream(file);
|
bos = new BufferedOutputStream(fos);
|
bos.write(bfile);
|
} catch (Exception e) {
|
e.printStackTrace();
|
} finally {
|
if (bos != null) {
|
try {
|
bos.close();
|
} catch (IOException e1) {
|
e1.printStackTrace();
|
}
|
}
|
if (fos != null) {
|
try {
|
fos.close();
|
} catch (IOException e1) {
|
e1.printStackTrace();
|
}
|
}
|
}
|
}
|
// 创建临时文件
|
private static File getFileFromBytes(byte[] b, String suffix) {
|
BufferedOutputStream stream = null;
|
File file = null;
|
try {
|
file = File.createTempFile("pattern", "." + suffix);
|
System.out.println("临时文件位置："+file.getCanonicalPath());
|
FileOutputStream fstream = new FileOutputStream(file);
|
stream = new BufferedOutputStream(fstream);
|
stream.write(b);
|
} catch (Exception e) {
|
e.printStackTrace();
|
} finally {
|
if (stream != null) {
|
try {
|
stream.close();
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
}
|
}
|
return file;
|
}
|
|
public static File byte2File(byte[] buf, String filePath, String fileName)
|
{
|
BufferedOutputStream bos = null;
|
FileOutputStream fos = null;
|
File file = null;
|
try
|
{
|
File dir = new File(filePath);
|
if (!dir.exists() && dir.isDirectory())
|
{
|
dir.mkdirs();
|
}
|
file = new File(filePath + File.separator + fileName);
|
fos = new FileOutputStream(file);
|
bos = new BufferedOutputStream(fos);
|
bos.write(buf);
|
}
|
catch (Exception e)
|
{
|
e.printStackTrace();
|
}
|
finally
|
{
|
if (bos != null)
|
{
|
try
|
{
|
bos.close();
|
}
|
catch (IOException e)
|
{
|
e.printStackTrace();
|
}
|
}
|
if (fos != null)
|
{
|
try
|
{
|
fos.close();
|
}
|
catch (IOException e)
|
{
|
e.printStackTrace();
|
}
|
}
|
}
|
return file;
|
}
|
}
|
