Administrator Blog

Tuesday, 11 March 2025 01:57

Lock Cells, Rows, and Columns in Excel with JavaScript in React

When working with Excel, you may sometimes need to protect critical data while allowing users to edit other parts of the worksheet. This is especially important for scenarios where certain formulas, headers, or reference values must remain unchanged to ensure data integrity. By locking specific areas, you can prevent accidental modifications, maintain consistency, and control access to key information within the spreadsheet. In this article, you will learn how to lock cells, rows, and columns in Excel in React using JavaScript and the Spire.XLS for JavaScript library.

Lock Cells in Excel
Lock Rows in Excel
Lock Columns in Excel

Install Spire.XLS for JavaScript

To get started with locking cells, rows, and columns in Excel files within a React application, you can either download Spire.XLS for JavaScript from our website or install it via npm with the following command:

Package Manager

npm i spire.xls

After that, copy the "Spire.Xls.Base.js" and "Spire.Xls.Base.wasm" files to the public folder of your project. Additionally, include the required font files to ensure accurate and consistent text rendering.

For more details, refer to the documentation: How to Integrate Spire.XLS for JavaScript in a React Project

Lock Cells in Excel

Spire.XLS for JavaScript offers the Worksheet.Range.get().Style.Locked property, allowing you to protect critical data cells while enabling edits to the rest of the worksheet. The detailed steps are as follows.

Create a Workbook object using the wasmModule.Workbook.Create() method.
Load a sample Excel file using the Workbook.LoadFromFile() method.
Get the first worksheet using the Workbook.Worksheets.get() method.
Unlock all cells in the used range of the worksheet by setting the Worksheet.Range.Style.Locked property to "false".
Set text for specific cells using the Worksheet.Range.get().Text property and then lock them by setting the Worksheet.Range.get().Style.Locked property to "true".
Protect the worksheet with a password using the Worksheet.Protect() method.
Save the result file using the Workbook.SaveToFile() method.

JavaScript

import React, { useState, useEffect } from 'react';

