/* ====================================================================
Licensed to the Apache Software Foundation (ASF) under one or more
contributor license agreements. See the NOTICE file distributed with
this work for additional information regarding copyright ownership.
The ASF licenses this file to You under the Apache License, Version 2.0
(the "License"); you may not use this file except in compliance with
the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
==================================================================== */
namespace HH.WMS.Utils.NPOI.HSSF.Util
{
using System;
using System.Collections;
using HH.WMS.Utils.NPOI.HSSF.UserModel;
using HH.WMS.Utils.NPOI.SS.UserModel;
///
/// 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 HSSFCellStyles 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. there is an upper limit in Excel on the number of styles that
/// can be supported.
/// @author Eric Pugh epugh@upstate.com
///
public class HSSFCellUtil
{
public static String ALIGNMENT = "alignment";
public static String BORDER_BOTTOM = "borderBottom";
public static String BORDER_LEFT = "borderLeft";
public static String BORDER_RIGHT = "borderRight";
public static String BORDER_TOP = "borderTop";
public static String BOTTOM_BORDER_COLOR = "bottomBorderColor";
public static String DATA_FORMAT = "dataFormat";
public static String FILL_BACKGROUND_COLOR = "fillBackgroundColor";
public static String FILL_FOREGROUND_COLOR = "fillForegroundColor";
public static String FILL_PATTERN = "fillPattern";
public static String FONT = "font";
public static String HIDDEN = "hidden";
public static String INDENTION = "indention";
public static String LEFT_BORDER_COLOR = "leftBorderColor";
public static String LOCKED = "locked";
public static String RIGHT_BORDER_COLOR = "rightBorderColor";
public static String ROTATION = "rotation";
public static String TOP_BORDER_COLOR = "topBorderColor";
public static String VERTICAL_ALIGNMENT = "verticalAlignment";
public static String WRAP_TEXT = "wrapText";
private static UnicodeMapping[] unicodeMappings;
static HSSFCellUtil()
{
unicodeMappings = new UnicodeMapping[15];
unicodeMappings[0] = um("alpha", "\u03B1");
unicodeMappings[1] = um("beta", "\u03B2");
unicodeMappings[2] = um("gamma", "\u03B3");
unicodeMappings[3] = um("delta", "\u03B4");
unicodeMappings[4] = um("epsilon", "\u03B5");
unicodeMappings[5] = um("zeta", "\u03B6");
unicodeMappings[6] = um("eta", "\u03B7");
unicodeMappings[7] = um("theta", "\u03B8");
unicodeMappings[8] = um("iota", "\u03B9");
unicodeMappings[9] = um("kappa", "\u03BA");
unicodeMappings[10] = um("lambda", "\u03BB");
unicodeMappings[11] = um("mu", "\u03BC");
unicodeMappings[12] = um("nu", "\u03BD");
unicodeMappings[13] = um("xi", "\u03BE");
unicodeMappings[14] = um("omicron", "\u03BF");
}
private class UnicodeMapping
{
public String entityName;
public String resolvedValue;
public UnicodeMapping(String pEntityName, String pResolvedValue)
{
entityName = "&" + pEntityName + ";";
resolvedValue = pResolvedValue;
}
}
private HSSFCellUtil()
{
// no instances of this class
}
///
/// Get a row from the spreadsheet, and Create it if it doesn't exist.
///
/// The 0 based row number
/// The sheet that the row is part of.
/// The row indicated by the rowCounter
public static HH.WMS.Utils.NPOI.SS.UserModel.IRow GetRow(int rowCounter, HSSFSheet sheet)
{
HH.WMS.Utils.NPOI.SS.UserModel.IRow row = sheet.GetRow(rowCounter);
if (row == null)
{
row = sheet.CreateRow(rowCounter);
}
return row;
}
///
/// Get a specific cell from a row. If the cell doesn't exist,
///
/// The row that the cell is part of
/// The column index that the cell is in.
/// The cell indicated by the column.
public static HH.WMS.Utils.NPOI.SS.UserModel.ICell GetCell(HH.WMS.Utils.NPOI.SS.UserModel.IRow row, int column)
{
HH.WMS.Utils.NPOI.SS.UserModel.ICell cell = row.GetCell(column);
if (cell == null)
{
cell = row.CreateCell(column);
}
return cell;
}
///
/// Creates a cell, gives it a value, and applies a style if provided
///
/// the row to Create the cell in
/// the column index to Create the cell in
/// The value of the cell
/// If the style is not null, then Set
/// A new HSSFCell
public static HH.WMS.Utils.NPOI.SS.UserModel.ICell CreateCell(HH.WMS.Utils.NPOI.SS.UserModel.IRow row, int column, String value, HSSFCellStyle style)
{
HH.WMS.Utils.NPOI.SS.UserModel.ICell cell = GetCell(row, column);
cell.SetCellValue(new HSSFRichTextString(value));
if (style != null)
{
cell.CellStyle = (style);
}
return cell;
}
///
/// Create a cell, and give it a value.
///
/// the row to Create the cell in
/// the column index to Create the cell in
/// The value of the cell
/// A new HSSFCell.
public static HH.WMS.Utils.NPOI.SS.UserModel.ICell CreateCell(HH.WMS.Utils.NPOI.SS.UserModel.IRow row, int column, String value)
{
return CreateCell(row, column, value, null);
}
///
/// Take a cell, and align it.
///
/// the cell to Set the alignment for
/// The workbook that is being worked with.
/// the column alignment to use.
public static void SetAlignment(ICell cell, HSSFWorkbook workbook, short align)
{
SetCellStyleProperty(cell, workbook, ALIGNMENT, align);
}
///
/// Take a cell, and apply a font to it
///
/// the cell to Set the alignment for
/// The workbook that is being worked with.
/// The HSSFFont that you want to Set...
public static void SetFont(ICell cell, HSSFWorkbook workbook, HSSFFont font)
{
SetCellStyleProperty(cell, workbook, FONT, font);
}
/**
* This method attempt to find an already existing HSSFCellStyle that matches
* what you want the style to be. If it does not find the style, then it
* Creates a new one. If it does Create a new one, then it applies the
* propertyName and propertyValue to the style. This is necessary because
* Excel has an upper limit on the number of Styles that it supports.
*
*@param workbook The workbook that is being worked with.
*@param propertyName The name of the property that is to be
* changed.
*@param propertyValue The value of the property that is to be
* changed.
*@param cell The cell that needs it's style changes
*@exception NestableException Thrown if an error happens.
*/
public static void SetCellStyleProperty(HH.WMS.Utils.NPOI.SS.UserModel.ICell cell, HSSFWorkbook workbook, String propertyName, Object propertyValue)
{
HH.WMS.Utils.NPOI.SS.UserModel.ICellStyle originalStyle = cell.CellStyle;
HH.WMS.Utils.NPOI.SS.UserModel.ICellStyle newStyle = null;
Hashtable values = GetFormatProperties(originalStyle);
values[propertyName] = propertyValue;
// index seems like what index the cellstyle is in the list of styles for a workbook.
// not good to compare on!
short numberCellStyles = workbook.NumCellStyles;
for (short i = 0; i < numberCellStyles; i++)
{
HH.WMS.Utils.NPOI.SS.UserModel.ICellStyle wbStyle = workbook.GetCellStyleAt(i);
Hashtable wbStyleMap = GetFormatProperties(wbStyle);
if (wbStyleMap.Equals(values))
{
newStyle = wbStyle;
break;
}
}
if (newStyle == null)
{
newStyle = workbook.CreateCellStyle();
SetFormatProperties(newStyle, workbook, values);
}
cell.CellStyle = (newStyle);
}
///
/// Returns a map containing the format properties of the given cell style.
///
/// cell style
/// map of format properties (String -> Object)
private static Hashtable GetFormatProperties(HH.WMS.Utils.NPOI.SS.UserModel.ICellStyle style)
{
Hashtable properties = new Hashtable();
PutShort(properties, ALIGNMENT, (short)style.Alignment);
PutShort(properties, BORDER_BOTTOM, (short)style.BorderBottom);
PutShort(properties, BORDER_LEFT, (short)style.BorderLeft);
PutShort(properties, BORDER_RIGHT, (short)style.BorderRight);
PutShort(properties, BORDER_TOP, (short)style.BorderTop);
PutShort(properties, BOTTOM_BORDER_COLOR, style.BottomBorderColor);
PutShort(properties, DATA_FORMAT, style.DataFormat);
PutShort(properties, FILL_BACKGROUND_COLOR, style.FillBackgroundColor);
PutShort(properties, FILL_FOREGROUND_COLOR, style.FillForegroundColor);
PutShort(properties, FILL_PATTERN, (short)style.FillPattern);
PutShort(properties, FONT, style.FontIndex);
PutBoolean(properties, HIDDEN, style.IsHidden);
PutShort(properties, INDENTION, style.Indention);
PutShort(properties, LEFT_BORDER_COLOR, style.LeftBorderColor);
PutBoolean(properties, LOCKED, style.IsLocked);
PutShort(properties, RIGHT_BORDER_COLOR, style.RightBorderColor);
PutShort(properties, ROTATION, style.Rotation);
PutShort(properties, TOP_BORDER_COLOR, style.TopBorderColor);
PutShort(properties, VERTICAL_ALIGNMENT, (short)style.VerticalAlignment);
PutBoolean(properties, WRAP_TEXT, style.WrapText);
return properties;
}
///
/// Sets the format properties of the given style based on the given map.
///
/// The cell style
/// The parent workbook.
/// The map of format properties (String -> Object).
private static void SetFormatProperties(
HH.WMS.Utils.NPOI.SS.UserModel.ICellStyle style, HSSFWorkbook workbook, Hashtable properties)
{
style.Alignment = (HH.WMS.Utils.NPOI.SS.UserModel.HorizontalAlignment)GetShort(properties, ALIGNMENT);
style.BorderBottom = (HH.WMS.Utils.NPOI.SS.UserModel.BorderStyle)GetShort(properties, BORDER_BOTTOM);
style.BorderLeft = (HH.WMS.Utils.NPOI.SS.UserModel.BorderStyle)GetShort(properties, BORDER_LEFT);
style.BorderRight = (HH.WMS.Utils.NPOI.SS.UserModel.BorderStyle)GetShort(properties, BORDER_RIGHT);
style.BorderTop = (HH.WMS.Utils.NPOI.SS.UserModel.BorderStyle)GetShort(properties, BORDER_TOP);
style.BottomBorderColor = (GetShort(properties, BOTTOM_BORDER_COLOR));
style.DataFormat = (GetShort(properties, DATA_FORMAT));
style.FillBackgroundColor = (GetShort(properties, FILL_BACKGROUND_COLOR));
style.FillForegroundColor = (GetShort(properties, FILL_FOREGROUND_COLOR));
style.FillPattern = (HH.WMS.Utils.NPOI.SS.UserModel.FillPatternType)GetShort(properties, FILL_PATTERN);
style.SetFont(workbook.GetFontAt(GetShort(properties, FONT)));
style.IsHidden = (GetBoolean(properties, HIDDEN));
style.Indention = (GetShort(properties, INDENTION));
style.LeftBorderColor = (GetShort(properties, LEFT_BORDER_COLOR));
style.IsLocked = (GetBoolean(properties, LOCKED));
style.RightBorderColor = (GetShort(properties, RIGHT_BORDER_COLOR));
style.Rotation = (GetShort(properties, ROTATION));
style.TopBorderColor = (GetShort(properties, TOP_BORDER_COLOR));
style.VerticalAlignment = (HH.WMS.Utils.NPOI.SS.UserModel.VerticalAlignment)GetShort(properties, VERTICAL_ALIGNMENT);
style.WrapText = (GetBoolean(properties, WRAP_TEXT));
}
///
/// Utility method that returns the named short value form the given map.
/// Returns zero if the property does not exist, or is not a {@link Short}.
///
/// The map of named properties (String -> Object)
/// The property name.
/// property value, or zero
private static short GetShort(Hashtable properties, String name)
{
Object value = properties[name];
if (value is short)
{
return (short)value;
}
else
{
return 0;
}
}
///
/// Utility method that returns the named boolean value form the given map.
/// Returns false if the property does not exist, or is not a {@link Boolean}.
///
/// map of properties (String -> Object)
/// The property name.
/// property value, or false
private static bool GetBoolean(Hashtable properties, String name)
{
Object value = properties[name];
if (value is Boolean)
{
return ((Boolean)value);
}
else
{
return false;
}
}
///
/// Utility method that Puts the named short value to the given map.
///
/// The map of properties (String -> Object).
/// The property name.
/// The property value.
private static void PutShort(Hashtable properties, String name, short value)
{
properties[name] = value;
}
///
/// Utility method that Puts the named boolean value to the given map.
///
/// map of properties (String -> Object)
/// property name
/// property value
private static void PutBoolean(Hashtable properties, String name, bool value)
{
properties[name] = value;
}
///
/// Looks for text in the cell that should be unicode, like alpha; and provides the
/// unicode version of it.
///
/// The cell to check for unicode values
/// transalted to unicode
public static ICell TranslateUnicodeValues(ICell cell)
{
String s = cell.RichStringCellValue.String;
bool foundUnicode = false;
String lowerCaseStr = s.ToLower();
for (int i = 0; i < unicodeMappings.Length; i++)
{
UnicodeMapping entry = unicodeMappings[i];
String key = entry.entityName;
if (lowerCaseStr.IndexOf(key, StringComparison.Ordinal) != -1)
{
s = s.Replace(key, entry.resolvedValue);
foundUnicode = true;
}
}
if (foundUnicode)
{
cell.SetCellValue(new HSSFRichTextString(s));
}
return cell;
}
private static UnicodeMapping um(String entityName, String resolvedValue)
{
return new UnicodeMapping(entityName, resolvedValue);
}
}
}