albert's blog: September 2016

2016/09/30

[Travel] 台東

多良車站

鹿野高台

台東太麻里海岸線

台東金針山

台東縣小野柳風景區

台東藝術鐵花村

太麻里第一道曙光紀念公園

利吉惡地

2016/09/09

[Java] try-with-resources statement

JDK 7 introduces a new version of try statement known as try-with-resources statement. This feature add another way to exception handling with resources management,it is also referred to as automatic resource management.

If this class implements AutoClossable interface, then you can make good use of try-with-resources statement. Do not need to close manually.
e

As-is (does not apply try-with-resources statement)

  FileOutputStream outputStream = null;
  File tmpFile = null;
  try {
      tmpFile = File.createTempFile("issue", ".xls");
      outputStream = new FileOutputStream(tmpFile);
      workbook.write(outputStream);
  } catch (FileNotFoundException e) {
      log.error(e.getMessage(), e);
  } finally {
      if (outputStream != null) {
          outputStream.close();
      }
      if (workbook != null) {
          workbook.close();
      }
  }

To-be (apply try-with-resources statement)

  File tmpFile = File.createTempFile("issue", ".xls");
  try(FileOutputStream outputStream  = new FileOutputStream(tmpFile)){
      workbook.write(outputStream);
  } catch (FileNotFoundException e) {
      log.error(e.getMessage(), e);
  } finally{
      if (workbook != null) {
          workbook.close();
      }
  }

Reference
[1] http://www.studytonight.com/java/try-with-resource-statement.php

2016/09/08

[Apache POI] How to freeze the first row

Problem
I am using Apache POI to write data into Microsoft Excel.
If I would like to keep the first row on the top of this document when I scroll down, how to do it?

How-to
You can utilize createFreezePane API which provide by Apache POI to fulfill this requirement. Here has code snippet:

 @Override
 public File exportIssuesToXls(List < Issue > issues) throws IOException, RedmineException {
  Workbook workbook = new HSSFWorkbook();
  Sheet sheet = workbook.createSheet("issue list");
  CellStyle style = createCellStyle(workbook);

  //....

  //freeze the first row
  sheet.createFreezePane(0, 1);

  FileOutputStream outputStream = null;
  File tmpFile = null;
  try {
   tmpFile = File.createTempFile("issue", ".xls");
   log.info("tmpFile = " + tmpFile.getCanonicalPath());
   outputStream = new FileOutputStream(tmpFile);
   workbook.write(outputStream);

  } catch (FileNotFoundException e) {
   e.printStackTrace();
  } finally {
   if (outputStream != null) {
    outputStream.close();
   }
   if (workbook != null) {
    workbook.close();
   }
  }
  return tmpFile;
 }

Reference
[1] https://stackoverflow.com/questions/17932575/apache-poi-locking-header-rows

2016/09/07

[Apache POI] How to add new line in cell data

Problem
I am using Apache POI to write data into Microsoft Excel.
I would like to break data into new line in some cell, how to do it?

How-to
You can add \n for the new line. For example,

1	Issue issue1 = new Issue(1, "查不到資料", "新建立", "正常", "蜘蛛人(2016-05-26 17:05:00):\n這個提議不錯，來做吧！\n\n浩克(2016-05-26 17:05:00):\n測試無誤\n\n");

Reference
[1] https://stackoverflow.com/questions/14646349/how-to-add-new-line-character-in-the-cell-comment-using-apache-poi

2016/09/06

[Microsoft Word] 如何新增檔名到頁尾

Problem
如果我要在 word 文件的頁尾插入 word 檔名，在 Microsoft Word 中如何做？

How-to
1. 點選頁尾

2. 插入 => 快速組件 => 功能變數

3. 選擇 file name，按下確定按鈕

4. 此時，就可以看到頁尾出現此檔案的檔名

Reference
[1] https://support.office.com/zh-tw/article/%E6%96%B0%E5%A2%9E%E6%AA%94%E5%90%8D%E5%88%B0%E9%A0%81%E9%A6%96%E6%88%96%E9%A0%81%E5%B0%BE-dc62245f-b6af-45b2-a521-17753fc3539e

2016/09/05

[Windows 7] 參考到的帳戶目前已鎖定，且可能無法登入

Problem
有次在登入windows 7的帳號密碼時，因故輸入三次錯誤，導致電腦被鎖定，即便是重新開機以後還是出現相同的錯誤訊息

Solution
由於之前已經有過一次經驗，必須要有windows 7光碟進入才有辦法解決，故經過上次以後，有建立一個擁有 administration 權限的另外一個 user

所以在本次，我只要先重開機，按下F8，選擇以安全模式進入Windows，再利用另外一個帳號log in 進去

Steps:
1. 在我的電腦按下右鍵，選擇管理

2 .系統工具=>本機使用者與群組=>使用者

3. 於 lock 的使用者帳號按下滑鼠右鍵，選擇內容

