CODEWITHSUNDEEP
javaapache-poixls
Ankit Chaudhary
Ankit Chaudhary

Reputation: 23

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

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.

Upvotes: 2

Views: 3314

Answers (3)

Kapil Thakkar
Kapil Thakkar

Reputation: 870

You just need to prepend = sign before data. Ex: ="00082"

"=" + "\"0000882\""

Upvotes: 0

Axel Richter
Axel Richter

Reputation: 61915

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.

Upvotes: 1

Saurav Kumar Singh
Saurav Kumar Singh

Reputation: 1478

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.

Upvotes: 0

Related Questions