The below code not able to write to the files.
i wrote a program to execute the commands in cmd. Those commands are nothing but executing the java file which is in jar file. This java file which in jar is expecting file path as a command line argument.
Note:- jar file also created by me only.
here is my java file
public class ExcelDriver extends Thread {
public static void main(String[] args) throws IOException, InterruptedException {
File directory = new File("C://Users//kondeti.venkatarao//Documents//Regresion_sheets//custome");
File[] files = directory.listFiles();
for (File file: files) {
System.out.println("\""+file.getAbsolutePath()+"\"");
if(file.isFile()){
Runtime.getRuntime().exec("cmd.exe /c start java -jar Demo.jar readExcelDemo.Final "+file.getAbsolutePath());
ExcelDriver.sleep(5000);
}
}
}
}
Here is the jar file code
public class Final {
public static int getExcelColumnNumber(String column) {
int result = 0;
for (int i = 0; i < column.length(); i++) {
result *= 26;
result += column.charAt(i) - 'A' + 1;
}
return result;
}
public static String getExcelColumnName(int number) {
final StringBuilder sb = new StringBuilder();
int num = number - 1;
while (num >= 0) {
int numChar = (num % 26) + 65;
sb.append((char)numChar);
num = (num / 26) - 1;
}
return sb.reverse().toString();
}
void run(File file, File errors, File misMatchs) throws IOException{
if (file.getName().endsWith(".xlsx") || file.getName().endsWith(".xlsm")) {
FileInputStream fis = new FileInputStream(file);
StringBuilder error = new StringBuilder();
StringBuilder misMatch = new StringBuilder();
// Create Workbook instance holding reference to .xlsx file
//OPCPackage pkg = OPCPackage.open(file, PackageAccess.READ);
XSSFWorkbook workbook = new XSSFWorkbook(fis);
int i = 1;
while (i < workbook.getNumberOfSheets()) {
// System.out.println(workbook.getNumberOfSheets());
// Get first/desired sheet from the workbook
XSSFSheet sheet = workbook.getSheetAt(i);
if(sheet.getRow(0).getCell(0).getRawValue().equalsIgnoreCase("fail")){
// Iterate through each rows one by one
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
// For each row, iterate through all the columns
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
// Check the cell type and format accordingly
switch (cell.getCellType()) {
/*
* case Cell.CELL_TYPE_NUMERIC:
* System.out.print(cell.getNumericCellValue());
* break; case Cell.CELL_TYPE_STRING:
* System.out.print(cell.getStringCellValue());
* break;
*/
// case Cell.CELL_TYPE_FORMULA:
case Cell.CELL_TYPE_FORMULA:
if (cell.getCellFormula().startsWith("IF("))
if (sheet.getRow(row.getRowNum()).getCell(cell.getColumnIndex()).getRawValue().equals("1")) {
HashSet<Integer> number = new HashSet<Integer>();
ArrayList<String> alphas = new ArrayList<String>();
String formula = sheet.getRow(row.getRowNum()).getCell(cell.getColumnIndex()).toString();
Matcher digitMatcher = Pattern.compile("\\d+").matcher(formula);
Matcher alphabetMatcher = Pattern.compile("[a-zA-Z]+").matcher(formula);
while (alphabetMatcher.find()) {
if (!alphabetMatcher.group().equals("TYPE"))
alphas.add(alphabetMatcher.group());
}
int countIF = Collections.frequency(alphas, "IF");
int countABS = Collections.frequency(alphas, "ABS");
HashSet<String> alphaSet = new HashSet<String>(alphas);
if (countIF != 5 && countIF != 6)
alphaSet.remove("IF");
if (countABS != 3 && countABS != 4)
alphaSet.remove("ABS");
while (digitMatcher.find()) {
if (!digitMatcher.group().equals("0") && !digitMatcher.group().equals("1") && !digitMatcher.group().equals("01"))
number.add(Integer.parseInt(digitMatcher.group()));
}
ArrayList<Integer> numberList = new ArrayList<Integer>(number);
ArrayList<String> alphaList = new ArrayList<String>(alphaSet);
System.out.println("alphaSet"+ alphaSet);
System.out.println("numberList"+ numberList);
int rowIndex = numberList.get(0) - 1;
int originalColumnIndex = getExcelColumnNumber(alphaList.get(0)) - 1;
int referenceColumnIndex = getExcelColumnNumber(alphaList.get(1)) - 1;
if (originalColumnIndex > referenceColumnIndex) {
int temp = referenceColumnIndex;
referenceColumnIndex = originalColumnIndex;
originalColumnIndex = temp;
}
// System.out.println(sheet.getRow(row.getRowNum()));
System.out.println("File Name: "+ file.getName());
System.out.println("Sheet Name: "+ sheet.getSheetName());
System.out.println(sheet.getRow(row.getRowNum()).getCell(cell.getColumnIndex()).toString());
if (sheet.getRow(rowIndex).getCell(originalColumnIndex).getCellFormula().equals(""))
System.out.println("please help me out");
System.out.println("Function Name: "+ sheet.getRow(rowIndex).getCell(originalColumnIndex).getCellFormula());
System.out.println("row indext"+ rowIndex);
System.out.println("original column index"+ originalColumnIndex);
System.out.println("ref column index"+ referenceColumnIndex);
/*
* System.out.println("File Name: " +
* file.getName());
* System.out.println("Sheet Name: " +
* sheet.getSheetName());
* System.out.println(cell
* .getCellFormula());
*/
if (sheet.getRow(rowIndex).getCell(originalColumnIndex).getCellFormula().contains("qCRA_")&& sheet.getRow(rowIndex)
.getCell(originalColumnIndex).getRawValue().contains("Error:")) {
error.append(System.getProperty("line.separator"));
error.append("File Name: "+ file.getName());
error.append(System.getProperty("line.separator"));
error.append("Sheet Name: "+ sheet.getSheetName());
error.append(System.getProperty("line.separator"));
error.append("Function Name: "+ sheet.getRow(rowIndex).getCell(originalColumnIndex).getCellFormula());
error.append(System.getProperty("line.separator"));
error.append("Cell Number: "+getExcelColumnName(originalColumnIndex+1)+numberList.get(0));
error.append(System.getProperty("line.separator"));
error.append("Orginal Value : "+sheet.getRow(rowIndex).getCell(originalColumnIndex).getRawValue());
error.append(System.getProperty("line.separator"));
error.append("Reference Value : "+sheet.getRow(rowIndex).getCell(referenceColumnIndex));
error.append(System.getProperty("line.separator"));
} else {
misMatch.append(System.getProperty("line.separator"));
misMatch.append("File Name: "+ file.getName());
misMatch.append(System.getProperty("line.separator"));
misMatch.append("Sheet Name: "+ sheet.getSheetName());
misMatch.append(System.getProperty("line.separator"));
misMatch.append("Function Name: "+ sheet.getRow(rowIndex).getCell(originalColumnIndex).getCellFormula());
misMatch.append(System.getProperty("line.separator"));
misMatch.append("Cell Number: "+getExcelColumnName(originalColumnIndex+1)+numberList.get(0));
misMatch.append(System.getProperty("line.separator"));
misMatch.append("Orginal Value : "+sheet.getRow(rowIndex).getCell(originalColumnIndex).getRawValue());
misMatch.append(System.getProperty("line.separator"));
misMatch.append("Reference Value : "+sheet.getRow(rowIndex).getCell(referenceColumnIndex));
misMatch.append(System.getProperty("line.separator"));
}
}
break;
}
cell = null;
}
row = null;
}
}
i++;
fis.close();
sheet=null;
}
workbook=null;
//FileUtils.writeStringToFile(errors, error.toString(),true);
//FileUtils.writeStringToFile(misMatchs, misMatch.toString(),true);
FileWriter errorsFileWriter = new FileWriter(errors,true);
BufferedWriter errorsBufferedWriter = new BufferedWriter(errorsFileWriter);
errorsBufferedWriter.write(error.toString());
errorsBufferedWriter.flush();
errorsBufferedWriter.close();
FileWriter misMatchFileWriter = new FileWriter(misMatchs, true);
BufferedWriter misMatchesBufferedWriter = new BufferedWriter(misMatchFileWriter);
misMatchesBufferedWriter.write(misMatch.toString());
misMatchesBufferedWriter.flush();
misMatchesBufferedWriter.close();
}
}
public static void main(String[] args) {
try {
String filepath = args[0];//.replace("\" , ", "\\");
//System.out.println(filepath);
File directory = new File(filepath);
File errors = new File("C://Users//kondeti.venkatarao//Documents//Regresion_sheets//Error.txt");
if(!errors.exists()){
errors.createNewFile();
}
File mismatch = new File("C://Users//kondeti.venkatarao//Documents//Regresion_sheets//Mismatch.txt");
if(!mismatch.exists()){
mismatch.createNewFile();
}
Final hvd=new Final();
hvd.run(directory,errors,mismatch);
} catch (Exception e) {
e.printStackTrace();
}
}
}
