使用POI 生成excel 笔记

不同Workbook 实现类的区别

在poi 中已知存在三种实现类：

• HSSFWorkbook：操作Excel2003以前（包括2003）的版本，支持扩展名 .xls；
• XSSFWorkbook：扩展名.xlsx,操作版本Excel2007
• SXSSFWorkbook：XSSFWorkbook的升级版，用于解决写入大量数据时内存溢出问题。因为在任何时候允许部分可配置的行存在内存中。

HSSFWorkbook 和 其他两个版本，某些设置操作具有较大的差异。当设置未生效时，应先考虑Workbook的差异

依赖

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

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>

包 poi.jar 支持 XLS格式的文件（HSSF ），
poi-ooxml 用来支持office2007文件 xlsx（XSSF，SXSSF）。

其中poi-ooxml 依赖 poi和poi-ooxml-schemas。

更多的poi jar信息 ，请参考博客： [JAVA]POI各Jar包的作用

Row

1. getFirstCellNum
   获取该行第一列的索引值。
2. getLastCellNum
   注意它的返回值该行的最后一列的索引加一

   Gets the index of the last cell contained in this row PLUS ONE

3. getPhysicalNumberOfCells
   获取设置值的单元格数量。如果只有 2,4，5列 设置了值（包括空字符串）那么返回3
4. createCell
   创建一个单元格，参数是单元的列索引（索引从0开始）

注意：poi 中 行与列的 开始索引都是0

设置单元格属性及样式

设置字体

// 通过workbook对象创建字体对象
Font titleFont = workbook.createFont();
//设置加粗
titleFont.setBold(true);
//设置字号（10号） 单位磅
titleFont.setFontHeightInPoints((short) 10);
//字体
titleFont.setFontName("微软雅黑");
//颜色
titleFont.setColor(IndexedColors.BLACK.index);

设置单元格宽度

Sheet 接口中提供了方法 setColumnWidth 设置指定单元格宽度。

	 /**
     * Set the width (in units of 1/256th of a character width)
     *
     * <p>
     * The maximum column width for an individual cell is 255 characters.
     * This value represents the number of characters that can be displayed
     * in a cell that is formatted with the standard font (first font in the workbook).
     * </p>
     *
     * <p>
     * Character width is defined as the maximum digit width
     * of the numbers <code>0, 1, 2, ... 9</code> as rendered
     * using the default font (first font in the workbook).
     * <br/>
     * Unless you are using a very special font, the default character is '0' (zero),
     * this is true for Arial (default font font in HSSF) and Calibri (default font in XSSF)
     * </p>
     */
    @Override
    public void setColumnWidth(int columnIndex, int width)

• POI 中设置的宽度单位：字符宽度的1/256。
• 每个单元格的最大宽度 为 255个字符。
• 默认使用 workbook中的第一个字体渲染字符，其中渲染数字中的最大宽度作为字符宽度。
• 除非指定了特殊的字体，否则默认的字符是0，HSSF中字体为Arial 字号11 ， XSSF为Calibri 字号11

使用字符宽度是为了方便屏幕输出的计量单位，当要确定屏幕显示尺寸时，用能够“显示多少个标准字符”来描述更直观。而使用磅等单位的话，不同的字体不同的字号会占据不同的值。

设置行高度

不同于单元格的列宽，行高具有相对稳定的单位那就是 磅。

Row 接口中提供了两种方法来设置行高。区别在于行高的单位不一致。

excel中存在默认行高，这个行高作用于所有的行。当然也可以自定义每一行的行高，那么就需要对每一行进行设置

 	/**
     * Set the row's height or set to ff (-1) for undefined/default-height.  Set the height in "twips" or  1/20th of a point.
     */
    void setHeight(short height);

	 /**
     * Set the row's height in points.
     */
    void setHeightInPoints(float height);

设置行高10磅

//该方法的单位是缇， 1提=  1磅/20。 
// 所以实际行高为 height/20 磅。
row1.setHeight(6*20);
// 单位时磅
row1.setHeightInPoints(6);

单元格合并

单元格合并需要借助 类CellRangeAddress 实现，并将生成的区域对象添加到sheet 中

/**
 * Creates new cell range. Indexes are zero-based.
 *
 * @param firstRow Index of first row
 * @param lastRow Index of last row (inclusive), must be equal to or larger than {@code firstRow}
 * @param firstCol Index of first column
 * @param lastCol Index of last column (inclusive), must be equal to or larger than {@code firstCol}
 */
public CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)

第一个参数，开始行索引，
第二个参数：结束行索引
第三个参数，开始列索引，
第四个参数：结束列索引

上面提到过行列索引都是从0开始的。

// 创建一个合并单元格， 两行两列。 
// 从第二行开始，第三行结束, 第4列开始，第5列结束
CellRangeAddress address  = new CellRangeAddress(1,2,3,4);
sheet.addMergedRegion(address);

列合并

特别注意： row.createCell 方法的列数 ，必须加上已经跨的单元格列数。否则合并的单元格，数据不显示。

//第一行
Row row = sheet.createRow(0);
// 第一个单元格， 一行跨四列
CellRangeAddress address = new CellRangeAddress(0, 0, 0, 3);
sheet.addMergedRegion(address);
Cell cell = row.createCell(0);
cell.setCellValue("跨四列");

//第二个单元格跨1列，但是位置必须为4
cell = row.createCell(4);
cell.setCellValue("第二个单元格");

// 第二个单元格修改为
 cell = row.createCell(3);
 cell.setCellValue("第二个单元格");

如果创建第二个单元格时，没有将前面合并的单元格所占的列数加上，则第二个单元格内容不显示

行合并

行合并和列合并本质是相同的，和列合并一样需要注意合并后的索引变化。

在此创建行此时的行索引值等于上一行的索引值加上合并的行数

Row row = sheet.createRow(1);
CellRangeAddress address = new CellRangeAddress(1, 2, 0, 0);
sheet.addMergedRegion(address);

row.createCell(0).setCellValue("合并单元格1111111111111111111111111111111111");
row.createCell(1).setCellValue("第2行");

row = sheet.createRow(2);
row.createCell(1).setCellValue("第3行");

row = sheet.createRow(3);
row.createCell(0).setCellValue("第4行111111111111111111111111111111111111");

注意CellRangeAddress和 createRow 索引的变化

需要注意的 行合并后，即便设置了 CellStyle#setWrapText(true) 高度也无法自适应。

设置边框

对于非合并的单元格，设置边框比较简单。如果是合并的单元格还需要其他的步骤。

    Row row=sheet.createRow(0);
   
    CellStyle cellStyle =  workbook.createCellStyle();
     // 设置四个边的边框
    cellStyle.setBorderTop(BorderStyle.THIN);
    cellStyle.setBorderLeft(BorderStyle.THIN);
    cellStyle.setBorderRight(BorderStyle.THIN);
    cellStyle.setBorderBottom(BorderStyle.THIN);

	//设置第一个单元格
    CellRangeAddress address = new CellRangeAddress(0,0,0,3);
    sheet.addMergedRegion(address);
    Cell cell1 = row.createCell(0);
    cell1.setCellValue("合并单元格");
    cell1.setCellStyle(cellStyle);
   
    //第二个单元格
    Cell cell2 = row.createCell(4);
    cell2.setCellValue("第二个单元格");
    cell2.setCellStyle(cellStyle);

可以通过辅助类来设置边框

RegionUtil.setBorderLeft(BorderStyle.THIN,address, sheet);
RegionUtil.setBorderTop(BorderStyle.THIN,address, sheet);
RegionUtil.setBorderRight(BorderStyle.THIN,address, sheet);
RegionUtil.setBorderBottom(BorderStyle.THIN,address, sheet);

CellRangeAddress address = new CellRangeAddress(0,0,0,3);
sheet.addMergedRegion(address);
Cell cell1 = row.createCell(0);
cell1.setCellValue("合并单元格");
cell1.setCellStyle(cellStyle);

设置背景色

POI的设置背景色必须与另外一个设置结合起来

CellStyle style = workbook.createCellStyle();
//设置背景色
cellStyle.setFillForegroundColor(IndexedColors.CORAL.getIndex());
//填充模式
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

参考： POI 设置单元格背景色，背景色编码与实际颜色对照表

设置自定义背景色

在 XSSFCellStyle 中可以通过方法setFillForegroundColor可以设置某个自定义背景色。

void org.apache.poi.xssf.usermodel.XSSFCellStyle.setFillForegroundColor(XSSFColor color)

