javascript - How with XLSX npm package add custom headers with color - Stack Overflow

admin管理员组

文章数量:1422062

I'm new to using XLSX package and I'm not quite sure how to create a specific header, with colors and group-specific columns.

I need to do something as the visual example

Under a header, I can have several columns and the title has a color. The columns are not fixed for every header so we can have a title with 2 columns under another one with 5 columns.

I started something basic and shared my code but I don't know how to make the headers as an example. The code is for a report extractor in XLS in a project I'm working on

import XLSX from 'xlsx';
import moment from 'moment';

const rawToHeaders = ({
  id,
  externalIds,
  dateOfBirth = {},
  postalCode,
  locale,
  siteId,
  status = {},
  prescreenerMetrics,
}) => {
  const { day, month, year } = dateOfBirth;
  const dob = [day, month, year].filter(Boolean).join('-');
  const { type, label, ment, timestamp } = status;
  const timeInStatus = moment(timestamp).toNow(true);

  const N_A = 'not available';

  return {
    'Candidate ID': id,
    'External IDs': externalIds
      ?.map(({ source, value }) => `${source}: ${value}`)
      .join('; '),
    'Date of birth': dob,
    'Postal code': postalCode,
    Locale: locale,
    'Site ID': siteId,
    'Current status': type,
    'Current sub-status': label,
    'Current status ment': ment,
    'Time in current status': timeInStatus,
    'Source/recruiter': prescreenerMetrics?.source,
    Referrer: prescreenerMetrics?.referrer,
  };
};

const generateMasterReport = (data) => {
  const wb = XLSX.utils.book_new();

  const ws = XLSX.utils.json_to_sheet(data.map(rawToHeaders));

  XLSX.utils.book_append_sheet(wb, ws);

  return XLSX.write(wb, { type: 'buffer', bookType: 'xlsx' });
};

export default generateMasterReport;

From the code above let's say I want to group the columns under specific headers with color

The columns are 12 so 4 headers and in the order, as are in the code like

• HEADER 1: Candidate ID, External ID, Date of birth -- RED
• HEADER 2: Postal code, Locale, Site ID -- BLUE
• HEADER 3: Site ID, current status, current sub status -- GREEN
• HEADER 4: Time in status, Source, Referrer -- WHITE

I was able to create the excel without the headers for now but this part I'm not sure how to do it

I'm new to using XLSX package and I'm not quite sure how to create a specific header, with colors and group-specific columns.

I need to do something as the visual example

Under a header, I can have several columns and the title has a color. The columns are not fixed for every header so we can have a title with 2 columns under another one with 5 columns.

I started something basic and shared my code but I don't know how to make the headers as an example. The code is for a report extractor in XLS in a project I'm working on

import XLSX from 'xlsx';
import moment from 'moment';

const rawToHeaders = ({
  id,
  externalIds,
  dateOfBirth = {},
  postalCode,
  locale,
  siteId,
  status = {},
  prescreenerMetrics,
}) => {
  const { day, month, year } = dateOfBirth;
  const dob = [day, month, year].filter(Boolean).join('-');
  const { type, label, ment, timestamp } = status;
  const timeInStatus = moment(timestamp).toNow(true);

  const N_A = 'not available';

  return {
    'Candidate ID': id,
    'External IDs': externalIds
      ?.map(({ source, value }) => `${source}: ${value}`)
      .join('; '),
    'Date of birth': dob,
    'Postal code': postalCode,
    Locale: locale,
    'Site ID': siteId,
    'Current status': type,
    'Current sub-status': label,
    'Current status ment': ment,
    'Time in current status': timeInStatus,
    'Source/recruiter': prescreenerMetrics?.source,
    Referrer: prescreenerMetrics?.referrer,
  };
};

const generateMasterReport = (data) => {
  const wb = XLSX.utils.book_new();

  const ws = XLSX.utils.json_to_sheet(data.map(rawToHeaders));

  XLSX.utils.book_append_sheet(wb, ws);

  return XLSX.write(wb, { type: 'buffer', bookType: 'xlsx' });
};

export default generateMasterReport;

From the code above let's say I want to group the columns under specific headers with color

The columns are 12 so 4 headers and in the order, as are in the code like

• HEADER 1: Candidate ID, External ID, Date of birth -- RED
• HEADER 2: Postal code, Locale, Site ID -- BLUE
• HEADER 3: Site ID, current status, current sub status -- GREEN
• HEADER 4: Time in status, Source, Referrer -- WHITE

I was able to create the excel without the headers for now but this part I'm not sure how to do it

• javascript
• excel
• xlsx

Share Improve this question Follow asked Oct 10, 2022 at 8:24 JakubJakub 2,73988 gold badges4545 silver badges104104 bronze badges

Add a ment  | 

3 Answers 3

Sorted by: Reset to default 1

You can set the Width of Each column by giving wch value in an array

const wb = XLSX.utils.book_new();

const colWidths = [{ wch: 12 },{ wch: 20 },{ wch: 25 },{ wch: 30 },{ wch: 40 }];

ws['!cols'] = colWidths;

XLSX.utils.book_append_sheet(wb, ws);

I found a solution to my problem and I don't know if it is the best :) However, worked for me so I'm sharing

Below code of what I did, I put the headers in a variable that is used to create the headers and I used the "merge" from documentation to create the merged cells of the headers.

What I don't know is how to make this more functional and how to make a with for single columns instead of a global one as

