Question

Java POI读取Excel的时候怎么按列读取

Answer 1

按列读取的方法：
String pathname = "E:\\files\\title.xlsx";
File file = new File(pathname);
InputStream in = new FileInputStream(file);
//得到整个excel对象
XSSFWorkbook excel = new XSSFWorkbook(in);
//获取整个excel有多少个sheet
int sheets = excel.getNumberOfSheets();
//便利第一个sheet
Map<String,String> colMap = new HashMap<String, String>();
for(int i = 0 ; i < sheets ; i++ ){
XSSFSheet sheet = excel.getSheetAt(i);
if(sheet == null){
continue;
}
int mergedRegions = sheet.getNumMergedRegions();
XSSFRow row2 = sheet.getRow(0);
Map<Integer,String> category = new HashMap<Integer, String>();
for(int j = 0 ; j < mergedRegions; j++ ){
CellRangeAddress rangeAddress = sheet.getMergedRegion(j);
int firstRow = rangeAddress.getFirstColumn();
int lastRow = rangeAddress.getLastColumn();
category.put(rangeAddress.getFirstColumn(), rangeAddress.getLastColumn()+"-"+row2.getCell(firstRow).toString());
}
//便利每一行
for( int rowNum = 1 ; rowNum <= sheet.getLastRowNum() ; rowNum++ ){
System.out.println();
XSSFRow row = sheet.getRow(rowNum);
if(row == null){
continue;
}
short lastCellNum = row.getLastCellNum();
String cate = "";
Integer maxIndex = 0;
for( int col = row.getFirstCellNum() ; col < lastCellNum ; col++ ){
XSSFCell cell = row.getCell(col);
if(cell == null ){
continue;
}
if("".equals(cell.toString())){
continue;
}
int columnIndex = cell.getColumnIndex();
String string = category.get(columnIndex);
if(string != null && !string.equals("")){
String[] split = string.split("-");
cate = split[1];
maxIndex = Integer.parseInt(split[0]);
System.out.println(cate+"<-->"+cell.toString());
}else {
//如果当前便利的列编号小于等于合并单元格的结束,说明分类还是上面的分类名称
if(columnIndex<=maxIndex){
System.out.println(cate+"<-->"+cell.toString());
}else {
System.out.println("分类未知"+"<-->"+cell.toString());
}
}
}
}
}
}

Answer 2

按不按列读取不重要，你是想在解析完成后输出（或返回）时数据按照列的先后顺序是吧，你可以还是按行循环读取，再把读取的各个行中的cell值放入各个ArrayList里，如cell0放入ArrayList0里，最后，你想取那一列的数据，就遍历那个ArrayList