用Java读取Excel需要导入相关的包。下面是项目中用到的jar包。
具体的代码如下:
public Map<String, String> Upload(HttpServletRequest request) throws FileUploadException, IOException {
Map<String, String> result = new HashMap<String, String>();
DiskFileItemFactory factory = new DiskFileItemFactory();
HashMap<Integer, Object> map = new HashMap<Integer, Object>();
// 设置缓冲区大小
factory.setSizeThreshold(1024 * 20);
// 磁盘的缓存位置
// sfactory.setRepository(new File("D:/2222"));
// 得到文件解析器
ServletFileUpload upload = new ServletFileUpload(factory);
List<FileItem> items = upload.parseRequest(request);
for (FileItem item : items) {
if (item.isFormField()) {// 判断是否为普通表单字段
// 普通form表单字段的参数名
String paramName = item.getFieldName();
// 普通form表单字段的参数值
String paramVal = item.getString("UTF-8");
result.put(paramName, paramVal);
} else if ((item.getName().endsWith(".xlsx")) && !StringUtils.isEmpty(item.getName())) {
// 上传表时删除上一次的数据
sampleService.delete();
Map<String, String> hashMap = null;
try {
book = new XSSFWorkbook(item.getInputStream());
sheet = book.getSheetAt(0);
hashMap = new HashMap<String, String>();
for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {
List<Object> list = new ArrayList<>();
row = sheet.getRow(i);
if (row != null) {
row.getPhysicalNumberOfCells();
for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
if (sheet.getRow(0).getCell(j) != null && row.getCell(j) != null) {
XSSFCell cell = row.getCell(j);
// 虽然excel中设置的都是文本,但是数字文本还被读错,如“1”取成“1.0”
// 加上下面这句,临时把它当做文本来读取
cell.setCellType(cell.CELL_TYPE_STRING);
list.add(sheet.getRow(0).getCell(j).toString());
hashMap.put(sheet.getRow(0).getCell(j).toString(), cell.toString());
}
}
for (int y = 0; y < list.size(); y++) {
map.put(y, (String) list.get(y));
}
ProjectUtil.setProject(map);
}
if (hashMap != null) {
String adress = null;
String num = null;
String name = null;
String data = null;
for (Map.Entry<String, String> entry : hashMap.entrySet()) {
String str = entry.getKey().toString();
if (map.get(0) != null) {
if (map.get(0).equals(entry.getKey().toString())) {
adress = entry.getValue().toString();
}}
if (map.get(1) != null) {
if (map.get(1).equals(entry.getKey().toString())) {
num = entry.getValue().toString();
}}
if (map.get(2) != null) {
if (map.get(2).equals(entry.getKey().toString())) {
data = entry.getValue().toString();
}}
if (map.get(3) != null) {
if (map.get(3).equals(entry.getKey().toString())) {
name = entry.getValue().toString();
}}
}
// 删除表中所有数据重新导入数据
Sample sample = new Sample();
sample.setAdress(adress);
sample.setName(name);
sample.setNum(num);
sample.setData(data);
}
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
} else {
result.put("msg", "请上传以.xlsx结尾的文件");
}
item.delete();
}
return result;
}