예제 #1
0
        static void makeimgCell_common(int startrow, int startcolum, Excel.Worksheet imgws, Excel.Worksheet copyws, String imgpath, readVO vo, int addcolum)
        {
            int afterrow = startrow;

            Excel.Range selectCell = null;
            //Insert Image
            Excel.Range imgRange = (Excel.Range)imgws.Cells[startrow + 1, startcolum + 1 + addcolum];
            float       Left     = (float)((double)imgRange.Left);
            float       Top      = (float)((double)imgRange.Top);

            imgws.Shapes.AddPicture(imgpath + "\\" + vo.pictureFileNameInExcel + "." + settingText9, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, Left, Top, (float)432, (float)246.8976);
            selectCell = imgws.Range[imgws.Cells[startrow, startcolum + addcolum], imgws.Cells[startrow + 17 - 1, startcolum + 9 + addcolum]];
            selectCell.Merge();
            selectCell.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium);
            selectCell = (Excel.Range)copyws.Cells[vo.orignalImgCell, settingText8];
            copyws.Hyperlinks.Add(selectCell, "#'" + imgws.Name + "'!" + ExcelColumnIndexToName(startcolum + addcolum) + (startrow), Type.Missing, Type.Missing, Type.Missing);
            //1열
            selectCell           = imgws.Range[imgws.Cells[startrow + 17, startcolum + addcolum], imgws.Cells[startrow + 17, startcolum + 1 + addcolum]];
            selectCell.RowHeight = 24;
            selectCell.Merge();
            selectCell.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium);
            selectCell.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            selectCell.Value = "위  치";
            selectCell       = imgws.Range[imgws.Cells[startrow + 17, startcolum + 2 + addcolum], imgws.Cells[startrow + 17, startcolum + 9 + addcolum]];
            selectCell.Merge();
            selectCell.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium);
            selectCell.IndentLevel = 1;
            selectCell.Value       = vo.sub_position + "(" + vo.position + ")";
            //2열
            selectCell           = imgws.Range[imgws.Cells[startrow + 18, startcolum + addcolum], imgws.Cells[startrow + 18, startcolum + 1 + addcolum]];
            selectCell.RowHeight = 24;
            selectCell.Merge();
            selectCell.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium);
            selectCell.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            selectCell.Value = "내  용";
            selectCell       = imgws.Range[imgws.Cells[startrow + 18, startcolum + 2 + addcolum], imgws.Cells[startrow + 18, startcolum + 5 + addcolum]];
            selectCell.Merge();
            selectCell.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium);
            selectCell.IndentLevel = 1;
            selectCell.Value       = vo.content;
            selectCell             = imgws.Range[imgws.Cells[startrow + 18, startcolum + 6 + addcolum], imgws.Cells[startrow + 18, startcolum + 9 + addcolum]];
            selectCell.Merge();
            selectCell.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium);
            selectCell.IndentLevel = 1;
            selectCell.Value       = vo.supply + " / " + vo.ea + " / " + vo.unit + "EA";
        }