4. 將最後一個 checkbox 勾勾取消，並重新開機即可

2016/09/04

[POI] Write /Read Excel Files in Java using Apache POI

Here has a simple example to demonstrate how to write / read excel file via Apache POI.

Add Apache POI dependency:

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.14</version>
</dependency>

Create a POJO class:

package albert.practice.poi;

import java.io.Serializable;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

@Data
@ToString
@AllArgsConstructor
@NoArgsConstructor
public class Issue implements Serializable {
    /**
    * 
    */
    private static final long serialVersionUID = 1L;

    private Integer id;
    private String subject;
    private String status;
    private String priority;
}

Sample code is as bellows:

package albert.practice.poi;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import lombok.extern.slf4j.Slf4j;

//http://www.codejava.net/coding/how-to-write-excel-files-in-java-using-apache-poi
//http://viralpatel.net/blogs/java-read-write-excel-file-apache-poi/
@Slf4j
public class PoiExample {

    private static String excelFile = "D:" + File.separator + "issue.xls";

    public static void main(String[] args) throws IOException {
        PoiExample test = new PoiExample();
        test.writeExcel();

        test.readExcel(excelFile);
    }

    public List<Issue> readExcel(String excelFile) throws IOException {

        List<Issue> issues = new ArrayList<Issue>();

        InputStream inputStream = null;
        Workbook workbook = null;
        try {
            // 1. Create a Workbook.
            inputStream = new FileInputStream(new File(excelFile));
            workbook = new HSSFWorkbook(inputStream);

            // 2. Get first sheet
            Sheet sheet = workbook.getSheetAt(0);

            // 3. Iterate through each rows from first sheet
            Iterator<Row> rowIterator = sheet.iterator();
            int rowCount = 1;
            while (rowIterator.hasNext()) {
                // (1) ignore header row
                if (rowCount == 1) {
                rowIterator.next();
                rowCount++;
                }
                // (2) start to read each row from second row
                else {
                    Row row = rowIterator.next();
                    Integer id = Double.valueOf(row.getCell(0).getNumericCellValue()).intValue();
                    String subject = row.getCell(1).getStringCellValue();
                    String status = row.getCell(2).getStringCellValue();
                    String priority = row.getCell(3).getStringCellValue();

                    Issue issue = new Issue();
                    issue.setId(id);
                    issue.setSubject(subject);
                    issue.setStatus(status);
                    issue.setPriority(priority);

                    issues.add(issue);
                 }
             }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } finally {
            if (inputStream != null) {
                inputStream.close();
            }
            if (workbook != null) {
                workbook.close();
            }
        }

        for (Issue issue : issues) {
            log.debug("issue = " + issue.toString());
        }

        return issues;
    }

    public void writeExcel() throws IOException {
        // 0. prepare dummy data
        List<Issue> issues = createDummyIssues();

        // 1. Create a Workbook.
        Workbook workbook = new HSSFWorkbook();

        // 2. Create a Sheet.
        Sheet sheet = workbook.createSheet("issue list");

        // 3. create cell style
        CellStyle style = createCellStyle(workbook);

        // 4. Repeat the following steps until all data is processed:
        // (1) Create a Row.
        // (2) Create Cells in a Row. Apply formatting using CellStyle.
        int rowCount = 0;
        Row headerRow = sheet.createRow(rowCount);
        writeHeader(headerRow, style);

        for (Issue issue : issues) {
            Row row = sheet.createRow(++rowCount);
            writeDataForEachRow(issue, row, style);
        }

        // 5. auto resize column width
        for (int i = 0; i < 4; i++) {
            sheet.autoSizeColumn(i);
        }

        // 6. Write to an OutputStream.
        // 7. Close the output stream.
        FileOutputStream outputStream = null;
        try {
            outputStream = new FileOutputStream(excelFile);
            workbook.write(outputStream);

            log.debug("write issue data to excel file successfully");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } finally {
            if (outputStream != null) {
                outputStream.close();
            }
            if (workbook != null) {
                workbook.close();
            }
        }
    }

    private void writeHeader(Row headerRow, CellStyle style) {
        Cell cell = headerRow.createCell(0);
        cell.setCellValue("編號");
        cell.setCellStyle(style);

        cell = headerRow.createCell(1);
        cell.setCellValue("主旨");
        cell.setCellStyle(style);

        cell = headerRow.createCell(2);
        cell.setCellValue("狀態");
        cell.setCellStyle(style);

        cell = headerRow.createCell(3);
        cell.setCellValue("優先");
        cell.setCellStyle(style);
    }

    private void writeDataForEachRow(Issue issue, Row row, CellStyle style) {
        Cell cell = row.createCell(0);
        cell.setCellValue(issue.getId());
        cell.setCellStyle(style);

        cell = row.createCell(1);
        cell.setCellValue(issue.getSubject());
        cell.setCellStyle(style);

        cell = row.createCell(2);
        cell.setCellValue(issue.getStatus());
        cell.setCellStyle(style);

        cell = row.createCell(3);
        cell.setCellValue(issue.getPriority());
        cell.setCellStyle(style);
    }

