WZExport/Utilitys/ExcelHelper.cs

using System.Collections.Generic;
using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System.Drawing;
using WZExport.Models;
using Utilty.Module.File.Source.Base;
using Microsoft.AspNetCore.Http;
using System;
using System.Text;

namespace WZExport.Utilitys
{
    /// <summary>
    /// Excel导入导出助手
    /// </summary>
    public class ExcelHelper
    {
        /// <summary>
        /// 导出授权记录
        /// </summary>
        /// <param name="prs"></param>
        /// <param name="fileName"></param>
        /// <param name="webRootFolder"></param>
        /// <returns></returns>
        public static void ExportLm(IEnumerable<Lm> lms, string fileName, string webRootFolder)
        {
            string filePath = Path.Combine(webRootFolder, $"{fileName}.xlxs");
            FileInfo file = new FileInfo(filePath);
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            using (ExcelPackage package = new ExcelPackage(file))
            {
                // 添加worksheet
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("栏目");

                //通用样式
                worksheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                worksheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                worksheet.Cells.Style.Font.Size = 12;
                worksheet.Cells.Style.Font.Bold = true;
                worksheet.DefaultRowHeight = 40;

                //添加表头
                worksheet.Cells[1, 1].Value = "栏目类型";
                worksheet.Cells[1, 1].Style.Font.Color.SetColor(Color.Red);
                worksheet.Column(1).Width = 15;

                worksheet.Cells[1, 2].Value = "栏目id";
                worksheet.Cells[1, 2].Style.Font.Color.SetColor(Color.Red);
                worksheet.Column(2).Width = 15;

                worksheet.Cells[1, 3].Value = "栏目url";
                worksheet.Cells[1, 3].Style.Font.Color.SetColor(Color.Red);
                worksheet.Column(3).Width = 15;

                worksheet.Cells[1, 4].Value = "数据表名";
                worksheet.Cells[1, 4].Style.Font.Color.SetColor(Color.Red);
                worksheet.Column(4).Width = 15;

                worksheet.Cells[1, 5].Value = "元素据id";
                worksheet.Cells[1, 5].Style.Font.Color.SetColor(Color.Red);
                worksheet.Column(5).Width = 15;

                worksheet.Cells[1, 6].Value = "栏目说明";
                worksheet.Cells[1, 6].Style.Font.Color.SetColor(Color.Red);
                worksheet.Column(6).Width = 40;

                worksheet.Cells[1, 7].Value = "一级栏目";
                worksheet.Cells[1, 7].Style.Font.Color.SetColor(Color.Red);
                worksheet.Column(7).Width = 40;

                worksheet.Cells[1, 8].Value = "二级栏目";
                worksheet.Cells[1, 8].Style.Font.Color.SetColor(Color.Red);
                worksheet.Column(8).Width = 40;

                worksheet.Cells[1, 9].Value = "三级栏目";
                worksheet.Cells[1, 9].Style.Font.Color.SetColor(Color.Red);
                worksheet.Column(9).Width = 40;

                worksheet.Cells[1, 10].Value = "四级栏目";
                worksheet.Cells[1, 10].Style.Font.Color.SetColor(Color.Red);
                worksheet.Column(10).Width = 40;

                worksheet.Cells[1, 11].Value = "五级栏目";
                worksheet.Cells[1, 11].Style.Font.Color.SetColor(Color.Red);
                worksheet.Column(11).Width = 40;

                worksheet.Cells[1, 12].Value = "六级栏目";
                worksheet.Cells[1, 12].Style.Font.Color.SetColor(Color.Red);
                worksheet.Column(12).Width = 40;

                int i = 1;
                foreach (var lm in lms)
                {
                    i++;
                    worksheet.Cells[i, 1].Value = "多信息";
                    worksheet.Cells[i, 2].Value = lm.Channelid;
                    worksheet.Cells[i, 3].Value = "";
                    worksheet.Cells[i, 4].Value = "";
                    worksheet.Cells[i, 5].Value = "";
                    worksheet.Cells[i, 6].Value = lm.Chnldesc;
                    worksheet.Cells[i, 6+lm.Siteid].Value = lm.Chnldesc;
                    //worksheet.Cells[i, 7].Value = lm.Chnldesc;
                    //worksheet.Cells[i, 8].Value = lm.Chnldesc;
                    //worksheet.Cells[i, 9].Value = lm.Chnldesc;
                    //worksheet.Cells[i, 10].Value = lm.Chnldesc;
                    //worksheet.Cells[i, 11].Value = lm.Chnldesc;
                    //worksheet.Cells[i, 12].Value = lm.Chnldesc;               
                }
                package.Save();
                //return File.OpenRead(filePath);
            }
        }

        /// <summary>
        /// Excel导入
        /// </summary>
        /// <param name="excelFile">Excel文件</param>
        public static List<Dictionary> ImportConfig(Stream excelFile)
        {
            List<Dictionary> result = new List<Dictionary>();
            // 读取单个工作表sheet内容          
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            using (ExcelPackage package = new ExcelPackage(excelFile))
            {
                ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
                int rowCount = worksheet.Dimension.Rows;
                Dictionary dic;              
                // 明细行
                for (int row = 2; row <= rowCount; row++)
                {
                    dic = new Dictionary();
                    dic.KeyLable = worksheet.Cells[row, 1].Value?.ToString().Trim();
                    dic.Value = worksheet.Cells[row, 2].Value?.ToString().Trim();                   
                    result.Add(dic);
                }
            }
            return result;
        }

        public static void ExportLmUniversal(IEnumerable<Lm> lms, string fileName, string webRootFolder)
        {
            string filePath=Path.Combine(webRootFolder, $"{fileName}.xls");
            string sheetName = "栏目";
            var sheetData = new List<List<string>>();
            var excelData=new Dictionary<string, List<List<string>>> { [sheetName]= sheetData };
            var headerData = new List<string>();
            headerData.Add("栏目类型");
            headerData.Add("栏目id");
            headerData.Add("栏目url");
            headerData.Add("数据表名");
            headerData.Add("元素据id");
            headerData.Add("栏目说明");
            headerData.Add("一级栏目");
            headerData.Add("二级栏目");
            headerData.Add("三级栏目");
            headerData.Add("四级栏目");
            headerData.Add("五级栏目");
            headerData.Add("六级栏目");
            sheetData.Add(headerData);

            foreach (var lm in lms)
            {
                var rowData = new List<string>();
                rowData.Add("多信息");
                rowData.Add(lm.Channelid.ToString());
                rowData.Add("");
                rowData.Add("");
                rowData.Add("");
                rowData.Add(lm.Chnldesc);
                for (int i = 1; i <= 6; i++)
                { 
                    if(i==lm.Siteid) rowData.Add(lm.Chnldesc);
                    else rowData.Add("");
                }
                sheetData.Add(rowData);
            }
            new ExcelBase().CreateExcel(filePath,excelData);
        }
    }
}