การใส่ค่าและกำหนดรูปแบบ Data ให้กับ Cell Excel ใน Java Apache POI

จากบทที่แล้วเราได้ทำการ สร้าง Cell ใน Excel โดยใช้ Java POI กันไปแล้ว แล้วก็เซตค่าให้กับแต่ละ Cell แล้วแต่ว่าถ้าสังเกตุดูจะเห็นว่าเวลาเราใส่ค่าเวลา หรือ Date ให้กับ Excel แล้วจะเป็นตัวเลขที่เดายาก และอ่านยาก ดังนั้น บทนี้เรามาดูวิธีง่าย ๆ ในการกำนหนดรูปแบบ และ format ให้กับ Date ใน Java Apache POI กันครับ มาดูตัวอย่างโค้ดตามด้านล่างกัน

package info.doesystem.tutorial.poi;

import java.io.FileOutputStream;
import java.util.Calendar;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelDateValueFormat {
	public static void main(String[] args) {
		try (
			Workbook wb = new XSSFWorkbook();
			FileOutputStream fileOut = new FileOutputStream("D://Blog/Temp File/workbook.xlsx")
		){
			// create sheet
			Sheet sheet = wb.createSheet("new sheet");
			
			// create row
			Row row = sheet.createRow(0);
			
			// set Date value to cell
			row.createCell(0).setCellValue(Calendar.getInstance());
			row.createCell(1).setCellValue(Calendar.getInstance().getTime());
			
			// set Date value Format
			CreationHelper createHelper = wb.getCreationHelper();
			CellStyle cellStyle = wb.createCellStyle();
		    cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm"));
		    
		    Cell cell2 = row.createCell(2);
		    cell2.setCellValue(Calendar.getInstance());
		    cell2.setCellStyle(cellStyle);
		    
		    Cell cell3 = row.createCell(3);
		    cell3.setCellValue(Calendar.getInstance().getTime());
		    cell3.setCellStyle(cellStyle);
		    
			wb.write(fileOut);

			System.out.println("SUCCESS");
		} catch (Exception ex) {
			System.out.println("Exception " + ex);
		}
	}
}

ผลลัพธ์ที่ได้จากการรันโค้ดข้างบนจะได้ประมาณนี้

จากผลลัพธ์เมื่อเทียบกับโค้ดจะเห็นว่าถ้าเราใส่ Date หรือ Calendar เข้าไปแบบไม่เซต format จะทำให้ค่าที่ได้เป็นตัวเลขที่อ่านยาก ดังนั้นการใส่ format สำหรับ Date ใน Excel จึงเป็นสิ่งที่ใช้กันหลาย ๆ ครั้ง

การสร้าง Cell ใน Excel โดยใช้ Java POI

เราได้สร้าง sheet ใน excel กันไปแล้ว ต่อไปมาดูวิธีการสร้าง cell กันบ้าง ซึ่งก็เป็นวิธีการง่าย ๆ ในการสร้าง Cell ใน Excel โดยใช้ Java POI เราลองมาดูโค้ดที่ใช้ในการสร้าง Cell กันก่อนครับ ตามด้านล่าง

package info.doesystem.tutorial.poi;

import java.io.FileOutputStream;
import java.util.Calendar;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class NewCell {
 public static void main(String[] args) {
  try (
    Workbook wb = new XSSFWorkbook();
    FileOutputStream fileOut = new FileOutputStream("D://Blog/Temp File/workbook.xlsx")
   ){
    // create sheet
    Sheet sheet = wb.createSheet("new sheet");
    
    // create row
    Row row = sheet.createRow(0);
    
    // create cell
    Cell cell = row.createCell(0);
    
    // set value to cell
    cell.setCellValue(1);
    
    // other example
    row.createCell(1).setCellValue("Test");
    row.createCell(2).setCellValue(Calendar.getInstance());
    row.createCell(3).setCellValue(Calendar.getInstance().getTime());
    CreationHelper createHelper = wb.getCreationHelper();
    row.createCell(4).setCellValue(createHelper.createRichTextString("This is a string"));

    wb.write(fileOut);

    System.out.println("SUCCESS");
   } catch (Exception ex) {
    System.out.println("Exception " + ex);
   }
 }
}

จากโค้ดจะเห็นว่าเราต้องทำการสร้าง Sheet จากนั้นก็สร้าง Row จากนั้นในแต่ละ Row เราถึงจะสร้าง Cell เมื่อต้องการเซตค่าให้กับแต่ละ Cell ก็เพียงแค่เรียก setCellValue เพียงแค่นี้เราก็ได้ทำการเซตค่าให้กับแต่ละ Cell แล้ว

