Beispiel #1
0
        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();
        }