/// <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 "";
}
}
}excel效果：
html效果：
发表评论