Apache Poi and excel generation basics

Generate and edit excel xlsx file with Apache Poi

Albert Lacambra BasilAlbert Lacambra Basil

Load a workbook

A woorkbook represents an excel file
XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream("/path/to/xlsx/file"));

Load and remove a sheet

Load a sheet named "table 1"
XSSFSheet sheet = workbook.getSheet("table 1");
removes sheet on position 1. The first sheet is position 0.
workbook.removeSheetAt(1);

Load Rows and Cells

You can work directly with row and cell indexes which are Integers and begin per the index 0.

Load rows and cells using an index
XSSFRow row = sheet.getRow(rowIndex);
XSSFCell cell = row.getCell(cellIndex);

Another option that helps to work together with an excel sheet as a template are CellReferences. The can reference cell in a more human way, using excel coordinates.

Load rows and cells using a CellReference
//Represent indexes (0, 0)
CellReference reference = new CellReference("A1");

XSSFRow row = sheet.getRow(reference.getRow());
XSSFCell cell = row.getCell(reference.getCol());

Create a color map for you application.

Definae a color map with the colors that you are gone a use in your application.

public class MyIndexedColorMap implements IndexedColorMap {

  public static final byte RED = 0;
  public static final byte GREEN_ = 1;
  public static final byte YELLOW = 2;
  public static final byte NORMAL = 3;


  private static final byte[][] colorIndex;

  static {
    colorIndex = new byte[4][3];
    colorIndex[RED] = new byte[]{(byte) 255, 0, 0};
    colorIndex[GREEN_] = new byte[]{0, (byte) 255, 0};
    colorIndex[YELLOW] = new byte[]{(byte) 255, (byte) 255, 0};
    colorIndex[NORMAL] = new byte[]{0, 0, 0};
  }

  @Override
  public byte[] getRGB(int index) {
    if (colorIndex == null || index < -127 || index >= colorIndex.length) return null;
    return colorIndex[index];
  }
}

It is also possible to use the DefaultInsdexedColorMap.class which can be used with a variety of predefined colors.

On HSSFColor.HSSFColorPredefined.class
   public enum HSSFColorPredefined {
        BLACK                (0x08,   -1, 0x000000),
        BROWN                (0x3C,   -1, 0x993300),
        OLIVE_GREEN          (0x3B,   -1, 0x333300),
        DARK_GREEN           (0x3A,   -1, 0x003300),
        DARK_TEAL            (0x38,   -1, 0x003366),
        DARK_BLUE            (0x12, 0x20, 0x000080),
        INDIGO               (0x3E,   -1, 0x333399),
        GREY_80_PERCENT      (0x3F,   -1, 0x333333),
        ORANGE               (0x35,   -1, 0xFF6600),
        DARK_YELLOW          (0x13,   -1, 0x808000),
        GREEN                (0x11,   -1, 0x008000),
        TEAL                 (0x15, 0x26, 0x008080),
        BLUE                 (0x0C, 0x27, 0x0000FF),
        BLUE_GREY            (0x36,   -1, 0x666699),
        GREY_50_PERCENT      (0x17,   -1, 0x808080),
        RED                  (0x0A,   -1, 0xFF0000),
        LIGHT_ORANGE         (0x34,   -1, 0xFF9900),
        LIME                 (0x32,   -1, 0x99CC00),
        SEA_GREEN            (0x39,   -1, 0x339966),
        AQUA                 (0x31,   -1, 0x33CCCC),
        LIGHT_BLUE           (0x30,   -1, 0x3366FF),
        VIOLET               (0x14, 0x24, 0x800080),
        GREY_40_PERCENT      (0x37,   -1, 0x969696),
        PINK                 (0x0E, 0x21, 0xFF00FF),
        GOLD                 (0x33,   -1, 0xFFCC00),
        YELLOW               (0x0D, 0x22, 0xFFFF00),
        BRIGHT_GREEN         (0x0B,   -1, 0x00FF00),
        TURQUOISE            (0x0F, 0x23, 0x00FFFF),
        DARK_RED             (0x10, 0x25, 0x800000),
        SKY_BLUE             (0x28,   -1, 0x00CCFF),
        PLUM                 (0x3D, 0x19, 0x993366),
        GREY_25_PERCENT      (0x16,   -1, 0xC0C0C0),
        ROSE                 (0x2D,   -1, 0xFF99CC),
        LIGHT_YELLOW         (0x2B,   -1, 0xFFFF99),
        LIGHT_GREEN          (0x2A,   -1, 0xCCFFCC),
        LIGHT_TURQUOISE      (0x29, 0x1B, 0xCCFFFF),
        PALE_BLUE            (0x2C,   -1, 0x99CCFF),
        LAVENDER             (0x2E,   -1, 0xCC99FF),
        WHITE                (0x09,   -1, 0xFFFFFF),
        CORNFLOWER_BLUE      (0x18,   -1, 0x9999FF),
        LEMON_CHIFFON        (0x1A,   -1, 0xFFFFCC),
        MAROON               (0x19,   -1, 0x7F0000),
        ORCHID               (0x1C,   -1, 0x660066),
        CORAL                (0x1D,   -1, 0xFF8080),
        ROYAL_BLUE           (0x1E,   -1, 0x0066CC),
        LIGHT_CORNFLOWER_BLUE(0x1F,   -1, 0xCCCCFF),
        TAN                  (0x2F,   -1, 0xFFCC99),


    }
