如何在 Apache POI 中将 Excel 单元格格式化为日期，就像 Excel 中的格式刷操作一样 [英] How to format Excel Cell as Date in Apache POI as with the Format Painter operation in Excel

问题描述

我对Apache POI很陌生，我想知道如何进行Format Painter操作将单元格格式化为日期格式，每次尝试复制单元格的日期格式时，在POI中，它只能给我数字, 我想知道如何保持日期格式?

//获取源单元格类型和样式CellType type_from = cell_from.getCellTypeEnum();CellStyle style_from = cell_from.getCellStyle();//获取源单元格数据格式短 df = style_from.getDataFormat();//改变目标单元格类型，在其上设置格式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相同但为斜体.

解决方案

正如评论中所说，只有您的代码片段无法重现该问题.这说明了为什么需要

代码:

import java.io.*;导入 org.apache.poi.ss.usermodel.*;导入 org.apache.poi.xssf.usermodel.*;导入 org.apache.poi.ss.util.CellUtil;导入 java.util.Map;导入 java.util.HashMap;公共类 ExcelSetCellStyleDataFormat {//从单元格获取当前字体的方法私有静态字体 getFont(Cell cell) {工作簿 wb = cell.getRow().getSheet().getWorkbook();CellStyle 样式 = cell.getCellStyle();返回 wb.getFontAt(style.getFontIndex());}私有枚举 FontProperty {粗体、彩色、正字体、字体名称、斜体、删除线、字体偏移、下划线}//获取具有给定源字体之外的特殊设置的字体的方法private static Font getFont(Workbook wb, Font fontSrc, Map fontproperties) {boolean isBold = fontSrc.getBold();短颜色 = fontSrc.getColor();短 fontHeight = fontSrc.getFontHeight();String fontName = fontSrc.getFontName();boolean isItalic = fontSrc.getItalic();boolean isStrikeout = fontSrc.getStrikeout();short typeOffset = fontSrc.getTypeOffset();字节下划线 = fontSrc.getUnderline();for (FontProperty 属性: fontproperties.keySet()) {开关(属性){案例粗体:isBold = (boolean)fontproperties.get(property);休息;外壳颜色:color = (short)fontproperties.get(property);休息;案例正版:fontHeight = (short)fontproperties.get(property);休息;案例字体名称:fontName = (String)fontproperties.get(property);休息;案例斜体:isItalic = (boolean)fontproperties.get(property);休息;案例罢工:isStrikeout = (boolean)fontproperties.get(property);休息;案例类型偏移:typeOffset = (short)fontproperties.get(property);休息;案例下划线:下划线 = (byte)fontproperties.get(property);休息;}}Font font = wb.findFont(isBold, color, fontHeight, fontName, isItalic, isStrikeout, typeOffset, underline);如果(字体==空){font = wb.createFont();font.setBold(isBold);font.setColor(颜色);font.setFontHeight(fontHeight);font.setFontName(fontName);font.setItalic(isItalic);font.setStrikeout(isStrikeout);font.setTypeOffset(typeOffset);font.setUnderline(下划线);}返回字体；}public static void main(String[] args) 抛出异常 {工作簿 wb = WorkbookFactory.create(new FileInputStream("ExcelTest.xlsx"));DataFormatter formatter = new DataFormatter();Sheet sheet = wb.getSheetAt(0);行行 = 空；单元格单元格 = 空；字体 font = null;映射<字符串，对象>样式属性 = 空；地图<FontProperty, Object>字体属性 = 空；//将A2单元格变成数字，字体大小为16pt，填充颜色为绿色://获取单元格A2row = CellUtil.getRow(1, sheet);cell = CellUtil.getCell(row, 0);//获取旧的单元格值并将其设置为数字String cellvalue = formatter.formatCellValue(cell);cell.setCellValue(Double.valueOf(cellvalue));//获取需要的字体fontproperties = new HashMap();fontproperties.put(FontProperty.FONTHEIGHT, (short)(16*20));font = getFont(wb, getFont(cell), fontproperties);//设置新的单元格样式属性styleproperties = new HashMap();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);//从B1获取数据格式row = CellUtil.getRow(0, sheet);cell = CellUtil.getCell(row, 1);短数据格式B1 = cell.getCellStyle().getDataFormat();//将B2转成与B1相同的数据格式和斜体://获取单元格B2row = CellUtil.getRow(1, sheet);cell = CellUtil.getCell(row, 1);//获取需要的字体fontproperties = new HashMap();fontproperties.put(FontProperty.ITALIC, true);font = getFont(wb, getFont(cell), fontproperties);//设置新的单元格样式属性styleproperties = new HashMap();styleproperties.put(CellUtil.DATA_FORMAT, dataFormatB1);styleproperties.put(CellUtil.FONT, font.getIndex());CellUtil.setCellStyleProperties(cell, styleproperties);//设置具有特殊字体设置的新单元格D6row = CellUtil.getRow(5, sheet);cell = CellUtil.getCell(row, 3);fontproperties = new HashMap();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();styleproperties.put(CellUtil.FONT, font.getIndex());CellUtil.setCellStyleProperties(cell, styleproperties);cell.setCellValue("新建单元格");//设置具有特殊字体设置的新单元格C4row = CellUtil.getRow(3, sheet);cell = CellUtil.getCell(row, 2);fontproperties = new HashMap();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();styleproperties.put(CellUtil.FONT, font.getIndex());CellUtil.setCellStyleProperties(cell, styleproperties);//将富文本字符串设置到该单元格中RichTextString richString = new XSSFRichTextString("E = m c2");//^0 ^7fontproperties = new HashMap();fontproperties.put(FontProperty.TYPEOFFSET, Font.SS_SUPER);font = getFont(wb, getFont(cell), fontproperties);richString.applyFont(7, 8, 字体);cell.setCellValue(richString);wb.write(new FileOutputStream("ExcelTestNew.xlsx"));wb.close();}}

结果:

I am pretty new to Apache POI, I wonder how to do that Format Painter operation to format a cell into Date format, each time when I try to copy the date format of cell, in POI, it can only give me Numeric, I wonder how can I keep the date format?

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

And I need to change some other style, like border, background color, font italic, etcs. Could you give out one example: create one xlsx file, set 1A to number( say 10 ), 2A to text ("10") 1B to date(01/12/2018), 2B to 10000(just a number), then try to turn 2A into number with font 16 and green cell background, and turn 2B into date with same format as 1B but italic font.

解决方案

As said in comment already, the issue is not reproducible having your code snippet only. That shows why Minimal, Complete, and Verifiable examples are necessary.

I suspect the following: You are having the snippet in a loop and do changing the same style style_to multiple times having different style_from as source. This is possible since multiple cells cell_to may sharing the same style.

Manipulating cell styles for Excel spreadsheets is not as simple as one may think. The cell styles are stored on workbook level and are limited to 64,000 unique cell formats/cell styles in modern Excel versions. So one must be careful with new creating cell styles. At least one should not trying creating a new cell style for each single cell.

Apache poi provides CellUtil which has various utility functions that make working with a cells and rows easier. The various methods that deal with style's allow you to create your CellStyles as you need them. When you apply a style change to a cell, the code will attempt to see if a style already exists that meets your needs. If not, then it will create a new style. This is to prevent creating too many styles. What lacks until now is the same utility functions for dealing with fonts. Fonts also are on workbook level and so also should not being created without care.

The following example provides utility functions for creating fonts too.

It takes the ExcelTest.xlsx as you had described in your last comment and makes the changings you also had described there. It also makes some additional changings to show how the utility functions are working.

Source:

Code:

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();
 }  
}

Result:

这篇关于如何在 Apache POI 中将 Excel 单元格格式化为日期，就像 Excel 中的格式刷操作一样的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！