【15.2.3】集算表公式问题

收藏

本帖最后由 palalpapa 于 2023-7-27 14:13 编辑

image.png114051512.png

在表头上面加了两个汇总行，一个是一列的总的汇总，公式是 templateSheet.setFormula(0, 6, "=SUM(TestSheet1[freight])");这个是可以的，

[size=13.0667px]还有一行汇总我想实现条件汇总，代码是 templateSheet.setFormula(1, 6, "=IF(TestSheet1[customerId]=VINET,SUM(TestSheet1[freight]),0)");
这个就不行，想问一下这个公式应该怎么写，列是动态变的，字段名是不变的。

Joestar.Xu · Joestar.Xu

您好，请您提供下可以复现出这个问题的Demo或ssjson文件，我们这边调研一下。

palalpapa · palalpapa

Joestar.Xu 发表于 2023-7-27 14:19
您好，请您提供下可以复现出这个问题的Demo或ssjson文件，我们这边调研一下。

<template>
<div id="spread-host">
<el-button @click="download">Download</el-button>
<gc-spread-sheets hostClass="spreadHost" @workbookInitialized="initWorkbook">
</gc-spread-sheets>
</div>
</template>
<script>
import '@grapecity/spread-sheets/styles/gc.spread.sheets.excel2016colorful.css'
import '@grapecity/spread-sheets-vue'
import "@grapecity/spread-sheets-tablesheet";
import GC from '@grapecity/spread-sheets'
export default {
name: "spreadJs",
props: {},
data() {
return {
spread:null
}
},
computed: {},
created() {
},
mounted() {
},
methods: {
//绑定数据
initWorkbook(spread){
this.spread = spread;
spread.options.scrollbarMaxAlign=true;
spread.options.newTabVisible = false;
this.handleSpreadSheet(spread)
},
handleSpreadSheet(spread){
const _this = this
spread.suspendPaint();
spread.clearSheets();
spread.clearSheetTabs();
spread.options.autoFitType = GC.Spread.Sheets.AutoFitType.cellWithHeader;
let tableHeader = this.getTableHeader()
let dataManager = spread.dataManager();
let myTable = dataManager.addTable("myTable1", {
remote:{
read:{
url:'https://demodata.grapecity.com/northwind/api/v1/orders'
}
}
})
myTable.showFooter = true
let sheet = spread.addSheetTab(0,'TestSheet1',GC.Spread.Sheets.SheetType.tableSheet)
sheet.applyTableTheme(GC.Spread.Sheets.Tables.TableThemes["light9"]);
let rowActions = GC.Spread.Sheets.TableSheet.BuiltInRowActions;
let options = sheet.rowActionOptions();
options.push(rowActions.saveRow);
sheet.options.allowAddNew = false; //hide new row
myTable.fetch().then(function () {
let view = myTable.addView("myView", tableHeader);
_this.addTotalRow(sheet,tableHeader)
sheet.setDataView(view);
if (tableHeader.length > 0) {
sheet.togglePinnedColumns([0, 1]); //冻结列
}
setTimeout(() => {
spread.refresh();
},500);
});
spread.resumePaint();
},
addTotalRow(sheet, tableHeader) {
let templateSheet = new GC.Spread.Sheets.Worksheet();
// templateSheet.options.keepUnknownFormulas = true;
templateSheet.setRowCount(2);
templateSheet.setColumnCount(tableHeader.length);
let currencyFormatterStyle = new GC.Spread.Sheets.Style();
currencyFormatterStyle.backColor = "#e0e0e0";
currencyFormatterStyle.foreColor = "#333";
currencyFormatterStyle.locked = true;
templateSheet.setValue(0, 0, "Total");
templateSheet.addSpan(0, 0, 1, 5);
templateSheet.setStyle(0, 0, currencyFormatterStyle);
let style = new GC.Spread.Sheets.Style();
style.backColor = "green";
style.borderBottom = new GC.Spread.Sheets.LineBorder("black", GC.Spread.Sheets.LineStyle.thin);
templateSheet.setStyle(0, 6, style);
templateSheet.setFormula(0, 6, "=SUM(TestSheet1[freight])");
templateSheet.setStyle(0, 5, style);
templateSheet.setFormula(0, 5, "=SUM(TestSheet1[shipVia])");
//row 1
let currencyFormatterStyle1 = new GC.Spread.Sheets.Style();
currencyFormatterStyle1.backColor = "#e0e0e0";
currencyFormatterStyle1.foreColor = "#333";
currencyFormatterStyle1.locked = true;
templateSheet.setValue(1, 0, "VINET Total");
templateSheet.addSpan(1, 0, 1, 5);
templateSheet.setStyle(1, 0, currencyFormatterStyle1);
let style1 = new GC.Spread.Sheets.Style();
style1.backColor = "red";
style1.borderBottom = new GC.Spread.Sheets.LineBorder("black", GC.Spread.Sheets.LineStyle.thin);
templateSheet.setStyle(1, 6, style1);
// templateSheet.setFormula(1, 6, "=SUMIF(TestSheet1[customerId],TestSheet1[customerId]=VINET,@freight)");
//TODO:-------------------------------公式
templateSheet.setFormula(1, 6, "=IF(TestSheet1[customerId]=VINET,SUM(TestSheet1[freight]),0)");
templateSheet.setStyle(1, 5, style1);
templateSheet.setFormula(1, 5, "=SUM(TestSheet1[shipVia])");
let template = templateSheet.toJSON();
sheet.applyFreeHeaderArea(template);
},
getTableHeader(){
return [
{
caption:'customerId',
value:'customerId',
width:200,
visible:true,
readonly:true,
style:{
backColor:'yellow',
foreColor:'blue',
locked:true
}
},
{
caption:'employeeId',
value:'employeeId',
width:200
},
{
caption:'orderDate',
value:'customerId',
width:200
},
{
caption:'requiredDate',
value:'requiredDate',
width:200,
style: {
formatter:'yyyy-mm-dd HH:mm:ss'
}
},
{
caption:'shippedDate',
value:'shippedDate',
width:200,
style: {
formatter:'yyyy-mm-dd'
}
},
{
caption:'shipVia',
value:'shipVia',
width:200
},
{
caption:'freight',
value:'=IFERROR([@shipVia] * 2,0)',
width:200
},
{
caption:'shipName',
value:'shipName',
width:200
},
{
caption:'shipAddress',
value:'shipAddress',
width:200
},
{
caption:'shipCity',
value:'shipCity',
width:200
},
{
caption:'shipRegion',
value:'shipRegion',
width:200
},
{
caption:'shipPostalCode',
value:'shipPostalCode',
width:200
},
{
caption:'shipCountry',
value:'shipCountry',
width:200
},
]
},
download(){
let fileName = '111.xlsx'
this.spread.export(function (blob) {
// save blob to a file
saveAs(blob, fileName);
}, function (e) {
console.log(e);
}, {
fileType: GC.Spread.Sheets.FileType.excel,
includeBindingSource: true
});
}
}
}
</script>
<style scoped>
.spreadHost {
width: 100%;
height: 800px;
}
</style>