1.action中的代码
@RequestMapping("/importexcel")
public String importexcel(@RequestParam("filename") CommonsMultipartFile file,
HttpServletRequest request, HttpServletResponse response){
//判断文件是否为空
if(file==null) return null;
//获取文件名
String name=file.getOriginalFilename();
//进一步判断文件是否为空(即判断其大小是否为0或其名称是否为null)
long size=file.getSize();
if(name==null || ("").equals(name) && size==0) return null;
//批量导入。参数:文件名,文件。
ReadExcel readExcel=new ReadExcel();
List<Device> list=readExcel.getExcelInfo(name, file,defendService);
boolean flage=false;
if(list!=null){
flage=true;
List<Device> list2=deviceService.selectByPage(null);
}
for (Device device : list) {
deviceService.insert(device);
}
JSONObject result=new JSONObject();
if(flage){
String Msg ="批量导入EXCEL成功!";
result.put("success", "true");//业务逻辑,需要返回success,但返回的是错误message
}else{
String errorMsg ="批量导入EXCEL失败!";
result.put("success", "true");//业务逻辑,需要返回success,但返回的是错误message
result.put("errorMsg", "保存失败");
}
return "devicelist";
}
2.辅助类 读取数据
package com.gqxt.utils;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
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.commons.CommonsMultipartFile;
import com.gqxt.model.Defend;
import com.gqxt.model.Device;
import com.gqxt.service.DefendService;
/**
* 工具类导入数据
*
* @author user
*
*/
public class ReadExcel {
// 总行数
private int totalRows = 0;
// 总条数
private int totalCells = 0;
// 错误信息接收器
private String errorMsg;
// 构造方法
public ReadExcel() {
}
// 获取总行数
public int getTotalRows() {
return totalRows;
}
// 获取总列数
public int getTotalCells() {
return totalCells;
}
// 获取错误信息
public String getErrorInfo() {
return errorMsg;
}
/**
* 验证EXCEL文件
*
* @param filePath
* @return
*/
public boolean validateExcel(String filePath) {
if (filePath == null || !(WDWUtil.isExcel2003(filePath) || WDWUtil.isExcel2007(filePath))) {
errorMsg = "文件名不是excel格式";
return false;
}
return true;
}
/**
* 读EXCEL文件,获取客户信息集合
*
* @param fielName
* @return
*/
public List<Device> getExcelInfo(String name, CommonsMultipartFile uploadExcel, DefendService defendService) {
// 把spring文件上传的MultipartFile转换成CommonsMultipartFile类型
CommonsMultipartFile cf = (CommonsMultipartFile) uploadExcel;
// 获取本地存储路径
File file = new File("D:\\fileupload");
// 创建一个目录 (它的路径名由当前 File 对象指定,包括任一必须的父路径。)
if (!file.exists()) {
file.mkdirs();
}
// 新建一个文件
File file1 = new File("D:\\fileupload" + new Date().getTime() + ".xlsx");
// 将上传的文件写入新建的文件中
try {
cf.getFileItem().write(file1);
} catch (Exception e) {
e.printStackTrace();
}
// 初始化客户信息的集合
List<Device> customerList = new ArrayList<Device>();
// 初始化输入流
InputStream is = null;
try {
// 验证文件名是否合格
if (!validateExcel(name)) {
return null;
}
// 根据文件名判断文件是2003版本还是2007版本
boolean isExcel2003 = true;
if (WDWUtil.isExcel2007(name)) {
isExcel2003 = false;
}
// 根据新建的文件实例化输入流
is = new FileInputStream(file1);
// 根据excel里面的内容读取客户信息
customerList = getExcelInfo(is, isExcel2003, defendService);
is.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (is != null) {
try {
is.close();
} catch (IOException e) {
is = null;
e.printStackTrace();
}
}
}
return customerList;
}
/**
* 根据excel里面的内容读取客户信息
*
* @param is
* 输入流
* @param isExcel2003
* excel是2003还是2007版本
* @return
* @throws IOException
*/
private List<Device> getExcelInfo(InputStream is, boolean isExcel2003, DefendService defenService) {
List<Device> pmList = null;
try {
/** 根据版本选择创建Workbook的方式 */
Workbook wb = null;
// 当excel是2003时
if (isExcel2003) {
wb = new HSSFWorkbook(is);
} else {// 当excel是2007时
wb = new XSSFWorkbook(is);
}
// 读取Excel里面客户的信息
pmList = readExcelValue(wb, defenService);
} catch (IOException e) {
e.printStackTrace();
}
return pmList;
}
/**
* 读取Excel里面客户的信息
*
* @param wb
* @return
*/
private List<Device> readExcelValue(Workbook wb, DefendService defendService) {
// 得到第一个shell
Sheet sheet = wb.getSheetAt(0);
// 得到Excel的行数
this.totalRows = sheet.getPhysicalNumberOfRows();
// 得到Excel的列数(前提是有行数)
if (totalRows >= 1 && sheet.getRow(0) != null) {
this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
}
List<Device> pmList = new ArrayList<Device>();
Device device = null;
// 循环Excel行数,从第二行开始。标题不入库
for (int r = 1; r < totalRows; r++) {
Row row = sheet.getRow(r);
if (row == null)
continue;
device = new Device();
// 循环Excel的列
for (int c = 0; c < this.totalCells; c++) {
Cell cell = row.getCell(c);
if (null != cell) {
if (c == 0) {
row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
device.setId(cell.getStringCellValue());
} else if (c == 1) {
row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
device.setName(cell.getStringCellValue());
} else if (c == 2) {
row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
device.setFgb(Integer.parseInt(cell.getStringCellValue()));
} else if (c == 3) {
row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
device.setSlsb(cell.getStringCellValue());
} else if (c == 4) {
row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
device.setSlsbdk(cell.getStringCellValue());
} else if (c == 5) {
row.getCell(5).setCellType(Cell.CELL_TYPE_STRING);
device.setJrms(cell.getStringCellValue());
} else if (c == 6) {
row.getCell(6).setCellType(Cell.CELL_TYPE_STRING);
device.setRwsj(cell.getStringCellValue());
} else if (c == 7) {
row.getCell(7).setCellType(Cell.CELL_TYPE_STRING);
device.setFgfw(cell.getStringCellValue());
} else if (c == 8) {
row.getCell(8).setCellType(Cell.CELL_TYPE_STRING);
device.setAzdz(cell.getStringCellValue());
} else if (c == 9) {
row.getCell(9).setCellType(Cell.CELL_TYPE_STRING);
device.setGsigx(cell.getStringCellValue());
} else if (c == 10) {
row.getCell(10).setCellType(Cell.CELL_TYPE_STRING);
device.setJd(cell.getStringCellValue());
} else if (c == 11) {
row.getCell(11).setCellType(Cell.CELL_TYPE_STRING);
device.setWd(cell.getStringCellValue());
} else if (c == 12) {
row.getCell(12).setCellType(Cell.CELL_TYPE_STRING);
device.setSljgx(cell.getStringCellValue());
} else if (c == 13) {
row.getCell(13).setCellType(Cell.CELL_TYPE_STRING);
device.setSum(Integer.parseInt(cell.getStringCellValue()));
} else if (c == 14) {
row.getCell(14).setCellType(Cell.CELL_TYPE_STRING);
device.setKxsum(Integer.parseInt(cell.getStringCellValue()));
} else if (c == 15) {
row.getCell(15).setCellType(Cell.CELL_TYPE_STRING);
Defend defend = defendService.selectByName(cell.getStringCellValue());
device.setDefend(defend);
}
}
}
// 添加客户
pmList.add(device);
}
return pmList;
}
}
3.辅助类 判断是2003还是2007
package com.gqxt.utils;
/**
* �ж�excel�����
* @author user
*
*/
public class WDWUtil {
// @�������Ƿ���2003��excel������true��2003
public static boolean isExcel2003(String filePath) {
return filePath.matches("^.+\\.(?i)(xls)$");
}
//@�������Ƿ���2007��excel������true��2007
public static boolean isExcel2007(String filePath) {
return filePath.matches("^.+\\.(?i)(xlsx)$");
}
}