Home > Net >  Laravel Excel - Number value auto change
Laravel Excel - Number value auto change

Time:05-30

So I have some data i saved it as Varchar on database, data ex:3578012605010001 and when I export the data to excel, it'll change to 3,57801E 15, then I tried to change it as Number, the result is 3578012605010000 i think it's works. But then i realize that it didn't, it change the last number from 1 to 0, that is not the exact data from the database. I wanted a result that export an exact data like 3578012605010001.

Anw, this is my code to export from query to excel, im using Laravel Excel package.

class PengawasExport extends \PhpOffice\PhpSpreadsheet\Cell\StringValueBinder implements FromCollection, WithHeadings, ShouldAutoSize, WithColumnFormatting
{
    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        $dataTanggalMulai = Master::first();
        $dataTanggalSelesai = Master::first();

        $from = $dataTanggalMulai->periode_mulai;
        $to = $dataTanggalSelesai->periode_akhir;

        $pengawas = Penugasan::join('pengawas', 'penugasans.pengawas_id', 'pengawas.id')
        ->join('ujians', 'penugasans.ujian_id', 'ujians.id')
        ->groupBy('pengawas.nama', 'pengawas.nik', 'pengawas.pns', 'pengawas.bank', 'pengawas.norek')
        ->whereBetween('ujians.tanggal', [$from, $to])
        ->selectRaw('pengawas.nama, pengawas.nik, pengawas.pns, pengawas.bank, pengawas.norek, count(*) as total')
        ->get();

        return $pengawas;
    }

    public function headings(): array
    {
        return [
            'Nama',
            'NIP/NPI/NIK',
            'Status Kepegawaian',
            'Nama Bank',
            'Nomor Rekening',
            'Total Mengawas'
        ];
    }

    public function columnFormats(): array
    {
        return [
            'A' => NumberFormat::FORMAT_TEXT,
            'B' => NumberFormat::FORMAT_NUMBER,
            'C' => NumberFormat::FORMAT_TEXT,
            'D' => NumberFormat::FORMAT_TEXT,
            'E' => NumberFormat::FORMAT_NUMBER
        ];
    }
}

And here are my example datasets that i want to export

enter image description here

CodePudding user response:

try this add '0' for specific columns you need to display the full number

public function columnFormats(): array
    {
        return [
            // F is the column
            'F' => '0'
        ];
    }
  • Related