Source

main / Source / Excel / Excel.BizLogic / Extensions / WorkbookExtensions.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
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using DataUp.Excel.Common;
using Microsoft.Office.Interop.Excel;

namespace DataUp.Excel.BizLogic
{
    /// <summary>
    /// Extensions for workbook class.
    /// </summary>
    public static class WorkbookExtensions
    {
        /// <summary>
        /// Retrieve a List of all the comments in a workbook.
        /// </summary>
        /// <returns>List of errorDetails related to Comments in the workbook.</returns>
        public static List<ErrorDetail> XLGetComments(this _Workbook workbook)
        {
            List<ErrorDetail> errors = new List<ErrorDetail>();

            if (workbook != null)
            {
                foreach (var item in workbook.Worksheets)
                {
                    Worksheet worksheet = (Worksheet)item;

                    if (workbook.IsValid(worksheet))
                    {
                        int count = 0;
                        StringBuilder locations = new StringBuilder();
                        for (int i = 1; i <= worksheet.Comments.Count && count <= Common.Constants.MaxErrorCount; i++)
                        {
                            count++;

                            var parent = worksheet.Comments[i].Parent as Range;
                            if (parent != null)
                            {
                                locations.Append(parent.get_Address());
                                locations.Append(",");
                            }
                        }

                        if (count > 0)
                        {
                            errors.Add(new ErrorDetail()
                            {
                                Error = ErrorType.Comments,
                                ControlName = "Embedded comments",
                                ErrorAddress = locations.ToString(), //GetFormattedLocations(count, locations),
                                SheetName = worksheet.Name,
                                IsRemovable = true,
                                Category = ErrorCategory.Medium,
                                Description = "Comments will not be visible when data are exported as a .csv file.  Also, these elements are visible only if the file is opened with Microsoft Excel. ",
                                Recommendation = "Create a new column titled “Comments” and add your text there"
                            });
                        }
                    }
                }
            }

            return errors;
        }

        /// <summary>
        /// Retrieve a List of all the charts in a workbook.
        /// </summary>
        /// <returns>List of errorDetails related to charts in the workbook.</returns>
        public static List<ErrorDetail> XLGetChart(this _Workbook workbook)
        {
            List<ErrorDetail> errors = new List<ErrorDetail>();

            // Given an Excel workbook, and the title of a chart to export, create a 
            // new xml file containing the chart. Although the chart won't be loadable
            // in Excel, you could extract the chart from it at a later date.
            foreach (var item in workbook.Worksheets)
            {
                Worksheet sheet = (Worksheet)item;
                if (workbook.IsValid(sheet))
                {
                    int count = 0;
                    StringBuilder locations = new StringBuilder();

                    ChartObjects chartObjects = (ChartObjects)(sheet.ChartObjects());

                    for (int i = 1; i <= chartObjects.Count && count <= Common.Constants.MaxErrorCount; i++)
                    {
                        ChartObject chtObj = (ChartObject)(chartObjects.Item(i));
                        Chart cht = (Chart)chtObj.Chart;

                        count++;

                        // You know you have a chart that has a title. Is it the right title?
                        if ((cht != null) && (cht.Name != null))
                        {
                            locations.Append(cht.Name);
                            locations.Append(",");
                        }
                    }

                    if (count > 0)
                    {
                        errors.Add(new ErrorDetail()
                        {
                            Error = ErrorType.Charts,
                            ControlName = "Embedded charts, pictures",
                            SheetName = sheet.Name,
                            ErrorAddress = locations.ToString(), //GetFormattedLocations(count, locations),
                            IsRemovable = true,
                            Category = ErrorCategory.High,
                            Description = "These embedded items will not be visible when data are exported as a .csv file. Also, these elements are visible only if the file is opened with Microsoft Excel.",
                            Recommendation = "Move embedded charts, or pictures to other tabs in your file or a completely separate file."
                        });
                    }
                }
            }

            return errors;
        }

