C# EPPlus帮助类(EPPlusExcelHelper)

2021-06-09 14:01

阅读:551

标签:names   rap   转换   system   get   没有   设置   pac   sys   

public class EPPlusExcelHelper : IDisposable
{
    public ExcelPackage ExcelPackage { get; private set; }
    private Stream fs;

    public EPPlusExcelHelper(string filePath)
    {
        if (File.Exists(filePath))
        {
            var file = new FileInfo(filePath);
            ExcelPackage = new ExcelPackage(file);
        }
        else
        {
            fs = File.Create(filePath);
            ExcelPackage = new ExcelPackage(fs);

        }
    }

    /// 
    /// 获取sheet,没有则创建
    /// 
    /// 
    /// 
    public ExcelWorksheet GetOrAddSheet(string sheetName)
    {
        ExcelWorksheet ws = ExcelPackage.Workbook.Worksheets.FirstOrDefault(i => i.Name == sheetName);
        if (ws == null)
        {
            ws = ExcelPackage.Workbook.Worksheets.Add(sheetName);
        }
        return ws;
    }

    /// 
    /// DataTable数据导出到Excel(xlsx)
    /// 
    /// ExcelPackage
    /// 数据源
    public void AppendSheetToWorkBook(DataTable sourceTable)
    {
        AppendSheetToWorkBook(sourceTable, true);
    }

    /// 
    /// DataTable数据导出到Excel(xlsx)
    /// 
    /// ExcelPackage
    /// 数据源
    /// 是否删除同名的sheet
    public void AppendSheetToWorkBook(DataTable sourceTable, bool isDeleteSameNameSheet)
    {
        //创建worksheet
        ExcelWorksheet ws = AddSheet(sourceTable.TableName, isDeleteSameNameSheet);
        //从单元格A1开始,将数据表加载到工作表中。第1行输出列名
        ws.Cells["A1"].LoadFromDataTable(sourceTable, true);
        //格式化Row
        FromatRow(sourceTable.Rows.Count, sourceTable.Columns.Count, ws);
    }


    /// 
    /// 删除指定的sheet
    /// 
    /// 
    /// 
    public void DeleteSheet(string sheetName)
    {
        var sheet = ExcelPackage.Workbook.Worksheets.FirstOrDefault(i => i.Name == sheetName);
        if (sheet != null)
        {
            ExcelPackage.Workbook.Worksheets.Delete(sheet);
        }
    }

    /// 
    /// 导出列表到excel,已存在同名sheet将删除已存在的
    /// 
    /// 
    /// 
    /// 数据源
    /// sheet名称
    public void AppendSheetToWorkBook(IEnumerable list, string sheetName)
    {
        AppendSheetToWorkBook(list, sheetName, true);
    }

    /// 
    /// 导出列表到excel,已存在同名sheet将删除已存在的
    /// 
    /// 
    /// 
    /// 数据源
    /// sheet名称
    /// 是否删除已存在的同名sheet,false时将重命名导出的sheet
    public void AppendSheetToWorkBook(IEnumerable list, string sheetName, bool isDeleteSameNameSheet)
    {
        ExcelWorksheet ws = AddSheet(sheetName, isDeleteSameNameSheet);
        ws.Cells["A1"].LoadFromCollection(list, true);
    }

    /// 
    /// 添加文字图片
    /// 
    /// 
    /// 要转换成图片的文字
    public void AddPicture(string sheetName, string msg)
    {
        Bitmap img = GetPictureString(msg);

        var sheet = GetOrAddSheet(sheetName);
        var picName = "92FF5CFE-2C1D-4A6B-92C6-661BDB9ED016";
        var pic = sheet.Drawings.FirstOrDefault(i => i.Name == picName);
        if (pic != null)
        {
            sheet.Drawings.Remove(pic);
        }
        pic = sheet.Drawings.AddPicture(picName, img);

        pic.SetPosition(3, 0, 6, 0);
    }