การใส่ชื่อ sheet name ใน excel แบบปลอดภัยโดยใช้ WorkbookUtil.createSafeSheetName

จากบทความเรื่อง การสร้าง Sheet ใน Excel โดยใช้ Java POI เราได้ทำการสร้างและได้กำหนดชื่อของ sheet แต่ในการตั้งชื่อ sheet นั้นก็มีข้อจำกัดบางอย่าง ถ้าหากเราตั้งชื่อผิดกฎของมันละก็อาจทำให้เกิด Exception บางอย่างได้ ตัวอย่าง Exception ที่เกิดจากการตั้งชื่อ sheet ดังนั้นบทความนี้เราได้แนะนำ Class ที่ชื่อว่า WorkbookUtil ซึ่งจะมี method ที่ช่วยในการตั้งชื่อให้กับ sheet อยู่ ซึ่งที่เราแนะนำจะเป็น method ชื่อ createSafeSheetName

เรามาดูกันดีกว่าว่า method นี้มีการทำงานอย่างไร ให้เราเข้าไปที่ Class WorkbookUtil เพื่อดูโค้ด จะได้ประมาณนี้

public static final String createSafeSheetName(String nameProposal) {
 return createSafeSheetName(nameProposal, ' ');
}

public static final String createSafeSheetName(String nameProposal,
  char replaceChar) {
 if (nameProposal == null) {
  return "null";
 }
 if (nameProposal.length() < 1) {
  return "empty";
 }
 int length = Math.min(31, nameProposal.length());
 String shortenname = nameProposal.substring(0, length);
 StringBuilder result = new StringBuilder(shortenname);
 for (int i = 0; i < length; ++i) {
  char ch = result.charAt(i);
  switch (ch) {
  case '\0':
  case '\3':
  case '*':
  case '/':
  case ':':
  case '?':
  case '[':
  case '\\':
  case ']':
   result.setCharAt(i, replaceChar);
   break;
  case '\'':
   if ((i == 0) || (i == length - 1)) {
    result.setCharAt(i, replaceChar);
   }

  }

 }

 return result.toString();
}

จากโค้ด เราจะเห็นว่าเมื่อเราเรียก method createSafeSheetName ถ้าเราใส่ชื่อ sheet อย่างเดียว มันจะไปเรียก method อีกตัวหนึ่งที่ชื่อเดียวกันแต่ส่งพารามิเตอร์ต่างกัน ซึ่ง method ที่ใส่ชื่อ sheet name อย่างเดียว จะส่งค่าช่องว่างหนึ่งตัวเข้าไป

ใน method ที่ใส่ทั้ง sheet name และก็ replaceChar จะทำการเช็คก่อนว่าเป็น null กับค่าว่างหรือเปล่า

จากนั้นก็เช็คว่าขนาดความยาวของ sheet name ที่เราใส่เข้าไปมีขนาดเกิน 31 ตัวหรือเปล่าโดยใช้ Math.min จะเห็นว่าถ้าชื่อ sheet ของคุณมีขนาดน้อยกว่าก็เอาขนาดของชื่อ sheet คุณ แต่ถ้าชื่อ sheet คุณมากกว่า 31 ตัวก็เอาค่า 31 มา จากนั้นก็ทำการ substring ซะ (ซึ่งข้อกำหนดของ sheet name ต้องมีขนาดไม่เกิน 31 ตัวอักษร)

จากนั้นก็ทำการ check ว่ามีอักขระพิเศษหรือเปล่าโดยถ้าเจอก็แทนอักขระเหล่านั้นด้วย replaceChar ที่ใส่เข้ามา

เพียงแค่นี้เราก็ไม่ต้องกังวลว่าจะตั้งชื่อ sheet name ผิดกฎ หรือกังวลเกี่ยวกับ Exception ที่เกิดขึ้นจากการตั้งชื่อ sheet name แล้วเพียงแค่ใช้ createSafeSheetName 

Exception java.lang.IllegalArgumentException: Invalid char (:) found at index (11) in sheet name

ใครเคยเจอ Exception แบบนี้บ้าง Exception ที่เจอนี้เป็น Exception ที่ผมสร้าง sheet โดยใช้ POI แล้วใส่ชื่อเข้าไป มาดูโค้ดที่ทำให้เกิด Exception นี้กัน

package info.doesystem.tutorial.poi;

