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++;
}