    /// 
    /// 文字绘制图片
    /// 
    /// 
    /// 
    private static Bitmap GetPictureString(string msg)
    {
        var msgs = msg.Split(new string[] { System.Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries);
        var maxLenght = msgs.Max(i => i.Length);
        var rowCount = msgs.Count();
        var rowHeight = 23;
        var fontWidth = 17;
        var img = new Bitmap(maxLenght * fontWidth, rowCount * rowHeight);
        using (Graphics g = Graphics.FromImage(img))
        {
            g.Clear(Color.White);
            Font font = new Font("Arial", 12, (FontStyle.Bold));
            LinearGradientBrush brush = new LinearGradientBrush(new Rectangle(0, 0, img.Width, img.Height), Color.Blue, Color.DarkRed, 1.2f, true);

            for (int i = 0; i 
    /// List转DataTable
    /// 
    /// 
    /// 
    /// 
    public DataTable ListToDataTable(IEnumerable data)
    {
        PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
        DataTable dataTable = new DataTable();
        for (int i = 0; i 
    /// 插入行
    /// 
    /// 
    /// 行类容,一个单元格一个对象
    /// 插入位置,起始位置为1
    public void InsertValues(string sheetName, List values, int rowIndex)
    {
        var sheet = GetOrAddSheet(sheetName);
        sheet.InsertRow(rowIndex, 1);
        int i = 1;
        foreach (var item in values)
        {
            sheet.SetValue(rowIndex, i, item);
            i++;
        }
    }

    /// 
    /// 保存修改
    /// 
    public void Save()
    {
        ExcelPackage.Save();
    }

    /// 
    /// 添加Sheet到ExcelPackage
    /// 
    /// ExcelPackage
    /// sheet名称
    /// 如果存在同名的sheet是否删除
    /// 
    private ExcelWorksheet AddSheet(string sheetName, bool isDeleteSameNameSheet)
    {
        if (isDeleteSameNameSheet)
        {
            DeleteSheet(sheetName);
        }
        else
        {
            while (ExcelPackage.Workbook.Worksheets.Any(i => i.Name == sheetName))
            {
                sheetName = sheetName + "(1)";
            }
        }

        ExcelWorksheet ws = ExcelPackage.Workbook.Worksheets.Add(sheetName);
        return ws;
    }

    private void FromatRow(int rowCount, int colCount, ExcelWorksheet ws)
    {
        ExcelBorderStyle borderStyle = ExcelBorderStyle.Thin;
        Color borderColor = Color.FromArgb(155, 155, 155);

        using (ExcelRange rng = ws.Cells[1, 1, rowCount + 1, colCount])
        {
            rng.Style.Font.Name = "宋体";
            rng.Style.Font.Size = 10;
            rng.Style.Fill.PatternType = ExcelFillStyle.Solid;  //设置图案的背景为Solid
            rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, 255, 255));

            rng.Style.Border.Top.Style = borderStyle;
            rng.Style.Border.Top.Color.SetColor(borderColor);

            rng.Style.Border.Bottom.Style = borderStyle;
            rng.Style.Border.Bottom.Color.SetColor(borderColor);

            rng.Style.Border.Right.Style = borderStyle;
            rng.Style.Border.Right.Color.SetColor(borderColor);
        }

        // 格式化标题行
        using (ExcelRange rng = ws.Cells[1, 1, 1, colCount])
        {
            rng.Style.Font.Bold = true;
            rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(234, 241, 246));
            rng.Style.Font.Color.SetColor(Color.FromArgb(51, 51, 51));
        }
    }

    public void Dispose()
    {
        ExcelPackage.Dispose();
        if (fs != null)
        {
            fs.Dispose();
            fs.Close();
        }
    }
}

C# EPPlus帮助类(EPPlusExcelHelper)

标签:names   rap   转换   system   get   没有   设置   pac   sys   

原文地址:https://www.cnblogs.com/zhaoshujie/p/14864683.html


评论


亲,登录后才可以留言!