Toggle the colors of a column using the java library - apache poi

Question

Toggle the colors of a column using the java library - apache poi

Navigation

#1 by (1 votes)
#2 by (0 votes)

2

I'm using java with the api poi library to create an excel spreadsheet.
I need to keep alternating colors, from the 1st column (that is from department), between yellow and blue, when the data are different too.Ex .:

Dep = 1 - Color = yellow Dep = 1 - Color = yellow
Dep = 2 - Color = blue
Dep = 2 - Color = blue
Dep = 3 - Color = yellow
(and so on)

Every time I change a department I need to change the color, however with the algorithm I thought or got the whole result in yellow or all in blue. I can not think of another algorithm.

        HSSFCellStyle style = workbook.createCellStyle();
        HSSFPalette palette = workbook.getCustomPalette();
        int mudou = 0;

        for (int i=0; i <teste.length; i++) 
        {
            row = sheet.createRow(i+5);

            if (mudou == 0 )
            {                   

                palette.setColorAtIndex(palette.getColor(36).getIndex(), (byte) 255, (byte) 255 , (byte) 204 ); //amarelo
                style.setFillForegroundColor(palette.getColor(36).getIndex());
                style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

                if (i>0 && !teste[i].getDepto().equals(teste[i-1].getDepto()) )
                {
                    mudou = 1;
                }
            }
            if (mudou == 1)
            {
                palette.setColorAtIndex(palette.getColor(8).getIndex(), (byte) 204, (byte) 255, (byte) 255); //azul
                style.setFillForegroundColor(palette.getColor(8).getIndex());
                style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

                if(i+1<teste.length && !teste[i].getDepto().equals(teste[i+1].getDepto()) )
                {
                    mudou = 0;
                }
            }           

            row.createCell(0).setCellValue(teste[i].getDepto());
            row.getCell(0).setCellStyle(style);

            row.createCell(1).setCellValue(teste[i].getSigla());
            row.createCell(2).setCellValue(teste[i].getCiclo());
            row.createCell(3).setCellValue(teste[i].getState());
            row.createCell(4).setCellValue(teste[i].getDataEntradaEstado());
            row.createCell(5).setCellValue("");
            row.createCell(6).setCellValue(teste[i].getId());
        }

java style apache-poi

asked by anonymous 20.09.2017 / 20:23

2 answers

Record count How to put links from social networks to share the link of the page of a post?

score 1 · Answer 1

A very simple way is to create a method for this:

private static void estilizarCor(CellStyle estilo, Integer indiceColuna) {
  IndexedColors[] cores = new IndexedColors[]{
    IndexedColors.AQUA, IndexedColors.RED
  };

  short indiceCor = 0;

  for (Integer indice = cores.length - 1; indice >= 0; indice--) {
    if (indice % indiceColuna == 0) {
      indiceCor = cores[indice].getIndex();
      break;
    }
  }

  estilo.setFillForegroundColor(indiceCor);
  estilo.setFillPattern(FillPatternType.SOLID_FOREGROUND);
}

And use in your code as follows:

estilizarCor(style, i);

score 0 · Answer 2

I've got some galera ... It must be somehow more practical, but I'll post here what I've done for anyone who needs it:

        int mudou = 0;
        String depPassado = "";

        for (int i=0; i <teste.length; i++) 
        {
            row = sheet.createRow(i+5);

            //TODO alternando a cor entre os Deps
            if (i == 0)
            {
                depPassado = teste[i].getDepto();

                HSSFCellStyle style = workbook.createCellStyle();
                HSSFPalette palette = workbook.getCustomPalette();

                palette.setColorAtIndex(palette.getColor(36).getIndex(), (byte) 255, (byte) 255 , (byte) 204 ); //amarelo
                style.setFillForegroundColor(palette.getColor(36).getIndex());
                style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

                row.createCell(0).setCellStyle(style);
            }
            else if (depPassado.equals(teste[i].getDepto()))
            {
                if (mudou == 0)
                {
                    HSSFCellStyle style = workbook.createCellStyle();
                    HSSFPalette palette = workbook.getCustomPalette();

                    palette.setColorAtIndex(palette.getColor(36).getIndex(), (byte) 255, (byte) 255 , (byte) 204 ); //amarelo
                    style.setFillForegroundColor(palette.getColor(36).getIndex());
                    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

                    row.createCell(0).setCellStyle(style);
                }

                else 
                {
                    HSSFCellStyle style = workbook.createCellStyle();
                    HSSFPalette palette = workbook.getCustomPalette();

                    palette.setColorAtIndex(palette.getColor(8).getIndex(), (byte) 204, (byte) 255, (byte) 255); //azul
                    style.setFillForegroundColor(palette.getColor(8).getIndex());
                    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

                    row.createCell(0).setCellStyle(style);
                }
            }
            else
            {
                if (mudou == 0) mudou = 1;
                else mudou = 0;
                depPassado = teste[i].getDepto();

                if (mudou == 0)
                {
                    HSSFCellStyle style = workbook.createCellStyle();
                    HSSFPalette palette = workbook.getCustomPalette();

                    palette.setColorAtIndex(palette.getColor(36).getIndex(), (byte) 255, (byte) 255 , (byte) 204 ); //amarelo
                    style.setFillForegroundColor(palette.getColor(36).getIndex());
                    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

                    row.createCell(0).setCellStyle(style);
                }

                else 
                {
                    HSSFCellStyle style = workbook.createCellStyle();
                    HSSFPalette palette = workbook.getCustomPalette();

                    palette.setColorAtIndex(palette.getColor(8).getIndex(), (byte) 204, (byte) 255, (byte) 255); //azul
                    style.setFillForegroundColor(palette.getColor(8).getIndex());
                    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

                    row.createCell(0).setCellStyle(style);
                }

            }