package com.matrix.system.hive.plugin.util;
|
|
import org.apache.poi.hssf.usermodel.*;
|
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 java.io.File;
|
import java.io.FileInputStream;
|
import java.io.IOException;
|
import java.text.DecimalFormat;
|
import java.text.SimpleDateFormat;
|
import java.util.LinkedList;
|
import java.util.List;
|
/**
|
* excel导入（根据需求调用03/07版本方法导入）
|
* @ClassName: ExcelImport
|
* @author jyy
|
* @date 2016年8月2日 上午10:39:04
|
*
|
*/
|
public class ExcelImport {
|
/**
|
* 对外提供读取excel 的方法
|
*/
|
public static List<List<Object>> readExcel(File file, String fileName, Integer rowNum, Integer cellNum)
|
throws IOException {
|
String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName.substring(fileName.lastIndexOf(".") + 1);
|
if ("xls".equals(extension)) {
|
return read2003Excel(file, rowNum, cellNum);
|
} else if ("xlsx".equals(extension)) {
|
return read2007Excel(file, rowNum, cellNum);
|
} else {
|
throw new IOException("不支持的文件类型");
|
}
|
}
|
|
/**
|
*
|
* @Title: read2003Excel
|
* @param @param file 待读取的文件
|
* @param @param rowNum 行数
|
* @param @param cellNum 列数
|
* @param @return
|
* @param @throws IOException 设定文件
|
* @return List<List<Object>> 返回类型 Object:单元格对象 List<Object>:行对象 List<List<Object>>:整个excel对象
|
* @throws
|
*/
|
private static List<List<Object>> read2003Excel(File file, Integer rowNum, Integer cellNum) throws IOException {
|
List<List<Object>> list = new LinkedList<List<Object>>();
|
HSSFWorkbook hwb = new HSSFWorkbook(new FileInputStream(file));
|
HSSFSheet sheet = hwb.getSheetAt(0);
|
Object value = null;
|
HSSFRow row = null;
|
HSSFCell cell = null;
|
Integer rowsNum = 0;// 读取的行数
|
Integer cellsNum = 0;// 读取的列数
|
if (rowNum != null) {
|
rowsNum = rowNum;
|
} else {
|
rowsNum = sheet.getPhysicalNumberOfRows();
|
}
|
for (int i = sheet.getFirstRowNum(); i <= rowsNum; i++) {
|
row = sheet.getRow(i);
|
if (row == null) {
|
continue;
|
}
|
if (row.getFirstCellNum() < 0) {
|
continue;
|
}
|
List<Object> linked = new LinkedList<Object>();
|
if (cellNum != null) {
|
cellsNum = cellNum;
|
} else {
|
cellsNum = (int) row.getLastCellNum();
|
}
|
for (int j = 0; j <= cellsNum; j++) {// row.getFirstCellNum()
|
cell = row.getCell(j);
|
if (cell == null) {
|
value = null;
|
} else {
|
DecimalFormat df = new DecimalFormat("0");// 格式化 number
|
// String
|
// 字符
|
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
|
DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字
|
switch (cell.getCellType()) {
|
case XSSFCell.CELL_TYPE_STRING:
|
value = cell.getStringCellValue();
|
break;
|
case XSSFCell.CELL_TYPE_NUMERIC:
|
if ("@".equals(cell.getCellStyle().getDataFormatString())) {
|
value = df.format(cell.getNumericCellValue());
|
} else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
|
value = nf.format(cell.getNumericCellValue());
|
} else {
|
value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
|
}
|
break;
|
case XSSFCell.CELL_TYPE_BOOLEAN:
|
value = cell.getBooleanCellValue();
|
break;
|
case XSSFCell.CELL_TYPE_BLANK:
|
value = "";
|
break;
|
default:
|
value = cell.toString();
|
}
|
}
|
linked.add(value);
|
}
|
list.add(linked);
|
}
|
return list;
|
}
|
|
|
/**
|
*
|
* @Title: read2007Excel
|
* @param @param file 待读取的文件
|
* @param @param rowNum 行数
|
* @param @param cellNum 列数
|
* @param @return
|
* @param @throws IOException 设定文件
|
* @return List<List<Object>> 返回类型 Object:单元格对象 List<Object>:行对象 List<List<Object>>:整个excel对象
|
* @throws
|
*/
|
private static List<List<Object>> read2007Excel(File file, Integer rowNum, Integer cellNum) throws IOException {
|
List<List<Object>> list = new LinkedList<List<Object>>();
|
// 构造 XSSFWorkbook 对象，strPath 传入文件路径
|
XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));
|
// 读取第一章表格内容
|
XSSFSheet sheet = xwb.getSheetAt(0);
|
Object value = null;
|
XSSFRow row = null;
|
XSSFCell cell = null;
|
Integer rowsNum = 0;// 读取的行数
|
Integer cellsNum = 0;// 读取的列数
|
if (rowNum != null) {
|
rowsNum = rowNum;
|
} else {
|
rowsNum = sheet.getPhysicalNumberOfRows();
|
}
|
for (int i = sheet.getFirstRowNum(); i <= rowsNum; i++) {
|
row = sheet.getRow(i);
|
if (row == null) {
|
continue;
|
}
|
if (row.getFirstCellNum() < 0) {
|
continue;
|
}
|
if (cellNum != null) {
|
cellsNum = cellNum;
|
} else {
|
cellsNum = (int) row.getLastCellNum();
|
}
|
List<Object> linked = new LinkedList<Object>();
|
for (int j = 0; j <= cellsNum; j++) {
|
cell = row.getCell(j);
|
if (cell == null) {
|
value = null;
|
} else {
|
DecimalFormat df = new DecimalFormat("0");// 格式化 number
|
// String
|
// 字符
|
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
|
DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字
|
switch (cell.getCellType()) {
|
case XSSFCell.CELL_TYPE_STRING:
|
value = cell.getStringCellValue();
|
break;
|
case XSSFCell.CELL_TYPE_NUMERIC:
|
if ("@".equals(cell.getCellStyle().getDataFormatString())) {
|
value = df.format(cell.getNumericCellValue());
|
} else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
|
value = nf.format(cell.getNumericCellValue());
|
} else {
|
value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
|
}
|
break;
|
case XSSFCell.CELL_TYPE_BOOLEAN:
|
value = cell.getBooleanCellValue();
|
break;
|
case XSSFCell.CELL_TYPE_BLANK:
|
value = "";
|
break;
|
default:
|
value = cell.toString();
|
}
|
}
|
linked.add(value);
|
}
|
list.add(linked);
|
}
|
return list;
|
}
|
/**
|
*
|
* @Title: getCellValue
|
* 获取某个单元格数据
|
* @param @param cell
|
* @param @return
|
* @param @throws Exception 设定文件
|
* @return Object 返回类型
|
* @throws
|
*/
|
public static Object getCellValue(HSSFCell cell) throws Exception {
|
Object value = null;
|
if (cell != null) {
|
DecimalFormat df = new DecimalFormat("0");// 格式化
|
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
|
DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字
|
switch (cell.getCellType()) {
|
case XSSFCell.CELL_TYPE_STRING:
|
value = cell.getStringCellValue();
|
break;
|
case XSSFCell.CELL_TYPE_NUMERIC:
|
if ("@".equals(cell.getCellStyle().getDataFormatString())) {
|
value = df.format(cell.getNumericCellValue());
|
} else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
|
value = nf.format(cell.getNumericCellValue());
|
} else {
|
value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
|
}
|
break;
|
case XSSFCell.CELL_TYPE_BOOLEAN:
|
value = cell.getBooleanCellValue();
|
break;
|
case XSSFCell.CELL_TYPE_BLANK:
|
value = "";
|
break;
|
default:
|
value = cell.toString();
|
}
|
}
|
return value;
|
}
|
|
public static void main(String[] args) throws IOException {
|
}
|
}
|
