ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

This content has been translated automatically.  Click here  to view the French version.
Help / WLanguage / WLanguage functions / Standard functions / XLS file functions
  • Overview
  • Method 1: Handling the XLS and XLSX files dynamically
  • Method 2: Reading the Excel files (method kept for backward compatibility)
  • WLanguage functions
WINDEV
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadIOS WidgetApple WatchMac CatalystUniversal Windows 10 App
Others
Stored procedures
Overview
WINDEV and WEBDEV propose two methods to handle Excel files:
  • Method 1: Handling the XLS and XLSX worksheets dynamically
    This method uses different types of variables as well as WLanguage functions. This method allows you to manage the Excel files (XLS or XLSX files) in read and write mode.
  • Method 2 (kept for backward compatibility): Using the WLanguage functions to manage the XLS functions.
    This method allows you to manage the XLS files in read mode.
Method 1: Handling the XLS and XLSX files dynamically
To handle the XLS and XLSX files, the WLanguage proposes:
  • different types of variables. These types of variables are used to handle an XLS document, an XLS row or column, an XLS cell.
  • several WLanguage functions that handle the different types of data.
To handle the XLS and XLSX files:
  1. Declare a variable of type xlsDocument. This type of variable is used to describe and modify an Excel document.
  2. If necessary, open the XLS file (function xlsOpen) and link it to the Variable of type xlsDocument.
  3. The different elements in the Excel document can be handled:
    • with the WLanguage functions.
    • or with properties of the type xlsDocument .
Example:
IDFicXLS is xlsDocument
UnFichier is string
UnFichier = fExeDir + "\Suivi d'affaires1.xls"
 
IDFicXLS = xlsOpen(UnFichier, xlsWrite)
IF ErrorOccurred = False THEN
Azz is int = xlsCurrentWorksheet(IDFicXLS)
Info("Feuille en cours dans le fichier Excel: " + Azz)
// Changement de feuille
IF xlsCurrentWorksheet(IDFicXLS, 2) THEN
Info("La feuille courante a été modifiée.")
// Lecture d'une cellule pour test
Info(xlsData(IDFicXLS, 11, 2))
ELSE
Error("Le fichier sélectionné ne comporte pas de seconde feuille!")
END
ELSE
Error("Attention! le fichier est déjà ouvert sur un poste de travail!")
END
Remarks:
  • If the Excel file is opened in read/write mode (xlsWrite constant), the file is opened and locked until it is closed. To save the modifications performed, use xlsSave.
  • The XLS functions do not require "Microsoft Excel" to be installed on the user computers.
  • The XLS files and the files specific to Office 2007 (.xlsx files) are supported.
Related Examples:
XLS Type Unit examples (WINDEV): XLS Type
[ + ] Handling the xlsDocument, xlsColumn, xlsRow and xlsCell variables.
The purpose of this example is to explain how these types of variables can be easily handled.
Method 2: Reading the Excel files (method kept for backward compatibility)
WINDEV and WEBDEV propose several functions allowing you to manage your ".XLS" files from your WINDEV applications and from your WEBDEV sites. These functions allow you to retrieve the data entered in Excel.
To handle an XLS document:
  1. Use the xlsOpen function. This function returns the identifier of the XLS file used.
  2. Use the XLS functions to retrieve the requested information about the Excel file and its data.
Remarks:
  • The XLS functions do not require "Microsoft Excel" to be installed on the user computers.
  • Only the XLS files are supported. The files specific to Office 2007 (.xlsx files) are not supported.
  • Two operating modes are available for the XLS functions:
    • Operating mode compatible with the earlier versions of WINDEV and WEBDEV: in this mode, only the first worksheet of the workbook can be accessed.
    • New multi-sheet operating mode: in this mode, you have the ability to select the worksheet where the operations are performed.
The function used to modify the mode for handling the XLS functions is named xlsOpen.
WLanguage functions
These functions are used to:
  • handle the ".XLS" files: opening and closing.
  • get information about your ".XLS" files: data of a cell, number of rows, number of columns, type of column, title of column (see the diagram).
  • find out the Excel version used.
  • identify the errors that occurred during the operations performed on an ".XLS" file.
For a list of functions that can be used with Excel files, see XLS functions.
Related Examples:
The Excel functions Unit examples (WINDEV): The Excel functions
[ + ] Using the functions for importing/exporting a WINDEV table to/from Excel and Word. This is automatically performed by the WLanguage functions.
The following topics are presented in this example:
1/ How to export the content of a table to Excel or Word
2/ How to import the content of an Excel file to a WINDEV table
A button is used to import the entire data from a Excel file into an application table.
A second button is used to export the table content to the source Excel file in order to apply the modifications.
The table can also be exported in table format into Word.
XLS Type Unit examples (WINDEV): XLS Type
[ + ] Handling the xlsDocument, xlsColumn, xlsRow and xlsCell variables.
The purpose of this example is to explain how these types of variables can be easily handled.
WD Controlling Excel Training (WINDEV): WD Controlling Excel
[ + ] This example explains how to control Excel via OLE Automation.
All the features included in Excel can be emulated.

This example requires Excel 97 or later.

Summary of the example supplied with WINDEV:
Controlling a spreadsheet as Excel may be useful to allow the user to keep his own habits from the software powered by WINDEV.
This process is very easy via the "CExcel" class supplied with WINDEV.
The main features of Excel can be directly called (chart, inserting objects, sorts, ...).
How do I control Excel via OLE Automation?
An OLE Automation contains methods and properties. This allows you to emulate it directly in WLanguage.
For example, to display the selected cell in bold:
MyOLEAutomationObject>>Selection>>Font>>Bold = True
WD Controlling Spreadsheets Training (WINDEV): WD Controlling Spreadsheets
[ + ] This example explains how to control OpenOffice Calc and Excel with a generic code.
It gives you the ability to create, open and handle the workbooks opened in OpenOfficeCalc and Excel from a WINDEV application.
You can position on a worksheet, select cells, read and modify their content, save the workbook and close it.
The use of an abstract class (CSpreadsheet) enables you to implement a unique code, regardless of the spreadsheet installed on the user computer
Minimum version required
  • Version 9
This page is also available for…
Comments
Click [Add] to post a comment

Last update: 06/23/2023

Send a report | Local help