Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Requirement is:
Tfileinputexcel ----> Tfileoutputexcel ------> after loading into the output. i want to empty the inputexcel file without deleting the file.
How to achieve this.
@ravi777,Once the file has loaded and then take on subjob ok and delete the existing file or archive the file and create a new file as similar with your source file columns.
But when i am creating new .xlsx file using tfiletouch. it is creating but not able to open it.
@ravi777 - if you want a java solution to handle excel files then you can write a routine and invoke that in your talend job something like this -
And, you can use below code to check and delete all rows from your excel file -
FileInputStream ExcelFileToRead = new FileInputStream(filePath); XSSFWorkbook workbook = new XSSFWorkbook(ExcelFileToRead); Sheet sheet = workbook.getSheetAt(0); String sheetName = sheet.getSheetName(); int numberOfRows = sheet.getPhysicalNumberOfRows(); if(numberOfRows > 0) { for (int index = sheet.getLastRowNum(); index >= sheet.getFirstRowNum(); index--) { //System.out.println(index); if(sheet.getRow(index) != null) { sheet.removeRow( sheet.getRow(index)); } else{ System.out.println("Info: clean sheet"); }
} } ExcelFileToRead.close(); // Write the output to the file FileOutputStream ExcelFileToWrite = new FileOutputStream(filePath); workbook.write(ExcelFileToWrite); ExcelFileToWrite.close();
// Closing the workbook workbook.close(); }
Hello ravi777,
Is there any update for your issue?
Best regards
Sabrina
Thank you very much, very useful.
Just 2 comments to help here:
1- Just in case, you need to add the following in your tJava component under Advanced setting -> import
import java.io.FileOutputStream;
import java.io.FileInputStream;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
2- Sheet must be replaced by XSSFSheet in the code
3- there is an extra } at the end of the code that need to be removed
here is the corrected code:
/* start */
FileInputStream ExcelFileToRead = new FileInputStream(context.filePath);
XSSFWorkbook workbook = new XSSFWorkbook(ExcelFileToRead);
XSSFSheet sheet = workbook.getSheetAt(0);
String sheetName = sheet.getSheetName();
int numberOfRows = sheet.getPhysicalNumberOfRows();
if(numberOfRows > 0) {
for (int index = sheet.getLastRowNum(); index >= sheet.getFirstRowNum(); index--) {
//System.out.println(index);
if(sheet.getRow(index) != null) {
sheet.removeRow( sheet.getRow(index));
}
else{
System.out.println("Info: clean sheet");
}
}
}
ExcelFileToRead.close();
// Write the output to the file
FileOutputStream ExcelFileToWrite = new FileOutputStream(context.filePath);
workbook.write(ExcelFileToWrite);
ExcelFileToWrite.close();
// Closing the workbook
workbook.close();
/* end */