load a color
//Load a color from MyIndexedColorMap.class
IndexedColorMap COLOR_MAP = new MyIndexedColorMap();
xssfColor.setRGB(COLOR_MAP.getRGB(color));

//Use the HSSFColorPredefined.class
xssfColor.setRGB(DefaultIndexedColorMap.getDefaultRGB(HSSFColor.HSSFColorPredefined.BLACK.getIndex()));

Set background color to a cell fromma cloned CellStyle

XSSFCellStyle style = (XSSFCellStyle) cell.getCellStyle().clone();

style.cloneStyleFrom(cell.getCellStyle());
XSSFColor xssfColor = new XSSFColor(COLOR_MAP);
xssfColor.setRGB(COLOR_MAP.getRGB(colorIndex));
style.setFillForegroundColor(xssfColor);
cell.setCellStyle(style);

Add a cell comment

CreationHelper factory = wrkbook.getCreationHelper();
ClientAnchor anchor = factory.createClientAnchor();
Drawing drawing = cell.getSheet().createDrawingPatriarch();

anchor.setCol1(cell.getColumnIndex());
anchor.setCol2(cell.getColumnIndex() + 1);

anchor.setRow1(cell.getRowIndex());
anchor.setRow2(cell.getRowIndex() + 1);

Comment comment = drawing.createCellComment(anchor);
XSSFRichTextString xssfRichTextString = new XSSFRichTextString("some text in first row\n");
xssfRichTextString.append("text in second row");
comment.setString(xssfRichTextString);

Apply color to the text in a cell

XSSFFont xssfFont = new XSSFFont();
XSSFColor xssfColor = new XSSFColor(COLOR_MAP);
byte color = MyIndexedColorMap.RED;
xssfColor.setRGB(COLOR_MAP.getRGB(color));
xssfFont.setColor(xssfColor);
richTextString.append("a RED text", xssfFont);

XSSFFont xssfFont = new XSSFFont();
XSSFColor xssfColor = new XSSFColor(COLOR_MAP);
byte color = MyIndexedColorMap.GREEN;
xssfColor.setRGB(COLOR_MAP.getRGB(color));
xssfFont.setColor(xssfColor);
richTextString.append("a GREEN text", xssfFont);

Copy a row from one sheet to another

XSSFSheet sheet1 = workbook.getSheet("table 1");
XSSFSheet sheet2 = workbook.getSheet("table 2");

XSSFRow sourceRow = sheet2.getRow(0);

int targetRowIndex = 0;
sheet1.copyRows(
    Collections.singletonList(sourceRow),
    targetRowIndex,
    new CellCopyPolicy.Builder().cellStyle(true).cellValue(true).cellFormula(true).build()
);

With this method you can copy rows from one sheet to another. That allows you to create some templates and use it directly to another sheet. You can remove at the end the templates sheet, leaving only the reals content.

However, you need to be aware, that if the source row index and the target row index are the same, Poi will throw and exception.

It will treat it as if you try to copy over the source row. That happens because Poi is not aware the the rows belongs to a different sheet.

  •   Tags:

Clean non excluded git branches

Remove branches that have note been explicitly excluded (e.g. master) from the local repo

Albert Lacambra BasilAlbert Lacambra Basil

Clean all branches branches except master and development

git branch | grep -v '^* master$' | grep -v '^  master$' | grep -v '^* development$' | grep -v '^  development$' | xargs git branch -D

Clean all branches merged branches except master and development

