实现Excel直接在以Html的形式在页面上展示

最新推荐文章于 2025-03-03 10:49:30 发布

沉默的方三毛

最新推荐文章于 2025-03-03 10:49:30 发布

阅读量1.8w

点赞数 7

分类专栏： c# 前端

本文链接：https://blog.csdn.net/weixin_43663915/article/details/103657140

版权

c# 同时被 2 个专栏收录

4 篇文章

订阅专栏

前端

3 篇文章

订阅专栏

目前大多数Excel内容需要在页面展示时，通常需要使用控件或将excel转为html的形式在页面上进行展示，但是经常会出现达不到理想的效果。本文主要使用NPOI读取excel内容以及单元格样式，然后使用jquery的datatables（官网：http://www.datatables.net/）插件来显示数据。

核心代码如下：

其中前端js代码如下，文件名为table.js(自己定就好了)：

(function ($) {
    /*
    其中参数说明：
    isExcelHeader处理是否需要加上类似excel一样的表头
    isLoadExcel 用来判断是否加载excel文件内容,如果为false则假在DataTable数据格式
    style在isLoadExcel为false时有效，用来指定DataTable中的列在页面上对齐方式
    */
    var defaults = {
        isExcelHeader: true,//处理是否需要加上类似excel一样的表头
        paging: false,
        searching: false,
        ordering: false,
        info: false,
        scrollX: true,
        autoWidth: false,
        style: null,
        isLoadExcel: true
    }
    var configArgs = {
        columns: null,
        data: null,
        mergeCells: null,
        cellStyles: null
    }

    $.fn.extend({
        loadDataToTable: function (jsonStr) {
            var json = jsonStr;
            if (typeof jsonStr === "object") {//判断是否为对象，如果为对象直接覆盖默认值
                $.extend(true, defaults, jsonStr)
                json = jsonStr.data;
            }

            if (defaults.isLoadExcel) {
                return initTable4Excel(json, defaults, $(this));
            } else {
                return initTable(json, defaults, $(this));
            }
        }
    });

    /**
    *加载c#中为datatable的数据格式
    */
    function initTable(json, defaults, $table) {
        var jsonStr = json;
        if (typeof jsonStr === "object") {//判断是否为对象，如果为对象直接覆盖默认值
            $.extend(true, defaults, jsonStr)
            jsonStr = json.data;
        }
        if (jsonStr == null || jsonStr == "") {
            alert("数据为空！");
            return;
        }
        var json = JSON.parse(jsonStr);
        var coloumDef = Object.keys(json[0]);//获取DataTable中表头数据
        var data = [];
        var column = [];
        if (!defaults.isExcelHeader) {
            for (var i = 0; i < json.length; i++) {
                data[i] = [];
                for (var j = 0; j < coloumDef.length; j++) {
                    data[i][j] = json[i][coloumDef[j]];
                }
            }
            for (var i = 0; i < coloumDef.length; i++) {
                column[i] = { title: coloumDef[i] };
            }
        } else {
            for (var r = 0; r <= json.length; r++) {
                data[r] = [];
                for (var c = 0; c < coloumDef.length; c++) {
                    if (r == 0) {
                        column[c+1] = { title: numToExcelHeader(c) }
                        data[r][c+1] = coloumDef[c];
                    } else {
                        data[r][c+1] = json[r - 1][coloumDef[c]];

                    }
                    if (c == 0) {
                        data[r][0] = r + 1;
                        column[c] = { title: "" };
                    }
                }
            }
        }

        var $parent = $($table).parent();
        if (column.length < 4) {//通过列的数量控制宽度占比
            $($parent).attr("style", "width:60% !important;");
        } else {
            $($parent).attr("style", "width:100% !important;");
        }
        //对于DataTable的数据无需隐藏Th，因此需要移除父元素的hiddenTh类
        $($parent).removeClass("hiddenTh");


        return $table.dataTable({
            paging: defaults.paging,
            searching: defaults.searching,
            ordering: defaults.ordering,
            info: defaults.info,
            scrollX: defaults.scrollX,
            autoWidth: defaults.autoWidth,
            data: data,
            columns: column,
            columnDefs: [{
                targets: '_all',
                createdCell: function (td, cellData, rowData, row, col) {
                    if (row == 0 && defaults.isExcelHeader) {
                        //$(td).attr('style', "text-align:center;font-weight:bold;")
                        return;
                    }
                    if (defaults.style != null) {
                        var tdStyle = getConfigStyle(defaults.style[col]);
                        $(td).attr('style', tdStyle);
                    }
                }
            }
            ]
        }).api();
    }


    /**
     *加载excel内容
     */
    function initTable4Excel(json, defaults, $table) {
        var jsonStr = json;
        if (typeof jsonStr === "object") {//判断是否为对象，如果为对象直接覆盖默认值
            $.extend(true, defaults, jsonStr)
            jsonStr = json.data;
        }
        if (jsonStr == null || jsonStr == "") {
            alert("数据为空！");
            return;
        }
        prepareData(jsonStr, defaults.isExcelHeader);

        var $parent = $($table).parent();

        if (configArgs.columns.length < 4) {
            $($parent).attr("style", "width:60% !important;");
        } else {
            $($parent).attr("style", "width:100% !important;");
        }
        if (defaults.isExcelHeader) {
            $($parent).removeClass("hiddenTh");
        } else {
            $($parent).addClass("hiddenTh");
        }

        return $table.dataTable({
            paging: defaults.paging,
            searching: defaults.searching,
            ordering: defaults.ordering,
            info: defaults.info,
            scrollX: defaults.scrollX,
            autoWidth: defaults.autoWidth,
            data: configArgs.data,
            columns: configArgs.columns,
            columnDefs: [{
                targets: '_all',
                createdCell: function (td, cellData, rowData, row, col) {
                    if (defaults.isHeader && col == 0) {
                        return;
                    }
                    var mi = configArgs.mergeCells[row + '_' + col];
                    if (mi != null) {
                        if (mi.rowspan == 0 || mi.colspan == 0) {
                            $(td).remove();
                            return;
                        }
                        if (mi.rowspan > 1) {
                            $(td).attr('rowspan', mi.rowspan)
                        }
                        if (mi.colspan > 1) {
                            $(td).attr('colspan', mi.colspan)
                        }
                    }
                    var cStyle = configArgs.cellStyles[row][col];
                    if (cStyle != null) {
                        $(td).attr('style', cStyle)
                    }
                }
            }
            ]
        }).api();
    }

    function prepareData(jsonStr, isHeader) {
        configArgs.columns = [], configArgs.data = [], configArgs.mergeCells = {}, configArgs.cellStyles = [];
        var rows = JSON.parse(jsonStr);
        for (var r = 0; r < rows.length; r++) {
            var cells = rows[r];
            for (var c = 0; c < cells.length; c++) {
                if (r == 0) {
                    if (!isHeader) {
                        configArgs.columns[c] = { title: numToExcelHeader(c) };
                    } else {
                        configArgs.columns[c+1] = { title: numToExcelHeader(c) };
                    }
                    
                }
                if (c == 0) {
                    configArgs.data[r] = [];
                    if (isHeader) {
                        configArgs.data[r][0] = r + 1;
                        configArgs.columns[c] = { title: "" };
                    }
                    configArgs.cellStyles[r] = [];
                }
                if (isHeader) {
                    configArgs.data[r][c+1] = cells[c].Value;
                    if (cells[c].IsMergeCell) {
                        if (cells[c].MergeInfo != null) {
                            configArgs.mergeCells[r + '_' + (c+1)] = cells[c].MergeInfo;
                        } else {
                            configArgs.mergeCells[r + '_' + (c+1)] = { rowspan: 0, colspan: 0 };
                        }
                    }
                    configArgs.cellStyles[r][c+1] = getStyle(cells[c]);
                } else {
                    configArgs.data[r][c] = cells[c].Value;
                    if (cells[c].IsMergeCell) {
                        if (cells[c].MergeInfo != null) {
                            configArgs.mergeCells[r + '_' + c] = cells[c].MergeInfo;
                        } else {
                            configArgs.mergeCells[r + '_' + c] = { rowspan: 0, colspan: 0 };
                        }
                    }
                    configArgs.cellStyles[r][c] = getStyle(cells[c]);
                }
                
            }
        }
    }

    function getStyle(cell) {
        var style = null;
        if (cell.HorizontalAlign == 'Center') {
            style = 'text-align:center;';
        } else if (cell.HorizontalAlign == 'Right') {
            style = 'text-align:right;';
        }

        return style;
    }
})(jQuery);


