Apache POI單元格/Cells

輸入到電子表格中的任何數據總是存儲在一個單元中。我們使用的行和列的標籤來識別單元格。本章介紹瞭如何使用Java編程操縱單元電子表格的數據。

創建一個單元格

需要創建一個單元之前創建一個行。行是什麼？只不過是單元的集合。

下面的代碼片段用於創建一個單元格。

//create new workbook XSSFWorkbook workbook = new XSSFWorkbook(); //create spreadsheet with a name XSSFSheet spreadsheet = workbook.createSheet("new sheet"); //create first row on a created spreadsheet XSSFRow row = spreadsheet.createRow(0); //create first cell on created row XSSFCell cell = row.createCell(0);

單元格類型

單元格類型指定單元格是否可以包含字符串，數值，或公式。字符串單元不能持有數值和數值單元格無法容納字符串。下面給出是單元格值和類型的語法。

單元格的值類型

類型語法

Blank cell value

XSSFCell.CELL_TYPE_BLANK

Boolean cell value

XSSFCell.CELL.TYPE_BOOLEAN

Error cell value

XSSFCell.CELL_TYPE_ERROR

Numeric cell value

XSSFCell.CELL_TYPE_NUMERIC

String cell value

XSSFCell.CELL_TYPE_STRING

以下代碼是用於在電子表格創建不同類型的單元格。

import java.io.File; import java.io.FileOutputStream; import java.util.Date; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class TypesofCells { public static void main(String[] args)throws Exception { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet spreadsheet = workbook.createSheet("cell types"); XSSFRow row = spreadsheet.createRow((short) 2); row.createCell(0).setCellValue("Type of Cell"); row.createCell(1).setCellValue("cell value"); row = spreadsheet.createRow((short) 3); row.createCell(0).setCellValue("set cell type BLANK"); row.createCell(1); row = spreadsheet.createRow((short) 4); row.createCell(0).setCellValue("set cell type BOOLEAN"); row.createCell(1).setCellValue(true); row = spreadsheet.createRow((short) 5); row.createCell(0).setCellValue("set cell type ERROR"); row.createCell(1).setCellValue(XSSFCell.CELL_TYPE_ERROR ); row = spreadsheet.createRow((short) 6); row.createCell(0).setCellValue("set cell type date"); row.createCell(1).setCellValue(new Date()); row = spreadsheet.createRow((short) 7); row.createCell(0).setCellValue("set cell type numeric" ); row.createCell(1).setCellValue(20 ); row = spreadsheet.createRow((short) 8); row.createCell(0).setCellValue("set cell type string"); row.createCell(1).setCellValue("A String"); FileOutputStream out = new FileOutputStream( new File("typesofcells.xlsx")); workbook.write(out); out.close(); System.out.println( "typesofcells.xlsx written successfully"); } }

保存上面的代碼到一個名爲TypesofCells.java文件，編譯並從命令提示符如下執行它。

$javac TypesofCells.java
$java TypesofCells

如果您的系統配置了POI庫，那麼它會編譯和執行在當前目錄中生成一個名爲typesofcells.xlsx的Excel文件，並顯示以下輸出。

typesofcells.xlsx written successfully

typesofcells.xlsx文件如下所示。

單元格樣式

在這裏，可以學習如何做單元格格式，並採用不同的風格，如合併相鄰的單元格，添加邊框，設置單元格對齊方式和填充顏色。

以下代碼是使用Java編程用於不同樣式應用到單元格。

