Exemplo n.º 1
0
        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);
        }
Exemplo n.º 2
0
        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);
        }
Exemplo n.º 3
0
        /// <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);
        }
Exemplo n.º 4
0
        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; ");
            }
        }
Exemplo n.º 5
0
        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());
        }
Exemplo n.º 6
0
        /// <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));
        }