关闭

C# Epplus 创建Excel表格

标签: c#excel 2007
373人阅读 评论(0) 收藏 举报
分类:

EPPlus 可以创建 Excel 2007/2010 表格,官网地址:http://epplus.codeplex.com
支持的功能:
  • Cell Ranges
  • Cell styling (Border, Color, Fill, Font, Number, Alignments)
  • Charts
  • Pictures
  • Shapes
  • Comments
  • Tables
  • Protection
  • Encryption
  • Pivot tables
  • Data validation
  • Conditional formatting
  • VBA
  • Formula calculation
  • Many more...

这里不详细介绍如何使用,只记录下目前所使用的一些常用方法:
1
using System.IO;
2
using OfficeOpenXml;
3
using System.Drawing;
4
using OfficeOpenXml.Style;
5
6
/// <summary>
7
/// AssetBundle报告
8
/// </summary>
9
public class AssetBundleReporter
10
{
11
    private const string kSheetNameAbAssets = "资源使用情况";
12
    private const string kSheetNameAbDetail = "每个所包含的具体资源";
13
14
    public static void PrintReporter()
15
    {
16
        var newFile = new FileInfo("f:/test.xlsx");
17
        if (newFile.Exists)
18
        {
19
            newFile.Delete();
20
        }
21
22
        using (var package = new ExcelPackage(newFile))
23
        {
24
            CreateWorksheetAbAssets(package.Workbook.Worksheets.Add(kSheetNameAbAssets));
25
            CreateWorksheetAbDetail(package.Workbook.Worksheets.Add(kSheetNameAbDetail));
26
27
            FillWorksheetAbAssets(package.Workbook.Worksheets[1]);
28
            package.Save();
29
        }
30
    }
31
32
    private static void CreateWorksheetAbAssets(ExcelWorksheet ws)
33
    {
34
        // 标签颜色
35
        ws.TabColor = ColorTranslator.FromHtml("#32b1fa");
36
37
        // 全体颜色
38
        ws.Cells.Style.Font.Color.SetColor(ColorTranslator.FromHtml("#3d4d65"));
39
        {
40
            // 边框样式
41
            var border = ws.Cells.Style.Border;
42
            border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style
43
                = ExcelBorderStyle.Thin;
44
45
            // 边框颜色
46
            var clr = ColorTranslator.FromHtml("#cad7e2");
47
            border.Bottom.Color.SetColor(clr);
48
            border.Top.Color.SetColor(clr);
49
            border.Left.Color.SetColor(clr);
50
            border.Right.Color.SetColor(clr);
51
        }
52
53
        // 标题
54
        ws.Cells[1, 1].Value = "AssetBundle 文件的资源使用情况";
55
        using (var range = ws.Cells[1, 1, 1, 5])
56
        {
57
            range.Merge = true;
58
            range.Style.Font.Bold = true;
59
            range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
60
            range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
61
        }
62
63
        // 列头
64
        ws.Cells[2, 1].Value = "AssetBundle 名称";
65
        ws.Cells[2, 2].Value = "Mesh";
66
        ws.Cells[2, 3].Value = "Material";
67
        ws.Cells[2, 4].Value = "Texture2D";
68
        ws.Cells[2, 5].Value = "Shader";
69
70
        using (var range = ws.Cells[2, 1, 2, 5])
71
        {
72
            // 字体样式
73
            range.Style.Font.Bold = true;
74
75
            // 背景颜色
76
            range.Style.Fill.PatternType = ExcelFillStyle.Solid;
77
            range.Style.Fill.BackgroundColor.SetColor(ColorTranslator.FromHtml("#F2F5FA"));
78
79
            // 开启自动筛选
80
            range.AutoFilter = true;
81
        }
82
83
        // 列宽
84
        ws.Column(1).Width = 110;
85
        ws.Column(2).Width = 15;
86
        ws.Column(3).Width = 15;
87
        ws.Column(4).Width = 15;
88
        ws.Column(5).Width = 15;
89
90
        // 冻结前两行
91
        ws.View.FreezePanes(3, 1);
92
    }
93
94
    private static void FillWorksheetAbAssets(ExcelWorksheet ws)
95
    {
96
        // 测试数据
97
        ws.Cells[3, 1].Value = "SubTerrainObjs_1_1.assetbundle";
98
        ws.Cells[3, 1].Hyperlink = new ExcelHyperLink(kSheetNameAbDetail + "!A3", "SubTerrainObjs_1_1.assetbundle");
99
        ws.Cells[3, 2].Value = 45;
100
        ws.Cells[3, 3].Value = 150;
101
102
        ws.Cells[4, 1].Value = "Terrain_Data_1_8.assetbundle";
103
        ws.Cells[4, 1].Hyperlink = new ExcelHyperLink(kSheetNameAbDetail + "!A300", "Terrain_Data_1_8.assetbundle");
104
        ws.Cells[4, 2].Value = 38;
105
        ws.Cells[4, 3].Value = 130;
106
107
        ws.Cells[5, 1].Value = "Terrain_Data_3_3.assetbundle";
108
        ws.Cells[5, 2].Value = 22;
109
        ws.Cells[5, 3].Value = 200;
110
    }
111
112
    private static void CreateWorksheetAbDepend(ExcelWorksheet ws)
113
    {
114
        
115
    }
116
117
    private static void CreateWorksheetAbDetail(ExcelWorksheet ws)
118
    {
119
        // 测试数据
120
        ws.Cells[3, 1].Value = "SubTerrainObjs_1_1.assetbundle";
121
        ws.Cells[300, 1].Value = "Terrain_Data_1_8.assetbundle";
122
        ws.Cells[3000, 3].Value = "Terrain_Data_3_3.assetbundle";
123
    }
124
}
生成的 Excel 效果如下:

 

更好的参考文章:



0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:3388939次
    • 积分:34498
    • 等级:
    • 排名:第137名
    • 原创:395篇
    • 转载:88篇
    • 译文:5篇
    • 评论:3381条
    个人说明
    联系方式:
    最新评论