git branch --merged | grep -v '^* master$' | grep -v '^  master$' | grep -v '^* development$' | grep -v '^  development$' | xargs git branch -d

Create Openshift Jenkins pipelines

Steps to get jenkins piepliens tunning on Openshift

Albert Lacambra BasilAlbert Lacambra Basil

Create yaml template for the pipeline

Create the pipeline resource template pipeline.yaml:

kind: BuildConfig
apiVersion: v1
metadata:
  name: deploy-downloader
  labels:
    app: deploy-downloader
spec:
  resources:
    requests:
      cpu: 2000Mi
      memory: 1G
  runPolicy: Parallel
  source:
    git:
      ref: master
      uri: "https://github.com/alacambra/youtubedl-java-api.git"
    type: Git
  strategy:
    type: JenkinsPipeline
    jenkinsPipelineStrategy:
      jenkinsfilePath: deploy.oc.Jenkinsfile

Create the pipeline template resource

oc create -f pipeline.yaml

Update the pipeline template resource

oc replace -f pipeline.yaml

Create the jenkins pipeline

Create the deploy.oc.Jenkinsfile

def applicationName = "downloader";


pipeline{
    agent {
        label 'maven'
    }

    stages{
        stage('build') {
            steps{
                sh script: "mvn clean test"
            }
        }

        stage('copy ui') {
            steps{
                sh script: "cp -Rf ui/src/* downloader-server/src/main/webapp/app"
            }
        }

         stage('package') {
            steps{
                sh script: "mvn package -Dmaven.test.skip=true"
            }
        }

        stage('s2i build'){
            steps{
                script{
                    openshift.withCluster(){
                        openshift.withProject(){
                            def build = openshift.selector("bc", applicationName);
                            def startedBuild = build.startBuild("--from-file=\"./downloader-server/target/downloader.war\"");
                            startedBuild.logs('-f');
                            echo "${applicationName} build status: ${startedBuild.object().status}";
                        }
                    }
                }
            }
        }
    }
}

Keyset pagination

Make an effective pagination without offset using a table index

Albert Lacambra BasilAlbert Lacambra Basil

Motivation of keyset pagination

When we use OFFSET and LIMIT for pagination, the database needs to fetch all the results that are being skipped.

That is just a waste of effort.

Using keyset pagination, we instruct the database to begin to count from a given index.

In this way, no wasted results need to be fetched. For more deeper information visit Use the Index, LUKE!

Basics about keyset pagination

We just need to indicate the database where to begin and how should the result set be ordered.

That means to pass some indexed value.

SELECT b.title
FROM Books AS b
WHERE
AND b.id < :last_seen_id
ORDER BY id DESC
LIMIT 10 ROWS ONLY

Here he is saying go to the last_seen_id and give me back all books with a smaller id. The fact that the id is indexed, is what is making the magic.

Paginating with strings

Nice to order by id. However, most of the time is useless. Normally, if we want to list books, we are gone an order it by title.

SELECT b.title
 FROM Books AS b
 WHERE
 AND b.title < :last_seen_title
 ORDER BY title DESC
 LIMIT 10 ROWS ONLY

That makes much more sense!

Paginating and ordering by not unique attributes

Now, let suppose that to different books (different ISBN) have the same title.

If we use the above-mentioned query, we are not gonna be able to define which was the last seen book, since the title is repeated.

That means that we are gone to begin always by the first book or skip all books with the same title. Well, in this case, we just need to add some unique attribute.

The primary id will do the job.

SELECT b.title
 FROM Books AS b
 WHERE
 AND (b.title, b.id) < (:last_seen_title, :last_seen_id\) ORDER BY title DESC
 LIMIT 10 ROWS ONLY
Note

Row values in WHERE clause

(x, y) > (a, b) is true if (x > a OR (x=a AND y>b)\) In other words, (x, y) sorts after (a, b\)

Let’s see an example

Let’s make a full example. We will create a book table with an id, title and finally the ISBN code

