Changeset 7674


Ignore:
Timestamp:
Mar 27, 2019, 9:27:06 AM (3 years ago)
Author:
Nicklas Nordborg
Message:

References #2161: Import data from Excel files

Added functionality for setting a base style that is used when creating new cells on a worksheet. The intention is that it can be used to, for example, set a bold font the header line, or to style different lines with different background colors.

Location:
trunk/src/core/net/sf/basedb/util/excel
Files:
2 edited

Legend:

Unmodified
Added
Removed
  • trunk/src/core/net/sf/basedb/util/excel/CellStyleCreator.java

    r7647 r7674  
    22
    33import java.util.HashMap;
     4import java.util.IdentityHashMap;
    45import java.util.Locale;
    56import java.util.Map;
     
    78import org.apache.poi.ss.usermodel.CellStyle;
    89import org.apache.poi.ss.usermodel.DataFormat;
     10import org.apache.poi.ss.usermodel.Font;
    911import org.apache.poi.ss.usermodel.Workbook;
    1012import org.apache.poi.ss.util.DateFormatConverter;
     
    2022public class CellStyleCreator
    2123{
    22  
    23   private final Workbook workbook;
    24   private final DataFormat dataFormat;
    25   private final Map<String, CellStyle> styles;
    26   private final CellStyle intStyle;
    2724
    28   /**
    29     Creates a new style creator for the given Excel workbook.
    30   */
    31   public CellStyleCreator(Workbook workbook)
    32   {
    33     this.workbook = workbook;
    34     this.dataFormat = workbook.createDataFormat();
    35     this.styles = new HashMap<String, CellStyle>();
    36     this.intStyle = getCellStyle("0");
    37   }
    38  
    3925  /**
    4026    Converts a Java date format string to the corresponding Excel
    4127    date format string.
    4228  */
    43   public String convertJavaDateFormatToExcelFormat(String javaFormat)
     29  public static String convertJavaDateFormatToExcelFormat(String javaFormat)
    4430  {
    4531    if (javaFormat == null) return null;
     
    5642    return excelFormat;
    5743  }
     44
    5845 
     46  private final Workbook workbook;
     47  private final DataFormat dataFormat;
     48  // Base style --> Format string --> Final cell style
     49  private final Map<CellStyle, Map<String, CellStyle>> styles;
     50  private CellStyle baseStyle;
     51
     52  /**
     53    Creates a new style creator for the given Excel workbook.
     54  */
     55  public CellStyleCreator(Workbook workbook)
     56  {
     57    this.workbook = workbook;
     58    this.dataFormat = workbook.createDataFormat();
     59    this.styles = new IdentityHashMap<>();
     60  }
     61 
     62  /**
     63    Create a new cell style.
     64  */
     65  public CellStyle createStyle()
     66  {
     67    return workbook.createCellStyle();
     68  }
     69 
     70  /**
     71    Create a new font style.
     72  */
     73  public Font createFont()
     74  {
     75    return workbook.createFont();
     76  }
     77 
     78  /**
     79    Get the current base style for new cells.
     80  */
     81  public CellStyle getBaseStyle()
     82  {
     83    return baseStyle;
     84  }
     85 
     86  /**
     87    Set a base style to use for new cells. When a base style has been set it used
     88    as a template for all new styles that are created with {@link #getCellStyle(String)}.
     89    This can be useful for setting different fonts, colors, etc.
     90  */
     91  public void setBaseStyle(CellStyle baseStyle)
     92  {
     93    this.baseStyle = baseStyle;
     94  }
    5995 
    6096  /**
     
    65101  public CellStyle getIntCellStyle()
    66102  {
    67     return intStyle;
     103    return getCellStyle("0");
    68104  }
    69105 
     
    93129  public CellStyle getCellStyle(String format)
    94130  {
    95     if (format == null) return null;
    96     CellStyle style = styles.get(format);
     131    if (format == null) return baseStyle;
     132    Map<String, CellStyle> formats = styles.get(baseStyle);
     133    if (formats == null)
     134    {
     135      formats = new HashMap<>();
     136      styles.put(baseStyle, formats);
     137    }
     138    CellStyle style = formats.get(format);
    97139    if (style == null)
    98140    {
    99141      style = workbook.createCellStyle();
     142      if (baseStyle != null) style.cloneStyleFrom(baseStyle);
    100143      style.setDataFormat(dataFormat.getFormat(format));
    101       styles.put(format, style);
     144      formats.put(format, style);
    102145    }
    103146    return style;
  • trunk/src/core/net/sf/basedb/util/excel/ExcelValue.java

    r7651 r7674  
    172172    {
    173173      cell.setCellType(CellType.BLANK);
     174      cell.setCellStyle(styleCreator.getBaseStyle());
    174175    }   
    175176  }
     
    190191    {
    191192      cell.setCellValue(value);
     193      cell.setCellStyle(styleCreator.getBaseStyle());
    192194    }
    193195  }
Note: See TracChangeset for help on using the changeset viewer.