package com.cv.java.xlsx;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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;
/**
* @author Chandra Vardhan
*
*/
public class ReadExcel {
private static Properties readPropertiesFile = new Properties();
private static String sourceFilesDirectory = "";
private static String readSpecificSheets = "";
private static String[] sheetNamesOrIndexes = null;
private static boolean specificSheetsReadEnabled = false;
static {
readPropertiesFile = readPropertiesFile();
setValues(readPropertiesFile);
checkSpecificSheets();
}
private static Properties readPropertiesFile() {
Properties props = new Properties();
try {
props.load(ReadExcel.class.getClassLoader().getResourceAsStream("input.properties"));
System.out.println("input.properties file read successfully...");
} catch (Exception e) {
System.err.println("Problem while reading properties file!!!");
}
return props;
}
private static void checkSpecificSheets() {
String readSpecificSheetsValue = readSpecificSheets;
String[] sheetNamesIndexes = sheetNamesOrIndexes;
if (readSpecificSheetsValue != null && readSpecificSheetsValue.equalsIgnoreCase("yes")) {
specificSheetsReadEnabled = true;
if (sheetNamesIndexes != null && sheetNamesIndexes.length <= 0) {
System.out.println("Please specify sheetNames Or Indexes...");
}
}
}
private static void setValues(final Properties readPropertiesFile2) {
if (readPropertiesFile2 != null) {
sourceFilesDirectory = readPropertiesFile2.getProperty("source_files_location");
readSpecificSheets = readPropertiesFile2.getProperty("read_specific_sheets");
String sheetNamesIndexes = readPropertiesFile2.getProperty("sheet_names_or_indexes");
if (sheetNamesIndexes == null || sheetNamesIndexes.trim().isEmpty()) {
System.out.println("Please specify sheetNames Or Indexes...");
} else {
sheetNamesOrIndexes = sheetNamesIndexes.split(",");
}
}
}
public static String readAppSpecificFile(final String fileName, final String sourceFilesDirectory) {
String result = sourceFilesDirectory;
String sourceFile = ClassLoader.getSystemResource(fileName).getPath();
if (sourceFile != null) {
if (sourceFile.startsWith("/")) {
sourceFile = sourceFile.replaceAll("/", "//");
sourceFile = sourceFile.substring(2, sourceFile.length());
result = sourceFile;
}
}
return result;
}
public static boolean isFileExists(final String sourceFilesDirectory) {
boolean exist = false;
String sourceLocation = sourceFilesDirectory;
if (sourceLocation != null && !sourceLocation.trim().isEmpty()) {
File file = new File(sourceFilesDirectory);
if (file != null && file.exists()) {
File[] listFiles = file.listFiles();
if (listFiles != null && listFiles.length > 0) {
exist = true;
}
}
}
return exist;
}
public static Map<String, List<String[]>> retrieveSheet(final File file) {
Map<String, List<String[]>> readBooksFromExcelFile = new HashMap<String, List<String[]>>();
if (specificSheetsReadEnabled) {
readBooksFromExcelFile = readSpecificExcelSheets(file, sheetNamesOrIndexes);
} else {
readBooksFromExcelFile = readExcelSheets(file);
}
return readBooksFromExcelFile;
}
public static Map<String, List<String[]>> readExcelSheets(final File excelFilePath) {
Workbook workbook = null;
Map<String, List<String[]>> map = new HashMap<String, List<String[]>>();
try {
workbook = getWorkbook(excelFilePath);
if (workbook != null) {
int numberOfSheets = workbook.getNumberOfSheets();
DataFormatter dataFormatter = new DataFormatter();
for (int i = 0; i < numberOfSheets; ++i) {
List<String[]> list = new ArrayList<String[]>();
Sheet sheet = workbook.getSheetAt(i);
Iterator<Row> iterator = sheet.iterator();
while (iterator.hasNext()) {
Row nextRow = iterator.next();
Iterator<Cell> cellIterator = nextRow.cellIterator();
String result = "";
while (cellIterator.hasNext()) {
Cell nextCell = cellIterator.next();
String formatCellValue = dataFormatter.formatCellValue(nextCell);
formatCellValue = changeBoolean(formatCellValue);
if (formatCellValue != null) {
result += formatCellValue + ",";
}
}
if (result != null && !result.trim().isEmpty()) {
result = result.substring(0, result.length() - 1);
String[] values = result.split(",");
if (values != null && values.length > 0) {
list.add(values);
}
}
}
map.put(sheet.getSheetName(), list);
}
}
} catch (Exception e) {
System.err.println("Error while reading the workbook/sheet...");
e.printStackTrace();
} finally {
if (workbook != null) {
workbook = null;
}
}
return map;
}
public static Map<String, List<String[]>> readSpecificExcelSheets(final File excelFilePath,
final String... sheetsIndex) {
Workbook workbook = null;
Map<String, List<String[]>> map = new HashMap<String, List<String[]>>();
try {
workbook = getWorkbook(excelFilePath);
if (workbook != null) {
int numberOfSheets = workbook.getNumberOfSheets();
for (int i = 0; i < numberOfSheets; ++i) {
Sheet sheet = workbook.getSheetAt(i);
boolean flag = false;
if (sheetsIndex != null && sheetsIndex.length > 0) {
for (String sheetNameOrIndex : sheetsIndex) {
if (sheetNameOrIndex != null && !sheetNameOrIndex.trim().isEmpty()) {
if (sheetNameOrIndex.equalsIgnoreCase("" + i)
|| sheetNameOrIndex.equalsIgnoreCase(sheet.getSheetName())) {
flag = true;
break;
}
}
}
}
if (!flag) {
continue;
}
List<String[]> list = new ArrayList<String[]>();
Iterator<Row> iterator = sheet.iterator();
DataFormatter dataFormatter = new DataFormatter();
while (iterator.hasNext()) {
Row nextRow = iterator.next();
Iterator<Cell> cellIterator = nextRow.cellIterator();
String result = "";
while (cellIterator.hasNext()) {
Cell nextCell = cellIterator.next();
String formatCellValue = dataFormatter.formatCellValue(nextCell);
formatCellValue = changeBoolean(formatCellValue);
if (formatCellValue != null) {
result += formatCellValue + ",";
}
}
if (result != null && !result.trim().isEmpty()) {
result = result.substring(0, result.length() - 1);
String[] values = result.split(",");
if (values != null && values.length > 0) {
list.add(values);
}
}
}
map.put(sheet.getSheetName(), list);
}
}
} catch (Exception e) {
System.err.println("ERROR : while reading the workbook/sheet...");
e.printStackTrace();
} finally {
if (workbook != null) {
workbook = null;
}
}
return map;
}
private static String changeBoolean(final String formatCellValue) {
String result = formatCellValue;
if (result != null) {
if (result.contains("TRUE()")) {
result = result.replace("TRUE()", "true");
}
if (result.contains("FALSE()")) {
result = result.replace("FALSE()", "false");
}
}
return result;
}
public static Workbook getWorkbook(final File excelFilePath) {
Workbook workbook = null;
FileInputStream inputStream = null;
try {
if (excelFilePath != null) {
inputStream = new FileInputStream(excelFilePath);
if (excelFilePath != null
&& (excelFilePath.getName().endsWith("xlsx") || excelFilePath.getName().endsWith("xlsm"))) {
workbook = new XSSFWorkbook(inputStream);
} else if (excelFilePath != null && excelFilePath.getName().endsWith("xls")) {
workbook = new HSSFWorkbook(inputStream);
} else {
workbook = null;
}
}
return workbook;
} catch (Exception e) {
System.err.println("Specified file is not availale.");
e.printStackTrace();
} finally {
if (inputStream != null) {
try {
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if (workbook != null) {
workbook = null;
}
}
return null;
}
public static List<File> getFilesPath(final File directory, List<File> result) {
if (directory != null) {
if (directory.isDirectory()) {
File[] listFiles = directory.listFiles();
if (listFiles != null) {
for (File file : listFiles) {
if (file != null) {
if (file.isDirectory()) {
getFilesPath(file, result);
} else {
result.add(file);
}
}
}
}
} else {
result.add(directory);
}
}
return result;
}
public static List<File> getFiles() {
String sourceDir = sourceFilesDirectory;
boolean filesExists = isFileExists(sourceDir);
if (!filesExists) {
sourceDir = readAppSpecificFile("test.xlsx", sourceDir);
}
return getFilesPath(new File(sourceDir), new ArrayList<File>());
}
public static String getSourceFilesDirectory() {
return sourceFilesDirectory;
}
public static void setSourceFilesDirectory(String sourceFilesDirectory) {
ReadExcel.sourceFilesDirectory = sourceFilesDirectory;
}
public static String getReadSpecificSheets() {
return readSpecificSheets;
}
public static void setReadSpecificSheets(String readSpecificSheets) {
ReadExcel.readSpecificSheets = readSpecificSheets;
}
} | 
No comments:
Post a Comment