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

Asked

Viewed 614 times

2

I am using java with apache poi library to create an excel spreadsheet.
I need to keep alternating colors, 1st column (which is department), between yellow and blue, when the data are different as well. Ex.:

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

Every time I change a department I need to change the color, but with the algorithm I thought or have the result all in yellow or all in blue. I can’t 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 apache-poi style

  • Has any response helped solve the problem and can address similar questions from other users? If so, make sure to mark the answer as accepted. To do this just click on the left side of it (below the indicator of up and down votes).

2 answers

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);

0

I got guys... There must be some way much more practical, but I’ll post here what I did for those who need:

        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);
                }

            }

Browser other questions tagged java apache-poi style

You are not signed in. Login or sign up in order to post.