        /// <summary>
        /// Retrieve a List of all the shapes in a workbook.
        /// </summary>
        /// <returns>List of errorDetails related to comma cells in the workbook.</returns>
        public static List<ErrorDetail> XLGetShapes(this _Workbook workbook)
        {
            List<ErrorDetail> errors = new List<ErrorDetail>();

            foreach (var item in workbook.Worksheets)
            {
                Worksheet sheet = (Worksheet)item;
                // if (workbook.IsValid(sheet))
                {
                    int count = 0;
                    StringBuilder locations = new StringBuilder();

                    var shapes = sheet.Shapes;

                    for (int i = 1; i <= shapes.Count && count <= Common.Constants.MaxErrorCount; i++)
                    {
                        try
                        {
                            Shape shape = shapes.Item(i);

                            // You know you have a chart that has a title. Is it the right title?
                            if ((shape != null)
                                && (shape.Name != null)
                                && shape.HasChart != Microsoft.Office.Core.MsoTriState.msoTrue
                                && shape.Visible == Microsoft.Office.Core.MsoTriState.msoTrue
                                && shape.Type != Microsoft.Office.Core.MsoShapeType.msoComment)
                            {
                                locations.Append(shape.Name);
                                locations.Append(",");
                                count++;
                            }
                        }
                        catch (Exception)
                        {
                            // Ignore : 
                        }
                    }

                    if (count > 0)
                    {
                        errors.Add(new ErrorDetail()
                        {
                            Error = ErrorType.Shapes,
                            ControlName = "Embedded charts, pictures",
                            SheetName = sheet.Name,
                            ErrorAddress = locations.ToString(), //GetFormattedLocations(count, locations),
                            IsRemovable = true,
                            Category = ErrorCategory.High,
                            Description = "These embedded items will not be visible when data are exported as a .csv file. Also, these elements are visible only if the file is opened with Microsoft Excel.",
                            Recommendation = "Move embedded charts, or pictures to other tabs in your file or a completely separate file."
                        });
                    }
                }
            }

            return errors;
        }

