Bug 51037 - setDefaultColumnStyle() in XSSFSheet not working
Summary: setDefaultColumnStyle() in XSSFSheet not working
Status: NEEDINFO
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.8-dev
Hardware: PC All
: P2 normal with 8 votes (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
: 58461 (view as bug list)
Depends on:
Blocks:
 
Reported: 2011-04-07 05:20 UTC by M8R-3mye921
Modified: 2016-08-26 10:40 UTC (History)
3 users (show)



Attachments
Full test class (2.16 KB, text/x-java-source)
2011-04-07 05:20 UTC, M8R-3mye921
Details

Note You need to log in before you can comment on or make changes to this bug.
Description M8R-3mye921 2011-04-07 05:20:24 UTC
Created attachment 26865 [details]
Full test class

The method setDefaultColumnStyle(int column, CellStyle style) is not having any effect in an XSSFWorkbook. This is my test code:

public static void columnStyles(boolean xml) throws IOException {
	Workbook wb = xml ? new XSSFWorkbook() : new HSSFWorkbook();
	
	CellStyle blueStyle = wb.createCellStyle();
	blueStyle.setFillForegroundColor(IndexedColors.AQUA.getIndex());
	blueStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
	
	CellStyle pinkStyle = wb.createCellStyle();
	pinkStyle.setFillForegroundColor(IndexedColors.PINK.getIndex());
	pinkStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
	
	Sheet s1 = wb.createSheet("Pretty columns");
	
	s1.setDefaultColumnStyle(4, blueStyle);
	s1.setDefaultColumnStyle(6, pinkStyle);
	
	Row r = s1.createRow(7);
	r.createCell(1).setCellStyle(pinkStyle);
	r.createCell(8).setCellStyle(blueStyle);
	
	fill(s1.createRow(3), "The quick brown fox jumps over the lazy dog".split("\\s+"));
	save(wb, (xml ? "columnStyles.xlsx" : "columnStyles.xls"));
}

The styles are applied to the individual cells via setCellStyle(), but not to the columns.

Everything works as expected when using an HSSFWorkbook.
Comment 1 Nick Burch 2011-04-07 07:11:29 UTC
There's code in XSSFSheet that looks to be doing the right thing, no it isn't missing code

Could you try one thing to help track down what's wrong? Steps are:
* Create a .xlsx file with a different style
* Take a copy
* Using Excel, apply that existing style as the default to a column
* Using POI, apply that existing style as the default to a column
* Unzip both resuling files (.xlsx is a zip of xml files)
* Try to spot what POI did differently to Excel

Once we can narrow down what Excel is doing differently, we can then try to fix it in POI.
Comment 2 M8R-3mye921 2011-04-07 10:34:59 UTC
If I look at "sheet1.xml", the Excel-generated file has
	<dimension ref="B1:E1"/>

where the POI-generated file had
	<dimension ref="B1"/>

and Excel has
	<col min="2" max="2" width="9.140625" style="1"/>
	<col min="5" max="5" width="9.140625" style="1"/>

where POI has
	<col min="2" max="2" style="1" width="9.140625" collapsed="false"/>
	<col min="5" max="5" style="0"/>

"styles.xml" is not substantially different; the custom style is stored as
	<cellStyle name="PurpleStyle" xfId="1"/>

In both cases, column B (2) was the column which already had the style. Column E (5) was the one which I copied the style to in Excel or POI respectively.

So it looks like POI is creating a column entry in the worksheet file, but not storing the style.

Hope that helps.
Comment 3 M8R-3mye921 2011-04-07 10:47:59 UTC
Update: I've just simplified my test code, and this time POI managed to update the spreadsheet correctly.

If I use "workbook.getCellStyleAt((short) 1)" to reference the style, then it works. If I use "cell.getCellStyle()", it doesn't.
Comment 4 M8R-3mye921 2011-04-07 11:59:22 UTC
*Correction*!

The original code does in fact fill the columns as I asked. However, the column widths are then set to 0, which made me originally think that the code had not done anything.

In addition, when I unhide the columns, I find that the newly-created cells had not respected the default column style, but had been created with a blank (unfilled) background.
Comment 5 Nick Burch 2011-04-14 14:08:21 UTC
I've just added a unit test in r1092423 for the case you describe. However, it does all seem to be working fine for me - the column gets set up with the style as you'd expect. I can't replicate your problem of the style not being set.

Are you able to compare my test code to yours to see what's happening differently?
Comment 6 Bill Somerville 2011-04-27 09:49:01 UTC
(In reply to comment #5)
> I've just added a unit test in r1092423 for the case you describe. However, it
> does all seem to be working fine for me - the column gets set up with the style
> as you'd expect. I can't replicate your problem of the style not being set.
> 
> Are you able to compare my test code to yours to see what's happening
> differently?

Hi.  I'm having what I think to be the same problem.  The code below reliably reproduces the problem.  Note that the same code works fine (as expected) with HSSFWorkbook, and note also the workaround:

import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import java.io.FileOutputStream;

public class Test
{

    public static void main(String args[])
    {
        try
        {
            Test test = new Test();
            test.run();
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
    }

    Test()
    {
    }

    void run()
            throws Exception
    {
	// Problem: column 0 ends up with zero width and white background in XSSFWorkbook; looks OK in HSSFWorkbook
        Workbook workbook = new XSSFWorkbook();
	FileOutputStream stream = new FileOutputStream("C:\\Temp\\Foo.xlsx");
        //Workbook workbook = new HSSFWorkbook();
	//FileOutputStream stream = new FileOutputStream("C:\\Temp\\Foo.xls");
	Sheet sheet = workbook.createSheet();
	CellStyle styleGray = workbook.createCellStyle();

	styleGray.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
	styleGray.setFillPattern(CellStyle.SOLID_FOREGROUND);

	sheet.setDefaultColumnStyle(0, styleGray);

	Row row = sheet.createRow(0);
	Cell cell;
	int column = 0;
	cell = row.createCell(column);
	cell.setCellValue("I'm gray");
	// Workaround: cell.setCellStyle(sheet.getColumnStyle(column));
	// Workaround: sheet.autoSizeColumn(column);

	cell = row.createCell(++column);
	cell.setCellValue("I'm white");
	workbook.write(stream);
	stream.flush();
    }
}
Comment 7 sumedh 2013-03-14 06:55:48 UTC
I tried with SXSSFSheet and it doesn't work there either.
Comment 8 maksim.tsilayeu 2014-07-11 13:24:09 UTC
it seems like an issue with generated xl\worksheets\sheet1.xml

If you use sheet.setDefaultColumnStyle then generated xml is missing "s" attribute - which is used for style reference, so it looks like:
&lt;c r="I2" t="n"&gt;&lt;v&gt;41830.0&lt;/v&gt;&lt;/c&gt;

However if you use cell.setCellStyle then style attribute is generated, so it looks like: 
&lt;c r="I2" t="n" s="1"&gt;&lt;v&gt;41830.0&lt;/v&gt;&lt;/c&gt;

It would be nice to have this issue fixed :)
Comment 9 Nick Burch 2014-07-14 18:17:36 UTC
(In reply to maksim.tsilayeu from comment #8)
> it seems like an issue with generated xl\worksheets\sheet1.xml
> 
> If you use sheet.setDefaultColumnStyle then generated xml is missing "s"
> attribute - which is used for style reference, so it looks like:
> &lt;c r="I2" t="n"&gt;&lt;v&gt;41830.0&lt;/v&gt;&lt;/c&gt;
> 
> However if you use cell.setCellStyle then style attribute is generated, so
> it looks like: 
> &lt;c r="I2" t="n" s="1"&gt;&lt;v&gt;41830.0&lt;/v&gt;&lt;/c&gt;

Making changes to the default style on a column won't alter the cells within that column. If you want to style on specific cell (c element in xlsx) you need to use cell.setCellStyle on that specific cell.
Comment 10 Dominik Stadler 2015-09-30 12:11:18 UTC
*** Bug 58461 has been marked as a duplicate of this bug. ***
Comment 11 Dominik Stadler 2015-09-30 12:12:19 UTC
Bug 58461 handles basically the same thing and contains standalone code to reproduce the problem.
Comment 12 Nick Burch 2016-01-13 07:43:23 UTC
For anyone wanting to investigate this further, there looks to be some good information at http://stackoverflow.com/questions/34463072/i-want-to-arrange-entire-cells-in-specific-column-instead-of-individual-cells on this area