Java (新)将Excel数据读取到ListMap
Maven依赖: pom.xml
<!-- excel -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.8</version>
</dependency>
Controller访问类:
@Controller
@RequestMapping("/excel")
public class ImportExcelController {
@RequestMapping(value = "/readto/listmap", method = { RequestMethod.GET, RequestMethod.POST})
@ResponseBody
public String readExcelToListMap(MultipartFile upfile,HttpServletRequest request, HttpServletResponse response) {
ReturnResult result = ExcelUtil.readExcelDataToReturnResult(upfile);
if (200 == result.getStatus()) {
List<Map<String, Object>> listMap = (List<Map<String, Object>>) result.getData();
if (listMap.size() > 0) {
for (Map<String, Object> map : listMap) {
System.out.println(map.toString());
}
return ReturnResult.objectToJson(ReturnResult.build(200, "导入成功 ",null));
}else{
return ReturnResult.objectToJson(ReturnResult.build(300, "文件内容读取为空!"));
}
}else{
return ReturnResult.objectToJson(result);
}
}
}
Excel工具类:
import java.io.BufferedReader;
import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
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;
import com.test.common.pojo.ReturnResult;
public class ExcelUtil {
/**
* 将Excel数据读取到ListMap
* @param file
* 仅支持 csv,xls,XLS,XLs,Xls,xlsx
*/
public static ReturnResult readExcelDataToReturnResult(MultipartFile file) {
if (null != file) {
String fileName = file.getOriginalFilename(); // 获取文件名
if (StringUtils.isNotBlank(fileName)) {
if (fileName.toLowerCase().matches("^.*(csv)$")) {
try {
List<Map<String,Object>> listMap = getResource(file.getBytes());
return ReturnResult.build(200, "成功",listMap);
} catch (Exception e) {
e.printStackTrace();
return ReturnResult.build(500, "系统繁忙,请稍后再试!");
}
}else{
if (fileName.toLowerCase().matches("^.*(xls|XLS|XLs|Xls|xlsx)$")) {
String edition_2003 = "^.+\\.(?i)(xls)$"; // Excel_2003版本
String edition_2007 = "^.+\\.(?i)(xlsx)$"; // Excel_2007版本
try {
// 根据Excel版本创建对象
Workbook wb = null;
if (fileName.matches(edition_2003)) {
wb = new HSSFWorkbook(file.getInputStream());
} else {
if (fileName.matches(edition_2007)) {
wb = new XSSFWorkbook(file.getInputStream());
}
}
// 读取Excel里面的数据
if (null != wb) {
// 得到第一个shell
Sheet sheet = wb.getSheetAt(0);
// 得到Excel的行数
int totalRows = sheet.getPhysicalNumberOfRows();
// 得到Excel的列数(前提是有行数)
int totalCells = 0;
if (totalRows > 1 && sheet.getRow(0) != null) {
totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
}
List<Map<String, Object>> listMap = new ArrayList<Map<String, Object>>();
// 循环Excel行数
for (int r = 1; r < totalRows; r++) {
Row row = sheet.getRow(r);
if (row == null) {
continue;
}
// 循环Excel的列
Map<String, Object> map = new HashMap<String, Object>();
for (int c = 0; c < totalCells; c++) {
Cell cell = row.getCell(c);
if (null != cell) {
int cellType = cell.getCellType();
if (cellType == 0) {
DataFormatter dataFormatter = new DataFormatter();
dataFormatter.addFormat("###########", null);
String str = dataFormatter.formatCellValue(cell);
if (StringUtils.isNotBlank(str)) {
map.put(c+"",str);
}
}else{
String str = String.valueOf(cell);
if (StringUtils.isNotBlank(str)) {
map.put(c+"",str);
}
}
}
}
// 添加到list
if (map.size() > 0) {
listMap.add(map);
}
}
return ReturnResult.build(200, "成功",listMap);
}else{
return ReturnResult.build(300, "读取文件内容为空!");
}
} catch (Exception e) {
e.printStackTrace();
return ReturnResult.build(500, "系统繁忙,请稍后再试!");
}
}else{
return ReturnResult.build(300, "文件格式错误,仅支持 xls,XLS,XLs,Xls,xlsx后缀的文件!");
}
}
}else{
return ReturnResult.build(300, "文件名不能为空!");
}
}else{
return ReturnResult.build(300, "文件不能为空!");
}
}
/** 获取csv文件内容 **/
private static List<Map<String,Object>> getResource(byte[] bate) throws IOException {
List<Map<String,Object>> allString = new ArrayList<>();
// 获取文件内容
List<String> list = getSource(bate);
if (list.size() > 1) {
Map<String,Object> callLogInfo = new HashMap<>();
// 循环内容
for(int i = 1; i<list.size();i++){
List<String> content = Arrays.asList(list.get(i).split(","));
if(content!=null && content.size()>0){
callLogInfo = new HashMap<>();
for (int j = 0; j < content.size(); j++) {
callLogInfo.put(j+"",content.get(j));
}
allString.add(callLogInfo);
}
}
}
return allString;
}
/** 读文件数据 **/
private static List<String> getSource(byte[] bate) throws IOException {
BufferedReader br = null;
ByteArrayInputStream fis=null;
InputStreamReader isr = null;
try {
fis = new ByteArrayInputStream(bate);
isr = new InputStreamReader(fis,"GB2312");
br = new BufferedReader(isr);
} catch (Exception e) {
e.printStackTrace();
}
String line;
String everyLine ;
List<String> allString = new ArrayList<>();
try {
//读取到的内容给line变量
while ((line = br.readLine()) != null){
everyLine = line;
allString.add(everyLine.trim());
}
} catch (IOException e) {
e.printStackTrace();
}finally {
if(fis != null){
fis.close();
}
if(isr != null){
isr.close();
}
}
return allString;
}
}
