Apache Poi 简明教程

Apache POI - Cells

您输入到电子表格中的任何数据始终存储在单元格中。我们使用行和列的标签来标识单元格。本章介绍如何使用 Java 编程操纵电子表格中单元格中的数据。

Any data that you enter into a spreadsheet is always stored in a cell. We use the labels of rows and columns to identify a cell. This chapter describes how to manipulate data in cells in a spreadsheet using Java programming.

Create a Cell

创建单元格之前，您需要创建一行。行只不过是一组单元格。

You need to create a row before creating a cell. A row is nothing but a collection of cells.

以下代码段用于创建单元格。

The following code snippet is used for creating a cell.

//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);

Types of Cells

单元格类型指定单元格可以包含字符串、数值或公式。字符串单元格不能容纳数值，数值单元格不能容纳字符串。

The cell type specifies whether a cell can contain strings, numeric value, or formulas. A string cell cannot hold numeric values and a numeric cell cannot hold strings.

以下代码用于在电子表格中创建不同类型的单元格。

The following code is used to create different types of cells in a spreadsheet.

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 date");
      row.createCell(1).setCellValue(new Date());

      row = spreadsheet.createRow((short) 6);
      row.createCell(0).setCellValue("set cell type numeric");
      row.createCell(1).setCellValue(20 );

      row = spreadsheet.createRow((short) 7);
      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 的文件中，从命令提示符编译并执行代码，如下所示：

Save the above code in a file named TypesofCells.java, compile and execute it from the command prompt as follows −

$javac TypesofCells.java
$java TypesofCells

如果你的系统配置有 POI 库，那么你可以在当前目录中编译并执行代码，生成一个名为 typesofcells.xlsx 的 Excel 文件，然后显示以下输出。

If your system is configured with the POI library, then it will compile and execute to generate an Excel file named typesofcells.xlsx in your current directory and display the following output.

typesofcells.xlsx written successfully

typesofcells.xlsx 文件如下所示：

The typesofcells.xlsx file looks as follows −

Cell Styles

在这里，你可以学习如何进行单元格格式化，并应用不同的样式，如合并相邻单元格、添加边框、设置单元格对齐和填充颜色。

Here you can learn how to do cell formatting and apply different styles such as merging adjacent cells, adding borders, setting cell alignment and filling with colors.

以下代码用于使用 Java 编程对单元格应用不同的样式。

The following code is used to apply different styles to cells using Java programming.

import java.io.File;
import java.io.FileOutputStream;

import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
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.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.Color;
import org.apache.poi.ss.usermodel.FillPatternType;

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(HorizontalAlignment.LEFT);
      style1.setVerticalAlignment(VerticalAlignment.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(HorizontalAlignment.CENTER);
      style2.setVerticalAlignment(VerticalAlignment.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(HorizontalAlignment.RIGHT);
      style3.setVerticalAlignment(VerticalAlignment.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(HorizontalAlignment.JUSTIFY);
      style4.setVerticalAlignment(VerticalAlignment.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(BorderStyle.THICK);
      style5.setBottomBorderColor(IndexedColors.BLUE.getIndex());
      style5.setBorderLeft(BorderStyle.DOUBLE);
      style5.setLeftBorderColor(IndexedColors.GREEN.getIndex());
      style5.setBorderRight(BorderStyle.HAIR);
      style5.setRightBorderColor(IndexedColors.RED.getIndex());
      style5.setBorderTop(BorderStyle.DOTTED);
      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(IndexedColors.LIME.index);
      style6.setFillPattern(FillPatternType.LESS_DOTS);
      style6.setAlignment(HorizontalAlignment.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(IndexedColors.BLUE.index);
      style7.setFillPattern( FillPatternType.LESS_DOTS);
      style7.setAlignment(HorizontalAlignment.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 的文件中，从命令提示符编译并执行代码，如下所示：

Save the above code in a file named CellStyle.java, compile and execute it from the command prompt as follows −

$javac CellStyle.java
$java CellStyle

它将在你的当前目录中生成一个名为 cellstyle.xlsx 的 Excel 文件，然后显示以下输出。

It will generate an Excel file named cellstyle.xlsx in your current directory and display the following output.

cellstyle.xlsx written successfully