Create the book table
create table book
(
 `id` int NOT NULL,
 `title` varchar(255) DEFAULT NULL,
 `isbn` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`),
 unique (isbn),
 INDEX (title\) );
Insert some books
insert into book (id, title, isbn) values (1, "WD122", "978-1-60309-455-9");
insert into book (id, title, isbn) values (2, "WDWW", "978-1-63309-455-9");
insert into book (id, title, isbn\) values (3, "WDWW", "978-1-60309-445-9");
insert into book (id, title, isbn\) values (4, "WEWD", "978-1-60209-455-9");
insert into book (id, title, isbn\) values (5, "ZTR", "978-1-60309-455-1");
insert into book (id, title, isbn\) values (6, "GHJ", "978-1-60309-459-9");
insert into book (id, title, isbn\) values (7, "GG", "918-1-60309-455-9");
insert into book (id, title, isbn\) values (8, "AA", "978-1-60209-755-9");
insert into book (id, title, isbn\) values (9, "AA ", "978-4-60309-455-9");
insert into book (id, title, isbn\) values (10, "AA AA BB", "972-1-60309-455-9");
insert into book (id, title, isbn\) values (11, "BBB", "978-1-60309-455-2");
insert into book (id, title, isbn\) values (12, "B", "978-1-61309-425-9");
insert into book (id, title, isbn\) values (13, "AA", "998-1-60309-455-9");
insert into book (id, title, isbn\) values (14, "A", "978-1-80309-479-9");

The given titles are just "easy to order" letters. In this way, we can then easily appreciate how the pagination and ordering are working.

First, we trigger a simple select with an order by title:

select b.*
from book as b
order by title ASC;
id title

14

A

13

AA

9

AA

8

AA

10

AA AA BB

12

B

11

BBB

7

GG

6

GHJ

1

WD122

3

WDWW

2

WDWW

4

WEWD

5

ZTR

Now, let us paginate from the first result:

select b.*
from book as b
where b.title > ""
order by title ASC;

We indicate title > "" so all title can pass the filters. The result is the same as in the table above.

Now let’s suppose that we want to show results after title GG (GG is the last title we have on the last page).

select b.*
from book as b
where b.title > "GG"
order by title ASC;
id title

6

GHJ

1

WD122

3

WDWW

2

WDWW

4

WEWD

5

ZTR

As expected we begin by id 6, title GHJ.

No we repeat the pagination listin from id 3, title WDWW:

id title

4

WEWD

5

ZTR

Oops! We have expected to see id 2, title WDWW, however, we see id 4, title WEWD!!

That happens because id 3 and id 2 have the same title, so both are equally out-filtered.

The solution here is to add the field id (could also be ISDN, relevant here is only to use none repeated ids).

Since we are now using also the field id for filtering, we must add it to the order by field too.

select b.*
from book as b
where b.id <> -1
 AND (b.title, b.id) >= ("", -1)
order by title ASC, id ASC;
id title

14

A

8

AA

9

AA

13

AA

10

AA AA BB

12

B

11

BBB

7

GG

6

GHJ

1

WD122

2

WDWW

3

WDWW

4

WEWD

5

ZTR

We see here how the books using the same title have changed its order. Now we can paginate correctly.

So, back to our problem. We want to start pagination on id 3, title WDWW

select b.*
from book as b
where b.id <> 2
 AND (b.title, b.id) >= ("WDWW", 2)
order by title ASC, id ASC;

Basically, on the filter we need to accept title repetition, so we use now >=. Ids are unique, so they are not gone a be repeated. At the same time, we want to exclude the book with id 2. Therefore we exclude the id in the predicate b.id <> 2.

id title

3

WDWW

4

WEWD

5

ZTR

And here the result! We are now showing the expected entries!

Useful bash commands

Som command examples using AWK, sed, find, watch and aria2

Albert Lacambra BasilAlbert Lacambra Basil

Reset console colors to bash

echo -e "\033[0m"

AWK useful commands

print column n using a separator #:

awk -F# '{print $n}'

print column 3 of lines that start with a number:

awk '/^[0-9]/ {print $3}'

Variables:

NF: number of fields

NR: Number of record being processed

Avoid first record(row) and print last field (columnd):

awk '{if(NR>1) print $NF}':

More about built-in variables: 8 Powerful Awk Built-in

Some more tricks here

Match and replace using sed

sed -i -e ‘s/pattern/replace-for/g'

Apply commands using find

Execute rm on *.class files:

find . -name *.class -type f -exec rm;

Execute the sed param on files containing java in the name:

find . -type f |grep java|xargs sed -i -e ‘s/pattern/replace-for/g'

Rerun command using watch

To rerun automatically a command use:

watch -n 1 --differences {COMMAND TO REFRESH}

For example, to see creation of docker containers:

watch -n 1 --differences docker ps

Aria2, the wget alternative

Aria2 offers interesting options to download a file, like parallel downloading:

aria2c -x4 {utl} to use 4 connections