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