java - 如何使用 Excel 中的格式刷操作在 Apache POI 中将 Excel 单元格格式设置为日期

Question

我对 Apache POI 还很陌生，我想知道如何执行格式刷操作将单元格格式化为日期格式，每次当我尝试复制单元格的日期格式时，在 POI 中，它只能给我数字，我想知道如何保留日期格式？

    // Get source cell type and style
    CellType type_from = cell_from.getCellTypeEnum();
    CellStyle style_from = cell_from.getCellStyle();

    // Get source cell data format
    short df = style_from.getDataFormat();

    // Change dest cell cell type, set format on it
    cell_to.setCellType(type_from);
    CellStyle style_to = cell_to.getCellStyle();
    style_to.setDataFormat(df);
    cell_to.setCellStyle(style_to);

我需要更改一些其他样式，例如边框、背景颜色、斜体字体等。你能举一个例子吗:创建一个xlsx文件，将1A设置为数字(比如10)，2A设置为文本(“10”)1B设置为日期(01/12/2018)，2B设置为10000(只是一个数字)，然后尝试将 2A 转换为数字，字体为 16，单元格背景为绿色，将 2B 转换为与 1B 格式相同但斜体的日期。

Answer 1

正如评论中所述，仅使用您的代码片段无法重现该问题。这说明了原因 Minimal, Complete, and Verifiable examples是必要的。

我怀疑以下情况:您将片段置于循环中，并多次更改相同的样式 style_to 并将不同的 style_from 作为源。这是可能的，因为多个单元格 cell_to 可能共享相同的样式。

操作 Excel 电子表格的单元格样式并不像人们想象的那么简单。单元格样式存储在工作簿级别，为 limited现代 Excel 版本中包含 64,000 种独特的单元格格式/单元格样式。因此，必须小心创建新的单元格样式。至少不应尝试为每个单元格创建新的单元格样式。

Apache poi 提供 CellUtil它具有各种实用功能，使单元格和行的处理变得更加容易。处理样式的各种方法允许您根据需要创建 CellStyle。当您将样式更改应用于单元格时，代码将尝试查看是否已存在满足您需求的样式。如果没有，那么它将创造一种新的风格。这是为了防止创建太多样式。到目前为止，缺少的是处理字体的相同实用函数。字体也是工作簿级别的，因此也不应该不小心地创建。

以下示例也提供了用于创建字体的实用函数。

它采用您在上一条评论中描述的 ExcelTest.xlsx 并进行您在此处描述的更改。它还进行了一些额外的更改以显示实用程序函数如何工作。

来源:

代码:

import java.io.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import org.apache.poi.ss.util.CellUtil;

import java.util.Map;
import java.util.HashMap;

public class ExcelSetCellStyleDataFormat {

 //method for getting current font from cell
 private static Font getFont(Cell cell) {
  Workbook wb = cell.getRow().getSheet().getWorkbook();
  CellStyle style = cell.getCellStyle();
  return wb.getFontAt(style.getFontIndex());
 }

 private enum FontProperty {
  BOLD, COLOR, FONTHEIGHT, FONTNAME, ITALIC, STRIKEOUT, TYPEOFFSET, UNDERLINE
 }

 //method for getting font having special settings additional to given source font
 private static Font getFont(Workbook wb, Font fontSrc, Map<FontProperty, Object> fontproperties) {
  boolean isBold = fontSrc.getBold();
  short color = fontSrc.getColor();
  short fontHeight = fontSrc.getFontHeight();
  String fontName = fontSrc.getFontName();
  boolean isItalic = fontSrc.getItalic();
  boolean isStrikeout = fontSrc.getStrikeout();
  short typeOffset = fontSrc.getTypeOffset();
  byte underline = fontSrc.getUnderline();

  for (FontProperty property : fontproperties.keySet()) {
   switch (property) {
    case BOLD:
     isBold = (boolean)fontproperties.get(property);
    break;
    case COLOR:
     color = (short)fontproperties.get(property);
    break;
    case FONTHEIGHT:
     fontHeight = (short)fontproperties.get(property);
    break;
    case FONTNAME:
     fontName = (String)fontproperties.get(property);
    break;
    case ITALIC:
     isItalic = (boolean)fontproperties.get(property);
    break;
    case STRIKEOUT:
     isStrikeout = (boolean)fontproperties.get(property);
    break;
    case TYPEOFFSET:
     typeOffset = (short)fontproperties.get(property);
    break;
    case UNDERLINE:
     underline = (byte)fontproperties.get(property);
    break;
   }
  }

  Font font = wb.findFont(isBold, color, fontHeight, fontName, isItalic, isStrikeout, typeOffset, underline);
  if (font == null) {
   font = wb.createFont();
   font.setBold(isBold);
   font.setColor(color);
   font.setFontHeight(fontHeight);
   font.setFontName(fontName);
   font.setItalic(isItalic);
   font.setStrikeout(isStrikeout);
   font.setTypeOffset(typeOffset);
   font.setUnderline(underline);
  }

  return font;
 }