function getConfigStyle(style) {
    if (style == "right") {
        return 'text-align:right;';
    } else if (style == "center") {
        return 'text-align:center;';
    } else {
        return 'text-align:left;';
    }
}

(function () {
    if (!Object.keys) Object.keys = function (o) {
        if (o !== Object(o))
            throw new TypeError('Object.keys called on a non-object');
        var k = [], p;
        for (p in o) if (Object.prototype.hasOwnProperty.call(o, p))
            k.push(p);
        return k;
    }

})()

function isNumber(value) {         //验证是否为数字
    var patrn = /^(-)?\d+(\.\d+)?$/;
    if (patrn.exec(value) == null || value == "") {
        return false
    } else {
        return true
    }
}


function numToExcelHeader(col) {
    var ordA = 'A'.charCodeAt(0);
    var len = 'Z'.charCodeAt(0) - 'A'.charCodeAt(0) + 1;
    var s = '', c = col;
    while (c >= 0) {
        s = String.fromCharCode(c % len + ordA) + s;
        c = Math.floor(c / len) - 1;
    }
    return s;
}

我这里使用C#语言来读取Excel内容（如果是java的话使用POI来读取Excel内容，写法和C#大概一致），其解析excel代码如下，文件名为TableUtil.cs：

using Newtonsoft.Json;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using iTextSharp.text;
using System.Text;

