首页 笔记 C#

# 前言

记录一些平时整理的工具类，方便日后查找使用

# Excel 工具类 (NPOI)

ExcelHelper.cs
public static class ExcelHelper
{
    public static FileInfo ExportExcel<TSource>(this List<TSource> sources, ExcelColumn<TSource>[] columns, string saveDirPath, string fileName = null)
    {
        IWorkbook workbook = new XSSFWorkbook();// 创建 xlsx
        ISheet sheet = workbook.CreateSheet("Sheet1");
        IRow headerRow = sheet.CreateRow(0);
        for (int i = 0; i < columns.Length; i++)
        {
            var column = columns[i];
            headerRow.CreateCell(i).SetCellValue(column.Title);
        }
        for (int i = 0; i < sources.Count; i++)
        {
            var source = sources[i];
            var row = sheet.CreateRow(i + 1);
            row.FillRow(source, columns);
        }
        using MemoryStream stream = new MemoryStream();
        workbook.Write(stream);
        var buf = stream.ToArray();
        if (!Directory.Exists(saveDirPath))
        {
            Directory.CreateDirectory(saveDirPath);
        }
        if (string.IsNullOrWhiteSpace(fileName))
        {
            fileName = $"{DateTime.Now.ToFileNameString()}.xlsx";
        }
        var fullSavePath = Path.Combine(saveDirPath, fileName);
        using FileStream fs = new FileStream(fullSavePath, FileMode.Create, FileAccess.Write);
        fs.Write(buf, 0, buf.Length);
        fs.Flush();
        return new FileInfo(fullSavePath);
    }
    private static void FillRow<TSource>(this IRow row, TSource source, ExcelColumn<TSource>[] columns)
    {
        for (int i = 0; i < columns.Length; i++)
        {
            var column = columns[i];
            var value = source.ToStringValue(column);
            row.CreateCell(i).SetCellType(CellType.String).SetCellValue(value);
        }
    }
    private static string ToStringValue<TSource>(this TSource source, ExcelColumn<TSource> column)
    {
        var value = column.Selector(source);
        if (value is null)
        {
            return string.Empty;
        }
        if (source is DateTime)
        {
            return ((DateTime)value).ToString("yyyy-MM-dd HH:mm:ss");
        }
        return value.ToString();
    }
}
ExcelColumn.cs
public record ExcelColumn<TSource>
{
    public string Title { get; set; }
    public Func<TSource, object> Selector { get; set; }
    public ExcelColumn(string title, Func<TSource, object> selector)
    {
        this.Title = title;
        this.Selector = selector;
    }
}
使用示例
public (FileInfo, string) CreateExcel(List<InvoiceRequestDto> datas)
{
    var columns = new ExcelColumn<InvoiceRequestDto>[]
    {
        new("发票号",o=>o.InvoiceCode),
        new("客户编号",o=>o.CustomerCode),
        new("类型",o=>o.Type.ToString()),
        new("名称",o=>o.Name),
        new("Tin",o=>o.Tin),
        new("Brn",o=>o.Brn),
        new("Tel",o=>o.Tel),
        new("Email",o=>o.Email),
        new("地址",o=>o.Address),
        new("邮编",o=>o.PostCode),
        new("州属",o=>o.StateName),
        new("申请人",o=>o.UserName),
        new("发票链接",o=>BusinessHelper.GetEInvoiceUrl(o.Uuid,o.LongId)),
    };
    var excelDirPath = FilePath.GetExcelDirPath();
    var fileInfo = datas.ExportExcel(columns, excelDirPath);
    var relativePath = fileInfo.ToRelativePath(excelDirPath);
    return (fileInfo, relativePath);
}
# DateTime 工具类
DateTimeHelper.cs
public static class DateTimeHelper
{
    /// <summary>
    /// yyyy-MM-dd HH:mm:ss
    /// </summary>
    private static readonly DateTimeFormatInfo SimpleDateTimeFormat = new DateTimeFormatInfo()
    {
        ShortDatePattern = "yyyy-MM-dd HH:mm:ss"
    };
    /// <summary>
    /// 接收一个日期字符串，返回当天 00:00:00 的日期
    /// </summary>
    /// <param name="dateStr"></param>
    /// <returns></returns>
    public static DateTime GetDayStart(this string dateStr)
    {
        var dt = Convert.ToDateTime(dateStr);
        return new DateTime(dt.Year, dt.Month, dt.Day, 0, 0, 0);
    }
    /// <summary>
    /// 接收一个日期字符串，返回当天 23:59:59 的日期
    /// </summary>
    /// <param name="dateStr"></param>
    /// <returns></returns>
    public static DateTime GetDayEnd(this string dateStr)
    {
        var dt = Convert.ToDateTime(dateStr);
        return new DateTime(dt.Year, dt.Month, dt.Day, 23, 59, 59);
    }
    /// <summary>
    /// 接收一个日期字符串，返回当天 00:00:00 的日期字符串
    /// </summary>
    /// <param name="dateStr"></param>
    /// <returns></returns>
    public static string GetDayStartStr(this string dateStr)
    {
        return GetDayStart(dateStr).ToString("yyyy-MM-dd 00:00:00");
    }
    /// <summary>
    /// 接收一个日期字符串，返回当天 23:59:59 的日期字符串
    /// </summary>
    /// <param name="dateStr"></param>
    /// <returns></returns>
    public static string GetDayEndStr(this string dateStr)
    {
        return GetDayEnd(dateStr).ToString("yyyy-MM-dd 23:59:59");
    }
    /// <summary>
    /// 获取今天开始时间
    /// </summary>
    /// <returns></returns>
    public static DateTime GetTodayStart()
    {
        DateTime now = DateTime.Now;
        return new DateTime(now.Year, now.Month, now.Day);
    }
    /// <summary>
    /// 获取今天结束时间
    /// </summary>
    /// <returns></returns>
    public static DateTime GetTodayEnd()
    {
        DateTime now = DateTime.Now;
        return new DateTime(now.Year, now.Month, now.Day, 23, 59, 59);
    }
    /// <summary>
    /// 获取本周开始时间
    /// </summary>
    /// <returns></returns>
    public static DateTime GetWeekStart()
    {
        DateTime now = DateTime.Now;
        DateTime temp = new DateTime(now.Year, now.Month, now.Day);
        int count = now.DayOfWeek - DayOfWeek.Monday;
        if (count == -1) count = 6;
        return temp.AddDays(-count);
    }
    /// <summary>
    /// 获取本周结束时间
    /// </summary>
    /// <returns></returns>
    public static DateTime GetWeekEnd()
    {
        DateTime now = DateTime.Now;
        DateTime temp = new DateTime(now.Year, now.Month, now.Day, 23, 59, 59);
        int count = now.DayOfWeek - DayOfWeek.Sunday;
        if (count != 0) count = 7 - count;
        return temp.AddDays(count);
    }
    /// <summary>
    /// 获取本周开始时间
    /// </summary>
    /// <returns></returns>
    public static DateTime GetMonthStart()
    {
        DateTime now = DateTime.Now;
        return new DateTime(now.Year, now.Month, 1, 0, 0, 0);
    }
    /// <summary>
    /// 获取本周结束时间
    /// </summary>
    /// <returns></returns>
    public static DateTime GetMonthEnd()
    {
        DateTime now = DateTime.Now;
        DateTime temp = new DateTime(now.Year, now.Month, 1, 23, 59, 59);
        return temp.AddMonths(1).AddDays(-1);
    }
    /// <summary>
    /// 获取本年开始时间
    /// </summary>
    /// <returns></returns>
    public static DateTime GetYearStart()
    {
        DateTime now = DateTime.Now;
        return new DateTime(now.Year, 1, 1, 0, 0, 0);
    }
    /// <summary>
    /// 获取本年结束时间
    /// </summary>
    /// <returns></returns>
    public static DateTime GetYearEnd()
    {
        DateTime now = DateTime.Now;
        return new DateTime(now.Year, 12, 31, 23, 59, 59);
    }
    /// <summary>
    /// 获取昨天开始时间
    /// </summary>
    /// <returns></returns>
    public static DateTime GetYesterdayStart()
    {
        return GetTodayStart().AddDays(-1);
    }
    /// <summary>
    /// 获取昨天结束时间
    /// </summary>
    /// <returns></returns>
    public static DateTime GetYesterdayEnd()
    {
        return GetTodayEnd().AddDays(-1);
    }
    // <summary>
    /// 获取上周开始时间
    /// </summary>
    /// <returns></returns>
    public static DateTime GetLastWeekStart()
    {
        return GetWeekStart().AddDays(-7);
    }
    /// <summary>
    /// 获取上周结束时间
    /// </summary>
    /// <returns></returns>
    public static DateTime GetLastWeekEnd()
    {
        return GetWeekEnd().AddDays(-7);
    }
    // <summary>
    /// 获取上月开始时间
    /// </summary>
    /// <returns></returns>
    public static DateTime GetLastMonthStart()
    {
        DateTime now = DateTime.Now;
        DateTime temp = new DateTime(now.Year, now.Month, 1, 0, 0, 0);
        return temp.AddMonths(-1);
    }
    /// <summary>
    /// 获取上月结束时间
    /// </summary>
    /// <returns></returns>
    public static DateTime GetLastMonthEnd()
    {
        DateTime now = DateTime.Now;
        DateTime temp = new DateTime(now.Year, now.Month, 1, 23, 59, 59);
        return temp.AddDays(-1);
    }
    /// <summary>
    /// 根据一个总秒数，返回 "时：分：秒" 格式字符串
    /// </summary>
    /// <param name="totalSecond"></param>
    /// <returns></returns>
    public static string GetTimeStrBySeconds(int totalSecond)
    {
        string hour = (totalSecond / 3600).ToString().PadLeft(2, '0');
        string minute = ((totalSecond % 3600) / 60).ToString().PadLeft(2, '0');
        string second = (totalSecond % 60).ToString().PadLeft(2, '0');
        return string.Format("{0}:{1}:{2}", hour, minute, second);
    }
    /// <summary>
    /// 获取两个日期的秒数差值
    /// </summary>
    /// <param name="timeStart"></param>
    /// <param name="timeEnd"></param>
    /// <returns></returns>
    public static int SecondDiff(this DateTime timeEnd, DateTime timeStart)
    {
        TimeSpan timeSpanStart = new TimeSpan(timeStart.Ticks);
        TimeSpan timeSpanEnd = new TimeSpan(timeEnd.Ticks);
        TimeSpan timeDiff = timeSpanEnd.Subtract(timeSpanStart);
        return (int)timeDiff.TotalSeconds;
    }
    /// <summary>
    /// 获取两个日期的秒数差值
    /// </summary>
    /// <param name="timeStart"></param>
    /// <param name="timeEnd"></param>
    /// <returns></returns>
    public static TimeSpan GetTimeSpanDiff(DateTime timeStart, DateTime timeEnd)
    {
        TimeSpan timeSpanStart = new TimeSpan(timeStart.Ticks);
        TimeSpan timeSpanEnd = new TimeSpan(timeEnd.Ticks);
        return timeSpanEnd.Subtract(timeSpanStart);
    }
    /// <summary>
    /// 获取两个日期的天数差值
    /// </summary>
    /// <param name="timeStart"></param>
    /// <param name="timeEnd"></param>
    /// <returns></returns>
    public static int GetDayDiff(DateTime timeStart, DateTime timeEnd)
    {
        TimeSpan timeSpanStart = new TimeSpan(timeStart.Ticks);
        TimeSpan timeSpanEnd = new TimeSpan(timeEnd.Ticks);
        TimeSpan timeDiff = timeSpanEnd.Subtract(timeSpanStart);
        return (int)timeDiff.TotalDays;
    }
    /// <summary>
    /// 获取当前时间 n 秒后的实例
    /// </summary>
    /// <param name="minutes"></param>
    /// <returns></returns>
    public static DateTime GetDateTimeAfterMinutes(int minutes)
    {
        return DateTime.Now.AddMinutes(minutes);
    }
    /// <summary>
    /// DateTime 转换为 Unix 时间戳 (秒)
    /// </summary>
    /// <param name="dateTime"></param>
    /// <returns></returns>
    public static long ToTimeStamp(this DateTime dateTime)
    {
        DateTime startTime = new DateTime(1970, 1, 1, 0, 0, 0, 0, DateTimeKind.Utc);
        return (long)(dateTime.ToUniversalTime() - startTime).TotalSeconds;
    }
    /// <summary>
    /// Unix 时间戳 (秒) 转 DateTime
    /// </summary>
    /// <param name="timeStamp"></param>
    /// <returns></returns>
    public static DateTime ToDateTime(this long timeStamp)
    {
        DateTime dateTime = new DateTime(1970, 1, 1, 0, 0, 0, 0, DateTimeKind.Utc);
        dateTime = dateTime.AddSeconds(timeStamp).ToLocalTime();
        return dateTime;
    }
    /// <summary>
    /// 获取当天是本星期的第几天
    /// </summary>
    /// <returns></returns>
    public static int GetDayOfWeek()
    {
        DayOfWeek dayOfWeek = DateTime.Today.DayOfWeek;
        if (dayOfWeek == DayOfWeek.Monday) return 1;
        if (dayOfWeek == DayOfWeek.Tuesday) return 2;
        if (dayOfWeek == DayOfWeek.Wednesday) return 3;
        if (dayOfWeek == DayOfWeek.Thursday) return 4;
        if (dayOfWeek == DayOfWeek.Friday) return 5;
        if (dayOfWeek == DayOfWeek.Saturday) return 6;
        if (dayOfWeek == DayOfWeek.Sunday) return 7;
        return 0;
    }
    /// <summary>
    /// 将一个 DateTime 格式化为时间字符串
    /// </summary>
    /// <param name="datetime"></param>
    /// <returns></returns>
    public static string ToSimpleString(this DateTime datetime)
    {
        return datetime.ToString("yyyy-MM-dd HH:mm:ss");
    }
    /// <summary>
    /// 将一个 DateTime 格式化为时间字符串
    /// </summary>
    /// <param name="datetime"></param>
    /// <returns></returns>
    public static string ToFileNameString(this DateTime datetime)
    {
        return datetime.ToString("yyyyMMddHHmmssffffff");
    }
    /// <summary>
    /// 将一个字符串转换为 DateTime
    /// </summary>
    /// <param name="datetime"></param>
    /// <returns></returns>
    public static DateTime? ToDateTime(this string str, string formatStr = "")
    {
        try
        {
            if (string.IsNullOrEmpty(formatStr))
            {
                return Convert.ToDateTime(str, SimpleDateTimeFormat);
            }
            DateTimeFormatInfo formatInfo = new DateTimeFormatInfo()
            {
                ShortDatePattern = formatStr
            };
            return Convert.ToDateTime(str, formatInfo);
        }
        catch (Exception)
        {
            return null;
        }
    }
}
 阅读次数  次