//自定义颜色,输入值分别为 red、green、blue 对应的值（0~225）
XSSFCellStyle cellStyle = (XSSFCellStyle)getStringCellStyle(workbook);
//自定义颜色,输入值分别为 red、green、blue 对应的值（0~225）
XSSFColor color = new XSSFColor(new java.awt.Color(12, 234, 123));
//设置填充模式，出色前景色
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//设置填充颜色
cellStyle.setFillForegroundColor(color);

关于设置背景色不生效的问题

项目中使用的是SXSSFWorkbook 创建的对象，项目测试中发现对于不涉及合并的单元格可以正常设置自定义背景色。但是对于合并的单元格没有起作用并显示为黑色。

考虑到之前遇到的单元格合并设置边框显示不全的问题。尝试对处于合并区域的单元格都设置一遍样式（包含背景色），然后就可以正常显示。
代码和工具类RegionUtil类似

//合并区域的开始行
int rowStart = address.getFirstRow();
//合并区域的结束行
int rowEnd = address.getLastRow();
for (int i = rowStart; i <= rowEnd; i++) {
	//合并区域的开始列
	int columStart =address.getFirstColumn();
	//合并区域的结束列
	int columnEnd = address.getLastColumn();
	row = sheet.getRow(i);
	for(int j=columStart;j<columnEnd;j++) {
		//设置样式，categoryStyle 和上面代码（设置自定义背景色部分）含义相同。
		//对合并区域中的每个单元格设置样式
		row.getCell(j).setCellStyle(categoryStyle);
	}
}

获取单元格的值

获取单元格的值

POI 为获取单元格数据的提供了很多API

很多时候我们不关注数据的具体格式，统一使用字符串接受。那么如果使用 getStringCellValue() 获取一个数字格式的数据那么就会报错( Cannot get a NUMERIC value from a STRING cell )。

那么就可以通过设置 cell.setCellType(Cell.CELL_TYPE_STRING); 就可以以字符串的形式获取数据了。但是对于日期类型的单元格（ 1900-1-1 — 9999 -12-31） 将会获取到一个 数字（当前日期到 1900-1-1 的天数 ）。

public String getValue(Cell cell) {
		if (cell == null)
			return "";

		// 判断是否是日期格式
		if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
			if (DateUtil.isCellDateFormatted(cell)) {
				Date date2 = cell.getDateCellValue();
				SimpleDateFormat dff = new SimpleDateFormat("yyyy/MM/dd");
				String date1 = dff.format(date2); // 日期转化
				return date1;
			}
		}
		cell.setCellType(Cell.CELL_TYPE_STRING);
		return cell.getStringCellValue().trim();
	}

生成excel时，数据格式化

excel 格式化， 可以借助 DataFormat 实现。

private static short getDataFormat(Workbook workbook, String pattern) {
    DataFormat format = workbook.createDataFormat();
    return format.getFormat(pattern);
}

百分比

pattern : 0.00%

private static CellStyle getPercentCellStyle(Workbook workbook) {
    CellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setDataFormat(getDataFormat(workbook, "0.00%"));
    return cellStyle;
}

注意： 如果数值，设置的百分比格式，会乘以100后展示。比如： 输入0.2 ，显示 20%

金额格式

金额，千分位展示。 保留两位小数。
pattern : #,###,###,###,###,###,##0.00

private static CellStyle getNumberCellStyle(Workbook workbook) {
    CellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setDataFormat(getDataFormat(workbook, "#,###,###,###,###,###,##0.00"));
    return cellStyle;
}

日期格式

pattern : yyyy年m月d日

含义	标识符
将年显示为 00–99	yy
将年显示为 1900–9999	yyyy
将月份显示为 1–12	m
将月份显示为 01–12	mm
将日期显示为 1–31	d
将日期显示为 01–31	dd
小时为 0-23	h
小时数为 00–23	hh
分钟为 0-59	m
分钟为 00-59	mm
秒数为 0-59	s
秒为 00–59	ss

更多自定义事件样式，请查看Microsoft官网 将数字设置为日期或时间

注意： 如果在h或hh后，或者在ss前使用m，表示分钟而不是月份。

private static CellStyle getDateCellStyle(Workbook workbook) {
    CellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setDataFormat(getDataFormat(workbook, "yyyy年m月d日"));
    return cellStyle;
}

