Open save in Vue Spreadsheet component
19 Jun 202424 minutes to read
The native data format for Spreadsheet is JSON
. When you open an excel file, it needs to be read and converted to client side Spreadsheet model. The converted client side Spreadsheet model is sent as JSON which is used to render Spreadsheet. Similarly, when you save the Spreadsheet, the client Spreadsheet model is sent to the server as JSON for processing and saved as Excel file formats. Server configuration
is used for this process.
Open
The Spreadsheet control opens an Excel document with its data, style, format, and more. To enable this feature, set allowOpen
as true
and assign service url to the openUrl
property.
User Interface:
In user interface you can open an Excel document by clicking File > Open
menu item in ribbon.
The following sample shows the Open
option by using the openUrl
property in the Spreadsheet control. You can also use the beforeOpen
event to trigger before opening an Excel file.
<template>
<ejs-spreadsheet :openUrl="openUrl" :allowOpen="true" :beforeOpen="beforeOpen"></ejs-spreadsheet>
</template>
<script setup>
import { SpreadsheetComponent as EjsSpreadsheet } from "@syncfusion/ej2-vue-spreadsheet";
const openUrl = 'https://services.syncfusion.com/vue/production/api/spreadsheet/open';
const beforeOpen = function (args) {
// your code snippets here
}
</script>
<style>
@import '../node_modules/@syncfusion/ej2-base/styles/material.css';
@import '../node_modules/@syncfusion/ej2-buttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-dropdowns/styles/material.css';
@import '../node_modules/@syncfusion/ej2-inputs/styles/material.css';
@import '../node_modules/@syncfusion/ej2-navigations/styles/material.css';
@import '../node_modules/@syncfusion/ej2-popups/styles/material.css';
@import '../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-grids/styles/material.css';
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
</style>
<template>
<ejs-spreadsheet :openUrl="openUrl" :allowOpen="true" :beforeOpen="beforeOpen"></ejs-spreadsheet>
</template>
<script>
import { SpreadsheetComponent } from "@syncfusion/ej2-vue-spreadsheet";
export default {
name: "App",
components: {
"ejs-spreadsheet": SpreadsheetComponent
},
data: () => {
return {
openUrl: 'https://services.syncfusion.com/vue/production/api/spreadsheet/open'
}
},
methods: {
beforeOpen: function (args) {
// your code snippets here
}
}
}
</script>
<style>
@import '../node_modules/@syncfusion/ej2-base/styles/material.css';
@import '../node_modules/@syncfusion/ej2-buttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-dropdowns/styles/material.css';
@import '../node_modules/@syncfusion/ej2-inputs/styles/material.css';
@import '../node_modules/@syncfusion/ej2-navigations/styles/material.css';
@import '../node_modules/@syncfusion/ej2-popups/styles/material.css';
@import '../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-grids/styles/material.css';
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
</style>
Please find the below table for the beforeOpen event arguments.
Parameter | Type | Description |
---|---|---|
file | FileList or string or File | To get the file stream. FileList - contains length and item index. File - specifies the file lastModified and file name. |
cancel | boolean | To prevent the open operation. |
requestData | object | To provide the Form data. |
Open an external URL excel file while initial load
You can achieve to access the remote excel file by using the created
event. In this event you can fetch the excel file and convert it to a blob. Convert this blob to a file and open
this file by using Spreadsheet component open method.
<template>
<ejs-spreadsheet ref="spreadsheet" :openUrl="openUrl" :allowOpen="true" :created="created"></ejs-spreadsheet>
</template>
<script setup>
import { ref } from "vue";
import { SpreadsheetComponent as EjsSpreadsheet } from "@syncfusion/ej2-vue-spreadsheet";
const spreadsheet = ref(null);
const openUrl = 'https://services.syncfusion.com/vue/production/api/spreadsheet/open';
const created = function () {
fetch("https://cdn.syncfusion.com/scripts/spreadsheet/Sample.xlsx") // fetch the remote url
.then((response) => {
response.blob().then((fileBlob) => { // convert the excel file to blob
let file = new File([fileBlob], "Sample.xlsx"); //convert the blob into file
spreadsheet.value.open({ file: file }); // open the file into Spreadsheet
})
})
}
</script>
<style>
@import '../node_modules/@syncfusion/ej2-base/styles/material.css';
@import '../node_modules/@syncfusion/ej2-buttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-dropdowns/styles/material.css';
@import '../node_modules/@syncfusion/ej2-inputs/styles/material.css';
@import '../node_modules/@syncfusion/ej2-navigations/styles/material.css';
@import '../node_modules/@syncfusion/ej2-popups/styles/material.css';
@import '../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-grids/styles/material.css';
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
</style>
<template>
<ejs-spreadsheet ref="spreadsheet" :openUrl="openUrl" :allowOpen="true" :created="created"></ejs-spreadsheet>
</template>
<script>
import { SpreadsheetComponent } from "@syncfusion/ej2-vue-spreadsheet";
export default {
name: "App",
components: {
"ejs-spreadsheet": SpreadsheetComponent
},
data: () => {
return {
openUrl: 'https://services.syncfusion.com/vue/production/api/spreadsheet/open',
}
},
methods: {
created: function () {
fetch("https://cdn.syncfusion.com/scripts/spreadsheet/Sample.xlsx") // fetch the remote url
.then((response) => {
response.blob().then((fileBlob) => { // convert the excel file to blob
let file = new File([fileBlob], "Sample.xlsx"); //convert the blob into file
this.$refs.spreadsheet.open({ file: file }); // open the file into Spreadsheet
})
})
}
}
}
</script>
<style>
@import '../node_modules/@syncfusion/ej2-base/styles/material.css';
@import '../node_modules/@syncfusion/ej2-buttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-dropdowns/styles/material.css';
@import '../node_modules/@syncfusion/ej2-inputs/styles/material.css';
@import '../node_modules/@syncfusion/ej2-navigations/styles/material.css';
@import '../node_modules/@syncfusion/ej2-popups/styles/material.css';
@import '../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-grids/styles/material.css';
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
</style>
To add custom header during open
You can add your own custom header to the open action in the Spreadsheet. For processing the data, it has to be sent from server to client side and adding customer header can provide privacy to the data with the help of Authorization Token. Through the beforeOpen
event, the custom header can be added to the request during open action.
<template>
<ejs-spreadsheet :openUrl="openUrl" :allowOpen="true" :beforeOpen="beforeOpen"></ejs-spreadsheet>
</template>
<script setup>
import { SpreadsheetComponent as EjsSpreadsheet } from "@syncfusion/ej2-vue-spreadsheet";
const openUrl = "https://services.syncfusion.com/vue/production/api/spreadsheet/open";
const beforeOpen = function (args) {
args.requestData["headers"] = {
...args.requestData,
headers: { Authorization: "YOUR TEXT" }
};
}
</script>
<style>
@import "../node_modules/@syncfusion/ej2-base/styles/material.css";
@import "../node_modules/@syncfusion/ej2-buttons/styles/material.css";
@import "../node_modules/@syncfusion/ej2-dropdowns/styles/material.css";
@import "../node_modules/@syncfusion/ej2-inputs/styles/material.css";
@import "../node_modules/@syncfusion/ej2-navigations/styles/material.css";
@import "../node_modules/@syncfusion/ej2-popups/styles/material.css";
@import "../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css";
@import "../node_modules/@syncfusion/ej2-grids/styles/material.css";
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
</style>
<template>
<ejs-spreadsheet :openUrl="openUrl" :allowOpen="true" :beforeOpen="beforeOpen"></ejs-spreadsheet>
</template>
<script>
import { SpreadsheetComponent } from "@syncfusion/ej2-vue-spreadsheet";
export default {
name: "App",
components: {
"ejs-spreadsheet": SpreadsheetComponent
},
data: () => {
return {
openUrl:
"https://services.syncfusion.com/vue/production/api/spreadsheet/open",
};
},
methods: {
beforeOpen: function (args) {
args.requestData["headers"] = {
...args.requestData,
headers: { Authorization: "YOUR TEXT" },
};
},
},
};
</script>
<style>
@import "../node_modules/@syncfusion/ej2-base/styles/material.css";
@import "../node_modules/@syncfusion/ej2-buttons/styles/material.css";
@import "../node_modules/@syncfusion/ej2-dropdowns/styles/material.css";
@import "../node_modules/@syncfusion/ej2-inputs/styles/material.css";
@import "../node_modules/@syncfusion/ej2-navigations/styles/material.css";
@import "../node_modules/@syncfusion/ej2-popups/styles/material.css";
@import "../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css";
@import "../node_modules/@syncfusion/ej2-grids/styles/material.css";
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
</style>
Open excel file into a read-only mode
You can open excel file into a read-only mode by using the openComplete
event. In this event, you must protect all the sheets and lock its used range cells by using protectSheet
and lockCells
methods
<template>
<ejs-spreadsheet ref="spreadsheet" :openUrl="openUrl" :allowOpen="true"
:openComplete="openComplete"></ejs-spreadsheet>
</template>
<script setup>
import { ref } from "vue";
import { SpreadsheetComponent as EjsSpreadsheet, getRangeAddress } from "@syncfusion/ej2-vue-spreadsheet";
const spreadsheet = ref(null);
const openUrl = 'https://services.syncfusion.com/vue/production/api/spreadsheet/open';
const openComplete = function () {
let sheets = spreadsheet.value.sheets;
for (let index = 0; index < sheets.length; index++) {
let name = spreadsheet.value.sheets[index].name;
let protectSetting = {
selectCells: true,
formatCells: false,
};
//To protect the sheet using sheet name
spreadsheet.value.protectSheet(name, protectSetting);
let address = getRangeAddress([
0,
0,
sheets[index].usedRange.rowIndex,
sheets[index].usedRange.colIndex,
]);
//To lock the used range cells
spreadsheet.value.lockCells(name + '!' + address, true);
}
}
</script>
<style>
@import '../node_modules/@syncfusion/ej2-base/styles/material.css';
@import '../node_modules/@syncfusion/ej2-buttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-dropdowns/styles/material.css';
@import '../node_modules/@syncfusion/ej2-inputs/styles/material.css';
@import '../node_modules/@syncfusion/ej2-navigations/styles/material.css';
@import '../node_modules/@syncfusion/ej2-popups/styles/material.css';
@import '../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-grids/styles/material.css';
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
</style>
<template>
<ejs-spreadsheet ref="spreadsheet" :openUrl="openUrl" :allowOpen="true"
:openComplete="openComplete"></ejs-spreadsheet>
</template>
<script>
import { SpreadsheetComponent, getRangeAddress } from "@syncfusion/ej2-vue-spreadsheet";
export default {
name: "App",
components: {
"ejs-spreadsheet": SpreadsheetComponent
},
data: () => {
return {
openUrl: 'https://services.syncfusion.com/vue/production/api/spreadsheet/open',
}
},
methods: {
openComplete: function () {
let spreadsheet = this.$refs.spreadsheet.ej2Instances;
let sheets = spreadsheet.sheets;
for (let index = 0; index < sheets.length; index++) {
let name = spreadsheet.sheets[index].name;
let protectSetting = {
selectCells: true,
formatCells: false,
};
//To protect the sheet using sheet name
spreadsheet.protectSheet(name, protectSetting);
let address = getRangeAddress([
0,
0,
sheets[index].usedRange.rowIndex,
sheets[index].usedRange.colIndex,
]);
//To lock the used range cells
spreadsheet.lockCells(name + '!' + address, true);
}
}
}
}
</script>
<style>
@import '../node_modules/@syncfusion/ej2-base/styles/material.css';
@import '../node_modules/@syncfusion/ej2-buttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-dropdowns/styles/material.css';
@import '../node_modules/@syncfusion/ej2-inputs/styles/material.css';
@import '../node_modules/@syncfusion/ej2-navigations/styles/material.css';
@import '../node_modules/@syncfusion/ej2-popups/styles/material.css';
@import '../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-grids/styles/material.css';
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
</style>
Configure JSON deserialization options
Previously, when opening a workbook JSON object into the Spreadsheet using the openFromJson method, the entire workbook, including all features specified in the JSON object, was processed and loaded into the Spreadsheet.
Now, you have the option to selectively ignore some features during the opening of the JSON object by configuring deserialization options and passing them as arguments to the openFromJson
method. This argument is optional, and if not configured, the entire workbook JSON object will be loaded without ignoring any features.
spreadsheet.openFromJson({ file: file }, { ignoreStyle: true });
Options | Description |
---|---|
onlyValues | If true, only the cell values will be loaded. |
ignoreStyle | If true, styles will be excluded when loading the JSON data. |
ignoreFormula | If true, formulas will be excluded when loading the JSON data. |
ignoreFormat | If true, number formats will be excluded when loading the JSON data. |
ignoreConditionalFormat | If true, conditional formatting will be excluded when loading the JSON data. |
ignoreValidation | If true, data validation rules will be excluded when loading the JSON data. |
ignoreFreezePane | If true, freeze panes will be excluded when loading the JSON data. |
ignoreWrap | If true, text wrapping settings will be excluded when loading the JSON data. |
ignoreChart | If true, charts will be excluded when loading the JSON data. |
ignoreImage | If true, images will be excluded when loading the JSON data. |
ignoreNote | If true, notes will be excluded when loading the JSON data. |
The following code snippet demonstrates how to configure the deserialization options and pass them as arguments to the openFromJson method:
<template>
<div>
<div id="Openfromjson">
<label id="Heading">Open From Json Options:</label> <br>
<input type="checkbox" id="valueOnly" @change="toggleCheckboxes"><label for="valueOnly">Only Values</label>
<input type="checkbox" id="style"><label for="style">Ignore Style</label>
<input type="checkbox" id="formula"><label for="formula">Ignore Formula</label>
<input type="checkbox" id="format"><label for="format">Ignore Format</label>
<input type="checkbox" id="cf"><label for="cf">Ignore CF</label>
<input type="checkbox" id="dv"><label for="dv">Ignore Validation</label>
<input type="checkbox" id="freeze"><label for="freeze">Ignore Freezepane</label>
<input type="checkbox" id="wrap"><label for="wrap">Ignore Wrap</label>
<input type="checkbox" id="chart"><label for="chart">Ignore Chart</label>
<input type="checkbox" id="image"><label for="image">Ignore Image</label>
<input type="checkbox" id="note"><label for="note">Ignore Note</label>
<ejs-uploader
:asyncSettings="path"
:success="onSuccess"
:allowedExtensions="allowExtentions"
:buttons="buttons"
:showFileList="false"
></ejs-uploader>
</div>
<ejs-spreadsheet id="spreadsheet" ref="spreadsheet" :beforeOpen="beforeOpen">
</ejs-spreadsheet>
</div>
</template>
<script setup>
import { SpreadsheetComponent as EjsSpreadsheet } from "@syncfusion/ej2-vue-spreadsheet";
import { UploaderComponent as EjsUploader } from "@syncfusion/ej2-vue-inputs";
import { getComponent } from "@syncfusion/ej2-base";
const allowExtentions = ".xlsx, .xls, .csv";
const path = {
saveUrl: "https://services.syncfusion.com/vue/production/api/FileUploader/Save",
removeUrl: "https://services.syncfusion.com/vue/production/api/FileUploader/Remove",
};
const buttons = { browse: 'Choose file' };
const onSuccess = (args) => {
var spreadsheet = getComponent(
document.getElementById("spreadsheet"),
"spreadsheet"
);
if (args.operation === "upload")
spreadsheet.open({ file: args.file.rawFile });
}
const beforeOpen = (args) => {
args.cancel = true;
var valueOnlyCheckbox = document.getElementById("valueOnly").checked;
var options = valueOnlyCheckbox ? { onlyValues: true } : createOptions;
var spreadsheet = getComponent(
document.getElementById("spreadsheet"),
"spreadsheet"
);
fetch(
'https://services.syncfusion.com/vue/production/api/spreadsheet/open',
args.requestData
).then((response) => {
response.json().then((data) => {
spreadsheet.openFromJson({ file: data }, options)
});
});
};
const createOptions = () => {
var options = {};
options.ignoreStyle = document.getElementById('style').checked;
options.ignoreFormula = document.getElementById('formula').checked;
options.ignoreFormat = document.getElementById('format').checked;
options.ignoreConditionalFormat = document.getElementById('cf').checked;
options.ignoreValidation = document.getElementById('dv').checked;
options.ignoreFreezePane = document.getElementById('freeze').checked;
options.ignoreWrap = document.getElementById('wrap').checked;
options.ignoreChart = document.getElementById('chart').checked;
options.ignoreImage = document.getElementById('image').checked;
options.ignoreNote = document.getElementById('note').checked;
return options;
};
const toggleCheckboxes = () => {
var valueOnlyCheckbox = document.getElementById('valueOnly');
var checkboxes = document.querySelectorAll('#Openfromjson input[type="checkbox"]:not(#valueOnly)');
checkboxes.forEach(checkbox => {
(checkbox).disabled = valueOnlyCheckbox.checked;
if (valueOnlyCheckbox.checked) {
(checkbox).checked = false;
}
});
};
</script>
<style>
@import '../node_modules/@syncfusion/ej2-base/styles/material.css';
@import '../node_modules/@syncfusion/ej2-buttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-dropdowns/styles/material.css';
@import '../node_modules/@syncfusion/ej2-inputs/styles/material.css';
@import '../node_modules/@syncfusion/ej2-navigations/styles/material.css';
@import '../node_modules/@syncfusion/ej2-popups/styles/material.css';
@import '../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-grids/styles/material.css';
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
#Openfromjson {
margin-top: 10px;
margin-bottom: 20px;
}
#Openfromjson input[type="checkbox"] {
margin: 7px;
}
#Openfromjson label {
font-size: 14px;
}
#Heading {
font-weight: bold;
margin-left: 6px;
}
</style>
<template>
<div>
<div id="Openfromjson">
<label id="Heading">Open From Json Options:</label> <br>
<input type="checkbox" id="valueOnly" @change="toggleCheckboxes"><label for="valueOnly">Only Values</label>
<input type="checkbox" id="style"><label for="style">Ignore Style</label>
<input type="checkbox" id="formula"><label for="formula">Ignore Formula</label>
<input type="checkbox" id="format"><label for="format">Ignore Format</label>
<input type="checkbox" id="cf"><label for="cf">Ignore CF</label>
<input type="checkbox" id="dv"><label for="dv">Ignore Validation</label>
<input type="checkbox" id="freeze"><label for="freeze">Ignore Freezepane</label>
<input type="checkbox" id="wrap"><label for="wrap">Ignore Wrap</label>
<input type="checkbox" id="chart"><label for="chart">Ignore Chart</label>
<input type="checkbox" id="image"><label for="image">Ignore Image</label>
<input type="checkbox" id="note"><label for="note">Ignore Note</label>
<ejs-uploader
:asyncSettings="path"
:success="onSuccess"
:allowedExtensions="allowExtentions"
:buttons="buttons"
:showFileList="false"
></ejs-uploader>
</div>
<ejs-spreadsheet id="spreadsheet" ref="spreadsheet" :beforeOpen="beforeOpen">
</ejs-spreadsheet>
</div>
</template>
<script>
import { SpreadsheetComponent } from '@syncfusion/ej2-vue-spreadsheet';
import { UploaderComponent } from "@syncfusion/ej2-vue-inputs";
import { getComponent } from "@syncfusion/ej2-base";
export default {
components: {
'ejs-uploader': UploaderComponent,
'ejs-spreadsheet': SpreadsheetComponent,
},
data: () => {
return {
allowExtentions: ".xlsx, .xls, .csv",
path: {
saveUrl:
"https://services.syncfusion.com/vue/production/api/FileUploader/Save",
removeUrl:
"https://services.syncfusion.com/vue/production/api/FileUploader/Remove",
},
buttons: { browse: 'Choose file' }
}
},
methods: {
onSuccess: function(args) {
var spreadsheet = getComponent(
document.getElementById("spreadsheet"),
"spreadsheet"
);
if (args.operation === "upload")
spreadsheet.open({ file: args.file.rawFile });
},
beforeOpen: function(args) {
args.cancel = true;
var valueOnlyCheckbox = document.getElementById("valueOnly").checked;
var options = valueOnlyCheckbox ? { onlyValues: true } : this.createOptions();
var spreadsheet = getComponent(
document.getElementById("spreadsheet"),
"spreadsheet"
);
fetch(
'https://services.syncfusion.com/vue/production/api/spreadsheet/open',
args.requestData
).then((response) => {
response.json().then((data) => {
spreadsheet.openFromJson({ file: data }, options)
});
});
},
createOptions: function() {
var options = {};
options.ignoreStyle = document.getElementById('style').checked;
options.ignoreFormula = document.getElementById('formula').checked;
options.ignoreFormat = document.getElementById('format').checked;
options.ignoreConditionalFormat = document.getElementById('cf').checked;
options.ignoreValidation = document.getElementById('dv').checked;
options.ignoreFreezePane = document.getElementById('freeze').checked;
options.ignoreWrap = document.getElementById('wrap').checked;
options.ignoreChart = document.getElementById('chart').checked;
options.ignoreImage = document.getElementById('image').checked;
options.ignoreNote = document.getElementById('note').checked;
return options;
},
toggleCheckboxes: () => {
var valueOnlyCheckbox = document.getElementById('valueOnly');
var checkboxes = document.querySelectorAll('#Openfromjson input[type="checkbox"]:not(#valueOnly)');
checkboxes.forEach(checkbox => {
(checkbox).disabled = valueOnlyCheckbox.checked;
if (valueOnlyCheckbox.checked) {
(checkbox).checked = false;
}
});
}
}
}
</script>
<style>
@import '../node_modules/@syncfusion/ej2-base/styles/material.css';
@import '../node_modules/@syncfusion/ej2-buttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-dropdowns/styles/material.css';
@import '../node_modules/@syncfusion/ej2-inputs/styles/material.css';
@import '../node_modules/@syncfusion/ej2-navigations/styles/material.css';
@import '../node_modules/@syncfusion/ej2-popups/styles/material.css';
@import '../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-grids/styles/material.css';
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
#Openfromjson {
margin-top: 10px;
margin-bottom: 20px;
}
#Openfromjson input[type="checkbox"] {
margin: 7px;
}
#Openfromjson label {
font-size: 14px;
}
#Heading {
font-weight: bold;
margin-left: 6px;
}
</style>
External workbook confirmation dialog
When you open an excel file that contains external workbook references, you will see a confirmation dialog. This dialog allows you to either continue with the file opening or cancel the operation. This confirmation dialog will appear only if you set the AllowExternalWorkbook
property value to false during the open request, as shown below. This prevents the spreadsheet from displaying inconsistent data.
public IActionResult Open(IFormCollection openRequest)
{
OpenRequest open = new OpenRequest();
open.AllowExternalWorkbook = false;
open.File = openRequest.Files[0];
return Content(Workbook.Open(open));
}
This feature is only applicable when importing an Excel file and not when loading JSON data or binding cell data.
Supported file formats
The following list of Excel file formats are supported in Spreadsheet:
- MS Excel (.xlsx)
- MS Excel 97-2003 (.xls)
- Comma Separated Values (.csv)
- Excel Macro-Enabled Workbook (.xlsm)
- Excel Binary Workbook(.xlsb)
Save
The Spreadsheet control saves its data, style, format, and more as Excel file document. To enable this feature, set allowSave
as true
and assign service url to the saveUrl
property.
User Interface:
In user interface, you can save Spreadsheet data as Excel document by clicking File > Save As
menu item in ribbon.
The following sample shows the Save
option by using the saveUrl
property in the Spreadsheet control. You can also use the beforeSave
event to trigger before saving the Spreadsheet as an Excel file.
<template>
<ejs-spreadsheet :saveUrl="saveUrl" :allowSave="true" :beforeSave="beforeSave">
<e-sheets>
<e-sheet>
<e-ranges>
<e-range :dataSource="dataSource"></e-range>
</e-ranges>
<e-columns>
<e-column :width="width1"></e-column>
<e-column :width="width2"></e-column>
<e-column :width="width2"></e-column>
<e-column :width="width1"></e-column>
<e-column :width="width2"></e-column>
<e-column :width="width3"></e-column>
</e-columns>
</e-sheet>
</e-sheets>
</ejs-spreadsheet>
</template>
<script setup>
import { SpreadsheetComponent as EjsSpreadsheet, ColumnsDirective as EColumns, ColumnDirective as EColumn, RangesDirective as ERanges, RangeDirective as ERange, SheetsDirective as ESheets, SheetDirective as ESheet } from "@syncfusion/ej2-vue-spreadsheet";
import { data } from './data.js';
const dataSource = data;
const width1 = 180;
const width2 = 130;
const width3 = 120;
const saveUrl = 'https://services.syncfusion.com/vue/production/api/spreadsheet/save';
const beforeSave = function (args) {
// your code snippets here
}
</script>
<style>
@import '../node_modules/@syncfusion/ej2-base/styles/material.css';
@import '../node_modules/@syncfusion/ej2-buttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-dropdowns/styles/material.css';
@import '../node_modules/@syncfusion/ej2-inputs/styles/material.css';
@import '../node_modules/@syncfusion/ej2-navigations/styles/material.css';
@import '../node_modules/@syncfusion/ej2-popups/styles/material.css';
@import '../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-grids/styles/material.css';
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
</style>
<template>
<ejs-spreadsheet :saveUrl="saveUrl" :allowSave="true" :beforeSave="beforeSave">
<e-sheets>
<e-sheet>
<e-ranges>
<e-range :dataSource="dataSource"></e-range>
</e-ranges>
<e-columns>
<e-column :width="width1"></e-column>
<e-column :width="width2"></e-column>
<e-column :width="width2"></e-column>
<e-column :width="width1"></e-column>
<e-column :width="width2"></e-column>
<e-column :width="width3"></e-column>
</e-columns>
</e-sheet>
</e-sheets></ejs-spreadsheet>
</template>
<script>
import { SpreadsheetComponent, ColumnsDirective, ColumnDirective, RangesDirective, RangeDirective, SheetsDirective, SheetDirective } from "@syncfusion/ej2-vue-spreadsheet";
import { data } from './data.js';
export default {
name: "App",
components: {
"ejs-spreadsheet": SpreadsheetComponent,
"e-sheets": SheetsDirective,
"e-sheet": SheetDirective,
"e-ranges": RangesDirective,
"e-range": RangeDirective,
"e-columns": ColumnsDirective,
"e-column": ColumnDirective
},
data: () => {
return {
dataSource: data,
width1: 180,
width2: 130,
width3: 120,
saveUrl: 'https://services.syncfusion.com/vue/production/api/spreadsheet/save'
}
},
methods: {
beforeSave: function (args) {
// your code snippets here
}
}
}
</script>
<style>
@import '../node_modules/@syncfusion/ej2-base/styles/material.css';
@import '../node_modules/@syncfusion/ej2-buttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-dropdowns/styles/material.css';
@import '../node_modules/@syncfusion/ej2-inputs/styles/material.css';
@import '../node_modules/@syncfusion/ej2-navigations/styles/material.css';
@import '../node_modules/@syncfusion/ej2-popups/styles/material.css';
@import '../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-grids/styles/material.css';
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
</style>
Please find the below table for the beforeSave event arguments.
Parameter | Type | Description |
---|---|---|
url | string | Specifies the save url. |
fileName | string | Specifies the file name. |
saveType | SaveType | Specifies the saveType like Xlsx, Xls, Csv and Pdf. |
customParams | object | Passing the custom parameters from client to server while performing save operation. |
isFullPost | boolean | It sends the form data from client to server, when set to true. It fetches the data from client to server and returns the data from server to client, when set to false. |
needBlobData | boolean | You can get the blob data if set to true. |
cancel | boolean | To prevent the save operations. |
To send and receive custom params from client to server
Passing the custom parameters from client to server by using beforeSave
event.
<template>
<ejs-spreadsheet :saveUrl="saveUrl" :allowSave="true" :beforeSave="beforeSave">
<e-sheets>
<e-sheet>
<e-ranges>
<e-range :dataSource="dataSource"></e-range>
</e-ranges>
<e-columns>
<e-column :width="width1"></e-column>
<e-column :width="width2"></e-column>
<e-column :width="width2"></e-column>
<e-column :width="width1"></e-column>
<e-column :width="width2"></e-column>
<e-column :width="width3"></e-column>
</e-columns>
</e-sheet>
</e-sheets>
</ejs-spreadsheet>
</template>
<script setup>
import { SpreadsheetComponent as EjsSpreadsheet, ColumnsDirective as EColumns, ColumnDirective as EColumn, RangesDirective as ERanges, RangeDirective as ERange, SheetsDirective as ESheets, SheetDirective as ESheet } from "@syncfusion/ej2-vue-spreadsheet";
import { data } from './data.js';
const dataSource = data;
const width1 = 180;
const width2 = 130;
const width3 = 120;
const saveUrl = 'https://services.syncfusion.com/vue/production/api/spreadsheet/save';
const beforeSave = function (args) {
args.customParams = { customParams: 'you can pass custom params in server side' }; // you can pass the custom params
}
</script>
<style>
@import '../node_modules/@syncfusion/ej2-base/styles/material.css';
@import '../node_modules/@syncfusion/ej2-buttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-dropdowns/styles/material.css';
@import '../node_modules/@syncfusion/ej2-inputs/styles/material.css';
@import '../node_modules/@syncfusion/ej2-navigations/styles/material.css';
@import '../node_modules/@syncfusion/ej2-popups/styles/material.css';
@import '../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-grids/styles/material.css';
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
</style>
<template>
<ejs-spreadsheet :saveUrl="saveUrl" :allowSave="true" :beforeSave="beforeSave">
<e-sheets>
<e-sheet>
<e-ranges>
<e-range :dataSource="dataSource"></e-range>
</e-ranges>
<e-columns>
<e-column :width="width1"></e-column>
<e-column :width="width2"></e-column>
<e-column :width="width2"></e-column>
<e-column :width="width1"></e-column>
<e-column :width="width2"></e-column>
<e-column :width="width3"></e-column>
</e-columns>
</e-sheet>
</e-sheets>
</ejs-spreadsheet>
</template>
<script>
import { SpreadsheetComponent, ColumnsDirective, ColumnDirective, RangesDirective, RangeDirective, SheetsDirective, SheetDirective } from "@syncfusion/ej2-vue-spreadsheet";
import { data } from './data.js';
export default {
name: "App",
components: {
"ejs-spreadsheet": SpreadsheetComponent,
"e-sheets": SheetsDirective,
"e-sheet": SheetDirective,
"e-ranges": RangesDirective,
"e-range": RangeDirective,
"e-columns": ColumnsDirective,
"e-column": ColumnDirective
},
data: () => {
return {
dataSource: data,
width1: 180,
width2: 130,
width3: 120,
saveUrl: 'https://services.syncfusion.com/vue/production/api/spreadsheet/save'
}
},
methods: {
beforeSave: function (args) {
args.customParams = { customParams: 'you can pass custom params in server side' }; // you can pass the custom params
}
}
}
</script>
<style>
@import '../node_modules/@syncfusion/ej2-base/styles/material.css';
@import '../node_modules/@syncfusion/ej2-buttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-dropdowns/styles/material.css';
@import '../node_modules/@syncfusion/ej2-inputs/styles/material.css';
@import '../node_modules/@syncfusion/ej2-navigations/styles/material.css';
@import '../node_modules/@syncfusion/ej2-popups/styles/material.css';
@import '../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-grids/styles/material.css';
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
</style>
Server side code snippets:
public IActionResult Save(SaveSettings saveSettings, string customParams)
{
Console.WriteLine(customParams); // you can get the custom params in controller side
return Workbook.Save(saveSettings);
}
To add custom header during save
You can add your own custom header to the save action in the Spreadsheet. For processing the data, it has to be sent from client to server side and adding customer header can provide privacy to the data with the help of Authorization Token. Through the fileMenuItemSelect
event, the custom header can be added to the request during save action.
<template>
<ejs-spreadsheet ref="spreadsheet" :saveUrl="saveUrl" :allowSave="true" :fileMenuItemSelect="fileMenuItemSelect">
<e-sheets>
<e-sheet>
<e-ranges>
<e-range :dataSource="dataSource"></e-range>
</e-ranges>
<e-columns>
<e-column :width="width1"></e-column>
<e-column :width="width2"></e-column>
<e-column :width="width2"></e-column>
<e-column :width="width1"></e-column>
<e-column :width="width2"></e-column>
<e-column :width="width3"></e-column>
</e-columns>
</e-sheet> </e-sheets></ejs-spreadsheet>
</template>
<script setup>
import { ref } from "vue";
import { SpreadsheetComponent as EjsSpreadsheet, ColumnsDirective as EColumns, ColumnDirective as EColumn, RangesDirective as ERanges, RangeDirective as ERange, SheetsDirective as ESheets, SheetDirective as ESheet } from "@syncfusion/ej2-vue-spreadsheet";
import { createElement } from "@syncfusion/ej2-base";
import { data } from "./data.js";
const spreadsheet = ref(null);
const dataSource = data;
const width1 = 180;
const width2 = 130;
const width3 = 120;
const saveUrl = "https://services.syncfusion.com/vue/production/api/spreadsheet/save";
const fileMenuItemSelect = function (args) {
if (args.item.text === "Microsoft Excel") {
args.cancel = true;
spreadsheet.value.saveAsJson().then((response) => {
let formData = new FormData();
formData.append(
"JSONData",
JSON.stringify(response.jsonObject.Workbook)
);
formData.append("fileName", "Sample");
formData.append("saveType", "Xlsx");
formData.append("pdfLayoutSettings", JSON.stringify({ fitSheetOnOnePage: false, orientation: "Portrait" }));
fetch(
"https://services.syncfusion.com/vue/production/api/spreadsheet/save",
{
method: "POST",
headers: { Authorization: "YOUR TEXT" },
body: formData,
}
).then((response) => {
response.blob().then((data) => {
let anchor = createElement("a", {
attrs: { download: "Sample.xlsx" },
});
let url = URL.createObjectURL(data);
anchor.href = url;
document.body.appendChild(anchor);
anchor.click();
URL.revokeObjectURL(url);
document.body.removeChild(anchor);
});
});
});
}
}
</script>
<style>
@import "../node_modules/@syncfusion/ej2-base/styles/material.css";
@import "../node_modules/@syncfusion/ej2-buttons/styles/material.css";
@import "../node_modules/@syncfusion/ej2-dropdowns/styles/material.css";
@import "../node_modules/@syncfusion/ej2-inputs/styles/material.css";
@import "../node_modules/@syncfusion/ej2-navigations/styles/material.css";
@import "../node_modules/@syncfusion/ej2-popups/styles/material.css";
@import "../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css";
@import "../node_modules/@syncfusion/ej2-grids/styles/material.css";
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
</style>
<template>
<ejs-spreadsheet ref="spreadsheet" :saveUrl="saveUrl" :allowSave="true" :fileMenuItemSelect="fileMenuItemSelect">
<e-sheets>
<e-sheet>
<e-ranges>
<e-range :dataSource="dataSource"></e-range>
</e-ranges>
<e-columns>
<e-column :width="width1"></e-column>
<e-column :width="width2"></e-column>
<e-column :width="width2"></e-column>
<e-column :width="width1"></e-column>
<e-column :width="width2"></e-column>
<e-column :width="width3"></e-column>
</e-columns>
</e-sheet> </e-sheets></ejs-spreadsheet>
</template>
<script>
import { SpreadsheetComponent, ColumnsDirective, ColumnDirective, RangesDirective, RangeDirective, SheetsDirective, SheetDirective } from "@syncfusion/ej2-vue-spreadsheet";
import { createElement } from "@syncfusion/ej2-base";
import { data } from "./data.js";
export default {
name: "App",
components: {
"ejs-spreadsheet": SpreadsheetComponent,
"e-sheets": SheetsDirective,
"e-sheet": SheetDirective,
"e-ranges": RangesDirective,
"e-range": RangeDirective,
"e-columns": ColumnsDirective,
"e-column": ColumnDirective
},
data: () => {
return {
dataSource: data,
width1: 180,
width2: 130,
width3: 120,
saveUrl:
"https://services.syncfusion.com/vue/production/api/spreadsheet/save"
};
},
methods: {
fileMenuItemSelect: function (args) {
var spreadsheet = this.$refs.spreadsheet;
if (args.item.text === "Microsoft Excel") {
args.cancel = true;
spreadsheet.saveAsJson().then((response) => {
var formData = new FormData();
formData.append(
"JSONData",
JSON.stringify(response.jsonObject.Workbook)
);
formData.append("fileName", "Sample");
formData.append("saveType", "Xlsx");
formData.append("pdfLayoutSettings", JSON.stringify({ fitSheetOnOnePage: false, orientation: "Portrait" }));
fetch(
"https://services.syncfusion.com/vue/production/api/spreadsheet/save",
{
method: "POST",
headers: { Authorization: "YOUR TEXT" },
body: formData,
}
).then((response) => {
response.blob().then((data) => {
var anchor = createElement("a", {
attrs: { download: "Sample.xlsx" },
});
var url = URL.createObjectURL(data);
anchor.href = url;
document.body.appendChild(anchor);
anchor.click();
URL.revokeObjectURL(url);
document.body.removeChild(anchor);
});
});
});
}
}
}
};
</script>
<style>
@import "../node_modules/@syncfusion/ej2-base/styles/material.css";
@import "../node_modules/@syncfusion/ej2-buttons/styles/material.css";
@import "../node_modules/@syncfusion/ej2-dropdowns/styles/material.css";
@import "../node_modules/@syncfusion/ej2-inputs/styles/material.css";
@import "../node_modules/@syncfusion/ej2-navigations/styles/material.css";
@import "../node_modules/@syncfusion/ej2-popups/styles/material.css";
@import "../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css";
@import "../node_modules/@syncfusion/ej2-grids/styles/material.css";
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
</style>
To change the PDF orientation
By default, the PDF document is created in Portrait orientation. You can change the orientation of the PDF document by using the args.pdfLayoutSettings.orientation
argument settings in the beforeSave
event.
The possible values are:
- Portrait - Used to display content in a vertical layout.
- Landscape - Used to display content in a horizontal layout.
<template>
<ejs-spreadsheet :saveUrl="saveUrl" :allowSave="true" :beforeSave="beforeSave">
<e-sheets>
<e-sheet>
<e-ranges>
<e-range :dataSource="dataSource"></e-range>
</e-ranges>
<e-columns>
<e-column :width="width1"></e-column>
<e-column :width="width2"></e-column>
<e-column :width="width2"></e-column>
<e-column :width="width1"></e-column>
<e-column :width="width2"></e-column>
<e-column :width="width3"></e-column>
</e-columns>
</e-sheet>
</e-sheets>
</ejs-spreadsheet>
</template>
<script setup>
import { SpreadsheetComponent as EjsSpreadsheet, ColumnsDirective as EColumns, ColumnDirective as EColumn, RangesDirective as ERanges, RangeDirective as ERange, SheetsDirective as ESheets, SheetDirective as ESheet } from "@syncfusion/ej2-vue-spreadsheet";
import { data } from './data.js';
const dataSource = data;
const width1 = 180;
const width2 = 130;
const width3 = 120;
const saveUrl = 'https://services.syncfusion.com/vue/production/api/spreadsheet/save';
const beforeSave = function (args) {
args.pdfLayoutSettings.orientation = 'Landscape'; // You can change the orientation of the PDF document
}
</script>
<style>
@import '../node_modules/@syncfusion/ej2-base/styles/material.css';
@import '../node_modules/@syncfusion/ej2-buttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-dropdowns/styles/material.css';
@import '../node_modules/@syncfusion/ej2-inputs/styles/material.css';
@import '../node_modules/@syncfusion/ej2-navigations/styles/material.css';
@import '../node_modules/@syncfusion/ej2-popups/styles/material.css';
@import '../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-grids/styles/material.css';
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
</style>
<template>
<ejs-spreadsheet :saveUrl="saveUrl" :allowSave="true" :beforeSave="beforeSave">
<e-sheets>
<e-sheet>
<e-ranges>
<e-range :dataSource="dataSource"></e-range>
</e-ranges>
<e-columns>
<e-column :width="width1"></e-column>
<e-column :width="width2"></e-column>
<e-column :width="width2"></e-column>
<e-column :width="width1"></e-column>
<e-column :width="width2"></e-column>
<e-column :width="width3"></e-column>
</e-columns>
</e-sheet>
</e-sheets>
</ejs-spreadsheet>
</template>
<script>
import { SpreadsheetComponent, ColumnsDirective, ColumnDirective, RangesDirective, RangeDirective, SheetsDirective, SheetDirective } from "@syncfusion/ej2-vue-spreadsheet";
import { data } from './data.js';
export default {
name: "App",
components: {
"ejs-spreadsheet": SpreadsheetComponent,
"e-sheets": SheetsDirective,
"e-sheet": SheetDirective,
"e-ranges": RangesDirective,
"e-range": RangeDirective,
"e-columns": ColumnsDirective,
"e-column": ColumnDirective
},
data: () => {
return {
dataSource: data,
width1: 180,
width2: 130,
width3: 120,
saveUrl: 'https://services.syncfusion.com/vue/production/api/spreadsheet/save'
}
},
methods: {
beforeSave: function (args) {
args.pdfLayoutSettings.orientation = 'Landscape'; // You can change the orientation of the PDF document
}
}
}
</script>
<style>
@import '../node_modules/@syncfusion/ej2-base/styles/material.css';
@import '../node_modules/@syncfusion/ej2-buttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-dropdowns/styles/material.css';
@import '../node_modules/@syncfusion/ej2-inputs/styles/material.css';
@import '../node_modules/@syncfusion/ej2-navigations/styles/material.css';
@import '../node_modules/@syncfusion/ej2-popups/styles/material.css';
@import '../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-grids/styles/material.css';
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
</style>
Configure JSON serialization options
Previously, when saving the Spreadsheet as a workbook JSON object using the saveAsJson method, the entire workbook with all loaded features were processed and saved as a JSON object.
Now, you have the option to selectively ignore some features while saving the Spreadsheet as a JSON object by configuring serialization options and passing them as arguments to the saveAsJson
method. This argument is optional, and if not configured, the entire workbook JSON object will be saved without ignoring any features.
spreadsheet.saveAsJson({ onlyValues: true });
Options | Description |
---|---|
onlyValues | If true, includes only the cell values in the JSON output. |
ignoreStyle | If true, excludes styles from the JSON output. |
ignoreFormula | If true, excludes formulas from the JSON output. |
ignoreFormat | If true, excludes number formats from the JSON output. |
ignoreConditionalFormat | If true, excludes conditional formatting from the JSON output. |
ignoreValidation | If true, excludes data validation rules from the JSON output. |
ignoreFreezePane | If true, excludes freeze panes from the JSON output. |
ignoreWrap | If true, excludes text wrapping settings from the JSON output. |
ignoreChart | If true, excludes charts from the JSON output. |
ignoreImage | If true, excludes images from the JSON output. |
ignoreNote | If true, excludes notes from the JSON output. |
The following code snippet demonstrates how to configure the serialization options and pass them as arguments to the saveAsJson method:
<template>
<div class="control-section" id="spreadsheet-default-section">
<div id="Saveasjson">
<label id="Heading">Save As Json Options:</label> <br>
<input type="checkbox" id="valueOnly" @change="toggleCheckboxes"><label for="valueOnly">Only Values</label>
<input type="checkbox" id="style"><label for="style">Ignore Style</label>
<input type="checkbox" id="formula"><label for="formula">Ignore Formula</label>
<input type="checkbox" id="format"><label for="format">Ignore Format</label>
<input type="checkbox" id="cf"><label for="cf">Ignore CF</label>
<input type="checkbox" id="dv"><label for="dv">Ignore Validation</label>
<input type="checkbox" id="freeze"><label for="freeze">Ignore Freezepane</label>
<input type="checkbox" id="wrap"><label for="wrap">Ignore Wrap</label>
<input type="checkbox" id="chart"><label for="chart">Ignore Chart</label>
<input type="checkbox" id="image"><label for="image">Ignore Image</label>
<input type="checkbox" id="note"><label for="note">Ignore Note</label>
<button id="save" @click="saveFile()">Save with JSON Serialization</button>
</div>
<ejs-spreadsheet id="spreadsheet" ref="spreadsheet">
</ejs-spreadsheet>
</div>
</template>
<script setup>
import { ref } from "vue";
import { SpreadsheetComponent as EjsSpreadsheet } from "@syncfusion/ej2-vue-spreadsheet";
import { createElement } from "@syncfusion/ej2-base";
const spreadsheet = ref(null);
const saveFile = () => {
var spreadsheet = this.$refs.spreadsheet;
var valueOnlyCheckbox = document.getElementById("valueOnly").checked;
var options = valueOnlyCheckbox ? { onlyValues: true } : createOptions;
spreadsheet.saveAsJson(options).then((response) => {
var formData = new FormData();
formData.append(
'JSONData',
JSON.stringify(response.jsonObject.Workbook)
);
formData.append('fileName', 'Sample');
formData.append('saveType', 'Xlsx');
formData.append('pdfLayoutSettings', JSON.stringify({ fitSheetOnOnePage: false, orientation: 'Portrait' })),
fetch(
'https://services.syncfusion.com/vue/production/api/spreadsheet/save',
{
method: 'POST',
body: formData,
}
).then((response) => {
response.blob().then((data) => {
var anchor = createElement('a', {
attrs: { download: 'Sample.xlsx' },
});
var url = URL.createObjectURL(data);
anchor.href = url;
document.body.appendChild(anchor);
anchor.click();
URL.revokeObjectURL(url);
document.body.removeChild(anchor);
});
});
});
};
const createOptions = () => {
var options = {};
options.ignoreStyle = document.getElementById('style').checked;
options.ignoreFormula = document.getElementById('formula').checked;
options.ignoreFormat = document.getElementById('format').checked;
options.ignoreConditionalFormat = document.getElementById('cf').checked;
options.ignoreValidation = document.getElementById('dv').checked;
options.ignoreFreezePane = document.getElementById('freeze').checked;
options.ignoreWrap = document.getElementById('wrap').checked;
options.ignoreChart = document.getElementById('chart').checked;
options.ignoreImage = document.getElementById('image').checked;
options.ignoreNote = document.getElementById('note').checked;
return options;
};
const toggleCheckboxes = () => {
var valueOnlyCheckbox = document.getElementById('valueOnly');
var checkboxes = document.querySelectorAll('#Saveasjson input[type="checkbox"]:not(#valueOnly)');
checkboxes.forEach(checkbox => {
checkbox.disabled = valueOnlyCheckbox.checked;
if (valueOnlyCheckbox.checked) {
checkbox.checked = false;
}
});
}
</script>
<style>
@import '../node_modules/@syncfusion/ej2-base/styles/material.css';
@import '../node_modules/@syncfusion/ej2-buttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-dropdowns/styles/material.css';
@import '../node_modules/@syncfusion/ej2-inputs/styles/material.css';
@import '../node_modules/@syncfusion/ej2-navigations/styles/material.css';
@import '../node_modules/@syncfusion/ej2-popups/styles/material.css';
@import '../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-grids/styles/material.css';
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
#Saveasjson {
margin-top: 10px;
margin-bottom: 20px;
}
#Saveasjson input[type="checkbox"] {
margin: 7px;
}
#Saveasjson label {
font-size: 14px;
}
#Heading {
font-weight: bold;
}
</style>
<template>
<div class="control-section" id="spreadsheet-default-section">
<div id="Saveasjson">
<label id="Heading">Save As Json Options:</label> <br>
<input type="checkbox" id="valueOnly" @change="toggleCheckboxes"><label for="valueOnly">Only Values</label>
<input type="checkbox" id="style"><label for="style">Ignore Style</label>
<input type="checkbox" id="formula"><label for="formula">Ignore Formula</label>
<input type="checkbox" id="format"><label for="format">Ignore Format</label>
<input type="checkbox" id="cf"><label for="cf">Ignore CF</label>
<input type="checkbox" id="dv"><label for="dv">Ignore Validation</label>
<input type="checkbox" id="freeze"><label for="freeze">Ignore Freezepane</label>
<input type="checkbox" id="wrap"><label for="wrap">Ignore Wrap</label>
<input type="checkbox" id="chart"><label for="chart">Ignore Chart</label>
<input type="checkbox" id="image"><label for="image">Ignore Image</label>
<input type="checkbox" id="note"><label for="note">Ignore Note</label>
<button id="save" @click="saveFile()">Save with JSON Serialization</button>
</div>
<ejs-spreadsheet id="spreadsheet" ref="spreadsheet">
</ejs-spreadsheet>
</div>
</template>
<script>
import { SpreadsheetComponent } from '@syncfusion/ej2-vue-spreadsheet';
import { createElement } from "@syncfusion/ej2-base";
export default {
components: {
'ejs-spreadsheet': SpreadsheetComponent,
},
data: () => {
return {
}
},
methods: {
saveFile: function() {
var spreadsheet = this.$refs.spreadsheet;
var valueOnlyCheckbox = document.getElementById("valueOnly").checked;
var options = valueOnlyCheckbox ? { onlyValues: true } : this.createOptions();
spreadsheet.saveAsJson(options).then((response) => {
var formData = new FormData();
formData.append(
'JSONData',
JSON.stringify(response.jsonObject.Workbook)
);
formData.append('fileName', 'Sample');
formData.append('saveType', 'Xlsx');
formData.append('pdfLayoutSettings', JSON.stringify({ fitSheetOnOnePage: false, orientation: 'Portrait' })),
fetch(
'https://services.syncfusion.com/vue/production/api/spreadsheet/save',
{
method: 'POST',
body: formData,
}
).then((response) => {
response.blob().then((data) => {
var anchor = createElement('a', {
attrs: { download: 'Sample.xlsx' },
});
var url = URL.createObjectURL(data);
anchor.href = url;
document.body.appendChild(anchor);
anchor.click();
URL.revokeObjectURL(url);
document.body.removeChild(anchor);
});
});
});
},
createOptions: function() {
var options = {};
options.ignoreStyle = document.getElementById('style').checked;
options.ignoreFormula = document.getElementById('formula').checked;
options.ignoreFormat = document.getElementById('format').checked;
options.ignoreConditionalFormat = document.getElementById('cf').checked;
options.ignoreValidation = document.getElementById('dv').checked;
options.ignoreFreezePane = document.getElementById('freeze').checked;
options.ignoreWrap = document.getElementById('wrap').checked;
options.ignoreChart = document.getElementById('chart').checked;
options.ignoreImage = document.getElementById('image').checked;
options.ignoreNote = document.getElementById('note').checked;
return options;
},
toggleCheckboxes: () => {
var valueOnlyCheckbox = document.getElementById('valueOnly');
var checkboxes = document.querySelectorAll('#Saveasjson input[type="checkbox"]:not(#valueOnly)');
checkboxes.forEach(checkbox => {
checkbox.disabled = valueOnlyCheckbox.checked;
if (valueOnlyCheckbox.checked) {
checkbox.checked = false;
}
});
}
}
}
</script>
<style>
@import '../node_modules/@syncfusion/ej2-base/styles/material.css';
@import '../node_modules/@syncfusion/ej2-buttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-dropdowns/styles/material.css';
@import '../node_modules/@syncfusion/ej2-inputs/styles/material.css';
@import '../node_modules/@syncfusion/ej2-navigations/styles/material.css';
@import '../node_modules/@syncfusion/ej2-popups/styles/material.css';
@import '../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-grids/styles/material.css';
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
#Saveasjson {
margin-top: 10px;
margin-bottom: 20px;
}
#Saveasjson input[type="checkbox"] {
margin: 7px;
}
#Saveasjson label {
font-size: 14px;
}
#Heading {
font-weight: bold;
}
</style>
Supported file formats
The following list of Excel file formats are supported in Spreadsheet:
- MS Excel (.xlsx)
- MS Excel 97-2003 (.xls)
- Comma Separated Values (.csv)
- Portable Document Format (.pdf)
Methods
To save the Spreadsheet document as an xlsx, xls, csv, or pdf
file, by using save method should be called with the url
, fileName
and saveType
as parameters. The following code example shows to save the spreadsheet file as an xlsx, xls, csv, or pdf
in the button click event.
<template>
<div>
<ejs-dropdownbutton :items='items' :select='itemSelect'>Save</ejs-dropdownbutton>
<ejs-spreadsheet ref="spreadsheet">
<e-sheets>
<e-sheet>
<e-ranges>
<e-range :dataSource="dataSource"></e-range>
</e-ranges>
<e-columns>
<e-column :width="width1"></e-column>
<e-column :width="width2"></e-column>
</e-columns>
</e-sheet>
</e-sheets>
</ejs-spreadsheet>
</div>
</template>
<script setup>
import { ref } from "vue";
import { SpreadsheetComponent as EjsSpreadsheet, ColumnsDirective as EColumns, ColumnDirective as EColumn, RangesDirective as ERanges, RangeDirective as ERange, SheetsDirective as ESheets, SheetDirective as ESheet, getRangeIndexes } from "@syncfusion/ej2-vue-spreadsheet";
import { addClass, removeClass } from '@syncfusion/ej2-base';
import { DropDownButtonComponent as EjsDropdownbutton } from "@syncfusion/ej2-vue-splitbuttons";
import { defaultData } from './data.js';
const spreadsheet = ref(null);
const dataSource = defaultData
const width1 = 130
const width2 = 96
const items = [
{
text: "Save As xlsx"
},
{
text: "Save As xls"
},
{
text: "Save As csv"
},
{
text: "Save As pdf"
}
];
const itemSelect = function (args) {
if (args.item.text === 'Save As xlsx')
spreadsheet.value.save({ url: 'https://services.syncfusion.com/vue/production/api/spreadsheet/save', fileName: "Sample", saveType: "Xlsx" });
if (args.item.text === 'Save As xls')
spreadsheet.value.save({ url: 'https://services.syncfusion.com/vue/production/api/spreadsheet/save', fileName: "Sample", saveType: "Xls" });
if (args.item.text === 'Save As csv')
spreadsheet.value.save({ url: 'https://services.syncfusion.com/vue/production/api/spreadsheet/save', fileName: "Sample", saveType: "Csv" });
if (args.item.text === 'Save As pdf')
spreadsheet.value.save({ url: 'https://services.syncfusion.com/vue/production/api/spreadsheet/save', fileName: "Sample", saveType: "Pdf" });
}
</script>
<style>
@import '../node_modules/@syncfusion/ej2-base/styles/material.css';
@import '../node_modules/@syncfusion/ej2-buttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-dropdowns/styles/material.css';
@import '../node_modules/@syncfusion/ej2-inputs/styles/material.css';
@import '../node_modules/@syncfusion/ej2-navigations/styles/material.css';
@import '../node_modules/@syncfusion/ej2-popups/styles/material.css';
@import '../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-grids/styles/material.css';
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
</style>
<template>
<div>
<ejs-dropdownbutton :items='items' :select='itemSelect'>Save</ejs-dropdownbutton>
<ejs-spreadsheet ref="spreadsheet">
<e-sheets>
<e-sheet>
<e-ranges>
<e-range :dataSource="dataSource"></e-range>
</e-ranges>
<e-columns>
<e-column :width="width1"></e-column>
<e-column :width="width2"></e-column>
</e-columns>
</e-sheet>
</e-sheets>
</ejs-spreadsheet>
</div>
</template>
<script>
import { SpreadsheetComponent, ColumnsDirective, ColumnDirective, RowsDirective, RowDirective, CellsDirective, CellDirective, RangesDirective, RangeDirective, SheetsDirective, SheetDirective, getRangeIndexes } from "@syncfusion/ej2-vue-spreadsheet";
import { addClass, removeClass } from '@syncfusion/ej2-base';
import { DropDownButtonComponent } from "@syncfusion/ej2-vue-splitbuttons";
import { defaultData } from './data.js';
export default {
name: "App",
components: {
"ejs-dropdownbutton": DropDownButtonComponent,
"ejs-spreadsheet": SpreadsheetComponent,
"e-sheets": SheetsDirective,
"e-sheet": SheetDirective,
"e-ranges": RangesDirective,
"e-range": RangeDirective,
"e-columns": ColumnsDirective,
"e-column": ColumnDirective
},
data: () => {
return {
dataSource: defaultData,
width1: 130,
width2: 96,
items: [
{
text: "Save As xlsx"
},
{
text: "Save As xls"
},
{
text: "Save As csv"
},
{
text: "Save As pdf"
}
]
}
},
methods: {
itemSelect: function (args) {
let spreadsheet = this.$refs.spreadsheet;
if (args.item.text === 'Save As xlsx')
spreadsheet.save({ url: 'https://services.syncfusion.com/vue/production/api/spreadsheet/save', fileName: "Sample", saveType: "Xlsx" });
if (args.item.text === 'Save As xls')
spreadsheet.save({ url: 'https://services.syncfusion.com/vue/production/api/spreadsheet/save', fileName: "Sample", saveType: "Xls" });
if (args.item.text === 'Save As csv')
spreadsheet.save({ url: 'https://services.syncfusion.com/vue/production/api/spreadsheet/save', fileName: "Sample", saveType: "Csv" });
if (args.item.text === 'Save As pdf')
spreadsheet.save({ url: 'https://services.syncfusion.com/vue/production/api/spreadsheet/save', fileName: "Sample", saveType: "Pdf" });
}
}
}
</script>
<style>
@import '../node_modules/@syncfusion/ej2-base/styles/material.css';
@import '../node_modules/@syncfusion/ej2-buttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-dropdowns/styles/material.css';
@import '../node_modules/@syncfusion/ej2-inputs/styles/material.css';
@import '../node_modules/@syncfusion/ej2-navigations/styles/material.css';
@import '../node_modules/@syncfusion/ej2-popups/styles/material.css';
@import '../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-grids/styles/material.css';
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
</style>
Server Configuration
Import and export are processed in server-side
using Spreadsheet server library. The following code snippets shows server configuration using WebAPI
service,
[Route("api/[controller]")]
public class SpreadsheetController : Controller
{
//To open excel file
[AcceptVerbs("Post")]
[HttpPost]
[EnableCors("AllowAllOrigins")]
[Route("Open")]
public IActionResult Open(IFormCollection openRequest)
{
OpenRequest open = new OpenRequest();
open.File = openRequest.Files[0];
return Content(Workbook.Open(open));
}
//To save as excel file
[AcceptVerbs("Post")]
[HttpPost]
[EnableCors("AllowAllOrigins")]
[Route("Save")]
public IActionResult Save([FromForm]SaveSettings saveSettings)
{
return Workbook.Save(saveSettings);
}
}
Server Dependencies
Open and save helper functions are shipped in the Syncfusion.EJ2.Spreadsheet package, which is available in Essential Studio and nuget.org
. Following list of dependencies required for Spreadsheet open and save operations.
- Syncfusion.EJ2
- Syncfusion.EJ2.Spreadsheet
- Syncfusion.Compression.Base
- Syncfusion.XlsIO.Base
And also refer this for more information.
Note
You can refer to our Vue Spreadsheet feature tour page for its groundbreaking feature representations. You can also explore our Vue Spreadsheet example to knows how to present and manipulate data.