package com.wonders.task.excel.util;
import com.wonders.task.excel.model.SendBo;
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 java.io.File;
import java.io.FileInputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
* Created with IntelliJ IDEA.
* User: zhoushun
* Date: 2014/12/6
* Time: 20:25
* To change this template use File | Settings | File Templates.
*/
public class PoiUtil {
private static String excel2007 = "D:\\all.xlsx";
private static void setCode(SendBo bo){
Pattern pattern = Pattern.compile("(\\S+)\\((\\d+)\\)(\\d+)号");
String sendId = bo.getSendId();
if(sendId == null || sendId.length() == 0){
bo.setCode1("");bo.setCode2("");bo.setCode3("");
}else{
Matcher matcher = pattern.matcher(sendId);
if(matcher.find()){
bo.setCode1(matcher.group(1));
bo.setCode2(matcher.group(2));
bo.setCode3(matcher.group(3));
}
}
}
public static List readExcel2007(){
List list = new ArrayList();
try{
File excelFile = new File(excel2007);
FileInputStream is = new FileInputStream(excelFile);// 获取文件输入流
XSSFWorkbook workbook2007 = new XSSFWorkbook(is);// 创建Excel2003文件对象
XSSFSheet sheet = workbook2007.getSheetAt(0);// 取出第一个工作表,索引是0
// 开始循环遍历行,表头不处理,从1开始
for (int i = 1; i <= sheet.getLastRowNum(); i++) {
XSSFRow row = sheet.getRow(i);// 获取行对象
if (row == null) {// 如果为空,不处理
continue;
}
SendBo bo = new SendBo();
// 循环遍历单元格
for (int j = 0; j < row.getLastCellNum(); j++) {
XSSFCell cell = row.getCell(j);// 获取单元格对象
switch(j){
case 0 :
bo.setSendDept(getStringCellValue(cell).trim());
break;
case 1 :
bo.setSendId(getStringCellValue(cell).trim());
break;
case 2 :
bo.setSendDate(getStringCellValue(cell).trim());
setCode(bo);
break;
case 3 :
bo.setTitle(getStringCellValue(cell).trim());
break;
case 4 :
bo.setSendMain(getStringCellValue(cell).trim());
break;
case 5 :
break;
case 6 :
break;
default:
break;
}
}
list.add(bo);
}
}catch(Exception e){}
return list;
}
/**
* 获取单元格数据内容为字符串类型的数据
*
* @param cell Excel单元格
* @return String 单元格数据内容
*/
private static String getStringCellValue(XSSFCell cell) {
String strCell = "";
switch (cell.getCellType()) {
case XSSFCell.CELL_TYPE_STRING:
strCell = cell.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_NUMERIC:
if (XSSFDateUtil.isCellDateFormatted(cell)) {
// 如果是date类型则 ,获取该cell的date值
strCell = new SimpleDateFormat("yyyy-MM-dd").format(XSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
} else { // 纯数字
strCell = String.valueOf(cell.getNumericCellValue());
}
break;
case XSSFCell.CELL_TYPE_BOOLEAN:
strCell = String.valueOf(cell.getBooleanCellValue());
break;
case XSSFCell.CELL_TYPE_BLANK:
strCell = "";
break;
default:
strCell = "";
break;
}
if (strCell.equals("") || strCell == null) {
return "";
}
if (cell == null) {
return "";
}
return strCell;
}
public static void main(String[] args){
String s = "沪地铁(2014)22号";
Pattern pattern = Pattern.compile("(\\S+)\\((\\d+)\\)(\\d+)号");
Matcher matcher = pattern.matcher(s);
if(matcher.find()){
int gc = matcher.groupCount();
for(int i = 0; i <= gc; i++)
System.out.println("group " + i + " :" + matcher.group(i));
}
//readExcel2007();
}
}
自定义处理日期:
package com.wonders.task.excel.util;
import org.apache.poi.ss.usermodel.DateUtil;
import java.util.Calendar;
/**
* Created with IntelliJ IDEA.
* User: zhoushun
* Date: 2014/12/7
* Time: 0:36
* To change this template use File | Settings | File Templates.
*/
public class XSSFDateUtil extends DateUtil {
protected static int absoluteDay(Calendar cal, boolean use1904windowing) {
return DateUtil.absoluteDay(cal, use1904windowing);
}
}
再分享一下我老师大神的人工智能教程吧。零基础!通俗易懂!风趣幽默!还带黄段子!希望你也加入到我们人工智能的队伍中来!https://blog.csdn.net/jiangjunshow
标签:xlsx,java,matcher,bo,excel,util,File,POI,import
来源: https://www.cnblogs.com/skinchqqhah/p/10351452.html