需求：读excel中的数据，去掉空行，入库
常用的读取数据方法当然是用poi了，循环sheet页，每一行row，每一个单元格cell。获得的数据再通过反射封装到对象中。
遇到的坑有：
（1）空行问题：具体情况具体处理吧，如果某一个的每个单元格如果都为空就认为是空行，也有某个单元格为空，就忽略该行的情况。
（2）性能问题：100条左右的数据，从读取数据到封装入库，总共花了20多秒，结果当然是poi速度太慢，虽然是小系统，但是这种速度也是不可忍受的。于是找到eventUserModel方式读取，见下一节
import java.io.File;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
import org.apache.commons.io.FileUtils;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
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.ss.usermodel.WorkbookFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.picc.ermss.controller.UploadController;
import com.picc.ermss.model.Require;
import com.picc.ermss.model.UserSheet;
public class ExcelUtil {
private final static Logger logger = LoggerFactory.getLogger(UploadController.class);
public static Map<String, List<String>> readSheetTitle(File file) {
Map<String, List<String>> sheetTitleMap = new HashMap<String, List<String>>();
Workbook workbook = null;
try {
// 获取 workbook
workbook = WorkbookFactory.create(FileUtils.openInputStream(file));
// 遍历 workbook
for (int i=0; i<workbook.getNumberOfSheets(); i++) {
// 获取某个sheet
Sheet sheet = workbook.getSheetAt(i);
List<String> titleList = new ArrayList<String>();
// 获取 sheet的第一行 表头
Row firstRow = sheet.getRow(0);
if(firstRow == null) {
continue;
}
// 遍历第一行的每个cell
for (int j=0; j<firstRow.getLastCellNum(); j++) {
Cell cell = firstRow.getCell(j);
String title = cell.getStringCellValue().trim();
if (!"".equals(title)) {
// 标题不为空，才添加到 titleList
titleList.add(title);
}
}
sheetTitleMap.put(sheet.getSheetName(), titleList);
}
} catch (IOException | EncryptedDocumentException | InvalidFormatException e) {
logger.info(e.getMessage());
} finally {
try {
if(workbook != null) {
workbook.close();
}
} catch (IOException e) {
logger.error(e.getMessage());
}
}
return sheetTitleMap;
}
private static Sheet sheet = null;
private static Row rowFirst = null;
private static Row row = null;
private static Map<String, String> objectMap = new HashMap<String, String>();
private static String title = "";
private static String value = "";
// 解析excel，返回List<Map<String, String>>格式的对象
public static List<Map<String, String>> parseExcelData(List<UserSheet> userSheetListChanged, File file) {
logger.info("开始方法-----解析excel成List<Map<String, String>>对象");
logger.info("获取workbook");
List<Map<String, String>> resultList = new LinkedList<Map<String, String>>();
Workbook workbook = null;
// 获取 workbook
try {
workbook = WorkbookFactory.create(FileUtils.openInputStream(file));
} catch (EncryptedDocumentException | InvalidFormatException | IOException e) {
e.printStackTrace();
}
logger.info("开始解析数据，并添加到list中");
for (UserSheet userSheet : userSheetListChanged) {
// 根据名字获取sheet
logger.info("要解析的sheetname：" + userSheet.getSheetname());
sheet = workbook.getSheet(userSheet.getSheetname());
// 获取第一行
rowFirst = sheet.getRow(0);
// 遍历每一行sheet.getPhysicalNumberOfRows()
for(int rowIndex = 1; rowIndex < sheet.getPhysicalNumberOfRows(); rowIndex++) {
logger.info("遍历第" + rowIndex + "行");
row = sheet.getRow(rowIndex);
// 每一行 对应一个 ObjectMap
objectMap.clear();
// 遍历数据行里的每一个单元格，遍历长度以第一行为准
for(int cellIndex=0;cellIndex < rowFirst.getPhysicalNumberOfCells();cellIndex++){ //cell
title = rowFirst.getCell(cellIndex).getStringCellValue();
// 给sheetid赋值
if("sheetid".equals(title)) {
value = userSheet.getSheetid();
}
// 给serialno赋值
if("serialno".equals(title)) {
value = String.valueOf(rowIndex);
}
Cell cell = row.getCell(cellIndex);
if(cell != null) {
cell.setCellType(Cell.CELL_TYPE_STRING);
value = cell.getStringCellValue();
}
// 把每个单元格里的值都放到map里
objectMap.put(title, value);
}
// 过滤空行
Set<Entry<String,String>> entrySet = objectMap.entrySet();
int sum = 0;
int actualSum = entrySet.size();
for(Entry<String,String> entry : entrySet) {
String value = entry.getValue();
if("".equals(value) || value == null) {
sum += 1;
}
}
if(actualSum - sum == 2) {
//resultList.add(objectMap);
break;
} else {
resultList.add(objectMap);
}
}
}
logger.info("结束方法-----解析excel成List<Map<String, String>>对象");
return resultList;
}
/**
* 利用反射将 List<Map<String,String>>结构 生成相应的List<T>数据
*
* */
public static <T> List<T> toObjectList(List<Map<String, String>> list, Class<T> clazz) {
List<T> returnList = new LinkedList<T>();
for(int i=0; i<list.size(); i++){
Set<Map.Entry<String, String>> set = list.get(i).entrySet();
Iterator<Entry<String, String>> iterator = set.iterator();
// 生成一个对象实例
T obj = null;
try {
obj = clazz.newInstance();
} catch (InstantiationException | IllegalAccessException e) {
e.printStackTrace();
}
Method[] methods = clazz.getDeclaredMethods();
while (iterator.hasNext()) {
Map.Entry<String, String> entry = (Map.Entry<String, String>) iterator.next();
for(Method m : methods){
if(m.getName().startsWith("set")) {
String methodName = entry.getKey().toString();
StringBuffer sb = new StringBuffer(methodName);
sb.replace(0, 1, (methodName.charAt(0)+"").toUpperCase());
// 获取set方法
methodName = "set" + sb.toString();
if(methodName.equals(m.getName())) {
try {
// 调用set方法，给对象赋值
m.invoke(obj, entry.getValue());
} catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
e.printStackTrace();
}
break;
}
}
}
}
returnList.add(obj);
}
System.out.println("size=" + returnList.size());
return returnList;
}
public static void main(String[] args) {
// System.out.println(ExcelUtil.class.getResource("/").getPath().substring(1));
// File file = new File("C:/Users/Administrator/Desktop/说明文档2/xxxx.xlsx");
// System.out.println(readSheetTitle_HSSF(file));
List<UserSheet> usersheetList = new ArrayList<UserSheet>();
UserSheet sheet = new UserSheet();
sheet.setSheetid("024e1efd-0c2b-4484-b807-e12ce9f0263a");
sheet.setSheetname("需求提验升级时间");
usersheetList.add(sheet);
File file = new File("D:/profiles/sts-bundle/workspace20170628/ermss/target/classes/aaa.xlsx");
List<Map<String, String>> list = parseExcelData(usersheetList, file);
System.out.println(list);
try {
System.out.println(toObjectList(list, Require.class));
} catch (Exception e) {
e.printStackTrace();
}
}
}
网友评论