import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class NewSheet {
 public static void main(String[] args) {
  try (
   Workbook wb = new XSSFWorkbook();
   FileOutputStream fileOut = new FileOutputStream("D://Blog/Temp File/workbook.xlsx")
  ){
   Sheet sheet1 = wb.createSheet("new sheet 1111111111111111111111111111111111111111111111111");
   Sheet sheet2 = wb.createSheet("new sheet 2:2");

   wb.write(fileOut);

   System.out.println("SUCCESS");
  } catch (Exception ex) {
   System.out.println("Exception " + ex);
  }
 }
}

จากโค้ดนี้ถ้าเราลองสังเกต Exception จะเห็นว่าเป็น Exception ที่เกิดจากการประกาศ sheet name แล้วบอกว่าอักขระ : ไม่ถูกต้องใน index ที่ 11

ถ้าเราลองดูในโค้ดจะเห็นว่ามีการประกาศอักขระ : อยู่ในการสร้าง sheet name ซึ่งอักขระ : เป็นตัวที่ 11 พอดี

ดังนั้น Exception นี้จึงเกิดจากที่เราสร้าง sheet แล้วใส่ชื่อ sheet ผิด ซึ่งเป็นอักขระที่ไม่สามารถใช้ได้ ให้แก้อักขระนี้เสีย แล้วก็จะรันได้

วิธีการแก้ก็ลบออก หรือไม่ก็ใช้ WorkbookUtil.createSafeSheetName ก็ได้

การสร้าง Sheet ใน Excel โดยใช้ Java POI

เรามาดูวิธีการสร้าง Sheet ใน Excel โดยใช้ Java POI กันครับ ซึ่งการสร้าง Sheet เป็นขั้นตอนที่ง่ายมากตามตัวอย่างโค้ดด้านล่างเลย

package info.doesystem.tutorial.poi;

import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class NewSheet {
 public static void main(String[] args) {
  try (
   Workbook wb = new XSSFWorkbook();
   FileOutputStream fileOut = new FileOutputStream("D://Blog/Temp File/workbook.xlsx")
  ){
   Sheet sheet1 = wb.createSheet("new sheet 1");
   Sheet sheet2 = wb.createSheet("new sheet 2");

   wb.write(fileOut);

   System.out.println("SUCCESS");
  } catch (Exception ex) {
   System.out.println("Exception " + ex);
  }
 }
}

จากตัวอย่างโค้ดการสร้าง Sheet ใน Excel เพียงแค่เราเอา Object Workbook ที่มีอยู่มาเรียกใช้คำสั่ง createSheet แล้วใส่ชื่อของ Sheet ที่ต้องการลงไป เพียงเท่านี้ Excel ของเราก็ได้ Sheet ที่ต้องการแล้ว

ตัวอย่างผลลัพธ์ดังรูปด้านล่าง

ข้อควรระวัง
ในการสร้าง Sheet ใหม่นั้นสิ่งที่ต้องใส่ใจมีอยู่อย่างหนึ่งนั่นคือชื่อของ Sheet ซึ่งมีดังนี้
1. ความยาวชื่อของ Sheet จะมีขนาดไม่เกิน 31 ตัวอักษร
2. ชื่อของ Sheet จะต้องไม่มีอักขระพิเศษดังนี้ 0x0000 0x0003 colon (:) backslash (\) asterisk (*) question mark (?) forward slash (/) opening square bracket ([) closing square bracket (])

การสร้างไฟล์ Excel โดยใช้ Java POI

บทความนี้เรามาดูวิธีการสร้างไฟล์ Excel โดยใช้ Java แบบง่าย ๆ กัน ซึ่งในการสร้างไฟล์นั้นจะใช้ Library ที่ชื่อว่า POI หรือที่รู้จักกันในชื่อ Apache POI นั่นแหละครับ

การสร้างไฟล์ Excel XLS

เรามาดูตัวอย่างโค้ดการสร้างไฟล์ Excel โดยใช้ POI แบบแรกกันก่อนซึ่งจะเป็นการสร้างไฟล์ที่มีนามสกุลเป็น .xls ตัวอย่างโค้ดตามด้านล่าง

package info.doesystem.tutorial.poi;

import java.io.FileOutputStream;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;

public class NewWorkbook {
 public static void main(String[] args) {
  try (
   Workbook wb = new HSSFWorkbook();
   FileOutputStream fileOut = new FileOutputStream("D://Blog/Temp File/workbook.xls");
  ){
   wb.write(fileOut);
   
   System.out.println("SUCCESS");
  } catch (Exception ex) {
   System.out.println("Exception " + ex);
  }
 }
}

การสร้างไฟล์ Excel XLSX

ตัวอย่างโค้ดแบบที่สองเป็นการสร้างไฟล์ Excel ที่มีนามสกุลเป็น .xlsx ตัวอย่างโค้ดตามด้านล่างครับ

