Data Table or Dataset to .xlsx

Few things you can do to create a .xlsx file with all the data and a smaller size:

1. Use a smaller font size. This will reduce the file size by reducing the amount of data that needs to be stored.
2. Remove any unnecessary columns. If there are any columns that you don't need, remove them from the table. This will also reduce the file size.
3. Compress the file. Once you have created the .xlsx file, you can compress it using a compression utility such as WinRAR or 7-Zip. This will further reduce the file size.

Here are some additional tips for creating a smaller .xlsx file:

• Use a single sheet for your data. This will reduce the file size by reducing the number of sheets that need to be stored.
• Avoid using formulas or macros. These can increase the file size significantly.
• Save the file in a .xlsx format instead of a .xls format. The .xlsx format is newer and uses a more efficient compression algorithm.

When dealing with large datasets, generating Excel files (.xlsx) can be memory-intensive and may lead to out-of-memory exceptions. To overcome this issue and create a smaller-sized .xlsx file, you can consider using a library like EPPlus, which provides efficient memory usage and compression capabilities.
Install the EPPlus NuGet package in your project.

using OfficeOpenXml;

// Create a new Excel package
using (var package = new ExcelPackage())
{
    // Add a new worksheet
    ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Sheet1");

    // Get your data from the Oracle stored procedure and populate the worksheet
    // Assuming you have the data in a DataTable called 'dataTable'
    worksheet.Cells["A1"].LoadFromDataTable(dataTable, true);

    // Auto-fit columns (optional)
    worksheet.Cells.AutoFitColumns();

    // Save the package to a file
    string filePath = "path/to/your/file.xlsx";
    package.SaveAs(new FileInfo(filePath));
}