        /// <summary>
        /// Retrieve a List of all non contiguous data in a workbook
        /// </summary>
        /// <returns>ist of errorDetails related to non contiguous data in the workbook.</returns>
        public static List<ErrorDetail> XLGetNonContiguous(this _Workbook workbook)
        {
            List<ErrorDetail> errors = new List<ErrorDetail>();

            foreach (var item in workbook.Worksheets)
            {
                Worksheet sheet = (Worksheet)item;
                if (workbook.IsValid(sheet))
                {
                    int count = 0;
                    StringBuilder locations = new StringBuilder();

                    Range range = sheet.UsedRange;

                    // Check for non-continuous data in Rows.
                    for (int rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
                    {
                        var rowRange = (range.Cells[rCnt, 1] as Range).EntireRow;

                        if (workbook.IsEmpty(rowRange))
                        {
                            locations.Append((string)rowRange.Address);
                            locations.Append(",");

                            if (++count >= Common.Constants.MaxErrorCount)
                            {
                                break;
                            }
                        }
                    }

                    // Check for non-continuous data in columns.
                    for (int cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
                    {
                        var columnRange = (range.Cells[1, cCnt] as Range).EntireColumn;

                        if (workbook.IsEmpty(columnRange))
                        {
                            locations.Append((string)columnRange.Address);
                            locations.Append(",");

                            if (++count >= Common.Constants.MaxErrorCount)
                            {
                                break;
                            }
                        }
                    }

                    if (count > 0)
                    {
                        errors.Add(new ErrorDetail()
                        {
                            Error = ErrorType.NonContiguous,
                            ControlName = "Non-contiguous data",
                            SheetName = sheet.Name,
                            IsRemovable = false,
                            Category = ErrorCategory.High,
                            ErrorAddress = locations.ToString(), //GetFormattedLocations(count, locations),
                            Description = "Empty columns or rows tend to be used to separate multiple data tables on the same tab.",
                            Recommendation = "Move multiple tables onto separate tabs."
                        });
                    }
                }
            }
            return errors;
        }

        /// <summary>
        /// Retrieve a List of all the mixed type in a workbook.
        /// </summary>
        /// <returns>List of errorDetails related to mixed type in the workbook.</returns>
        public static List<ErrorDetail> XLGetMixedType(this _Workbook workbook)
        {
            List<ErrorDetail> errors = new List<ErrorDetail>();
            foreach (var item in workbook.Worksheets)
            {
                Worksheet sheet = (Worksheet)item;
                if (workbook.IsValid(sheet))
                {
                    int count = 0;
                    StringBuilder locations = new StringBuilder();

                    string str = null;
                    Range range = sheet.UsedRange;
                    for (int cCnt = 1; cCnt <= range.Columns.Count && count <= Common.Constants.MaxErrorCount; cCnt++)
                    {
                        if ((range.Cells[1, cCnt] as Range).Value != null)
                        {
                            str = (range.Cells[1, cCnt] as Range).Value.GetType().FullName;
                        }
                        if (str != null)
                        {
                            for (int rCnt = 2; rCnt <= range.Rows.Count && count <= Common.Constants.MaxErrorCount; rCnt++)
                            {
                                if ((range.Cells[rCnt, cCnt] as Range).Value != null)
                                {
                                    if (str != (range.Cells[rCnt, cCnt] as Range).Value.GetType().FullName)
                                    {
                                        count++;

                                        locations.Append((string)(range.Cells[rCnt, cCnt] as Range).EntireColumn.Address);
                                        locations.Append(",");

                                        break;
                                    }
                                }
                            }
                        }
                    }

                    if (count > 0)
                    {
                        errors.Add(new ErrorDetail()
                        {
                            Error = ErrorType.MixedType,
                            ControlName = "Columns have mixed data types",
                            SheetName = sheet.Name,
                            IsRemovable = false,
                            Category = ErrorCategory.Medium,
                            ErrorAddress = GetFormattedLocations(count, locations),
                            Description = "Some programs cannot handle mixed data types (e.g. numbers and text in the same column).",
                            Recommendation = "Ensure you are using only numbers or only text in a column; split data into multiple columns if necessary"
                        });
                    }
                }
            }
            return errors;
        }

        /// <summary>
        /// Retrieve a List of all the colour coded cells in a workbook.
        /// </summary>
        /// <returns>List of errorDetails related to colour coded cells in the workbook.</returns>
        public static List<ErrorDetail> XLGetCellRelatedErrors(this _Workbook workbook)
        {
            Regex reg = new Regex(Common.Constants.SpecialCharacter);
            Regex regkey = new Regex(Common.Constants.SpecialCharKeySequence);

            List<ErrorDetail> errors = new List<ErrorDetail>();

            foreach (var item in workbook.Worksheets)
            {
                Worksheet sheet = (Worksheet)item;
                if (workbook.IsValid(sheet))
                {
                    int colorCount = 0;
                    int mergedCellCount = 0;
                    int blankCellCount = 0;
                    int specialCharCellCount = 0;
                    int commaCellCount = 0;
                    StringBuilder colourLocations = new StringBuilder();
                    StringBuilder mergedCellLocations = new StringBuilder();
                    StringBuilder blankCellLocations = new StringBuilder();
                    StringBuilder specialCharCellLocations = new StringBuilder();
                    StringBuilder commaCellLocations = new StringBuilder();

                    // Check Merged Cell
                    if (sheet.UsedRange.MergeCells is System.DBNull || (bool)sheet.UsedRange.MergeCells)
                    {
                        mergedCellLocations.Append(sheet.Name);
                        mergedCellCount++;
                    }

                    AddMergedCellError(errors, sheet, mergedCellCount, mergedCellLocations);

                    // Check Color Coded Cell.
                    bool hasFontColor = (sheet.UsedRange.Font.Color is System.DBNull) ? true : Convert.ToInt32(sheet.UsedRange.Font.Color) > 0;
                    bool hasFillColor = (sheet.UsedRange.Interior.ColorIndex is System.DBNull) ? true : Convert.ToInt32(sheet.UsedRange.Interior.ColorIndex) > 0;
                    if (hasFontColor || hasFillColor)
                    {
                        colourLocations.Append(sheet.Name);
                        colorCount++;
                    }

                    AddColorCodedError(errors, sheet, colorCount, colourLocations);

                    object[,] data = null;
                    try
                    {
                        data = sheet.UsedRange.GetDataArray(false);

                        int rows = data.GetLength(0);
                        int cols = data.GetLength(1);

                        if (data != null)
                        {
                            for (int rCnt = 1; rCnt <= rows; rCnt++)
                            {
                                for (int cCnt = 1; cCnt <= cols; cCnt++)
                                {
                                    var val = data[rCnt, cCnt];
                                    if (val == null)
                                    {
                                        // Blank Cell
                                        blankCellLocations.AppendFormat("{1}{0},", rCnt, Helper.GetExcelColumnName(cCnt));
                                        blankCellCount++;
                                    }
                                    else
                                    {
                                        // Check Cell contains a comma
                                        string str = val.ToString();
                                        if (commaCellCount < Common.Constants.MaxErrorCount && str.Contains(','))
                                        {
                                            commaCellLocations.AppendFormat("{1}{0},", rCnt, Helper.GetExcelColumnName(cCnt));
                                            commaCellCount++;
                                        }

                                        // Check Cell contains special Characters.
                                        if ((specialCharCellCount < Common.Constants.MaxErrorCount) && (reg.IsMatch(str) || regkey.IsMatch(str)))
                                        {
                                            specialCharCellLocations.AppendFormat("{1}{0},", rCnt, Helper.GetExcelColumnName(cCnt));
                                            specialCharCellCount++;
                                        }
                                    }

                                    if (blankCellCount >= Common.Constants.MaxErrorCount
                                        && commaCellCount >= Common.Constants.MaxErrorCount
                                        && specialCharCellCount >= Common.Constants.MaxErrorCount)
                                    {
                                        break;
                                    }
                                }
                            }

                            AddBlankCellError(errors, sheet, blankCellCount, new StringBuilder());
                            AddCommaCellError(errors, sheet, commaCellCount, commaCellLocations);
                            AddSpecialCharError(errors, sheet, specialCharCellCount, specialCharCellLocations);
                        }
                    }
                    finally
                    {
                        data = null;
                    }
                }
            }
            return errors;
        }

        private static void AddColorCodedError(List<ErrorDetail> errors, Worksheet sheet, int colorCount, StringBuilder locations)
        {
            if (colorCount > 0)
            {
                errors.Add(new ErrorDetail()
                {
                    Error = ErrorType.ColorCoded,
                    ControlName = "Color coded text or cell shading",
                    SheetName = sheet.Name,
                    IsRemovable = true,
                    Category = ErrorCategory.Medium,
                    ErrorAddress = (colorCount == 1) ? "Total Count : 1+" : locations.ToString(), //GetFormattedLocations(count, locations),
                    Description = "Formatting will not be visible when data are exported as a .csv file. If formatting is used as a coding scheme, all codes will be lost upon export.",
                    Recommendation = "Use descriptions or alphanumeric coding schemes in a new column."
                });
            }
        }

        private static void AddSpecialCharError(List<ErrorDetail> errors, Worksheet sheet, int count, StringBuilder locations)
        {

            if (count > 0)
            {
                errors.Add(new ErrorDetail()
                {
                    Error = ErrorType.SpecialCharacter,
                    ControlName = "Special Characters",
                    SheetName = sheet.Name,
                    IsRemovable = false,
                    Category = ErrorCategory.Medium,
                    ErrorAddress = locations.ToString(), //GetFormattedLocations(count, locations),
                    Description = "Special characters ($, %, ~, {, ∑, ∂, ç, etc.) may cause problems for other programs or may be modified upon export",
                    Recommendation = "Use alpha-numeric characters only. If needed, describe the symbol in a new column"
                });
            }
        }

        private static void AddCommaCellError(List<ErrorDetail> errors, _Worksheet sheet, int count, StringBuilder locations)
        {

            if (count > 0)
            {
                errors.Add(new ErrorDetail()
                {
                    Error = ErrorType.Commas,
                    ControlName = "Commas",
                    SheetName = sheet.Name,
                    ErrorAddress = locations.ToString(), //GetFormattedLocations(count, locations),
                    IsRemovable = false,
                    Category = ErrorCategory.Low,
                    Description = "Commas are often used to separate multiple piece of information/data (e.g. City, State). Cells should have only one piece of information.",
                    Recommendation = "Split pieces of information into multiple columns (e.g. City column and State column)"
                });
            }
        }

        private static void AddBlankCellError(List<ErrorDetail> errors, Worksheet sheet, int count, StringBuilder locations)
        {

            if (count > 0)
            {
                errors.Add(new ErrorDetail()
                {
                    Error = ErrorType.BlankCell,
                    ControlName = "Blank Cells",
                    SheetName = sheet.Name,
                    ErrorAddress = string.Format("Total Count : {0}", (count < Common.Constants.MaxErrorCount ? count.ToString() : Common.Constants.MaxErrorCount.ToString() + "+")),//GetFormattedLocations(count, locations),
                    IsRemovable = false,
                    Category = ErrorCategory.Medium,
                    Description = "Blank cells within a contiguous data table are potentially problematic for reading files in other programs.",
                    Recommendation = "Designate a coding scheme for missing data or other explanations for blank cells."
                });
            }
        }

        private static void AddMergedCellError(List<ErrorDetail> errors, Worksheet sheet, int count, StringBuilder locations)
        {
            if (count > 0)
            {
                errors.Add(new ErrorDetail()
                {
                    Error = ErrorType.MergedCell,
                    ControlName = "Merged cells",
                    SheetName = sheet.Name,
                    ErrorAddress = (count == 1) ? "Total Count : 1+" : locations.ToString(), //GetFormattedLocations(count, locations),
                    IsRemovable = true,
                    Category = ErrorCategory.High,
                    Description = "Merged cells will not be maintained when data are exported as a .csv file. Information may be lost when cells are unmerged upon export.",
                    Recommendation = "Unmerge cells and annotate appropriately so information is not lost."
                });
            }
        }

        private static string GetFormattedLocations(int count, StringBuilder locations)
        {
            return string.Format("Total Count : {0} - {1}", (count < Common.Constants.MaxErrorCount ? count.ToString() : Common.Constants.MaxErrorCount.ToString() + "+"), locations.ToString());
        }
    }
}