namespace TableDemo.Table
{
    public class TableUtil
    {
        /// <summary>
        /// 使用的Newtonsoft.Json
        /// JSON反序列化，支持Dictionary
        /// </summary>
        public static T Parse<T>(string jsonString)
        {
            return JsonConvert.DeserializeObject<T>(jsonString);
        }

        /// <summary>
        /// 使用的Newtonsoft.Json
        /// JSON序列化，支持Dictionary
        /// </summary>
        public static string ToJson(object jsonObject)
        {
            return JsonConvert.SerializeObject(jsonObject);
        }

        /// <summary>
        /// 将DataTable数据类型转换为JSON字符串
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static string DataTableToJson(DataTable dt)
        {
            StringBuilder JsonString = new StringBuilder();
            if (dt != null && dt.Rows.Count > 0)
            {
                JsonString.Append("[ ");
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    JsonString.Append("{ ");
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        if (j < dt.Columns.Count - 1)
                        {
                            JsonString.Append("\"" + dt.Columns[j].ColumnName.ToString() + "\":" + "\"" + dt.Rows[i][j].ToString() + "\",");
                        }
                        else if (j == dt.Columns.Count - 1)
                        {
                            JsonString.Append("\"" + dt.Columns[j].ColumnName.ToString() + "\":" + "\"" + dt.Rows[i][j].ToString() + "\"");
                        }
                    }
                    /**/
                    /**/
                    /**/
                    /*end Of String*/
                    if (i == dt.Rows.Count - 1)
                    {
                        JsonString.Append("} ");
                    }
                    else
                    {
                        JsonString.Append("}, ");
                    }
                }
                JsonString.Append("]");
                return JsonString.ToString();
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获取excel单元格信息,字体、边框、背景均为黑色处理，如有实际需要可以自行处理
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static List<List<ReportCell>> GetExcelCells(string filePath)
        {
            List<List<ReportCell>> lists = new List<List<ReportCell>>();

            IWorkbook hw = null;
            using (FileStream fs = new FileStream(filePath, FileMode.Open, System.IO.FileAccess.Read))
            {
                if (filePath.EndsWith(".xls"))
                {
                    hw = new HSSFWorkbook(fs);
                }
                else if (filePath.EndsWith(".xlsx"))
                {
                    hw = new XSSFWorkbook(fs);
                }
            }

            try
            {
                ISheet sheet = hw.GetSheetAt(0);
                for (int r = sheet.FirstRowNum; r < sheet.PhysicalNumberOfRows; r++)
                {
                    IRow row = sheet.GetRow(r);
                    if (row == null)
                    {
                        continue;
                    }

                    float rowHeight = row.HeightInPoints;
                    List<ReportCell> list = new List<ReportCell>();
                    for (int c = row.FirstCellNum; c < row.PhysicalNumberOfCells && c > -1; c++)
                    {
                        ICell cell = row.Cells[c];
                        ReportCell info = new ReportCell();
                        string cellValue = cell.ToString();
                        info.Value = cellValue;
                        info.RowHeiht = rowHeight * 1.33f; // pt => px
                        info.ColWidth = sheet.GetColumnWidthInPixels(c);
                        info.IsMergeCell = cell.IsMergedCell;
                        if (info.IsMergeCell)
                        {
                            int[] span = GetMergeCellSpan(sheet, r, c);
                            if (span[0] != 1 || span[1] != 1)
                            {
                                ReportCellMergeInfo mi = new ReportCellMergeInfo();
                                mi.row = r;
                                mi.col = c;
                                mi.rowspan = span[0];
                                mi.colspan = span[1];
                                info.MergeInfo = mi;
                            }
                        }
                        info.HorizontalAlign = cell.CellStyle.Alignment.ToString();
                        info.VerticalAlign = cell.CellStyle.VerticalAlignment.ToString();
                        IFont font = cell.CellStyle.GetFont(hw);
                        info.FontSize = (float)font.FontHeightInPoints;
                        

                        //Color ftColor = Color.BLACK;
                        //short ft = font.Color;

                        //info.FontColor = ftColor.R + "," + ftColor.G + "," + ftColor.B;
                        //info.IsBorder = HasBorder(cell);

                        //if (info.IsBorder)
                        //{
                        //    Color bdColor = Color.BLACK;
                        //    short bd = cell.CellStyle.TopBorderColor;

                        //    info.BorderColor = bdColor.R + "," + bdColor.G + "," + bdColor.B;
                        //}

                        //short bg = cell.CellStyle.FillForegroundColor;
                        //Color bgColor = Color.WHITE;

                        //info.BackgroundColor = bgColor.R + "," + bgColor.G + "," + bgColor.B;
                        list.Add(info);
                    }
                    lists.Add(list);
                }
            }
            finally
            {
                hw.Close();
            }

            return lists;
        }




