需要的jar是
poi-3.14.jar
poi-ooxml-3.14.jar
poi-ooxml-schemas-3.14.jar
xmlbeans-2.6.0.jar
可从https://download.csdn.net/download/samurai77/11014250 下载
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.DataFormatter;
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;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.util.*;
public static void main(String[] args) throws FileNotFoundException {
//
//String filename = "D:/test/gggggg.xlsx";
String filePath = "C:\\Users\\Duenan\\Desktop\\testvideo\\test.xlsx";
String filename = "test.xlsx";
// List<Map<String,Object>> list = FileUtil.readExcelFileToMapList(filename, new FileInputStream(file), 2, true, 0,"Sheet1");
List<Map<String,Object>> mapList = readExcelFileToMapList(filename,new FileInputStream(filePath),2,true,0,"Sheet1");
for(Map<String,Object> mapObject : mapList){
//System.out.println(mapObject);
String key = String.valueOf(mapObject.keySet());
String value = String.valueOf(mapObject.entrySet());
String values [] = value.split(",");
System.out.println("value="+value+"--------key="+key);
}
}
public static List<Map<String,Object>> readExcelFileToMapList(String filename, InputStream fileStream, int processStartRow, boolean processMaxData, int processRowNumber, String excelSheetName)
{
Row row;
StringBuilder lineBuilder;
Map<String,Object> map;
List<Map<String,Object>> contentList = new ArrayList<>();
Sheet sheet;
int columnNumber;
try
{
//Create Workbook instance for xlsx/xls file input stream
Workbook workbook = null;
if(filename.toLowerCase().endsWith("xlsx"))
workbook = new XSSFWorkbook(fileStream);
else
if(filename.toLowerCase().endsWith("xls"))
workbook = new HSSFWorkbook(fileStream);
//Get the nth sheet from the workbook
if ( excelSheetName == null || excelSheetName.trim().isEmpty() )
sheet = workbook.getSheetAt(0);
else
sheet = workbook.getSheet(excelSheetName.trim());
columnNumber = getSheetMaxColumnNumber(sheet);
//every sheet has rows, iterate over them
Iterator<Row> rowIterator = sheet.iterator();
int i=1;
while (rowIterator.hasNext() )
{
//Get the row object
row = rowIterator.next();
if ( i < processStartRow )
{
i++;
continue;
}
DataFormatter formatter = new DataFormatter();
lineBuilder = new StringBuilder();
String strValue;
for (int j = 0; j < columnNumber; j++)
{
try
{
strValue = formatter.formatCellValue(row.getCell(j));
strValue = strValue.replace(ColumnSeparatorType.COMMA.getSeparator(), " ");
}
catch(Exception e)
{
strValue = "";
}
lineBuilder.append(strValue).append(ColumnSeparatorType.COMMA.getSeparator());
}
String newStr = lineBuilder.toString();
if ( newStr.length()>1 )
newStr = newStr.substring(0,newStr.length()-1);
map = new HashMap<>();
map.put("index",i);
map.put("line",newStr);
map.put("originalLine",new String(newStr));
contentList.add(map);
if ( i == processStartRow+processRowNumber && processMaxData==false )
break;
i++;
} //end of rows iterator
fileStream.close();
}
catch(Exception e)
{
return null;
}
return contentList;
}
public static int getSheetMaxColumnNumber(Sheet sheet)
{
Row row;
int maxColumnNumber = 0;
Iterator<Row> rowIterator = sheet.iterator();
int i=1;
while (rowIterator.hasNext() )
{
//Get the row object
row = rowIterator.next();
if ( row.getLastCellNum() > maxColumnNumber )
maxColumnNumber = row.getLastCellNum();
}
return maxColumnNumber;
}