예제 #2
0
        // 1 구분 2 경간 3 부재 4 결함종류 5 개소 6 물량 7 단위 8 사진번호
        public static Dictionary <string, List <readVO> > ReadExcelData(string path)
        {
            int settingText1 = GetExcelColumnToInt(Properties.Settings.Default.settingText1);
            int settingText2 = GetExcelColumnToInt(Properties.Settings.Default.settingText2);
            int settingText3 = GetExcelColumnToInt(Properties.Settings.Default.settingText3);
            int settingText4 = GetExcelColumnToInt(Properties.Settings.Default.settingText4);
            int settingText5 = GetExcelColumnToInt(Properties.Settings.Default.settingText5);
            int settingText6 = GetExcelColumnToInt(Properties.Settings.Default.settingText6);

            Dictionary <string, List <readVO> > dictionary = new Dictionary <string, List <readVO> >();


            Excel.Application excelApp = null;
            Excel.Workbook    wb       = null;
            Excel.Worksheet   ws       = null;

            try
            {
                excelApp = new Excel.Application();
                wb       = excelApp.Workbooks.Open(path);
                for (int i = 1; i <= wb.Worksheets.Count; i++)
                {
                    ws = wb.Worksheets.get_Item(i) as Excel.Worksheet;
                    Excel.Range rng = ws.UsedRange;
                    object[,] data = rng.Value;
                    List <readVO> list = new List <readVO>();
                    for (int r = 1; r <= data.GetLength(0); r++)
                    {
                        if (!(data[r, settingText1] == null))
                        {
                            if (data[r, settingText1].ToString().Equals("구분") || data[r, settingText1].ToString().Equals(""))
                            {
                                continue;
                            }
                            else
                            {
                                readVO vo = new readVO();
                                vo.category               = data[r, settingText1] == null ? "구분없음" : data[r, settingText1].ToString();
                                vo.position               = data[r, settingText2] == null ? "경간없음" : data[r, settingText2].ToString();
                                vo.sub_position           = data[r, settingText3] == null ? "부재없음" : data[r, settingText3].ToString();
                                vo.content                = data[r, settingText4] == null ? "결함내용없음" : data[r, settingText4].ToString();
                                vo.unit                   = data[r, settingText5] == null ? "개소없음" : data[r, settingText5].ToString();
                                vo.supply                 = data[r, settingText6] == null ? "물량없음" : data[r, settingText6].ToString();
                                vo.ea                     = data[r, settingText7] == null ? "단위없음" : data[r, settingText7].ToString();
                                vo.pictureFileNameInExcel = data[r, settingText8] == null ? "사진없음" : data[r, settingText8].ToString();
                                vo.orignalImgCell         = data[r, settingText8] == null ? 0 : r;
                                vo.sheetnum               = i;
                                list.Add(vo);
                            }
                        }
                    }
                    if (list != null)
                    {
                        dictionary.Add(ws.Name, list);
                    }
                    ReleaseExcelObject(rng);
                    ReleaseExcelObject(ws);
                }
                //wb.Close(true, null, null);
                ReleaseExcelObject(wb);

                excelApp.DisplayAlerts = false; // 저장할 것인가 확인하지 않도록 설정
                if (excelApp != null)
                {
                    excelApp.Quit();
                    int  hWnd = excelApp.Application.Hwnd;
                    uint processID;
                    GetWindowThreadProcessId((IntPtr)hWnd, out processID);
                    Process[] procs = Process.GetProcessesByName("EXCEL");
                    foreach (Process p in procs)
                    {
                        if (p.Id == processID)
                        {
                            p.Kill();
                        }
                    }
                    Marshal.FinalReleaseComObject(excelApp);
                }
                return(dictionary);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
                MessageBox.Show(ex.ToString(), "오류발생", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return(dictionary);
            }
        }
예제 #3
0
        static void makeImgCell(Excel.Worksheet copyws, Excel.Worksheet imgws, String imgpath, List <readVO> data, int row_select)
        {
            int           startrow     = 1;
            int           startcolum   = 1;
            int           settingText8 = GetExcelColumnToInt(Properties.Settings.Default.settingText8);
            string        settingText9 = Properties.Settings.Default.settingText9;
            List <readVO> checkList    = new List <readVO>();

            foreach (readVO vo in data)
            {
                if (!vo.pictureFileNameInExcel.Equals("사진없음"))
                {
                    checkList.Add(vo);
                }
            }

            if (row_select == 1)
            {
                for (int i = 0; i <= checkList.Count; i++)
                {
                    readVO vo = checkList[i];
                    makeimgCell_common(startrow, startcolum, imgws, copyws, imgpath, vo, 0);
                    startrow += 19;
                }
            }
            else if (row_select == 2)
            {
                for (int i = 0; i < checkList.Count; i++)
                {
                    readVO vo = checkList[i];
                    if (i % 2 == 0)
                    {
                        makeimgCell_common(startrow, startcolum, imgws, copyws, imgpath, vo, 0);
                    }
                    else
                    {
                        makeimgCell_common(startrow, startcolum, imgws, copyws, imgpath, vo, 10);
                        startrow += 19;
                    }
                }
            }
            else if (row_select == 3)
            {
                for (int i = 0; i < checkList.Count; i++)
                {
                    readVO vo = checkList[i];
                    if (data.IndexOf(vo) % 3 == 0)
                    {
                        makeimgCell_common(startrow, startcolum, imgws, copyws, imgpath, vo, 0);
                    }
                    else if (data.IndexOf(vo) % 3 == 1)
                    {
                        makeimgCell_common(startrow, startcolum, imgws, copyws, imgpath, vo, 10);
                    }
                    else
                    {
                        makeimgCell_common(startrow, startcolum, imgws, copyws, imgpath, vo, 20);
                        startrow += 19;
                    }
                }
            }
            else if (row_select == 4)
            {
                for (int i = 0; i < checkList.Count; i++)
                {
                    readVO vo = checkList[i];
                    if (data.IndexOf(vo) % 4 == 0)
                    {
                        makeimgCell_common(startrow, startcolum, imgws, copyws, imgpath, vo, 0);
                    }
                    else if (data.IndexOf(vo) % 4 == 1)
                    {
                        makeimgCell_common(startrow, startcolum, imgws, copyws, imgpath, vo, 10);
                    }
                    else if (data.IndexOf(vo) % 4 == 2)
                    {
                        makeimgCell_common(startrow, startcolum, imgws, copyws, imgpath, vo, 20);
                    }
                    else
                    {
                        makeimgCell_common(startrow, startcolum, imgws, copyws, imgpath, vo, 30);
                        startrow += 19;
                    }
                }
            }
        }