private RowCollection GetRows(IDynamic workSheet)
{
List <object> result = new List <object>();
DynamicTypeManager dynType = new DynamicTypeManager("Sorlov.PowerShell.MicrosoftOffice", "ExcelWorkbook#" + parFile.GetHashCode().ToString());
IDynamic endCell = workSheet.Property("Cells").Get().Property("SpecialCells").PropertyParam(11).Get();
IDynamic range = workSheet.Property("Range").PropertyParam("A1").PropertyParam(GetExcelCellName(endCell.Property("Column").Get <int>(), endCell.Property("Row").Get <int>())).Get();
IDynamic columns = range.Property("Columns").Get();
int columnCount = columns.Property("Count").Get <int>();
IDynamic rows = range.Property("Rows").Get();
int rowCount = rows.Property("Count").Get <int>();
int startingRow = range.Property("Row").Get <int>();
int startingColumn = range.Property("Column").Get <int>();
for (int i = 0; i < columnCount; i++)
{
string colName = workSheet.Property("Cells").Get().Index(startingRow, startingColumn + i).Get().Property("Value").Get <string>();
if (colName == null)
{
break;
}
dynType.CreateProperty(colName, typeof(string));
}
for (int i = 1; i < rowCount; i++)
{
object newRow = dynType.Create();
for (int j = 0; j < columnCount; j++)
{
string colName = workSheet.Property("Cells").Get().Index(startingRow, startingColumn + j).Get().Property("Text").Get <string>();
if (colName == null)
{
break;
}
string cellData = workSheet.Property("Cells").Get().Index(startingRow + i, startingColumn + j).Get().Property("Text").Get <string>();
DynamicTypeManager.SetProperty(newRow, colName, cellData);
}
result.Add(newRow);
}
return(new RowCollection(result));
}
protected override void BeginProcessing()
{
base.BeginProcessing();
if (parFile.StartsWith(@".\"))
{
parFile = System.IO.Path.Combine(this.CurrentProviderLocation("FileSystem").ProviderPath, parFile.Substring(2));
}
parFile = System.IO.Path.GetFullPath(parFile);
if (!System.IO.File.Exists(parFile))
{
ThrowTerminatingError(new ErrorRecord(new FileNotFoundException(string.Format("The source file does not exist", parFile)), "200", ErrorCategory.OpenError, parFile));
}
FileInfo fileInfo = new System.IO.FileInfo(parFile);
ExcelPackage excelPackage = new ExcelPackage(fileInfo);
ExcelWorkbook excelWorkbook = excelPackage.Workbook;
Workbook workbook = new Workbook();
workbook.Filepath = parFile;
workbook.FileIsReadOnly = fileInfo.IsReadOnly;
workbook.FileLastModified = fileInfo.LastWriteTime;
workbook.FileCreated = fileInfo.CreationTime;
workbook.Author = excelWorkbook.Properties.Author;
workbook.Title = excelWorkbook.Properties.Title;
workbook.Comments = excelWorkbook.Properties.Comments;
workbook.Keywords = excelWorkbook.Properties.Keywords;
List <Worksheet> worksheets = new List <Worksheet>();
foreach (ExcelWorksheet excelWorksheet in excelWorkbook.Worksheets)
{
DynamicTypeManager dynType = new DynamicTypeManager("Sorlov.PowerShell.MicrosoftOffice", "ExcelWorkbook#" + parFile.GetHashCode().ToString());
Worksheet worksheet = new Worksheet();
worksheet.Name = excelWorksheet.Name;
int colCount = 1;
while (true)
{
if (excelWorksheet.Cell(1, colCount).Value == null)
{
break;
}
if (excelWorksheet.Cell(1, colCount).Value.Trim() == string.Empty)
{
break;
}
dynType.CreateProperty(excelWorksheet.Cell(1, colCount).Value.Trim(), typeof(string));
colCount++;
}
List <object> rowList = new List <object>();
int rowCount = 2;
while (true)
{
bool foundSomething = false;
object newRow = dynType.Create();
for (int i = 1; i < colCount; i++)
{
string cellValue = excelWorksheet.Cell(rowCount, i).Value;
if (cellValue != null)
{
if (cellValue.Trim() != string.Empty)
{
DynamicTypeManager.SetProperty(newRow, excelWorksheet.Cell(1, i).Value.Trim(), cellValue);
foundSomething = true;
}
}
}
if (foundSomething == false)
{
break;
}
rowList.Add(newRow);
rowCount++;
}
RowCollection rowCollection = new RowCollection(rowList);
worksheet.Rows = rowCollection;
worksheets.Add(worksheet);
}
workbook.Worksheets = new WorksheetCollection(worksheets);
WriteObject(workbook);
excelPackage.Dispose();
}