EXCEL(带图)转html

文章目录

叙述
1、效果
2、excel 转换主逻辑
3、其他补充
- - - 3.0 主前端bootstrap
    - 3.1 my.css：
    - 3.2 my.js
    - 3.3 入口home.html
    - 3.4 Data.ashx

叙述

要实现H5 展示excel
查询了一下没有好的办法,自己写了一个,简单记录一下

1、效果

在这里插入图片描述
用bootstrap 根据sheet做了一个菜单。

2、excel 转换主逻辑

using System;
using System.IO;
using System.Text;
using NPOI.OpenXmlFormats.Spreadsheet;
using NPOI.XSSF.Model;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.Functions;
using static NPOI.HSSF.Util.HSSFColor;
using NPOI.XSSF.Streaming;
using System.Collections.Generic;
using System.Linq;public class ForExcel
{public static string Main(string excelFilePath){string htmlFilePath = AppDomain.CurrentDomain.BaseDirectory + "show1.html";//excelFilePath= HttpUtility.UrlDecode(excelFilePath);try{string htmlContent = ConvertWorkbookToHtml(excelFilePath);File.WriteAllText(htmlFilePath, htmlContent, Encoding.UTF8);return "show1.html";}catch (Exception ex){return "false";}}public static string ConvertWorkbookToHtml(string filePath){IWorkbook workbook;using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read)){if (Path.GetExtension(filePath) == ".xls")workbook = new HSSFWorkbook(fs); // 处理 Excel 2003elseworkbook = new XSSFWorkbook(fs); // 处理 Excel 2007+}StringBuilder htmlBuilder = new StringBuilder();htmlBuilder.Append("<html>");htmlBuilder.Append("<head>\r\n    <meta charset=\"UTF-8\">\r\n    <meta name=\"viewport\" content=\"width=device-width, initial-scale=1.0\"><script src=\"assist/bootstrap/jquery-3.7.0.min.js\"></script>\r\n    <script src=\"assist/bootstrap/js/bootstrap.js\"></script>\r\n    <link href=\"assist/bootstrap/css/bootstrap.css\" rel=\"stylesheet\" />  <link href=\"assist/my.css\" rel=\"stylesheet\" />");//脚本htmlBuilder.Append("<script type=\"text/javascript\">"+ "        function onMenu(o){"+ "            $(\".nav-link\").removeClass(\"active\");"+ "            $(\"#nav\" + o).addClass(\"active\");"+ "            $(\".dtable\").hide();"+ "            $(\"#dtable\" + o).show();"+ "" + "        }"+ "   window.onload = function () {"+ "            var menuheight = $(\".menu\").height()+10;"+ "            $(\".content\").css(\"padding-top\", menuheight+40);"+ "        }</script >");htmlBuilder.Append("</head>");//顶部返回htmlBuilder.Append("<body>"+ "<nav class='navbar navbar-light bg-light'>"+ "<div class='container-fluid'>"+ "<a href='javascript:history.back()' class='back-arrow' aria-label='返回'>&lt;</a>"+ "</div>"+ "</nav>"+ "<div class='menu'>");//菜单htmlBuilder.Append("<ul class=\"nav nav-tabs\">");for (int i = 0; i < workbook.NumberOfSheets; i++){ISheet sheet = workbook.GetSheetAt(i);htmlBuilder.AppendFormat(" <li class=\"nav-item\">\r\n  <a class=\"nav-link " + (i == 0 ? "active" : "") + "\" "+ $" id=\"nav{i}\""+ $" href=\"javascript:onMenu({i})\">{sheet.SheetName}</a>"+ "</li>", sheet.SheetName);}htmlBuilder.Append(" </ul></div>");//内容htmlBuilder.Append("<div  class='content'>");for (int i = 0; i < workbook.NumberOfSheets; i++){htmlBuilder.Append($"<table class='dtable' id='dtable{i}' " + (i != 0 ? "style='display:none;'" : "") + ">");ISheet sheet = workbook.GetSheetAt(i);//标记插入图片后导致的位移List<tude> InImg = new List<tude>();//最大列、最大行int maxcol = 0; int maxrow = sheet.LastRowNum;//图片不占单元格，但是需要循环到位置if (sheet is XSSFSheet xssfSheet2){foreach (var drawing in xssfSheet2.GetDrawingPatriarch().GetShapes()){if (drawing is XSSFPicture picture){int m1 = picture.ClientAnchor.Col2;int m2 = picture.ClientAnchor.Row2;if (m1 > maxcol) maxcol = m1;if (m2 > maxrow) maxrow = m2;}}}for (int x = 0; x < maxrow; x++)//行{IRow row = sheet.GetRow(x);htmlBuilder.Append("<tr>");if (x == 0)//没图片就取第一行列数{ if (row.LastCellNum > maxcol) maxcol = row.LastCellNum; }for (int j = 0; j < maxcol; j++)//列{ICell cell; string cellValue;if (row != null){cell = row.GetCell(j);cellValue = cell?.ToString() ?? "";}else cellValue = "";// 检查单元格是否包含图片bool hasImage = false;if (sheet is XSSFSheet xssfSheet){foreach (var drawing in xssfSheet.GetDrawingPatriarch().GetShapes()){int nowrow = 0; int nowcol = 0;if (drawing is XSSFPicture picture){if (picture.ClientAnchor.Col1 == j && picture.ClientAnchor.Row1 == x){byte[] imageBytes = ((XSSFPicture)drawing).PictureData.Data;string base64Image = Convert.ToBase64String(imageBytes);string imageFormat = ((XSSFPicture)drawing).PictureData.MimeType.Split('/')[1];cellValue = $"<img src='data:image/{imageFormat};base64,{base64Image}'/>";hasImage = true;nowrow = picture.ClientAnchor.Row2 - picture.ClientAnchor.Row1;//图片占几行nowcol = picture.ClientAnchor.Col2 - picture.ClientAnchor.Col1;//图片占列行//标记坐标,解决多行多列导致的位移for(int a= picture.ClientAnchor.Row1; a<= picture.ClientAnchor.Row2;a++)//行{for (int b = picture.ClientAnchor.Col1; b <= picture.ClientAnchor.Col2; b++)//列{tude t = new tude();t.xx = b;//列 xt.yy = a;//行 yInImg.Add(t);}}if (hasImage)htmlBuilder.AppendFormat("<td rowspan='{1}'  colspan='{2}' >{0}</td>", cellValue, nowrow, nowcol);}}}}if (!hasImage){if (InImg.Where(p=>p.xx==j &p.yy==x).ToList().Count>0) { }//坐标校对 是否被图片占用else{htmlBuilder.AppendFormat("<td>{0}</td>", cellValue);}}}htmlBuilder.Append("</tr>");}//整个sheet没有内容,只有图if (maxcol == 0 && maxrow == 0){if (sheet is XSSFSheet xssfSheet){foreach (var drawing in xssfSheet.GetDrawingPatriarch().GetShapes()){htmlBuilder.Append("<tr>");if (drawing is XSSFPicture picture){byte[] imageBytes = ((XSSFPicture)drawing).PictureData.Data;string base64Image = Convert.ToBase64String(imageBytes);string imageFormat = ((XSSFPicture)drawing).PictureData.MimeType.Split('/')[1];string cellValue = $"<img src='data:image/{imageFormat};base64,{base64Image}'/>";htmlBuilder.AppendFormat("<td>{0}</td>", cellValue);}htmlBuilder.Append("</tr>");}}}htmlBuilder.Append("</table>");}htmlBuilder.Append("</div>");htmlBuilder.Append("</body></html>");return htmlBuilder.ToString();}class tude { public int xx { set; get; }public int yy { set; get; }
}

主要比较坑的是
1、使用 foreach (IRow row in sheet)，永远访问不到插入的图片，因为单元格是空
2、workbook.GetAllPictures()很容易获取到图片，sheet就很难各种报错
补充一些环境 .Net FramWork4.72 +NPOI 2.6.2

3、其他补充

3.0 主前端bootstrap

下载：https://bootstrap.p2hp.com/docs/5.3/getting-started/download/
不想搞太复杂，bootstrap就JS+CSS 下载解压复制到项目就行
我的样式

3.1 my.css：

body {padding-left: 10px;background-color: #e1e0e0;margin-bottom: 50px;
}
table {border: solid 1px #e1e0e0;background-color: white;}
table td {border: solid 1px #e1e0e0;}.nav {padding-left: 10px;padding-top: 10px;background-color:#e1e0e0
}
.menu {width: 100%;position: fixed;top: 40px;z-index: 100;
}
.content {position: relative;padding-top: 40px;z-index: 99;
}
@media (min-width: 768px) {}
@media (max-width: 768px) {.menu {position: fixed;top:40px;float: left;left: 10px;max-width: 350px;}.content {max-width:350px;overflow:scroll;}.nav-item {font-size:11px;white-space: nowrap;
/*        max-width: 60px;overflow: hidden;*/}}.navbar {position: fixed;z-index: 100;width:100%;background-color: #f8f9fa;box-shadow: 0 2px 4px rgba(0,0,0,0.1);
}
.back-arrow {text-decoration: none !important;color: inherit;display: inline-block;}

3.2 my.js

//加密
function encryptAES(plainText, key) {const encrypted = CryptoJS.AES.encrypt(plainText, CryptoJS.enc.Utf8.parse(key), {mode: CryptoJS.mode.ECB,padding: CryptoJS.pad.Pkcs7});return encrypted.toString();
}
//URL参数
function getQueryParam(param) {const regex = new RegExp('[?&]' + param + '=([^&#]*)');const match = regex.exec(window.location.search);return match ? decodeURIComponent(match[1].replace(/\+/g, ' ')) : null;
}

3.3 入口home.html

请求

<!DOCTYPE html>
<html>
<head><meta charset="utf-8" /><title></title><script src="assist/bootstrap/jquery-3.7.0.min.js"></script><script src="assist/my.js"></script><script type="text/javascript">window.onload = function () {var _src = getQueryParam('src');if (_src == null) return;var src =$.ajax({url: 'Data.ashx',type: 'POST',data: { src: _src },success: function (response) {window.location.href = response;}});}</script>
</head>
<body></body>
</html>

3.4 Data.ashx

public class Data : IHttpHandler
{public void ProcessRequest(HttpContext context){context.Response.ContentType = "text/plain";context.Response.Write(myvoid(context.Request.Form["src"]));}private string myvoid(string url) {if (url.Contains(".xlsx"))rst = ForExcel.Main(url);}}