ABOUT ME

-

Today
-
Yesterday
-
Total
-
  • 엑셀 테이블에서 공백 컬럼 있는지 체크하는 코드 샘플 c#
    프로그래밍/코드 조각 2018. 6. 16. 12:15
    반응형
    private void btnEmptyCheck_Click(object sender, EventArgs e)
    {
    var app = new Excel.Application();
    try
    {
    var checkedFileNames = CheckedFileNames;
    int count = checkedFileNames.Count;
    int prog = 0;
    foreach (string fileName in checkedFileNames)
    {
    string fullPathName = Path.Combine(Path.GetFullPath(path), fileName);
    CheckEmptySpace(app, fullPathName);
    string completeMessage = fileName + string.Format("의 공백 검사를 마쳤습니다.({0}/{1})", ++prog, count);
    AddListString(completeMessage);
    }
    }
    finally
    {
    app.Quit();
    }
    }

    void CheckEmptySpace(Excel.Application app, string fileName)
    {
    if(!File.Exists(fileName))
    {
    return;
    }

    Excel.Workbook wb = app.Workbooks.Open(fileName, ReadOnly:true);
    try
    {
    if(0 < wb.Worksheets.Count)
    {
    Excel.Worksheet ws = wb.Worksheets[1] as Excel.Worksheet;
    var range = ws.Rows[1] as Excel.Range;
    int firstFieldCount = GetExcelFieldCount(ws, 1);

    int row = 2;
    while (true)
    {
    int fieldCount = GetExcelFieldCount(ws, row);
    if (fieldCount == 0)
    {
    break;
    }

    if (fieldCount < firstFieldCount)
    {
    string messageFormat =
    @"필드 개수가 일치하지 않습니다.
    파일: {3}
    첫 필드 개수: {0}
    현재 필드 개수: {1}
    행: {2}
    ";
    MessageBox.Show(string.Format(messageFormat, firstFieldCount, fieldCount, row, fileName), "위험!");
    }
    ++row;
    }
    }
    }
    finally
    {
    if(wb != null)
    {
    wb.Close(SaveChanges:false);
    }
    }
    }

    int GetExcelFieldCount(Excel.Worksheet ws, int row)
    {
    var range = ws.Rows[row] as Excel.Range;
    int fieldCount = 0;
    foreach (Excel.Range r in range.Cells)
    {
    dynamic val = r.Value2;
    if (val == null)
    {
    break;
    }
    ++fieldCount;
    }
    return fieldCount;
    }


    반응형
Designed by Tistory.