Excel sheet removing zero(s) for number starting with zero(s)

Question

I need to write CSV/Xls file with a column having numbers like 00078, but even after writing the number as a string, excel sheet displays it as 78.

I have tried apache poi library and try setting cell type to String.

HSSFWorkbook workbook = new HSSFWorkbook(); 

// Create a blank sheet 
HSSFSheet sheet = workbook.createSheet("student Details"); 

// This data needs to be written (Object[]) 
Map<String, Object[]> data = new TreeMap<String, Object[]>(); 
data.put("1", new Object[]{ "ID", "NAME", "LASTNAME" }); 
data.put("2", new Object[]{ 00078, "Pankaj", "Kumar" }); 

// Iterate over data and write to sheet 
Set<String> keyset = data.keySet(); 
int rownum = 0; 
for (String key : keyset) { 
    // this creates a new row in the sheet 
    Row row = sheet.createRow(rownum++); 
    Object[] objArr = data.get(key); 
    int cellnum = 0; 
    for (Object obj : objArr) { 
        // this line creates a cell in the next column of that row 
        Cell cell = row.createCell(cellnum++); 
        if (obj instanceof String) 
            cell.setCellValue((String)obj); 
        else if (obj instanceof Integer){
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(String.valueOf(obj));
        }
    } 
}

I expect the column to treat 00078 as a String and display the same.

Try `data.put("2", new Object[]{ "00078", "Pankaj", "Kumar" });` add quotes, you have it as a number there currently. — Adder, Jul 31 '19 at 13:11
Great advice @Adder it worked. Now I wonder how did I not see it. But the problem is that the place I'm implementing this has object with this number as Integer type in it. — Ankit Chaudhary, Jul 31 '19 at 13:19
See [pad a string](https://stackoverflow.com/questions/388461/how-can-i-pad-a-string-in-java) for how to pad a string - or just use a while loop to prepend zeroes till the width is reached. — Adder, Jul 31 '19 at 13:24
`00078` as integer is removes automatically leading zeros. `00078` is the same as `78`. If you want leading zeros, store the value as String in java, or add them at a later point, but also as string — XtremeBaumer, Jul 31 '19 at 13:27
I successfully wrote a xls file with 00078 value but, when I edit the cell and then defocus, it goes back to 78. — Ankit Chaudhary, Jul 31 '19 at 15:36

score 0 · Answer 1 · answered Jul 31 '19 at 13:24

0

I guess writing this would have caused compilation error -

Integer number too large

data.put("2", new Object[]{ 00078, "Pankaj", "Kumar" });

In comments Adder has said it rightly

data.put("2", new Object[]{ "00078", "Pankaj", "Kumar" });

This should solve the problem.

answered Jul 31 '19 at 13:24

Saurav Kumar Singh

955
5
14

I successfully wrote a xls file with 00078 value but, when I edit the cell and then defocus, it goes back to 78. – Ankit Chaudhary Jul 31 '19 at 15:37

score 0 · Answer 2 · answered Aug 02 '19 at 18:33

If a ID must have leading zeros, then data type of this ID must be String. Numbers do not have leading zeros. The number 000123 is mathematically exactly the same as 123.

Additional in case of Excel the cells containing this ID values should have cell styles of Text format. And to prevent changing the cell style to number style by editing the cell, additional the cell style should be quote prefixed. Else editing the cell may lead to the case that 000123 changes to 123 again.

The following code shows a complete example where the ID column is protected that way.

import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.util.GregorianCalendar;

class CreateExcelSheetFromDataObjectArray {

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

  Object[][] data = new Object[][]{
   new Object[]{"ID", "NAME", "LASTNAME", "AMOUNT", "DATE"},
   new Object[]{"000123", "John", "Doe", 1234.56, new GregorianCalendar(2019, 0, 1) },
   new Object[]{"000456", "Jane", "Stiles", 7890.12, new GregorianCalendar(2019, 1, 11) },
   new Object[]{"000789", "Mary", "Major", 3456.78, new GregorianCalendar(2019, 2, 22) }
  };

  String filePath = "./Excel";

  String wantedXLFormat = 
   //"XSSF";
   "HSSF";

  try (Workbook workbook = ("XSSF".equals(wantedXLFormat))?new XSSFWorkbook():new HSSFWorkbook(); 
       FileOutputStream fileout = new FileOutputStream(filePath + (("XSSF".equals(wantedXLFormat))?".xlsx":".xls")) ) {

   DataFormat dataFormat = workbook.createDataFormat();
   CellStyle dateStyle = workbook.createCellStyle();
   dateStyle.setDataFormat(dataFormat.getFormat("DDDD, MMMM, DD, YYYY"));
   CellStyle numberStyle = workbook.createCellStyle();
   numberStyle.setDataFormat(dataFormat.getFormat("#,##0.00 \" Coins\""));

   // the cell style for the ID column has Text format and is quote prefixed
   CellStyle idStyle = workbook.createCellStyle();
   idStyle.setDataFormat(dataFormat.getFormat("@"));
   idStyle.setQuotePrefixed(true);

   Sheet sheet = workbook.createSheet(); 

   for (int r = 0; r < data.length; r++) {
    Row row = sheet.createRow(r);
    for (int c = 0; c < data[0].length; c++) {
     Cell cell = row.createCell(c);

     if (r == 0) cell.setCellValue((String)data[r][c]); // the header row, all columns are strings

     if (r > 0 && c == 0) { // the ID column
      cell.setCellValue((String)data[r][c]);
      cell.setCellStyle(idStyle);
     } else if (r > 0 && c == 3) { // the number column
      cell.setCellValue((Double)data[r][c]);
      cell.setCellStyle(numberStyle);
     } else if (r > 0 && c == 4) { // the date column
      cell.setCellValue((GregorianCalendar)data[r][c]);
      cell.setCellStyle(dateStyle);
     } else if (r > 0) { // all other columns are strings
      cell.setCellValue((String)data[r][c]);
     }
    }
   }

   for (int c = 0; c < data[0].length; c++) {
    sheet.autoSizeColumn(c);
   }

   workbook.write(fileout);
  }
 }
}

The code works tested using apache poi 4.1.0.

Excel sheet removing zero(s) for number starting with zero(s)

2 Answers2