package info.doesystem.tutorial.poi;

import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class NewWorkbook {
 public static void main(String[] args) {
  try (
   Workbook wb = new XSSFWorkbook();
   FileOutputStream fileOut = new FileOutputStream("D://Blog/Temp File/workbook.xlsx");
  ){
   wb.write(fileOut);
   
   System.out.println("SUCCESS");
  } catch (Exception ex) {
   System.out.println("Exception " + ex);
  }
 }
}

จากตัวอย่างโค้ดเราจะสร้าง Workbook ไว้ใน Try ซึ่ง java 1.7 สามารถใช้ได้ แต่ถ้าต่ำกว่านี้ก็ให้ใส่ไปข้างในแล้วใช้ close เอานะครับถ้าหากเจอ Error

ตัวอย่างโค้ดทั้งสองโค้ดนี้จะเป็นการสร้างไฟล์ Excel ที่มีนามสกุลเป็น .xls กับ .xlsx ซึ่งจะต่างกันตรงที่ ไฟล์ Excel นามสกุล .xls จะสร้างโดยใช้ HSSFWorkbook ส่วนไฟล์ Excel ที่มีนามสกุลเป็น .xlsx จะสร้างโดยใช้ XSSFWorkbook

จากโค้ดเราแค่สร้าง Object Workbook ขึ้นมา แล้วก็ write ใส่ใน FileOutputStream เพียงแค่นี้เราก็ได้ไฟล์ Excel แบบง่าย ๆ กันแล้ว

java.lang.NoClassDefFoundError: org/apache/poi/wp/usermodel/Paragraph

วันนนี้ได้มีโอกาศได้ทำเกี่ยวกับ POI การสร้างเอกสาร Microsoft Word โดยการใช้ Lobrary ที่เรียกกันว่า POI ซึ่งในตอนกำลังเขียนโค้ดอยู่นั้นลองกดรัน กลับมี Error เกิดขึ้นมาว่า

Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/poi/wp/usermodel/Paragraph
 at java.lang.ClassLoader.defineClass1(Native Method)
 at java.lang.ClassLoader.defineClassCond(ClassLoader.java:631)
 at java.lang.ClassLoader.defineClass(ClassLoader.java:615)
 at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:141)
 at java.net.URLClassLoader.defineClass(URLClassLoader.java:283)
 at java.net.URLClassLoader.access$000(URLClassLoader.java:58)
 at java.net.URLClassLoader$1.run(URLClassLoader.java:197)
 at java.security.AccessController.doPrivileged(Native Method)
 at java.net.URLClassLoader.findClass(URLClassLoader.java:190)
 at java.lang.ClassLoader.loadClass(ClassLoader.java:306)
 at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)
 at java.lang.ClassLoader.loadClass(ClassLoader.java:247)
 at org.apache.poi.xwpf.usermodel.XWPFTableCell.(XWPFTableCell.java:93)
 at org.apache.poi.xwpf.usermodel.XWPFTableRow.getTableCells(XWPFTableRow.java:160)
 at org.apache.poi.xwpf.usermodel.XWPFTableRow.(XWPFTableRow.java:48)
 at org.apache.poi.xwpf.usermodel.XWPFTable.(XWPFTable.java:109)
 at org.apache.poi.xwpf.usermodel.XWPFDocument.createTable(XWPFDocument.java:865)
 at helpwork.SpecWord.createTableForBean(SpecWord.java:36)
 at helpwork.SpecWord.main(SpecWord.java:22)
Caused by: java.lang.ClassNotFoundException: org.apache.poi.wp.usermodel.Paragraph
 at java.net.URLClassLoader$1.run(URLClassLoader.java:202)
 at java.security.AccessController.doPrivileged(Native Method)
 at java.net.URLClassLoader.findClass(URLClassLoader.java:190)
 at java.lang.ClassLoader.loadClass(ClassLoader.java:306)
 at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)
 at java.lang.ClassLoader.loadClass(ClassLoader.java:247)
 ... 19 more

เมื่อลองค้นหาวิธีการแก้ไขก็ได้ว่าต้องแก้ไขโดยการไปดาวน์โหลด Library อื่นมาเพิ่มเติมซึ่งขาดอยู่นั่นกคือ hamcrest-core-xxx.jar

ดังนั้นใครที่เกิดปัญหานี้ลองไปดาวน์โหลดhamcrest-core-xxx.jar มาใส่ดูครับ พอใส่เสร็จแล้วคราวนี้ก็รันโค้ดได้แบบสบายใจละ