实例

    public static void main(String[] args) throws IOException {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet();

        // 设置单元格格式
        CellStyle numberCellStyle = getNumberCellStyle(workbook);
        CellStyle percentCellStyle = getPercentCellStyle(workbook);
        CellStyle stringCellStyle = getStringCellStyle(workbook);
        CellStyle dateCellStyle = getDateCellStyle(workbook);
        CellStyle timeCellStyle = getTimeCellStyle(workbook);

        int rowNum = 0;
        // 第一行
        Row firstRow = sheet.createRow(rowNum);
        setCellValue(stringCellStyle, firstRow, 0, "金额：");
        setCellValue(numberCellStyle, firstRow, 1, 12345567);
        setCellValue(numberCellStyle, firstRow, 2, 10.123);
        setCellValue(numberCellStyle, firstRow, 3, 0.582);

        rowNum++;
        Row secondRow = sheet.createRow(rowNum);
        setCellValue(stringCellStyle, secondRow, 0, "百分比： ");
        setCellValue(percentCellStyle, secondRow, 1, 1);
        setCellValue(percentCellStyle, secondRow, 2, 1.21);
        setCellValue(percentCellStyle, secondRow, 3, 0.2123);

        // 第三行
        rowNum++;
        Row thirdRow = sheet.createRow(rowNum);
        setCellValue(stringCellStyle, thirdRow, 0, "时间：");
        setCellValue(dateCellStyle, thirdRow, 1, LocalDate.now());
        setCellValue(timeCellStyle, thirdRow, 2, LocalDateTime.now());

        // 写文件
        workbook.write(new FileOutputStream(new File("E:/2.xlsx")));
    }

    private static short getDataFormat(Workbook workbook, String pattern) {
        DataFormat format = workbook.createDataFormat();
        return format.getFormat(pattern);
    }

    private static CellStyle getNumberCellStyle(Workbook workbook) {
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setDataFormat(getDataFormat(workbook, "#,###,###,###,###,###,##0.00"));
        return cellStyle;
    }

    private static CellStyle getPercentCellStyle(Workbook workbook) {
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setDataFormat(getDataFormat(workbook, "0.00%"));
        return cellStyle;
    }

    private static CellStyle getDateCellStyle(Workbook workbook) {
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setDataFormat(getDataFormat(workbook, "yyyy年m月d日"));
        return cellStyle;
    }

    private static CellStyle getTimeCellStyle(Workbook workbook) {
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setDataFormat(getDataFormat(workbook, "h:mm:ss"));
        return cellStyle;
    }

    private static CellStyle getStringCellStyle(Workbook workbook) {
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        return cellStyle;
    }

    private static void setCellValue(CellStyle cellStyle, Row row, int column, Object value) {
        Cell cell = row.createCell(column);
        cell.setCellStyle(cellStyle);

        if (value != null) {
            if (value instanceof Number) {
                cell.setCellValue(((Number) value).doubleValue());
            } else if (ClassUtils.isPrimitiveOrWrapper(value.getClass())) {
                cell.setCellValue(new BigDecimal(value.toString()).doubleValue());
            } else if (value instanceof Date) {
                // 日期格式
                cell.setCellValue((Date) value);
            } else if (value instanceof LocalDate) {
                cell.setCellValue((LocalDate) value);
            } else if (value instanceof LocalDateTime) {
                cell.setCellValue((LocalDateTime) value);
            } else {
                cell.setCellValue(value.toString());
            }
        }
    }

错误

Cannot get a STRING value from a NUMERIC cell

很显然是单元格格式的问题，默认情况下纯数字是 数值格式的，虽然显示的格式常规。测试方式很简单，就是输入较多的数字会以 科学计数法显示。

解决方式通过 setCellType 方法设置指定单元格的数据格式：

cell.setCellType(CellType.STRING);

java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0…65535)

写入行数超过最大限制。 因为爱情所以 使用HSSFWorkbook 写入Excel，然后就报出了如上的错误。这是因为.xls 格式的Excel 允许的最大行数为65536。

所以解决方案就是使用XSSFWorkbook或者SXSSFWorkbook生成后缀为.xlsx的文件

org.apache.poi.openxml4j.exceptions.NotOfficeXmlFileException: No valid entries or contents found, this is not a valid OOXML (Office Open XML) file。

上传Excel 文件时抛出来该错误。使用Excel打开时发现文件已损坏无法打开，所以该异常的原因之一就是 文件本身损坏。

---

引用文献：

1. [JAVA]POI各Jar包的作用
2. POI 设置单元格背景色，背景色编码与实际颜色对照表
3. Microsoft官网 将数字设置为日期或时间