public static void SetCellStyle(CellStyle cellStyle, CellBorderType borderType)
{
cellStyle.BorderTop = borderType;
cellStyle.BorderBottom = borderType;
cellStyle.BorderLeft = borderType;
cellStyle.BorderRight = borderType;
}
public Cell AddCell(object value, int columnIndex, int rowIndex, TextStyle textStyle,
short backgroundColor = short.MinValue, CellBorderType borderStyle = CellBorderType.NONE, short borderColor = short.MinValue)
{
Row row = GetRow(rowIndex);
return(AddCell(value, columnIndex, row, textStyle, backgroundColor, borderStyle, borderColor));
}
public static void SetCellStyle(CellStyle cellStyle, CellBorderType borderType, HorizontalAlignment hAlign)
{
cellStyle.BorderTop = borderType;
cellStyle.BorderBottom = borderType;
cellStyle.BorderLeft = borderType;
cellStyle.BorderRight = borderType;
cellStyle.Alignment = hAlign;
}
private static void ApplyBorder(Style style, Color color, CellBorderType cellBorderType)
{
style.Borders[BorderType.TopBorder].Color = color;
style.Borders[BorderType.TopBorder].LineStyle = cellBorderType;
style.Borders[BorderType.BottomBorder].Color = color;
style.Borders[BorderType.BottomBorder].LineStyle = cellBorderType;
style.Borders[BorderType.LeftBorder].Color = color;
style.Borders[BorderType.LeftBorder].LineStyle = cellBorderType;
style.Borders[BorderType.RightBorder].Color = color;
style.Borders[BorderType.RightBorder].LineStyle = cellBorderType;
}
private void WriteCell(ExcelWorkbook ew, Worksheet ws, int level, int totalLevels, int row, int column, object val, ExcelDataTableFormat format, bool useHeaderFormats, Dictionary <string, Style> styleCollection)
{
Cell cell = ws.Cells[row, column];
cell.Value = val;
bool cellHasLineBreaks = false;
if (val != null && val is string)
{
if (string.IsNullOrWhiteSpace((string)val))
{
cell.Value = null;
}
else
{
string s = (string)val;
s = StringUtil.StripHTML(s);
s = s.Replace(" ", " ");
s = s.Replace("&NBSP;", " ");
s = s.Replace("<br />", "\n");
s = s.Replace("<br>", "\n");
s = s.Replace("", "-");
cell.Value = s;
//if (s.IndexOf("<") != -1 && s.IndexOf(">") != -1)
//{
//cell.HtmlString = s; // the aspose throws errors on html it doesn't recognize; commenting out for now
//}
if (s.IndexOf("\n") != -1)
{
cellHasLineBreaks = true;
}
}
}
Style style = null;
if (useHeaderFormats)
{
if (styleCollection.Keys.Contains("L" + level + "_HEADER" + (cellHasLineBreaks ? "_WITHLINEBREAK" : "")))
{
style = styleCollection["L" + level + "_HEADER" + (cellHasLineBreaks ? "_WITHLINEBREAK" : "")];
}
else
{
style = new Style();
style.Pattern = BackgroundType.Solid;
style.ForegroundColor = format.HeaderBackgroundColor; // the aspost style.foreground color actually sets the background
style.Font.Name = format.HeaderFontName;
style.Font.Size = format.HeaderFontSize;
style.Font.IsBold = format.HeaderFontBold;
style.Font.IsItalic = format.HeaderFontItatlic;
style.Font.Color = format.HeaderFontColor;
style.Font.Underline = format.HeaderFontUnderline ? FontUnderlineType.Single : FontUnderlineType.None;
CellBorderType cbt = CellBorderType.None;
if (format.HeaderBorderWidth > 0 && format.HeaderBorderWidth < 1.0f)
{
cbt = format.HeaderBorderDotted ? CellBorderType.Dotted : format.HeaderBorderDashed ? CellBorderType.Dashed : CellBorderType.Hair;
}
else if (format.HeaderBorderWidth >= 1.0f && format.HeaderBorderWidth < 2.0f)
{
cbt = format.HeaderBorderDotted ? CellBorderType.Dotted : format.HeaderBorderDashed ? CellBorderType.Dashed : CellBorderType.Thin;
}
else if (format.HeaderBorderWidth >= 2.0f)
{
cbt = format.HeaderBorderDotted ? CellBorderType.Dotted : format.HeaderBorderDashed ? CellBorderType.Dashed : CellBorderType.Medium;
}
if (cbt != CellBorderType.None)
{
style.SetBorder(BorderType.TopBorder, cbt, format.HeaderBorderColor);
style.SetBorder(BorderType.RightBorder, cbt, format.HeaderBorderColor);
style.SetBorder(BorderType.BottomBorder, cbt, format.HeaderBorderColor);
style.SetBorder(BorderType.LeftBorder, cbt, format.HeaderBorderColor);
}
styleCollection["L" + level + "_HEADER" + (cellHasLineBreaks ? "_WITHLINEBREAK" : "")] = style;
}
}
else
{
if (styleCollection.Keys.Contains("L" + level + "_DATA" + (cellHasLineBreaks ? "_WITHLINEBREAK" : "")))
{
style = styleCollection["L" + level + "_DATA" + (cellHasLineBreaks ? "_WITHLINEBREAK" : "")];
}
else
{
style = new Style();
style.Pattern = BackgroundType.Solid;
style.ForegroundColor = format.BackgroundColor; // the aspost style.foreground color actually sets the background
style.Font.Name = format.FontName;
style.Font.Size = format.FontSize;
style.Font.IsBold = format.FontBold;
style.Font.IsItalic = format.FontItatlic;
style.Font.Color = format.FontColor;
style.Font.Underline = format.FontUnderline ? FontUnderlineType.Single : FontUnderlineType.None;
if (format.AllowWrap)
{
style.IsTextWrapped = cellHasLineBreaks;
}
CellBorderType cbt = CellBorderType.None;
if (format.BorderWidth > 0 && format.HeaderBorderWidth < 1.0f)
{
cbt = format.BorderDotted ? CellBorderType.Dotted : format.BorderDashed ? CellBorderType.Dashed : CellBorderType.Hair;
}
else if (format.BorderWidth >= 1.0f && format.HeaderBorderWidth < 2.0f)
{
cbt = format.BorderDotted ? CellBorderType.Dotted : format.BorderDashed ? CellBorderType.Dashed : CellBorderType.Thin;
}
else if (format.BorderWidth >= 2.0f)
{
cbt = format.BorderDotted ? CellBorderType.Dotted : format.BorderDashed ? CellBorderType.Dashed : CellBorderType.Medium;
}
if (cbt != CellBorderType.None)
{
style.SetBorder(BorderType.TopBorder, cbt, format.BorderColor);
style.SetBorder(BorderType.RightBorder, cbt, format.BorderColor);
style.SetBorder(BorderType.BottomBorder, cbt, format.BorderColor);
style.SetBorder(BorderType.LeftBorder, cbt, format.BorderColor);
}
styleCollection["L" + level + "_DATA" + (cellHasLineBreaks ? "_WITHLINEBREAK" : "")] = style;
}
}
cell.SetStyle(style);
}
public static void SetCellStyle(CellStyle cellStyle, CellBorderType borderType, HorizontalAlignment hAlign, VerticalAlignment vAlign)
{
cellStyle.BorderTop = borderType;
cellStyle.BorderBottom = borderType;
cellStyle.BorderLeft = borderType;
cellStyle.BorderRight = borderType;
cellStyle.Alignment = hAlign;
cellStyle.VerticalAlignment = vAlign;
}
public static void SetCellStyle(CellStyle cellStyle, CellBorderType borderType)
{
cellStyle.BorderTop = borderType;
cellStyle.BorderBottom = borderType;
cellStyle.BorderLeft = borderType;
cellStyle.BorderRight = borderType;
}
public void setBorders(string name, CellBorderType type)
{
HSSFExcelStyles[name].BorderBottom = type;
HSSFExcelStyles[name].BorderLeft = type;
HSSFExcelStyles[name].BorderRight = type;
HSSFExcelStyles[name].BorderTop = type;
}
public void setBorders(string name, bool top, bool bottom, bool left, bool right, CellBorderType type)
{
if (bottom)
HSSFExcelStyles[name].BorderBottom = type;
if (left)
HSSFExcelStyles[name].BorderLeft = type;
if (right)
HSSFExcelStyles[name].BorderRight = type;
if (top)
HSSFExcelStyles[name].BorderTop = type;
}
public static CellBorder Get(CellBorderType name)
{
return(_BORDERS[name]);
}
public void AddList <T>(IEnumerable <T> dataList, IEnumerable <DataColumn> columns, CellBorderType borderStyle,
short borderColor)
{
//PropertyInfo[] propertys = typeof (T).GetProperties();
// шапка таблицы
Row tableHederRow = NextRow;
int index = 0;
foreach (DataColumn column in columns)
{
if (column.ExcelColumWidth >= 0)
{
Sheet.SetColumnWidth(index, (int)(256 * column.ExcelColumWidth));
}
AddCell(column.FieldAlias, index++, tableHederRow, TextStyle.BoldTextAlignCenter,
HSSFColor.GREY_25_PERCENT.index, borderStyle, borderColor);
}
// заполнение данными
int listRowIndex = 1;
foreach (T cls in dataList)
{
int indexData = 0;
Row dataRow = NextRow;
foreach (DataColumn column in columns)
{
if (column.IsRowNumberColumn)
{
AddCell(listRowIndex++, indexData++, dataRow, TextStyle.NormalText, HSSFColor.WHITE.index,
borderStyle, borderColor);
}
else
{
PropertyInfo property = typeof(T).GetProperty(column.FieldName);
object value = property.GetValue(cls, null);
AddCell(value, indexData++, dataRow, TextStyle.NormalText, HSSFColor.WHITE.index, borderStyle,
borderColor);
}
}
}
}
public Cell AddCell(Object value, int columnIndex, Row row, TextStyle textStyle, short backgroundColor,
CellBorderType borderStyle, short borderColor)
{
Cell cell = row.GetCell(columnIndex) ?? row.CreateCell(columnIndex);
CellStyle cellStyle = Styles[textStyle];
if (value == null)
{
cell.SetCellValue("");
}
else if (value.GetType() == typeof(DateTime))
{
cell.SetCellValue((DateTime)value);
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy");
}
else if (value.GetType() == typeof(double) || value.GetType() == typeof(float))
{
cell.SetCellValue((double)value);
DataFormat format = Workbook.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("# ### ### ##0.00");
}
else if (value.GetType() == typeof(decimal))
{
var d = (decimal)value;
cell.SetCellValue((double)d);
DataFormat format = Workbook.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("# ### ### ##0.00");
}
else if (value.GetType() == typeof(Int16) ||
value.GetType() == typeof(Int32) ||
value.GetType() == typeof(Int64) ||
value.GetType() == typeof(UInt16) ||
value.GetType() == typeof(UInt32) ||
value.GetType() == typeof(UInt64) ||
value.GetType() == typeof(Byte) ||
value.GetType() == typeof(SByte))
{
cell.SetCellValue((int)value);
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
}
else if (value.GetType() == typeof(bool))
{
cell.SetCellValue((bool)value);
}
else
{
cell.SetCellValue(value.ToString());
}
if (backgroundColor != short.MinValue)
{
cellStyle.FillForegroundColor = backgroundColor;
cellStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
}
if (borderColor != short.MinValue)
{
cellStyle.BorderBottom = borderStyle;
cellStyle.BorderLeft = borderStyle;
cellStyle.BorderRight = borderStyle;
cellStyle.BorderTop = borderStyle;
cellStyle.BottomBorderColor = borderColor;
cellStyle.LeftBorderColor = borderColor;
cellStyle.RightBorderColor = borderColor;
cellStyle.TopBorderColor = borderColor;
}
cell.CellStyle = cellStyle;
return(cell);
}
/// <summary>
/// Convert Excel BorderType to Word LineStyle
/// </summary>
/// <param name="lineType">Excel LineType</param>
/// <returns>Word LineStyle</returns>
private LineStyle ConvertLineStyle(CellBorderType lineType)
{
LineStyle wordsLineStyle = LineStyle.None;
switch (lineType)
{
case CellBorderType.DashDot:
{
wordsLineStyle = LineStyle.DotDash;
break;
}
case CellBorderType.DashDotDot:
{
wordsLineStyle = LineStyle.DotDotDash;
break;
}
case CellBorderType.Dashed:
{
wordsLineStyle = LineStyle.DashSmallGap;
break;
}
case CellBorderType.Dotted:
{
wordsLineStyle = LineStyle.Dot;
break;
}
case CellBorderType.Double:
{
wordsLineStyle = LineStyle.Double;
break;
}
case CellBorderType.Hair:
{
wordsLineStyle = LineStyle.Hairline;
break;
}
case CellBorderType.Medium:
{
wordsLineStyle = LineStyle.Single;
break;
}
case CellBorderType.MediumDashDot:
{
wordsLineStyle = LineStyle.DotDash;
break;
}
case CellBorderType.MediumDashDotDot:
{
wordsLineStyle = LineStyle.DotDotDash;
break;
}
case CellBorderType.MediumDashed:
{
wordsLineStyle = LineStyle.DashSmallGap;
break;
}
case CellBorderType.None:
{
wordsLineStyle = LineStyle.None;
break;
}
case CellBorderType.SlantedDashDot:
{
wordsLineStyle = LineStyle.DotDash;
break;
}
case CellBorderType.Thick:
{
wordsLineStyle = LineStyle.Thick;
break;
}
case CellBorderType.Thin:
{
wordsLineStyle = LineStyle.Single;
break;
}
default:
{
wordsLineStyle = LineStyle.None;
break;
}
}
return wordsLineStyle;
}
