private CellStyle GetCellStyleForFormat(Workbook workbook, string dataFormat)
{
if (!this.cellStyleCache.ContainsKey(dataFormat))
{
var style = workbook.CreateCellStyle();
// check if this is a built-in format
var builtinFormatId = HSSFDataFormat.GetBuiltinFormat(dataFormat);
if (builtinFormatId != -1)
{
style.DataFormat = builtinFormatId;
}
else
{
// not a built-in format, so create a new one
var newDataFormat = workbook.CreateDataFormat();
style.DataFormat = newDataFormat.GetFormat(dataFormat);
}
this.cellStyleCache[dataFormat] = style;
}
return(this.cellStyleCache[dataFormat]);
}
public static ICellStyle GetCellStyle(IndexedColors indexedColor)
{
ICellStyle style = Workbook.CreateCellStyle();
style.FillForegroundColor = indexedColor.Index;
style.FillPattern = FillPattern.SolidForeground;
return(style);
}
protected CellStyle CreateCellStyle(Cell cell)
{
//if (cell.CellStyle != null) return cell.CellStyle;
//var style = Workbook.CreateCellStyle();
return(cell.CellStyle = Workbook.CreateCellStyle());
}
protected CellStyle CreateCellStyle(CellRangeAddress region)
{
/*var firstCell = true;
* var sameStyle = true;
* CellStyle style = null;
*
* for (int iRow = region.FirstRow; iRow <= region.LastRow; iRow++)
* {
* var row = Sheet.GetRow(iRow) ?? Sheet.CreateRow(iRow);
* for (int iCol = region.FirstColumn; iCol <= region.LastColumn; iCol++)
* {
* var cell = row.GetCell(iCol) ?? row.CreateCell(iCol);
* if (cell != null && cell.CellStyle != null)
* {
* if (firstCell)
* style = cell.CellStyle;
* else
* if (style != cell.CellStyle)
* {
* sameStyle = false;
* break;
* }
* }
* else
* {
* sameStyle = false;
* break;
* }
* firstCell = false;
* }
* if (!sameStyle) break;
* }
* if (sameStyle && style != null)
* return style;*/
var style = Workbook.CreateCellStyle();
for (var iRow = region.FirstRow; iRow <= region.LastRow; iRow++)
{
var row = Sheet.GetRow(iRow) ?? Sheet.CreateRow(iRow);
for (var iCol = region.FirstColumn; iCol <= region.LastColumn; iCol++)
{
var cell = row.GetCell(iCol) ?? row.CreateCell(iCol);
if (cell != null)
{
cell.CellStyle = style;
}
}
}
return(style);
}
private XSSFCellStyle GetElementColumnHeaderCellStyleDarkText()
{
var style = (XSSFCellStyle)Workbook.CreateCellStyle();
style = AddBordersToStyle(style);
var font = GetHeaderFont();
((XSSFFont)font).SetColor(new XSSFColor(BlackBackgroundRgb));
style.SetFont(font);
style.WrapText = true;
return(style);
}
public void TestDefaultColumnStyle()
{
Workbook wb = GetTestDataProvider().CreateWorkbook();
CellStyle style = wb.CreateCellStyle();
Sheet sheet = wb.CreateSheet();
sheet.SetDefaultColumnStyle(0, style);
Assert.IsNotNull(sheet.GetColumnStyle(0));
Assert.AreEqual(style.Index, sheet.GetColumnStyle(0).Index);
Row row = sheet.CreateRow(0);
Cell cell = row.CreateCell(0);
CellStyle style2 = cell.CellStyle;
Assert.IsNotNull(style2);
Assert.AreEqual(style.Index, style2.Index, "style should match");
}
public ExportDoAssetsBase(IAssetsOptionService assetsOptionService, int doAssetsId) : base()
{
AssetsOptionService = assetsOptionService;
DoAssetsId = doAssetsId;
HeaderStyle = Workbook.CreateCellStyle();
HeaderStyle.Alignment = HorizontalAlignment.Center;
HeaderStyle.VerticalAlignment = VerticalAlignment.Center;
IFont font = Workbook.CreateFont();
font.Boldweight = short.MaxValue;
HeaderStyle.SetFont(font);
CommCellStyle = Workbook.CreateCellStyle();
CommCellStyle.VerticalAlignment = VerticalAlignment.Center;
CommCellStyle.WrapText = true;
FooterCellStyle = Workbook.CreateCellStyle();
FooterCellStyle.Alignment = HorizontalAlignment.Center;
FooterCellStyle.VerticalAlignment = VerticalAlignment.Center;
FooterCellStyle.WrapText = true;
}
protected CellStyle GetDateTimeStyle()
{
if (Parent != null)
{
var style = Parent.GetDateTimeStyle();
if (style != null)
{
return(style);
}
}
if (_dateTimeStyle != null)
{
return(_dateTimeStyle);
}
_dateTimeStyle = Workbook.CreateCellStyle();
var format = Workbook.CreateDataFormat();
_dateTimeStyle.DataFormat = format.GetFormat("DD.MM.YYYY");
GetRootParent()._dateTimeStyle = _dateTimeStyle;
return(_dateTimeStyle);
}
private ICellStyle GetColumnStyle(int index, string format)
{
if (ColumnStyles.ContainsKey(index))
{
return(ColumnStyles[index]);
}
var cellStyle = Workbook.CreateCellStyle();
if (string.IsNullOrEmpty(format))
{
ColumnStyles.Add(index, cellStyle);
return(cellStyle);
}
var cellFormat = Workbook.CreateDataFormat();
cellStyle.DataFormat = cellFormat.GetFormat(format);
ColumnStyles.Add(index, cellStyle);
return(cellStyle);
}
private void Put <T>(ISheet sheet, IEnumerable <T> objects, bool overwrite, int rowOffset = 0, bool shiftDownExistingRows = false)
{
var sheetName = sheet.SheetName;
var firstRow = sheet.GetRow(sheet.FirstRowNum);
var objectArray = objects as T[] ?? objects.ToArray();
var type = MapHelper.GetConcreteType(objectArray);
var columns = GetTrackedColumns(sheetName, type) ??
GetColumns(firstRow ?? PopulateFirstRow(sheet, null, type), type);
firstRow = sheet.GetRow(sheet.FirstRowNum) ?? PopulateFirstRow(sheet, columns, type);
var rowIndex = overwrite
? HasHeader ? sheet.FirstRowNum + 1 : sheet.FirstRowNum
: sheet.GetRow(sheet.LastRowNum) != null ? sheet.LastRowNum + 1 : sheet.LastRowNum;
rowIndex += rowOffset;
ICellStyle baseRowCellStyle = null;
if (shiftDownExistingRows)
{
var cell = sheet.GetRow(rowIndex)?.GetCell(0);
if (cell != null)
{
baseRowCellStyle = Workbook.CreateCellStyle();
baseRowCellStyle.CloneStyleFrom(cell.CellStyle);
}
sheet.ShiftRows(rowIndex, sheet.LastRowNum, objectArray.Length, true, false);
}
MapHelper.EnsureDefaultFormats(columns, TypeFormats);
foreach (var o in objectArray)
{
var row = sheet.GetRow(rowIndex);
if (overwrite && !shiftDownExistingRows && row != null)
{
sheet.RemoveRow(row);
row = sheet.CreateRow(rowIndex);
}
row = row ?? sheet.CreateRow(rowIndex);
foreach (var column in columns)
{
var pi = column.Attribute.Property;
var value = pi?.GetValue(o);
var cell = row.GetCell(column.Attribute.Index, MissingCellPolicy.CREATE_NULL_AS_BLANK);
column.CurrentValue = value;
if (column.Attribute.TryPut == null || column.Attribute.TryPut(column, o))
{
SetCell(cell, column.CurrentValue, column, setStyle: overwrite);
if (baseRowCellStyle != null)
{
var cellStyle = Workbook.CreateCellStyle();
cellStyle.CloneStyleFrom(baseRowCellStyle);
cellStyle.DataFormat = cell.CellStyle.DataFormat;
cell.CellStyle = cellStyle;
}
}
}
rowIndex++;
}
// Remove not used rows if any.
while (overwrite && !shiftDownExistingRows && rowIndex <= sheet.LastRowNum)
{
var row = sheet.GetRow(rowIndex);
if (row != null)
{
sheet.RemoveRow(row);
}
rowIndex++;
}
// Injects custom action for headers.
if (overwrite && HasHeader && _headerAction != null)
{
firstRow?.Cells.ForEach(c => _headerAction(c));
}
}
