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)); }