function App() {

  // State to hold the loaded WASM module
  const [wasmModule, setWasmModule] = useState(null);

  // useEffect hook to load the WASM module when the component mounts
  useEffect(() => {
    const loadWasm = async () => {
      try {

        // Access the Module and spirexls from the global window object
        const { Module, spirexls } = window;

        // Set the wasmModule state when the runtime is initialized
        Module.onRuntimeInitialized = () => {
          setWasmModule(spirexls);
        };
      } catch (err) {

        // Log any errors that occur during loading
        console.error('Failed to load WASM module:', err);
      }
    };

    // Create a script element to load the WASM JavaScript file

    const script = document.createElement('script');
    script.src = `${process.env.PUBLIC_URL}/Spire.Xls.Base.js`;
    script.onload = loadWasm;

    // Append the script to the document body
    document.body.appendChild(script);

    // Cleanup function to remove the script when the component unmounts
    return () => {
      document.body.removeChild(script);
    };
  }, []); 

  // Function to lock specific cells in Excel
  const LockExcelCells = async () => {
    if (wasmModule) {
      // Load the ARIALUNI.TTF font file into the virtual file system (VFS)
      await wasmModule.FetchFileToVFS('ARIALUNI.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/`);
      
      // Load the input Excel file into the virtual file system (VFS)
      const inputFileName = 'Sample.xlsx';
      await wasmModule.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}/`);
      
      // Create a new workbook
      const workbook = wasmModule.Workbook.Create();
      // Load the Excel file from the virtual file system
      workbook.LoadFromFile({fileName: inputFileName});

      // Get the first worksheet
      let sheet = workbook.Worksheets.get(0);

      // Unlock all cells in the used range of the worksheet
      sheet.Range.Style.Locked = false;

      // Lock a specific cell in the worksheet
      sheet.Range.get("A1").Text = "Locked";
      sheet.Range.get("A1").Style.Locked = true;

      // Lock a specific cell range in the worksheet
      sheet.Range.get("C1:E3").Text = "Locked";
      sheet.Range.get("C1:E3").Style.Locked = true;

      // Protect the worksheet with a password
      sheet.Protect({password: "123", options: wasmModule.SheetProtectionType.All});

      let outputFileName = "LockCells.xlsx";
      // Save the resulting file
      workbook.SaveToFile({ fileName: outputFileName, version: wasmModule.ExcelVersion.Version2013 });
      
      // Read the saved file and convert it to a Blob object
      const modifiedFileArray = wasmModule.FS.readFile(outputFileName);
      const modifiedFile = new Blob([modifiedFileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
      
      // Create a URL for the Blob and initiate the download
      const url = URL.createObjectURL(modifiedFile);
      const a = document.createElement('a');
      a.href = url;
      a.download = outputFileName;
      document.body.appendChild(a);
      a.click(); 
      document.body.removeChild(a); 
      URL.revokeObjectURL(url); 

      // Clean up resources used by the workbooks
      workbook.Dispose();
    }
  };

  return (
    <div style={{ textAlign: 'center', height: '300px' }}>
      <h1>Lock Specific Cells in Excel Using JavaScript in React</h1>
      <button onClick={LockExcelCells} disabled={!wasmModule}>
        Lock
      </button>
    </div>
  );
}

export default App;

Run the code to launch the React app at localhost:3000. Once it's running, click on the "Lock" button to lock specific cells in the Excel file:

Run the code to launch the React app

Upon opening the output Excel sheet and attempting to edit the protected cells, a dialog box will appear, notifying you that the cell you're trying to change is on a protected sheet:

Lock Cells in Excel

Lock Rows in Excel

If you need to preserve row-based data, such as headers or summaries, you can lock entire rows using the Worksheet.Rows.get().Style.Locked property in Spire.XLS for JavaScript. The detailed steps are as follows.

Create a Workbook object using the wasmModule.Workbook.Create() method.
Load a sample Excel file using the Workbook.LoadFromFile() method.
Get the first worksheet using the Workbook.Worksheets.get() method.
Unlock all cells in the used range of the worksheet by setting the Worksheet.Range.Style.Locked property to "false".
Set text for a specific row using the Worksheet.Rows.get().Text property and then lock it by setting the Worksheet.Rows.get().Style.Locked property to "true".
Protect the worksheet with a password using the Worksheet.Protect() method.
Save the result file using the Workbook.SaveToFile() method.

JavaScript

import React, { useState, useEffect } from 'react';

function App() {

  // State to hold the loaded WASM module
  const [wasmModule, setWasmModule] = useState(null);

  // useEffect hook to load the WASM module when the component mounts
  useEffect(() => {
    const loadWasm = async () => {
      try {

        // Access the Module and spirexls from the global window object
        const { Module, spirexls } = window;

        // Set the wasmModule state when the runtime is initialized
        Module.onRuntimeInitialized = () => {
          setWasmModule(spirexls);
        };
      } catch (err) {

        // Log any errors that occur during loading
        console.error('Failed to load WASM module:', err);
      }
    };

    // Create a script element to load the WASM JavaScript file

    const script = document.createElement('script');
    script.src = `${process.env.PUBLIC_URL}/Spire.Xls.Base.js`;
    script.onload = loadWasm;

    // Append the script to the document body
    document.body.appendChild(script);

    // Cleanup function to remove the script when the component unmounts
    return () => {
      document.body.removeChild(script);
    };
  }, []); 

  // Function to lock specific rows in Excel
  const LockExcelRows = async () => {
    if (wasmModule) {
      // Load the ARIALUNI.TTF font file into the virtual file system (VFS)
      await wasmModule.FetchFileToVFS('ARIALUNI.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/`);
      
      // Load the input Excel file into the virtual file system (VFS)
      const inputFileName = 'Sample.xlsx';
      await wasmModule.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}/`);
      
      // Create a new workbook
      const workbook = wasmModule.Workbook.Create();
      // Load the Excel file from the virtual file system
      workbook.LoadFromFile({fileName: inputFileName});

      // Get the first worksheet
      let sheet = workbook.Worksheets.get(0);

      // Unlock all cells in the used range of the worksheet
      sheet.Range.Style.Locked = false;

      // Lock the third row in the worksheet
      sheet.Rows.get(2).Text = "Locked";
      sheet.Rows.get(2).Style.Locked = true;

      // Protect the worksheet with a password
      sheet.Protect({password: "123", options: wasmModule.SheetProtectionType.All});

      let outputFileName = "LockRows.xlsx";
      // Save the resulting file
      workbook.SaveToFile({ fileName: outputFileName, version: wasmModule.ExcelVersion.Version2013 });
      
      // Read the saved file and convert it to a Blob object
      const modifiedFileArray = wasmModule.FS.readFile(outputFileName);
      const modifiedFile = new Blob([modifiedFileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
      
      // Create a URL for the Blob and initiate the download
      const url = URL.createObjectURL(modifiedFile);
      const a = document.createElement('a');
      a.href = url;
      a.download = outputFileName;
      document.body.appendChild(a);
      a.click(); 
      document.body.removeChild(a); 
      URL.revokeObjectURL(url); 

      // Clean up resources used by the workbooks
      workbook.Dispose();
    }
  };

  return (
    <div style={{ textAlign: 'center', height: '300px' }}>
      <h1>Lock Specific Rows in Excel Using JavaScript in React</h1>
      <button onClick={LockExcelRows} disabled={!wasmModule}>
        Lock
      </button>
    </div>
  );
}

export default App;

Lock Rows in Excel

Lock Columns in Excel

To maintain the integrity of key vertical data, such as fixed identifiers or category labels, you can lock entire columns using the Worksheet.Columns.get().Style.Locked property in Spire.XLS for JavaScript. The detailed steps are as follows.

Create a Workbook object using the wasmModule.Workbook.Create() method.
Load a sample Excel file using the Workbook.LoadFromFile() method.
Get the first worksheet using the Workbook.Worksheets.get() method.
Unlock all cells in the used range of the worksheet by setting the Worksheet.Range.Style.Locked property to "false".
Set text for a specific column using the Worksheet.Columns.get().Text property and then lock it by setting the Worksheet.Columns.get().Style.Locked property to "true".
Protect the worksheet with a password using the Worksheet.Protect() method.
Save the result file using the Workbook.SaveToFile() method.

JavaScript

import React, { useState, useEffect } from 'react';

function App() {

  // State to hold the loaded WASM module
  const [wasmModule, setWasmModule] = useState(null);

  // useEffect hook to load the WASM module when the component mounts
  useEffect(() => {
    const loadWasm = async () => {
      try {

        // Access the Module and spirexls from the global window object
        const { Module, spirexls } = window;

        // Set the wasmModule state when the runtime is initialized
        Module.onRuntimeInitialized = () => {
          setWasmModule(spirexls);
        };
      } catch (err) {

        // Log any errors that occur during loading
        console.error('Failed to load WASM module:', err);
      }
    };

    // Create a script element to load the WASM JavaScript file

    const script = document.createElement('script');
    script.src = `${process.env.PUBLIC_URL}/Spire.Xls.Base.js`;
    script.onload = loadWasm;

    // Append the script to the document body
    document.body.appendChild(script);

    // Cleanup function to remove the script when the component unmounts
    return () => {
      document.body.removeChild(script);
    };
  }, []); 

  // Function to lock specific columns in Excel
  const LockExcelColumns = async () => {
    if (wasmModule) {
      // Load the ARIALUNI.TTF font file into the virtual file system (VFS)
      await wasmModule.FetchFileToVFS('ARIALUNI.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/`);
      
      // Load the input Excel file into the virtual file system (VFS)
      const inputFileName = 'Sample.xlsx';
      await wasmModule.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}/`);
      
      // Create a new workbook
      const workbook = wasmModule.Workbook.Create();
      // Load the Excel file from the virtual file system
      workbook.LoadFromFile({fileName: inputFileName});

      // Get the first worksheet
      let sheet = workbook.Worksheets.get(0);

      // Unlock all cells in the used range of the worksheet
      sheet.Range.Style.Locked = false;

      // Lock the fourth column in the worksheet
      sheet.Columns.get(3).Text = "Locked";
      sheet.Columns.get(3).Style.Locked = true;

      // Protect the worksheet with a password
      sheet.Protect({password: "123", options: wasmModule.SheetProtectionType.All});

      let outputFileName = "LockColumns.xlsx";
      // Save the resulting file
      workbook.SaveToFile({ fileName: outputFileName, version: wasmModule.ExcelVersion.Version2013 });
      
      // Read the saved file and convert it to a Blob object
      const modifiedFileArray = wasmModule.FS.readFile(outputFileName);
      const modifiedFile = new Blob([modifiedFileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
      
      // Create a URL for the Blob and initiate the download
      const url = URL.createObjectURL(modifiedFile);
      const a = document.createElement('a');
      a.href = url;
      a.download = outputFileName;
      document.body.appendChild(a);
      a.click(); 
      document.body.removeChild(a); 
      URL.revokeObjectURL(url); 

      // Clean up resources used by the workbooks
      workbook.Dispose();
    }
  };

  return (
    <div style={{ textAlign: 'center', height: '300px' }}>
      <h1>Lock Specific Columns in Excel Using JavaScript in React</h1>
      <button onClick={LockExcelColumns} disabled={!wasmModule}>
        Lock
      </button>
    </div>
  );
}

export default App;

Lock Columns in Excel

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

Published in Security

Tagged under

XLS React Security

Wednesday, 19 February 2025 07:20

Spire.Presentation for Java 10.2.2 enhances the conversion from PowerPoint to images

We are delighted to announce the release of Spire.Presentation for Java 10.2.2. This version enhances the conversion from PowerPoint documents to images. Moreover, some known issues are fixed successfully in this version, such as the issue that it threw "Value cannot be null" when saving a PowerPoint document. More details are listed below.

Here is a list of changes made in this release

Category	ID	Description
Bug	SPIREPPT-2669	Fixes the issue that the shadow effect of text was lost when converting PowerPoint to images.
Bug	SPIREPPT-2717	Optimizes the function of adding annotations for specific text.
Bug	SPIREPPT-2718	Fixes the issue that it threw "StringIndexOutOfBoundsException" when adding annotations for specific text.
Bug	SPIREPPT-2719	Fixes the issue that the effect of converting PowerPoint to images was incorrect.
Bug	SPIREPPT-2722	Fixes the issue that it threw "Value cannot be null" when saving a PowerPoint document.

Click the link below to download Spire.Presentation for Java 10.2.2:

https://www.e-iceblue.com/Download/presentation-for-java.html

Published in Spire.Presentation for Java

Wednesday, 19 February 2025 03:12

Spire.Doc 13.2.3 optimizes the time and resource consumption when converting Word to PDF

We're pleased to announce the release of Spire.Doc 13.2.3. This version optimizes the time and resource consumption when converting Word to PDF, and also adds new interfaces for reading and writing chart titles, data labels, axis, legends, data tables and other chart attributes. More details are listed below.

Here is a list of changes made in this release

Category	ID	Description
New feature	-	Adds new interfaces for reading and writing chart titles, chart data labels, chart axis, chart legends, chart data tables and other attributes. ChartTitle.Text property: Sets the chart title text. ChartDataLabel.ShowValue property: Sets whether the data label includes the value. ChartAxis.CategoryType property: Sets the type of the horizontal axis (automatic, text, or date). ChartLegend.Position property: Sets the position of the legend. ChartDataTable.Show property: Sets whether to display the data table.
New feature	-	Namespace changes: Spire.Doc.Formatting.RowFormat.TablePositioning->Spire.Doc.Formatting.TablePositioning Spire.Doc.Printing.PagesPreSheet->Spire.Doc.Printing.PagesPerSheet
New feature	-	Optimizes the time and resource consumption when converting Word to PDF, especially when working with large files or complex layouts.

Click the link to download Spire.Doc 13.2.3:

https://www.e-iceblue.com/Download/download-word-for-net-now.html

More information of Spire.Doc new release or hotfix:

https://www.e-iceblue.com/forum/spire-doc-new-release-or-hotfix-t4749.html

Published in Spire.Doc for .NET

Tuesday, 18 February 2025 08:58

Spire.XLS for Java 15.2.1 enhances conversions from Excel to images and PDF

We are excited to announce the release of the Spire.XLS for Java 15.2.1. The latest version enhances conversions from Excel to images and PDF. Besides, this update fixes the issue that the program threw a "NullPointerException" when loading an XLSX document. More details are listed below.

Here is a list of changes made in this release

Category	ID	Description
Bug	SPIREXLS-5575	Fixes the issue that the program threw a "NullPointerException" when loading an XLSX document.
Bug	SPIREXLS-5668	Fixes the issue that incorrect colors existed when converting Excel to images.
Bug	SPIREXLS-5685	Fixes the issue that incomplete content displayed when converting Excel to PDF.

Click the link to download Spire.XLS for Java 15.2.1:

https://www.e-iceblue.com/Download/xls-for-java.html

Published in Spire.XLS for Java

Tuesday, 09 September 2014 03:39

Marker Designer

Data

Name	Capital	Continent	Area	Population
Argentina	Buenos Aires	South America	2777815	32300003
Bolivia	La Paz	South America	1098575	7300000
Brazil	Brasilia	South America	8511196	150400000
Canada	Ottawa	North America	9976147	26500000
Chile	Santiago	South America	756943	13200000
Colombia	Bagota	South America	1138907	33000000
Cuba	Havana	North America	114524	10600000
Ecuador	Quito	South America	455502	10600000
El Salvador	San Salvador	North America	20865	5300000
Guyana	Georgetown	South America	214969	800000

Option

Excel Version:

downloads

Demo
Java
C# source

This demo shows you the usage of WorkbookDesigner.

import com.spire.data.table.DataTable;
import com.spire.xls.FileFormat;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

public class MarkerDesignerDemo {

    public void markerDesignerDemo(String filePath, String dataFilePath, String resultFilePath){
        Workbook data_book = new Workbook();
        data_book.loadFromFile(dataFilePath);
        DataTable table = data_book.getWorksheets().get(0).exportDataTable();

        Workbook workbook = new Workbook();
        workbook.loadFromFile(filePath);
        Worksheet sheet = workbook.getWorksheets().get(0);
        Worksheet sheet2 = workbook.getWorksheets().get(1);

        sheet.setName( "Result");
        sheet2.setName("DataSource");
        sheet2.insertDataTable(table,true,1,1);

        workbook.getMarkerDesigner().addParameter("Variable1", 1234.5678);
        workbook.getMarkerDesigner().addDataTable("Country", table);
        workbook.getMarkerDesigner().apply();

        sheet.getAllocatedRange().autoFitRows();
        sheet.getAllocatedRange().autoFitColumns();

        workbook.saveToFile(resultFilePath, FileFormat.Version2013);


    }
}

using System.Data;
using Spire.Xls;

namespace DemoOnlineCode
{
    class MarkerDesigner
    {

        public void demoMarkerDesigner(string filePath, string dataFilePath, string resultFilePath)
        {
            Workbook data_book = new Workbook();
            data_book.LoadFromFile(dataFilePath);
            DataTable table = data_book.Worksheets[0].ExportDataTable();

            Workbook workbook = new Workbook();
            workbook.LoadFromFile(filePath);
            Worksheet sheet = workbook.Worksheets[0];
            Worksheet sheet2 = workbook.Worksheets[1];

            sheet.Name = "Result";
            sheet2.Name = "DataSource";
            sheet2.InsertDataTable(table, true, 1, 1);

            workbook.MarkerDesigner.AddParameter("Variable1", 1234.5678);
            workbook.MarkerDesigner.AddDataTable("Country", table);
            workbook.MarkerDesigner.Apply();

            sheet.AllocatedRange.AutoFitRows();
            sheet.AllocatedRange.AutoFitColumns();
            workbook.SaveToFile(resultFilePath, ExcelVersion.Version2010);
        }
    }
}

No Matter How Big or Small Your Project is,

Any technical question related to our product, contact us at [email protected].

Any question related to the purchase of product, contact us at [email protected].

If you don't find the function you want, please request a free demo from us.

Published in Spire.XLS

Tuesday, 09 September 2014 03:38

Calculate Formulas

Mathematic Functions:

Calculate symbol :

Calculate Data:

Logic Function:

Calculate symbol :

Calculate Data:

Simple Expression:

Calculate symbol :

Calculate Data:

MID Functions:

Text :

Start Number:

Number Charts:

Option:

Excel Version:

downloads

Demo
Java
C# source

This demo shows you how to calculate formulas and export data to datatable with calculating formulas.

import com.spire.xls.*;

public class CalculateFormulaDemo {
    public void CalculateFormulas(String resultFile){
        Workbook workbook = new Workbook();
        Worksheet sheet = workbook.getWorksheets().get(0);
        Calculate(workbook, sheet);
        workbook.saveToFile(resultFile, ExcelVersion.Version2010);
    }

    public void Calculate(Workbook workbook, Worksheet worksheet){
        int currentRow = 1;
        String currentFormula = null;
        Object formulaResult = null;
        String value = null;

        // Set width respectively of Column A ,Column B,Column C
        worksheet.setColumnWidth(1,32);
        worksheet.setColumnWidth(2,16);
        worksheet.setColumnWidth(3,16);

        //Set the value of Cell A1
        worksheet.getRange().get(currentRow++, 1).setValue("Examples of formulas :");

        // Set the value of Cell A2
        worksheet.getRange().get(++currentRow, 1).setValue("Test data:");

        // Set the style of Cell A1
        CellRange range = worksheet.getRange().get("A1");
        range.getStyle().getFont().isBold(true);
        range.getStyle().setFillPattern(ExcelPatternType.Solid);
        range.getStyle().setKnownColor(ExcelColors.LightGreen1);
        range.getStyle().getBorders().getByBordersLineType(BordersLineType.EdgeBottom).setLineStyle(LineStyleType.Medium);

        // Additive operation of mutiple cells

        worksheet.getRange().get(currentRow, 2).setNumberValue(7.3);
        worksheet.getRange().get(currentRow, 3).setNumberValue(5);
        worksheet.getRange().get(currentRow, 4).setNumberValue(8.2);
        worksheet.getRange().get(currentRow, 5).setNumberValue(4);
        worksheet.getRange().get(currentRow, 6).setNumberValue(3);
        worksheet.getRange().get(currentRow, 7).setNumberValue(11.3);

        // Create arithmetic expression string about cells
        currentFormula = "=Sheet1!$B$3 + Sheet1!$C$3+Sheet1!$D$3+Sheet1!$E$3+Sheet1!$F$3+Sheet1!$G$3";

        //Caculate arithmetic expression  about cells
        formulaResult = workbook.calculateFormulaValue(currentFormula);
        value = formulaResult.toString();
        worksheet.getRange().get(currentRow,2).setValue(value);

        // Set the value and format of two head cell
        worksheet.getRange().get(currentRow,1).setValue("Formulas");
        worksheet.getRange().get(currentRow,2).setValue("Results");
        worksheet.getRange().get(currentRow,2).setHorizontalAlignment(HorizontalAlignType.Right);
        range = worksheet.getRange().get(currentRow,1,currentRow,2);
        range.getStyle().getFont().isBold(true);
        range.getStyle().setKnownColor(ExcelColors.LightGreen1);
        range.getStyle().setFillPattern(ExcelPatternType.Solid);
        range.getStyle().getBorders().getByBordersLineType(BordersLineType.EdgeBottom).setLineStyle(LineStyleType.Medium);

        // Expression caculation
        // Create arithmetic tables enclosed type string
        currentFormula = "=33*3/4-2+10";
        worksheet.getRange().get(++currentRow,1).setText(currentFormula);

        // Caculate arithmetic expression
        formulaResult = workbook.calculateFormulaValue(currentFormula);

        value = formulaResult.toString();

        worksheet.getRange().get(currentRow,2).setValue(value);

        //Absolute value function
        // Create abosolute value function string
        currentFormula = "=ABS(-1.21)";
        worksheet.getRange().get(++currentRow,1).setText(currentFormula);

        // Caculate abosulte value function
        formulaResult = workbook.calculateFormulaValue(currentFormula);
        value = formulaResult.toString();
        worksheet.getRange().get(currentRow,2).setValue(value);

        // Sum function
        // Create sum function string
        currentFormula = "=SUM(18,29)";
        worksheet.getRange().get(++currentRow,1).setText(currentFormula);

        // Caculate sum function
        formulaResult = workbook.calculateFormulaValue(currentFormula);
        value = formulaResult.toString();
        worksheet.getRange().get(currentRow,2).setValue(value);

        //NOT function
        // Create NOT function string
        currentFormula = "=NOT(true)";
        worksheet.getRange().get(++currentRow,1).setText(currentFormula);

        //Caculate NOT function
        formulaResult = workbook.calculateFormulaValue(currentFormula);
        value = formulaResult.toString();
        worksheet.getRange().get(currentRow,2).setValue(value);
        worksheet.getRange().get(currentRow,2).setHorizontalAlignment(HorizontalAlignType.Right);

        //String Manipulation function
        //Get the substring
        // Build substring function
        currentFormula = "=MID(\"world\",4,2)";
        worksheet.getRange().get(++currentRow,1).setText(currentFormula);

        //Caculate substring function
        formulaResult = workbook.calculateFormulaValue(currentFormula);
        value = formulaResult.toString();
        worksheet.getRange().get(currentRow,2).setValue(value);

        worksheet.getRange().get(currentRow,2).setHorizontalAlignment(HorizontalAlignType.Right);

        // Random function

        // Create random function string.
        currentFormula = "=RAND()";
        worksheet.getRange().get(++currentRow,1).setText(currentFormula);

        //Caculate random function
        formulaResult = workbook.calculateFormulaValue(currentFormula);
        value = formulaResult.toString();
        worksheet.getRange().get(currentRow,2).setValue(value);


    }
}

using Spire.Xls;

namespace DemoOnlineCode
{
    class CalculateFormulas
    {
        public void demoCalculateFormulas(string resultFile)
        {
            Workbook workbook = new Workbook();
            Worksheet sheet = workbook.Worksheets[0];
            Calculate(workbook, sheet);
            workbook.SaveToFile(resultFile, ExcelVersion.Version2010);
        }
        public void Calculate(Workbook workbook, Worksheet sheet)
        {
            int currentRow = 1;
            string currentFormula = string.Empty;
            object formulaResult = null;
            string value = string.Empty;

            // Set width respectively of Column A ,Column B,Column C
            sheet.SetColumnWidth(1, 32);
            sheet.SetColumnWidth(2, 16);
            sheet.SetColumnWidth(3, 16);

            //Set the value of Cell A1
            sheet.Range[currentRow++, 1].Value = "Examples of formulas :";

            // Set the value of Cell A2
            sheet.Range[++currentRow, 1].Value = "Test data:";

            // Set the style of Cell A1
            CellRange range = sheet.Range["A1"];
            range.Style.Font.IsBold = true;

            range.Style.FillPattern = ExcelPatternType.Solid;

            range.Style.KnownColor = ExcelColors.LightGreen1;

            range.Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Medium;

            // Additive operation of mutiple cells
            sheet.Range[currentRow, 2].NumberValue = 7.3;
            sheet.Range[currentRow, 3].NumberValue = 5;
            sheet.Range[currentRow, 4].NumberValue = 8.2;
            sheet.Range[currentRow, 5].NumberValue = 4;
            sheet.Range[currentRow, 6].NumberValue = 3;
            sheet.Range[currentRow, 7].NumberValue = 11.3;

            // Create arithmetic expression string about cells
            currentFormula = "=Sheet1!$B$3 + Sheet1!$C$3+Sheet1!$D$3+Sheet1!$E$3+Sheet1!$F$3+Sheet1!$G$3";

            //Caculate arithmetic expression  about cells
            formulaResult = workbook.CaculateFormulaValue(currentFormula);
            value = formulaResult.ToString();
            sheet.Range[currentRow, 2].Value = value;

            // Set the value and format of two head cell
            sheet.Range[++currentRow, 1].Value = "Formulas"; ;
            sheet.Range[currentRow, 2].Value = "Results";
            sheet.Range[currentRow, 2].HorizontalAlignment = HorizontalAlignType.Right;
            range = sheet.Range[currentRow, 1, currentRow, 2];
            range.Style.Font.IsBold = true;
            range.Style.KnownColor = ExcelColors.LightGreen1;
            range.Style.FillPattern = ExcelPatternType.Solid;
            range.Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Medium;

            // Expression caculation
            // Create arithmetic tables enclosed type string
            currentFormula = "=33*3/4-2+10";
            sheet.Range[++currentRow, 1].Text = currentFormula;

            // Caculate arithmetic expression
            formulaResult = workbook.CaculateFormulaValue(currentFormula);

            value = formulaResult.ToString();

            sheet.Range[currentRow, 2].Value = value;

            /// The mathematics function ///

            //Absolute value function

            // Create abosolute value function string
            currentFormula = "=ABS(-1.21)";
            sheet.Range[++currentRow, 1].Text = currentFormula;

            // Caculate abosulte value function
            formulaResult = workbook.CaculateFormulaValue(currentFormula);
            value = formulaResult.ToString();
            sheet.Range[currentRow, 2].Value = value;


            ///  Statistical function///
            // Sum function

            // Create sum function string
            currentFormula = "=SUM(18,29)";
            sheet.Range[++currentRow, 1].Text = currentFormula;

            // Caculate sum function
            formulaResult = workbook.CaculateFormulaValue(currentFormula);
            value = formulaResult.ToString();
            sheet.Range[currentRow, 2].Value = value;

            ///logic function///
            //NOT function
            // Create NOT function string
            currentFormula = "=NOT(true)";
            sheet.Range[currentRow, 1].Text = currentFormula;

            //Caculate NOT function
            formulaResult = workbook.CaculateFormulaValue(currentFormula);
            value = formulaResult.ToString();
            sheet.Range[currentRow, 2].Value = value;
            sheet.Range[currentRow, 2].HorizontalAlignment = HorizontalAlignType.Right;

            //String Manipulation function//
            //Get the substring
            // Build substring function
            currentFormula = "=MID(\"world\",4,2)";
            sheet.Range[++currentRow, 1].Text = currentFormula;

            //Caculate substring function
            formulaResult = workbook.CaculateFormulaValue(currentFormula);
            value = formulaResult.ToString();
            sheet.Range[currentRow, 2].Value = value;

            sheet.Range[currentRow, 2].HorizontalAlignment = HorizontalAlignType.Right;

            // Random function

            // Create random function string.
            currentFormula = "=RAND()";
            sheet.Range[++currentRow, 1].Text = currentFormula;

            //Caculate random function
            formulaResult = workbook.CaculateFormulaValue(currentFormula);
            value = formulaResult.ToString();
            sheet.Range[currentRow, 2].Value = value;

        }
    }
}

No Matter How Big or Small Your Project is,

Any technical question related to our product, contact us at [email protected].

Any question related to the purchase of product, contact us at [email protected].

If you don't find the function you want, please request a free demo from us.

Published in Spire.XLS

Tuesday, 09 September 2014 03:33

Create Pivot Table

Data

e-iceblue

Option

Average Column:			Sum Column:
Max Column:			Min Column:
Excel Version:

downloads

Demo
Java
C# source

This demo shows you how to create a privot table in excel.

import com.spire.xls.*;
import com.spire.xls.core.IPivotField;


public class CreatePivotTableDemo {
    public void createPivotTableExcel(String filePath, String resultFilePath){
        Workbook workbook = createPivotTable(filePath);
        workbook.saveToFile(resultFilePath);
    }

    public Workbook createPivotTable(String filePath){
        Workbook workbook = new Workbook();
        workbook.loadFromFile(filePath, ExcelVersion.Version2010);
        Worksheet worksheet = workbook.getWorksheets().get(0);
        worksheet.setName("Data Source");
        Worksheet worksheet1 = workbook.createEmptySheet();
        worksheet1.setName("Pivot Table");

        CellRange dataRange = worksheet.getRange().get("A1:G17");
        PivotCache cache = workbook.getPivotCaches().add(dataRange);
        PivotTable pt = worksheet1.getPivotTables().add("Pivot Table",worksheet.getRange().get("A1"),cache);

        IPivotField r1 = pt.getPivotFields().get("Vendor No");
        r1.setAxis(AxisTypes.Row);
        pt.getOptions().setRowHeaderCaption("Vendor No");

        IPivotField r2 = pt.getPivotFields().get("Name");
        r2.setAxis(AxisTypes.Row);

        pt.getDataFields().add(pt.getPivotFields().get("Area"), "Average of Area", SubtotalTypes.Average);
        pt.getDataFields().add(pt.getPivotFields().get("Sales"), "SUM of Sales", SubtotalTypes.Sum);
        pt.getDataFields().add(pt.getPivotFields().get("OnHand"), "Max of OnHand", SubtotalTypes.Max);
        pt.getDataFields().add(pt.getPivotFields().get("OnOrder"), "Min of OnOrder", SubtotalTypes.Min);
        pt.setBuiltInStyle(PivotBuiltInStyles.PivotStyleMedium12);
        pt.calculateData();
        worksheet1.getAllocatedRange().autoFitColumns();
        worksheet1.getAllocatedRange().autoFitRows();
        return workbook;
    }
}

using Spire.Xls;

namespace DemoOnlineCode
{
    class CreatePivotTableInExcel
    {
        public void demoCreatePivotTableInExcel(string filePath, string resultFilePath)
        {
            Workbook workbook = CreatePivotTable(filePath);
            workbook.SaveToFile(resultFilePath);
        }

        public Workbook CreatePivotTable(string filePath)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(filePath, ExcelVersion.Version2007);
            Worksheet sheet = workbook.Worksheets[0];
            sheet.Name = "Data Source";
            Worksheet sheet2 = workbook.CreateEmptySheet();
            sheet2.Name = "Pivot Table";

            CellRange dataRange = sheet.Range["A1:G17"];
            PivotCache cache = workbook.PivotCaches.Add(dataRange);
            PivotTable pt = sheet2.PivotTables.Add("Pivot Table", sheet.Range["A1"], cache);

            var r1 = pt.PivotFields["Vendor No"];
            r1.Axis = AxisTypes.Row;
            pt.Options.RowHeaderCaption = "Vendor No";

            var r2 = pt.PivotFields["Name"];
            r2.Axis = AxisTypes.Row;

            pt.DataFields.Add(pt.PivotFields["Area"], "Average of Area", SubtotalTypes.Average);
            pt.DataFields.Add(pt.PivotFields["Sales"], "SUM of Sales", SubtotalTypes.Sum);
            pt.DataFields.Add(pt.PivotFields["OnHand"], "Max of OnHand", SubtotalTypes.Max);
            pt.DataFields.Add(pt.PivotFields["OnOrder"], "Min of OnOrder", SubtotalTypes.Min);
            pt.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium12;
            pt.CalculateData();
            sheet2.AllocatedRange.AutoFitColumns();
            sheet2.AllocatedRange.AutoFitRows();
            return workbook;

        }
    }
}

No Matter How Big or Small Your Project is,

Any technical question related to our product, contact us at [email protected].

Any question related to the purchase of product, contact us at [email protected].

If you don't find the function you want, please request a free demo from us.

Published in Spire.XLS

Tuesday, 09 September 2014 03:28

Charts

Data

e-iceblue

Chart Option

Chart format:

Excel Version:

downloads

Demo
Java
C# source

This demo shows you how to create chart in an excel workbook.

import com.spire.xls.*;

public class ChartDemo {
    public void chartDemo(String excelFile, ExcelChartType chartType, String resultFileName){
        Workbook workbook = new Workbook();
		workbook.loadFromFile(excelFile);
        Worksheet worksheet = workbook.getWorksheets().get(0);
        
        setChart(worksheet,chartType);
        sheetStyle(workbook,worksheet);
        workbook.saveToFile(resultFileName+".xlsx",FileFormat.Version2013);

    }
    private void setChart(Worksheet sheet, ExcelChartType chartType){
        sheet.setName("Chart data");
        sheet.setGridLinesVisible(false);

        //Add a new  chart worsheet to workbook
        Chart chart = sheet.getCharts().add();
        chart.setChartType(chartType);

        //Set region of chart data
        chart.setDataRange(sheet.getCellRange("A1:C7"));
        chart.setSeriesDataFromRange(false);

        //Set position of chart
        chart.setLeftColumn(4);
        chart.setTopRow(2);
        chart.setRightColumn(12);
        chart.setBottomRow(22);

        //Chart title
        chart.setChartTitle("Sales market by country");
        chart.getChartTitleArea().isBold(true);
        chart.getChartTitleArea().setSize(12);

        chart.getPrimarySerieAxis().setTitle("Country");
        chart.getPrimarySerieAxis().getFont().isBold(true);
        chart.getPrimarySerieAxis().getTitleArea().isBold(true);

        chart.getPrimarySerieAxis().setTitle("Sales(in Dollars)");
        chart.getPrimarySerieAxis().hasMajorGridLines(false);
        chart.getPrimarySerieAxis().getTitleArea().setTextRotationAngle(90);
        chart.getPrimarySerieAxis().setMinValue(1000);
        chart.getPrimarySerieAxis().getTitleArea().isBold(true);

        chart.getPlotArea().getFill().setFillType(ShapeFillType.SolidColor);
        chart.getPlotArea().getFill().setForeKnownColor(ExcelColors.White);

        for (int i = 0; i < chart.getSeries().getCount(); i++){
            chart.getSeries().get(i).getFormat().getOptions().isVaryColor(true);
            chart.getSeries().get(i).getDataPoints().getDefaultDataPoint().getDataLabels().hasValue(true);
        }
        chart.getLegend().setPosition(LegendPositionType.Top);
    }

    public static void sheetStyle(Workbook workbook, Worksheet sheet){
        CellStyle oddStyle = workbook.getStyles().addStyle("oddStyle");
        oddStyle.getBorders().getByBordersLineType(BordersLineType.EdgeLeft).setLineStyle(LineStyleType.Thin);
        oddStyle.getBorders().getByBordersLineType(BordersLineType.EdgeTop).setLineStyle(LineStyleType.Thin);
        oddStyle.getBorders().getByBordersLineType(BordersLineType.EdgeBottom).setLineStyle(LineStyleType.Thin);
        oddStyle.setKnownColor(ExcelColors.LightGreen1);

        CellStyle evenStyle = workbook.getStyles().addStyle("evenStyle");
        evenStyle.getBorders().getByBordersLineType(BordersLineType.EdgeLeft).setLineStyle(LineStyleType.Thin);
        evenStyle.getBorders().getByBordersLineType(BordersLineType.EdgeRight).setLineStyle(LineStyleType.Thin);
        evenStyle.getBorders().getByBordersLineType(BordersLineType.EdgeTop).setLineStyle(LineStyleType.Thin);
        evenStyle.getBorders().getByBordersLineType(BordersLineType.EdgeBottom).setLineStyle(LineStyleType.Thin);
        evenStyle.setKnownColor(ExcelColors.LightTurquoise);

        for (int i = 0; i < sheet.getAllocatedRange().getRows().length; i++) {
            CellRange[] ranges = sheet.getAllocatedRange().getRows();
            if (ranges[i].getRow() != 0){
                if (ranges[i].getRow() % 2 == 0)
                {
                    ranges[i].setCellStyleName(evenStyle.getName());
                }
                else
                {
                    ranges[i].setCellStyleName(oddStyle.getName());
                }
            }
        }

        //Sets header style
        CellStyle styleHeader = workbook.getStyles().addStyle("headerStyle");
        styleHeader.getBorders().getByBordersLineType(BordersLineType.EdgeLeft).setLineStyle(LineStyleType.Thin);
        styleHeader.getBorders().getByBordersLineType(BordersLineType.EdgeRight).setLineStyle(LineStyleType.Thin);
        styleHeader.getBorders().getByBordersLineType(BordersLineType.EdgeTop).setLineStyle(LineStyleType.Thin);
        styleHeader.getBorders().getByBordersLineType(BordersLineType.EdgeBottom).setLineStyle(LineStyleType.Thin);
        styleHeader.setVerticalAlignment(VerticalAlignType.Center);
        styleHeader.setKnownColor(ExcelColors.Green);
        styleHeader.getFont().setKnownColor(ExcelColors.White);
        styleHeader.getFont().isBold(true);
        styleHeader.setHorizontalAlignment(HorizontalAlignType.Center);

        for (int i = 0; i < sheet.getRows()[0].getCount(); i++) {
            CellRange range = sheet.getRows()[0];
            range.setCellStyleName(styleHeader.getName());
        }

        sheet.getColumns()[sheet.getAllocatedRange().getLastColumn() -1].getStyle().setNumberFormat("\"$\"#,##0");
        sheet.getColumns()[sheet.getAllocatedRange().getLastColumn() -2].getStyle().setNumberFormat("\"$\"#,##0");
        sheet.getRows()[0].getStyle().setNumberFormat("General");

        sheet.getAllocatedRange().autoFitColumns();
        sheet.getAllocatedRange().autoFitRows();

        sheet.getRows()[0].setRowHeight(20);
    }
}

using System.Data;
using Spire.Xls;

namespace DemoOnlineCode
{
    class Charts
    {
        public void demoChart(DataTable table, ExcelChartType chartFormat, string resultFileName)
        {
            Workbook book = new Workbook();
            Worksheet sheet = book.Worksheets[0];
            sheet.InsertDataTable(table, true, 1, 1);
            SetChart(sheet, chartFormat);
            sheetStyle(book, sheet);
            book.SaveToFile(resultFileName, ExcelVersion.Version2010);

        }
        private void SetChart(Worksheet sheet, ExcelChartType chartFormat)
        {
            sheet.Name = "Chart data";
            sheet.GridLinesVisible = false;

            //Writes chart data
            //CreateChartData(sheet);
            //Add a new  chart worsheet to workbook
            Chart chart = sheet.Charts.Add();

            //Set region of chart data

            chart.DataRange = sheet.Range["A1:C7"];
            chart.SeriesDataFromRange = false;

            //Set position of chart
            chart.LeftColumn = 1;
            chart.TopRow = 8;
            chart.RightColumn = 11;
            chart.BottomRow = 29;
            chart.ChartType = chartFormat;


            //Chart title
            chart.ChartTitle = "Sales market by country";
            chart.ChartTitleArea.IsBold = true;
            chart.ChartTitleArea.Size = 12;

            chart.PrimaryCategoryAxis.Title = "Country";
            chart.PrimaryCategoryAxis.Font.IsBold = true;
            chart.PrimaryCategoryAxis.TitleArea.IsBold = true;

            chart.PrimaryValueAxis.Title = "Sales(in Dollars)";
            chart.PrimaryValueAxis.HasMajorGridLines = false;
            chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90;
            chart.PrimaryValueAxis.MinValue = 1000;
            chart.PrimaryValueAxis.TitleArea.IsBold = true;


            foreach (Spire.Xls.Charts.ChartSerie cs in chart.Series)
            {
                cs.Format.Options.IsVaryColor = true;
                cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = true;
            }

            chart.Legend.Position = LegendPositionType.Top;

        }
        public static void sheetStyle(Workbook book, Worksheet sheet)
        {
            CellStyle oddStyle = book.Styles.Add("oddStyle");
            oddStyle.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
            oddStyle.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
            oddStyle.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
            oddStyle.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
            oddStyle.KnownColor = ExcelColors.LightGreen1;

            CellStyle evenStyle = book.Styles.Add("evenStyle");
            evenStyle.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
            evenStyle.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
            evenStyle.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
            evenStyle.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
            evenStyle.KnownColor = ExcelColors.LightTurquoise;


            foreach (CellRange range in sheet.AllocatedRange.Rows)
            {
                if (range.Row % 2 == 0)
                    range.CellStyleName = evenStyle.Name;
                else
                    range.CellStyleName = oddStyle.Name;
            }

            //Sets header style
            CellStyle styleHeader = sheet.Rows[0].Style;
            styleHeader.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
            styleHeader.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
            styleHeader.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
            styleHeader.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
            styleHeader.VerticalAlignment = VerticalAlignType.Center;
            styleHeader.KnownColor = ExcelColors.Green;
            styleHeader.Font.KnownColor = ExcelColors.White;
            styleHeader.Font.IsBold = true;

            sheet.Columns[sheet.AllocatedRange.LastColumn - 1].Style.NumberFormat = "\"$\"#,##0";
            sheet.Columns[sheet.AllocatedRange.LastColumn - 2].Style.NumberFormat = "\"$\"#,##0";

            sheet.AllocatedRange.AutoFitColumns();
            sheet.AllocatedRange.AutoFitRows();

            sheet.Rows[0].RowHeight = 20;
        }
    }
}

No Matter How Big or Small Your Project is,

Any technical question related to our product, contact us at [email protected].

Any question related to the purchase of product, contact us at [email protected].

If you don't find the function you want, please request a free demo from us.

Published in Spire.XLS

Tuesday, 09 September 2014 02:17

Conversion

Upload

Maximum file size: 1 MB. Files accepted: xls, xlsx, xlsb, ods.

Click here to browse files.

fileerrors

Convert to

Source file:

filename

Target file type:

Demo
Java
C# source

This demo shows you how to convert a Excel document (xls/xlsx/xlsb/ods) to PDF, HTML, Image.

import com.spire.xls.FileFormat;
import com.spire.xls.Workbook;
import com.spire.xls.core.spreadsheet.HTMLOptions;

import javax.imageio.ImageIO;
import java.awt.*;
import java.awt.image.BufferedImage;
import java.io.File;
import java.io.IOException;


public class ConvertDemo {

   public void convertDemo(String filePath, String convertTo, String resultFileName) throws IOException {
       Workbook workbook = new Workbook();
       workbook.loadFromFile(filePath);
       ConvertFormat(workbook,convertTo,resultFileName);

   }
    private void ConvertFormat(Workbook workbook, String convertTo, String resultFileName) throws IOException {
        switch (convertTo){
            case "PDF":
                workbook.getConverterSetting().setSheetFitToPage(true);
                workbook.saveToFile(resultFileName + ".pdf", FileFormat.PDF);
                break;
            case "IMAGE":
                BufferedImage[] images = (BufferedImage[]) new Image[workbook.getWorksheets().size()];
                for (int i = 0; i < workbook.getWorksheets().size();i++){
                    images[i] = workbook.saveAsImage(i,300,300);
                }
                if (images != null && images.length > 0){
                    if (images.length == 1){
                        ImageIO.write(images[0],".PNG", new File(resultFileName+".png"));
                    }
                }else {
                    for (int j = 0; j < images.length;j++){
                        String fileName = String.format("image-{0}.png",j);
                        ImageIO.write(images[j],".PNG",new File(fileName));
                    }
                }
                    break;
            case "HTML":
                for (int i = 0; i < workbook.getWorksheets().size(); i++) {
                    HTMLOptions options = new HTMLOptions();
                    options.setImageEmbedded(true);
                    String htmlPath = String.format(resultFileName+"-{0}.html",i++);
                    workbook.getWorksheets().get(i).saveToHtml(htmlPath,options);
                }
                break;
            case "TIFF":
                workbook.saveToTiff(resultFileName+".tiff");
                break;
            case "XPS":
                workbook.saveToFile(resultFileName+".xps",FileFormat.XPS);
                break;
        }
    }
}

using System;
using Spire.Xls;
using System.Drawing;
using System.Drawing.Imaging;

namespace DemoOnlineCode
{
    class Convertors
    {
        public void demoConvert(String filePath, string format, string resultFileName)
        {
            Workbook book = new Workbook();
            book.LoadFromFile(filePath);
            ConvertFormat(book, format, resultFileName);
        }
        private void ConvertFormat(Workbook workbook, string format, string resultFileName)
        {
            switch (format)
            {
                case "PDF":
                    workbook.ConverterSetting.SheetFitToPage = true;
                    workbook.SaveToFile(resultFileName + ".pdf", Spire.Xls.FileFormat.PDF);
                    break;
                case "IMAGE":
                    Image[] images = new Image[workbook.Worksheets.Count];
                    for (int i = 0; i < workbook.Worksheets.Count; i++)
                    {
                        images[i] = workbook.SaveAsImage(i, 300, 300);
                    }
                    if (images != null && images.Length > 0)
                    {
                        if (images.Length == 1)
                        {
                            images[0].Save(resultFileName + ".png", ImageFormat.Png);
                        }
                        else
                        {
                            for (int i = 0; i < images.Length; i++)
                            {
                                String fileName = String.Format("{0}-Image-{1}.png", resultFileName, i);
                                images[i].Save(fileName, ImageFormat.Png);
                            }
                        }
                    }
                    break;

                case "HTML":
                    for (int i = 0; i < workbook.Worksheets.Count; i++)
                    {
                        Worksheet sheet = workbook.Worksheets[i];
                        string htmlPath = string.Format(resultFileName + "-{0}.html", i++);
                        sheet.SaveToHtml(htmlPath);
                    }
                    break;
                case "TIFF":
                    JoinTiffImages(ToImage(workbook), resultFileName + ".tiff", EncoderValue.CompressionLZW);
                    break;
                case "XPS":
                    workbook.SaveToFile(resultFileName+".xps",FileFormat.XPS);
                    break;
            }
        }
        private static Image[] ToImage(Workbook workbook)
        {
            //Get the worksheet count of workbook
            int workSheetNo = workbook.Worksheets.Count;

            //Create an array
            Image[] images = new Image[workSheetNo];

            //Save worksheet to image and add the array
            for (int i = 0; i < workSheetNo; i++)
            {
                Worksheet workSheet = workbook.Worksheets[i];
                string output = string.Format("result{0}.jpg", i + 1);
                workSheet.SaveToImage(output);
                Image image = Image.FromFile(output);
                images[i] = image;
            }
            return images;
        }

        private static ImageCodecInfo GetEncoderInfo(string mimeType)
        {
            ImageCodecInfo[] encoders = ImageCodecInfo.GetImageEncoders();
            for (int j = 0; j < encoders.Length; j++)
            {
                if (encoders[j].MimeType == mimeType)
                    return encoders[j];
            }
            throw new Exception(mimeType + " mime type not found in ImageCodecInfo");
        }

        private void JoinTiffImages(Image[] images, string outFile, EncoderValue compressEncoder)
        {
            //Use the save encoder
            Encoder enc = Encoder.SaveFlag;
            EncoderParameters ep = new EncoderParameters(2);
            ep.Param[0] = new EncoderParameter(enc, (long)EncoderValue.MultiFrame);
            ep.Param[1] = new EncoderParameter(Encoder.Compression, (long)compressEncoder);
            Image pages = images[0];
            int frame = 0;
            ImageCodecInfo info = GetEncoderInfo("image/tiff");
            foreach (Image img in images)
            {
                if (frame == 0)
                {
                    pages = img;
                    //save the first frame
                    pages.Save(outFile, info, ep);
                }

                else
                {
                    //save the intermediate frames
                    ep.Param[0] = new EncoderParameter(enc, (long)EncoderValue.FrameDimensionPage);

                    pages.SaveAdd(img, ep);
                }
                if (frame == images.Length - 1)
                {
                    //flush and close.
                    ep.Param[0] = new EncoderParameter(enc, (long)EncoderValue.Flush);
                    pages.SaveAdd(ep);
                }
                frame++;
            }
        }
    }
}

No Matter How Big or Small Your Project is,

Any technical question related to our product, contact us at [email protected].

Any question related to the purchase of product, contact us at [email protected].

If you don't find the function you want, please request a free demo from us.

Published in Spire.XLS

Thursday, 19 June 2014 01:50

MailMerge

Agreement Start Date:	You must select a date value.
Agreement End Date:	You must select a date value.
Agreement Extension Date:	You must select a date value.
Documentation Start Date:	You must select a date value.
Documentation End Date:	You must select a date value.
downloads

Demo
Java
C# source

This demo shows you how to merge some data into a Word template. Our Spire.Doc provides also the function NestedMailMerge with which you can merge the main-table and sub-table into a Word template to get a professional report. You can get a full demo from the article How to Use Mail Merge to Create Report

import com.spire.doc.Document;
import com.spire.doc.FileFormat;

import java.text.SimpleDateFormat;
import java.util.ArrayList;

public class MailMargeDemo {

     public void mailMerge(String docFile, String resultFilePath) throws Exception {
        Document doc = new Document();
        doc.loadFromFile(docFile);

        SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd");
        Calendar calendar=Calendar.getInstance();

        ArrayList dateList=new ArrayList<>();
        int []offset={-5,5,6,-2,2};
        for(int data:offset){
            calendar.setTime(new Date());
            calendar.add(Calendar.YEAR,data);
            dateList.add(format.format(calendar.getTime()));
        }
        String[] values =new String[dateList.size()];
        dateList.toArray(values);
        
        String []fields={
                "SubGrantPAStartDateValue",
                "SubGrantPAEndDateValue",
                "SubGrantPAExtensionDateValue",
                "SubGrantPSStartDateValue",
                "SubGrantPSEndDateValue"
        };
        doc.getMailMerge().execute(fields,values);
        doc.saveToFile(resultFilePath, FileFormat.Docx);
    }
}

using System;
using Spire.Doc;

namespace DemoOnlineCode
{
    class MailMerge
    {
        public void demoMailMerge(String docFile)
        {
            Document document = new Document(docFile, FileFormat.Auto);

            string[] values = {
                                  DateTime.Today.AddYears(-5).ToString("yyyy-MM-dd"),
                                  DateTime.Today.AddYears(5).ToString("yyyy-MM-dd"),
                                  DateTime.Today.AddYears(6).ToString("yyyy-MM-dd"),
                                  DateTime.Today.AddYears(-2).ToString("yyyy-MM-dd"),
                                  DateTime.Today.AddYears(2).ToString("yyyy-MM-dd")
                              };
            string[] fields = {
                                   "SubGrantPAStartDateValue",
                                   "SubGrantPAEndDateValue",
                                   "SubGrantPAExtensionDateValue",
                                   "SubGrantPSStartDateValue",
                                   "SubGrantPSEndDateValue"
                                 };
            document.MailMerge.Execute(fields, values);
            document.SaveToFile("demo.doc", FileFormat.Doc);

        }
    }
}

No Matter How Big or Small Your Project is,

Any technical question related to our product, contact us at [email protected].

Any question related to the purchase of product, contact us at [email protected].

If you don't find the function you want, please request a free demo from us.

Published in Spire.Doc Samples