/
Form1.cs
388 lines (337 loc) · 19 KB
/
Form1.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
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
using DevExpress.Export.Xl;
using DevExpress.XtraExport.Csv;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Drawing;
using System.Globalization;
using System.IO;
using System.Windows.Forms;
namespace XLExportExample {
public partial class Form1 : Form {
List<SalesData> sales = SalesRepository.GetSalesData();
XlCellFormatting headerRowFormatting;
XlCellFormatting evenRowFormatting;
XlCellFormatting oddRowFormatting;
XlCellFormatting totalRowFormatting;
public Form1() {
InitializeComponent();
InitializeFormatting();
}
void InitializeFormatting() {
// Specify formatting settings for the even rows.
evenRowFormatting = new XlCellFormatting();
evenRowFormatting.Font = new XlFont();
evenRowFormatting.Font.Name = "Century Gothic";
evenRowFormatting.Font.SchemeStyle = XlFontSchemeStyles.None;
evenRowFormatting.Alignment = XlCellAlignment.FromHV(XlHorizontalAlignment.General, XlVerticalAlignment.Center);
// Specify formatting settings for the odd rows.
oddRowFormatting = new XlCellFormatting();
oddRowFormatting.CopyFrom(evenRowFormatting);
oddRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Light1, -0.15));
// Specify formatting settings for the header row.
headerRowFormatting = new XlCellFormatting();
headerRowFormatting.CopyFrom(evenRowFormatting);
headerRowFormatting.Font.Bold = true;
headerRowFormatting.Font.Color = XlColor.FromTheme(XlThemeColor.Light1, 0.0);
headerRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent1, 0.0));
headerRowFormatting.Border = new XlBorder();
headerRowFormatting.Border.TopColor = XlColor.FromTheme(XlThemeColor.Dark1, 0.0);
headerRowFormatting.Border.TopLineStyle = XlBorderLineStyle.Medium;
headerRowFormatting.Border.BottomColor = XlColor.FromTheme(XlThemeColor.Dark1, 0.0);
headerRowFormatting.Border.BottomLineStyle = XlBorderLineStyle.Medium;
// Specify formatting settings for the total row.
totalRowFormatting = new XlCellFormatting();
totalRowFormatting.CopyFrom(evenRowFormatting);
totalRowFormatting.Font.Bold = true;
}
// Export the document to XLSX format.
void btnExportToXLSX_Click(object sender, EventArgs e) {
string fileName = GetSaveFileName("Excel Workbook files(*.xlsx)|*.xlsx", "Document.xlsx");
if (string.IsNullOrEmpty(fileName))
return;
if (ExportToFile(fileName, XlDocumentFormat.Xlsx))
ShowFile(fileName);
}
// Export the document to XLS format.
void btnExportToXLS_Click(object sender, EventArgs e) {
string fileName = GetSaveFileName("Excel 97-2003 Workbook files(*.xls)|*.xls", "Document.xls");
if (string.IsNullOrEmpty(fileName))
return;
if (ExportToFile(fileName, XlDocumentFormat.Xls))
ShowFile(fileName);
}
// Export the document to CSV format.
void btnExportToCSV_Click(object sender, EventArgs e) {
string fileName = GetSaveFileName("CSV (Comma delimited files)(*.csv)|*.csv", "Document.csv");
if (string.IsNullOrEmpty(fileName))
return;
if (ExportToFile(fileName, XlDocumentFormat.Csv))
ShowFile(fileName);
}
string GetSaveFileName(string filter, string defaulName) {
saveFileDialog1.Filter = filter;
saveFileDialog1.FileName = defaulName;
if (saveFileDialog1.ShowDialog() != DialogResult.OK)
return null;
return saveFileDialog1.FileName;
}
void ShowFile(string fileName) {
if (!File.Exists(fileName))
return;
DialogResult dResult = MessageBox.Show(String.Format("Do you want to open the resulting file?", fileName),
this.Text, MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dResult == DialogResult.Yes)
Process.Start(fileName);
}
bool ExportToFile(string fileName, XlDocumentFormat documentFormat) {
try {
using (FileStream stream = new FileStream(fileName, FileMode.Create)) {
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
// Create a new document and begin to write it to the specified stream.
using (IXlDocument document = exporter.CreateDocument(stream)) {
// Generate the document content.
GenerateDocument(document);
}
}
return true;
}
catch (Exception ex) {
MessageBox.Show(ex.Message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
return false;
}
}
void GenerateDocument(IXlDocument document) {
// Specify the document culture.
document.Options.Culture = CultureInfo.CurrentCulture;
// Specify options for exporting the document in CSV format.
CsvDataAwareExporterOptions csvOptions = document.Options as CsvDataAwareExporterOptions;
if (csvOptions != null) {
csvOptions.WritePreamble = true;
csvOptions.UseCellNumberFormat = false;
csvOptions.NewlineAfterLastRow = true;
}
// Add a new worksheet to the document.
using (IXlSheet sheet = document.CreateSheet()) {
// Specify the worksheet name.
sheet.Name = "Annual Sales";
// Specify page settings.
sheet.PageSetup = new XlPageSetup();
// Scale the print area to fit to one page wide.
sheet.PageSetup.FitToPage = true;
sheet.PageSetup.FitToWidth = 1;
sheet.PageSetup.FitToHeight = 0;
// Generate worksheet columns.
GenerateColumns(sheet);
// Add the title to the documents exported to the XLSX and XLS formats.
if (document.Options.DocumentFormat != XlDocumentFormat.Csv)
GenerateTitle(sheet);
// Create the header row.
GenerateHeaderRow(sheet);
int firstDataRowIndex = sheet.CurrentRowIndex;
// Create the data rows.
for (int i = 0; i < sales.Count; i++)
GenerateDataRow(sheet, sales[i], (i + 1) == sales.Count);
// Create the total row.
GenerateTotalRow(sheet, firstDataRowIndex);
// Specify the data range to be printed.
sheet.PrintArea = sheet.DataRange;
// Create conditional formatting rules to be applied to worksheet data.
GenerateConditionalFormatting(sheet, firstDataRowIndex);
}
}
void GenerateColumns(IXlSheet sheet) {
XlNumberFormat numberFormat =@"#,##0,,""M""";
// Create the "State" column and set its width.
using (IXlColumn column = sheet.CreateColumn())
column.WidthInPixels = 140;
// Create the "Sales" column, adjust its width and set the specific number format for its cells.
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 140;
column.ApplyFormatting(numberFormat);
}
// Create the "Sales vs Target" column, adjust its width and format its cells as percentage values.
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 120;
column.ApplyFormatting(XlNumberFormat.Percentage2);
}
// Create the "Profit" column, adjust its width and set the specific number format for its cells.
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 140;
column.ApplyFormatting(numberFormat);
}
// Create the "Market Share" column, adjust its width and format its cells as percentage values.
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 120;
column.ApplyFormatting(XlNumberFormat.Percentage);
}
}
void GenerateTitle(IXlSheet sheet) {
// Specify formatting settings for the document title.
XlCellFormatting formatting = new XlCellFormatting();
formatting.Font = new XlFont();
formatting.Font.Name = "Calibri Light";
formatting.Font.SchemeStyle = XlFontSchemeStyles.None;
formatting.Font.Size = 24;
formatting.Font.Color = XlColor.FromTheme(XlThemeColor.Dark1, 0.35);
formatting.Border = new XlBorder();
formatting.Border.BottomColor = XlColor.FromTheme(XlThemeColor.Dark1, 0.35);
formatting.Border.BottomLineStyle = XlBorderLineStyle.Medium;
// Add the document title.
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = "SALES 2014";
cell.Formatting = formatting;
}
// Create four empty cells with the title formatting applied.
for (int i = 0; i < 4; i++) {
using (IXlCell cell = row.CreateCell())
cell.Formatting = formatting;
}
}
// Skip one row before starting to generate data rows.
sheet.SkipRows(1);
}
void GenerateHeaderRow(IXlSheet sheet) {
string[] columnNames = new string[] { "State", "Sales", "Sales vs Target", "Profit", "Market Share" };
// Create the header row and set its height.
using (IXlRow row = sheet.CreateRow()) {
row.HeightInPixels = 25;
// Create required cells in the header row, assign values from the columnNames array to them and apply specific formatting settings.
row.BulkCells(columnNames, headerRowFormatting);
}
}
void GenerateDataRow(IXlSheet sheet, SalesData data, bool isLastRow) {
// Create the data row to display sales information for the specific state.
using (IXlRow row = sheet.CreateRow()) {
row.HeightInPixels = 25;
// Specify formatting settings to be applied to the data rows to shade alternate rows.
XlCellFormatting formatting = new XlCellFormatting();
formatting.CopyFrom((row.RowIndex % 2 == 0) ? evenRowFormatting : oddRowFormatting);
// Set the bottom border for the last data row.
if (isLastRow) {
formatting.Border = new XlBorder();
formatting.Border.BottomColor = XlColor.FromTheme(XlThemeColor.Dark1, 0.0);
formatting.Border.BottomLineStyle = XlBorderLineStyle.Medium;
}
// Create the cell containing the state name.
using (IXlCell cell = row.CreateCell()) {
cell.Value = data.State;
cell.ApplyFormatting(formatting);
}
// Create the cell containing sales data.
using (IXlCell cell = row.CreateCell()) {
cell.Value = data.ActualSales;
cell.ApplyFormatting(formatting);
}
// Create the cell that displays the difference between the actual and target sales.
using (IXlCell cell = row.CreateCell()) {
cell.Value = data.ActualSales / data.TargetSales - 1;
cell.ApplyFormatting(formatting);
}
// Create the cell containing the state profit.
using (IXlCell cell = row.CreateCell()) {
cell.Value = data.Profit;
cell.ApplyFormatting(formatting);
}
// Create the cell containing the percentage of a total market.
using (IXlCell cell = row.CreateCell()) {
cell.Value = data.MarketShare;
cell.ApplyFormatting(formatting);
}
}
}
void GenerateTotalRow(IXlSheet sheet, int firstDataRowIndex) {
// Create the total row and set its height.
using (IXlRow row = sheet.CreateRow()) {
row.HeightInPixels = 25;
// Create the first cell in the row and apply specific formatting settings to this cell.
using (IXlCell cell = row.CreateCell())
cell.ApplyFormatting(totalRowFormatting);
// Create the second cell in the total row and assign the SUBTOTAL function to it to calculate the average of the subtotal of the cells located in the "Sales" column.
using (IXlCell cell = row.CreateCell()) {
cell.SetFormula(XlFunc.Subtotal(XlCellRange.FromLTRB(1, firstDataRowIndex, 1, row.RowIndex - 1), XlSummary.Average, false));
cell.ApplyFormatting(totalRowFormatting);
cell.ApplyFormatting((XlNumberFormat)@"""Avg=""#,##0,,""M""");
}
// Create the third cell in the row and apply specific formatting settings to this cell.
using (IXlCell cell = row.CreateCell())
cell.ApplyFormatting(totalRowFormatting);
// Create the fourth cell in the total row and assign the SUBTOTAL function to it to calculate the sum of the subtotal of the cells located in the "Profit" column.
using (IXlCell cell = row.CreateCell()) {
cell.SetFormula(XlFunc.Subtotal(XlCellRange.FromLTRB(3, firstDataRowIndex, 3, row.RowIndex - 1), XlSummary.Sum, false));
cell.ApplyFormatting(totalRowFormatting);
cell.ApplyFormatting((XlNumberFormat)@"""Sum=""#,##0,,""M""");
}
}
}
void GenerateConditionalFormatting(IXlSheet sheet, int firstDataRowIndex) {
// Create an instance of the XlConditionalFormatting class to define a new rule.
XlConditionalFormatting formatting = new XlConditionalFormatting();
// Specify the cell range to which the conditional formatting rule should be applied (B4:B38).
formatting.Ranges.Add(XlCellRange.FromLTRB(1, firstDataRowIndex, 1, sheet.CurrentRowIndex - 2));
// Create the rule to compare values in the "Sales" column using data bars.
XlCondFmtRuleDataBar rule1 = new XlCondFmtRuleDataBar();
// Specify the color of data bars.
rule1.FillColor = XlColor.FromTheme(XlThemeColor.Accent1, 0.4);
// Set the solid fill type.
rule1.GradientFill = false;
formatting.Rules.Add(rule1);
// Add the specified rule to the worksheet collection of conditional formatting rules.
sheet.ConditionalFormattings.Add(formatting);
// Create an instance of the XlConditionalFormatting class to define new rules.
formatting = new XlConditionalFormatting();
// Specify the cell range to which the conditional formatting rules should be applied (C4:C38).
formatting.Ranges.Add(XlCellRange.FromLTRB(2, firstDataRowIndex, 2, sheet.CurrentRowIndex - 2));
// Create the rule to identify negative values in the "Sales vs Target" column.
XlCondFmtRuleCellIs rule2 = new XlCondFmtRuleCellIs();
// Specify the relational operator to be used in the conditional formatting rule.
rule2.Operator = XlCondFmtOperator.LessThan;
// Set the threshold value.
rule2.Value = 0;
// Specify formatting options to be applied to cells if the condition is true.
// Set the font color to dark red.
rule2.Formatting = new XlFont() { Color = Color.DarkRed };
formatting.Rules.Add(rule2);
// Create the rule to identify top five values in the "Sales vs Target" column.
XlCondFmtRuleTop10 rule3 = new XlCondFmtRuleTop10();
rule3.Rank = 5;
// Specify formatting options to be applied to cells if the condition is true.
// Set the font color to dark green.
rule3.Formatting = new XlFont() { Color = Color.DarkGreen };
formatting.Rules.Add(rule3);
// Add the specified rules to the worksheet collection of conditional formatting rules.
sheet.ConditionalFormattings.Add(formatting);
// Create an instance of the XlConditionalFormatting class to define a new rule.
formatting = new XlConditionalFormatting();
// Specify the cell range to which the conditional formatting rules should be applied (D4:D38).
formatting.Ranges.Add(XlCellRange.FromLTRB(3, firstDataRowIndex, 3, sheet.CurrentRowIndex - 2));
// Create the rule to compare values in the "Profit" column using data bars.
XlCondFmtRuleDataBar rule4 = new XlCondFmtRuleDataBar();
// Specify the color of data bars.
rule4.FillColor = Color.FromArgb(99, 195, 132);
// Specify the positive bar border color.
rule4.BorderColor = Color.FromArgb(99, 195, 132);
// Specify the negative bar fill color.
rule4.NegativeFillColor = Color.FromArgb(255, 85, 90);
// Specify the negative bar border color.
rule4.NegativeBorderColor = Color.FromArgb(255, 85, 90);
// Specify the solid fill type.
rule4.GradientFill = false;
formatting.Rules.Add(rule4);
// Add the specified rule to the worksheet collection of conditional formatting rules.
sheet.ConditionalFormattings.Add(formatting);
// Create an instance of the XlConditionalFormatting class to define a new rule.
formatting = new XlConditionalFormatting();
// Specify the cell range to which the conditional formatting rules should be applied (E4:E38).
formatting.Ranges.Add(XlCellRange.FromLTRB(4, firstDataRowIndex, 4, sheet.CurrentRowIndex - 2));
// Create the rule to apply a specific icon from the three traffic lights icon set to each cell in the "Market Share" column based on its value.
XlCondFmtRuleIconSet rule5 = new XlCondFmtRuleIconSet();
rule5.IconSetType = XlCondFmtIconSetType.TrafficLights3;
formatting.Rules.Add(rule5);
// Add the specified rule to the worksheet collection of conditional formatting rules.
sheet.ConditionalFormattings.Add(formatting);
}
}
}