public static void SetPageSpecificSettings(Excel.Worksheet sheet, Excel.PageSetup pageSetup) { string type = ListPage.GetDocumentType(sheet); switch (type) { case "Перечень элементов": pageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4; pageSetup.PrintArea = "A:Z"; break; case "Спецификация": pageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4; pageSetup.PrintArea = "A:Z"; break; case "Ведомость покупных изделий": pageSetup.PaperSize = Excel.XlPaperSize.xlPaperA3; pageSetup.Orientation = Excel.XlPageOrientation.xlLandscape; pageSetup.PrintArea = "A:AT"; break; default: break; } }
public void SetPrnAreaToSingleWidth(Excel.Application oXL, Excel.Worksheet oWS) { RAIIFuncs rFuncs = new RAIIFuncs(); using (RAIICont _raiiCont = new RAIICont()) { //oXL.PrintCommunication = false; Excel.PageSetup ps = oWS.PageSetup; //_raiiCont.Push(rFuncs.GetFunc(ps)); { //header and footer setup ps.LeftHeader = ""; ps.CenterHeader = ""; ps.RightHeader = ""; ps.LeftFooter = ""; ps.CenterFooter = ""; ps.RightFooter = ""; } //end of header and footer setup { //begin of margin ps.LeftMargin = oXL.InchesToPoints(0.7); ps.RightMargin = oXL.InchesToPoints(0.7); ps.TopMargin = oXL.InchesToPoints(0.78); ps.BottomMargin = oXL.InchesToPoints(0.78); ps.HeaderMargin = oXL.InchesToPoints(0.3); ps.FooterMargin = oXL.InchesToPoints(0.3); } //end of margin { //etcs ps.PrintHeadings = false; ps.PrintGridlines = false; ps.PrintComments = Excel.XlPrintLocation.xlPrintNoComments; ps.CenterHorizontally = false; ps.CenterVertically = false; ps.Orientation = Excel.XlPageOrientation.xlLandscape; ps.Draft = false; ps.PaperSize = Excel.XlPaperSize.xlPaperA4; ps.FirstPageNumber = (int)Excel.Constants.xlAutomatic; ps.Order = Excel.XlOrder.xlDownThenOver; ps.BlackAndWhite = false; ps.Zoom = false; ps.PrintErrors = Excel.XlPrintErrors.xlPrintErrorsDisplayed; ps.OddAndEvenPagesHeaderFooter = false; ps.DifferentFirstPageHeaderFooter = false; ps.ScaleWithDocHeaderFooter = true; ps.AlignMarginsHeaderFooter = true; } //etcs ps.FitToPagesWide = 1; ps.FitToPagesTall = false; oXL.PrintCommunication = true; } }
static void PrintExcelToPDF(string ExcelFilePath) { //Create a UDC object and get its interfaces IUDC objUDC = new APIWrapper(); IUDCPrinter Printer = objUDC.get_Printers("Universal Document Converter"); IProfile Profile = Printer.Profile; //Use Universal Document Converter API to change settings of converterd document Profile.PageSetup.ResolutionX = 600; Profile.PageSetup.ResolutionY = 600; Profile.FileFormat.ActualFormat = FormatID.FMT_PDF; Profile.FileFormat.PDF.ColorSpace = ColorSpaceID.CS_TRUECOLOR; Profile.FileFormat.PDF.Multipage = MultipageModeID.MM_MULTI; Profile.OutputLocation.Mode = LocationModeID.LM_PREDEFINED; Profile.OutputLocation.FolderPath = @"c:\UDC Output Files"; Profile.OutputLocation.FileName = @"&[DocName(0)] -- &[Date(0)] -- &[Time(0)].&[ImageType]"; Profile.OutputLocation.OverwriteExistingFile = false; Profile.PostProcessing.Mode = PostProcessingModeID.PP_OPEN_FOLDER; //Create a Excel's Application object Excel.Application ExcelApp = new Excel.ApplicationClass(); Object ReadOnly = true; Object Missing = Type.Missing; //This will be passed when ever we don’t want to pass value //If you run an English version of Excel on a computer with the regional settings are configured for a non-English language, you must set the CultureInfo prior calling Excel methods. System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); //Open the document from a file Excel.Workbook Workbook = ExcelApp.Workbooks.Open(ExcelFilePath, Missing, ReadOnly, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing); //Change active worksheet settings and print it Excel.Worksheet Worksheet = (Excel.Worksheet)Workbook.ActiveSheet; Excel.PageSetup PageSetup = Worksheet.PageSetup; PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape; Object Preview = false; Worksheet.PrintOut(Missing, Missing, Missing, Preview, "Universal Document Converter", Missing, Missing, Missing); //Close the spreadsheet without saving changes Object SaveChanges = false; Workbook.Close(SaveChanges, Missing, Missing); //Close Microsoft Excel ExcelApp.Quit(); }
public static void SetPrintSettings(Excel.Worksheet sheet) { xlApp.PrintCommunication = false; Excel.PageSetup pageSetup = (Excel.PageSetup)sheet.PageSetup; pageSetup.BottomMargin = xlApp.CentimetersToPoints(0.0); pageSetup.TopMargin = xlApp.CentimetersToPoints(0.5); pageSetup.LeftMargin = xlApp.CentimetersToPoints(0.7); pageSetup.RightMargin = xlApp.CentimetersToPoints(0.5); pageSetup.HeaderMargin = xlApp.CentimetersToPoints(0.0); pageSetup.FooterMargin = xlApp.CentimetersToPoints(0.0); pageSetup.PrintArea = "A:Z"; pageSetup.Zoom = 100; xlApp.PrintCommunication = true; SetPageSpecificSettings(sheet, pageSetup); }
private void button6_Click(object sender, EventArgs e) { Excel.Application app = new Excel.Application(); string Filestr = $@"C:\hw4\{textBox8.Text}"; app.Visible = true; Excel.Workbook workbook = app.Workbooks.Open(Filestr); Excel.Worksheet worksheet = (Excel.Worksheet)workbook.ActiveSheet; //app.Workbooks.Open(Filestr); Excel.PageSetup pageSetup = worksheet.PageSetup; pageSetup.Orientation = Excel.XlPageOrientation.xlLandscape; workbook.Save(); worksheet.PrintOutEx(); workbook.Close(); app.Quit(); }
public void SetPrintAreaToTable(Excel.Worksheet oWS, string strTblName) { RAIIFuncs rFuncs = new RAIIFuncs(); using (RAIICont _raiiCont = new RAIICont()) { Excel.ListObjects oLOs = oWS.ListObjects; _raiiCont.Push(rFuncs.GetFunc(oLOs)); Excel.ListObject oLO = oLOs[strTblName]; _raiiCont.Push(rFuncs.GetFunc(oLO)); Excel.Range rngLO = oLO.Range; _raiiCont.Push(rFuncs.GetFunc(rngLO)); string prntArea = rngLO.Address; Excel.PageSetup pageSetup = oWS.PageSetup; //_raiiCont.Push(rFuncs.GetFunc(pageSetup)); pageSetup.PrintArea = prntArea; } }
private void BtnExcel_Click(object sender, EventArgs e) { DTOFactory.Action(); try { DataTable dt_orderMaster = DS.Tables["ds_master"]; DataTable dt_orderList = DS.Tables["ds_list"]; string path = @"c:\temp\" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx"; Excel.Application app = new Excel.Application(); Excel.Workbook work = app.Workbooks.Add(); Excel._Worksheet sheet = (Excel._Worksheet)app.ActiveSheet; app.ActiveWindow.DisplayGridlines = false; #region width 정의 ((Excel.Range)sheet.Cells[1, 1]).EntireColumn.ColumnWidth = 3; ((Excel.Range)sheet.Cells[1, 2]).EntireColumn.ColumnWidth = 8; ((Excel.Range)sheet.Cells[1, 3]).EntireColumn.ColumnWidth = 9; ((Excel.Range)sheet.Cells[1, 4]).EntireColumn.ColumnWidth = 2; ((Excel.Range)sheet.Cells[1, 5]).EntireColumn.ColumnWidth = 4; ((Excel.Range)sheet.Cells[1, 6]).EntireColumn.ColumnWidth = 5.88; ((Excel.Range)sheet.Cells[1, 7]).EntireColumn.ColumnWidth = 5; ((Excel.Range)sheet.Cells[1, 8]).EntireColumn.ColumnWidth = 5; ((Excel.Range)sheet.Cells[1, 9]).EntireColumn.ColumnWidth = 10; ((Excel.Range)sheet.Cells[1, 10]).EntireColumn.ColumnWidth = 10; ((Excel.Range)sheet.Cells[1, 11]).EntireColumn.ColumnWidth = 10.88; #endregion #region 발주서 제목 //height ((Excel.Range)sheet.Cells[1, 1]).EntireRow.RowHeight = 15; ((Excel.Range)sheet.Cells[2, 1]).EntireRow.RowHeight = 33; ((Excel.Range)sheet.Cells[3, 1]).EntireRow.RowHeight = 15; ((Excel.Range)sheet.Cells[4, 1]).EntireRow.RowHeight = 33; ((Excel.Range)sheet.Cells[5, 1]).EntireRow.RowHeight = 13.5; //셀 병합 sheet.Range["A1", "B1"].Merge(); sheet.Range["A2", "B2"].Merge(); sheet.Range["C1", "J2"].Merge(); sheet.Range["A3", "B3"].Merge(); sheet.Range["A4", "B4"].Merge(); sheet.Range["D3", "E3"].Merge(); sheet.Range["D4", "E4"].Merge(); sheet.Range["F3", "J3"].Merge(); sheet.Range["F4", "J4"].Merge(); //색지정 sheet.Range["A1", "B1"].Interior.Color = System.Drawing.Color.FromArgb(200, 200, 200); sheet.Range["A3", "K3"].Interior.Color = System.Drawing.Color.FromArgb(200, 200, 200); //BorderLine sheet.Range["A1", "B2"].Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range["A1", "B2"].Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range["A1", "B2"].Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range["A1", "B2"].Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range["A1", "B2"].Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range["A1", "B2"].Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range["A3", "K4"].Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range["A3", "K4"].Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range["A3", "K4"].Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range["A3", "K4"].Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range["A3", "K4"].Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range["A3", "K4"].Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range["A1", "K7"].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; sheet.Range["F4", "J4"].HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; //font sheet.Range["A1", "B1"].Font.Bold = true; sheet.Range["A3", "K3"].Font.Bold = true; sheet.Range["A1", "K4"].Font.Size = 10; sheet.Range["C1", "J2"].Font.Size = 24; sheet.Range["A4", "K4"].WrapText = true; sheet.Range["A2", "B2"].NumberFormat = 0; //데이터 입력 sheet.Cells[1, 1] = "발주번호"; sheet.Cells[1, 3] = "발 주 서"; sheet.Cells[3, 1] = "발주일자"; sheet.Cells[3, 3] = "지불조건"; sheet.Cells[3, 4] = "사업장"; sheet.Cells[3, 6] = "납품장소"; sheet.Cells[3, 11] = "납기일"; sheet.Cells[2, 1] = dt_orderMaster.Rows[0]["DOC_ID"].ToString(); sheet.Cells[4, 1] = dt_orderMaster.Rows[0]["CRT_DT"].ToString(); sheet.Cells[4, 3] = dt_orderMaster.Rows[0]["COST_TYP"].ToString(); sheet.Cells[4, 4] = dt_orderMaster.Rows[0]["COMP_NM"].ToString(); sheet.Cells[4, 6] = dt_orderMaster.Rows[0]["ADDR"].ToString(); sheet.Cells[4, 11] = dt_orderMaster.Rows[0]["ORDER_DT"].ToString(); //Logo FileInfo flLogo = new FileInfo(@"C:\Temp\" + dt_orderMaster.Rows[0]["LOGO"].ToString()); ImageGet logo = new ImageGet(dt_orderMaster.Rows[0]["LOGO"].ToString()); using (FileStream stream = new FileStream(flLogo.FullName, FileMode.Create, FileAccess.Write)) { byte[] buffer = logo.GetImageBlock(); stream.Write(buffer, 0, buffer.Length); } sheet.Shapes.AddPicture(flLogo.FullName, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue , 388, 2, 90, 45); flLogo.Delete(); #endregion #region 공급사 내용 //height ((Excel.Range)sheet.Cells[6, 1]).EntireRow.RowHeight = 15; ((Excel.Range)sheet.Cells[7, 1]).EntireRow.RowHeight = 33; ((Excel.Range)sheet.Cells[8, 1]).EntireRow.RowHeight = 13.5; //셀병합 sheet.Range["A6", "B6"].Merge(); sheet.Range["A7", "B7"].Merge(); sheet.Range["C6", "H6"].Merge(); sheet.Range["C7", "H7"].Merge(); //색지정 sheet.Range["A6", "K6"].Interior.Color = System.Drawing.Color.FromArgb(200, 200, 200); //Font sheet.Range["A6", "K7"].Font.Size = 10; sheet.Range["A6", "K6"].Font.Bold = true; sheet.Range["C7", "H7"].WrapText = true; //데이터 입력 sheet.Cells[6, 1] = "공급사"; sheet.Cells[6, 3] = "주소"; sheet.Cells[6, 9] = "대표"; sheet.Cells[6, 10] = "직위"; sheet.Cells[6, 11] = "연락처"; sheet.Cells[7, 1] = dt_orderMaster.Rows[0]["VENDOR_NM"].ToString(); sheet.Cells[7, 3] = dt_orderMaster.Rows[0]["VENDOR_ADDR"].ToString(); sheet.Cells[7, 9] = dt_orderMaster.Rows[0]["VENDOR_USER_NM"].ToString(); sheet.Cells[7, 10] = dt_orderMaster.Rows[0]["VENDOR_GRADE_NM"].ToString(); sheet.Cells[7, 11] = dt_orderMaster.Rows[0]["VENDOR_TEL"].ToString(); //BorderLine sheet.Range["A6", "K7"].Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range["A6", "K7"].Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range["A6", "K7"].Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range["A6", "K7"].Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range["A6", "K7"].Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range["A6", "K7"].Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range["A6", "K7"].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; sheet.Range["C7", "H7"].HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; #endregion #region 물품 //height ((Excel.Range)sheet.Cells[9, 1]).EntireRow.RowHeight = 15; //병합 sheet.Range["C9", "D9"].Merge(); sheet.Range["E9", "F9"].Merge(); //BorderLine sheet.Range["A9", "K9"].Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range["A9", "K9"].Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range["A9", "K9"].Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range["A9", "K9"].Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range["A9", "K9"].Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range["A9", "K9"].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //색지정 sheet.Range["A9", "K9"].Interior.Color = System.Drawing.Color.FromArgb(200, 200, 200); //Font sheet.Range["A9", "K9"].Font.Size = 10; sheet.Range["A9", "K9"].Font.Bold = true; //입력 sheet.Cells[9, 1] = "No."; sheet.Cells[9, 2] = "자재코드"; sheet.Cells[9, 3] = "품명"; sheet.Cells[9, 5] = "규격"; sheet.Cells[9, 7] = "수량"; sheet.Cells[9, 8] = "단위"; sheet.Cells[9, 9] = "단가"; sheet.Cells[9, 10] = "금액"; sheet.Cells[9, 11] = "비고"; int i = 0; for (; i < dt_orderList.Rows.Count; i++) { ((Excel.Range)sheet.Cells[10 + i, 1]).EntireRow.RowHeight = 30; //병합 sheet.Range[string.Format("C{0}", 10 + i), string.Format("D{0}", 10 + i)].Merge(); sheet.Range[string.Format("E{0}", 10 + i), string.Format("F{0}", 10 + i)].Merge(); //BorderLine sheet.Range[string.Format("A{0}", 10 + i), string.Format("K{0}", 10 + i)].Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range[string.Format("A{0}", 10 + i), string.Format("K{0}", 10 + i)].Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range[string.Format("A{0}", 10 + i), string.Format("K{0}", 10 + i)].Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range[string.Format("A{0}", 10 + i), string.Format("K{0}", 10 + i)].Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range[string.Format("A{0}", 10 + i), string.Format("K{0}", 10 + i)].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; sheet.Range[string.Format("I{0}", 10 + i), string.Format("J{0}", 10 + i)].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight; sheet.Range[string.Format("I{0}", 10 + i), string.Format("J{0}", 10 + i)].NumberFormat = "\\ #,##0\"원\""; //Font sheet.Range[string.Format("B{0}", 10 + i), string.Format("K{0}", 10 + i)].Font.Size = 8; sheet.Range[string.Format("B{0}", 10 + i), string.Format("K{0}", 10 + i)].WrapText = true; //데이터 입력 sheet.Cells[10 + i, 1] = i + 1.ToString(); sheet.Cells[10 + i, 2] = dt_orderList.Rows[i]["MODEL_ID"].ToString(); sheet.Cells[10 + i, 3] = dt_orderList.Rows[i]["MODEL_NM"].ToString(); sheet.Cells[10 + i, 5] = dt_orderList.Rows[i]["SPEC_VAL"].ToString(); sheet.Cells[10 + i, 7] = dt_orderList.Rows[i]["QTY"].ToString(); sheet.Cells[10 + i, 8] = dt_orderList.Rows[i]["QTY_CD"].ToString(); sheet.Cells[10 + i, 9] = dt_orderList.Rows[i]["UNIT_COST"].ToString(); sheet.Cells[10 + i, 10] = dt_orderList.Rows[i]["COST"].ToString(); } //빈칸 그리드 for (; i < 16; i++) { ((Excel.Range)sheet.Cells[10 + i, 1]).EntireRow.RowHeight = 30; //병합 sheet.Range[string.Format("C{0}", 10 + i), string.Format("D{0}", 10 + i)].Merge(); sheet.Range[string.Format("E{0}", 10 + i), string.Format("F{0}", 10 + i)].Merge(); //BorderLine sheet.Range[string.Format("A{0}", 10 + i), string.Format("K{0}", 10 + i)].Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range[string.Format("A{0}", 10 + i), string.Format("K{0}", 10 + i)].Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range[string.Format("A{0}", 10 + i), string.Format("K{0}", 10 + i)].Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range[string.Format("A{0}", 10 + i), string.Format("K{0}", 10 + i)].Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; } #endregion #region 합계 ((Excel.Range)sheet.Cells[26, 1]).EntireRow.RowHeight = 30; sheet.Range["A26", "F26"].Merge(); sheet.Range["G26", "H26"].Merge(); sheet.Range["I26", "J26"].Merge(); sheet.Range["A26", "K26"].Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range["A26", "K26"].Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range["A26", "K26"].Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range["A26", "K26"].Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range["A26", "K26"].Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range["A26", "K26"].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; sheet.Range["G26", "J26"].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight; sheet.Range["I26", "J26"].NumberFormat = "\\ #,##0\"원\""; sheet.Cells[26, 1] = "합 계(부가세 별도)"; sheet.Cells[26, 7] = "=sum(G10:G25)"; sheet.Cells[26, 9] = "=sum(J10:J25)"; #endregion Excel.PageSetup ps = sheet.PageSetup; ps.LeftMargin = 53.4; ps.RightMargin = 53.4; ps.HeaderMargin = 22.8; ps.FooterMargin = 22.8; ps.TopMargin = 57.3; ps.BottomMargin = 57.3; work.SaveAs(path, Excel.XlFileFormat.xlWorkbookDefault); work.Close(); app.Quit(); DTOFactory.Complete(); //2021-02-19 hsh 완료 popup 대신 실행 기능으로 변경 System.Diagnostics.Process.Start(path); } catch (Exception ex) { DTOFactory.Complete(); ViewMessage.Error(ex.Message); } }
public void ExcelExport(object sender, DataGridViewCellEventArgs e) { DataGridView view = (DataGridView)sender; DataGridViewRow ViewRow = view.Rows[e.RowIndex]; int row; for (row = 0; row < dt_Group.Rows.Count; row++) { if (ViewRow.Cells["DOC_ID"].Value.ToString() == dt_Group.Rows[row]["DOC_ID"].ToString()) { break; } } DTOFactory.Action(); try { ClearSearchData(); SetSearchData("DOC_ID", txtDocId.Text); SetServiceId("SendEstimateList"); DTOFactory.Transaction(new ReportDTO()); string path = @"c:\temp\" + dt_Group.Rows[row]["SUBJECT"].ToString() + ".xlsx"; DateTime tm = DateTime.Parse(dt_Group.Rows[row]["CRT_TM"].ToString()); Excel.Application app = new Excel.Application(); Excel.Workbook work = app.Workbooks.Add(); Excel._Worksheet sheet = (Excel._Worksheet)app.ActiveSheet; app.ActiveWindow.DisplayGridlines = false; #region 수정본 #region Width 정의 ((Excel.Range)sheet.Cells[1, 1]).EntireColumn.ColumnWidth = 4.3; ((Excel.Range)sheet.Cells[1, 2]).EntireColumn.ColumnWidth = 4.3; ((Excel.Range)sheet.Cells[1, 3]).EntireColumn.ColumnWidth = 8.38; ((Excel.Range)sheet.Cells[1, 4]).EntireColumn.ColumnWidth = 8.38; ((Excel.Range)sheet.Cells[1, 5]).EntireColumn.ColumnWidth = 8.38; ((Excel.Range)sheet.Cells[1, 6]).EntireColumn.ColumnWidth = 9.75; ((Excel.Range)sheet.Cells[1, 7]).EntireColumn.ColumnWidth = 4.3; ((Excel.Range)sheet.Cells[1, 8]).EntireColumn.ColumnWidth = 4.3; ((Excel.Range)sheet.Cells[1, 9]).EntireColumn.ColumnWidth = 5.5; ((Excel.Range)sheet.Cells[1, 10]).EntireColumn.ColumnWidth = 5.5; ((Excel.Range)sheet.Cells[1, 11]).EntireColumn.ColumnWidth = 8.38; ((Excel.Range)sheet.Cells[1, 12]).EntireColumn.ColumnWidth = 8.38; #endregion #region 견적서 HEAD #region Logo FileInfo flLogo = new FileInfo(@"C:\Temp\" + DTOFactory.CompLogo.ToString()); ImageGet logo = new ImageGet(DTOFactory.CompLogo.ToString()); using (FileStream stream = new FileStream(flLogo.FullName, FileMode.Create, FileAccess.Write)) { byte[] buffer = logo.GetImageBlock(); stream.Write(buffer, 0, buffer.Length); } sheet.Shapes.AddPicture(flLogo.FullName, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue , 0, 0, 90, 45); flLogo.Delete(); #endregion endLogo //Height int i, j = 0; for (i = 1; i < 8; i++) { ((Excel.Range)sheet.Cells[i, 1]).EntireRow.RowHeight = 16.5; } ((Excel.Range)sheet.Cells[8, 1]).EntireRow.RowHeight = 8.25; ((Excel.Range)sheet.Cells[9, 1]).EntireRow.RowHeight = 4; ((Excel.Range)sheet.Cells[10, 1]).EntireRow.RowHeight = 21; ((Excel.Range)sheet.Cells[11, 1]).EntireRow.RowHeight = 30.8; ((Excel.Range)sheet.Cells[12, 1]).EntireRow.RowHeight = 16.5; //셀 병합 sheet.Range["I6", "J6"].Merge(); sheet.Range["K6", "L6"].Merge(); sheet.Range["I7", "J7"].Merge(); sheet.Range["K7", "L7"].Merge(); sheet.Range["A11", "L11"].Merge(); sheet.Range["A9", "I9"].Merge(); sheet.Range["J9", "L9"].Merge(); sheet.Range["A9", "I9"].Interior.Color = Color.FromArgb(87, 87, 87); sheet.Range["J9", "L9"].Interior.Color = Color.FromArgb(233, 79, 54); sheet.Range["I6", "L7"].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; sheet.Range["A11", "L11"].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //데이터 입력 sheet.Cells[4, 1] = "주식회사 엠와이씨"; sheet.Cells[5, 1] = "충북 청주시 서원구 충대로 1 S21-1동 214호"; sheet.Cells[6, 1] = "Tel 043)904-9300"; sheet.Cells[6, 4] = "Fax 043)904-9301"; sheet.Cells[6, 9] = "발급자"; sheet.Cells[6, 11] = dt_Group.Rows[row]["USER_NM"].ToString(); sheet.Cells[7, 9] = "발급일"; sheet.Cells[7, 11] = tm.ToString("yyyy.MM.dd"); sheet.Cells[7, 1] = "발행번호 : " + dt_Group.Rows[row]["DOC_ID"].ToString(); sheet.Cells[11, 1] = "견 적 서"; sheet.Range["A11", "L11"].Font.Bold = true;; sheet.Range["A11", "L11"].Font.Size = 20; #endregion #region 업체 정보 sheet.Range["A13", "E13"].Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous; for (i = 13; i < 18; i++) { ((Excel.Range)sheet.Cells[i, 1]).EntireRow.RowHeight = 21; } for (i = 13; i < 18; i++) { sheet.Range[string.Format("A{0}", i), string.Format("B{0}", i)].Merge(); sheet.Range[string.Format("C{0}", i), string.Format("E{0}", i)].Merge(); sheet.Range[string.Format("A{0}", i), string.Format("B{0}", i)].Interior.Color = Color.FromArgb(242, 242, 242); sheet.Range[string.Format("A{0}", i), string.Format("E{0}", i)].Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range[string.Format("A{0}", i), string.Format("E{0}", i)].Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range[string.Format("A{0}", i), string.Format("E{0}", i)].Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range[string.Format("A{0}", i), string.Format("E{0}", i)].Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range[string.Format("A{0}", i), string.Format("E{0}", i)].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; } sheet.Cells[13, 1] = "견적일자"; sheet.Cells[13, 3] = tm.ToString("yy.MM.dd"); sheet.Cells[14, 1] = "제 출 처"; sheet.Cells[14, 3] = dt_Group.Rows[row]["CST_COMP_NM"].ToString(); sheet.Cells[15, 1] = "담 당 자"; sheet.Cells[15, 3] = dt_Group.Rows[row]["CST_USER_NM"].ToString() + "님"; sheet.Cells[16, 1] = "PROJECT"; sheet.Range["C16", "E16"].Font.Size = 10; sheet.Cells[16, 3] = dt_Group.Rows[row]["PRO_NM"].ToString(); sheet.Cells[17, 1] = "유효기간"; sheet.Cells[17, 3] = "발행후 3개월"; #endregion #region 회사 정보 sheet.Range["G13", "L13"].Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous; for (i = 13; i < 18; i++) { sheet.Range[string.Format("G{0}", i), string.Format("H{0}", i)].Merge(); sheet.Range[string.Format("I{0}", i), string.Format("L{0}", i)].Merge(); sheet.Range[string.Format("G{0}", i), string.Format("H{0}", i)].Interior.Color = Color.FromArgb(242, 242, 242); sheet.Range[string.Format("G{0}", i), string.Format("L{0}", i)].Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range[string.Format("G{0}", i), string.Format("L{0}", i)].Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range[string.Format("G{0}", i), string.Format("L{0}", i)].Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range[string.Format("G{0}", i), string.Format("L{0}", i)].Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range[string.Format("G{0}", i), string.Format("L{0}", i)].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; } sheet.Range["G13", "H13"].Font.Size = 10; sheet.Cells[13, 7] = "사업자번호"; sheet.Cells[13, 9] = dt_Group.Rows[row]["REG_ID"].ToString(); sheet.Cells[14, 7] = "상 호 명"; sheet.Cells[14, 9] = dt_Group.Rows[row]["COMP_NM"].ToString(); sheet.Cells[15, 7] = "대표자명"; sheet.Cells[15, 9] = dt_Group.Rows[row]["COMP_USER_NM"].ToString(); sheet.Cells[16, 7] = "업 태"; sheet.Cells[16, 9] = dt_Group.Rows[row]["COMP_TYP_1"].ToString(); sheet.Cells[17, 7] = "업 종"; sheet.Cells[17, 9] = dt_Group.Rows[row]["COMP_TYP_2"].ToString(); #endregion #region Body sheet.Range["A18", "B18"].Merge(); sheet.Range["C18", "E18"].Merge(); sheet.Range["G18", "H18"].Merge(); sheet.Range["I18", "K18"].Merge(); sheet.Cells[18, 1] = "발주금액"; sheet.Cells[18, 6] = "원정"; sheet.Cells[18, 7] = @"(\"; sheet.Cells[18, 12] = ")"; sheet.Range["I18", "K18"].NumberFormat = "\\ #,##0"; sheet.Range["A18", "E18"].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; sheet.Range["G18", "H18"].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; sheet.Range["L18", "L18"].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight; sheet.Range["L19", "L19"].Font.Size = 8; sheet.Cells[19, 12] = "(단위 : KRW)"; for (i = 21; i < 31; i++) { ((Excel.Range)sheet.Cells[i, 1]).EntireRow.RowHeight = 22.5; } if (!chkVAT.Checked) { string cost = (int.Parse(dt_Group.Rows[row]["COST"].ToString()) - int.Parse(dt_Group.Rows[row]["VAT"].ToString())).ToString(); sheet.Cells[18, 3] = ClsSpread.GetPriceNumtoHan(cost); sheet.Cells[18, 9] = cost; for (i = 20, j = 0; i < 32; i++, j++) { sheet.Range[string.Format("B{0}", i), string.Format("D{0}", i)].Merge(); sheet.Range[string.Format("G{0}", i), string.Format("J{0}", i)].Merge(); sheet.Range[string.Format("K{0}", i), string.Format("L{0}", i)].Merge(); sheet.Range[string.Format("K{0}", i), string.Format("L{0}", i)].Font.Size = 8; sheet.Range[string.Format("K{0}", i), string.Format("L{0}", i)].WrapText = true; sheet.Range[string.Format("A{0}", i), string.Format("A{0}", i)].Interior.Color = Color.FromArgb(242, 242, 242); sheet.Range[string.Format("A{0}", i), string.Format("E{0}", i)].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; sheet.Range[string.Format("K{0}", i), string.Format("L{0}", i)].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; sheet.Cells[i, 1] = j; if (i == 20) { sheet.Range[string.Format("A{0}", i), string.Format("L{0}", i)].Interior.Color = Color.FromArgb(242, 242, 242); sheet.Cells[i, 1] = "NO."; sheet.Cells[i, 2] = "품명"; sheet.Cells[i, 5] = "수량"; sheet.Cells[i, 6] = "단가"; sheet.Cells[i, 7] = "합계"; sheet.Cells[i, 11] = "비고"; } else if (i == 31) { sheet.Range[string.Format("A{0}", i), string.Format("D{0}", i)].Merge(); sheet.Range[string.Format("E{0}", i), string.Format("L{0}", i)].Merge(); sheet.Range[string.Format("E{0}", i), string.Format("L{0}", i)].NumberFormat = "_-₩* #,##0_-;-₩* #,##0_-;_-₩* \"-\"_-;_-@_-"; sheet.Range[string.Format("A{0}", i), string.Format("D{0}", i)].Interior.Color = Color.FromArgb(242, 242, 242); sheet.Range[string.Format("A{0}", i), string.Format("L{0}", i)].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; sheet.Cells[i, 1] = "합계"; sheet.Cells[i, 5] = cost; } if (j <= dt_List.Rows.Count - 1) { sheet.Cells[i + 1, 2] = dt_List.Rows[j]["MODEL_DESC"].ToString(); sheet.Cells[i + 1, 5] = dt_List.Rows[j]["QTY"].ToString() + dt_List.Rows[j]["QTY_NM"].ToString(); sheet.Range[string.Format("F{0}", i + 1), string.Format("J{0}", i + 1)].NumberFormat = "\\ #,##0"; sheet.Cells[i + 1, 6] = dt_List.Rows[j]["UNIT_COST"].ToString(); sheet.Cells[i + 1, 7] = int.Parse("".Equals(dt_List.Rows[j]["QTY"].ToString()) ? "0" : dt_List.Rows[j]["QTY"].ToString()) * int.Parse("".Equals(dt_List.Rows[j]["UNIT_COST"].ToString()) ? "0" : dt_List.Rows[j]["UNIT_COST"].ToString()); sheet.Cells[i + 1, 11] = dt_List.Rows[j]["ITM_DESC"].ToString(); } } } else //부가세 포함 { sheet.Cells[18, 3] = ClsSpread.GetPriceNumtoHan(dt_Group.Rows[row]["COST"].ToString()); sheet.Cells[18, 9] = dt_Group.Rows[row]["COST"].ToString(); for (i = 20, j = 0; i < 33; i++, j++) { sheet.Range[string.Format("B{0}", i), string.Format("D{0}", i)].Merge(); sheet.Range[string.Format("G{0}", i), string.Format("H{0}", i)].Merge(); sheet.Range[string.Format("I{0}", i), string.Format("J{0}", i)].Merge(); sheet.Range[string.Format("K{0}", i), string.Format("L{0}", i)].Merge(); sheet.Range[string.Format("K{0}", i), string.Format("L{0}", i)].Font.Size = 8; sheet.Range[string.Format("K{0}", i), string.Format("L{0}", i)].WrapText = true; sheet.Range[string.Format("A{0}", i), string.Format("A{0}", i)].Interior.Color = Color.FromArgb(242, 242, 242); sheet.Range[string.Format("A{0}", i), string.Format("E{0}", i)].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; sheet.Range[string.Format("K{0}", i), string.Format("L{0}", i)].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; sheet.Cells[i, 1] = j; if (i == 20) { sheet.Range[string.Format("A{0}", i), string.Format("L{0}", i)].Interior.Color = Color.FromArgb(242, 242, 242); sheet.Cells[i, 1] = "NO."; sheet.Cells[i, 2] = "품명"; sheet.Cells[i, 5] = "수량"; sheet.Cells[i, 6] = "단가"; sheet.Cells[i, 7] = "부가세"; sheet.Cells[i, 9] = "합계"; sheet.Cells[i, 11] = "비고"; } else if (i == 31) { sheet.Range[string.Format("A{0}", i), string.Format("D{0}", i)].Merge(); sheet.Range[string.Format("E{0}", i), string.Format("L{0}", i)].Merge(); sheet.Range[string.Format("E{0}", i), string.Format("L{0}", i)].NumberFormat = "_-₩* #,##0_-;-₩* #,##0_-;_-₩* \"-\"_-;_-@_-"; sheet.Range[string.Format("A{0}", i), string.Format("D{0}", i)].Interior.Color = Color.FromArgb(242, 242, 242); sheet.Range[string.Format("A{0}", i), string.Format("L{0}", i)].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; sheet.Cells[i, 1] = "부가세"; sheet.Cells[i, 5] = dt_Group.Rows[row]["VAT"].ToString(); } else if (i == 32) { sheet.Range[string.Format("A{0}", i), string.Format("D{0}", i)].Merge(); sheet.Range[string.Format("E{0}", i), string.Format("L{0}", i)].Merge(); sheet.Range[string.Format("E{0}", i), string.Format("L{0}", i)].NumberFormat = "_-₩* #,##0_-;-₩* #,##0_-;_-₩* \"-\"_-;_-@_-"; sheet.Range[string.Format("A{0}", i), string.Format("D{0}", i)].Interior.Color = Color.FromArgb(242, 242, 242); sheet.Range[string.Format("A{0}", i), string.Format("L{0}", i)].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; sheet.Cells[i, 1] = "합계"; sheet.Cells[i, 5] = dt_Group.Rows[row]["COST"].ToString(); } if (j <= dt_List.Rows.Count - 1) { sheet.Cells[i + 1, 2] = dt_List.Rows[j]["MODEL_DESC"].ToString(); sheet.Cells[i + 1, 5] = dt_List.Rows[j]["QTY"].ToString() + dt_List.Rows[j]["QTY_NM"].ToString(); sheet.Range[string.Format("F{0}", i + 1), string.Format("J{0}", i + 1)].NumberFormat = "\\ #,##0"; sheet.Cells[i + 1, 6] = dt_List.Rows[j]["UNIT_COST"].ToString(); sheet.Cells[i + 1, 7] = dt_List.Rows[j]["VAT"].ToString(); sheet.Cells[i + 1, 9] = int.Parse("".Equals(dt_List.Rows[j]["QTY"].ToString()) ? "0" : dt_List.Rows[j]["QTY"].ToString()) * (int.Parse("".Equals(dt_List.Rows[j]["UNIT_COST"].ToString()) ? "0" : dt_List.Rows[j]["UNIT_COST"].ToString()) + int.Parse("".Equals(dt_List.Rows[j]["VAT"].ToString()) ? "0" : dt_List.Rows[j]["VAT"].ToString())); sheet.Cells[i + 1, 11] = dt_List.Rows[j]["ITM_DESC"].ToString(); } } } sheet.Range["A20", "L20"].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; sheet.Range["A20", "L20"].Font.Size = 11; #endregion #region Bottom sheet.Range[string.Format("A{0}", i), string.Format("L{0}", i)].Merge(); sheet.Range[string.Format("A{0}", i + 1), string.Format("L{0}", i + 3)].Merge(); sheet.Range[string.Format("A{0}", i + 4), string.Format("B{0}", i + 4)].Merge(); sheet.Range[string.Format("E{0}", i + 4), string.Format("F{0}", i + 4)].Merge(); sheet.Range[string.Format("I{0}", i + 4), string.Format("L{0}", i + 4)].Merge(); sheet.Range[string.Format("A{0}", i), string.Format("L{0}", i)].Interior.Color = Color.FromArgb(242, 242, 242); sheet.Range[string.Format("A{0}", i), string.Format("L{0}", i)].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; sheet.Range[string.Format("A{0}", i + 4), string.Format("B{0}", i + 4)].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; sheet.Range[string.Format("D{0}", i + 4), string.Format("D{0}", i + 4)].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; sheet.Range[string.Format("I{0}", i + 4), string.Format("L{0}", i + 4)].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; sheet.Cells[i, 1] = "특이사항"; for (j = 31; j < i + 4; j++) { ((Excel.Range)sheet.Cells[j, 1]).EntireRow.RowHeight = 21.7; } sheet.Range["A20", string.Format("L{0}", i + 4)].Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range["A20", string.Format("L{0}", i + 4)].Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range["A20", string.Format("L{0}", i + 4)].Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range["A20", string.Format("L{0}", i + 4)].Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range["A20", string.Format("L{0}", i + 4)].Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range["A20", string.Format("L{0}", i + 3)].Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; string strDesc = ""; int no = 1; if (!"".Equals(dt_Group.Rows[row]["CASH_NM"].ToString())) { strDesc += string.Format("{0}. 대금 지급조건: {1}\r\n", no++, dt_Group.Rows[row]["CASH_NM"]); } if (!"".Equals(dt_Group.Rows[row]["WARR_NM"].ToString())) { strDesc += string.Format("{0}. 유지보수 조건: {1}\r\n", no++, dt_Group.Rows[row]["WARR_NM"]); } strDesc += string.Format("{0}. Project 담담자: {1}\r\n", no++, dt_Group.Rows[row]["EMAIL"]); int pos = strDesc.Length + 1; if (chkVAT.Checked) { strDesc += string.Format("{0}. 부가세 포함", no++); } else { strDesc += string.Format("{0}. 부가세 별도", no++); } sheet.Cells[i + 1, 1] = strDesc; sheet.Cells[i + 4, 1] = "담당자"; sheet.Cells[i + 4, 3] = dt_Group.Rows[row]["USER_NM"].ToString(); sheet.Cells[i + 4, 4] = "연락처"; sheet.Cells[i + 4, 5] = dt_Group.Rows[row]["TEL_1"].ToString(); sheet.Cells[i + 4, 7] = "결제 방법"; sheet.Cells[i + 4, 9] = ("CASH".Equals(dt_Group.Rows[row]["CASH_TYP_CD_2"].ToString()) ? "■" : "□") + "현금 " + ("CARD".Equals(dt_Group.Rows[row]["CASH_TYP_CD_2"].ToString()) ? "■" : "□") + "카드 " + ("ETC".Equals(dt_Group.Rows[row]["CASH_TYP_CD_2"].ToString()) ? "■" : "□") + "기타 "; ((Excel.Range)sheet.Cells[i + 5, 1]).EntireRow.RowHeight = 15.5; ((Excel.Range)sheet.Cells[i + 6, 1]).EntireRow.RowHeight = 13.5; sheet.Range[string.Format("A{0}", i + 6), string.Format("L{0}", i + 6)].Merge(); sheet.Range[string.Format("A{0}", i + 6), string.Format("L{0}", i + 6)].Font.Size = 9; sheet.Range[string.Format("A{0}", i + 6), string.Format("L{0}", i + 6)].Font.Color = Color.White; sheet.Range[string.Format("A{0}", i + 6), string.Format("L{0}", i + 6)].Interior.Color = Color.FromArgb(87, 87, 87); sheet.Range[string.Format("A{0}", i + 6), string.Format("L{0}", i + 6)].Font.Bold = true; sheet.Cells[i + 6, 1] = " SOLUTIONS OF TECHNOLOGY"; #endregion #endregion Excel.PageSetup ps = sheet.PageSetup; ps.LeftMargin = 28; ps.RightMargin = 28; if (chkVAT.Checked) { ps.TopMargin = 41; ps.BottomMargin = 41; } else { ps.TopMargin = 48; ps.BottomMargin = 48; } ps.HeaderMargin = 0; ps.FooterMargin = 0; FileInfo file = new FileInfo(path); if (file.Exists) { file.Delete(); } work.SaveAs(path, Excel.XlFileFormat.xlWorkbookDefault); work.Close(); app.Quit(); System.Diagnostics.Process.Start(path); } catch (Exception ex) { ViewMessage.Error(ex.Message); } DTOFactory.Complete(); ViewGroup(gd_Group, new DataGridViewCellEventArgs(0, e.RowIndex)); }