[X++]Excel取り込み処理のサンプルコード

X++

using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.ExcelPackage;
using OfficeOpenXml.ExcelRange;

class EmplAttendance
{
  public void run()
  {
    this.updateDailyAttendance();
  }
  void updateDailyAttendance()
  {
    System.IO.Stream stream;
    ExcelSpreadsheetName sheeet;
    FileUploadBuild fileUpload;
    DialogGroup dlgUploadGroup;
    FileUploadBuild fileUploadBuild;
    FormBuildControl formBuildControl;
    Dialog dialog = new Dialog("Import the data from Excel");

    dlgUploadGroup = dialog.addGroup("@SYS54759"); // Import
    formBuildControl = dialog.formBuildDesign().control(dlgUploadGroup.name());
    fileUploadBuild = formBuildControl.addControlEx(classstr(FileUpload), "Upload");
    fileUploadBuild.style(FileUploadStyle::MinimalWithFilename);
    fileUploadBuild.fileTypesAccepted(".xlsx");

    if (dialog.run() && dialog.closedOk())
    {
      FileUpload fileUploadControl = dialog.formRun().control(dialog.formRun().controlId("Upload"));
      FileUploadTemporaryStorageResult fileUploadResult = fileUploadControl.getFileUploadResult();

      if (fileUploadResult != null && fileUploadResult.getUploadStatus())
      {
        stream = fileUploadResult.openResult();
        using (ExcelPackage package = new ExcelPackage(stream))
        {
          int rowCount, iterator; ;
          package.Load(stream);
          ExcelWorksheet worksheet = package.get_Workbook().get_Worksheets().get_Item(1);
          OfficeOpenXml.ExcelRange range = worksheet.Cells;
          rowCount = worksheet.Dimension.End.Row – worksheet.Dimension.Start.Row + 1;
          columncount = (worksheet.Dimension.End.Column);
          for (i = 2; i <= rowCount; i++)
          {
            str Emplid;
            TransDate WorkingDate;
            emplid = (range.get_Item(i, 1).value);
            WorkingDate = str2Date((range.get_Item(i, 2).value), 123);
            select* from emplTimeAttendance
            where emplTimeAttendance.EmplId == Emplid
               && emplTimeAttendance.WorkingDate == WorkingDate;
            if (emplTimeAttendance)
            {
              emplTimeAttendance.selectForUpdate(true);
              emplTimeAttendance.Timein = any2Str(range.get_Item(i, 3).value);
              emplTimeAttendance.Timeout = any2Str(range.get_Item(i, 4).value);
              emplTimeAttendance.OT = any2Real(range.get_Item(i, 5).value);
              ttsbegin;
              emplTimeAttendance.update();
              ttscommit;
            }
            else
            {
              insertTimeAttendance.EmplId = (range.get_Item(i, 1).value);
              insertTimeAttendance.WorkingDate = str2Date((range.get_Item(i, 2).value), 123);
              insertTimeAttendance.Timein = any2Str(range.get_Item(i, 3).value);
              insertTimeAttendance.Timeout = any2Str(range.get_Item(i, 4).value);
              insertTimeAttendance.OT = any2Real(range.get_Item(i, 5).value);
              insertTimeAttendance.insert();
            }
          }
        }
      }
      else
      {
        error("Error here");
      }
    }
  }
  public static void main(Args args)
  {
    EmplAttendance emplDailyAttendanceImport;
    emplDailyAttendanceImport = new EmplAttendance();
    emplDailyAttendanceImport.run();
  }
}