The code below generates a 1-cell spreadsheet. When I open it, the "12345" is left-justified, like I would expect a string value to be. But if I double-click the cell and then click out of it without making any changes, it becomes the integer 12345. If I were creating the spreadsheet by hand I would have entered '12345 to force the value to be a string, and the apostrophe would be there when I double-click the cell. --Andy ========== import org.apache.poi.hssf.usermodel.*; import java.io.*; public class Test { public static void main(String[] args) { try { doMain(); } catch (Exception e) { e.printStackTrace(); } } private static void doMain() throws Exception { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("test"); HSSFRow row = sheet.createRow((short)0); HSSFCell cell = row.createCell((short)0); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue("12345"); FileOutputStream fos = new FileOutputStream("/tmp/test.xls"); wb.write(fos); fos.flush(); fos.close(); } }
Created attachment 4724 [details] Spreadsheet generated by the code I submitted.
Created attachment 4725 [details] How I would have created the cell by hand.
On a related note, if I do setCellValue("'abc'") and double-click the cell, I lose the first apostrophe, so instead of staying 'abc' it becomes abc' --Andy
This looks like a reasonably simple patch for you to submit to us... To start look at the HSSFCell.setCellValue(String). My guess is that you will need to add a ' to the string entered into the cell if the string you supply starts with a ' I dont believe that the first thing that you mention is a bug, it is normal Excel behaviour. If you want a String cell that cant be changed, then firstly fix the issue mentioned above and then write cell.setCellValue("'12345") in your code. Jason
The problem with prepending an extra ' is that it will show up in Excel. For example, setCellValue("''abc'") causes this to appear in Excel: ''abc' When I double-click the cell and then exit it without changing it, the display changes to... 'abc' ...and thereafter behaves the way I want. But of course I shouldn't have to double-click every cell to make it appear correctly. As for the original problem, if I use '12345 then it starts out displayed as '12345 (which is not the intended effect) and only becomes 12345 after the double-click. It seems to me we need to know how *Excel* decides to prepend the ', which it should only do when I double-click the cell for editing. --Andy
Ok Andy, Seems like you have a good understanding of the combinations. Do you feel up to creating a patch? If so can I suggest that you use the biffviewer utility to display the file from POI before opening in excel. Then open the file in excel and produce the desired behaviour and then compare the outputs. It is likely that the difference you are interested in will be in either in the SST record or the LABELSST. OR, if you are not confident in producing a patch then let me know and i may have a look at it. Jason
I'd be glad to take a shot at it -- checking out the source tree now. I've never submitted a patch before, but the instructions look quite straightforward. I assume there is no great hurry. --Andy
I wonder if the thing has a dataformat of NOT-general if this still ahppens. This is too minor to hold up 2.0
Andy O. is right. This is a little gotcha with excel. The default data format is general which looks at the value and tries to determine what it is. I doesn't default to what the cell type is (part of the reason is there is not direct way through the interface to do that). The solution is to set the data format for that particular cell to text which then makes it come out properly. Setting the format to text also fixes the quoting problem. This comes about because you put a single ' in front of a number to tell excel that it is text and not to treat it as a number. This really isn't a bug as much as a feature of excel.