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