poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Eric Douglas <edoug...@blockhouse.com>
Subject Re: How do you code cell striping?
Date Tue, 13 Dec 2016 13:50:24 GMT
I don't want a loop.  I don't want formula values on every cell.
I want a conditional format applied to the entire sheet to color every
other row.  If you sort columns and change the order of the rows it should
still color every other row.
Can we attach files on this list?  Attachments would show exactly what the
output looks like.
To reproduce what I'm explaining, here's the code of the first part.

import java.awt.Color;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.ss.usermodel.ConditionalFormattingRule;
import org.apache.poi.ss.usermodel.PatternFormatting;
import org.apache.poi.ss.usermodel.SheetConditionalFormatting;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class TestExcel {

     public static void main(String[] args) throws IOException {
          XSSFWorkbook wb = new XSSFWorkbook();
          XSSFSheet curSheet = wb.createSheet("Sheet " +
(wb.getNumberOfSheets() + 1));
          XSSFCell c1 = curSheet.createRow(0).createCell(0);

c1.setCellValue(wb.getCreationHelper().createRichTextString("Color this
cell"));
          XSSFCell c2 = curSheet.createRow(1).createCell(0);
          c2.setCellValue(wb.getCreationHelper().createRichTextString("No
color"));
          XSSFCell c3 = curSheet.createRow(2).createCell(0);

c3.setCellValue(wb.getCreationHelper().createRichTextString("Color this
cell"));
          XSSFCell c4 = curSheet.createRow(3).createCell(0);
          c4.setCellValue(wb.getCreationHelper().createRichTextString("No
color"));
          CellRangeAddress[] regions =
{CellRangeAddress.valueOf("A1:AMJ1048576")};
          SheetConditionalFormatting sheetCF =
curSheet.getSheetConditionalFormatting();
          ConditionalFormattingRule rule1 =
sheetCF.createConditionalFormattingRule("ISEVEN(ROW())");
          PatternFormatting fill1 = rule1.createPatternFormatting();
          final XSSFColor customColor = new XSSFColor(new Color(228, 247,
247));
          fill1.setFillForegroundColor(customColor);
          fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
          sheetCF.addConditionalFormatting(regions, rule1);
          File fi = new File("output.xlsx");
          if (fi.exists()) {
               fi.delete();
          }
          FileOutputStream output = new FileOutputStream(fi);
          wb.write(output);
          wb.close();
          output.flush();
          output.close();
     }
}

Now to the second part I can't figure out how to code:
1) I open the output file in LibreOffice Calc, click the blank space on the
row and column heading bar to select all cells.
2) I select Styles and Formatting from the tool bar on the right.
3) I select the option New Style, give it a name, select OK.
4) I right click the style I just created, select Modify, select a
background color, select OK to accept.
5) With the style I just created still selected, I select the New Style
option, assign a different name.  This second style shows under the first.
6) I right click this second style, Modify, and select No Fill background.
7) From the menu I select Format, Conditional Formatting, Manage.  This
shows the format I created from the above program.
8) I Edit this condition, select Apply Style = the first style I just
created.

Rows are now striped.  I select column A, select Data - Sort from the menu,
you see the rows change order and color is still applied to only even
numbered rows.


On Mon, Dec 12, 2016 at 4:33 PM, Nick Burch <apache@gagravarr.org> wrote:

> On Mon, 12 Dec 2016, Eric Douglas wrote:
>
>> I found one sample that shows how to code the condition using
>> org.apache.poi.ss.usermodel.SheetConditionalFormatting.addConditionalFormatting()
>> to put in the formula that would color each cell if it's in an even
>> numbered row, but I'm having trouble figuring out the API to apply the
>> formula to every cell on the worksheet.
>>
>
> For every cell on a sheet, just give a cellrangeaddress that covers the
> whole extent
>
> For every formula cell, you'd need to loop over all cells checking the
> cell type, then add just those
>
> Nick
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message