private void ExportToExcel(ElectroExcelProject excel, string folder, string fileName, string fileType)
{
ExcelPackage excelPack;
var file = Path.GetFileName(fileName);
var count = 0;
while (true)
{
var fullPath = Path.Combine(folder, fileType + (count == 0 ? "" : count.ToString()) + file);
count++;
if (!File.Exists(fullPath))
{
excelPack = new ExcelPackage(new FileInfo(fullPath));
break;
}
}
_row = 1;
excelPack.Workbook.Worksheets.Add("AAA");
var ws = excelPack.Workbook.Worksheets[1];
ExportToExcel(excel, ws, "", 1);
excelPack.Save();
}
private void ExportToExcel(ElectroExcelProject excel, ExcelWorksheet ws, string num, int levelCount)
{
var count = 1;
foreach (var o in excel.Row)
{
ws.Cells[_row, count++].Value = o;
}
ws.Cells[_row, count].Value = num + levelCount + ".";
if (excel.Positions.Count > 0)
{
ws.Cells[_row, 1, _row, count].Style.Font.Bold = true;
ws.Cells[_row, 1, _row, count].Style.Font.Size += 1;
}
_row++;
var chCount = 1;
foreach (var excelProject in excel.Positions)
{
ExportToExcel(excelProject, ws, num + levelCount + ".", chCount++);
}
}
public ExcelProject ConvertElectoExcelToDkExcel(ElectroExcelProject electroExcelProject)
{
var converted = ConvertElectroExcelToDkExcel(electroExcelProject);
return converted;
}
private ExcelProject ConvertElectroExcelToDkExcel(ElectroExcelProject electroExcelProject)
{
var dkExcelRow = new ExcelProject();
dkExcelRow.Row = new object[CountColumnRowExcelDk];
dkExcelRow.Row[2] = electroExcelProject.GetField(Field.NumberBySpec); // pos po spec
dkExcelRow.Row[3] = electroExcelProject.GetField(Field.Designation); // pos po spec
dkExcelRow.Row[4] = electroExcelProject.GetField(Field.Name); // pos po spec
dkExcelRow.Row[11] = electroExcelProject.GetField(Field.Size); // размеры пока в длину
dkExcelRow.Row[13] = electroExcelProject.GetField(Field.CountByOne); // кол-во на одну
dkExcelRow.Row[14] = electroExcelProject.GetField(Field.CountByAll); // кол-во на все
if (electroExcelProject.Positions.Count == 1 && electroExcelProject.Positions[0].Positions.Count == 0)
{
dkExcelRow.Row[5] = electroExcelProject.Positions[0].GetField(Field.InfoAboutZag); // profile (тащим из дитяти если есть)
dkExcelRow.Row[15] = electroExcelProject.Positions[0].GetField(Field.WeigthByOne); // вес на одну (тащим из дитяти если есть)
dkExcelRow.Row[16] = electroExcelProject.Positions[0].GetField(Field.WeigthByAll); // вес на все (тащим из дитяти если есть)
dkExcelRow.Row[11] = electroExcelProject.Positions[0].GetField(Field.Size);
if (electroExcelProject.Positions[0].GetIndexField(Field.ClearWeigthDetail) != -1)
{
dkExcelRow.Row[15] = electroExcelProject.Positions[0].GetField(Field.ClearWeigthDetail);
}
}
else
{
foreach (var excelProject in electroExcelProject.Positions)
{
dkExcelRow.Positions.Add(ConvertElectroExcelToDkExcel(excelProject));
}
}
var swert = dkExcelRow.Positions.FirstOrDefault(x => x.Row[3].ToString().ToLowerInvariant().EndsWith("д"));
if (swert != null)
{
if (dkExcelRow.Row[3].ToString().ToLowerInvariant() ==
swert.Row[3].ToString()
.ToLowerInvariant()
.Substring(0, swert.Row[3].ToString().ToLowerInvariant().Length - 1))
{
foreach (var excelProject in swert.Positions)
{
excelProject.Parent = dkExcelRow;
}
dkExcelRow.Positions.InsertRange(0, swert.Positions);
dkExcelRow.Positions.Remove(swert);
}
}
return dkExcelRow;
}
public void FilterExcelProject(ElectroExcelProject excelProject)
{
var tu = new Regex(@"(ТУ)[\s]*[0-9]+[\s]*[0-9-]*", RegexOptions.IgnoreCase);
var sp = new Regex(@"(С[\s]*П)[\s-]*(Ф[\s]*С)[\s-]*(82)", RegexOptions.IgnoreCase);
var keyWords = new Regex(@"(полуавтоматическая)|(окраска)|(черная резина)|(подкраска)|(красная резина)|(вспомогательное средство)|(сварочный газ)|(шпатлевка)|(смазка)|(приклеивание)|(флюс)|(бензин)", RegexOptions.IgnoreCase);
var delete = new List<ElectroExcelProject>();
foreach (var child in excelProject.Positions)
{
var name = child.GetField(Field.Name).ToString();
if (keyWords.IsMatch(name) || tu.IsMatch(name) || sp.IsMatch(name))
{
delete.Add(child);
}
}
foreach (var project in delete)
{
excelProject.Positions.Remove(project);
}
foreach (var child in excelProject.Positions)
{
FilterExcelProject(child);
}
}
private List<ElectroExcelProject> GetListRows(string fileName)
{
var fileInfo = new FileInfo(fileName);
var excelPack = new ExcelPackage(fileInfo);
var listRows = new List<ElectroExcelProject>();
var workSheet = excelPack.Workbook.Worksheets[1];
var listCaption = GetCaptions(workSheet);
for (var i = 2; i <= workSheet.Dimension.Rows; i++)
{
var structure = new ElectroExcelProject();
var dataRow = workSheet.Cells[i, 1, i, _countExcelColumn];
structure.Row = new object[_countExcelColumn];
for (var j = 0; j < _countExcelColumn; j++)
{
structure.Row[j] = ((object[,]) dataRow.Value)[0, j];
}
structure.ColumnsCaptions = listCaption;
structure.FieldsToCaptions = FieldsToCaptions;
listRows.Add(structure);
}
return listRows;
}
private IEnumerable<ElectroExcelProject> GetChildrens(ElectroExcelProject excelProject)
{
var res = new List<ElectroExcelProject>();
res.AddRange(excelProject.Positions);
foreach (var position in excelProject.Positions)
{
res.AddRange(GetChildrens(position));
}
return res;
}
