3

I am using apache poi 3.17 to create an Excel file.

Different columns may contain different type of values and I would like to style them accordingly. Rather than creating the style every time, I try to use the same style( to avoid the unnecessary object creation) and change the necessary properties. For eg: some cell I want to make italics, some bold, some with yellow color, some with underline etc..

But to my surprise, I found that style is not changing.

Below is a sample code where I try to set row 1 with 'yellow color' and rest with 'red color' but in the generated excel all rows are red.

import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Hyperlink;
import org.apache.poi.xssf.usermodel.*;

import java.awt.*;
import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;


public class WriteExcelBasic {
public static void main(String[] args) throws IOException {

    String excelFileName = "/Users/username/Test3.xlsx";
    FileOutputStream fos = new FileOutputStream(excelFileName);


    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFCellStyle style = wb.createCellStyle();

    XSSFSheet sheet = wb.createSheet("sheet");
    Font urlFont = wb.createFont();
    urlFont.setFontHeight((short)(9*20));
      style.setFont(urlFont);
    for (int r = 0; r < 3; r++) {
        XSSFRow row = sheet.createRow(r);


        for (int c = 0; c < 3; c++) {
            XSSFCell cell = row.createCell(c);


            Hyperlink link = wb.getCreationHelper().createHyperlink(HyperlinkType.URL);
            String ss = "http://news.google.com/news/headlines?ned=us&hl=en";
                  link.setAddress(ss);
            cell.setHyperlink(link);
            cell.setCellValue(ss);
            if(r == 1) {
                System.out.println("In yellow");
                style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                style.setFillForegroundColor(new XSSFColor(Color.YELLOW));
            } else {
                System.out.println("In red");
                style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                style.setFillForegroundColor(new XSSFColor(Color.RED));
            }
            cell.setCellStyle(style);


        }
    }

    try (ByteArrayOutputStream baos = new ByteArrayOutputStream()) {
        wb.write(baos);
        byte[] myByteArray = baos.toByteArray();
        fos.write(myByteArray);
        fos.flush();
    }
    finally {
        wb.close();
        fos.close();
    }
}
}

To solve this issue, I could create 2 styles separately and apply them based on the condition but in a practical case I have to create 24 different styles but for that, I have to write so much redundant code.

If I want to create a new style with a different property then I have to create another 24 styles with this new property. Therefore the complexity will go exponentially.

Therefore, can anyone provide some suggestion on this issue?

Yash
  • 11,486
  • 4
  • 19
  • 35
nantitv
  • 3,539
  • 4
  • 38
  • 61
  • 2
    The reason all the cells turn red is because the same style is being shared amongst all the 3 cells. So even though your code does change the color of the cells in row 1,it reverts back to red when you change it for the next rows. I'm afraid the only way to go about it is to add different styles. If redundancy of code is the matter, then why not just create a map of styles, which can be utilized wherever necessary ? As given here: https://stackoverflow.com/a/37855933/6620886 – mahesh Rao Nov 24 '17 at 09:50
  • @maheshRao Thanks for the reply & link. Not just the redundancy, if I want to create a new style where I want to just change one property and then I may want to store both the old styles and new styles the this may leads to exponential number of styles. In the provided link I could see a cloning of the style , it should solve most of the problem for now. – nantitv Nov 24 '17 at 11:00

0 Answers0