public static SendAlbum GetSendPeoples(string FileName) { SendAlbum album = new SendAlbum(); int line = 1; int collCount = 4; Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); Workbook worKbooK = null; excel.Visible = false; excel.DisplayAlerts = false; try { worKbooK = excel.Workbooks.Open(FileName); Worksheet worKsheeT = (Worksheet)worKbooK.ActiveSheet; line = 1; album.Name = (string)(worKsheeT.Cells[line, 1] as Microsoft.Office.Interop.Excel.Range).Value; album.Name = (album.Name.Split('.').Length == 1) ? album.Name : album.Name.Split('.')[1]; line = 2; while (true) { if (Convert.ToString((worKsheeT.Cells[line, collCount] as Microsoft.Office.Interop.Excel.Range).Value) == "Сумма к оплате") { break; } else if (Convert.ToString((worKsheeT.Cells[line, collCount] as Microsoft.Office.Interop.Excel.Range).Value) != null && Convert.ToString((worKsheeT.Cells[line, collCount] as Microsoft.Office.Interop.Excel.Range).Value) != "") { album.Photos.Add(Convert.ToString((worKsheeT.Cells[line, collCount] as Microsoft.Office.Interop.Excel.Range).Value)); } collCount++; } line = 4; while (true) { if (Convert.ToString((worKsheeT.Cells[line, 1] as Microsoft.Office.Interop.Excel.Range).Value) == "" || Convert.ToString((worKsheeT.Cells[line, 1] as Microsoft.Office.Interop.Excel.Range).Value) == null) { break; } album.Peoples.Add(new SendPeople()); album.Peoples[album.Peoples.Count - 1].Name = (string)(worKsheeT.Cells[line, 2] as Microsoft.Office.Interop.Excel.Range).Value; album.Peoples[album.Peoples.Count - 1].Id = Convert.ToInt64((worKsheeT.Cells[line, 3] as Microsoft.Office.Interop.Excel.Range).Value); collCount = 4; SendProduct buffItem = new SendProduct(); bool breakwhile = true; while (breakwhile) { if (Convert.ToString((worKsheeT.Cells[3, collCount] as Microsoft.Office.Interop.Excel.Range).Value) == "" || Convert.ToString((worKsheeT.Cells[3, collCount] as Microsoft.Office.Interop.Excel.Range).Value) == null) { if (Convert.ToString((worKsheeT.Cells[2, collCount] as Microsoft.Office.Interop.Excel.Range).Value) == "Сумма к оплате") { album.Peoples[album.Peoples.Count - 1].AllCost = Convert.ToDouble((worKsheeT.Cells[line, collCount] as Microsoft.Office.Interop.Excel.Range).Value); } break; } switch ((string)(worKsheeT.Cells[3, collCount] as Microsoft.Office.Interop.Excel.Range).Value) { case "Комментарии": buffItem.Name = Convert.ToString((worKsheeT.Cells[2, collCount] as Microsoft.Office.Interop.Excel.Range).Value); break; case "Размерность": album.isUseSize = true; buffItem.Size = Convert.ToString((worKsheeT.Cells[line, collCount] as Microsoft.Office.Interop.Excel.Range).Value); break; case "Кол-во": buffItem.Count = Convert.ToDouble((worKsheeT.Cells[line, collCount] as Microsoft.Office.Interop.Excel.Range).Value); break; case "Цена": buffItem.Cost = Convert.ToDouble((worKsheeT.Cells[line, collCount] as Microsoft.Office.Interop.Excel.Range).Value); if (buffItem != null && buffItem.Count != 0 && buffItem.Cost != 0) { album.Peoples[album.Peoples.Count - 1].Product.Add(buffItem); } if (buffItem != null && buffItem.Count != 0 && buffItem.Cost == 0) { throw new BadFormatException("Поле Цена незаполнено"); } buffItem = new SendProduct(); break; default: throw new UnknownColumnException(); } collCount++; } line++; } for (int i = 0; i < album.Peoples.Count; i++) { if (album.Peoples[i].Product.Count == 0) { album.Peoples.RemoveAt(i); i = i - 1; } } } catch (Exception ex) { throw ex; } finally { IntPtr handle = (IntPtr)excel.Hwnd; worKbooK.Close(); excel.Quit(); MyFunctions.TerminateProcess(handle, 1); } return(album); }
private void CreatePrintFiles(SendAlbum album, String NameFile, int Format) { int line = 1; Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); excel.Visible = false; excel.DisplayAlerts = false; Workbook worKbooK = excel.Workbooks.Add(Type.Missing); Worksheet worKsheeT = (Worksheet)worKbooK.ActiveSheet; worKsheeT.Name = "SoapOperaPrint"; try { line = 1; worKsheeT.Cells[line, 1] = album.Name; line = 2; worKsheeT.Cells[line, 1] = "№"; worKsheeT.Cells[line, 2] = "Участник"; if (album.isUseSize) { for (int i = 3; i < album.Photos.Count * 2 + 3; i = i + 2) { worKsheeT.Cells[line, i] = album.Photos[(i - 3) / 2]; worKsheeT.Cells[line, i].Orientation = 90; } } else { for (int i = 3; i < album.Photos.Count + 3; i++) { worKsheeT.Cells[line, i] = album.Photos[i - 3]; worKsheeT.Cells[line, i].Orientation = 90; } } line = 3; for (int i = 0; i < album.Peoples.Count; i++) { worKsheeT.Cells[line, 1] = (i + 1); worKsheeT.Cells[line, 2] = album.Peoples[i].Name; for (int j = 0; j < album.Peoples[i].Product.Count; j++) { for (int k = 0; k < album.Photos.Count; k++) { if (album.Peoples[i].Product[j].Name == album.Photos[k]) { if (album.isUseSize) { worKsheeT.Cells[line, 3 + k * 2] = album.Peoples[i].Product[j].Size; worKsheeT.Cells[line, 3 + k * 2 + 1] = album.Peoples[i].Product[j].Count; } else { worKsheeT.Cells[line, 3 + k] = album.Peoples[i].Product[j].Count; } } } } line++; } int countCollum = 0; if (album.isUseSize) { countCollum = 2 + album.Photos.Count * 2; } else { countCollum = 2 + album.Photos.Count; } Microsoft.Office.Interop.Excel.Borders border = worKsheeT.Range[worKsheeT.Cells[2, 1], worKsheeT.Cells[line - 1, countCollum]].Borders; border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; border.Weight = 2d; Microsoft.Office.Interop.Excel.Range usedrange = worKsheeT.Range[worKsheeT.Cells[1, 1], worKsheeT.Cells[line - 1, countCollum]]; usedrange.Rows.AutoFit(); usedrange.Columns.AutoFit(); try { if (Format <= 0) { worKbooK.SaveAs(NameFile, XlFileFormat.xlExcel3); } else { worKbooK.SaveAs(NameFile); } } catch (Exception ex2) { throw new BadFormatSafeException(); } worKbooK.Close(); } catch (Exception ex) { throw ex; } finally { IntPtr handle = (IntPtr)excel.Hwnd; excel.Quit(); MyFunctions.TerminateProcess(handle, 1); } }