-
Notifications
You must be signed in to change notification settings - Fork 1
Expand file tree
/
Copy pathDataFileToTable.cs
More file actions
178 lines (157 loc) · 6.27 KB
/
Copy pathDataFileToTable.cs
File metadata and controls
178 lines (157 loc) · 6.27 KB
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 System.Threading.Tasks;
using Microsoft.VisualBasic.FileIO;
using System.IO;
using OfficeOpenXml;
namespace StatTag.Core.Models
{
public static class DataFileToTable
{
/// <summary>
///
/// </summary>
/// <param name="tableFilePath"></param>
/// <returns>An array containing the dimensions (R x C), or NULL if it could not determine
/// the table size.</returns>
public static int[] GetCSVTableDimensions(string tableFilePath)
{
if (!File.Exists(tableFilePath))
{
return null;
}
int rows = 0;
int columns = 0;
using (var parser = new TextFieldParser(tableFilePath))
{
parser.TextFieldType = FieldType.Delimited;
parser.SetDelimiters(",");
parser.HasFieldsEnclosedInQuotes = true;
var data = new List<string>();
while (!parser.EndOfData)
{
rows++;
string[] fields = parser.ReadFields();
if (fields != null)
{
columns = Math.Max(columns, fields.Length);
}
}
}
return new int[] {rows, columns};
}
public static Table GetTableResult(string tableFilePath)
{
tableFilePath = tableFilePath.Trim().ToLower();
if (tableFilePath.EndsWith(".xlsx"))
{
return GetXLSXTableResult(tableFilePath);
}
else if (tableFilePath.EndsWith(".xls"))
{
throw new Exception("StatTag is unable to import data from older Excel files (those ending in .XLS). If possible, please use the newer .XLSX format, or use a CSV.");
}
else
{
return GetCSVTableResult(tableFilePath);
}
}
private static Table GetXLSXTableResult(string tableFilePath)
{
var table = new Table();
if (!File.Exists(tableFilePath))
{
return table;
}
var excelFile = new FileInfo(tableFilePath);
using (var package = new ExcelPackage(excelFile))
{
var worksheets = package.Workbook.Worksheets;
if (worksheets == null || worksheets.Count == 0)
{
return table;
}
// Right now, we will only use the first sheet in a workbook
var sheet = package.Workbook.Worksheets.First();
// If any of the necessary objects (sheet, cells, values) are null, we assume it means that the sheet
// is empty, and we can just return a blank table structure.
if (sheet == null || sheet.Cells == null || sheet.Cells.Value == null)
{
return table;
}
var sheetData = (Object[,])sheet.Cells.Value;
var dimensions = new int[] {sheetData.GetLength(0), sheetData.GetLength(1)};
var data = new string[dimensions[0], dimensions[1]];
// The EPPlus library uses 1-based indexing for Excel cells (which is consistent with the usual Office
// object interface, but not consistent with how C# works). This is why we are doing 1-based indexing
// in the loop, and in the data array we are subtracting 1 to get it back to 0-based.
for (int row = 1; row <= dimensions[0]; row++)
{
for (int column = 1; column <= dimensions[1]; column++)
{
data[row-1, column-1] = sheet.Cells[row, column].GetValue<string>();
data[row-1, column-1] = data[row-1, column-1] ?? string.Empty;
}
}
table.RowSize = dimensions[0];
table.ColumnSize = dimensions[1];
table.Data = data;
}
return table;
}
/// <summary>
/// Combines the different components of a matrix command into a single structure.
/// </summary>
/// <param name="tableFilePath"></param>
/// <returns></returns>
private static Table GetCSVTableResult(string tableFilePath)
{
var table = new Table();
if (!File.Exists(tableFilePath))
{
return table;
}
var dimensions = GetCSVTableDimensions(tableFilePath);
if (dimensions == null || dimensions.Contains(0))
{
return table;
}
using (var parser = new TextFieldParser(tableFilePath, System.Text.Encoding.Default))
{
parser.TextFieldType = FieldType.Delimited;
parser.SetDelimiters(",");
parser.HasFieldsEnclosedInQuotes = true;
int row = 0;
var data = new string[dimensions[0], dimensions[1]];
while (!parser.EndOfData)
{
int column = 0;
string[] fields = parser.ReadFields();
if (fields != null)
{
for (int index = 0; index < fields.Length; index++)
{
data[row, index] = fields[index];
}
}
int fieldsLength = (fields == null ? 0 : fields.Length);
// If this is an unbalanced row, balance it with empty strings
if (fieldsLength < dimensions[1])
{
for (int index = fieldsLength; index < dimensions[1]; index++)
{
data[row, index] = string.Empty;
}
}
row++;
}
table.RowSize = dimensions[0];
table.ColumnSize = dimensions[1];
table.Data = data;
}
return table;
}
}
}