import java.io.File; import java.io.FileOutputStream; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class CellStyle { public static void main(String[] args)throws Exception { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet spreadsheet = workbook.createSheet("cellstyle"); XSSFRow row = spreadsheet.createRow((short) 1); row.setHeight((short) 800); XSSFCell cell = (XSSFCell) row.createCell((short) 1); cell.setCellValue("test of merging"); //MEARGING CELLS //this statement for merging cells spreadsheet.addMergedRegion(new CellRangeAddress( 1, //first row (0-based) 1, //last row (0-based) 1, //first column (0-based) 4 //last column (0-based) )); //CELL Alignment row = spreadsheet.createRow(5); cell = (XSSFCell) row.createCell(0); row.setHeight((short) 800); // Top Left alignment XSSFCellStyle style1 = workbook.createCellStyle(); spreadsheet.setColumnWidth(0, 8000); style1.setAlignment(XSSFCellStyle.ALIGN_LEFT); style1.setVerticalAlignment(XSSFCellStyle.VERTICAL_TOP); cell.setCellValue("Top Left"); cell.setCellStyle(style1); row = spreadsheet.createRow(6); cell = (XSSFCell) row.createCell(1); row.setHeight((short) 800); // Center Align Cell Contents XSSFCellStyle style2 = workbook.createCellStyle(); style2.setAlignment(XSSFCellStyle.ALIGN_CENTER); style2.setVerticalAlignment( XSSFCellStyle.VERTICAL_CENTER); cell.setCellValue("Center Aligned"); cell.setCellStyle(style2); row = spreadsheet.createRow(7); cell = (XSSFCell) row.createCell(2); row.setHeight((short) 800); // Bottom Right alignment XSSFCellStyle style3 = workbook.createCellStyle(); style3.setAlignment(XSSFCellStyle.ALIGN_RIGHT); style3.setVerticalAlignment( XSSFCellStyle.VERTICAL_BOTTOM); cell.setCellValue("Bottom Right"); cell.setCellStyle(style3); row = spreadsheet.createRow(8); cell = (XSSFCell) row.createCell(3); // Justified Alignment XSSFCellStyle style4 = workbook.createCellStyle(); style4.setAlignment(XSSFCellStyle.ALIGN_JUSTIFY); style4.setVerticalAlignment( XSSFCellStyle.VERTICAL_JUSTIFY); cell.setCellValue("Contents are Justified in Alignment"); cell.setCellStyle(style4); //CELL BORDER row = spreadsheet.createRow((short) 10); row.setHeight((short) 800); cell = (XSSFCell) row.createCell((short) 1); cell.setCellValue("BORDER"); XSSFCellStyle style5 = workbook.createCellStyle(); style5.setBorderBottom(XSSFCellStyle.BORDER_THICK); style5.setBottomBorderColor( IndexedColors.BLUE.getIndex()); style5.setBorderLeft(XSSFCellStyle.BORDER_DOUBLE); style5.setLeftBorderColor( IndexedColors.GREEN.getIndex()); style5.setBorderRight(XSSFCellStyle.BORDER_HAIR); style5.setRightBorderColor( IndexedColors.RED.getIndex()); style5.setBorderTop(XSSFCellStyle.BIG_SPOTS); style5.setTopBorderColor( IndexedColors.CORAL.getIndex()); cell.setCellStyle(style5); //Fill Colors //background color row = spreadsheet.createRow((short) 10 ); cell = (XSSFCell) row.createCell((short) 1); XSSFCellStyle style6 = workbook.createCellStyle(); style6.setFillBackgroundColor( HSSFColor.LEMON_CHIFFON.index ); style6.setFillPattern(XSSFCellStyle.LESS_DOTS); style6.setAlignment(XSSFCellStyle.ALIGN_FILL); spreadsheet.setColumnWidth(1,8000); cell.setCellValue("FILL BACKGROUNG/FILL PATTERN"); cell.setCellStyle(style6); //Foreground color row = spreadsheet.createRow((short) 12); cell = (XSSFCell) row.createCell((short) 1); XSSFCellStyle style7=workbook.createCellStyle(); style7.setFillForegroundColor(HSSFColor.BLUE.index); style7.setFillPattern( XSSFCellStyle.LESS_DOTS); style7.setAlignment(XSSFCellStyle.ALIGN_FILL); cell.setCellValue("FILL FOREGROUND/FILL PATTERN"); cell.setCellStyle(style7); FileOutputStream out = new FileOutputStream( new File("cellstyle.xlsx")); workbook.write(out); out.close(); System.out.println("cellstyle.xlsx written successfully"); } }

保存上面的代碼在一個名爲CellStyle.java文件，編譯並從命令提示符如下執行它。

$javac CellStyle.java
$java CellStyle

它會生成一個名爲cellstyle.xlsx在當前目錄中的Excel文件並顯示以下輸出。

cellstyle.xlsx written successfully

cellstyle.xlsx文件如下所示。