这是一个思路希望能帮助到大家:如果大家有更好的解决方法希望分享出来
公司导入是这样做的
每个到导入的地方
@Override
public List<DataImportMessage> materialDataImport2(byte[] fileBytes,
String fileName) {
//return DataImport(fileBytes, fileName, "inv_m");
File file = FileUtils.getFileFromBytes(fileBytes, fileName);
ExcelUtil excelUtil = null;
try {
excelUtil = new ExcelUtil(file);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
throw new CustomException("COM_016");
} catch (IOException e) {
throw new CustomException("COM_016");
}
StringBuffer insertSQLSB = new StringBuffer();
String insertSQLFormat = "insert into scm_inventory(pk_inv,pk_invClass,invCode,invName,pk_sizeGroup,pk_aunit,invStd,pk_color,invYarn,invElement,invWidth,vdef1) values('%s','%s','%s','%s',%s,'%s','%s','%s','%s','%s',%s,'%s');";
Iterator<Row> rows = excelUtil.getRows(0);
int skipRows = 6;
int count = 0;
Map<String, String> invClsPkCache = new HashMap<String, String>();
Map<String, String> unitPkCache = new HashMap<String, String>();
Map<String, String> colorPkCache = new HashMap<String, String>();
List<DataImportMessage> dms = new ArrayList<DataImportMessage>(); //用来存放出错的行信息
Row headRow = null;
while (rows.hasNext()) {
count++;
Row row = rows.next();
if (count == 1) {
if (row == null || row.getCell(1) == null
|| !"inv_m".equals(row.getCell(1).getStringCellValue())) { //判断是不是材料导入模板
file.delete();
throw new CustomException("COM_017");
}
}
if (count == 2) {
headRow = row;
}
if (count > skipRows) { //从第7行开始进行遍历(模板中第7行才是要导入的数据)
String invCode = null;
if (row.getCell(2) == null) { //判断第7行的第3个单元格是否存在
DataImportMessage dm = new DataImportMessage(count, headRow
.getCell(2).getStringCellValue(), "DI_001"); //"DI_001" 对应数据库 "第{0}行的[{1}]不能为空"
dms.add(dm);
} else {
invCode = row.getCell(2).getStringCellValue();
if (StringUtils.isEmpty(invCode)) { //判断第7行的第3个单元格是否有值
DataImportMessage dm = new DataImportMessage(count,
headRow.getCell(2).getStringCellValue(),
"DI_001");
dms.add(dm);
} else {
if (inventoryDao.existValue("code", invCode)) { //判断是否存在此编码
DataImportMessage dm = new DataImportMessage(count,
headRow.getCell(2).getStringCellValue(),
"DI_002");
dms.add(dm);
}
}
}
String invPk = UuidUtility.compressedUuid();
String invClsCode = null;
String invClsPk = null;
if (row.getCell(1) == null) { //判断第7行的第2个单元格是否存在
DataImportMessage dm = new DataImportMessage(count, headRow
.getCell(1).getStringCellValue(), "DI_001");
dms.add(dm);
} else {
invClsCode = row.getCell(1).getStringCellValue();
if (StringUtils.isEmpty(invClsCode)) {
DataImportMessage dm = new DataImportMessage(count,
headRow.getCell(1).getStringCellValue(),
"DI_001");
dms.add(dm);
} else {
if (invClsPkCache.containsKey(invClsCode)) { //缓存,减少查询数据库次数
invClsPk = invClsPkCache.get(invClsCode);
} else {
InventoryClass inventoryClass = inventoryClassService
.getByCode(invClsCode);
if (inventoryClass != null) {
invClsPk = inventoryClass.getId();
invClsPkCache.put(invClsCode, invClsPk);
} else { //如果找不到物料分类
DataImportMessage dm = new DataImportMessage(
count, headRow.getCell(1)
.getStringCellValue(), "DI_003");
dms.add(dm);
}
}
}
}
String invName = null;
if (row.getCell(3) == null) {
DataImportMessage dm = new DataImportMessage(count, headRow //其他手输项只需要判断有没有单元格和值是否为空
.getCell(3).getStringCellValue(), "DI_001");
dms.add(dm);
} else {
invName = row.getCell(3).getStringCellValue();
if (StringUtils.isEmpty(invName)) {
DataImportMessage dm = new DataImportMessage(count,
headRow.getCell(3).getStringCellValue(),
"DI_001");
dms.add(dm);
}
}
String invStd = null;
if (row.getCell(4) == null) {
DataImportMessage dm = new DataImportMessage(count, headRow
.getCell(4).getStringCellValue(), "DI_001");
dms.add(dm);
} else {
invStd = row.getCell(4).getStringCellValue();
if (StringUtils.isEmpty(invStd)) {
DataImportMessage dm = new DataImportMessage(count,
headRow.getCell(4).getStringCellValue(),
"DI_001");
dms.add(dm);
}
}
String invWidth = null;
if (row.getCell(6) == null) {
DataImportMessage dm = new DataImportMessage(count, headRow
.getCell(6).getStringCellValue(), "DI_001");
dms.add(dm);
} else {
invWidth = row.getCell(6).getStringCellValue();
if (StringUtils.isEmpty(invWidth)) {
DataImportMessage dm = new DataImportMessage(count,
headRow.getCell(6).getStringCellValue(),
"DI_001");
dms.add(dm);
}
}
String invYarn = null;
if (row.getCell(7) == null) {
DataImportMessage dm = new DataImportMessage(count, headRow
.getCell(7).getStringCellValue(), "DI_001");
dms.add(dm);
} else {
invYarn = row.getCell(7).getStringCellValue();
if (StringUtils.isEmpty(invYarn)) {
DataImportMessage dm = new DataImportMessage(count,
headRow.getCell(7).getStringCellValue(),
"DI_001");
dms.add(dm);
}
}
String invElement = null;
if (row.getCell(8) == null) {
DataImportMessage dm = new DataImportMessage(count, headRow
.getCell(8).getStringCellValue(), "DI_001");
dms.add(dm);
} else {
invElement = row.getCell(8).getStringCellValue();
if (StringUtils.isEmpty(invElement)) {
DataImportMessage dm = new DataImportMessage(count,
headRow.getCell(8).getStringCellValue(),
"DI_001");
dms.add(dm);
}
}
String colorPk = null;
String colorCode = null;
if (row.getCell(5) == null) {
DataImportMessage dm = new DataImportMessage(count, headRow
.getCell(5).getStringCellValue(), "DI_001");
dms.add(dm);
} else {
colorCode = row.getCell(5).getStringCellValue();
if (StringUtils.isEmpty(colorCode)) {
DataImportMessage dm = new DataImportMessage(count,
headRow.getCell(5).getStringCellValue(),
"DI_001");
dms.add(dm);
} else {
if (colorPkCache.containsKey(colorCode)) {
colorPk = colorPkCache.get(colorCode);
} else {
Color color = colorService.getByCode(colorCode);
if (color != null) {
colorPk = color.getId();
colorPkCache.put(colorCode, colorPk);
} else {
DataImportMessage dm = new DataImportMessage(
count, headRow.getCell(5)
.getStringCellValue(), "DI_003");
dms.add(dm);
}
}
}
}
String unitPk = null;
String unitCode = null;
if (row.getCell(9) == null) {
DataImportMessage dm = new DataImportMessage(count, headRow
.getCell(9).getStringCellValue(), "DI_001");
dms.add(dm);
} else {
unitCode = row.getCell(9).getStringCellValue();
if (StringUtils.isEmpty(unitCode)) {
DataImportMessage dm = new DataImportMessage(count,
headRow.getCell(9).getStringCellValue(),
"DI_001");
dms.add(dm);
} else {
if (unitPkCache.containsKey(unitCode)) {
unitPk = unitPkCache.get(unitCode);
} else {
Aunit aunit = aunitService.getByCode(unitCode);
if (aunit != null) {
unitPk = aunit.getId();
unitPkCache.put(unitCode, unitPk);
} else {
DataImportMessage dm = new DataImportMessage(
count, headRow.getCell(9)
.getStringCellValue(), "DI_003");
dms.add(dm);
}
}
}
}
String vdef1 = null;
if (row.getCell(10) != null) {
vdef1 = row.getCell(10).getStringCellValue();
}
insertSQLSB.append(String.format(insertSQLFormat, invPk,
invClsPk, invCode, invName, "null", unitPk, invStd,
colorPk, invYarn, invElement, invWidth, vdef1)); //使用format函数替换每个%s
insertSQLSB.append("\n");
}
}
if (dms.size() == 0) {
inventoryDao.executeSql(insertSQLSB.toString(), null);
}
file.delete();
return dms;
}
对应模版的两个excel模版链接:http://pan.baidu.com/s/1gfFXRQN 密码:x3tp
我写的公共方法,因为公共要做到如的地方很多不可能每个地方分别写导入方法
定义模版 所有的导入模版都按照这个模版
public List<DataImportMessage> DataImport(byte[] fileBytes,
String fileName, String templateCode) {
// 读取Excel头部数据库信息存放到 ExcelDBInformation
ExcelDBInformation excelDBInformation = new ExcelDBInformation();
File file = FileUtils.getFileFromBytes(fileBytes, fileName);
ExcelUtil excelUtil = null;
try {
excelUtil = new ExcelUtil(file);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
throw new CustomException("COM_016");
} catch (IOException e) {
throw new CustomException("COM_016");
}catch (Exception e) {
throw new CustomException("COM_016");
}
//String insertSQL = "";
StringBuffer insertSQLSB = new StringBuffer();
String insertSQLFormat = "insert into %s(%s) values(%s);";
Iterator<Row> rows = excelUtil.getRows(0); // 得到excel的内容
// 1~11行固定的 数据库信息存放到 ExcelDBInformation
int skipRows = 16; // excel从第17行开始读取插入数据
int count = 0; // 标识读取到第几行
int rank = 0;// 统计excel共有几列(第一列不算)
List<DataImportMessage> dms = new ArrayList<DataImportMessage>(); // 数据格式化返回,显示返回信息
Row headRow = null; // 第九行,字段中文名
Map<Integer, String> map = null;
//在循环插入数据之前,先将头部固定的信息放到ExcelDBInformation里面,excel设置这块不可更改
while(rows.hasNext()){
count++;
Row row = rows.next();
if (count == 1) {
while (row.getCell(rank) != null) {
rank++;
}
--rank;
}
if (count == 2) {
if (row == null || row.getCell(1) == null || !templateCode // 判断是不是对应编码的模版
.equals(row.getCell(1).getStringCellValue())) {
file.delete();
throw new CustomException("COM_017");
}
}
if (count == 3) {//数据库名
excelDBInformation.setDataTable(row.getCell(1)
.getStringCellValue());
}
if(count == 4){//主键
excelDBInformation.setPkName(row.getCell(1).getStringCellValue());
}
if (count > 4 && count < 12) {
// 循环将信息放入 ExcelDBInformation的map中,没有信息放入null
if (count == 5) {
// 数据来源
map = new HashMap<Integer, String>();
for (int i = 1; i <= rank; i++) {
map.put(i, row.getCell(i).getStringCellValue());
}
excelDBInformation.setDataSources(map);
}
if (count == 6) {
// 数据来源字段
map = new HashMap<Integer, String>();
for (int i = 1; i <= rank; i++) {
map.put(i, row.getCell(i).getStringCellValue());
}
excelDBInformation.setDataSourcesField(map);
}
if (count == 7) {
// 字段名
map = new HashMap<Integer, String>();
for (int i = 1; i <= rank; i++) {
map.put(i, row.getCell(i).getStringCellValue());
}
excelDBInformation.setFieldName(map);
}
if (count == 8) {
// 批量导入数量
excelDBInformation.setInsertQuantity((int)row.getCell(1).getNumericCellValue());
}
if (count == 9) {
// 字段长度
Map<Integer, Integer> map1 = new HashMap<Integer, Integer>();
for (int i = 1; i <= rank; i++) {
//row.getCell(i).setCellType(1);
//map1.put(i,Integer.parseInt(row.getCell(i).getStringCellValue()));
map1.put(i,(int)row.getCell(i).getNumericCellValue());
}
excelDBInformation.setFieldSize(map1);
}
if (count == 10) {
// 是否可空
map = new HashMap<Integer, String>();
for (int i = 1; i <= rank; i++) {
map.put(i, row.getCell(i).getStringCellValue());
}
excelDBInformation.setIsNull(map);
}
if (count == 11) {
// 是否唯一
map = new HashMap<Integer, String>();
for (int i = 1; i <= rank; i++) {
map.put(i, row.getCell(i).getStringCellValue());
}
excelDBInformation.setIsUnique(map);
}
}
if (count == 12) {
headRow = row;
break;//跳出当前while循环
}
}
// 得到需要查询外表的数量,然后分别创建缓存,插入数据多的时候如果编码在缓存里面,就不需要再去查询数据库了。key:code/value:pk
// 根据“数据来源”有多少非空的 就创建几个,使用 “数据来源字段”+Cache 当cacheMap的key
Map<String, Map<String, String>> cacheMap =new HashMap<String, Map<String, String>>();
String dataSourcesField = null;
for (int i = 1; i <= rank; i++) {
dataSourcesField = excelDBInformation.getDataSourcesField().get(i);
if (!(dataSourcesField==null||"".equals(dataSourcesField))) {
String str = dataSourcesField+"Cache";
cacheMap.put(str,new HashMap<String, String>());
}
}
while (rows.hasNext()) {
count++;
Row row = rows.next();
if (count > skipRows) { // 从第17行开始读取要插入的数据
// 将“英文字段名”循环输出,组成一组要插入的字段
StringBuffer insertFieldNameSB = new StringBuffer();
//主键先加进去
insertFieldNameSB.append(excelDBInformation.getPkName());
insertFieldNameSB.append(",");
for (int i = 1; i <= rank; i++) {
insertFieldNameSB.append(excelDBInformation.getFieldName().get(i));
if (i != rank) {
insertFieldNameSB.append(",");
}
}
//循环读取要插入数据的值,并拼装成StringBuffer
StringBuffer valueSB = new StringBuffer();
//主键值先加进去
valueSB.append("\'");
valueSB.append(UuidUtility.compressedUuid());
valueSB.append("\'");
valueSB.append(",");
for (int i = 1; i <= rank; i++) {
String code = null;//单元格的编码
String pk=null;//单元格编码对应的pk
String dataSourcesField2 = null;//对应的缓存集合名字
Map<String, String> cacheMap2 = null;//对应的缓存集合
/*
https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Cell.html#CELL_TYPE_NUMERIC
static int CELL_TYPE_BLANK
Blank Cell type (3)
static int CELL_TYPE_BOOLEAN
Boolean Cell type (4)
static int CELL_TYPE_ERROR
Error Cell type (5)
static int CELL_TYPE_FORMULA
Formula Cell type (2)
static int CELL_TYPE_NUMERIC
Numeric Cell type (0)
static int CELL_TYPE_STRING
String Cell type (1)*/
//如果不是String类型统一设置为String
if(row.getCell(i).getCellType()==0||
row.getCell(i).getCellType()==2||
row.getCell(i).getCellType()==3||
row.getCell(i).getCellType()==4){
row.getCell(i).setCellType(1);
}
//头部的数据库信息,不用判断,直接在excel模版设置不可更改
if(row.getCell(i)==null){ //判断对应的单元格是否存在
DataImportMessage dm = new DataImportMessage(count, headRow
.getCell(2).getStringCellValue(), "DI_001");
dms.add(dm);
}else{
if("N".equals(excelDBInformation.getIsNull().get(i))){//该单元格标识不可空
if (StringUtils.isEmpty(row.getCell(i).getStringCellValue())) { //单元格没数据,提示信息
DataImportMessage dm = new DataImportMessage(count,
headRow.getCell(i).getStringCellValue(),
"DI_001");
dms.add(dm);
}else if (row.getCell(i).getStringCellValue().length()>excelDBInformation.getFieldSize().get(i)){
//判断单元格是否超出最大长度
DataImportMessage dm = new DataImportMessage(count,
headRow.getCell(i).getStringCellValue(),
"DI_005");
dms.add(dm);
}
}
//单元格 不为空,并且唯一字段 唯一
if("N".equals(excelDBInformation.getIsNull().get(i))&&"Y".equals(excelDBInformation.getIsUnique().get(i))){
//查询对应的 唯一字段 是否存在
String tempSQL = "select "+excelDBInformation.getFieldName().get(i)+" from "+excelDBInformation.getDataTable()+
" where "+excelDBInformation.getFieldName().get(i)+" = '"+row.getCell(i).getStringCellValue()+"'";
List findList=null;
findList = QueryHelper.findBySql(tempSQL, null);
if(!(findList==null||"".equals(findList)||findList.size()<1)){ //此字段的数值 已存在
DataImportMessage dm = new DataImportMessage(count,
headRow.getCell(i).getStringCellValue(),
"DI_002");
dms.add(dm);
}
}
valueSB.append("\'");
if (!(StringUtils.isEmpty(row.getCell(i).getStringCellValue()))) { //如果单元格是空的 就不需要查询对应的编码了
if (!(excelDBInformation.getDataSourcesField().get(i)==null||"".equals(excelDBInformation.getDataSourcesField().get(i)))) {
code=row.getCell(i).getStringCellValue();
dataSourcesField2 =excelDBInformation.getDataSourcesField().get(i)+"Cache";
cacheMap2 = cacheMap.get(dataSourcesField2);
if(cacheMap2.containsKey(code)){
pk = cacheMap2.get(code); //判断对应的缓存是否存在当前的编码
}else {
//如果字段为“pk_parent” 那么查询字段 使用当前数据表的主键,
//因为我们命名规范,引用当前类主键都是使用“pk_parent”,所以可以这样做
String pkName = excelDBInformation.getFieldName().get(i);
if("pk_parent".equals(pkName)){
pkName= excelDBInformation.getPkName();
}
String tempSQL = "select "+pkName+" from "+excelDBInformation.getDataSources().get(i)+
" where "+excelDBInformation.getDataSourcesField().get(i)+" = '"+code+"'";
List findList=null;
findList = QueryHelper.findBySql(tempSQL, null);
if(!(findList==null||"".equals(findList)||findList.size()<1)){
//放入缓存
Map<String, Object> mapReuslt = (Map<String, Object>) findList.get(0);
pk = (String) mapReuslt.get(pkName);
cacheMap2.put(code, pk);
} else { //找不到对应的编码
DataImportMessage dm = new DataImportMessage(
count, headRow.getCell(i)
.getStringCellValue(), "DI_003");
dms.add(dm);
}
}
valueSB.append(pk);
}else{
valueSB.append(row.getCell(i).getStringCellValue());
}
}
}
valueSB.append("\'");
if (i != rank) {
valueSB.append(",");
}
}
//将多条要插入的语句放在sb,然后一次性插入
insertSQLSB.append(String.format(insertSQLFormat, excelDBInformation.getDataTable(),
insertFieldNameSB.toString(), valueSB.toString())); //使用format函数替换每个%s
insertSQLSB.append("\n");
//批量插入
if (dms.size() == 0) {
if((count-skipRows)%excelDBInformation.getInsertQuantity()==0){
QueryHelper.executeSql(insertSQLSB.toString(), null); //一次性插入多条
insertSQLSB.setLength(0); //清空SB
}
}
}
}
if (dms.size() == 0) {
if(insertSQLSB.length()>0){
QueryHelper.executeSql(insertSQLSB.toString(), null); //一次性插入多条
}
}
file.delete();
return dms;
}
ExcelDBInformation
package cn.com.aperfect.auap.common.util;
import java.util.HashMap;
import java.util.Map;
/**
* Excel公共模版头部的数据库信息
*
* @author szd
*
*/
public class ExcelDBInformation {
private String pkName;// 主键
private Integer insertQuantity;//批量导入数量
private String dataTable;// 数据表
private Map<Integer, String> dataSources = new HashMap<Integer, String>();// 数据来源
private Map<Integer, String> dataSourcesField = new HashMap<Integer, String>();// 数据来源字段
private Map<Integer, String> fieldName = new HashMap<Integer, String>();// 字段名
private Map<Integer, Integer> fieldSize = new HashMap<Integer, Integer>();// 字段长度
private Map<Integer, String> isNull = new HashMap<Integer, String>();// 是否可空
private Map<Integer, String> isUnique = new HashMap<Integer, String>(); //是否唯一
public String getPkName() {
return pkName;
}
public void setPkName(String pkName) {
this.pkName = pkName;
}
public String getDataTable() {
return dataTable;
}
public void setDataTable(String dataTable) {
this.dataTable = dataTable;
}
public Map<Integer, String> getDataSources() {
return dataSources;
}
public void setDataSources(Map<Integer, String> dataSources) {
this.dataSources = dataSources;
}
public Map<Integer, String> getDataSourcesField() {
return dataSourcesField;
}
public void setDataSourcesField(Map<Integer, String> dataSourcesField) {
this.dataSourcesField = dataSourcesField;
}
public Map<Integer, String> getFieldName() {
return fieldName;
}
public void setFieldName(Map<Integer, String> fieldName) {
this.fieldName = fieldName;
}
public Map<Integer, String> getIsNull() {
return isNull;
}
public void setIsNull(Map<Integer, String> isNull) {
this.isNull = isNull;
}
public Integer getInsertQuantity() {
return insertQuantity;
}
public void setInsertQuantity(Integer insertQuantity) {
this.insertQuantity = insertQuantity;
}
public Map<Integer, String> getIsUnique() {
return isUnique;
}
public void setIsUnique(Map<Integer, String> isUnique) {
this.isUnique = isUnique;
}
public Map<Integer, Integer> getFieldSize() {
return fieldSize;
}
public void setFieldSize(Map<Integer, Integer> fieldSize) {
this.fieldSize = fieldSize;
}
}
FileUtils
package cn.com.aperfect.auap.external.util;
import java.io.BufferedOutputStream;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
public class FileUtils {
/**
* 判断文件夹是否存在
*
* @param pathname
* 文件夹路径
* @param mk
* 如果文件夹不存在是否创建文件夹
* @return 文件夹是否存在
*/
public static boolean directoryExists(String pathname, boolean mk) {
File file = new File(pathname);
if (file.exists() && file.isDirectory()) {
if (mk) {
file.mkdir();
}
return true;
}
return false;
}
/**
* 判断文件是否存在
*
* @param pathname
* 文件路径
* @param mk
* 如果文件不存在是否创建文件
* @return 文件是否存在
*/
public static boolean fileExists(String pathname, boolean mk) {
File file = new File(pathname);
if (file.exists() && file.isFile()) {
if (mk) {
try {
file.createNewFile();
} catch (IOException e) {
e.printStackTrace();
}
}
return true;
}
return false;
}
/**
* 创建文件,如果目录不存在,也同时创建
*
* @param pathname
* @return
*/
public static boolean createFile(String pathname) {
// 获取文件路径的目录
String dir = null;
if (pathname.indexOf(".") != -1) {
int index = pathname.lastIndexOf("\\");
if (index == -1) {
index = pathname.lastIndexOf("/");
}
dir = pathname.substring(0, index);
}
File file = new File(dir);
if (!file.exists() && !file.isFile()) {
// 目录不存在,创建目录
file.mkdirs();
}
file = null;
file = new File(pathname);
if (!file.exists() && !file.isFile()) {
// 文件不存在,创建文件
try {
return file.createNewFile();
} catch (IOException e) {
e.printStackTrace();
}
}
return false;
}
/**
*
* @param pathname
* 文件路径
* @return 文件全部内容
*/
public static String readToString(String pathname) {
String encoding = "UTF-8";
File file = new File(pathname);
Long filelength = file.length();
byte[] filecontent = new byte[filelength.intValue()];
try {
FileInputStream in = new FileInputStream(file);
in.read(filecontent);
in.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
try {
return new String(filecontent, encoding);
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
return null;
}
}
/**
* 获取文件后缀名
*
* @param file
* 文件对象
* @return String 文件后缀名
*/
public static String getFileExtension(File file) {
String fileName = file.getName();
String extension = fileName.substring(fileName.lastIndexOf(".") + 1);
return extension;
}
public static List<File> getListFile(String path, String fileNameRegex) {
return getListFile(path, null, fileNameRegex);
}
/**
* 递归文件夹下的所有子文件
*
* @param path
* 文件夹路径
* @param files
* 文件列表
* @param fileNameRegex
* 过滤文件名(正则表达式)
* @return List<File> 文件列表
*/
public static List<File> getListFile(String path, List<File> files,
String fileNameRegex) {
File file = new File(path);
File[] array = file.listFiles();
if (files == null) {
files = new ArrayList<File>();
}
for (int i = 0; i < array.length; i++) {
if (array[i].isFile()) {
Pattern pattern = Pattern.compile(fileNameRegex);
Matcher matcher = pattern.matcher(array[i].getName());
if (matcher.find()) {
files.add(array[i]);
}
} else if (array[i].isDirectory()) {
getListFile(array[i].getPath(), files, fileNameRegex);
}
}
return files;
}
public static boolean deleteFile(String path) {
File file = new File(path);
return file.delete();
}
/**
* 删除dir目录下的所有文件
*
* @param dir
* @return
*/
public static Integer deleteAllFile(String dir) {
// 获取目录下的所有文件
File file = new File(dir);
File[] array = file.listFiles();
Integer deleteNum = 0; // 成功删除的文件数量
boolean isDeleted = false;
if (array == null || array.length == 0) {
return 0;
}
// 删除文件
for (File one : array) {
isDeleted = one.delete();
if (isDeleted) {
deleteNum++;
}
}
// System.out.println("deleteNum = " + deleteNum);
return deleteNum;
}
public static void writeFile(String filepath, String context) {
File writefile = new File(filepath);
try {
if (!writefile.exists()) {
writefile.createNewFile();
}
BufferedWriter out = new BufferedWriter(new FileWriter(writefile));
out.write(context);
out.flush();
out.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void createFile(String filePathAndName, String fileContent) {
try {
String filePath = filePathAndName;
filePath = filePath.toString();
File myFilePath = new File(filePath);
if (!myFilePath.exists()) {
myFilePath.createNewFile();
}
FileWriter resultFile = new FileWriter(myFilePath);
PrintWriter myFile = new PrintWriter(resultFile);
String strContent = fileContent;
myFile.println(strContent);
myFile.close();
resultFile.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void createFile(String dir, String fileName,
String fileContent, String encoding) {
try {
File dirFile = new File(dir);
if (!dirFile.exists()) {
dirFile.mkdirs();
}
String fullName = dir + fileName;
File myFilePath = new File(fullName);
if (!myFilePath.exists()) {
myFilePath.createNewFile();
}
PrintWriter myFile = new PrintWriter(myFilePath, encoding);
String strContent = fileContent;
myFile.println(strContent);
myFile.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 将2进制数据转换为文件
*
* @param b
* @param outputFile
* @return
*/
public static File getFileFromBytes(byte[] b, String outputFile) {
BufferedOutputStream stream = null;
File file = null;
try {
file = new File(outputFile);
FileOutputStream fstream = new FileOutputStream(file);
stream = new BufferedOutputStream(fstream);
stream.write(b);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (stream != null) {
try {
stream.close();
} catch (IOException e1) {
e1.printStackTrace();
}
}
}
return file;
}
/**
* 获取文件扩展名
*
* @param fileName
* @return
*/
public static String getFileType(String fileName) {
return fileName.substring(fileName.lastIndexOf('.') + 1,
fileName.length());
}
}
ExcelUtil
package cn.com.aperfect.auap.common.util;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
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;
/**
* Excel工具类
*
* @author csh
*
*/
public class ExcelUtil {
public Workbook workbook;
public ExcelUtil(String fileName) throws FileNotFoundException {
if (!FileUtilty.fileExists(fileName, false)) {
throw new FileNotFoundException("Not Found " + fileName);
}
File file = new File(fileName);
try {
workbook = WorkbookFactory.create(file);
} catch (InvalidFormatException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public ExcelUtil(File file) throws InvalidFormatException, IOException {
workbook = WorkbookFactory.create(file);
}
public ExcelUtil(InputStream inputStream) throws FileNotFoundException {
try {
workbook = WorkbookFactory.create(inputStream);
} catch (InvalidFormatException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public Sheet getSheet(int sheetIndex) {
Sheet sheet = workbook.getSheetAt(sheetIndex);
return sheet;
}
public Sheet getSheet(String sheetName) {
Sheet sheet = workbook.getSheet(sheetName);
return sheet;
}
public Iterator<Row> getRows(int sheetIndex) {
return this.getSheet(sheetIndex).rowIterator();
}
public Iterator<Row> getRows(String sheetName) {
return this.getSheet(sheetName).rowIterator();
}
}