    private CellStyle createCellStyle(Workbook workbook) {
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setBorderBottom((short) 1);
        cellStyle.setBorderTop((short) 1);
        cellStyle.setBorderLeft((short) 1);
        cellStyle.setBorderRight((short) 1);
        cellStyle.setWrapText(true);

        return cellStyle;
    }

    private List<Issue> createDummyIssues() {
        Issue issue1 = new Issue(1, "查不到資料", "新建立", "正常");
        Issue issue2 = new Issue(2, "新增時發生錯誤", "新建立", "高");
        Issue issue3 = new Issue(3, "刪除失敗", "處理中", "高");

        return Arrays.asList(issue1, issue2, issue3);
    }

}

Excel file looks like: https://github.com/junyuo/AlbertGitProject/blob/master/src/albert/practice/poi/issue.xls

Reference
[1] http://www.codejava.net/coding/how-to-write-excel-files-in-java-using-apache-poi
[2] http://viralpatel.net/blogs/java-read-write-excel-file-apache-poi/

2016/09/03

[jackson] How to generate json from java bean and vice versa

Problem
If I would like to do conversation with Java Bean to JSON and JSON to Java Bean, any library can be used?

How-To
Add dependency to your pom.xml

<dependency>
    <groupId>org.codehaus.jackson</groupId>
    <artifactId>jackson-mapper-asl</artifactId>
    <version>1.9.13</version>
</dependency>

Create an User bean for test:

package albert.practice.json;

import java.io.Serializable;

import lombok.Data;
import lombok.ToString;

@Data
@ToString
public class User implements Serializable {
    private static final long serialVersionUID = 1L;

    private String id;
    private String login;
    private String mail;
}

Test code is as bellows:

package albert.practice.json;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.codehaus.jackson.JsonGenerationException;
import org.codehaus.jackson.JsonParseException;
import org.codehaus.jackson.map.JsonMappingException;
import org.codehaus.jackson.map.ObjectMapper;
import org.codehaus.jackson.type.TypeReference;

import lombok.extern.slf4j.Slf4j;

@Slf4j
public class JacksonTest {

    public static void main(String[] args) {
        JacksonTest test = new JacksonTest();
        String jsonString = test.convertOjbectToJson();

        List<User> users = test.convertJsonToObject(jsonString);
        for (User user : users) {
           log.debug("user = " + user.toString());
        }
    }

    public List<User> convertJsonToObject(String jsonString) {
        ObjectMapper mapper = new ObjectMapper();
        List<User> users = new ArrayList<User>();
        try {
            users = mapper.readValue(jsonString, new TypeReference<List<User>>() {
        });
    } catch (JsonParseException e) {
        e.printStackTrace();
    } catch (JsonMappingException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
        return users;
    }

    public String convertOjbectToJson() {
        List<User> users = createUsers();
        String jsonString = "";

        ObjectMapper mapper = new ObjectMapper();

        try {
            jsonString = mapper.writeValueAsString(users);
        } catch (JsonGenerationException e) {
            e.printStackTrace();
        } catch (JsonMappingException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        log.debug("jsonString = " + jsonString);
        return jsonString;
    }

    private List<User> createUsers() {
        User albert = new User();
        albert.setId("1");
        albert.setLogin("albert");
        albert.setMail("albert@gmail.com");

        User mandy = new User();
        mandy.setId("2");
        mandy.setLogin("mandy");
        mandy.setMail("mandy@gmail.com");

        User verio = new User();
        verio.setId("3");
        verio.setLogin("verio");
        verio.setMail("verio@gmail.com");

        List<User> users = new ArrayList<User>();
        users.add(albert);
        users.add(mandy);
        users.add(verio);

        return users;
    }

}

print log:

2016-06-01 15:35:39.630 [main] DEBUG albert.practice.json.JacksonTest - jsonString = [{"id":"1","login":"albert","mail":"albert@gmail.com"},{"id":"2","login":"mandy","mail":"mandy@gmail.com"},{"id":"3","login":"verio","mail":"verio@gmail.com"}]
2016-06-01 15:35:39.670 [main] DEBUG albert.practice.json.JacksonTest - user = User(id=1, login=albert, mail=albert@gmail.com)
2016-06-01 15:35:39.670 [main] DEBUG albert.practice.json.JacksonTest - user = User(id=2, login=mandy, mail=mandy@gmail.com)
2016-06-01 15:35:39.670 [main] DEBUG albert.practice.json.JacksonTest - user = User(id=3, login=verio, mail=verio@gmail.com)

Reference
[1] http://www.mkyong.com/java/how-to-convert-java-object-to-from-json-jackson/
[2] https://stackoverflow.com/questions/28821715/java-lang-classcastexception-java-util-linkedhashmap-cannot-be-cast-to-com-test

Total Pageviews