Read and edit the Power Query formula in Excel documents.
xlsx, xlsm, xlsb are ZIP based and thus can be extracted.
customXml\item1.xml (DataMashup) using ExcelCustomXml.ts
import { type UnzippedItem, ExcelCustomXml } from 'excel-datamashup';
const xml: string = '...';
// returns a working instance of the class
const excelXml: ExcelCustomXml = await ExcelCustomXml.create(xml);
// find the power query file
const powerQuery: UnzippedItem | undefined = excelXml.datamashup.rootItems.find(
(o) => o.path.endsWith('Section1.m')
);
// if found, set its contents to something else
if (powerQuery) {
excelXml.datamashup.setFileContents(powerQuery, '...');
// always reset permissions when editing
await excelXml.datamashup.resetPermissions();
}
// pack the data back to a xml string, then write it back to the customXml\item1.xml file using your favorite zip editing library
const newXml: string | undefined = await excelXml.pack();
`
.ts
import { type Result, ExcelZip, UnzippedItem } from 'excel-datamashup';
// read and store the binary zip data as number array or Uint8Array
const zip = new Uint8Array();
// process the zip into a more manageable object
const excelZip: ExcelZip = await ExcelZip.unzip(zip);
// get the power query contents
const powerQuery: UnzippedItem | undefined = await excelZip.getPowerQueryFile();
// modify the power query contents
if (powerQuery) {
await excelZip.setPowerQueryFile(
powerQuery,
'section Section1;\n\nshared Test = let\r\n result = #table(1, {{"This is an example."}})\r\nin\r\n result;'
);
}
// zip the contents back to an Excel file
const result: Result = await excelZip.zip();
// evaluate if it was successfull
if (result.ok) {
console.log('Save the xlsx file:', result.data.length);
} else {
console.log('Unable to create xlsx file.');
}
``