using DevExpress.XtraRichEdit;

using DevExpress.Spreadsheet;

  [ApiExplorerSettings(IgnoreApi = true)]

  [NonAction]

  public DataTable ConvertToDataTable(IFormFile excelFile)

  {

      try

      {

          if (excelFile == null || excelFile.Length == 0)

          {

              return new DataTable();

          }

          using (var stream = new MemoryStream())

          {

              excelFile.CopyTo(stream);

              stream.Position = 0;

              using (var workbook = new Workbook())

              {

                  workbook.LoadDocument(stream);

                  // İlk sayfayı al, gerekirse sayfa indeksini değiştirebilirsiniz

                  Worksheet worksheet = workbook.Worksheets[0];

                  // DataTable oluştur

                  DataTable dataTable = new DataTable();

                  // Başlık satırını ekleyerek sütunları oluştur

                  for (int columnIndex = 0; columnIndex < worksheet.Columns.LastUsedIndex + 1; columnIndex++)

                  {

                      dataTable.Columns.Add(Convert.ToString(worksheet.Cells[0, columnIndex].Value));

                  }

                  // Veri satırlarını ekleyerek DataTable'ı doldur

                  for (int rowIndex = 1; rowIndex < worksheet.Rows.LastUsedIndex + 1; rowIndex++)

                  {

                      DataRow dataRow = dataTable.NewRow();

                      for (int columnIndex = 0; columnIndex < worksheet.Columns.LastUsedIndex + 1; columnIndex++)

                      {

                          dataRow[columnIndex] = worksheet.Cells[rowIndex, columnIndex].Value;

                      }

                      dataTable.Rows.Add(dataRow);

                  }

                  // DataTable'ı döndür

                  return dataTable;

              }

          }

      }

      catch (Exception ex)

      {

          return new DataTable();

      }

  }

   [Route("addExcelUser")]

   [HttpPost]

   public IActionResult addExcelUser(IFormFile file,int sirketId, int subeId)

   {

       using var context = new Context();

       DataTable dt = null;

       ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

       if (file == null || file.Length == 0)

       {

           return BadRequest("Invalid file");

       }

       try

       {

           using (var stream = file.OpenReadStream())

           {

               using (var package = new ExcelPackage(stream))

               {

                   ExcelWorksheet worksheet = package.Workbook.Worksheets[0];

                   // Assumes the first row contains the column names

                   dt = new DataTable();

                   foreach (var firstRowCell in worksheet.Cells[1, 1, 1, worksheet.Dimension.End.Column])

                   {

                       dt.Columns.Add(firstRowCell.Text);

                   }

                   for (var rowNumber = 2; rowNumber <= worksheet.Dimension.End.Row; rowNumber++)

                   {

                       var row = worksheet.Cells[rowNumber, 1, rowNumber, worksheet.Dimension.End.Column];

                       var newRow = dt.Rows.Add();

                       foreach (var cell in row)

                       {

                           newRow[cell.Start.Column - 1] = cell.Text;

                       }

                   }

                   if (dt == null || dt.Rows.Count < 1)

                   {

                       return NotFound("Data Null");

                   }

                   foreach (DataRow item in dt.Rows)

                   {

                       Users users = new Users();

                       users.ad = item["ad"].ToString();

                       users.soyad = item["soyad"].ToString();

                       users.mail = item["mail"].ToString();

                       users.tel = item["tel"].ToString();

                       users.tc = item["tc"].ToString();

                       users.username = users.mail;

                       users.sifre = users.tc;

                       users.adminrole = false;

                       users.subeId = subeId;

                       users.sirketId = sirketId;

                       add(users);

                   }

               }

           }

       }

       catch (Exception ex)

       {

           return NotFound(ex.Message);

       }

       return Ok("Excel yükleme başarılı");

   }