        /// <summary>
        /// 合并单元格的rowspan、colspan
        /// </summary>
        private static int[] GetMergeCellSpan(ISheet sheet, int rowNum, int colNum)
        {
            int[] span = { 1, 1 };
            int regionsCount = sheet.NumMergedRegions;
            for (int i = 0; i < regionsCount; i++)
            {
                CellRangeAddress range = sheet.GetMergedRegion(i);
                sheet.IsMergedRegion(range);
                if (range.FirstRow == rowNum && range.FirstColumn == colNum)
                {
                    span[0] = range.LastRow - range.FirstRow + 1;
                    span[1] = range.LastColumn - range.FirstColumn + 1;
                    break;
                }
            }
            return span;
        }

        private static bool HasBorder(ICell cell)
        {
            int bottom = cell.CellStyle.BorderBottom != 0 ? 1 : 0;
            int top = cell.CellStyle.BorderTop != 0 ? 1 : 0;
            int left = cell.CellStyle.BorderLeft != 0 ? 1 : 0;
            int right = cell.CellStyle.BorderRight != 0 ? 1 : 0;
            return (bottom + top + left + right) > 2;
        }
    }
}

解析Excel所需要的实体类,文件名为ReportCell.cs：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace TableDemo.Table
{
    public class ReportCell
    {
         public string Value { get; set; }
        public bool IsMergeCell { get; set; }
        public ReportCellMergeInfo MergeInfo { get; set; }
        public string FontColor { get; set; }
        public float FontSize { get; set; }
        public string BorderColor { get; set; }
        public bool IsBorder { get; set; }
        public string VerticalAlign { get; set; }
        public string HorizontalAlign { get; set; }
        public string BackgroundColor { get; set; }
        public float RowHeiht { get; set; }
        public float ColWidth { get; set; }
    }

    public class ReportCellMergeInfo
    {
        public int row { get; set; }
        public int col { get; set; }
        public int rowspan { get; set; }
        public int colspan { get; set; }
    }
    
}

以上是核心代码，如下是Demo代码用的是c#以及实现效果：

