I have created a program which creates excel sheet, which is having some data column 1 - contains String column 2- contains number (which is basically an integer, but while reading why it is getting converted to Float ?)
public class ApachePOI8 {
public static void main(String[] args) {
String file = "C:\\Eclipse workspace\\PoiExample\\Excel8.xlsx";
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("Sheet1");
sheet.protectSheet("Pwd1"); //This statements locks the entire sheet with password: Pwd1
DataFormat fmt = wb.createDataFormat(); //created a textStyle, which prevents Excel to interprate data, in short it means it will be taken as String
CellStyle textStyle = wb.createCellStyle();
textStyle.setDataFormat(fmt.getFormat("@"));
textStyle.setLocked(false);
CellStyle numerStyle = wb.createCellStyle();
numerStyle.setDataFormat(fmt.getFormat("0"));
numerStyle.setLocked(false);
Row row0 = CellUtil.getRow(0, sheet);
for (int columnIndex = 0; columnIndex<4; columnIndex++){
Cell cell = CellUtil.getCell(row0, columnIndex);
if(columnIndex == 0) {
cell.setCellStyle(textStyle);
cell.setCellValue("Product 1");
}else if(columnIndex == 1) {
cell.setCellStyle(numerStyle);
cell.setCellValue(1);
}
}
else{
cell.setCellStyle(textStyle);
}
} //end of for Loop
try {
FileOutputStream outputStream = null;
outputStream = new FileOutputStream(file);
wb.write(outputStream);
outputStream.close();
System.out.println("Done!");
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
Now to read the excel sheet which got generated
public class ApachePOIExcelRead {
private static final String FILE_NAME = "C:\\Eclipse workspace\\PoiExample\\Excel8.xlsx";
public static void main(String[] args) {
try {
FileInputStream excelFile = new FileInputStream(new File(FILE_NAME));
Workbook workbook = new XSSFWorkbook(excelFile);
Sheet datatypeSheet = workbook.getSheetAt(0);
Iterator<Row> iterator = datatypeSheet.iterator();
while (iterator.hasNext()) {
Row currentRow = iterator.next();
Iterator<Cell> cellIterator = currentRow.iterator();
while (cellIterator.hasNext()) {
Cell currentCell = cellIterator.next();
//getCellTypeEnum shown as deprecated for version 3.15
//getCellTypeEnum ill be renamed to getCellType starting from version 4.0
if (currentCell.getCellTypeEnum() == CellType.STRING) {
System.out.print("String: "+currentCell.getStringCellValue() + "\n");
} else if (currentCell.getCellTypeEnum() == CellType.NUMERIC) {
System.out.print("NUMERIC "+currentCell.getNumericCellValue() + "\n");
}
}
System.out.println();
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
But I am getting the output as String: Product 1 NUMERIC 1.0 String: 12-12-12
Why is it so that the number which is entered i.e 1 is coming in float NUMERIC 1.0?
Kindly suggest.