Stay hungry,Stay foolish!

0%

C# 将多个DataTable导出到Excel的一个Sheet中

最近接到一个需求,需要将多个DataTable导出到一个Excel的一个Sheet中,从上往下依次打印,表和表之间留一空行,还需要合并单元格和给表头填充颜色。

网上搜索一番之后,决定使用微软官方的OpenXml插件。

一、在NuGet中添加OpenXml

点击你的项目右键 -> “管理NuGet程序包”。

202007172135572020-7-17-21-35-58

然后点击作者为“Microsoft”的那个下载即可。

202007172136312020-7-17-21-36-32

二、代码示例

需求多,业务繁忙,来不及解释了,先贴上代码,后期有时间再深入研究一波。

1
/// <summary>
2
/// 将多个datatable导入到一个excel的一个sheet中
3
/// </summary>
4
/// <param name="tables"></param>
5
/// <returns></returns>
6
public static void CreatMultiDataTablesToOneSheetData(string excelFilePath, DataTable[] tables, string sheetName, string tableNameRgb = "B0E0E6")
7
{
8
    // 创建文件
9
    SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(excelFilePath, SpreadsheetDocumentType.Workbook);
10
    WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
11
    workbookpart.Workbook = new Workbook();
12
    WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
13
    SheetData sheetData = new SheetData();
14
15
    #region 添加样式
16
    var stylesPart = spreadsheetDocument.WorkbookPart.AddNewPart<WorkbookStylesPart>();
17
    stylesPart.Stylesheet = new Stylesheet();
18
    //定义字体集  (先定义字体集,然后再把字体加入到字体集中)
19
    stylesPart.Stylesheet.Fonts = new Fonts()
20
    {
21
        Count = (UInt32Value)3U // 表名,列名,内容
22
    };
23
    //定义字体
24
    Font tableTitleFont = new Font(
25
        new FontSize() { Val = 14D },
26
        new FontName() { Val = "Calibri" },
27
        new FontFamily() { Val = 2 },
28
        new FontScheme() { Val = FontSchemeValues.Major }
29
        );
30
    stylesPart.Stylesheet.Fonts.Append(tableTitleFont);
31
    Font columnFont = new Font(
32
        new FontSize() { Val = 12D },
33
        new FontName() { Val = "Calibri" },
34
        new FontFamily() { Val = 2 },
35
        new FontScheme() { Val = FontSchemeValues.Major }
36
        );
37
    stylesPart.Stylesheet.Fonts.Append(columnFont);
38
    Font contentFont = new Font(
39
        new FontSize() { Val = 11D },
40
        new FontName() { Val = "Calibri" },
41
        new FontFamily() { Val = 2 },
42
        new FontScheme() { Val = FontSchemeValues.Major }
43
        );
44
    stylesPart.Stylesheet.Fonts.Append(contentFont);
45
    // 边界
46
    stylesPart.Stylesheet.Borders = new Borders();
47
    stylesPart.Stylesheet.Borders.Count = 1;
48
    stylesPart.Stylesheet.Borders.AppendChild(new Border());
49
    // 填充
50
    stylesPart.Stylesheet.Fills = new Fills();
51
    var fillTableName = new PatternFill() { PatternType = PatternValues.Solid };
52
    fillTableName.ForegroundColor = new ForegroundColor { Rgb = HexBinaryValue.FromString(tableNameRgb) };
53
    var fillColumn = new PatternFill() { PatternType = PatternValues.Solid };
54
    fillColumn.ForegroundColor = new ForegroundColor { Rgb = HexBinaryValue.FromString(tableNameRgb) };
55
    var fillDefault = new PatternFill() { PatternType = PatternValues.Solid };
56
    fillDefault.ForegroundColor = new ForegroundColor { Rgb = HexBinaryValue.FromString(tableNameRgb) };
57
    stylesPart.Stylesheet.Fills.AppendChild(new Fill { PatternFill = fillTableName });
58
    stylesPart.Stylesheet.Fills.AppendChild(new Fill { PatternFill = fillColumn });
59
    stylesPart.Stylesheet.Fills.AppendChild(new Fill { PatternFill = fillDefault });
60
    stylesPart.Stylesheet.Fills.Count = 3;
61
    stylesPart.Stylesheet.CellStyleFormats = new CellStyleFormats();
62
    stylesPart.Stylesheet.CellStyleFormats.Count = 1;
63
    stylesPart.Stylesheet.CellStyleFormats.AppendChild(new CellFormat());
64
    // 定义格式组合
65
    stylesPart.Stylesheet.CellFormats = new CellFormats();
66
    stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat());    // 必须要加这个,否则打不开
67
    stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat { FontId = 0, BorderId = 0, FillId = 2, ApplyFill = true }).AppendChild(new Alignment { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center });
68
    stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat { FontId = 1, BorderId = 0, FillId = 0, ApplyFill = true }).AppendChild(new Alignment { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center });
69
    stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat { FontId = 2, BorderId = 0, FillId = 0, ApplyFill = true }).AppendChild(new Alignment { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center });
70
71
    stylesPart.Stylesheet.CellFormats.Count = 4;   // 4种组合
72
    stylesPart.Stylesheet.Save();
73
    #endregion
74
    List<string> mergePosList = new List<string>();
75
    int rowIndex = 1;   // 计算标题在哪一行
76
    for (int i = 0; i < tables.Count(); i++)
77
    {
78
        mergePosList.Add(GetColumnName(0) + rowIndex + ":" + GetColumnName(tables[i].Columns.Count - 1) + rowIndex);
79
        // 创建表明行
80
        Row tableNameRow = new Row();
81
        for (int tr = 0; tr < tables[i].Columns.Count; tr++)
82
        {
83
            Cell dataCell = new Cell();
84
            dataCell.CellValue = (tr == 0 ? new CellValue(tables[i].TableName) : new CellValue(""));
85
            dataCell.DataType = CellValues.String;
86
            dataCell.StyleIndex = 1;
87
            tableNameRow.AppendChild(dataCell);
88
        }
89
        sheetData.AppendChild(tableNameRow);
90
        // 创建列名行
91
        Row headLineRow = new Row();
92
        for (int hr = 0; hr < tables[i].Columns.Count; hr++)
93
        {
94
            Cell dataCell = new Cell();
95
            dataCell.CellValue = new CellValue(tables[i].Columns[hr].ToString());
96
            dataCell.DataType = CellValues.String;
97
            dataCell.StyleIndex = 2;
98
            headLineRow.AppendChild(dataCell);
99
        }
100
        sheetData.AppendChild(headLineRow);
101
        // 创建表格内容行
102
        for (int r = 0; r < tables[i].Rows.Count; r++)
103
        {
104
            Row row = new Row();
105
            for (int c = 0; c < tables[i].Columns.Count; c++)
106
            {
107
                Cell dataCell = new Cell();
108
                dataCell.CellValue = new CellValue(tables[i].Rows[r][c].ToString());
109
                dataCell.DataType = CellValues.String;
110
                dataCell.StyleIndex = 3;
111
                row.AppendChild(dataCell);
112
            }
113
            sheetData.Append(row);
114
        }
115
        sheetData.Append(new Row());
116
        rowIndex += tables[i].Rows.Count + 3;   // 总数据行+表名行和列名行和一个空行
117
    }
118
    worksheetPart.Worksheet = new Worksheet(sheetData);
119
    // 合并单元格
120
    MergeCells mergeCells = new MergeCells();
121
    for (int i = 0; i < mergePosList.Count; i++)
122
    {
123
        mergeCells.Append(new MergeCell() { Reference = new StringValue(mergePosList[i]) });
124
    }
125
    worksheetPart.Worksheet.InsertAfter(mergeCells, worksheetPart.Worksheet.Elements<SheetData>().First());
126
    Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild(new Sheets());
127
    Sheet sheet = new Sheet()
128
    {
129
        Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
130
        SheetId = new UInt32Value((uint)(1)),
131
        Name = sheetName
132
    };
133
    sheets.Append(sheet);
134
    workbookpart.Workbook.Save();
135
    spreadsheetDocument.Close();
136
}
137
/// <summary>
138
/// 获取excel对应的位置名
139
/// </summary>
140
/// <param name="index">从0开始</param>
141
/// <returns></returns>
142
public static string GetColumnName(int index)
143
{
144
    string name = "";
145
    char[] columnNames = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".ToCharArray();
146
    int num = index;
147
    do
148
    {
149
        int i = num % 26;
150
        name = columnNames[i] + name;
151
        num = num / 26 - 1;
152
    } while (num > -1);
153
    if (string.IsNullOrEmpty(name))
154
        name = "A";
155
    return name;
156
}

三、准备测试代码

准备两张数据表测试一下,代码如下:

1
DataTable dt1 = new DataTable("产品型号");
2
dt1.Columns.Add("Type");
3
dt1.Columns.Add("Size");
4
dt1.Rows.Add(new string[] { "SKI-1", "8mm" });
5
dt1.Rows.Add(new string[] { "SKI-1", "8mm" });
6
dt1.Rows.Add(new string[] { "SKI-1", "8mm" });
7
dt1.Rows.Add(new string[] { "SKI-2", "9mm" });
8
dt1.Rows.Add(new string[] { "SKI-3", "10mm" });
9
DataTable dt2 = new DataTable("角色信息");
10
dt2.Columns.Add("Name");
11
dt2.Columns.Add("Gender");
12
dt2.Columns.Add("Province");
13
dt2.Rows.Add(new string[] { "张三", "男", "广东省" });
14
dt2.Rows.Add(new string[] { "李四", "女", "湖南省" });
15
dt2.Rows.Add(new string[] { "黄二", "女", "福建省" });
16
dt2.Rows.Add(new string[] { "王五", "男", "广西省" });

四、测试结果

调用上面写的方法,即可导出excel。

1
CreatMultiDataTablesToOneSheetData(@"D:\导出Testl.xlsx", new DataTable[] { dt1,dt2 }, "导出测试");

结果如下图:

202007172136562020-7-17-21-36-56