前端代码
table.on('tool(mbdy)', function(obj){
$.ajax({
type : "post", //请求方式
async : true, //是否异步
url:"ajax.do?ctrl=Mbdy_readRecordsInputPath",
data : {
path : obj.data.PATH,
},
dataType : "json",
success : function(ttt) {
var aa = "";
for (i=0;i<ttt.length;i++ ){
if(i==0){
var strs = ttt[i];
aa += '<tr>'
for (j=0;j<strs.length;j++ ){
aa += '<th>'+strs[j]+'</th>';
}
aa += '</tr>'
continue;
} else{
var strs = ttt[i];
aa += '<tr>'
for (j=0;j<strs.length;j++ ){
aa += '<td>'+strs[j]+'</td>';
}
aa += '</tr>'
continue;
}
}
layer.open({
shadeClose: true,
offset: '20%',
area: ['70%', '50%'],
content: '<table cellspacing="0" cellpadding="0" border="0" class="layui-table">'+aa+'</table>'
});
}
});
});
后端代码
/**
* 通过文件路径获取Excel读取
* @param path 文件路径,只接受xls或xlsx结尾
* @param isHeader 是否表头
* @param headerCount 表头行数
* @return count 如果文件路径为空,返回0;
*/
public String readRecordsInputPath(Map<String, Object> rmap) {
init(rmap);
String path = getValue(this.getForm().get("path"));
boolean isHeader = false;
int headerCount = 0;
List<String[]> poiList = new ArrayList<String[]>();
if(path == null){
return null;
}else if(!path.endsWith("xls") && !path.endsWith("xlsx")
&& !path.endsWith("XLS") && !path.endsWith("XLSX")){
return null;
}
File file = new File(path);
try {
InputStream inputStream = new FileInputStream(file);
if(path.endsWith("xls") || path.endsWith("XLS")){
poiList = readXLSRecords(inputStream, isHeader, headerCount);
}else if(path.endsWith("xlsx") || path.endsWith("XLSX")){
poiList = readXLSXRecords(inputStream, isHeader, headerCount);
}
} catch (Exception e) {
System.err.println(e);
return null;
}
return JSON.toJSONString(poiList);
}
/**
* 解析EXCEL2003文件流
* 如果一行记录的行中或行尾出现空格,POI工具类可能会跳过空格不做处理,所以默认第一行是表头,所有待解析的记录都以表头为准
* @param inputStream 输入流
* @param isHeader 是否要跳过表头
* @param headerCount 表头占用行数
* @return 返回一个字符串数组List
*/
public static List<String[]> readXLSRecords(InputStream inputStream, boolean isHeader, int headerCount) {
List<String[]> poiList = new ArrayList<String[]>();
try {
HSSFWorkbook wbs = new HSSFWorkbook(inputStream);
HSSFSheet childSheet = wbs.getSheetAt(0);
// 获取表头
int begin = childSheet.getFirstRowNum();
HSSFRow firstRow = childSheet.getRow(begin);
int cellTotal = firstRow.getPhysicalNumberOfCells();
// 是否跳过表头解析数据
if (isHeader) {
begin += headerCount;
}
// 逐行获取单元格数据
for (int i = begin; i <= childSheet.getLastRowNum(); i++) {
HSSFRow row = childSheet.getRow(i); // 一行的所有单元格格式都是常规的情况下,返回的row为null
if (null != row) {
String[] cells = new String[cellTotal];
for (int k = 0; k < cellTotal; k++) {
HSSFCell cell = row.getCell(k);
cells[k] = getStringXLSCellValue(cell);
}
poiList.add(cells);
}
}
} catch (Exception e) {
return null;
}
return poiList;
}
/**
* 解析EXCEL2007文件流 如果一行记录的行中或行尾出现空格,POI工具类可能会跳过空格不做处理,所以默认第一行是表头,所有待解析的记录都以表头为准
* 该处理方法中,表头对应都占用一行
* @param inputStream 输入流
* @param isHeader 是否要跳过表头
* @param headerCount 表头占用行数
* @return 返回一个字符串数组List
*/
public static List<String[]> readXLSXRecords(InputStream inputStream, boolean isHeader, int headerCount) {
List<String[]> poiList = new ArrayList<String[]>();
try {
XSSFWorkbook wbs = new XSSFWorkbook(inputStream);
XSSFSheet childSheet = wbs.getSheetAt(0);
// 获取表头
int begin = childSheet.getFirstRowNum();
XSSFRow firstRow = childSheet.getRow(begin);
int cellTotal = firstRow.getPhysicalNumberOfCells();
// 是否跳过表头解析数据
if (isHeader) {
begin += headerCount;
}
for (int i = begin; i <= childSheet.getLastRowNum(); i++) {
XSSFRow row = childSheet.getRow(i); // 一行的所有单元格格式都是常规的情况下,返回的row为null
if (null != row) {
String[] cells = new String[cellTotal];
for (int k = 0; k < cellTotal; k++) {
XSSFCell cell = row.getCell(k);
cells[k] = getStringXLSXCellValue(cell);
}
poiList.add(cells);
}
}
} catch (Exception e) {
return null;
}
return poiList;
}
/**
* 获取单元格数据内容为字符串类型的数据
*
* @param cell Excel单元格
* @return String 单元格数据内容
*/
private static String getStringXLSCellValue(HSSFCell cell) {
String strCell = "";
if (cell == null) {
return "";
}
// 将数值型参数转成文本格式,该算法不能保证1.00这种类型数值的精确度
DecimalFormat df = (DecimalFormat) NumberFormat.getPercentInstance();
StringBuffer sb = new StringBuffer();
sb.append("0");
df.applyPattern(sb.toString());
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
strCell = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
double value = cell.getNumericCellValue();
while (Double.parseDouble(df.format(value)) != value) {
if ("0".equals(sb.toString())) {
sb.append(".0");
} else {
sb.append("0");
}
df.applyPattern(sb.toString());
}
strCell = df.format(value);
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
strCell = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK:
strCell = "";
break;
default:
strCell = "";
break;
}
if (strCell == null || "".equals(strCell)) {
return "";
}
return strCell;
}
/**
* 获取单元格数据内容为字符串类型的数据
*
* @param cell Excel单元格
* @return String 单元格数据内容
*/
private static String getStringXLSXCellValue(XSSFCell cell) {
String strCell = "";
if (cell == null) {
return "";
}
// 将数值型参数转成文本格式,该算法不能保证1.00这种类型数值的精确度
DecimalFormat df = (DecimalFormat) NumberFormat.getPercentInstance();
StringBuffer sb = new StringBuffer();
sb.append("0");
df.applyPattern(sb.toString());
switch (cell.getCellType()) {
case XSSFCell.CELL_TYPE_STRING:
strCell = cell.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_NUMERIC:
double value = cell.getNumericCellValue();
while (Double.parseDouble(df.format(value)) != value) {
if ("0".equals(sb.toString())) {
sb.append(".0");
} else {
sb.append("0");
}
df.applyPattern(sb.toString());
}
strCell = df.format(value);
break;
case XSSFCell.CELL_TYPE_BOOLEAN:
strCell = String.valueOf(cell.getBooleanCellValue());
break;
case XSSFCell.CELL_TYPE_BLANK:
strCell = "";
break;
default:
strCell = "";
break;
}
if (strCell == null || "".equals(strCell)) {
return "";
}
return strCell;
}