Elight.Utility/Files/ExcelUtils.cs

这里只显示每个文件的开头 60 行。登录后可以解锁完整代码。

微信扫码关注公众号，代码整理好后第一时间通知你。

using Elight.Utility.Core;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;

namespace Elight.Utility.Files
{
    public class ExcelUtils
    {
        public static string ExcelContentType
        {
            get
            {
                return "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            }
        }
        public static ExcelPackage ExportSheet<T>(ExcelPackage package, List<T> data, Dictionary<string, string> headDict, string sheetName = "", bool showSrNo = false)
        {
            return ExportSheet(package, data.ToDataTable<T>(), headDict, sheetName, showSrNo);
        }

        public static ExcelPackage ExportSheet(ExcelPackage package, DataTable dt, Dictionary<string, string> headDict, string sheetName = "", bool showSrNo = false)
        {
            List<string> keyList = new List<string>();
            if (showSrNo)
            {
                keyList.Add("RowNum");
                dt.Columns.Add("RowNum");
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    dt.Rows[i]["RowNum"] = i + 1;
                }
            }
            //通过键的集合取
            foreach (string key in headDict.Keys)
            {
                keyList.Add(key);
            }
            ExcelWorksheet sheet = package.Workbook.Worksheets.Add(sheetName.IsNullOrEmpty() ? "Sheet1" : sheetName);
            if (showSrNo)
            {
                headDict.Add("RowNum", "序号");
            }
            for (int i = 0; i < keyList.Count; i++)
            {
                sheet.Cells[1, i + 1].Value = headDict[keyList[i]];
            }
            if (dt.Rows.Count > 0)
            {

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    for (int j = 0; j < keyList.Count; j++)
                    {

                        sheet.Cells[i + 2, j + 1].Value = dt.Rows[i][keyList[j]].ToString();