 public static void main(String[] args) throws Exception {
  Workbook wb = WorkbookFactory.create(new FileInputStream("ExcelTest.xlsx"));

  DataFormatter formatter = new DataFormatter();

  Sheet sheet = wb.getSheetAt(0);

  Row row = null;
  Cell cell = null;
  Font font = null; 
  Map<String, Object> styleproperties = null;
  Map<FontProperty, Object> fontproperties = null;

  //turn cell A2 into numeric, font size 16pt and green fill color:
  //get cell A2
  row = CellUtil.getRow(1, sheet);
  cell = CellUtil.getCell(row, 0);

  //get old cell value and set it as numeric
  String cellvalue = formatter.formatCellValue(cell); 
  cell.setCellValue(Double.valueOf(cellvalue));

  //get the needed font
  fontproperties = new HashMap<FontProperty, Object>();
  fontproperties.put(FontProperty.FONTHEIGHT, (short)(16*20));
  font = getFont(wb, getFont(cell), fontproperties);

  //set new cell style properties
  styleproperties = new HashMap<String, Object>();
  styleproperties.put(CellUtil.DATA_FORMAT, BuiltinFormats.getBuiltinFormat("General"));
  styleproperties.put(CellUtil.FILL_FOREGROUND_COLOR, IndexedColors.GREEN.getIndex());
  styleproperties.put(CellUtil.FILL_PATTERN, FillPatternType.SOLID_FOREGROUND);
  styleproperties.put(CellUtil.FONT, font.getIndex());
  CellUtil.setCellStyleProperties(cell, styleproperties);

  //get data format from B1
  row = CellUtil.getRow(0, sheet);
  cell = CellUtil.getCell(row, 1);
  short dataFormatB1 = cell.getCellStyle().getDataFormat();

  //turn B2 into same data format as B1 and italic font:
  //get cell B2
  row = CellUtil.getRow(1, sheet);
  cell = CellUtil.getCell(row, 1);

  //get the needed font
  fontproperties = new HashMap<FontProperty, Object>();
  fontproperties.put(FontProperty.ITALIC, true);
  font = getFont(wb, getFont(cell), fontproperties);

  //set new cell style properties
  styleproperties = new HashMap<String, Object>();
  styleproperties.put(CellUtil.DATA_FORMAT, dataFormatB1);
  styleproperties.put(CellUtil.FONT, font.getIndex());
  CellUtil.setCellStyleProperties(cell, styleproperties);

  //set new cell D6 having special font settings
  row = CellUtil.getRow(5, sheet);
  cell = CellUtil.getCell(row, 3);
  fontproperties = new HashMap<FontProperty, Object>();
  fontproperties.put(FontProperty.BOLD, true);
  fontproperties.put(FontProperty.COLOR, IndexedColors.BLUE.getIndex());
  fontproperties.put(FontProperty.FONTHEIGHT, (short)(20*20));
  fontproperties.put(FontProperty.FONTNAME, "Courier New");
  fontproperties.put(FontProperty.STRIKEOUT, true);
  fontproperties.put(FontProperty.UNDERLINE, Font.U_DOUBLE);
  font = getFont(wb, getFont(cell), fontproperties);
  styleproperties = new HashMap<String, Object>();
  styleproperties.put(CellUtil.FONT, font.getIndex());
  CellUtil.setCellStyleProperties(cell, styleproperties);
  cell.setCellValue("new cell");

  //set new cell C4 having special font settings
  row = CellUtil.getRow(3, sheet);
  cell = CellUtil.getCell(row, 2);
  fontproperties = new HashMap<FontProperty, Object>();
  fontproperties.put(FontProperty.BOLD, true);
  fontproperties.put(FontProperty.COLOR, IndexedColors.DARK_RED.getIndex());
  fontproperties.put(FontProperty.FONTHEIGHT, (short)(42*20));
  fontproperties.put(FontProperty.FONTNAME, "Times New Roman");
  fontproperties.put(FontProperty.ITALIC, true);
  font = getFont(wb, getFont(cell), fontproperties);
  styleproperties = new HashMap<String, Object>();
  styleproperties.put(CellUtil.FONT, font.getIndex());
  CellUtil.setCellStyleProperties(cell, styleproperties);
  //set rich textt string into that cell
  RichTextString richString = new XSSFRichTextString("E = m c2");
                                                    //^0     ^7
  fontproperties = new HashMap<FontProperty, Object>();
  fontproperties.put(FontProperty.TYPEOFFSET, Font.SS_SUPER);
  font = getFont(wb, getFont(cell), fontproperties);
  richString.applyFont(7, 8, font);
  cell.setCellValue(richString);

  wb.write(new FileOutputStream("ExcelTestNew.xlsx"));
  wb.close();
 }  
}

结果: