セルの書式設定 - 文字の配置

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text.RegularExpressions;

namespace OpenXMLSDK_Sample5 {
    class Program {
        static void Main(string[] args) {
            const string FILE = @"C:\Users\Public\Documents\openxmlsdk12.xlsx";
            const string SHEET_NAME = "Sheet1";

            using (SpreadsheetDocument excelDoc = SpreadsheetDocument.Open(FILE, true)) {
                WorkbookPart bookPart = excelDoc.WorkbookPart;

                int sheet_index = GetWorksheetIndex(bookPart, SHEET_NAME);
                WorksheetPart sheet_part = GetWorksheetPart(bookPart, sheet_index);

                SetCellAlignment(bookPart, GetCell(sheet_part, "A1"), HorizontalAlignmentValues.Left, VerticalAlignmentValues.Top);
                SetCellAlignment(bookPart, GetCell(sheet_part, "B1"), HorizontalAlignmentValues.Center, VerticalAlignmentValues.Top);
                SetCellAlignment(bookPart, GetCell(sheet_part, "C1"), HorizontalAlignmentValues.Right, VerticalAlignmentValues.Top);

                SetCellAlignment(bookPart, GetCell(sheet_part, "A2"), HorizontalAlignmentValues.Left, VerticalAlignmentValues.Center);
                SetCellAlignment(bookPart, GetCell(sheet_part, "B2"), HorizontalAlignmentValues.Center, VerticalAlignmentValues.Center);
                SetCellAlignment(bookPart, GetCell(sheet_part, "C2"), HorizontalAlignmentValues.Right, VerticalAlignmentValues.Center);

                SetCellAlignment(bookPart, GetCell(sheet_part, "A3"), HorizontalAlignmentValues.Left, VerticalAlignmentValues.Bottom);
                SetCellAlignment(bookPart, GetCell(sheet_part, "B3"), HorizontalAlignmentValues.Center, VerticalAlignmentValues.Bottom);
                SetCellAlignment(bookPart, GetCell(sheet_part, "C3"), HorizontalAlignmentValues.Right, VerticalAlignmentValues.Bottom);

                //保存
                sheet_part.Worksheet.Save();
            }

            //ファイルを開く
            System.Diagnostics.Process.Start(FILE);
        }

        private static void SetCellAlignment(WorkbookPart bookPart, Cell cell, HorizontalAlignmentValues h_align, VerticalAlignmentValues v_align) {
            if(cell == null || bookPart == null) {
                return;
            }

            IEnumerable<CellFormat> cell_formats = bookPart.WorkbookStylesPart.Stylesheet.CellFormats.Elements<CellFormat>();
            if (cell.StyleIndex != null) {
                CellFormat current_xf = cell_formats.ElementAt(Convert.ToInt32(cell.StyleIndex.Value));

                //同じ属性をもち、かつ指定されたalignと同じ設定をもつCellFormatを探す
                CellFormat format_to = cell_formats.FirstOrDefault(
                    r => r.GetAttributes().SequenceEqual(current_xf.GetAttributes())
                    && r.Alignment != null
                    && r.Alignment.Horizontal != null && r.Alignment.Horizontal.Value == h_align
                    && r.Alignment.Vertical != null && r.Alignment.Vertical.Value == v_align);
                if (format_to == null) {
                    format_to = current_xf.Clone() as CellFormat;
                    if (format_to.Alignment == null) {
                        format_to.Alignment = new Alignment();
                    }
                    if(format_to.Alignment.Horizontal == null) {
                        format_to.Alignment.Horizontal = new EnumValue<HorizontalAlignmentValues>();
                    }
                    format_to.Alignment.Horizontal.Value = h_align;

                    if (format_to.Alignment.Vertical == null) {
                        format_to.Alignment.Vertical = new EnumValue<VerticalAlignmentValues>();
                    }
                    format_to.Alignment.Vertical.Value = v_align;
                    
                    bookPart.WorkbookStylesPart.Stylesheet.CellFormats.Append(format_to);
                    bookPart.WorkbookStylesPart.Stylesheet.Save();
                    cell.StyleIndex = new UInt32Value((uint)cell_formats.Count() - 1);
                }else {
                    cell.StyleIndex = new UInt32Value((uint)(cell_formats.ToList().IndexOf(format_to)));
                }
            } else {
                CellFormat format_to = new CellFormat();
                format_to.Alignment = new Alignment();

                format_to.Alignment.Horizontal = new EnumValue<HorizontalAlignmentValues>();
                format_to.Alignment.Horizontal.Value = h_align;

                format_to.Alignment.Vertical = new EnumValue<VerticalAlignmentValues>();                
                format_to.Alignment.Vertical.Value = v_align;

                bookPart.WorkbookStylesPart.Stylesheet.CellFormats.Append(format_to);
                bookPart.WorkbookStylesPart.Stylesheet.Save();
                cell.StyleIndex = new UInt32Value((uint)cell_formats.Count() - 1);
            }
        }
        // 以下省略
    }
}