public SurnameObject FindRange(ISheet sheet, int rowNumber) { SurnameObject Range = new SurnameObject(); //Find start for (int i = rowNumber; i > sheet.FirstRowNum; i--) { //if (sheet.GetRow(i).GetCell(1).CellType == CellType.String) //{ var currentCell = sheet.GetRow(i).GetCell(0).StringCellValue.Trim(); if (sheet.GetRow(i).GetCell(0).StringCellValue.Trim().Contains("Розрахунковий")) { Range.Start = i; break; } //} } //Find finish for (int i = rowNumber; i < sheet.LastRowNum; i++) { if (sheet.GetRow(i).GetCell(1).CellType == CellType.String) { if (sheet.GetRow(i).GetCell(1).StringCellValue.Trim().Contains("До видачі")) { Range.Finish = i; break; } } } return(Range); }
private List <SurnameObject> GetSurnamesFromFile(ISheet sheet, XSSFWorkbook workBook, string fileName) { for (int i = 0; i < sheet.LastRowNum; i++) { try { if (sheet.GetRow(i).GetCell(1).CellType == CellType.String) { if (sheet.GetRow(i).GetCell(0).StringCellValue.Trim().ToUpper() == FIOMarker) { SurnameObject Surname = FindRange(sheet, i); string surname = sheet.GetRow(i).GetCell(1).StringCellValue.Trim(); Surname.Surname = surname.Trim(); Surname.File = fileName; ShowInfo(this, "Найдено: " + surname.Trim() + " в файле: " + fileName.Trim()); Surnames.Add(Surname); } } } catch { } } return(Surnames); }
public async Task CopyPeople(XSSFWorkbook workBook, ISheet sheet, SurnameObject Surname) { SetColumnsWidth(sheet); CopyRange(sheet, workBook, Surname); }
public void CopyRange(ISheet sheet, IWorkbook wb, SurnameObject Surname) { if (Surname.Start == 0 || Surname.Finish == 0) { MessageBox.Show("Не смог найти начало или конец диапазона копирования"); return; } for (int sourceRowNum = Surname.Start; sourceRowNum <= Surname.Finish; sourceRowNum++) { //read row IRow sourceRow = sheet.GetRow(sourceRowNum); IRow newRow = destinationWb.GetSheet(destinationWb.GetSheetName(0)).CreateRow(destinationRowNum); // Loop through source columns to add to new row for (int i = 0; i < sourceRow.LastCellNum; i++) { // Grab a copy of the old/new cell XSSFCell oldCell = (XSSFCell)sourceRow.GetCell(i); XSSFCell newCell = (XSSFCell)newRow.CreateCell(i); // If the old cell is null jump to next cell if (oldCell == null) { newCell = null; continue; } // Copy style from old cell and apply to new cell XSSFCellStyle newCellStyle = (XSSFCellStyle)destinationWb.CreateCellStyle(); //newCellStyle.CloneStyleFrom(oldCell.CellStyle); //Borders CopyBordersStyle(oldCell, newCellStyle); ////Text Style CopyTextStyle(oldCell, newCellStyle); ////Font CopyFontStyle(wb, oldCell, newCellStyle); ////newCellStyle.CloneStyleFrom(oldCell.CellStyle); newCell.CellStyle = newCellStyle; // If there is a cell comment, copy if (newCell.CellComment != null) { newCell.CellComment = oldCell.CellComment; } // If there is a cell hyperlink, copy if (oldCell.Hyperlink != null) { newCell.Hyperlink = oldCell.Hyperlink; } // Set the cell data type newCell.SetCellType(oldCell.CellType); // Set the cell data value SetCellValue(oldCell, newCell); } // If there are are any merged regions in the source row, copy to new row for (int i = 0; i < sheet.NumMergedRegions; i++) { CellRangeAddress cellRangeAddress = sheet.GetMergedRegion(i); if (cellRangeAddress.FirstRow == sourceRow.RowNum) { CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.RowNum, (newRow.RowNum + (cellRangeAddress.FirstRow - cellRangeAddress.LastRow)), cellRangeAddress.FirstColumn, cellRangeAddress.LastColumn); destinationWb.GetSheet(destinationWb.GetSheetName(0)).AddMergedRegion(newCellRangeAddress); } } destinationRowNum++; } destinationRowNum++; }