ExcelファイルをHTMLに変換します。
| 場所 | 住所 | 最寄り駅 | 最寄り駅情報 | 徒歩 |
|---|---|---|---|---|
| 国会議事堂 | 東京都千代田区永田町１－７－１ | 国会議事堂前駅 | 東京メトロ丸ノ内線/東京メトロ千代田線 | 徒歩 4分 |
| 東京都庁 | 東京都新宿区西新宿二丁目 | 都庁前駅 | 都営大江戸線 | 徒歩 2分 |
| 国会図書館 | 東京都千代田区永田町1丁目10-1 | 永田町駅 | 東京メトロ南北線/東京メトロ半蔵門線/東京メトロ有楽町線 | 徒歩 3分 |
|
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 |
<table border="1" width="500" cellspacing="0" cellpadding="5" bordercolor="#000000"> <tr> <th style ="color: #000000; background-color:#ff8040">場所</th> <th style ="color: #000000; background-color:#ff8040">住所</th> <th style ="color: #000000; background-color:#ff8040">最寄り駅</th> <th style ="color: #000000; background-color:#ff8040">最寄り駅情報</th> <th style ="color: #000000; background-color:#ff8040">徒歩</th> </tr> <tr> <td style ="color: #000000; background-color:#ffff00">国会議事堂</td> <td style ="color: #000000; background-color:#ffffff">東京都千代田区永田町１－７－１</td> <td style ="color: #000000; background-color:#ffffff">国会議事堂前駅</td> <td style ="color: #000000; background-color:#ffffff">東京メトロ丸ノ内線/東京メトロ千代田線</td> <td style ="color: #000000; background-color:#ffffff">徒歩 4分</td> </tr> <tr> <td style ="color: #000000; background-color:#ffff00">東京都庁</td> <td style ="color: #000000; background-color:#ffffff">東京都新宿区西新宿二丁目</td> <td style ="color: #000000; background-color:#ffffff">都庁前駅</td> <td style ="color: #000000; background-color:#ffffff">都営大江戸線</td> <td style ="color: #000000; background-color:#ffffff">徒歩 2分</td> </tr> <tr> <td style ="color: #000000; background-color:#ffff00">国会図書館</td> <td style ="color: #000000; background-color:#ffffff">東京都千代田区永田町1丁目10-1</td> <td style ="color: #000000; background-color:#ffffff">永田町駅</td> <td style ="color: #000000; background-color:#ffffff">東京メトロ南北線/東京メトロ半蔵門線/東京メトロ有楽町線</td> <td style ="color: #000000; background-color:#ffffff">徒歩 3分</td> </tr> </table> |
ClodedXMLを使って要素を取り出し、これをHTMLに書き出せばできそうです。
ClosedXMLを使うのでNugetでインストールしておきましょう。
表のヘッダー（一行目）と各カラムの１列目、それ以外の部分の前景色と背景色をダイアログで設定することができるようにします。
|
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 |
public partial class Form1 : Form { Color FirstRowForeColor = Color.Black; Color FirstRowBackColor = Color.White; Color FirstColumForeColor = Color.Black; Color FirstColumBackColor = Color.White; Color OtherForeColor = Color.Black; Color OtherBackColor = Color.White; Color RuledlineColor = Color.Black; int RuledlineWidth = 1; private void button2_Click(object sender, EventArgs e) { Form2 form2 = new Form2( FirstRowForeColor, FirstRowBackColor, FirstColumForeColor, FirstColumBackColor, OtherForeColor, OtherBackColor, RuledlineColor, RuledlineWidth); if (form2.ShowDialog() == DialogResult.OK) { FirstRowForeColor = form2.FirstRowForeColor; FirstRowBackColor = form2.FirstRowBackColor; FirstColumForeColor = form2.FirstColumForeColor; FirstColumBackColor = form2.FirstColumBackColor; OtherForeColor = form2.OtherForeColor; OtherBackColor = form2.OtherBackColor; RuledlineColor = form2.RuledlineColor; RuledlineWidth = form2.RuledlineWidth; } form2.Dispose(); } } |
|
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 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 |
public partial class Form2 : Form { public Color FirstRowForeColor = Color.Black; public Color FirstRowBackColor = Color.White; public Color FirstColumForeColor = Color.Black; public Color FirstColumBackColor = Color.White; public Color OtherForeColor = Color.Black; public Color OtherBackColor = Color.White; public Color RuledlineColor = Color.Black; public int RuledlineWidth = 1; public Form2() { InitializeComponent(); } public Form2(Color firstRowForeColor, Color firstRowBackColor, Color firstColumForeColor, Color firstColumBackColor, Color otherForeColor, Color otherBackColor, Color ruledlineColor, int ruledlineWidth) { InitializeComponent(); FirstRowForeColor = firstRowForeColor; FirstRowBackColor = firstRowBackColor; FirstColumForeColor = firstColumForeColor; FirstColumBackColor = firstColumBackColor; OtherForeColor = otherForeColor; OtherBackColor = otherBackColor; RuledlineColor = ruledlineColor; RuledlineWidth = ruledlineWidth; ButtonOK.DialogResult = DialogResult.OK; } protected override void OnLoad(EventArgs e) { LabelFirstRow.ForeColor = FirstRowForeColor; LabelFirstRow.BackColor = FirstRowBackColor; LabelFirstColum.ForeColor = FirstColumForeColor; LabelFirstColum.BackColor = FirstColumBackColor; LabelOther.ForeColor = OtherForeColor; LabelOther.BackColor = OtherBackColor; LabelRuledline.BackColor = RuledlineColor; numericUpDownRuledlineWidth.Value = RuledlineWidth; base.OnLoad(e); } private void ButtonFirstRowForeColor_Click(object sender, EventArgs e) { ColorDialog dialog = new ColorDialog(); dialog.Color = FirstRowForeColor; if (dialog.ShowDialog() == DialogResult.OK) { FirstRowForeColor = dialog.Color; LabelFirstRow.ForeColor = FirstRowForeColor; } dialog.Dispose(); } private void ButtonFirstRowBackColor_Click(object sender, EventArgs e) { ColorDialog dialog = new ColorDialog(); dialog.Color = FirstRowBackColor; if (dialog.ShowDialog() == DialogResult.OK) { FirstRowBackColor = dialog.Color; LabelFirstRow.BackColor = FirstRowBackColor; } dialog.Dispose(); } private void ButtonFirstColumForeColor_Click(object sender, EventArgs e) { ColorDialog dialog = new ColorDialog(); dialog.Color = FirstColumForeColor; if (dialog.ShowDialog() == DialogResult.OK) { FirstColumForeColor = dialog.Color; LabelFirstColum.ForeColor = FirstColumForeColor; } dialog.Dispose(); } private void ButtonFirstColumBackColor_Click(object sender, EventArgs e) { ColorDialog dialog = new ColorDialog(); dialog.Color = FirstColumBackColor; if (dialog.ShowDialog() == DialogResult.OK) { FirstColumBackColor = dialog.Color; LabelFirstColum.BackColor = FirstColumBackColor; } dialog.Dispose(); } private void ButtonOtherForeColor_Click(object sender, EventArgs e) { ColorDialog dialog = new ColorDialog(); dialog.Color = OtherForeColor; if (dialog.ShowDialog() == DialogResult.OK) { OtherForeColor = dialog.Color; LabelOther.ForeColor = OtherForeColor; } dialog.Dispose(); } private void ButtonOtherBackColor_Click(object sender, EventArgs e) { ColorDialog dialog = new ColorDialog(); dialog.Color = OtherBackColor; if (dialog.ShowDialog() == DialogResult.OK) { OtherBackColor = dialog.Color; LabelOther.BackColor = OtherBackColor; } dialog.Dispose(); } private void ButtonRuledlineColor_Click(object sender, EventArgs e) { ColorDialog dialog = new ColorDialog(); dialog.Color = RuledlineColor; if (dialog.ShowDialog() == DialogResult.OK) { RuledlineColor = dialog.Color; LabelRuledline.BackColor = RuledlineColor; } dialog.Dispose(); } private void numericUpDownRuledlineWidth_ValueChanged(object sender, EventArgs e) { RuledlineWidth = (int)numericUpDownRuledlineWidth.Value; } } |
ファイルが選択されたらtableタグが生成され、リッチテキストボックスに表示されます。
まずExcelファイルの行と列がどれだけあるか調べます。1行目のセルを右へひとつひとつしらべてなにも入力されていなければ列はそこまでであると判断します。行は1列目のセルを下へへひとつひとつしらべてなにも入力されていなければ行はそこまでであると判断します。
|
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 |
public partial class Form1 : Form { private void button1_Click(object sender, EventArgs e) { OpenFileDialog dialog = new OpenFileDialog(); dialog.Filter = "Excelファイル(*.xlsx)|*.xlsx"; if (dialog.ShowDialog() == DialogResult.OK) { richTextBox1.Text = GetHtmlFromExcel(dialog.FileName); } dialog.Dispose(); } string GetHtmlFromExcel(string excelPath) { string str = ""; using (var workbook = new XLWorkbook(excelPath)) { string ruledlineColorCode = GetColorCode(this.RuledlineColor); string ruledlineWidth = this.RuledlineWidth.ToString(); IXLWorksheet worksheet = workbook.Worksheet(1); str = String.Format("<table border=\"{0}\" width=\"500\" cellspacing=\"0\" cellpadding=\"5\" bordercolor=\"{1}\">\n", ruledlineWidth, ruledlineColorCode); int rowCount = GetRowCount(worksheet); int columCount = GetColumCount(worksheet); str += GetTableHeader(worksheet, columCount); str += GetTableData(worksheet, rowCount, columCount); str += "</table>\n"; } return str; } } |
以下は行と列の数を調べるメソッドです。
|
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 |
public partial class Form1 : Form { int GetRowCount(IXLWorksheet worksheet) { int i = 1; while (true) { var cell = worksheet.Cell(i, 1); string str = cell.GetValue<string>(); if (str == null || str == "") break; i++; } return i - 1; } int GetColumCount(IXLWorksheet worksheet) { int i = 1; while (true) { var cell = worksheet.Cell(1, i); string str = cell.GetValue<string>(); if (str == null || str == "") break; i++; } return i - 1; } } |
GetTableHeader(IXLWorksheet worksheet, int columCount)メソッドは1行目の要素のみ、GetTableData(IXLWorksheet worksheet, int rowCount, int colomCount)メソッドはそれより下の要素を調べてHTMLを生成するメソッドです。
|
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 |
public partial class Form1 : Form { string GetTableHeader(IXLWorksheet worksheet, int columCount) { string ret = "<tr>\n"; for(int i = 1; i<= columCount; i++) { var cell = worksheet.Cell(1, i); string str = cell.GetValue<string>(); string frontColorCode = GetColorCode(this.FirstRowForeColor); string backColorCode = GetColorCode(this.FirstRowBackColor); ret += String.Format("<th style =\"color: {0}; background-color:{1}\">{2}</th>\n", frontColorCode, backColorCode, str); } ret += "</tr>\n"; return ret; } string GetColorCode(Color color) { return String.Format("#{0}{1}{2}", ((int)color.R).ToString("x2"), ((int)color.G).ToString("x2"), ((int)color.B).ToString("x2")); } string GetTableData(IXLWorksheet worksheet, int rowCount, int colomCount) { string ret = ""; for(int row = 2; row<= rowCount; row++) { ret += "<tr>\n"; for (int colom = 1; colom <= colomCount; colom++) { var cell = worksheet.Cell(row, colom); string str = cell.GetValue<string>(); if (colom == 1) { string frontColorCode = GetColorCode(this.FirstColumForeColor); string backColorCode = GetColorCode(this.FirstColumBackColor); ret += String.Format("<td style =\"color: {0}; background-color:{1}\">{2}</td>\n", frontColorCode, backColorCode, str); } else { string frontColorCode = GetColorCode(this.OtherForeColor); string backColorCode = GetColorCode(this.OtherBackColor); ret += String.Format("<td style =\"color: {0}; background-color:{1}\">{2}</td>\n", frontColorCode, backColorCode, str); } } ret += "</tr>\n"; } return ret; } } |
GetColorCode(Color color)メソッドは色を #ff0000のような形で出力させるためのものです。
|
1 2 3 4 5 6 7 |
public partial class Form1 : Form { string GetColorCode(Color color) { return String.Format("#{0}{1}{2}", ((int)color.R).ToString("x2"), ((int)color.G).ToString("x2"), ((int)color.B).ToString("x2")); } } |
この方法だとセルが結合されている場合、うまくいきません。次回はセルが結合されている場合にも対応する方法を考えます。
