如何在Excel 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操作将一个单元格格式化为Date格式，每当我尝试复制单元格的日期格式时，在POI中，它只能给我一个数字，我想知道如何保留日期格式？

  //获取源单元格类型和样式
 CellType type_from = cell_from.getCellTypeEnum（）; 
 CellStyle style_from = cell_from.getCellStyle（）; 
 
 //获取源单元数据格式
 short df = style_from.getDataFormat（）; 
 
 //更改dest cell单元格类型，在其上设置格式
 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. *; 
 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; 
 
公共类ExcelSetCellStyleDataFormat {
 
 //从单元格获取当前字体的方法
 private static字体getFont（单元格单元格）{
工作簿wb =单元格。.getRow（）getSheet（）getWorkbook（）; 
 CellStyle style = cell.getCellStyle（）; 
返回wb.getFontAt（style.getFontIndex（））; 
} 
 
私人枚举FontProperty {
 BOLD，COLOR，FONTHEIGHT，FONTNAME，ITALIC，STRIKEOUT，TYPEOFFSET，UNDERLINE 
} 
 
 / /获取给定源字体附加特殊设置的字体的方法
 private static字体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 ）; 
休息; 
 case COLOR：
 color =（short）fontproperties.get（property）; 
休息; 
 case FONTHEIGHT：
 fontHeight =（short）fontproperties.get（property）; 
休息; 
 case FONTNAME：
 fontName =（String）fontproperties.get（property）; 
休息; 
 case ITALIC：
 isItalic =（boolean）fontproperties.get（property）; 
休息; 
 case STRIKEOUT：
 isStrikeout =（boolean）fontproperties.get（property）; 
休息; 
 case TYPEOFFSET：
 typeOffset =（short）fontproperties.get（property）; 
休息; 
 case UNDERLINE：
 underline =（byte）fontproperties.get（property）; 
休息; 
} 
} 
 
 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（下划线）; 
} 
 
返回字体; 
} 
 
 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 = null; 
 Cell cell = null; 
 Font font = null; 
 Map< String，Object> styleproperties = null; 
 Map< FontProperty，Object> fontproperties = null; 
 
 //将单元格A2转换为数字，字体大小为16pt，绿色填充颜色：
 //获取单元格A2 
 row = CellUtil.getRow（1，sheet）; 
 cell = CellUtil.getCell（row，0）; 
 
 //获取旧单元格值并将其设置为数字
 String cellvalue = formatter.formatCellValue（cell）; 
 cell.setCellValue（Double.valueOf（cellvalue））; 
 
 //获取所需字体
 fontproperties = new HashMap< FontProperty，Object>（）; 
 fontproperties.put（FontProperty.FONTHEIGHT，（简称）（16 * 20））; 
 font = getFont（wb，getFont（cell），fontproperties）; 
 
 //设置新的单元格样式属性
 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）; 
 
 //从B1获取数据格式b $ b row = CellUtil.getRow（0，sheet）; 
 cell = CellUtil.getCell（row，1）; 
 short dataFormatB1 = cell.getCellStyle（）。getDataFormat（）; 
 
 //将B2变为与B1和斜体字体相同的数据格式：
 //获取单元格B2 
 row = CellUtil.getRow（1，sheet）; 
 cell = CellUtil.getCell（row，1）; 
 
 //获取所需字体
 fontproperties = new HashMap< FontProperty，Object>（）; 
 fontproperties.put（FontProperty.ITALIC，true）; 
 font = getFont（wb，getFont（cell），fontproperties）; 
 
 //设置新的单元格样式属性
 styleproperties = new HashMap< String，Object>（）; 
 styleproperties.put（CellUtil.DATA_FORMAT，dataFormatB1）; 
 styleproperties.put（CellUtil.FONT，font.getIndex（））; 
 CellUtil.setCellStyleProperties（cell，styleproperties）; 
 
 //设置具有特殊字体设置的新单元格D6 
 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，（简称）（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）; 
 
 //设置新单元格C4具有特殊字体设置
 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，（简称）（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）; 
 //将富文本字符串设置为该单元格
 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（）; 
} 
} 
  

结果：

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:

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