Can't set type for excel cell in java-CodePudding

I am using org.apache.poi for excel interaction through Java and I am using SXSSF (not any other format like HSSF etc.). Here is how I want to set type.

private void setTechSheet(SXSSFWorkbook workbook, String entityJson) {
        SXSSFSheet tech = workbook.createSheet("tech");
        SXSSFRow jwtRow = tech.createRow(0);
        SXSSFRow SecondRow = tech.createRow(1);
        jwtRow.createCell(1).setCellValue(entityJson);
    }

If to put this in the 4th line of method:

jwtRow.createCell(1).setCellType(CellType.STRING).setCellValue(entityJson),

it won't let me set value. As I read from tutorial, the cell will be the type of datas I write (string in my example). But when I open excel and click on my cell it still shows general type. How can I solve this problem? I want the cell to be any type, but not general.

CodePudding user response：

General is not the type but the data format. So you have a cell containing a text value in General data format. That is the default. There is nothing wrong with this.

The special Text data format only is needed when one needs to store numbers as text for example.

If you need Text data format, then setting @ as data format is necessary. For this, the cell needs a cell style having that data format. And because cell styles are stored on workbook level and shared between all cells having that date format, each data format needs to be created only once per workbook and then applied to all cells which shall use this data format.

SXSSFWorkbook workbook...
...
DataFormat format = workbook.createDataFormat();
CellStyle textStyle = workbook.createCellStyle();
textStyle.setDataFormat(format.getFormat("@"));
...
setTechSheet(workbook, "1234", textStyle);
...
private void setTechSheet(SXSSFWorkbook workbook, String entityJson, CellStyle textStyle) {
 SXSSFSheet tech = workbook.createSheet("tech");
 SXSSFRow jwtRow = tech.createRow(0);
 SXSSFCell cell = jwtRow.createCell(1);
 cell.setCellValue(entityJson);
 cell.setCellStyle(textStyle);
}
...

Following complete example shows when data format Text is needed.

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.*;

public class SXSSFTextDataFormat {
    
 private static void setTechSheet(SXSSFWorkbook workbook, Object[][] techSheetData, CellStyle textStyle) {
  SXSSFSheet tech = workbook.createSheet("tech");
  int r = 0;
  for (Object[] dataRow : techSheetData) {
   SXSSFRow row = tech.createRow(r);
   int c = 0;
   for (int i = 0; i < dataRow.length; i =2) {
    SXSSFCell cell = row.createCell(c);
    Object data = dataRow[i];
    String format = (String)dataRow[i 1];
    if (data instanceof Number) {
     cell.setCellValue(((Number)data).doubleValue());
    } else {
     cell.setCellValue(String.valueOf(data));   
    }
    if ("Text".equals(format)) {
     cell.setCellStyle(textStyle);
    }
    c  ;
   }
   r  ;
  }
 }

 public static void main(String args[]) throws Exception {
     
  Object[][] techSheetData = new Object[][] {
   new Object[] {"A1", "General", new java.math.BigInteger("12345678901234567890"), "General"},
   new Object[] {"A2", "General", "12345678901234567890", "Text"},
   new Object[] {"A3", "General", "12345678901234567890", "General"},
  };
  
  try ( SXSSFWorkbook workbook = new SXSSFWorkbook();
        java.io.FileOutputStream out = new java.io.FileOutputStream("./Excel.xlsx")) {
           
   DataFormat format = workbook.createDataFormat();
   CellStyle textStyle = workbook.createCellStyle();
   textStyle.setDataFormat(format.getFormat("@"));
   
   setTechSheet(workbook, techSheetData, textStyle);
            
   workbook.write(out);
   workbook.dispose();
  }
 }
}

The big integer in B1will not be stored in Excel properly because Excel only stores numbers in double precision.

The big integer string in B2 is formatted using Text data format. So one can edit that cell in Excel's GUI without destroying the format.

The big integer string in B3 is formatted using General data format. So if one edits that cell in Excel's GUI this will destroy the format and Excel converts the number into a double value.