- poi是Apache基金会下的一个开源项目,对excel、doc等文档进行解析和创建
- 首先是对excel的解析
- 这个是一个excel的模板,上代码
- 一个实体类对应excel的信息
package com.sunyw.xyz.vo;
import java.io.Serializable;
public class PoiExcelBean implements Serializable {
private static final long serialVersionUID = 4056442638289512007L;
private String name;
private String age;
private String phoneNumber;
private String email;
private String address;
@Override
public String toString() {
return "PoiExcelBean{" +
"name='" + name + '\'' +
", age='" + age + '\'' +
", phoneNumber='" + phoneNumber + '\'' +
", email='" + email + '\'' +
", address='" + address + '\'' +
'}';
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAge() {
return age;
}
public void setAge(String age) {
this.age = age;
}
public String getPhoneNumber() {
return phoneNumber;
}
public void setPhoneNumber(String phoneNumber) {
this.phoneNumber = phoneNumber;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
}
- excel工具类
package com.sunyw.xyz.utils;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Sheet;
import org.springframework.util.StringUtils;
import java.text.DecimalFormat;
import java.util.regex.Pattern;
public class ExcelUtils {
/**
* 取值
*
* @param row
* @param cell
* @param sheet
* @return
*/
public String getCellValue(int row, int cell, Sheet sheet) {
String value;
CellType cellType = sheet.getRow(row).getCell(cell).getCellType();
switch (cellType) {
case STRING:
value = sheet.getRow(row).getCell(cell).getStringCellValue();
break;
case NUMERIC:
value = String.valueOf(sheet.getRow(row).getCell(cell).getNumericCellValue());
break;
case FORMULA:
value = formatString(String.valueOf(sheet.getRow(row).getCell(cell).getNumericCellValue()));
break;
case BOOLEAN:
value = String.valueOf(sheet.getRow(row).getCell(cell).getBooleanCellValue());
break;
default:
value = "";
}
return value;
}
/**
* 去除"."
*
* @param value
* @return
*/
public String formatString(String value) {
if (!StringUtils.isEmpty(value)) {
value = value.substring(0, value.indexOf("."));
}
return value;
}
/**
* 科学计数法转换为数字
*
* @param value
* @return
*/
public String format(String value) {
Pattern pattern = Pattern.compile("(-?\\d+\\.?\\d*)[Ee]{1}[\\+-]?[0-9]*");
DecimalFormat ds = new DecimalFormat("0");
if (!pattern.matcher(value).matches()) {
return value;
}
value = ds.format(Double.parseDouble(value)).trim();
return value;
}
}
- 工具类的getcell中row为竖行,cell为列,从0开始计数
- 像姓名,如果要取值的话就应该是在第2行的第0位,往后依次类推
- 因为有的像电话中在读出来后可能会显示为科学计数法的形式,还有像整数会有.0,比如我上面的年龄20,在取值后就变为了20.0,所以做了去除0和科学计数法转换
- 处理类
package com.sunyw.xyz.service;
import com.sunyw.xyz.utils.ExcelUtils;
import com.sunyw.xyz.vo.PoiExcelBean;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
public class ExcelService {
private static final String xlsx = ".xlsx";
private static final String xls = ".xls";
public static void main(String[] args) throws IOException {
System.out.println("------------------------<开始进行excel解析>------------------------");
PoiExcelBean bean = new PoiExcelBean();
File file = new File("/21.xls");
//File file = new File("D:/21.xls");
String fileName = file.getName();
//判断文件类型
InputStream inputStream = new FileInputStream(file);
String end_name = fileName.substring(fileName.lastIndexOf("."));
Workbook wb;
System.out.println("endname:" + end_name);
if (xlsx.equals(end_name)) {
/*xlsx格式需要使用 XSSFWorkbook对象进行解析*/
wb = new XSSFWorkbook(inputStream);
System.out.println("当前为xlsx格式开始解析");
} else if (xls.equals(end_name)) {
/*xls格式需要使用 HSSFWorkbook对象进行解析*/
wb = new HSSFWorkbook(inputStream);
System.out.println("当前为xls格式,开始解析");
} else {
System.out.println("文件格式错误");
return;
}
//获取第一个sheet页,即excel下方的sheet
Sheet sheet = wb.getSheetAt(0);
//excel的角标读取时是从0开始,跟数组一样
ExcelUtils utils = new ExcelUtils();
String name = utils.getCellValue(2, 0, sheet);
System.out.println("姓名:" + name);
bean.setName(name);
String age = utils.formatString(utils.getCellValue(2, 1, sheet));
System.out.println("年龄:" + age);
bean.setAge(age);
String phone = utils.format(utils.getCellValue(2, 2, sheet));
System.out.println("电话:" + phone);
bean.setPhoneNumber(phone);
String email = utils.getCellValue(2, 3, sheet);
System.out.println("邮箱:" + email);
bean.setEmail(email);
String address = utils.getCellValue(2, 4, sheet);
System.out.println("地址:" + address);
bean.setAddress(address);
System.out.println("对象信息为:[" + bean + "]");
System.out.println("------------------------<excel解析完毕>------------------------");
}
}
-
结果
-
码云地址:https://gitee.com/sunyaowei/poitest