侧边栏壁纸
博主头像
分享你我博主等级

行动起来,活在当下

  • 累计撰写 108 篇文章
  • 累计创建 13 个标签
  • 累计收到 0 条评论

目 录CONTENT

文章目录

C# 使用NPOI手搓excel转html

管理员
2024-08-06 / 0 评论 / 0 点赞 / 6 阅读 / 33878 字
/// <summary>
/// excel转html
/// </summary>
public class ExcelToHtml
{
    /// <summary>
    /// excel转html
    /// </summary>
    /// <param name="inputFilePath"></param>
    /// <param name="outputHtmlPath"></param>
    public static void ConvertExcelToHtml(string inputFilePath, string outputHtmlPath)
    {
        // 创建一个HSSFWorkbook对象来处理.xls文件,如果是.xlsx文件,则应使用XSSFWorkbook
        using (var file = new FileStream(inputFilePath, FileMode.Open, FileAccess.Read))
        {
            IWorkbook workbook = null;
            try
            {
                //获取后缀名称
                string fileExt = AlphaFS.Path.GetExtension(inputFilePath)?.ToLower();
                switch (fileExt)
                {
                    //如果是XLSX格式选择XSSFWorkbook ,如果是XLS格式选择HSSFWorkbook 
                    case ".xlsx":
                        workbook = new XSSFWorkbook(file);
                        break;
                    case ".xls":
                        workbook = new HSSFWorkbook(file);
                        break;
                }
                StringBuilder htmlBuilder = new StringBuilder();
                //获取文件名称作为标题
                htmlBuilder.Append($"<html><head><title>{AlphaFS.Path.GetFileNameWithoutExtension(inputFilePath)}</title><meta http-equiv=\"content-type\" content=\"text/html;charset=utf-8\"></head><style>\r\n.excel-table {{\r\n        border-collapse: collapse;\r\n    }}\r\n\r\n    .excel-table tr:nth-child(odd) {{\r\n        border-bottom: 1px solid #ccc;\r\n    }}\r\n\r\n    .excel-table th, .excel-table td {{\r\n        border: 1px solid #ccc;\r\n        padding: 5px;\r\n    }}\r\n</style><body>");
                for (int sheetIndex = 0; sheetIndex < workbook.NumberOfSheets; sheetIndex++)
                {
                    ISheet sheet = workbook.GetSheetAt(sheetIndex);
                    if (sheet == null) continue;
                    List<CellRangeAddress> mergedRegions = sheet.MergedRegions;
                    htmlBuilder.AppendFormat("<div style=\"text-align: center;\"><h1>{0}</h1></div>", sheet.SheetName);
                    htmlBuilder.Append("<table class=\"excel-table\">");
                    HashSet<string> rows = new HashSet<string>();
                    HashSet<string> cells = new HashSet<string>();
                    // 遍历行
                    for (int rowNum = sheet.FirstRowNum; rowNum <= sheet.LastRowNum; rowNum++)
                    {
                        IRow row = sheet.GetRow(rowNum);
                        if (row == null) continue;
                        if (row.ZeroHeight) { continue; }
                        bool isBlankRow = true;
                        for (int colIdx = row.FirstCellNum; colIdx < row.LastCellNum; colIdx++)
                        {
                            ICell cell = row.GetCell(colIdx, MissingCellPolicy.RETURN_BLANK_AS_NULL);
                            if (cell != null && !string.IsNullOrEmpty(cell.ToString()))
                            {
                                isBlankRow = false;
                                break;
                            }
                        }
                        if (isBlankRow) continue;
                        htmlBuilder.Append("<tr>");
                        int mergedRows = 0; // 合并行数,默认为0
                        int mergedCols = 0; // 合并列数,默认为0
                        // 遍历单元格
                        for (int cellNum = row.FirstCellNum; cellNum <= row.LastCellNum; cellNum++)
                        {
                            ICell cell = row.GetCell(cellNum);
                            if (cell == null) continue;
                            bool isHidden = cell.CellStyle.IsHidden;
                            if (isHidden) { mergedCols++; continue; };
                            // 判断单元格是否属于合并区域
                            if (mergedCols == row.LastCellNum) break; // 合并列数等于行的最后一列时,跳出循环
                            if (cells.Contains(rowNum + "-" + cellNum)) { cells.Remove(rowNum + "-" + cellNum); continue; }// 合并列数大于当前列时,跳过当前列
                            if (rows.Contains(rowNum + "-" + cellNum))
                            {
                                mergedCols++;
                                //删除rows中的数据,以免影响下次循环时间
                                rows.Remove(rowNum + "-" + cellNum);
                                continue;
                            }  //如果已经合并过则跳过(防止重复合并)
                            if (!cell.IsMergedCell)
                            {
                                htmlBuilder.AppendFormat("<td ");
                                ProcessCellStyle(htmlBuilder, cell, workbook, sheet);
                                string cellValue = GetCellValue(cell, workbook);
                                htmlBuilder.AppendFormat(">{0}</td>", WebUtility.HtmlEncode(cellValue));
                                continue;
                            }
                            foreach (var region in mergedRegions)
                            {
                                if (!region.IsInRange(rowNum, cellNum)) continue;
                                //获取合并单元格的行数和列数
                                int rowspan = region.LastRow - region.FirstRow + 1;
                                int colspan = region.LastColumn - region.FirstColumn + 1;
                                mergedCols = colspan;
                                mergedRows = rowspan;
                                //mergedCols = colspan == row.LastCellNum ? colspan : mergedCols += colspan;
                                if (mergedCols > 1&& mergedCols!= row.LastCellNum && mergedRows==1)
                                {
                                    for (int i = 0; i < mergedCols; i++)
                                    {
                                        cells.Add(rowNum + "-" + (i + cellNum));
                                    }
                                }       
                                if (mergedRows > 1)
                                {
                                    for (int i = 0; i < mergedCols; i++)
                                    {
                                        for (int j = mergedRows + rowNum - 1; j > rowNum; j--)
                                        {
                                            rows.Add(j + "-" + (i + cellNum));
                                        }
                                    }
                                }
                                htmlBuilder.AppendFormat("<td ");
                                ProcessCellStyle(htmlBuilder, cell, workbook, sheet);
                                string cellValue = GetCellValue(cell, workbook);
                                htmlBuilder.AppendFormat(" rowspan='{0}' colspan='{1}'>{2}</td>", rowspan, colspan, cellValue);
                                break;
                            }
                        }
                        htmlBuilder.Append("</tr>");
                    }
                    htmlBuilder.Append("</table>");
                }
                htmlBuilder.Append("</body></html>");
                //判断路径是否存在
                if (!AlphaFS.Directory.Exists(AlphaFS.Path.GetDirectoryName(outputHtmlPath)))
                {
                    AlphaFS.Directory.CreateDirectory(AlphaFS.Path.GetDirectoryName(outputHtmlPath));
                }
                //判断文件是否存在,存在则覆盖
                if (AlphaFS.File.Exists(outputHtmlPath))
                {
                    AlphaFS.File.Delete(outputHtmlPath);
                }
                // 将生成的HTML写入文件
                AlphaFS.File.WriteAllText(outputHtmlPath, htmlBuilder.ToString());
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                workbook?.Close();
            }
        }
    }
    private static void ProcessCellStyle(StringBuilder htmlBuilder, ICell cell, IWorkbook workbook, ISheet sheet)
    {
        if (cell == null) return;
        // 获取单元格的样式
        var cellStyle = cell.CellStyle;
        // 获取字体对象
        var font = cellStyle.GetFont(workbook);
        // 获取字体大小
        var fontSize = font.FontHeightInPoints;
        // 获取字体颜色
        var fontColor = font.Color;
        // 获取背景颜色
        var backgroundColor = cellStyle.FillForegroundColorColor;
        // 将样式转换为CSS格式
        string cssStyle = $"style=\"";
        cssStyle += $"font-size: {fontSize}px;";
        // 如果设置了字体颜色,则将其转为CSS格式
        if (fontColor != null)
        {
            string fontColorHex = "#" + fontColor.ToString("X6");
            cssStyle += $" color: {fontColorHex};";
        }
        // 如果设置了背景颜色,则将其转为CSS格式
        if (backgroundColor != null)
        {
            bool isXlsx = workbook is XSSFWorkbook;
            byte[] rgb = isXlsx ? ((NPOI.XSSF.UserModel.XSSFColor)cell.CellStyle.FillForegroundColorColor).RGB : ((NPOI.HSSF.Util.HSSFColor)cell.CellStyle.FillForegroundColorColor).RGB;
            string backgroundColorHex = "#" + BitConverter.ToString(rgb).Replace("-", string.Empty);
            cssStyle += $" background-color: {backgroundColorHex};";
        }
        // 边框
        //cssStyle += "border-style: " + cellStyle.BorderTop.ToString().ToLower() + ";";
        //cssStyle += "border-color: " + "#" + cellStyle.TopBorderColor.ToString("X6") + ";";
        // 获取单元格的宽度和高度
        IRow row = sheet.GetRow(cell.RowIndex);
        float cellWidth = sheet.GetColumnWidthInPixels(cell.ColumnIndex);
        float cellHeight = row.HeightInPoints / 0.75f;
        // 将宽度和高度转换为CSS单位(像素)
        string widthCss = "width: " + cellWidth.ToString() + "px;";
        string heightCss = "height: " + cellHeight.ToString() + "px;";
        cssStyle += widthCss + heightCss;
        cssStyle += "\"";
        htmlBuilder.Append(cssStyle);
        // 输出单元格的值和样式
        Console.WriteLine($" CSSStyle: {cssStyle}");
    }
    private static string GetCellValue(ICell cell, IWorkbook workbook)
    {
        switch (cell.CellType)
        {
            case CellType.Formula:
                // 计算公式结果
                workbook.GetCreationHelper().CreateFormulaEvaluator().EvaluateInCell(cell);
                return GetCellValue(cell, workbook);
            case CellType.Numeric:
                if (DateUtil.IsCellDateFormatted(cell))
                {
                    return cell.DateCellValue.ToString("yyyy-MM-dd"); // 格式化日期
                }
                else
                {
                    return cell.NumericCellValue.ToString(); // 数字格式
                }
            case CellType.String:
                return cell.StringCellValue;
            case CellType.Boolean:
                return cell.BooleanCellValue ? "True" : "False";
            default:
                return "";
        }
    }
}

0

评论区