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