download

[HttpGet("export-all-location")]
public async Task<IActionResult> ExportAllLocation()
{
    try
    {
        using SqlConnection conn = new(GetConnectionString());
        await conn.OpenAsync();

        List<LocationModel> locations = new();

        string query = @"SELECT ID, LocationName, Sequence1, Sequence2, Zone, Line, LocationStatus 
                         FROM Location
                         ORDER BY Zone, Line, Sequence1";

        using (SqlCommand cmd = new(query, conn))
        {
            using SqlDataReader reader = await cmd.ExecuteReaderAsync();
            while (await reader.ReadAsync())
            {
                locations.Add(new LocationModel
                {
                    ID = Convert.ToInt32(reader["ID"]),
                    LocationName = reader["LocationName"].ToString(),
                    Seq1 = Convert.ToInt32(reader["Sequence1"]),
                    Seq2 = Convert.ToInt32(reader["Sequence2"]),
                    Zone = reader["Zone"].ToString(),
                    Line = Convert.ToInt32(reader["Line"]),
                    Status = Convert.ToBoolean(reader["LocationStatus"])
                });
            }
        }

        if (!locations.Any())
        {
            return BadRequest("No data found");
        }

        // ===== EXCEL =====
        using var workbook = new ClosedXML.Excel.XLWorkbook();
        var worksheet = workbook.Worksheets.Add("Location");

        // 👉 AUTO tất cả: header + data
        worksheet.Cell(1, 1).InsertTable(locations);

        // Style nhẹ
        worksheet.Columns().AdjustToContents();
        worksheet.SheetView.FreezeRows(1);

        // ===== EXPORT =====
        using var stream = new MemoryStream();
        workbook.SaveAs(stream);
        stream.Position = 0;

        return File(
            stream.ToArray(),
            "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            $"All_Location_{DateTime.Now:yyyyMMddHHmmss}.xlsx"
        );
    }
    catch (Exception ex)
    {
        _logger.LogError(ex, "Export ALL Excel failed");
        return StatusCode(500, "Internal server error");
    }
}