Esempio n. 1
0
        private bool ResizeRange(Range range)
        {
            var rangeWorker = new RangeWorker(excelPackage, range);

            var newLastRowIndex = rangeWorker.LastRowIndex;

            while (rangeWorker.IsEmpty(newLastRowIndex) && newLastRowIndex > rangeWorker.FirstRowIndex)
            {
                newLastRowIndex--;
            }

            while (!rangeWorker.IsEmpty(newLastRowIndex + 1))
            {
                newLastRowIndex++;
            }

            if (newLastRowIndex == rangeWorker.LastRowIndex)
            {
                return(rangeWorker.IsNewRange);
            }

            excelPackage.Workbook.Names.Remove(range.Name);

            var newRange = rangeWorker[rangeWorker.FirstRowIndex, rangeWorker.FirstColumnIndex, newLastRowIndex, rangeWorker.LastColumnIndex];

            excelPackage.Workbook.Names.Add(range.Name, newRange);

            return(true);
        }
Esempio n. 2
0
        private void CopyRangeToTable(Range range, DataTable dataTable)
        {
            var rangeWorker = new RangeWorker(excelPackage, range);

            for (var rowIndex = rangeWorker.FirstRowIndex + 1; rowIndex <= rangeWorker.LastRowIndex; rowIndex++)
            {
                DataRow newRow = dataTable.NewRow();

                for (int colIndex = rangeWorker.FirstColumnIndex; colIndex <= rangeWorker.LastColumnIndex; colIndex++)
                {
                    var columnName   = rangeWorker.GetColumnName(colIndex);
                    var expectedType = dataTable.Columns[columnName].DataType;
                    var value        = rangeWorker[rowIndex, colIndex, expectedType];

                    newRow[columnName] = value;
                }

                dataTable.Rows.Add(newRow);
            }
        }
Esempio n. 3
0
        private void CopyTableToRange(Range range, DataTable dataTable, bool keepFormula = false)
        {
            var rangeWorker = new RangeWorker(excelPackage, range);

            for (int colIndex = rangeWorker.FirstColumnIndex; colIndex <= rangeWorker.LastColumnIndex; colIndex++)
            {
                var columnName = rangeWorker.GetColumnName(colIndex);
                var column     = range.Columns?[columnName];

                rangeWorker[rangeWorker.FirstRowIndex, colIndex, column : column] = column.DisplayTitle;
            }

            var rowIndex = rangeWorker.FirstRowIndex + 1;

            foreach (DataRow row in dataTable.Rows)
            {
                for (int colIndex = rangeWorker.FirstColumnIndex; colIndex <= rangeWorker.LastColumnIndex; colIndex++)
                {
                    var columnName = rangeWorker.GetColumnName(colIndex);
                    var column     = range.Columns?[columnName];

                    rangeWorker[rowIndex, colIndex, column : column, keepFormula : keepFormula] = row[columnName];
                }

                rowIndex++;
            }

            var newLastRowIndex = rowIndex - 1;

            for (var emptyRowIndex = newLastRowIndex + 1; emptyRowIndex <= rangeWorker.LastRowIndex; emptyRowIndex++)
            {
                rangeWorker.Clear(emptyRowIndex);
            }

            excelPackage.Workbook.Names.Remove(range.Name);

            var newRange = rangeWorker[rangeWorker.FirstRowIndex, rangeWorker.FirstColumnIndex, newLastRowIndex, rangeWorker.LastColumnIndex];

            excelPackage.Workbook.Names.Add(range.Name, newRange);
        }