public static string[] ReadColumnsNames(ExcelPackage package, TableDescriptor descriptor)
{
var worksheet = package.Workbook.Worksheets[descriptor.SheetName];
var columnCount = GetColumnCount(descriptor, worksheet);
var columns = new List<string>(columnCount);
for (var columnIndex = 1; columnIndex <= columnCount; columnIndex++)
{
var cell = worksheet.Cell(descriptor.HeaderRow, columnIndex);
columns.Add(cell.Value);
}
return columns.ToArray();
}
public static string[] ReadColumnsNames(ExcelPackage package, TableDescriptor descriptor)
{
var worksheet = package.Workbook.Worksheets[descriptor.SheetName];
var columnCount = GetColumnCount(descriptor, worksheet);
var columns = new List <string>(columnCount);
for (var columnIndex = 1; columnIndex <= columnCount; columnIndex++)
{
var cell = worksheet.Cell(descriptor.HeaderRow, columnIndex);
columns.Add(cell.Value);
}
return(columns.ToArray());
}
private static int GetColumnCount(TableDescriptor descriptor, ExcelWorksheet worksheet)
{
var columnCount = descriptor.ColumnCount;
if (!descriptor.DynamicColumnCount)
{
return columnCount;
}
columnCount = 1;
while (!string.IsNullOrEmpty(worksheet.Cell(descriptor.HeaderRow, columnCount).Value))
{
columnCount++;
}
columnCount--;
return columnCount;
}
private static int GetColumnCount(TableDescriptor descriptor, ExcelWorksheet worksheet)
{
var columnCount = descriptor.ColumnCount;
if (!descriptor.DynamicColumnCount)
{
return(columnCount);
}
columnCount = 1;
while (!string.IsNullOrEmpty(worksheet.Cell(descriptor.HeaderRow, columnCount).Value))
{
columnCount++;
}
columnCount--;
return(columnCount);
}
/// <summary>
/// Write only to prepared empty workbook
/// </summary>
/// <param name="filePath"> </param>
/// <param name="elements"> </param>
/// <param name="descriptor"> </param>
public static void Write <T>(string filePath, IEnumerable <T> elements, TableDescriptor descriptor)
{
#region Check
if (string.IsNullOrEmpty(filePath))
{
throw new ArgumentNullException("filePath");
}
if (ReferenceEquals(elements, null))
{
throw new ArgumentNullException("elements");
}
if (ReferenceEquals(descriptor, null))
{
throw new ArgumentNullException("descriptor");
}
if (!descriptor.CanWrite)
{
throw new ArgumentException("Descriptor doesn't support write", "descriptor");
}
#endregion
var propertyInfos = new Dictionary <string, PropertyInfo>();
foreach (var info in typeof(T).GetProperties(BindingFlags.GetProperty | BindingFlags.Instance | BindingFlags.Public))
{
propertyInfos.Add(info.Name, info);
}
using (var package = ExcelPackage.Open(filePath))
{
var worksheet = package.Workbook.Worksheets[descriptor.SheetName];
var rowIndex = descriptor.BeginRow;
foreach (var element in elements)
{
for (var columnIndex = 0; columnIndex < descriptor.ColumnNames.Length; columnIndex++)
{
var columnName = descriptor.ColumnNames[columnIndex];
var cell = worksheet.Cell(rowIndex, columnIndex + 1);
cell.Value = Convert.ToString(propertyInfos[columnName].GetValue(element, new object[0]));
}
rowIndex++;
}
package.Save();
}
}
public static IEnumerable <string[]> Read(ExcelPackage package, TableDescriptor descriptor)
{
#region Check
if (!descriptor.CanRead)
{
throw new ArgumentException(string.Format("Descriptor '{0}' doesn't support read", descriptor.SheetName), "descriptors");
}
#endregion
{
{
var worksheet = package.Workbook.Worksheets[descriptor.SheetName];
var columnCount = GetColumnCount(descriptor, worksheet);
var rowIndex = descriptor.BeginRow;
do
{
#region Check end
if (descriptor.Key.Items.Any(keyCol => !string.IsNullOrEmpty(worksheet.Cell(rowIndex, keyCol).Value)))
{
goto NEXT;
}
break;
NEXT:
#endregion
var array = new string[columnCount];
for (var columnIndex = 1; columnIndex <= columnCount; columnIndex++)
{
var cell = worksheet.Cell(rowIndex, columnIndex);
array[columnIndex - 1] = cell.Value;
}
yield return(array);
rowIndex++;
} while (true);
}
}
}
public static IEnumerable<string[]> Read(ExcelPackage package, TableDescriptor descriptor)
{
#region Check
if (!descriptor.CanRead)
{
throw new ArgumentException(string.Format("Descriptor '{0}' doesn't support read", descriptor.SheetName), "descriptors");
}
#endregion
{
{
var worksheet = package.Workbook.Worksheets[descriptor.SheetName];
var columnCount = GetColumnCount(descriptor, worksheet);
var rowIndex = descriptor.BeginRow;
do
{
#region Check end
if (descriptor.Key.Items.Any(keyCol => !string.IsNullOrEmpty(worksheet.Cell(rowIndex, keyCol).Value)))
{
goto NEXT;
}
break;
NEXT:
#endregion
var array = new string[columnCount];
for (var columnIndex = 1; columnIndex <= columnCount; columnIndex++)
{
var cell = worksheet.Cell(rowIndex, columnIndex);
array[columnIndex - 1] = cell.Value;
}
yield return array;
rowIndex++;
} while (true);
}
}
}
