1.上传excel到服务器
jsp页面代码
<form action="actionname" method="post" id="form1" enctype="multipart/form-data">
<input type="file" name="excel" id="fileExecl" class="inputFile" οnchange="uploadFile(this)" size="1" title=""/>
<input type="button" value="导入excel" οnclick="importExcel()">
<form>
js代码
function importExcel() {
var fileExecl = document.getElementById("fileExecl").value;
if(fileExecl==null||fileExecl==""){
alert("请选择excel文件");
return false;
}
document.getElementById("action").value="importExcel";
document.getElementById("form1").submit();
}
function uploadFile(importObj) {
var path = importObj.value;
var type = path.substring(path.lastIndexOf(".") + 1, path.length).toLowerCase();
if (type != "xlsx"&&type != "xls") {
alert("请上传xlsx或xls后缀的Excel");
importObj.value = "";
} else {
document.getElementById("action").value="importExcel";
}
}
action代码
private File excel;//上传的文件
private String excelFileName; // File属性名 + FileName固定的
private File uploadFile() {
InputStream is = null;
OutputStream os = null;
try {
is = new FileInputStream(excel);
String uploadPath = this.getServletContext().getRealPath("/staticFiles");
//分解路径
//String filePath = getFileDirectory(uploadPath);
File destFile = new File(uploadPath, excelFileName);
os = new FileOutputStream(destFile);
byte[] buffer = new byte[400];
int length = 0;
while ((length = is.read(buffer)) > 0) {
os.write(buffer, 0, length);
}
is.close();
os.close();
return destFile;
} catch (FileNotFoundException e) {
logger.error(e, e);
} catch (IOException e) {
logger.error(e, e);
}
return null;
}
public String importExcel() {
//文件上传路径
File file = uploadFile();
String filePath = file.getPath();
//获取导出数据
ImportExcel importExcel = new ImportExcel();
List<String[]> importList = importExcel.getImportList(filePath);
//删除上传文件
if(file.isFile() && file.exists()) {
file.delete();
}
for (int i = 1; i < importList.size(); i++) {
String[] rowData = importList.get(i);
//rowData[0] excel中第一列数据
}
return null;
}
解析excel代码 xls和xlsx两种格式,代码可以优化
public class ImportExcel {
/**
*
* @param filePath 文件路径
* @return excel中一行数据储存在一个数组String[]
*/
public List<String[]> getImportList(String filePath) {
List<String[]> rowsData = new ArrayList<String[]>();;
if(filePath.endsWith("xls")){
try {
boolean importData = false;
HSSFWorkbook hwb = new HSSFWorkbook(new FileInputStream(filePath));
HSSFSheet sheet = hwb.getSheetAt(0);
int rows = sheet.getPhysicalNumberOfRows();// 获取表格的行数
for (int r = 0; r < rows; r++) { // 循环遍历表格的行
importData = false;
String cellValue = "";
HSSFRow row = sheet.getRow(r);
if (row != null) {
int cells =row.getLastCellNum();
String[] rowData = new String[cells];
for (int c = row.getFirstCellNum(); c < cells; c++) {
HSSFCell cell = row.getCell(c);
if (cell != null) {
if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { // 判断单元格的值是否为字符串类型
cellValue = cell.getStringCellValue();
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { // 判断单元格的值是否为数字类型
cellValue = cell.getNumericCellValue() + "";
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) { // 判断单元格的值是否为布尔类型
cellValue = cell.getStringCellValue();
} else {
cellValue = "";
}
}
if (cellValue.trim().length() > 0) {
importData = true;
}
rowData[c] = cellValue;
}
//数据全为空,不导入
if (r > 0 && !importData) {
continue;
}
rowsData.add(rowData);
}
}
} catch (Exception e) {
e.printStackTrace();
}
}else if(filePath.endsWith("xlsx")){
try {
boolean importData = false;
XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(
filePath)); // 创建对Excel工作簿文件的引用
XSSFSheet sheet = workbook.getSheetAt(0); // 创建对第一个工作表的引用,多个工作表暂未实现
int rows = sheet.getPhysicalNumberOfRows();// 获取表格的行数
for (int r = 0; r < rows; r++) { // 循环遍历表格的行
importData = false;
String cellValue = "";
XSSFRow row = sheet.getRow(r); // 获取单元格中指定的行对象
if (row != null) {
int cells = row.getPhysicalNumberOfCells();// 获取单元格中指定列对象
String[] rowData = new String[cells];
for (short c = 0; c < cells; c++) { // 循环遍历单元格中的列
XSSFCell cell = row.getCell((short) c); // 获取指定单元格中的列
if (cell != null) {
if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { // 判断单元格的值是否为字符串类型
cellValue = cell.getStringCellValue();
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { // 判断单元格的值是否为数字类型
cellValue = cell.getNumericCellValue() + "";
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) { // 判断单元格的值是否为布尔类型
cellValue = cell.getStringCellValue();
} else {
cellValue = "";
}
}
if (cellValue.trim().length() > 0) {
importData = true;
}
rowData[c] = cellValue;
}
//数据全为空,不导入
if (r > 0 && !importData) {
continue;
}
rowsData.add(rowData);
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
return rowsData;
}
}