package functions;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class TestExcel2 {
public static void main(String[] args) throws IOException {
// List<String> list =
// readXlsxAll("");
// for (int i = 0; i < list.size(); i++) {
// System.out.println(i + " " + list.get(i));
// }
List<String> list = readXlsxRow(
"",
1, 1);
// System.out.println(list.contains("GOVERNMENT_GOV_PILLAR_SCORE"));
// for (int i = 0; i < list.size(); i++) {
// System.out.println(i + " " + list.get(i));
// }
System.out
.println(readXlsxRowNum(
"",
1));
System.out
.println(readXlsxCloumnNum(
"",
1));
System.out
.println(readXlsxCell(
"",1,1,1));
}
public static List<String> readXlsxAll(String fileName) throws IOException {
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(fileName);
List<String> list = new ArrayList<String>();
for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
if (xssfSheet == null) {
continue;
}
for (int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
if (xssfRow == null) {
continue;
}
int mapNum = 0;
for (int cellNum = 0; cellNum <= xssfRow.getLastCellNum(); cellNum++) {
XSSFCell xssfCell = xssfRow.getCell(cellNum);
if (xssfCell == null) {
continue;
}
list.add(mapNum, getValue(xssfCell));
}
}
}
return list;
}
public static String readXlsxCell(String fileName, int numSheet,
int rowNum, int cellNum) throws IOException {
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(fileName);
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet-1);
if (xssfSheet == null) {
return "";
}
XSSFRow xssfRow = xssfSheet.getRow(rowNum-1);
if (xssfRow == null) {
return "";
}
XSSFCell xssfCell = xssfRow.getCell(cellNum-1);
if (xssfCell == null) {
return "";
}
return getValue(xssfCell);
}
public static int readXlsxRowNum(String fileName, int numSheet)
throws IOException {
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(fileName);
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet - 1);
if (xssfSheet == null) {
return 0;
}
return xssfSheet.getLastRowNum() + 1;
}
public static int readXlsxCloumnNum(String fileName, int numSheet)
throws IOException {
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(fileName);
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet - 1);
if (xssfSheet == null) {
return 0;
}
XSSFRow xssfRow = xssfSheet.getRow(0);
if (xssfRow == null) {
return 0;
}
return xssfRow.getLastCellNum();
}
public static List<String> readXlsxRow(String fileName, int numSheet,
int rowNum) throws IOException {
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(fileName);
List<String> list = new ArrayList<String>();
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet - 1);
if (xssfSheet == null) {
return null;
}
XSSFRow xssfRow = xssfSheet.getRow(rowNum - 1);
if (xssfRow == null) {
return null;
}
int mapNum = 0;
for (int cellNum = 0; cellNum <= xssfRow.getLastCellNum(); cellNum++) {
XSSFCell xssfCell = xssfRow.getCell(cellNum);
if (xssfCell == null) {
continue;
}
list.add(mapNum, getValue(xssfCell));
}
return list;
}
public static String getValue(XSSFCell xssfCell) {
if (xssfCell.getCellType() == xssfCell.CELL_TYPE_STRING) {
return String.valueOf(xssfCell.getStringCellValue());
} else if (xssfCell.getCellType() == xssfCell.CELL_TYPE_NUMERIC) {
return String.valueOf(xssfCell.getNumericCellValue());
} else {
return "here";
}
}
}
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class TestExcel2 {
public static void main(String[] args) throws IOException {
// List<String> list =
// readXlsxAll("");
// for (int i = 0; i < list.size(); i++) {
// System.out.println(i + " " + list.get(i));
// }
List<String> list = readXlsxRow(
"",
1, 1);
// System.out.println(list.contains("GOVERNMENT_GOV_PILLAR_SCORE"));
// for (int i = 0; i < list.size(); i++) {
// System.out.println(i + " " + list.get(i));
// }
System.out
.println(readXlsxRowNum(
"",
1));
System.out
.println(readXlsxCloumnNum(
"",
1));
System.out
.println(readXlsxCell(
"",1,1,1));
}
public static List<String> readXlsxAll(String fileName) throws IOException {
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(fileName);
List<String> list = new ArrayList<String>();
for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
if (xssfSheet == null) {
continue;
}
for (int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
if (xssfRow == null) {
continue;
}
int mapNum = 0;
for (int cellNum = 0; cellNum <= xssfRow.getLastCellNum(); cellNum++) {
XSSFCell xssfCell = xssfRow.getCell(cellNum);
if (xssfCell == null) {
continue;
}
list.add(mapNum, getValue(xssfCell));
}
}
}
return list;
}
public static String readXlsxCell(String fileName, int numSheet,
int rowNum, int cellNum) throws IOException {
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(fileName);
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet-1);
if (xssfSheet == null) {
return "";
}
XSSFRow xssfRow = xssfSheet.getRow(rowNum-1);
if (xssfRow == null) {
return "";
}
XSSFCell xssfCell = xssfRow.getCell(cellNum-1);
if (xssfCell == null) {
return "";
}
return getValue(xssfCell);
}
public static int readXlsxRowNum(String fileName, int numSheet)
throws IOException {
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(fileName);
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet - 1);
if (xssfSheet == null) {
return 0;
}
return xssfSheet.getLastRowNum() + 1;
}
public static int readXlsxCloumnNum(String fileName, int numSheet)
throws IOException {
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(fileName);
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet - 1);
if (xssfSheet == null) {
return 0;
}
XSSFRow xssfRow = xssfSheet.getRow(0);
if (xssfRow == null) {
return 0;
}
return xssfRow.getLastCellNum();
}
public static List<String> readXlsxRow(String fileName, int numSheet,
int rowNum) throws IOException {
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(fileName);
List<String> list = new ArrayList<String>();
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet - 1);
if (xssfSheet == null) {
return null;
}
XSSFRow xssfRow = xssfSheet.getRow(rowNum - 1);
if (xssfRow == null) {
return null;
}
int mapNum = 0;
for (int cellNum = 0; cellNum <= xssfRow.getLastCellNum(); cellNum++) {
XSSFCell xssfCell = xssfRow.getCell(cellNum);
if (xssfCell == null) {
continue;
}
list.add(mapNum, getValue(xssfCell));
}
return list;
}
public static String getValue(XSSFCell xssfCell) {
if (xssfCell.getCellType() == xssfCell.CELL_TYPE_STRING) {
return String.valueOf(xssfCell.getStringCellValue());
} else if (xssfCell.getCellType() == xssfCell.CELL_TYPE_NUMERIC) {
return String.valueOf(xssfCell.getNumericCellValue());
} else {
return "here";
}
}
}