private void BuildStyle_Border(IWorkbook workbook, StringBuilder style,
String type, BorderStyle xlsBorder, short borderColor)
{
if (xlsBorder == BorderStyle.None)
{
return;
}
StringBuilder borderStyle = new StringBuilder();
borderStyle.Append(ExcelToHtmlUtils.GetBorderWidth(xlsBorder));
borderStyle.Append(' ');
borderStyle.Append(ExcelToHtmlUtils.GetBorderStyle(xlsBorder));
if (workbook is HSSFWorkbook)
{
var customPalette = ((HSSFWorkbook)workbook).GetCustomPalette();
HSSFColor color = null;
if (customPalette != null)
{
color = customPalette.GetColor(borderColor);
}
if (color != null)
{
borderStyle.Append(' ');
borderStyle.Append(ExcelToHtmlUtils.GetColor(color));
}
}
else
{
IndexedColors clr = IndexedColors.ValueOf(borderColor);
if (clr != null)
{
borderStyle.Append(' ');
borderStyle.Append(clr.HexString);
}
else
{
XSSFColor color = null;
var stylesSource = ((XSSFWorkbook)workbook).GetStylesSource();
if (stylesSource != null)
{
var theme = stylesSource.GetTheme();
if (theme != null)
{
color = theme.GetThemeColor(borderColor);
}
}
if (color != null)
{
borderStyle.Append(' ');
borderStyle.Append(ExcelToHtmlUtils.GetColor(color));
}
}
}
style.AppendFormat("border-{0}: {1}; ", type, borderStyle);
}
private static IFont GetFont(this IWorkbook workbook, short fontSize, string fontName, Color color = Color.Black, FontBoldWeight fontBoldWeight = FontBoldWeight.None)
{
var font = workbook.CreateFont();
font.FontHeightInPoints = fontSize;
font.FontName = fontName;
font.Boldweight = (short)fontBoldWeight;
font.Color = IndexedColors.ValueOf(color.ToString()).Index;
return(font);
}
/// <summary>
/// Add new row to given sheet
/// </summary>
/// <param name="sheet">ISheet</param>
/// <param name="rownum">Row Number</param>
/// <param name="style">Enum</param>
/// <param name="color">Text color</param>
/// <returns></returns>
public static IRow CreateRow(this ISheet sheet, int rownum, Style style = Style.Normal, Color color = Color.Black)
{
var row = sheet.GetRow(rownum) ?? sheet.CreateRow(rownum);
row.SetStyle(style);
if (color != Color.Black)
{
var font = row.RowStyle.GetFont(sheet.Workbook);
font.Color = IndexedColors.ValueOf(color.ToString()).Index;
row.RowStyle.SetFont(font);
}
return(row);
}
void BuildStyle_Font(IWorkbook workbook, StringBuilder style,
IFont font)
{
switch (font.Boldweight)
{
case (short)FontBoldWeight.Bold:
style.Append("font-weight: bold; ");
break;
case (short)FontBoldWeight.Normal:
// by default, not not increase HTML size
// style.Append( "font-weight: normal; " );
break;
}
if (workbook is HSSFWorkbook)
{
HSSFColor fontColor = ((HSSFWorkbook)workbook).GetCustomPalette()?.GetColor(font.Color);
if (fontColor != null)
{
style.AppendFormat("color:{0}; ", ExcelToHtmlUtils.GetColor(fontColor));
}
}
else
{
IndexedColors clr = IndexedColors.ValueOf(font.Color);
string hexstring = null;
if (clr != null)
{
hexstring = clr.HexString;
}
else
{
StylesTable st = ((XSSFWorkbook)workbook).GetStylesSource();
XSSFColor fontColor = null;
if (st != null && st.GetTheme() != null)
{
fontColor = st.GetTheme().GetThemeColor(font.Color);
}
else
{
fontColor = ((XSSFFont)font).GetXSSFColor();
}
if (fontColor != null)
{
hexstring = ExcelToHtmlUtils.GetColor(fontColor);
}
}
if (hexstring != null)
{
style.AppendFormat("color:{0}; ", hexstring);
}
}
if (font.FontHeightInPoints != 0)
{
style.Append("font-size: " + font.FontHeightInPoints + "pt; ");
}
if (font.IsItalic)
{
style.Append("font-style: italic; ");
}
}
protected String BuildStyle(IWorkbook workbook, ICellStyle cellStyle)
{
StringBuilder style = new StringBuilder();
if (workbook is HSSFWorkbook)
{
HSSFPalette palette = ((HSSFWorkbook)workbook).GetCustomPalette();
style.Append("white-space: pre-wrap; ");
ExcelToHtmlUtils.AppendAlign(style, cellStyle.Alignment);
if (cellStyle.FillPattern == FillPattern.NoFill)
{
// no fill
}
else if (cellStyle.FillPattern == FillPattern.SolidForeground)
{
//cellStyle.
//HSSFColor.
HSSFColor foregroundColor = palette.GetColor(cellStyle.FillForegroundColor);
if (foregroundColor != null)
{
style.AppendFormat("background-color:{0}; ", ExcelToHtmlUtils.GetColor(foregroundColor));
}
}
else
{
HSSFColor backgroundColor = palette.GetColor(cellStyle.FillBackgroundColor);
if (backgroundColor != null)
{
style.AppendFormat("background-color:{0}; ", ExcelToHtmlUtils.GetColor(backgroundColor));
}
}
}
else
{
style.Append("white-space: pre-wrap; ");
ExcelToHtmlUtils.AppendAlign(style, cellStyle.Alignment);
if (cellStyle.FillPattern == FillPattern.NoFill)
{
// no fill
}
else if (cellStyle.FillPattern == FillPattern.SolidForeground)
{
//cellStyle
IndexedColors clr = IndexedColors.ValueOf(cellStyle.FillForegroundColor);
string hexstring = null;
if (clr != null)
{
hexstring = clr.HexString;
}
else
{
XSSFColor foregroundColor = (XSSFColor)cellStyle.FillForegroundColorColor;
if (foregroundColor != null)
{
hexstring = ExcelToHtmlUtils.GetColor(foregroundColor);
}
}
if (hexstring != null)
{
style.AppendFormat("background-color:{0}; ", hexstring);
}
}
else
{
IndexedColors clr = IndexedColors.ValueOf(cellStyle.FillBackgroundColor);
string hexstring = null;
if (clr != null)
{
hexstring = clr.HexString;
}
else
{
XSSFColor backgroundColor = (XSSFColor)cellStyle.FillBackgroundColorColor;
if (backgroundColor != null)
{
hexstring = ExcelToHtmlUtils.GetColor(backgroundColor);
}
}
if (hexstring != null)
{
style.AppendFormat("background-color:{0}; ", hexstring);
}
}
}
BuildStyle_Border(workbook, style, "top", cellStyle.BorderTop, cellStyle.TopBorderColor);
BuildStyle_Border(workbook, style, "right", cellStyle.BorderRight, cellStyle.RightBorderColor);
BuildStyle_Border(workbook, style, "bottom", cellStyle.BorderBottom, cellStyle.BottomBorderColor);
BuildStyle_Border(workbook, style, "left", cellStyle.BorderLeft, cellStyle.LeftBorderColor);
IFont font = cellStyle.GetFont(workbook);
BuildStyle_Font(workbook, style, font);
return(style.ToString());
}
/// <summary>
/// Converts the worksheet from the given file to a string.
/// Applies the given template file for the conversion.
/// </summary>
/// <param name="filename">The path to the worksheet file</param>
/// <param name="templateFile">The path to the template file that is used for the conversion</param>
/// <returns>The converted worksheet as string</returns>
public string ConvertWorksheet(string filename, string templateFile)
{
var workbook = new Workbook {
Name = "TestWorkbook"
};
var fileinfo = new FileInfo(filename);
if (!fileinfo.Exists)
{
throw new FileNotFoundException();
}
workbook.Name = fileinfo.Name;
IWorkbook poiWorkbook;
using (FileStream file = new FileStream(filename, FileMode.Open, FileAccess.Read))
{
if (filename.EndsWith(".xlsx"))
{
poiWorkbook = new XSSFWorkbook(file);
}
else if (filename.EndsWith(".xls"))
{
poiWorkbook = new HSSFWorkbook(file);
}
else
{
throw new Exception("Not Supported File Format");
}
}
var worksheets = new List <Worksheet>();
for (int j = 0; j < poiWorkbook.NumberOfSheets; j++)
{
ISheet sheet = poiWorkbook.GetSheetAt(j);
int rowCount = sheet.LastRowNum;
IRow headerRow = sheet.GetRow(0);
int colCount = 0;
if (headerRow != null)
{
colCount = headerRow.LastCellNum;
for (int rowNum = 0; rowNum < rowCount; rowNum++)
{
IRow hssfRow = sheet.GetRow(rowNum);
if (hssfRow != null)
{
colCount = hssfRow.LastCellNum > colCount ? hssfRow.LastCellNum : colCount;
}
}
}
var worksheet = new Worksheet();
worksheet.Name = sheet.SheetName;
var rows = new List <Row>();
var sheetWidth = 0;
for (int cellNum = 0; cellNum < colCount; cellNum++)
{
sheetWidth += Convert.ToInt32(Math.Round(sheet.GetColumnWidthInPixels(cellNum)));
}
worksheet.WidthPixel = sheetWidth;
for (int rowNum = 0; rowNum <= rowCount; rowNum++)
{
var row = new Row();
var cells = new List <Cell>();
IRow hssfRow = sheet.GetRow(rowNum);
if (hssfRow != null)
{
for (int cellNum = 0; cellNum < colCount; cellNum++)
{
var cell = new Cell();
var hssfCell = hssfRow.GetCell(cellNum, MissingCellPolicy.CREATE_NULL_AS_BLANK);
if (hssfCell.CellStyle.FillPattern == FillPattern.SolidForeground)
{
byte[] cellBackground = hssfCell.CellStyle.FillForegroundColorColor.RGB;
var hexBackground = "#" + string.Concat(cellBackground.Select(b => b.ToString("X2")).ToArray());
cell.BackgroundColor = hexBackground;
}
if (hssfCell.CellStyle != null && hssfCell.CellStyle.GetFont(poiWorkbook) != null)
{
var foregroundColor = IndexedColors.ValueOf(hssfCell.CellStyle.GetFont(poiWorkbook).Color);
if (foregroundColor != null)
{
cell.TextColor = foregroundColor.HexString;
}
}
if (hssfCell.CellType == CellType.Formula)
{
cell.Value = hssfCell.NumericCellValue.ToString();
}
else
{
cell.Value = hssfCell.ToString();
}
var cellWidthPixel = Convert.ToInt32(Math.Round(sheet.GetColumnWidthInPixels(cellNum)));
cell.WidthPixel = cellWidthPixel;
cells.Add(cell);
}
}
row.Cells = cells;
rows.Add(row);
}
worksheet.Rows = rows;
worksheets.Add(worksheet);
}
workbook.Worksheets = worksheets;
poiWorkbook = null;
return(renderer.RenderWorkbook(workbook, templateFile));
}