怎么在java里面批量导入文件_Java中Excel文件的批量导入和导出

需要jar包：

后面两个主要是保证Excel2007这个版本的。

页面提交时别忘了  enctype="multipart/form-data"

package com.jykj.comutils.magenUtils;

import java.io.InputStream;

import java.text.SimpleDateFormat;

import java.util.Date;

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

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

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;

public class ExcelUtil {

/**

* Excel导入

* @param myFile

* @throws Exception

*/

public static void getExcel(MultipartFile myFile) throws Exception{

Workbook workbook = null;

String filename = myFile.getOriginalFilename();

InputStream inS = myFile.getInputStream();

if (filename.endsWith("xls")) {//2003版本

workbook = new HSSFWorkbook(inS);

}else if (filename.endsWith("xlsx")) {//2007版本

workbook = new XSSFWorkbook(inS);

}else {

throw new Exception("文件不是Excel文件");

}

//如有多个工作薄，workbook.getSheetAt(2);获取第三个工作薄

Sheet sheet = workbook.getSheet("sheet1");

//Row row1 = sheet.getRow(0);获取第一行

//Cell cell = row1.getCell(6);获取第一行第六列信息

int rows = sheet.getLastRowNum();//获取文件有多少行。不包含第一行

//如需要获取多少列：sheet.getRow(0).getPhysicalNumberOfCells();这是获取第一行有多少列

if (rows == 0) {

throw new Exception("没有数据");

}

for (int i = 1; i <= rows+1; i++) {

Row row = sheet.getRow(i);

if (row != null) {//该行不为空

Student student = new Student();

student.setStuId(getCellValue(row.getCell(0)));//第一列数据 学号ID

student.setStuName(getCellValue(row.getCell(1)));//第二列数据 名称

student.setStuClass(getCellValue(row.getCell(2)));//第三列数据 班级

}

}

}

public static String getCellValue(Cell cell){

String value = null;

if (cell != null) {

switch (cell.getCellType()) {//判断cell的类型

case HSSFCell.CELL_TYPE_NUMERIC://数字

value = cell.getDateCellValue() + "";

if (HSSFDateUtil.isCellDateFormatted(cell)) {//判断cell是不是时间

Date date = cell.getDateCellValue();

if (date != null) {

value = new SimpleDateFormat("yyyy-MM-dd").format(date);

}else {

value = "";

}

}

break;

case HSSFCell.CELL_TYPE_STRING://字符

value = cell.getStringCellValue();

break;

case HSSFCell.CELL_TYPE_BLANK://空白

break;

case HSSFCell.CELL_TYPE_ERROR://错误

break;

default:

break;

}

}

return value;

}

}

/**

* 导出

* @param response

* @throws IOException

*/

public static void exportExcel(HttpServletResponse response) throws IOException{

//创建一个webbook,对应一个Excel文件

HSSFWorkbook workbook = new HSSFWorkbook();

//给Excel文件添加文件簿

HSSFSheet sheet = workbook.createSheet("Sheet1");

//给Excel文件添加行

HSSFRow row = sheet.createRow(0);

//

HSSFCellStyle style = workbook.createCellStyle();

//给第一行添加数据

HSSFCell cell = row.createCell(0);

cell.setCellValue("学号");

cell = row.createCell(1);

cell.setCellValue("名称");

row = sheet.createRow(1);

cell = row.createCell(0);

cell.setCellValue("10");

cell = row.createCell(1);

cell.setCellValue("马亘");

OutputStream output = response.getOutputStream();

response.reset();

SimpleDateFormat sdf = new SimpleDateFormat("yyyMMddHHmmss");

String fileName = sdf.format(new Date());//这里是系统时间

response.setHeader("Content-disposition", "attachment; filename="+java.net.URLEncoder.encode("文件名称", "UTF-8")+".xls");//这里添加文件名称

response.setContentType("application/mesexcel");

workbook.write(output);

output.flush();

IOUtils.closeQuietly(output);

}