Demo前端代码如下：

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Demo.aspx.cs" Inherits="Demo.Demo" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <title></title>
    <link href="Scripts/datatables.min.css" rel="stylesheet" />
    <link href="style/table.css" rel="stylesheet" />
    <script src="Scripts/jquery-3.4.1.min.js"></script>
    <script src="Scripts/datatables.min.js"></script>
    <script src="http://localhost:60313/Scripts/table.js?v=1"></script>
     <script>
         $(function () {
             var isExcel = true;//判断加载的是否为Excel内容
             var $Datatable;
             var style = ["center", "right", "center", "center"];//只有DataTable数据可以指定对齐位置，如不指定则默认靠左对齐，excel直接根据其对齐方式来
             $.ajax({
                 url: "Demo.aspx/GetData",
                 type: "POST",
                 contentType: "application/json; charset=utf-8",
                 dataType: "json",
                 data: JSON.stringify({ isExcel: isExcel }),
                 success: function (res) {
                     if ($.fn.DataTable.isDataTable('#datalist')) {
                         $('#datalist').DataTable().destroy();  
                     }
                     var table = $('<table class="table cell-border hover stripe display  nowrap" id="datalist" cellspacing="0" style="width: 100 %"></table>');
                     $('#divData').empty().append(table);
                     console.log(res);
                     $Datatable = $("#datalist").loadDataToTable({ data: res.d, isLoadExcel: isExcel, style: style, isExcelHeader: true });
                 },
                 error: function (err) {
                     alert(err);
                 }
             });

             $(window).resize(function () {
                 if (typeof $Datatable != "undefined") {
                     $("#datalist").dataTable().fnAdjustColumnSizing(false);
                 }
             });
         });
    </script>
</head>
<body>
    <form id="form1" runat="server">
         <div id="divData" style="width:100%;">
             <table class="table cell-border hover stripe  display  nowrap" id="datalist" cellspacing="0" style="width:100%"></table>
         </div>
    </form>
</body>
</html>

Demo后端代码：

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.UI;
using System.Web.UI.WebControls;
using TableDemo.Table;

namespace Demo
{
    public partial class Demo : System.Web.UI.Page
    {
        protected static string templateFolder;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                templateFolder = Server.MapPath("/Template/");
            }

        }


        [WebMethod(EnableSession = true)]
        public static string GetData(bool isExcel)
        {
            if (isExcel)
            {
                return GetExcelData();
            }
            else
            {
                return GetTableData();
            }
        }

        private static string GetExcelData()
        {
            List<List<ReportCell>> list = TableUtil.GetExcelCells(templateFolder + "Demo.xls");
            return TableUtil.ToJson(list);
        }

        private static string GetTableData()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("序号", typeof(string));
            dt.Columns.Add("姓名", typeof(string));
            dt.Columns.Add("职业", typeof(string));
            dt.Columns.Add("年龄", typeof(string));
            dt.Columns.Add("序号11111fasdfjksadfljsdajflkjsdlkflk", typeof(string));
            dt.Columns.Add("姓名222fsadfsadf2", typeof(string));
            dt.Columns.Add("职业2fsdfsdafsdf2222", typeof(string));
            dt.Columns.Add("年龄2sdfsdfsadf22222", typeof(string));
            dt.Columns.Add("序号33sfdfsdfsdf3333", typeof(string));
            dt.Columns.Add("姓名33fdsfsdfsdaf333", typeof(string));
            dt.Columns.Add("职业33sdffaaaa33", typeof(string));
            dt.Columns.Add("年龄33aaaaaaaaaadsf33", typeof(string));
            for (int i = 1; i <= 30; i++)
            {
                DataRow dr = dt.NewRow();
                dr.ItemArray = new object[] { i, "小明" + i, "程序员" + i, i, i, "小明" + i, "程序员" + i, i, i, "小明" + i, "程序员" + i, i };
                dt.Rows.Add(dr);
            }

            return TableUtil.DataTableToJson(dt);
        }
    }
}

excel内容如下：

在页面上显示的效果：

如果不需要Excel表头和左侧第一列，只需改动一个参数即可：$("#datalist").loadDataToTable({ data: res.d, isLoadExcel: isExcel, style: style, isExcelHeader: true });，将其中的isExcelHeader改为false即可，其效果如下：