今天在网上找了个java解析excel的代码,发现与项目不太符合,于是就改造了一下,可以分sheet页签来分页解析excel,感觉用处挺大的,分享给大家。
package com.hikvision.cms.modules.reverseconf.utils;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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.xssf.usermodel.XSSFWorkbook;
public class ReadExcel {
public static Map<String,ArrayList<ArrayList<String>>> readExcel(String fileName, String path) {
Map<String,ArrayList<ArrayList<String>>> map = new HashMap<String, ArrayList<ArrayList<String>>>();
try {
Workbook workBook = null;
try {
workBook = new XSSFWorkbook(path + "\\" + fileName);
} catch (Exception ex) {
workBook = new HSSFWorkbook(new FileInputStream(path + "\\"
+ fileName));
}
for (int numSheet = 0; numSheet < workBook.getNumberOfSheets(); numSheet++) {
ArrayList<ArrayList<String>> Row = new ArrayList<ArrayList<String>>();
Sheet sheet = workBook.getSheetAt(numSheet);
if (sheet == null) {
continue;
}
// 循环行Row
for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
Row row = sheet.getRow(rowNum);
if (row == null) {
continue;
}
// 循环列Cell
ArrayList<String> arrCell = new ArrayList<String>();
for (int cellNum = 0; cellNum < row.getLastCellNum(); cellNum++) {
Cell cell = row.getCell(cellNum);
if (cell == null) {
cell = row.createCell(cellNum);
cell.setCellValue("");
}
arrCell.add(getValue(cell));
}
Row.add(arrCell);
}
map.put(String.valueOf(numSheet), Row);
}
} catch (IOException e) {
System.out.println("e:" + e);
}
return map;
}
@SuppressWarnings("static-access")
private static String getValue(Cell cell) {
if (cell.getCellType() == cell.CELL_TYPE_BOOLEAN) {
return String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) {
return String.valueOf(cell.getNumericCellValue());
} else {
return String.valueOf(cell.getStringCellValue());
}
}
/**
* 返回map
* key:sheet页签
* value:List里面的String[]为一行的数据
* @param sheet
* @return
*/
public static Map<String,List<String[]>> analysisExcel(String fileName, String path){
Map<String,ArrayList<ArrayList<String>>> sheet = readExcel(fileName, path);
Map<String,List<String[]>> map = new HashMap<String, List<String[]>>();
for (int i=0;i<sheet.size();i++) {
ArrayList<ArrayList<String>> row = sheet.get(String.valueOf(i));
List<String[]> list = new ArrayList<String[]>();
if(row.size()>0){
for(int j=0;j<row.size();j++){
ArrayList<String> cell = row.get(j);
String[] c = new String[cell.size()];
for(int k=0;k<cell.size();k++){
c[k] = cell.get(k);
}
list.add(c);
}
}
map.put(String.valueOf(i), list);
}
return map;
}
public static void main(String[] args) {
Map<String,List<String[]>> list = analysisExcel("TEST1.xls",
"D:\\Program Files\\Java");
for(int i=0;i<list.size();i++){
List<String[]> l = list.get("0");
System.out.println(l);
}
}
}