java excel导入jsp_java spring mvc jsp excel 导入

pom文件添加

org.apache.poi

poi-ooxml

3.12

commons-fileupload

artifactId>commons-fileupload

spring配置

class="org.springframework.web.multipart.commons.CommonsMultipartResolver">

jsp页面代码

批量导入

......

js代码

$(".addExcelBtn").unbind('click');

$(".addExcelBtn").click(function() {

$(".import").unbind('click');

$(".import").click(function() {

$('input[type="file"][name="uploadFile"]').click();

});

$('input[type="file"]').unbind("change");

$('input[type="file"]').change(function () {

uploadFile();

$('input[type="file"][name="uploadFile"]').val("");

});

});

function uploadFile() {

var fileName = $('input[type="file"][name="uploadFile"]').val();

if (fileName === '') {

frameModal.hitModal("请选择文件");

return;

}

var fileType = (fileName.substring(fileName

.lastIndexOf(".") + 1, fileName.length))

.toLowerCase();

if (fileType !== 'xls' && fileType !== 'xlsx') {

frameModal.hitModal("文件格式不正确，excel文件！");

return;

}

$(".fileForm").ajaxSubmit({

dataType : "json",

data: {'1': 1},

type: "POST",

url: 'url',

success : function(data, textStatus) {

console.log(data);

},

error: function () {

frameModal.hitModal('操作失败');

}

});

};

controller代码

@RequestMapping(value = "/uploadExcel.action", method = RequestMethod.POST)

@ResponseBody

public ResponseVo uploadExcel(HttpServletRequest request) {

// 创建一个通用的多部分解析器

CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver(

request.getSession().getServletContext());

// 判断 request 是否有文件上传,即多部分请求

if (multipartResolver.isMultipart(request)) {

// 转换成多部分request

MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request;

MultipartFile file = multiRequest.getFile("uploadFile");

try {

//excelUtil工具类

List res = ExcelUtil.readExcel(file);

if (res != null && res.size() > 0) {

//存库或者其他处理逻辑

}

} catch (IOException e) {

}

}

return null;

}

excelUtil工具类

import java.io.FileNotFoundException;

import java.io.IOException;

import java.io.InputStream;

import java.text.DecimalFormat;

import java.util.ArrayList;

import java.util.List;

import org.apache.log4j.Logger;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.usermodel.Workbook;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.springframework.web.multipart.MultipartFile;

/**

* @ClassName: ExcelUtil

* @Description: Excel导入导出工具类

*/

public class ExcelUtil {

private static Logger logger = Logger.getLogger(ExcelUtil.class);

private final static String xls = "xls";

private final static String xlsx = "xlsx";

/**

* 读入excel文件，解析后返回

* @param file

* @throws IOException

*/

public static List readExcel(MultipartFile file) throws IOException{

//检查文件

checkFile(file);

//获得Workbook工作薄对象

Workbook workbook = getWorkBook(file);

//创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回

List list = new ArrayList();

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+1;rowNum <= lastRowNum;rowNum++){

int firstColumn = 0;

for(int rowNum = firstRowNum;rowNum <= lastRowNum;rowNum++){

//获得当前行

Row row = sheet.getRow(rowNum);

if(row == null){

continue;

}

//获得当前行的开始列

//int firstCellNum = row.getFirstCellNum();

int firstCellNum = 0;

//获得当前行的列数

int lastCellNum = row.getPhysicalNumberOfCells();

if (rowNum == firstRowNum) {

firstColumn = lastCellNum;

}

String[] cells = new String[firstColumn];

//循环当前行

for(int cellNum = firstCellNum; cellNum < firstColumn;cellNum++){

Cell cell = row.getCell(cellNum);

cells[cellNum] = getCellValue(cell);

}

list.add(cells);

}

}

workbook.close();

}

return list;

}

public static void checkFile(MultipartFile file) throws IOException{

//判断文件是否存在

if(null == file){

logger.error("文件不存在！");

throw new FileNotFoundException("文件不存在！");

}

//获得文件名

String fileName = file.getOriginalFilename();

//判断文件是否是excel文件

if(!fileName.endsWith(xls) && !fileName.endsWith(xlsx)){

logger.error(fileName + "不是excel文件");

throw new IOException(fileName + "不是excel文件");

}

}

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);

}

} catch (IOException e) {

logger.info(e.getMessage());

}

return workbook;

}

public static String getCellValue(Cell cell){

DecimalFormat df = new DecimalFormat("#");

String cellValue = "";

if(cell == null){

return "";

}

//把数字当成String来读，避免出现1读成1.0的情况

if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){

cell.setCellType(Cell.CELL_TYPE_STRING);

}

//判断数据的类型

switch (cell.getCellType()){

case Cell.CELL_TYPE_NUMERIC: //数字

//cellValue = String.valueOf(cell.getNumericCellValue());

//double c = cell.getNumericCellValue();

//cellValue = df.format(c);

//BigDecimal big = new BigDecimal(cell.getNumericCellValue());

double c = cell.getNumericCellValue();

cellValue = df.format(c);

break;

case Cell.CELL_TYPE_STRING: //字符串

//cellValue = String.valueOf(cell.getStringCellValue());

cellValue = cell.getStringCellValue().toString();

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;

}

}

注意：

1.excel中如果有长数字如果在getCellValue时不把类型转换为字符串类型，可能会导致数字损失精度

2.工具类原方法中解析excel是根据每行中的列数去获取，修改了工具类，如果列获取不到数值，就返回空。