如何在导出之前自定义 xlsx 表格的行。

huangapple go评论72阅读模式
英文:

How to customize rows of xlsx table before export

问题

以下是您要翻译的内容:

I get an array of objects(dicts) as input like the following example.

 const data = [
    { id: 1, serviceId: 1, title: "A", model: "A", },
    { id: 1, serviceId: 1, title: "T", model: "b", },
    { id: 1, serviceId: 2, title: "R", model: "A", },
    { id: 2, serviceId: 55, title: "Q", model: "A", },
    { id: 3, serviceId: 58, title: "S", model: "p", },
    { id: 3, serviceId: 58, title: "S", model: "m", },
    { id: 3, serviceId: 66, title: "y", model: "A", }
    ];

I want to cutomize the rows when I export the payload of data to an xlsx file. For the export, I am using the following function.

export const ExportToExcel = ({ apiData, fileName }) => {
	const fileType =
		"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8";
	const fileExtension = ".xlsx";

	const exportToCSV = (apiData, fileName) => {
		const ws = XLSX.utils.json_to_sheet(apiData);
		const wb = { Sheets: { data: ws }, SheetNames: ["data"] };
		const excelBuffer = XLSX.write(wb, { bookType: "xlsx", type: "array" });
		const data = new Blob([excelBuffer], { type: fileType });
		FileSaver.saveAs(data, fileName + fileExtension);
	};

	return (
		<Button
			onClick={(e) => exportToCSV(apiData, fileName)}
			sx={{ m: 2 }}
			variant="contained"
		>
			Export as xlsx
		</Button>
	);
};

For the customization, I want to get as an output something similar to what is showed in the following table:
xlsx file

I tried to achieve that by looping over each dictionary and regrouping elements that have the same parameters but the code was just massive.

Is there any simpler way to achieve this?

I am calling the export function in the main component as follows:

<ExportToExcel apiData={data} fileName={fileName}/>
英文:

I get an array of objects(dicts) as input like the following example.

 const data = [
    { id: 1, serviceId: 1, title: "A", model: "A", },
    { id: 1, serviceId: 1, title: "T", model: "b", },
    { id: 1, serviceId: 2, title: "R", model: "A", },
    { id: 2, serviceId: 55, title: "Q", model: "A", },
    { id: 3, serviceId: 58, title: "S", model: "p", },
    { id: 3, serviceId: 58, title: "S", model: "m", },
    { id: 3, serviceId: 66, title: "y", model: "A", }
    ];

I want to cutomize the rows when I export the payload of data to an xlsx file. For the export, I am using the following function.

export const ExportToExcel = ({ apiData, fileName }) => {
	const fileType =
		"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8";
	const fileExtension = ".xlsx";

	const exportToCSV = (apiData, fileName) => {
		const ws = XLSX.utils.json_to_sheet(apiData);
		const wb = { Sheets: { data: ws }, SheetNames: ["data"] };
		const excelBuffer = XLSX.write(wb, { bookType: "xlsx", type: "array" });
		const data = new Blob([excelBuffer], { type: fileType });
		FileSaver.saveAs(data, fileName + fileExtension);
	};

	return (
		<Button
			onClick={(e) => exportToCSV(apiData, fileName)}
			sx={{ m: 2 }}
			variant="contained"
		>
			Export as xlsx
		</Button>
	);
};

For the customization, I want to get as an output something similar to what is showed in the following table:
xlsx file

I tried to achieve that by looping over each dictionary and regrouping elements that have the same parameters but the code was just massive.

Is there any simpler way to achieve this?

I am calling the export function in the main component as follows:

<ExportToExcel apiData={data} fileName={fileName}/>

答案1

得分: 0

我不明白为什么这段代码会变得“庞大” - 你只需要应用经典的“控制中断”机制,比较当前行的列值与前一行的列值。只不过在这里,你希望从左到右执行 - 一旦某一列的值与前一行不再相同,你就中断它,不再修改后续的列。

const modified = [];
const keys = Object.keys(data[0]);

data.forEach(function(e, i) {
  let clone = { ...e }
  modified.push(clone);
  if (i > 0) {
    for (const k of keys) {
      if (data[i - 1][k] === clone[k]) {
        clone[k] = '';
      } else {
        break;
      }
    }
  }
});

console.log(modified)

结果:

0: {id: 1, serviceId: 1, title: 'A', model: 'A'}
1: {id: '', serviceId: '', title: 'T', model: 'b'}
2: {id: '', serviceId: 2, title: 'R', model: 'A'}
3: {id: 2, serviceId: 55, title: 'Q', model: 'A'}
4: {id: 3, serviceId: 58, title: 'S', model: 'p'}
5: {id: '', serviceId: '', title: '', model: 'm'}
6: {id: '', serviceId: 66, title: 'y', model: 'A'}
英文:

I don't see why the code for this should get "massive" - all you need to do is apply the classic control break mechanism of comparing the current row's value for a column, with that of the previous one. Only here you want to do it from left to right - and once the value in one column is not like that of the previous row any more, you break it off and don't modify the following columns any more.

const modified = [];
const keys = Object.keys(data[0]);

data.forEach(function(e, i) {
  let clone = { ...e }
  modified.push(clone);
  if (i > 0) {
    for (const k of keys) {
      if (data[i - 1][k] === clone[k]) {
        clone[k] = '';
      } else {
        break;
      }
    }
  }
});

console.log(modified)

Result:

0: {id: 1,  serviceId: 1,  title: 'A', model: 'A'}
1: {id: '', serviceId: '', title: 'T', model: 'b'}
2: {id: '', serviceId: 2,  title: 'R', model: 'A'}
3: {id: 2,  serviceId: 55, title: 'Q', model: 'A'}
4: {id: 3,  serviceId: 58, title: 'S', model: 'p'}
5: {id: '', serviceId: '', title: '',  model: 'm'}
6: {id: '', serviceId: 66, title: 'y', model: 'A'}

huangapple
  • 本文由 发表于 2023年3月9日 19:27:33
  • 转载请务必保留本文链接:https://go.coder-hub.com/75683972.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定