具体忘记是从那里转的核心内容了,业务逻辑我自己!!
/*
文件夹路径,读取文件夹中的文件
*/
public void readFile(String filepath) throws Exception {
// filepath = "D:\\caidan\\DH000145.xlsx";
// 存储错误文件信息
StringBuffer fileNames = new StringBuffer();
File file = new File(filepath);
if (!file.isDirectory()) {
String readfile = "";
System.out.println("文件");
System.out.println("path=" + file.getPath());
System.out.println("absolutepath=" + file.getAbsolutePath());
System.out.println("name=" + file.getName());
SmartMenuElement menuElement = null;
List<BdUnit> units = (List<BdUnit>) this.getArrangeDAO().executeQuery("from BdUnit where id=65 ");
List<SmartMenu> sm = null;
// 读取到相应的文件
menuElement = new SmartMenuElement();
String fileName = file.getPath();
InputStream is = null;
// p判断文件名称(商品编码)
sm = (List<SmartMenu>) this.getArrangeDAO().executeQuery(
"from SmartMenu where no='" + file.getName().substring(0, file.getName().indexOf(".")) + "'");
StringBuffer sb = new StringBuffer(" ");
sb.append("INSERT INTO smart_menu_element (HEADER_ID,OPERATION_ELEMENTS,UNIT_ID,VALUE) VALUES");
if (sm != null && sm.size() > 0) {
// 设置菜品对象
// menuElement.setHeader(sm.get(0));
// 用于标识读取到第一个元素,一共43个元素
int count = 1;
List<BdUnit> unit = null;
try {
/** 验证文件是否合法 */
if (!validateExcel(fileName)) {
System.out.println(errorInfo);
}
/** 判断文件的类型,是2003还是2007 */
boolean isExcel2003 = true;
if (WDWUtil.isExcel2007(fileName)) {
isExcel2003 = false;
}
/** 调用本类提供的根据流读取的方法 */
File files = new File(fileName);
is = new FileInputStream(files);
// dataLst = read(is, isExcel2003);
try {
/** 根据版本选择创建Workbook的方式 */
Workbook wb = isExcel2003 ? new HSSFWorkbook(is) : new XSSFWorkbook(is);
// dataLst = read(wb);
// List<List<String>> dataLst = new
// ArrayList<List<String>>();
/** 得到第一个shell */
Sheet sheet = wb.getSheetAt(0);
/** 得到Excel的行数 */
this.totalRows = sheet.getPhysicalNumberOfRows();
/** 得到Excel的列数 */
// if (this.totalRows >= 1 && sheet.getRow(0) !=
// null) {
// this.totalCells =
// sheet.getRow(0).getPhysicalNumberOfCells();
// }
/** 循环Excel的行 */
for (int r = 6; r < this.totalRows; r++) {
Row row = sheet.getRow(r);
if (row == null) {
continue;
}
if (count == 1) {
Cell cell = row.getCell(2);
if (cell != null && cell.getStringCellValue() == null) {
cell = row.getCell(3);
if (cell != null && cell.getStringCellValue() == null) {
cell = row.getCell(4);
}
}
// 列是从C列开始,行是从6行到15行开始判断
if (cell != null && cell.getStringCellValue() != null && cell.getStringCellValue().length() > 0) {
System.out.println(cell.getStringCellValue());
if ("营养素摄入量分析".equals(cell.getStringCellValue().trim())) {
sb.append("(" + sm.get(0).getId() + ",");
// 将行向下移三行则读取第一个元素值,同时向后移动1列则读取单位,再向后移动1列则读取数量
r = r + 3;
row = sheet.getRow(r);
if (row == null) {
System.out.println("文件" + file.getName());
continue;
}
cell = row.getCell(2);
Cell elementName = cell;
// 元素名称
if (cell != null && cell.getStringCellValue() != null && cell.getStringCellValue().length() > 0) {
System.out.println(cell.getStringCellValue());
// menuElement.setOperationElements(cell.getStringCellValue().trim());
sb.append("'" + cell.getStringCellValue() + "',");
} else {
fileNames.append(file.getName() + ":元素名称不存在 ;");
sb.append("NULL,");
}
// 单位
cell = row.getCell(8);
if (cell != null && cell.getStringCellValue() != null && cell.getStringCellValue().length() > 0) {
System.out.println(cell.getStringCellValue());
unit = (List<BdUnit>) this.getArrangeDAO().executeQuery(
"from BdUnit where name='" + cell.getStringCellValue().trim() + "'");
if (unit != null && unit.size() > 0) {
// menuElement.setUnit(unit.get(0));
sb.append(unit.get(0).getId() + ",");
} else if (elementName != null && "胆固醇".equals(elementName.getStringCellValue().trim())) {
// menuElement.setUnit(units.get(0));
sb.append(units.get(0).getId() + ",");
} else {
fileNames.append(file.getName() + ":单位不存在 ;");
sb.append("NULL,");
}
} else if (elementName != null && "胆固醇".equals(elementName.getStringCellValue().trim())) {
// menuElement.setUnit(units.get(0));
sb.append(units.get(0).getId() + ",");
} else {
fileNames.append(file.getName() + ":单位不存在 ;");
sb.append("NULL,");
}
// 数量
cell = row.getCell(11);
if (cell != null) {
cell.setCellType(Cell.CELL_TYPE_STRING);
if (cell.getStringCellValue() != null && cell.getStringCellValue().length() > 0) {
System.out.println("文件" + file.getName() + ":" + cell.getStringCellValue());
// menuElement.setValue(Double.parseDouble(cell.getStringCellValue().trim()));
sb.append(cell.getStringCellValue() + "),");
} else {
cell = row.getCell(12);
if (cell != null) {
cell.setCellType(Cell.CELL_TYPE_STRING);
if (cell.getStringCellValue() != null && cell.getStringCellValue().length() > 0) {
System.out.println("文件" + file.getName() + ":" + cell.getStringCellValue());
// menuElement.setValue(Double.parseDouble(cell.getStringCellValue().trim()));
sb.append(cell.getStringCellValue() + "),");
} else {
fileNames.append(file.getName() + ":摄入量不存在 ;");
// break;
sb.append("NULL),");
}
} else {
fileNames.append(file.getName() + ":摄入量不存在 ;");
// break;
sb.append("NULL),");
}
}
} else {
cell = row.getCell(12);
if (cell != null) {
cell.setCellType(Cell.CELL_TYPE_STRING);
if (cell.getStringCellValue() != null && cell.getStringCellValue().length() > 0) {
System.out.println("文件" + file.getName() + ":" + cell.getStringCellValue());
// menuElement.setValue(Double.parseDouble(cell.getStringCellValue().trim()));
sb.append(cell.getStringCellValue() + "),");
} else {
fileNames.append(file.getName() + ":摄入量不存在 ;");
// break;
sb.append("NULL),");
}
} else {
fileNames.append(file.getName() + ":摄入量不存在 ;");
// break;
sb.append("NULL),");
}
}
count++;
// commonComponentsService.save(menuElement);
// menuArrangeService.executeSql(sb.toString().substring(0,sb.length()-1));
}
}
} else {
sb.append("(" + sm.get(0).getId() + ",");
Cell cell = row.getCell(2);
Cell elementName = cell;
// 元素名称
if (cell != null && cell.getStringCellValue() != null && cell.getStringCellValue().length() > 0) {
System.out.println(cell.getStringCellValue());
// menuElement.setOperationElements(cell.getStringCellValue().trim());
sb.append("'" + cell.getStringCellValue() + "',");
} else {
fileNames.append(file.getName() + ":元素名称不存在 ;");
sb.append("NULL,");
}
// 单位
cell = row.getCell(8);
if (cell != null && cell.getStringCellValue() != null && cell.getStringCellValue().length() > 0) {
System.out.println(cell.getStringCellValue());
unit = (List<BdUnit>) this.getArrangeDAO()
.executeQuery("from BdUnit where name='" + cell.getStringCellValue().trim() + "'");
if (unit != null && unit.size() > 0) {
// menuElement.setUnit(unit.get(0));
sb.append(unit.get(0).getId() + ",");
} else if (elementName != null && "胆固醇".equals(elementName.getStringCellValue().trim())) {
// menuElement.setUnit(units.get(0));
sb.append(units.get(0).getId() + ",");
} else {
fileNames.append(file.getName() + ":单位不存在 ;");
// break;
sb.append("NULL,");
}
} else if (elementName != null && "胆固醇".equals(elementName.getStringCellValue().trim())) {
// menuElement.setUnit(units.get(0));
sb.append(units.get(0).getId() + ",");
} else {
fileNames.append(file.getName() + ":单位不存在 ;");
// break;
sb.append("NULL,");
}
// 数量
cell = row.getCell(11);
if (cell != null) {
cell.setCellType(Cell.CELL_TYPE_STRING);
if (cell.getStringCellValue() != null && cell.getStringCellValue().length() > 0) {
System.out.println("文件" + file.getName() + ":" + cell.getStringCellValue());
// menuElement.setValue(Double.parseDouble(cell.getStringCellValue().trim()));
sb.append(cell.getStringCellValue() + "),");
} else {
cell = row.getCell(12);
if (cell != null) {
cell.setCellType(Cell.CELL_TYPE_STRING);
if (cell.getStringCellValue() != null && cell.getStringCellValue().length() > 0) {
System.out.println("文件" + file.getName() + ":" + cell.getStringCellValue());
// menuElement.setValue(Double.parseDouble(cell.getStringCellValue().trim()));
sb.append(cell.getStringCellValue() + "),");
} else {
fileNames.append(file.getName() + ":摄入量不存在 ;");
// break;
sb.append("NULL),");
}
} else {
fileNames.append(file.getName() + ":摄入量不存在 ;");
// break;
sb.append("NULL),");
}
}
} else {
cell = row.getCell(12);
if (cell != null) {
cell.setCellType(Cell.CELL_TYPE_STRING);
if (cell.getStringCellValue() != null && cell.getStringCellValue().length() > 0) {
System.out.println("文件" + file.getName() + ":" + cell.getStringCellValue());
// menuElement.setValue(Double.parseDouble(cell.getStringCellValue().trim()));
sb.append(cell.getStringCellValue() + "),");
} else {
fileNames.append(file.getName() + ":摄入量不存在 ;");
// break;
sb.append("NULL),");
}
} else {
fileNames.append(file.getName() + ":摄入量不存在 ;");
// break;
sb.append("NULL),");
}
}
// count++;
// commonComponentsService.save(menuElement);
}
// List<String> rowLst = new
// ArrayList<String>();
/** 循环Excel的列 */
// for (short c = 0; c <
// this.getTotalCells(); c++) {
// Cell cell = row.getCell(c);
// String cellValue = "";
// if (cell == null) {
// rowLst.add(cellValue);
// continue;
// }
/** 处理Excel的字符串 */
// cellValue = cell.getStringCellValue();
// rowLst.add(cellValue);
// }
/** 保存第r行的第c列 */
// dataLst.add(rowLst);
}
} catch (IOException e) {
e.printStackTrace();
}
is.close();
} catch (Exception ex) {
ex.printStackTrace();
} finally {
if (is != null) {
try {
is.close();
} catch (IOException e) {
is = null;
e.printStackTrace();
}
}
}
} else {
fileNames.append(file.getName() + ":文件找不到对应的商品 ;");
}
if (!" INSERT INTO smart_menu_element (HEADER_ID,OPERATION_ELEMENTS,UNIT_ID,VALUE) VALUES".equalsIgnoreCase(sb.toString())) {
this.getArrangeDAO().executeSQLUpdate(sb.toString().substring(0, sb.toString().length() - 1));
}
// read(readfile.getPath());
} else if (file.isDirectory()) {
String[] filelist = file.list();
SmartMenuElement menuElement = null;
List<BdUnit> units = (List<BdUnit>) this.getArrangeDAO().executeQuery("from BdUnit where id=65 ");
List<SmartMenu> sm = null;
for (int i = 0; i < filelist.length; i++) {
File readfile = new File(filepath + "\\" + filelist[i]);
if (!readfile.isDirectory()) {
// 读取到相应的文件
menuElement = new SmartMenuElement();
String fileName = readfile.getPath();
InputStream is = null;
// p判断文件名称(商品编码)
sm = (List<SmartMenu>) this.getArrangeDAO().executeQuery(
"from SmartMenu where no='" + readfile.getName().substring(0, readfile.getName().indexOf(".")) + "'");
// sm = (List<SmartMenu>) this.getArrangeDAO().executeQuery(
// "from SmartMenu where name='" +
// readfile.getName().substring(0,
// readfile.getName().indexOf(".")) + "'");
StringBuffer sb = new StringBuffer(" ");
sb.append("INSERT INTO smart_menu_element (HEADER_ID,OPERATION_ELEMENTS,UNIT_ID,VALUE) VALUES");
if (sm != null && sm.size() > 0) {
// 设置菜品对象
// menuElement.setHeader(sm.get(0));
// 用于标识读取到第一个元素,一共43个元素
int count = 1;
List<BdUnit> unit = null;
try {
/** 验证文件是否合法 */
if (!validateExcel(fileName)) {
System.out.println(errorInfo);
}
/** 判断文件的类型,是2003还是2007 */
boolean isExcel2003 = true;
if (WDWUtil.isExcel2007(fileName)) {
isExcel2003 = false;
}
/** 调用本类提供的根据流读取的方法 */
File files = new File(fileName);
is = new FileInputStream(files);
// dataLst = read(is, isExcel2003);
try {
/** 根据版本选择创建Workbook的方式 */
Workbook wb = isExcel2003 ? new HSSFWorkbook(is) : new XSSFWorkbook(is);
// dataLst = read(wb);
// List<List<String>> dataLst = new
// ArrayList<List<String>>();
/** 得到第一个shell */
Sheet sheet = wb.getSheetAt(0);
/** 得到Excel的行数 */
this.totalRows = sheet.getPhysicalNumberOfRows();
/** 得到Excel的列数 */
// if (this.totalRows >= 1 && sheet.getRow(0) !=
// null) {
// this.totalCells =
// sheet.getRow(0).getPhysicalNumberOfCells();
// }
/** 循环Excel的行 */
for (int r = 6; r < this.totalRows; r++) {
Row row = sheet.getRow(r);
if (row == null) {
continue;
}
if (count == 1) {
Cell cell = row.getCell(2);
if (cell != null && cell.getStringCellValue() == null) {
cell = row.getCell(3);
if (cell != null && cell.getStringCellValue() == null) {
cell = row.getCell(4);
}
}
// 列是从C列开始,行是从6行到15行开始判断
if (cell != null && cell.getStringCellValue() != null && cell.getStringCellValue().length() > 0) {
System.out.println(cell.getStringCellValue());
if ("营养素摄入量分析".equals(cell.getStringCellValue().trim())) {
sb.append("(" + sm.get(0).getId() + ",");
// 将行向下移三行则读取第一个元素值,同时向后移动1列则读取单位,再向后移动1列则读取数量
r = r + 3;
row = sheet.getRow(r);
if (row == null) {
System.out.println("文件" + readfile.getName());
continue;
}
cell = row.getCell(2);
Cell elementName = cell;
// 元素名称
if (cell != null && cell.getStringCellValue() != null && cell.getStringCellValue().length() > 0) {
System.out.println(cell.getStringCellValue());
// menuElement.setOperationElements(cell.getStringCellValue().trim());
sb.append("'" + cell.getStringCellValue() + "',");
} else {
fileNames.append(readfile.getName() + ":元素名称不存在 ;");
sb.append("NULL,");
}
// 单位
cell = row.getCell(8);
if (cell != null && cell.getStringCellValue() != null && cell.getStringCellValue().length() > 0) {
System.out.println(cell.getStringCellValue());
unit = (List<BdUnit>) this.getArrangeDAO().executeQuery(
"from BdUnit where name='" + cell.getStringCellValue().trim() + "'");
if (unit != null && unit.size() > 0) {
// menuElement.setUnit(unit.get(0));
sb.append(unit.get(0).getId() + ",");
} else if (elementName != null && "胆固醇".equals(elementName.getStringCellValue().trim())) {
// menuElement.setUnit(units.get(0));
sb.append(units.get(0).getId() + ",");
} else {
fileNames.append(readfile.getName() + ":单位不存在 ;");
sb.append("NULL,");
}
} else if (elementName != null && "胆固醇".equals(elementName.getStringCellValue().trim())) {
// menuElement.setUnit(units.get(0));
sb.append(units.get(0).getId() + ",");
} else {
fileNames.append(readfile.getName() + ":单位不存在 ;");
sb.append("NULL,");
}
// 数量
cell = row.getCell(11);
if (cell != null) {
cell.setCellType(Cell.CELL_TYPE_STRING);
if (cell.getStringCellValue() != null && cell.getStringCellValue().length() > 0) {
System.out.println("文件" + readfile.getName() + ":" + cell.getStringCellValue());
// menuElement.setValue(Double.parseDouble(cell.getStringCellValue().trim()));
sb.append(cell.getStringCellValue() + "),");
} else {
cell = row.getCell(12);
if (cell != null) {
cell.setCellType(Cell.CELL_TYPE_STRING);
if (cell.getStringCellValue() != null && cell.getStringCellValue().length() > 0) {
System.out.println("文件" + readfile.getName() + ":" + cell.getStringCellValue());
// menuElement.setValue(Double.parseDouble(cell.getStringCellValue().trim()));
sb.append(cell.getStringCellValue() + "),");
} else {
fileNames.append(readfile.getName() + ":摄入量不存在 ;");
// break;
sb.append("NULL),");
}
} else {
fileNames.append(readfile.getName() + ":摄入量不存在 ;");
// break;
sb.append("NULL),");
}
}
} else {
cell = row.getCell(12);
if (cell != null) {
cell.setCellType(Cell.CELL_TYPE_STRING);
if (cell.getStringCellValue() != null && cell.getStringCellValue().length() > 0) {
System.out.println("文件" + readfile.getName() + ":" + cell.getStringCellValue());
// menuElement.setValue(Double.parseDouble(cell.getStringCellValue().trim()));
sb.append(cell.getStringCellValue() + "),");
} else {
fileNames.append(readfile.getName() + ":摄入量不存在 ;");
// break;
sb.append("NULL),");
}
} else {
fileNames.append(readfile.getName() + ":摄入量不存在 ;");
// break;
sb.append("NULL),");
}
}
count++;
// commonComponentsService.save(menuElement);
// menuArrangeService.executeSql(sb.toString().substring(0,sb.length()-1));
}
}
} else {
sb.append("(" + sm.get(0).getId() + ",");
Cell cell = row.getCell(2);
Cell elementName = cell;
// 元素名称
if (cell != null && cell.getStringCellValue() != null && cell.getStringCellValue().length() > 0) {
System.out.println(cell.getStringCellValue());
// menuElement.setOperationElements(cell.getStringCellValue().trim());
sb.append("'" + cell.getStringCellValue() + "',");
} else {
fileNames.append(readfile.getName() + ":元素名称不存在 ;");
sb.append("NULL,");
}
// 单位
cell = row.getCell(8);
if (cell != null && cell.getStringCellValue() != null && cell.getStringCellValue().length() > 0) {
System.out.println(cell.getStringCellValue());
unit = (List<BdUnit>) this.getArrangeDAO().executeQuery(
"from BdUnit where name='" + cell.getStringCellValue().trim() + "'");
if (unit != null && unit.size() > 0) {
// menuElement.setUnit(unit.get(0));
sb.append(unit.get(0).getId() + ",");
} else if (elementName != null && "胆固醇".equals(elementName.getStringCellValue().trim())) {
// menuElement.setUnit(units.get(0));
sb.append(units.get(0).getId() + ",");
} else {
fileNames.append(readfile.getName() + ":单位不存在 ;");
// break;
sb.append("NULL,");
}
} else if (elementName != null && "胆固醇".equals(elementName.getStringCellValue().trim())) {
// menuElement.setUnit(units.get(0));
sb.append(units.get(0).getId() + ",");
} else {
fileNames.append(readfile.getName() + ":单位不存在 ;");
// break;
sb.append("NULL,");
}
// 数量
cell = row.getCell(11);
if (cell != null) {
cell.setCellType(Cell.CELL_TYPE_STRING);
if (cell.getStringCellValue() != null && cell.getStringCellValue().length() > 0) {
System.out.println("文件" + readfile.getName() + ":" + cell.getStringCellValue());
// menuElement.setValue(Double.parseDouble(cell.getStringCellValue().trim()));
sb.append(cell.getStringCellValue() + "),");
} else {
cell = row.getCell(12);
if (cell != null) {
cell.setCellType(Cell.CELL_TYPE_STRING);
if (cell.getStringCellValue() != null && cell.getStringCellValue().length() > 0) {
System.out.println("文件" + readfile.getName() + ":" + cell.getStringCellValue());
// menuElement.setValue(Double.parseDouble(cell.getStringCellValue().trim()));
sb.append(cell.getStringCellValue() + "),");
} else {
fileNames.append(readfile.getName() + ":摄入量不存在 ;");
// break;
sb.append("NULL),");
}
} else {
fileNames.append(readfile.getName() + ":摄入量不存在 ;");
// break;
sb.append("NULL),");
}
}
} else {
cell = row.getCell(12);
if (cell != null) {
cell.setCellType(Cell.CELL_TYPE_STRING);
if (cell.getStringCellValue() != null && cell.getStringCellValue().length() > 0) {
System.out.println("文件" + readfile.getName() + ":" + cell.getStringCellValue());
// menuElement.setValue(Double.parseDouble(cell.getStringCellValue().trim()));
sb.append(cell.getStringCellValue() + "),");
} else {
fileNames.append(readfile.getName() + ":摄入量不存在 ;");
// break;
sb.append("NULL),");
}
} else {
fileNames.append(readfile.getName() + ":摄入量不存在 ;");
// break;
sb.append("NULL),");
}
}
// count++;
// commonComponentsService.save(menuElement);
}
// List<String> rowLst = new
// ArrayList<String>();
/** 循环Excel的列 */
// for (short c = 0; c <
// this.getTotalCells(); c++) {
// Cell cell = row.getCell(c);
// String cellValue = "";
// if (cell == null) {
// rowLst.add(cellValue);
// continue;
// }
/** 处理Excel的字符串 */
// cellValue = cell.getStringCellValue();
// rowLst.add(cellValue);
// }
/** 保存第r行的第c列 */
// dataLst.add(rowLst);
}
} catch (IOException e) {
e.printStackTrace();
}
is.close();
} catch (Exception ex) {
ex.printStackTrace();
} finally {
if (is != null) {
try {
is.close();
} catch (IOException e) {
is = null;
e.printStackTrace();
}
}
}
} else {
fileNames.append(readfile.getName() + ":文件找不到对应的商品 ;");
}
if (!" INSERT INTO smart_menu_element (HEADER_ID,OPERATION_ELEMENTS,UNIT_ID,VALUE) VALUES".equalsIgnoreCase(sb.toString())) {
this.getArrangeDAO().executeSQLUpdate(sb.toString().substring(0, sb.toString().length() - 1));
}
// read(readfile.getPath());
} else if (readfile.isDirectory()) {
readFile(filepath + "\\" + filelist[i]);
}
}
System.out.println(fileNames);
}
}
/** 总行数 */
private int totalRows = 0;
/** 总列数 */
private int totalCells = 0;
/** 错误信息 */
private String errorInfo;
/**
*
* @描述:得到总行数
*
* @作者:wdw
*
* @时间:2011-8-9 下午04:27:34
*
* @参数:@return
*
* @返回值:int
*/
public int getTotalRows() {
return totalRows;
}
/**
*
* @描述:得到总列数
*
* @作者:wdw
*
* @时间:2011-8-9 下午04:27:45
*
* @参数:@return
*
* @返回值:int
*/
public int getTotalCells() {
return totalCells;
}
/**
*
* @描述:得到错误信息
*
* @作者:wdw
*
* @时间:2011-8-9 下午04:28:17
*
* @参数:@return
*
* @返回值:String
*/
public String getErrorInfo() {
return errorInfo;
}
/**
*
* @描述:验证excel文件
*
* @作者:wdw
*
* @时间:2011-8-9 下午04:06:47
*
* @参数:@param fileName
*
* @参数:@return
*
* @返回值:boolean
*/
public boolean validateExcel(String fileName) {
/** 检查文件名是否为空或者是否是Excel格式的文件 */
if (fileName == null || !(WDWUtil.isExcel2003(fileName) || WDWUtil.isExcel2007(fileName))) {
errorInfo = "文件名不是excel格式";
return false;
}
/** 检查文件是否存在 */
File file = new File(fileName);
if (file == null || !file.exists()) {
errorInfo = "文件不存在";
return false;
}
return true;
}
/**
*
* @描述:根据文件名读取excel文件
*
* @作者:wdw
*
* @时间:2011-8-9 下午03:17:45
*
* @参数:@param fileName
*
* @参数:@return
*
* @返回值:List
*/
public List<List<String>> read(String fileName) {
List<List<String>> dataLst = new ArrayList<List<String>>();
InputStream is = null;
try {
/** 验证文件是否合法 */
if (!validateExcel(fileName)) {
System.out.println(errorInfo);
return null;
}
/** 判断文件的类型,是2003还是2007 */
boolean isExcel2003 = true;
if (WDWUtil.isExcel2007(fileName)) {
isExcel2003 = false;
}
/** 调用本类提供的根据流读取的方法 */
File file = new File(fileName);
is = new FileInputStream(file);
dataLst = read(is, isExcel2003);
is.close();
} catch (Exception ex) {
ex.printStackTrace();
} finally {
if (is != null) {
try {
is.close();
} catch (IOException e) {
is = null;
e.printStackTrace();
}
}
}
/** 返回最后读取的结果 */
return dataLst;
}
/**
*
* @描述:根据流读取Excel文件
*
* @作者:wdw
*
* @时间:2011-8-9 下午04:12:41
*
* @参数:@param inputStream
*
* @参数:@param isExcel2003
*
* @参数:@return
*
* @返回值:List
*/
public List<List<String>> read(InputStream inputStream, boolean isExcel2003) {
List<List<String>> dataLst = null;
try {
/** 根据版本选择创建Workbook的方式 */
Workbook wb = isExcel2003 ? new HSSFWorkbook(inputStream) : new XSSFWorkbook(inputStream);
dataLst = read(wb);
} catch (IOException e) {
e.printStackTrace();
}
return dataLst;
}
/**
*
* @描述:读取数据
*
* @作者:wdw
*
* @时间:2011-8-9 下午04:37:25
*
* @参数:@param wb
*
* @参数:@return
*
* @返回值:List<List<String>>
*/
private List<List<String>> read(Workbook wb) {
List<List<String>> dataLst = new ArrayList<List<String>>();
/** 得到第一个shell */
Sheet sheet = wb.getSheetAt(0);
/** 得到Excel的行数 */
this.totalRows = sheet.getPhysicalNumberOfRows();
/** 得到Excel的列数 */
if (this.totalRows >= 1 && sheet.getRow(0) != null) {
this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
}
/** 循环Excel的行 */
for (int r = 0; r < this.totalRows; r++) {
Row row = sheet.getRow(r);
if (row == null) {
continue;
}
List<String> rowLst = new ArrayList<String>();
/** 循环Excel的列 */
for (short c = 0; c < this.getTotalCells(); c++) {
Cell cell = row.getCell(c);
String cellValue = "";
if (cell == null) {
rowLst.add(cellValue);
continue;
}
/** 处理Excel的字符串 */
cellValue = cell.getStringCellValue();
rowLst.add(cellValue);
}
/** 保存第r行的第c列 */
dataLst.add(rowLst);
}
return dataLst;
}