Activate Excel file A1 cell of each sheet in Java

■ Cause

In various development sites, it is common that the A1 cell of the sheet is used when the Excel file is first opened when creating an artifact. It takes time to do it manually, and I think that the more files there are, the more omissions will be made to cell A1. I think it would be convenient to use Apache's POI to set all files under the specified folder or in cell A1 of each sheet of the specified file, so I made a simple program. (Can be set to any cell by modifying A1CELL_ROW and A1CELL_COL)

■ Environment 　OS：Windows JRE version: 1.8 POI version: 4.1.2

■ Source code package xxx.xxxxx.xxxx; // Change to your liking

import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.nio.file.FileVisitResult; import java.nio.file.Files; import java.nio.file.Path; import java.nio.file.Paths; import java.nio.file.SimpleFileVisitor; import java.nio.file.attribute.BasicFileAttributes; import java.util.ArrayList; import java.util.List; import java.util.stream.Collectors; import java.util.stream.IntStream;

import org.apache.poi.ss.usermodel.Cell; 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.ss.usermodel.WorkbookFactory;

public class A1CellJump {

//エクセルファイルを置いているフォルダー private static String INPUT_DIR = ".\"; private final int A1CELL_ROW = 0; private final int A1CELL_COL = 0; private final String EXCEL = ".xlsx";

public static void main(String[] args) throws IOException {

	A1CellJump tt = new A1CellJump();
	if (args.length > 0) {
		INPUT_DIR = args[0];
	}

	List<String> fileList = tt.getFileList(INPUT_DIR);
	for (int i = 0; i < fileList.size(); i++) {

		tt.jumpA1Cell(fileList.get(i));
	}

}

/**

Move to cell A1.
@param fileName File name (full path) */ private void jumpA1Cell (String fileName) { System.out.println ("configuration file:" + fileName); // Specify the Excel file to change FileInputStream in = null;
```
 Workbook wb = null;

 try {
 	in = new FileInputStream(fileName);
```

// Use WorkbookFactory when editing an existing excel file wb = WorkbookFactory.create(in);

	} catch (Exception e) {
		e.printStackTrace();
	}

// Get all sheet names. List list = getSheetList(wb);

// Move to cell A1 for each sheet for (int i = 0; i < list.size(); i++) { String sheetName = list.get(i); System.out.print ("\ t sheet:" + sheetName);

// Activate cell A1 of the specified sheet. activeA1Cell(wb.getSheet(sheetName)); }

	FileOutputStream out = null;

	try {

// Specify the Excel file to change out = new FileOutputStream(fileName);

// write wb.write(out); } catch (Exception e) { e.printStackTrace(); } finally { try {out.close();} catch (Exception e) {} try {wb.close();} catch (Exception e) {}

	}

}

/**

Get all sheet names.
- @param wkBook WorkBook

@return All sheet names */ private List getSheetList(Workbook wkBook) {

 return IntStream.range(0, wkBook.getNumberOfSheets())
 		.mapToObj(wkBook::getSheetAt)
 		.map(Sheet::getSheetName)
 		.collect(Collectors.toList());

}

/**

Activate cell A1 of the specified sheet.
@param sheet sheet */ private void activeA1Cell(Sheet sheet) { Row row = sheet.getRow(A1CELL_ROW); Cell cell = null; if (row != null) { cell = row.getCell(A1CELL_COL); if (cell == null) { cell = row.createCell(A1CELL_COL); } } else { row = sheet.createRow(A1CELL_ROW); cell = row.createCell(A1CELL_COL); }
```
 cell.setAsActiveCell();
 System.out.println(" OK");
```
}

/**
Get all Excel file names that exist in the specified path.
(If the specified path is an Excel file, the Excel file name is returned)
@param dir The path of the Excel file to get

@return Excel filename list */ private List getFileList(String dir) { List fileList = new ArrayList();; Path p1 = Paths.get(dir);

 if (dir.endsWith(EXCEL)) {
     fileList.add(dir);
     System.out.println(dir);
     return fileList;
 }

 try {
     Files.walkFileTree(p1, new SimpleFileVisitor<Path>() {
         @Override
         public FileVisitResult visitFile(Path file, BasicFileAttributes attrs)
             throws IOException
         {
             String fileName = file.toString();
             if (fileName.endsWith(EXCEL)) {
                 fileList.add(file.toString());
                 System.out.println(file.toString());
             }

             return FileVisitResult.CONTINUE;
         }
     });
 } catch (IOException e) {
     e.printStackTrace();
 }
 return fileList;

}

} ■ Results Case 1: There is a value in cell A1 (Sheet1 in BBB.xlsx) Case 2: No value in cell A1 (Sheet2 in BBB.xlsx) Case 3: A1 cell is a merged cell, no value (Sheet3 in BBB.xlsx) Case 4: A1 cell is a merged cell and has a value (Sheet4 in BBB.xlsx)

-------- Output message to console ----------------- ....\AAA.xlsx ....\BBB.xlsx Configuration file: .... \ AAA.xlsx Sheet: Sheet1 OK Configuration file: .... \ BBB.xlsx Sheet: Sheet1 OK Sheet: Sheet2 OK Sheet: Sheet3 OK Sheet: Sheet4 OK