const colWidths = [{ wch: 12 }];

I would like to customize every column to a specific width.

Another thing I believe without the pro version of the PKG I cannot style the cells with a different font, BGColor, and so on but if there is a way I'm ear :)

import XLSX from 'xlsx';
import moment from 'moment';

const N_A = null;

const headerGroups = [
  { name: 'Candidate data', origin: 'A1' },
  { name: 'Date receiving status', origin: 'N1' },
  { name: 'Candidate in Pending call center status', origin: 'U1' },
  {
    name: 'Candidate in Pending site OR Consented OR Randomized status',
    origin: 'AI1',
  },
];

const colWidths = [{ wch: 12 }];

const rawToHeaders = ({
  id,
  externalIds,
  dateOfBirth = {},
  postalCode,
  locale,
  siteId,
  status = {},
  prescreenerMetrics,
}) => {
  const { day, month, year } = dateOfBirth;
  const dob = [day, month, year].filter(Boolean).join('-');
  const { type, label, ment, timestamp } = status;
  const timeInStatus = moment(timestamp).toNow(true);
  return {
    // !Candidate data
    'Candidate ID': id,
    'External IDs': externalIds
      ?.map(({ source, value }) => `${source}: ${value}`)
      .join('; '),
    'Date of birth': dob,
    'Postal code': postalCode,
    Locale: locale,
    'Site ID': siteId,
    'PI name': N_A,
    'Current status': type,
    'Current sub-status': label,
    'Current status ment': ment,
    'Time in current status': timeInStatus,
    'Source/recruiter': prescreenerMetrics?.source,
    Referrer: prescreenerMetrics?.referrer,
    // !Date receiving status
    'Pending call center': N_A,
    'Pending site': N_A,
    Consented: N_A,
    Randomized: N_A,
    'Rejected call center': N_A,
    'Rejected  site (+ rejected_consented + rejected_randomized)': N_A,
    'Call Scheduled': N_A,
    // !Candidate in Pending call center status
    'Call - Attempts (by call center)': N_A,
    'SMS - Attempts (by call center)': N_A,
    'Email - Attempts (by call center)': N_A,
    'Phone number revealed (by call center) - quantity': N_A,
    'Email revealed (by call center) - quantity': N_A,
    'Call - date of first attempt (by call center)': N_A,
    'SMS - date of first attempt (by call center)': N_A,
    'Email - date of first attempt (by call center)': N_A,
    'Call - duration of longest call (by call center)': N_A,
    'Call - date of longest call (by call center)': N_A,
    'Comments (by call center) in Trialbee system': N_A,
    'Last staff activity (by call center) - Action': N_A,
    'Last staff activity by call center - User': N_A,
    'Last staff activity by call center - Date': N_A,
    // !Candidate in Pending site OR Consented OR Randomized status
    'Call - attempts (by site)': N_A,
    'SMS - attempts (by site)': N_A,
    'Email - attempts (by site)': N_A,
    'Phone number revealed (by site) - quantity': N_A,
    'Email revealed (by site) - quantity': N_A,
    'Call - date of first attempt (by site)': N_A,
    'SMS - date of first attempt (by site)': N_A,
    'Email - date of first attempt by site': N_A,
    'Call - Longest duration of call (by site)': N_A,
    'Call - Date of longest call (by site)': N_A,
    'Comments (by site) in Trialbee system': N_A,
    'Last staff activity (by site) - Action': N_A,
    'Last staff activity (by site) - User': N_A,
    'Last staff activity (by site) - Date': N_A,
  };
};

const generateMasterReport = (data) => {
  const wb = XLSX.utils.book_new();
  const ws = XLSX.utils.json_to_sheet(data.map(rawToHeaders), { origin: 'A2' });
  ws['!merges'] = [
    XLSX.utils.decode_range('A1:M1'),
    XLSX.utils.decode_range('N1:T1'),
    XLSX.utils.decode_range('U1:AH1'),
    XLSX.utils.decode_range('AI1:AV1'),
  ];
  headerGroups.forEach(({ name, origin }) => {
    XLSX.utils.sheet_add_aoa(ws, [[name]], { origin });
  });

  ws['!cols'] = colWidths;

  XLSX.utils.book_append_sheet(wb, ws);

  return XLSX.write(wb, { type: 'buffer', bookType: 'xlsx' });
};

export default generateMasterReport;

you can use the different package instead of XLSX , if you want to set only header color then you have to install => npm i xlsx-color

const exportToExcel = (data , fileName) =>{
    const fileType = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8';
const fileExtension = '.xlsx';

const ws = XLSX.utils.json_to_sheet(data);
ws['A1'].s = {
  fill: {
    patternType: 'solid',
    fgColor: { rgb: 'FF939393' }
  },
  font: {
    name: 'Times New Roman',
    sz: 16,
    color: { rgb: '#FF000000' },
    bold: false,
    italic: false,
    underline: false
  }
};
const wb = { Sheets: { data: ws }, SheetNames: ['data'] };
const excelBuffer = XLSX.write(wb, { bookType: 'xlsx', type: 'array' });
const excelData = new Blob([excelBuffer], { type: fileType });
FileSaver.saveAs(excelData, fileName + ' - ' + dayjs().format('DD/MM/YYYY') + fileExtension);
}

本文标签： javascriptHow with XLSX npm package add custom headers with colorStack Overflow