Posted 5 December 2023, 1:47 am EST
Hi,
Apologies for the late response. It took me some time to find the best solution for your use case. By default, when you export to the Excel file, the checkbox cells are marked TRUE/FALSE based on their value as Microsoft Excel doesn’t support CheckBox Cell Type.
To overcome this, you need to create a clone of the workbook, replace the values for the CheckBox Cell Type. You could achieve this by overriding the “exportExcelSJSFormat” of the FileMenuHandler. Kindly refer to the following code snippet and the sample:
GC.Spread.Sheets.Designer.FileMenuHandler.exportExcelSJSFormat = function (designer, options) {
var dialogOption = {
fileName: ""
};
GC.Spread.Sheets.Designer.showDialog("fileNameDialog", dialogOption, (result) => {
if (!result) {
return;
}
const fileName = "export.xlsx";
if (result.fileName !== "") {
fileName = result.fileName + ".xlsx";
}
// Clone a Workbook
let newWorkbook = new GC.Spread.Sheets.Workbook();
newWorkbook.fromJSON(spread.toJSON({ includeBindingSource: true }));
newWorkbook.suspendPaint();
newWorkbook.suspendCalcService();
newWorkbook.suspendEvent();
// Iterate through all the sheets
for (let i = 0; i < newWorkbook.getSheetCount(); i++) {
let sheetInstance = newWorkbook.getSheet(i);
let usedRange = sheetInstance.getUsedRange(GC.Spread.Sheets.UsedRangeType.data);
if (usedRange) {
for (let i = usedRange.row; i < usedRange.row + usedRange.rowCount; i++) {
for (let j = usedRange.col; j < usedRange.col + usedRange.colCount; j++) {
let cellType = sheetInstance.getCellType(i, j);
// Check if the Cell Type is CheckBox
if (cellType instanceof GC.Spread.Sheets.CellTypes.CheckBox) {
let value = sheetInstance.getValue(i, j);
// Replace the Values Where the Cell Type is CheckBox
if (value) {
// True Value is X
sheetInstance.setValue(i, j, "X");
} else {
sheetInstance.setValue(i, j, null);
}
}
}
}
}
}
newWorkbook.resumeEvent();
newWorkbook.resumeCalcService();
newWorkbook.resumePaint();
// export spread to xlsx, ssjson, csv file.
newWorkbook.export(function (blob) {
// save blob to a file
saveAs(blob, fileName);
}, function (e) {
console.log(e);
}, options.exportXlsxOptions);
}, (error) => {
console.error(error);
},);
}
Sample: https://jscodemine.grapecity.com/share/Yk5QvLd5UkSTRcvFc1Gz-w/?IsEmbed=false&Theme=Unset&PreviewDirection=0&IsEditorShow=true&IsExplorerShow=true&IsPreviewShow=true&IsConsoleShow=true&IsRunBTNShow=false&IsResetBTNShow=false&IsOpenInCodemineBTNShow=false&PanelWidth=20&PanelWidth=50&PanelWidth=30&defaultOpen={"OpenedFileName"%3A["%2Findex.html"%2C"%2Fsrc%2Fapp.js"]%2C"ActiveFile"%3A"%2Fsrc%2Fapp.js"}
Also, I couldn’t understand what you mean by “Table head is a text”. However, you could use the similar approach to make changes in the newWorkbook.
References:
showDialog method: https://developer.mescius.com/spreadjs/api/designer/modules/GC.Spread.Sheets.Designer#showdialog
getUsedRange method: https://developer.mescius.com/spreadjs/api/classes/GC.Spread.Sheets.Worksheet#getusedrange
Regards,
Ankit
