-
Notifications
You must be signed in to change notification settings - Fork 0
/
YangpaExcelManager.cs
178 lines (168 loc) · 6.36 KB
/
YangpaExcelManager.cs
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
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using System.Windows.Media;
using System.IO;
namespace YangpaH
{
class YangpaExcelManager
{
//indexer for person with same name
static int snc = 1;
static string fileName = Path.Combine(
Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location), "output.xlsx");
private static Application application;
public static string SaveExcelDatas(List<SInstance> instances, List<string> students)
{
try
{
fileName = fileName.Replace("output.x", "output." + ValidateFilepath(instances[0].Class) + ".x");
Dictionary<string, int> sdic = new Dictionary<string, int>();
try
{
foreach (string s in students)
{
sdic.Add(s, 0);
}
}
catch (ArgumentException)
{
return YangpaConstants.MSG_BUG_NO_DUP_NAME;
}
foreach (SInstance inst in instances)
{
for (int i = 0; i < inst.JoMember.Length; i++)
{
int score = Int32.Parse(inst.JoScoreToActualScore(i));
foreach (string s in inst.JoMember[i])
{
sdic[s] += score;
}
}
}
/*string block = "";
foreach (string s in students)
{
block += s + ":" + sdic[s] + "\n";
}*/
Workbook workbook = null;
Worksheet worksheet = null;
try
{
application = new Application();
workbook = application.Workbooks.Add();
worksheet = workbook.Worksheets[1] as
Microsoft.Office.Interop.Excel.Worksheet;
for (int i = 1; i < 32; i++)
{
if ((i - 1) % 5 == 0)
worksheet.Cells[1, i + 1] = i - 1;
worksheet.Cells[1, i].Interior.Color = ColorTranslator.ToOle(Color.FromRgb(34, 118, 4));
worksheet.Cells[1, i].Font.Color = ColorTranslator.ToOle(Color.FromRgb(255, 255, 255));
}
for (int i = 0; i < sdic.Count; i++)
{
worksheet.Cells[(i + 2), 1] = students[i];
worksheet.Cells[(i + 2), 2] = sdic[students[i]] + "점";
if (i % 2 == 1)
{
//홀짝 구분해서 구별 쉽게 명암 넣어주기
Range name = (Range)worksheet.Cells[(i + 2), 1];
Range score = (Range)worksheet.Cells[(i + 2), 2];
var gray = ColorTranslator.ToOle(Color.FromRgb(230, 230, 230));
name.Interior.Color = gray;
score.Interior.Color = gray;
}
for (int j = 0; j < sdic[students[i]]; j++)
{
Range c = (Range)worksheet.Cells[(i + 2), (j + 3)];
c.Interior.Color = ColorTranslator.ToOle(i % 2 == 1 ? Color.FromRgb(230, 230, 0) : Color.FromRgb(255, 255, 0));
}
}
// Save.
workbook.SaveAs(fileName);
workbook.Close();
}
catch (COMException e)
{
return e.Message;
}
catch (NullReferenceException e)
{
return e.Message;
}
catch (KeyNotFoundException e)
{
return YangpaConstants.MSG_DB_CLS_MISMATCH;
}
finally
{
// Clean up
ReleaseExcelObject(workbook);
ReleaseExcelObject(worksheet);
ReleaseExcelObject(application);
}
return YangpaConstants.MSG_EXC_EXP_SUCCESS;
}
catch (Exception ee)
{
return ee.ToString();
}
}
private static string ValidateFilepath(string p)
{
return p.Replace(":", "").Replace("\\", "").Replace("<", "").Replace(">", "").Replace("/", "").Replace("?", "").Replace("|", "").Replace("*", "");
}
private static void ReleaseExcelObject(object obj)
{
try
{
if (obj != null)
{
Marshal.ReleaseComObject(obj);
obj = null;
}
}
catch (Exception ex)
{
obj = null;
throw ex;
}
finally
{
GC.Collect();
}
}
}
static class ColorTranslator
{
const int RedShift = 0;
const int GreenShift = 8;
const int BlueShift = 16;
/// <summary>
/// Translates an Ole color value to a System.Media.Color for WPF usage
/// </summary>
/// <param name="oleColor">Ole int32 color value</param>
/// <returns>System.Media.Color color value</returns>
public static Color FromOle(this int oleColor)
{
return Color.FromRgb(
(byte)((oleColor >> RedShift) & 0xFF),
(byte)((oleColor >> GreenShift) & 0xFF),
(byte)((oleColor >> BlueShift) & 0xFF)
);
}
/// <summary>
/// Translates the specified System.Media.Color to an Ole color.
/// </summary>
/// <param name="wpfColor">System.Media.Color source value</param>
/// <returns>Ole int32 color value</returns>
public static int ToOle(Color wpfColor)
{
return wpfColor.R << RedShift | wpfColor.G << GreenShift | wpfColor.B << BlueShift;
}
}
}