前言
使用上篇博文的导入方法，写一个简单的导入demo。其实有了工具类之后就没啥难度了，也就只简单的拿数据。先写个简单的，然后想办法实现动态读取吧，这样读取其实还是比较烦的，每次该模板都要改代码，说到底我还是比较懒的。
excel文件
实体类
package cc.vvxtoys.poi;
public class Student {
private String id;
private String stcode;
private String stname;
private String sex;
private String phone;
private String school;
private String address;
private String birthday;
public String getBirthday() {
return birthday;
}
public void setBirthday(String birthday) {
this.birthday = birthday;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getStcode() {
return stcode;
}
public void setStcode(String stcode) {
this.stcode = stcode;
}
public String getStname() {
return stname;
}
public void setStname(String stname) {
this.stname = stname;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getSchool() {
return school;
}
public void setSchool(String school) {
this.school = school;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public Student(String id, String stcode, String stname, String sex, String phone, String school, String address) {
super();
this.id = id;
this.stcode = stcode;
this.stname = stname;
this.sex = sex;
this.phone = phone;
this.school = school;
this.address = address;
}
public Student() {
super();
}
}
service
package cc.vvxtoys.poi;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import cc.vvxtoys.util.CommonTools;
public class ExcelService {
private static ImportExcelUtils utils = new ImportExcelUtils();
public static Object getExcelParser(String path, String sheet, String start, String end) throws Exception {
InputStream is = null;
if (utils.isEmpty(path)) {
return new FileNotFoundException("file not found");
} else {
is = new FileInputStream(path);
}
if (path.endsWith(ImportExcelUtils.EXCEL_2003)) {
HSSFWorkbook workbook = new HSSFWorkbook(is);
return importXls(start, end, workbook, sheet);
} else if (path.endsWith(ImportExcelUtils.EXCEL_2007)) {
XSSFWorkbook workbook = new XSSFWorkbook(is);
return importXlsx(start, end, workbook, sheet);
} else {
is.close();
return new Exception("type error");
}
}
public static Map<String, Object> importXls(String start, String end, HSSFWorkbook workbook, String sheet) {
Map<String, Object> result = new HashMap<String, Object>();
List<Student> sList = new ArrayList<>();
HSSFSheet hssfSheet = null;
// 如果传过来sheet页的名称,取当前sheet页,否则遍历所有sheet
if (!utils.isEmpty(sheet)) {
hssfSheet = workbook.getSheet(sheet);
} else {
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
hssfSheet = workbook.getSheetAt(i);
int maxIndex = hssfSheet.getLastRowNum();// 获取最大下标
int startIndex = 0;
int endIndex = 0;
if (utils.isEmpty(start)) {
start = String.valueOf(0);
}
if (utils.isEmpty(end)) {
end = maxIndex + String.valueOf(1);
}
if (Integer.parseInt(start) <= 1) {
startIndex = 1;
} else {
startIndex = Integer.parseInt(start) - 1;
}
if (Integer.parseInt(end) > maxIndex) {
endIndex = maxIndex + 1;
} else {
endIndex = Integer.parseInt(end);
}
for (int j = startIndex; j < endIndex; j++) {
Student student = new Student();
HSSFRow row = hssfSheet.getRow(j);
//空行
if (utils.isBlank(row)) {
break;
}
HSSFCell c1 = row.getCell(0);
HSSFCell c2 = row.getCell(1);
HSSFCell c3 = row.getCell(2);
HSSFCell c4 = row.getCell(3);
HSSFCell c5 = row.getCell(4);
HSSFCell c6 = row.getCell(5);
HSSFCell c7 = row.getCell(6);
student.setId(CommonTools.get32UUID());//获取uuid 这个方法就不列出了，很简单
if(!utils.isEmpty(c1)){
String stcode = utils.getValue(c1);
student.setStcode(stcode);
}
if(!utils.isEmpty(c2)){
String stname = utils.getValue(c2);
student.setStname(stname);
}
if(!utils.isEmpty(c3)){
String sex = utils.getValue(c3);
student.setSex(sex);
}
if(!utils.isEmpty(c4)){
String phone = utils.getValue(c4);
student.setPhone(phone);
}
if(!utils.isEmpty(c5)){
String birthday = utils.getValue(c5).replaceAll("/", "-");
student.setBirthday(birthday);
}
if(!utils.isEmpty(c6)){
String school = utils.getValue(c6);
student.setSchool(school);
}
if(!utils.isEmpty(c7)){
String address = utils.getValue(c7);
student.setAddress(address);
}
sList.add(student);
}
}
}
result.put("sList", sList);
return result;
}
public static Map<String, Object> importXlsx(String start, String end, XSSFWorkbook workbook, String sheet) {
Map<String, Object> result = new HashMap<String, Object>();
List<Student> sList = new ArrayList<>();
XSSFSheet xssfSheet = null;
// 如果传过来sheet页的名称,取当前sheet页,否则遍历所有sheet
if (!utils.isEmpty(sheet)) {
xssfSheet = workbook.getSheet(sheet);
} else {
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
xssfSheet = workbook.getSheetAt(i);
int maxIndex = xssfSheet.getLastRowNum();// 获取最大下标
int startIndex = 0;
int endIndex = 0;
if (utils.isEmpty(start)) {
start = String.valueOf(0);
}
if (utils.isEmpty(end)) {
end = maxIndex + String.valueOf(1);
}
if (Integer.parseInt(start) <= 1) {
startIndex = 1;
} else {
startIndex = Integer.parseInt(start) - 1;
}
if (Integer.parseInt(end) > maxIndex) {
endIndex = maxIndex + 1;
} else {
endIndex = Integer.parseInt(end);
}
for (int j = startIndex; j < endIndex; j++) {
Student student = new Student();
XSSFRow row = xssfSheet.getRow(j);
XSSFCell c1 = row.getCell(0);
XSSFCell c2 = row.getCell(1);
XSSFCell c3 = row.getCell(2);
XSSFCell c4 = row.getCell(3);
XSSFCell c5 = row.getCell(4);
XSSFCell c6 = row.getCell(5);
XSSFCell c7 = row.getCell(6);
student.setId(CommonTools.get32UUID());//uuid
if(!utils.isEmpty(c1)){
String stcode = utils.getValue(c1);
student.setStcode(stcode);
}
if(!utils.isEmpty(c2)){
String stname = utils.getValue(c2);
student.setStname(stname);
}
if(!utils.isEmpty(c3)){
String sex = utils.getValue(c3);
student.setSex(sex);
}
if(!utils.isEmpty(c4)){
String phone = utils.getValue(c4);
student.setPhone(phone);
}
if(!utils.isEmpty(c5)){
String birthday = utils.getValue(c5).replaceAll("/", "-");
student.setBirthday(birthday);
}
if(!utils.isEmpty(c6)){
String school = utils.getValue(c6);
student.setSchool(school);
}
if(!utils.isEmpty(c7)){
String address = utils.getValue(c7);
student.setAddress(address);
}
sList.add(student);
}
}
}
result.put("sList", sList);
return result;
}
public static void main(String[] args) throws Exception {
String path = "D:\\Desktop\\2.xlsx"; //文件路径
String start = "0"; //开始条数
String end = "10";//结束条数
String sheet = null;//sheet页名称
ExcelService service = new ExcelService();
Map<String, Object> result = (Map<String, Object>) service.getExcelParser(path, sheet, start, end);
}
}