I need to check 30 sheets in an excel file. Every sheet has a maximum of 7 rows and plenty of columns.
I need to update every row (if a certain condition is true) by adding 32 new columns after the last existing column, then save the file.
So I need to be able to do both read and write on my excel file from the same piece of code, then to save my changes updating the original excel file (using the same file name).
I really think Apache Poi is the best solution when it comes to managing excels in Java. You might want to have a look at these examples provided by Apache Poi itself about this topic (the url may change slightly over time). This article also may provide an hint for the solution you're asking for.
You can create a new Workbook (the Java Object representing your excel) using WorkbookFactory as follows:
Workbook workbook = WorkbookFactory.create(new FileInputStream(excelFilePath));
where excelFilePath is (obviously) the Path of the excel file you want to update.
You can now do whatever you want with your Workbook, hurray! You can both read and change all its content and (eventually) write new one! Note that all the changes to your Workbook are NOT actually going to affect your excel file. You're editing the Workbook Java Object, not the actual excel file. In a sense, your Workbook is initially created based on your excel file content, then it is totally independent from it.
Once you're done editing your Workbook, you can save it overriding your initial excel file (in a way, you're now updating your excel file with all your changes) as follows:
FileOutputStream outputStream = new FileOutputStream(excelFilePath);
workbook.write(outputStream);
workbook.close();
outputStream.close();
and you're done! Simple and effective!
Here it is some code that may suit your needs:
public static void main(String[] args) throws EncryptedDocumentException, IOException {
// Step 1: load your excel file as a Workbook
String excelFilePath = "D:\\Desktop\\testExcel.xlsx";
Workbook workbook = WorkbookFactory.create(new FileInputStream(excelFilePath));
// Step 2: modify your Workbook as you prefer
Iterator<Sheet> sheetIterator = workbook.sheetIterator(); // Getting an iterator for all the sheets
while (sheetIterator.hasNext()) {
Iterator<Row> rowIterator = sheetIterator.next().rowIterator(); // Getting an iterator for all the rows (of current sheet)
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
// Put here your internal logic to understand if the row needs some changes!
int cellsn = row.getLastCellNum() == -1 ? 0 : row.getLastCellNum();
for (int j = cellsn ; j < cellsn + 32 ; ++j) {
row.createCell(j, CellType.STRING).setCellValue("New column after the last existing one n°" + (j - cellsn + 1));
}
}
}
// Step 3: update the original excel file with all your amazing changes!
FileOutputStream outputStream = new FileOutputStream(excelFilePath);
workbook.write(outputStream);
workbook.close();
outputStream.close();
}
Do not call the method create(java.io.File file) instead of create(java.io.InputStream inp) while using WorkbookFactory and creating your Workbook, it will lead to problems when saving your file as it's discussed on this post.
I really don't know the reason why you initially add the tag "selenium" to your question but if you're thinking to use some spider-code to "manually" open and edit your excel file, please don't. This solution is as horrible as HSSF is (did you get the joke?).
If you're using .xlsx excel files, you may want to use Apache Poi XSSF objects (as XSSFWorkbook, XSSFRow and XSSFCell). More about XSSF vs HSSF on this post.
Finally, here it is the explicit list of all libreries needed in order for my code to run (they're a lot, do not panic): Apache Poi 4.1.2, Apache Poi Ooxml 4.1.2, Apache Poi Ooxml Schemas 4.1.2, XmlBeans, Commons Codec, Commons Collections4, Commons Compress and Commons Math3. If you're using Maven, just add to your dependencies file the following lines:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
And that's all, hope it helps! Good work!