Monday, 18 December 2023 02:25

Python : convertir Word en PDF

De nos jours, les documents numériques jouent un rôle important dans notre vie quotidienne, tant dans le cadre personnel que professionnel. L'un de ces formats courants est Microsoft Word, utilisé pour créer et éditer des documents textuels. Cependant, il peut arriver un moment où vous devrez convertir vos fichiers Word dans un format plus universellement accessible, tel que PDF. Les PDF offrent des avantages tels que la préservation du formatage, la garantie de la compatibilité et le maintien de l'intégrité des documents sur différents appareils et systèmes d'exploitation. Dans cet article, vous apprendrez comment convertir Word en PDF en Python en utilisant Spire.Doc for Python.

Installer Spire.Doc for Python

Ce scénario nécessite Spire.Doc for Python et plum-dispatch v1.7.4. Ils peuvent être facilement installés dans votre VS Code via la commande pip suivante.

pip install Spire.Doc

Si vous ne savez pas comment procéder à l'installation, veuillez vous référer à ce didacticiel : Comment installer Spire.Doc for Python dans VS Code

Convertir Doc ou Docx en PDF en Python

Spire.Doc for Python propose la méthode Document.SaveToFile(string fileName, FileFormat fileFormat) qui permet d'enregistrer Word au format PDF, XPS, HTML, RTF, etc. Si vous souhaitez simplement enregistrer vos documents Word au format PDF standard sans paramètres supplémentaires, suivez les étapes ci-dessous.

  • Créez un objet Document.
  • Chargez un exemple de document Word à l’aide de la méthode Document.LoadFromFile().
  • Enregistrez le document au format PDF à l'aide de la méthode Doucment.SaveToFile().
  • Python
from spire.doc import *
from spire.doc.common import *

# Create word document
document = Document()

# Load a doc or docx file
document.LoadFromFile("C:\\Users\\Administrator\\Desktop\\input.docx")

#Save the document to PDF
document.SaveToFile("output/ToPDF.pdf", FileFormat.PDF)
document.Close()

Python: Convert Word to PDF

Convertir Word en PDF protégé par mot de passe en Python

Pour convertir Word en PDF protégé par mot de passe, vous pouvez utiliser la méthode Document.SaveToFile(string fileName, ToPdfParameterList paramList), où le paramètre ToPdfParameterList vous permet de contrôler le processus de conversion d'un document Word au format PDF. Cela inclut des options telles que le cryptage du document lors de la conversion. Voici les étapes spécifiques pour accomplir cette tâche.

  • Créez un objet Document.
  • Chargez un exemple de document Word à l’aide de la méthode Document.LoadFromFile().
  • Créez un objet ToPdfParameterList, utilisé pour définir les options de conversion.
  • Spécifiez le mot de passe d'ouverture et le mot de passe d'autorisation, puis définissez les deux mots de passe pour le PDF généré à l'aide de la méthode ToPdfParameterList.PdfSecurity.Encrypt().
  • Enregistrez le document Word au format PDF avec un mot de passe à l'aide de la méthode Doucment.SaveToFile (string fileName, ToPdfParameterList paramList).
  • Python
from spire.doc import *
from spire.doc.common import *

# Create a Document object
document = Document()

# Load a Word file
document.LoadFromFile("C:\\Users\\Administrator\\Desktop\\input.docx")

# Create a ToPdfParameterList object
parameter = ToPdfParameterList()

# Specify open password and permission password
openPsd = "abc-123"
permissionPsd = "permission"

# Protect the PDF to be generated with open password and permission password
parameter.PdfSecurity.Encrypt(openPsd, permissionPsd, PdfPermissionsFlags.Default, PdfEncryptionKeySize.Key128Bit)

# Save the Word document to PDF
document.SaveToFile("output/ToPdfWithPassword.pdf", parameter)
document.Close()

Python: Convert Word to PDF

Convertir Word en PDF avec des signets en Python

L'ajout de signets à un document peut améliorer sa lisibilité. Lors de la création d'un PDF à partir de Word, vous souhaiterez peut-être conserver les signets existants ou en créer de nouveaux en fonction des titres. Voici les étapes pour convertir Word en PDF tout en conservant les signets.

  • Créez un objet Document.
  • Chargez un fichier Word à l'aide de la méthode Document.LoadFromFile().
  • Créez un objet ToPdfParameterList, utilisé pour définir les options de conversion.
  • Créez des signets au format PDF à partir des titres dans Word en définissant ToPdfParameterList.CreateWordBookmarksUsingHeadings sur true.
  • Enregistrez le document au format PDF avec des signets à l'aide de la méthode Doucment.SaveToFile(string fileName, ToPdfParameterList paramList).
  • Python
from spire.doc import *
from spire.doc.common import *

# Create a Document object
document = Document()

# Load a Word file
document.LoadFromFile("C:\\Users\\Administrator\\Desktop\\input.docx")

# Create a ToPdfParameterList object
parames = ToPdfParameterList()

# Create bookmarks from Word headings
parames.CreateWordBookmarksUsingHeadings = True

# Create bookmarks in PDF from existing bookmarks in Word
# parames.CreateWordBookmarks = True

# Save the document to PDF
document.SaveToFile("output/ToPdfWithBookmarks.pdf", FileFormat.PDF)
document.Close()

Python: Convert Word to PDF

Convertir Word en PDF avec des polices intégrées dans Python

Pour garantir une apparence cohérente d'un document PDF sur n'importe quel appareil, vous devrez probablement intégrer des polices dans le document PDF généré. Voici les étapes pour intégrer les polices utilisées dans un document Word dans le PDF résultant.

  • Créez un objet Document.
  • Chargez un exemple de fichier Word à l’aide de la méthode Document.LoadFromFile().
  • Créez un objet ToPdfParameterList, utilisé pour définir les options de conversion.
  • Incorporez les polices dans le PDF généré via la propriété ToPdfParameterList.IsEmbeddedAllFonts.
  • Enregistrez le document au format PDF à l'aide de la méthode Doucment.SaveToFile(string fileName, ToPdfParameterList paramList).
  • Python
from spire.doc import *
from spire.doc.common import *

# Create a Document object
document = Document()

# Load a Word file
document.LoadFromFile("C:\\Users\\Administrator\\Desktop\\input.docx")

# Create a ToPdfParameterList object
parameter = ToPdfParameterList()

# Embed fonts in PDF
parameter.IsEmbeddedAllFonts = True

# Save the Word document to PDF
document.SaveToFile("output/EmbedFonts.pdf", parameter)
document.Close()

Python: Convert Word to PDF

Définir la qualité de l'image lors de la conversion de Word en PDF en Python

Lors de la conversion d'un document Word en PDF, il est important de prendre en compte la taille du fichier résultant, surtout s'il contient de nombreuses images de haute qualité. Vous avez la possibilité de compresser la qualité de l'image pendant le processus de conversion. Pour faire ça, suit les étapes suivantes.

  • Créez un objet Document.
  • Chargez un exemple de fichier Word à l’aide de la méthode Document.LoadFromFile().
  • Définissez la qualité de l’image via la propriété Document.JPEGQuality.
  • Enregistrez le document au format PDF à l'aide de la méthode Doucment.SaveToFile().
  • Python
from spire.doc import *
from spire.doc.common import *

# Create a Document object
document = Document()

# Load a Word file
document.LoadFromFile("C:\\Users\\Administrator\\Desktop\\input.docx")

# Compress image to 40% of its original quality
document.JPEGQuality = 40

# Preserve original image quality
# document.JPEGQuality = 100

# Save the Word document to PDF
document.SaveToFile("output/SetImageQuality.pdf", FileFormat.PDF)
document.Close()

Demander une licence temporaire

Si vous souhaitez supprimer le message d'évaluation des documents générés ou vous débarrasser des limitations fonctionnelles, veuillez demander une licence d'essai de 30 jours pour toi.

Voir également

Wednesday, 06 December 2023 03:15

Java: Create, Read, or Update Excel Documents

Install with Maven

<dependency>
    <groupId>e-iceblue</groupId>
       <artifactId>spire.xls</artifactId>
    <version>14.1.3</version>
</dependency>
    

Related Links

The Excel spreadsheet is a popular file format that allows users to arrange, analyze, and display data in tables. The ability to programmatically interact with Excel files is valuable for automating and integrating its features into software. This is especially beneficial when dealing with extensive datasets, complex calculations, or dynamically generating/updating data. In this article, you will learn how to create, read, or update Excel document in Java using Spire.XLS for Java.

Install Spire.XLS for Java

First, you're required to add the Spire.Xls.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in your application by adding the following code to your project's pom.xml file.

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <name>e-iceblue</name>
        <url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls</artifactId>
        <version>14.3.2</version>
    </dependency>
</dependencies>
    

Create an Excel Document in Java

Spire.XLS for Java offers a variety of classes and interfaces that you can use to create and edit Excel documents. Here is a list of important classes, properties and methods involved in this article.

Member Description
Workbook class Represents an Excel workbook model.
Workbook.getWorksheets().add() method Adds a worksheet to workbook.
Workbook.saveToFile() method Saves the workbook to an Excel document.
Worksheet class Represents a worksheet in a workbook.
Worksheet.getRange() method Gets a specific cell or cell range from worksheet.
Worksheet.insertArray() method Imports data from an array to worksheet.
CellRange class Represents a cell or cell range in worksheet.
CellRange.setValue() method Sets the value of a cell.
CellRange.getValue() method Gets the value of a cell.

The following are the steps to create an Excel document from scratch using Spire.XLS for Java.

  • Create a Workbook object.
  • Add a worksheet using Workbook.getWorksheets().add() method.
  • Write data to a specific cell using Worksheet.getRange.setValue() method.
  • Import data from an array to the worksheet using Worksheet.insertArray() method.
  • Save the workbook to an Excel document using Workbook.saveToFile() method.
  • Java
import com.spire.xls.*;
    
    public class CreateSpreadsheet {
    
        public static void main(String[] args) {
    
            //Create a Workbook object
            Workbook wb = new Workbook();
    
            //Remove default worksheets
            wb.getWorksheets().clear();
    
            //Add a worksheet and name it "Employee"
            Worksheet sheet = wb.getWorksheets().add("Employee");
    
            //Merge the cells between A1 and G1
            sheet.getRange().get("A1:G1").merge();
    
            //Write data to A1 and apply formatting to it
            sheet.getRange().get("A1").setValue("Basic Information of Employees of Huanyu Automobile Company");
            sheet.getRange().get("A1").setHorizontalAlignment(HorizontalAlignType.Center);
            sheet.getRange().get("A1").setVerticalAlignment(VerticalAlignType.Center);
            sheet.getRange().get("A1").getStyle().getFont().isBold(true);
            sheet.getRange().get("A1").getStyle().getFont().setSize(13);
    
            //Set row height of the first row
            sheet.setRowHeight(1,30);
    
            //Create a two-dimensional array
            String[][] twoDimensionalArray = new String[][]{
                    {"Name", "Gender", "Birth Date", "Educational Background", "Contact Number", "Position", "ID"},
                    {"Allen", "Male", "1990-02-10", "Bachelor", "24756854", "Mechanic", "0021"},
                    {"Patrick", "Male", "1985-06-08", "Master", "59863247", "Mechanic", "0022"},
                    {"Jenna", "Female", "1989-11-25", "Bachelor", "79540352", "Sales", "0023"},
                    {"Tommy", "Male", "1988-04-16", "Master", "52014060", "Mechanic", "0024"},
                    {"Christina", "Female", "1998-01-21", "Bachelor", "35401489", "HR", "0025"}
            };
    
            //Import data from DataTable to worksheet
            sheet.insertArray(twoDimensionalArray,2,1);
    
            //Set row height of a range
            sheet.getRange().get("A2:G7").setRowHeight(15);
    
            //Set column width
            sheet.setColumnWidth(2,15);
            sheet.setColumnWidth(3,21);
            sheet.setColumnWidth(4,15);
    
            //Set border style of a range
            sheet.getRange().get("A2:G7").borderAround(LineStyleType.Medium);
            sheet.getRange().get("A2:G7").borderInside(LineStyleType.Thin);
            sheet.getRange().get("A2:G2").borderAround(LineStyleType.Medium);
            sheet.getRange().get("A2:G7").getBorders().setKnownColor(ExcelColors.Black);
    
            //Save to a .xlsx file
            wb.saveToFile("output/NewSpreadsheet.xlsx", FileFormat.Version2016);
        }
    }

Java: Create, Read, or Update Excel Documents

Read Data of a Worksheet in Java

The CellRange.getValue() method returns number value or text value of a cell as a string. To get data of a whole worksheet or a cell range, loop through the cells within it. The following are the steps to get data of a worksheet using Spire.XLS for Java.

  • Create a Workbook object.
  • Load an Excel document using Workbook.loadFromFile() method.
  • Get a specific worksheet using Workbook.getWorksheets().get(index) method.
  • Get the cell range containing data using Worksheet.getAllocatedRange() method.
  • Iterate through the rows and columns to get cells within the range, and return the value of each cell using CellRange.getValue() method.
  • Java
import com.spire.xls.CellRange;
    import com.spire.xls.Workbook;
    import com.spire.xls.Worksheet;
    
    public class ReadData {
    
        public static void main(String[] args) {
    
            //Create a Workbook object
            Workbook wb = new Workbook();
    
            //Load an existing Excel file
            wb.loadFromFile("C:/Users/Administrator/Desktop/NewSpreadsheet.xlsx");
    
            //Get the first worksheet
            Worksheet sheet = wb.getWorksheets().get(0);
    
            //Get the cell range containing data
            CellRange locatedRange = sheet.getAllocatedRange();
    
            //Iterate through the rows
            for (int i = 0; i < locatedRange.getRows().length; i++) {
    
                //Iterate through the columns
                for (int j = 0; j < locatedRange.getColumnCount(); j++) {
    
                    //Get data of a specific cell
                    System.out.print(locatedRange.get(i + 1, j + 1).getValue() + "  ");
                }
                System.out.println();
            }
        }
    }

Java: Create, Read, or Update Excel Documents

Update an Excel Document in Java

To change the value of a certain cell, just re-assign a value to it using Worksheet.getRange().setValue() method. The following are the detailed steps.

  • Create a Workbook object.
  • Load an Excel document using Workbook.LoadFromFile() method.
  • Get a specific worksheet through Workbook.Worksheets[index] property.
  • Change the value of a particular cell though Worksheet.Range.Value property.
  • Save the workbook to an Excel file using Workbook.SaveToFile() method.
  • Java
import com.spire.xls.ExcelVersion;
    import com.spire.xls.Workbook;
    import com.spire.xls.Worksheet;
    
    public class UpdateExcel {
    
        public static void main(String[] args) {
    
            //Create a Workbook object
            Workbook wb = new Workbook();
    
            //Load an existing Excel file
            wb.loadFromFile("C:/Users/Administrator/Desktop/NewSpreadsheet.xlsx");
    
            //Get the first worksheet
            Worksheet sheet = wb.getWorksheets().get(0);
    
            //Change the value of a specific cell
            sheet.getRange().get("A1").setValue("Updated Value");
    
            //Save to file
            wb.saveToFile("output/Updated.xlsx", ExcelVersion.Version2016);
        }
    }

Java: Create, Read, or Update Excel Documents

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.

See Also

Wednesday, 06 December 2023 03:14

Java: crie, leia ou atualize documentos Excel

Instalar com Maven

<dependency>
    <groupId>e-iceblue</groupId>
        <artifactId>spire.xls</artifactId>
    <version>14.1.3</version>
</dependency>
    

Links Relacionados

A planilha do Excel é um formato de arquivo popular que permite aos usuários organizar, analisar e exibir dados em tabelas. A capacidade de interagir programaticamente com arquivos do Excel é valiosa para automatizar e integrar seus recursos ao software. Isso é especialmente benéfico ao lidar com conjuntos de dados extensos, cálculos complexos ou geração/atualização dinâmica de dados. Neste artigo você aprenderá como crie, leia ou atualize documentos Excel em Java usando Spire.XLS for Java.

Instale Spire.XLS for Java

Primeiro, é necessário adicionar o arquivo Spire.Xls.jar como uma dependência em seu programa Java. O arquivo JAR pode ser baixado neste link. Se você usa Maven, pode importar facilmente o arquivo JAR em seu aplicativo adicionando o código a seguir ao arquivo pom.xml do seu projeto.

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <name>e-iceblue</name>
        <url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls</artifactId>
        <version>14.3.2</version>
    </dependency>
</dependencies>
    

Crie um documento Excel em Java

Spire.XLS for Java oferece uma variedade de classes e interfaces que você pode usar para criar e editar documentos Excel. Aqui está uma lista de classes, propriedades e métodos importantes envolvidos neste artigo.

Membro Descrição
Aula de apostila Representa um modelo de pasta de trabalho do Excel.
Método Workbook.getWorksheets().add() Adiciona uma planilha à pasta de trabalho.
Método Workbook.saveToFile() Salva a pasta de trabalho em um documento Excel.
Aula de planilha Representa uma planilha em uma pasta de trabalho.
Método Worksheet.getRange() Obtém uma célula ou intervalo de células específico da planilha.
Método Worksheet.insertArray() Importa dados de uma matriz para uma planilha.
Classe CellRange Representa uma célula ou intervalo de células na planilha.
Método CellRange.setValue() Define o valor de uma célula.
Método CellRange.getValue() Obtém o valor de uma célula.

A seguir estão as etapas para criar um documento Excel do zero usando Spire.XLS for Java.

  • Crie um objeto Pasta de trabalho.
  • Adicione uma planilha usando o método Workbook.getWorksheets().add().
  • Grave dados em uma célula específica usando o método Worksheet.getRange.setValue().
  • Importe dados de um array para a planilha usando o método Worksheet.insertArray().
  • Salve a pasta de trabalho em um documento Excel usando o método Workbook.saveToFile().
  • Java
import com.spire.xls.*;
    
    public class CreateSpreadsheet {
    
        public static void main(String[] args) {
    
            //Create a Workbook object
            Workbook wb = new Workbook();
    
            //Remove default worksheets
            wb.getWorksheets().clear();
    
            //Add a worksheet and name it "Employee"
            Worksheet sheet = wb.getWorksheets().add("Employee");
    
            //Merge the cells between A1 and G1
            sheet.getRange().get("A1:G1").merge();
    
            //Write data to A1 and apply formatting to it
            sheet.getRange().get("A1").setValue("Basic Information of Employees of Huanyu Automobile Company");
            sheet.getRange().get("A1").setHorizontalAlignment(HorizontalAlignType.Center);
            sheet.getRange().get("A1").setVerticalAlignment(VerticalAlignType.Center);
            sheet.getRange().get("A1").getStyle().getFont().isBold(true);
            sheet.getRange().get("A1").getStyle().getFont().setSize(13);
    
            //Set row height of the first row
            sheet.setRowHeight(1,30);
    
            //Create a two-dimensional array
            String[][] twoDimensionalArray = new String[][]{
                    {"Name", "Gender", "Birth Date", "Educational Background", "Contact Number", "Position", "ID"},
                    {"Allen", "Male", "1990-02-10", "Bachelor", "24756854", "Mechanic", "0021"},
                    {"Patrick", "Male", "1985-06-08", "Master", "59863247", "Mechanic", "0022"},
                    {"Jenna", "Female", "1989-11-25", "Bachelor", "79540352", "Sales", "0023"},
                    {"Tommy", "Male", "1988-04-16", "Master", "52014060", "Mechanic", "0024"},
                    {"Christina", "Female", "1998-01-21", "Bachelor", "35401489", "HR", "0025"}
            };
    
            //Import data from DataTable to worksheet
            sheet.insertArray(twoDimensionalArray,2,1);
    
            //Set row height of a range
            sheet.getRange().get("A2:G7").setRowHeight(15);
    
            //Set column width
            sheet.setColumnWidth(2,15);
            sheet.setColumnWidth(3,21);
            sheet.setColumnWidth(4,15);
    
            //Set border style of a range
            sheet.getRange().get("A2:G7").borderAround(LineStyleType.Medium);
            sheet.getRange().get("A2:G7").borderInside(LineStyleType.Thin);
            sheet.getRange().get("A2:G2").borderAround(LineStyleType.Medium);
            sheet.getRange().get("A2:G7").getBorders().setKnownColor(ExcelColors.Black);
    
            //Save to a .xlsx file
            wb.saveToFile("output/NewSpreadsheet.xlsx", FileFormat.Version2016);
        }
    }

Java: Create, Read, or Update Excel Documents

Ler dados de uma planilha em Java

O método CellRange.getValue() retorna o valor numérico ou o valor do texto de uma célula como uma string. Para obter dados de uma planilha inteira ou de um intervalo de células, percorra as células dentro dela. A seguir estão as etapas para obter dados de uma planilha usando Spire.XLS for Java.

  • Crie um objeto Pasta de trabalho.
  • Carregue um documento Excel usando o método Workbook.loadFromFile().
  • Obtenha uma planilha específica usando o método Workbook.getWorksheets().get(index).
  • Obtenha o intervalo de células contendo dados usando o método Worksheet.getAllocatedRange().
  • Itere pelas linhas e colunas para obter células dentro do intervalo e retorne o valor de cada célula usando o método CellRange.getValue().
  • Java
import com.spire.xls.CellRange;
    import com.spire.xls.Workbook;
    import com.spire.xls.Worksheet;
    
    public class ReadData {
    
        public static void main(String[] args) {
    
            //Create a Workbook object
            Workbook wb = new Workbook();
    
            //Load an existing Excel file
            wb.loadFromFile("C:/Users/Administrator/Desktop/NewSpreadsheet.xlsx");
    
            //Get the first worksheet
            Worksheet sheet = wb.getWorksheets().get(0);
    
            //Get the cell range containing data
            CellRange locatedRange = sheet.getAllocatedRange();
    
            //Iterate through the rows
            for (int i = 0; i < locatedRange.getRows().length; i++) {
    
                //Iterate through the columns
                for (int j = 0; j < locatedRange.getColumnCount(); j++) {
    
                    //Get data of a specific cell
                    System.out.print(locatedRange.get(i + 1, j + 1).getValue() + "  ");
                }
                System.out.println();
            }
        }
    }

Java: Create, Read, or Update Excel Documents

Atualizar um documento Excel em Java

Para alterar o valor de uma determinada célula, basta reatribuir um valor a ela usando o método Worksheet.getRange().setValue(). A seguir estão as etapas detalhadas.

  • Crie um objeto Pasta de trabalho.
  • Carregue um documento Excel usando o método Workbook.LoadFromFile().
  • Obtenha uma planilha específica por meio da propriedade Workbook.Worksheets[index].
  • Altere o valor de uma célula específica por meio da propriedade Worksheet.Range.Value.
  • Salve a pasta de trabalho em um arquivo Excel usando o método Workbook.SaveToFile().
  • Java
import com.spire.xls.ExcelVersion;
    import com.spire.xls.Workbook;
    import com.spire.xls.Worksheet;
    
    public class UpdateExcel {
    
        public static void main(String[] args) {
    
            //Create a Workbook object
            Workbook wb = new Workbook();
    
            //Load an existing Excel file
            wb.loadFromFile("C:/Users/Administrator/Desktop/NewSpreadsheet.xlsx");
    
            //Get the first worksheet
            Worksheet sheet = wb.getWorksheets().get(0);
    
            //Change the value of a specific cell
            sheet.getRange().get("A1").setValue("Updated Value");
    
            //Save to file
            wb.saveToFile("output/Updated.xlsx", ExcelVersion.Version2016);
        }
    }

Java: Create, Read, or Update Excel Documents

Solicite uma licença temporária

Se desejar remover a mensagem de avaliação dos documentos gerados ou se livrar das limitações de função, por favor solicite uma licença de teste de 30 dias para você mesmo.

Veja também

Установка с помощью Maven

<dependency>
    <groupId>e-iceblue</groupId>
    <artifactId>spire.xls</artifactId>
    <version>14.1.3</version>
    </dependency>
    

Ссылки по теме

Электронная таблица Excel — это популярный формат файлов, который позволяет пользователям упорядочивать, анализировать и отображать данные в таблицах. Возможность программного взаимодействия с файлами Excel полезна для автоматизации и интеграции его функций в программное обеспечение. Это особенно полезно при работе с обширными наборами данных, сложными вычислениями или динамическим созданием/обновлением данных. В этой статье вы узнаете, как создавайте, читайте или обновляйте документ Excel на Java помощью Spire.XLS for Java.

Установите Spire.XLS for Java

Во-первых, вам необходимо добавить файл Spire.Xls.jar в качестве зависимости в вашу программу Java. JAR-файл можно скачать по этой ссылке. Если вы используете Maven, вы можете легко импортировать файл JAR в свое приложение, добавив следующий код в файл pom.xml вашего проекта.

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <name>e-iceblue</name>
        <url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls</artifactId>
        <version>14.3.2</version>
    </dependency>
</dependencies>
    

Создать документ Excel на Java

Spire.XLS for Java предлагает множество классов и интерфейсов, которые можно использовать для создания и редактирования документов Excel. Вот список важных классов, свойств и методов, рассматриваемых в этой статье.

Член Описание
Класс рабочей книги Представляет модель книги Excel.
Метод Workbook.getWorksheets().add() Добавляет лист в книгу.
Метод Workbook.saveToFile() Сохраняет книгу в документ Excel.
Класс рабочего листа Представляет лист в книге.
Метод Worksheet.getRange() Получает определенную ячейку или диапазон ячеек из листа.
Метод Worksheet.insertArray() Импортирует данные из массива на лист.
Класс CellRange Представляет ячейку или диапазон ячеек на листе.
Метод CellRange.setValue() Устанавливает значение ячейки.
Метод CellRange.getValue() Получает значение ячейки.

Ниже приведены шаги по созданию документа Excel с нуля с помощью Spire.XLS for Java.

  • Создайте объект книги.
  • Добавьте рабочий лист, используя метод Workbook.getWorksheets().add().
  • Запишите данные в определенную ячейку с помощью метода Worksheet.getRange.setValue().
  • Импортируйте данные из массива на лист с помощью метода Worksheet.insertArray().
  • Сохраните книгу в документ Excel, используя метод Workbook.saveToFile().
  • Java
import com.spire.xls.*;
    
    public class CreateSpreadsheet {
    
        public static void main(String[] args) {
    
            //Create a Workbook object
            Workbook wb = new Workbook();
    
            //Remove default worksheets
            wb.getWorksheets().clear();
    
            //Add a worksheet and name it "Employee"
            Worksheet sheet = wb.getWorksheets().add("Employee");
    
            //Merge the cells between A1 and G1
            sheet.getRange().get("A1:G1").merge();
    
            //Write data to A1 and apply formatting to it
            sheet.getRange().get("A1").setValue("Basic Information of Employees of Huanyu Automobile Company");
            sheet.getRange().get("A1").setHorizontalAlignment(HorizontalAlignType.Center);
            sheet.getRange().get("A1").setVerticalAlignment(VerticalAlignType.Center);
            sheet.getRange().get("A1").getStyle().getFont().isBold(true);
            sheet.getRange().get("A1").getStyle().getFont().setSize(13);
    
            //Set row height of the first row
            sheet.setRowHeight(1,30);
    
            //Create a two-dimensional array
            String[][] twoDimensionalArray = new String[][]{
                    {"Name", "Gender", "Birth Date", "Educational Background", "Contact Number", "Position", "ID"},
                    {"Allen", "Male", "1990-02-10", "Bachelor", "24756854", "Mechanic", "0021"},
                    {"Patrick", "Male", "1985-06-08", "Master", "59863247", "Mechanic", "0022"},
                    {"Jenna", "Female", "1989-11-25", "Bachelor", "79540352", "Sales", "0023"},
                    {"Tommy", "Male", "1988-04-16", "Master", "52014060", "Mechanic", "0024"},
                    {"Christina", "Female", "1998-01-21", "Bachelor", "35401489", "HR", "0025"}
            };
    
            //Import data from DataTable to worksheet
            sheet.insertArray(twoDimensionalArray,2,1);
    
            //Set row height of a range
            sheet.getRange().get("A2:G7").setRowHeight(15);
    
            //Set column width
            sheet.setColumnWidth(2,15);
            sheet.setColumnWidth(3,21);
            sheet.setColumnWidth(4,15);
    
            //Set border style of a range
            sheet.getRange().get("A2:G7").borderAround(LineStyleType.Medium);
            sheet.getRange().get("A2:G7").borderInside(LineStyleType.Thin);
            sheet.getRange().get("A2:G2").borderAround(LineStyleType.Medium);
            sheet.getRange().get("A2:G7").getBorders().setKnownColor(ExcelColors.Black);
    
            //Save to a .xlsx file
            wb.saveToFile("output/NewSpreadsheet.xlsx", FileFormat.Version2016);
        }
    }

Java: Create, Read, or Update Excel Documents

Чтение данных рабочего листа в Java

Метод CellRange.getValue() возвращает числовое значение или текстовое значение ячейки в виде строки. Чтобы получить данные всего листа или диапазона ячеек, пройдитесь по ячейкам внутри него. Ниже приведены шаги по получению данных листа с помощью Spire.XLS for Java.

  • Создайте объект книги.
  • Загрузите документ Excel с помощью метода Workbook.loadFromFile().
  • Получите конкретный лист, используя метод Workbook.getWorksheets().get(index).
  • Получите диапазон ячеек, содержащий данные, с помощью метода Worksheet.getAllocatedRange().
  • Перебирайте строки и столбцы, чтобы получить ячейки в диапазоне, и возвращайте значение каждой ячейки с помощью метода CellRange.getValue().
  • Java
import com.spire.xls.CellRange;
    import com.spire.xls.Workbook;
    import com.spire.xls.Worksheet;
    
    public class ReadData {
    
        public static void main(String[] args) {
    
            //Create a Workbook object
            Workbook wb = new Workbook();
    
            //Load an existing Excel file
            wb.loadFromFile("C:/Users/Administrator/Desktop/NewSpreadsheet.xlsx");
    
            //Get the first worksheet
            Worksheet sheet = wb.getWorksheets().get(0);
    
            //Get the cell range containing data
            CellRange locatedRange = sheet.getAllocatedRange();
    
            //Iterate through the rows
            for (int i = 0; i < locatedRange.getRows().length; i++) {
    
                //Iterate through the columns
                for (int j = 0; j < locatedRange.getColumnCount(); j++) {
    
                    //Get data of a specific cell
                    System.out.print(locatedRange.get(i + 1, j + 1).getValue() + "  ");
                }
                System.out.println();
            }
        }
    }

Java: Create, Read, or Update Excel Documents

Обновление документа Excel в Java

Чтобы изменить значение определенной ячейки, просто присвойте ей значение с помощью метода Worksheet.getRange().setValue(). Ниже приведены подробные шаги.

  • Создайте объект книги.
  • Загрузите документ Excel с помощью метода Workbook.LoadFromFile().
  • Получите конкретный лист через свойство Workbook.Worksheets[index].
  • Измените значение определенной ячейки с помощью свойства Worksheet.Range.Value.
  • Сохраните книгу в файл Excel, используя метод Workbook.SaveToFile().
  • Java
import com.spire.xls.ExcelVersion;
    import com.spire.xls.Workbook;
    import com.spire.xls.Worksheet;
    
    public class UpdateExcel {
    
        public static void main(String[] args) {
    
            //Create a Workbook object
            Workbook wb = new Workbook();
    
            //Load an existing Excel file
            wb.loadFromFile("C:/Users/Administrator/Desktop/NewSpreadsheet.xlsx");
    
            //Get the first worksheet
            Worksheet sheet = wb.getWorksheets().get(0);
    
            //Change the value of a specific cell
            sheet.getRange().get("A1").setValue("Updated Value");
    
            //Save to file
            wb.saveToFile("output/Updated.xlsx", ExcelVersion.Version2016);
        }
    }

Java: Create, Read, or Update Excel Documents

Подать заявку на временную лицензию

Если вы хотите удалить сообщение об оценке из сгенерированных документов или избавиться от ограничений функции, пожалуйста запросите 30-дневную пробную лицензию для себя.

Смотрите также

Mit Maven installieren

<dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls</artifactId>
        <version>14.1.3</version>
    </dependency>
    

verwandte Links

Die Excel-Tabelle ist ein beliebtes Dateiformat, mit dem Benutzer Daten in Tabellen anordnen, analysieren und anzeigen können. Die Fähigkeit zur programmgesteuerten Interaktion mit Excel-Dateien ist wertvoll für die Automatisierung und Integration ihrer Funktionen in Software. Dies ist insbesondere dann von Vorteil, wenn Sie mit umfangreichen Datensätzen, komplexen Berechnungen oder der dynamischen Generierung/Aktualisierung von Daten arbeiten. In diesem Artikel erfahren Sie, wie das geht Erstellen, lesen oder aktualisieren Sie Excel-Dokumente in Java mit Spire.XLS for Java.

Installieren Sie Spire.XLS for Java

Zunächst müssen Sie die Datei Spire.Xls.jar als Abhängigkeit zu Ihrem Java-Programm hinzufügen. Die JAR-Datei kann über diesen Linkheruntergeladen werden. Wenn Sie Maven verwenden, können Sie die JAR-Datei einfach in Ihre Anwendung importieren, indem Sie den folgenden Code zur pom.xml-Datei Ihres Projekts hinzufügen.

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <name>e-iceblue</name>
        <url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls</artifactId>
        <version>14.3.2</version>
    </dependency>
</dependencies>
    

Erstellen Sie ein Excel-Dokument in Java

Spire.XLS for Java bietet eine Vielzahl von Klassen und Schnittstellen, die Sie zum Erstellen und Bearbeiten von Excel-Dokumenten verwenden können. Hier ist eine Liste wichtiger Klassen, Eigenschaften und Methoden, die in diesem Artikel beteiligt sind.

Mitglied Beschreibung
Arbeitsbuchklasse Stellt ein Excel-Arbeitsmappenmodell dar.
Workbook.getWorksheets().add()-Methode Fügt der Arbeitsmappe ein Arbeitsblatt hinzu.
Workbook.saveToFile()-Methode Speichert die Arbeitsmappe in einem Excel-Dokument.
Arbeitsblattklasse Stellt ein Arbeitsblatt in einer Arbeitsmappe dar.
Worksheet.getRange()-Methode Ruft eine bestimmte Zelle oder einen bestimmten Zellbereich aus dem Arbeitsblatt ab.
Worksheet.insertArray()-Methode Importiert Daten aus einem Array in ein Arbeitsblatt.
CellRange-Klasse Stellt eine Zelle oder einen Zellbereich im Arbeitsblatt dar.
CellRange.setValue()-Methode Legt den Wert einer Zelle fest.
CellRange.getValue()-Methode Ruft den Wert einer Zelle ab.

Im Folgenden finden Sie die Schritte zum Erstellen eines Excel-Dokuments von Grund auf mit Spire.XLS for Java.

  • Erstellen Sie ein Workbook-Objekt.
  • Fügen Sie ein Arbeitsblatt mit der Methode Workbook.getWorksheets().add() hinzu.
  • Schreiben Sie Daten mit der Methode Worksheet.getRange.setValue() in eine bestimmte Zelle.
  • Importieren Sie Daten aus einem Array in das Arbeitsblatt mit der Methode .
  • Speichern Sie die Arbeitsmappe mit der Methode Workbook.saveToFile() in einem Excel-Dokument.
  • Java
import com.spire.xls.*;
    
    public class CreateSpreadsheet {
    
        public static void main(String[] args) {
    
            //Create a Workbook object
            Workbook wb = new Workbook();
    
            //Remove default worksheets
            wb.getWorksheets().clear();
    
            //Add a worksheet and name it "Employee"
            Worksheet sheet = wb.getWorksheets().add("Employee");
    
            //Merge the cells between A1 and G1
            sheet.getRange().get("A1:G1").merge();
    
            //Write data to A1 and apply formatting to it
            sheet.getRange().get("A1").setValue("Basic Information of Employees of Huanyu Automobile Company");
            sheet.getRange().get("A1").setHorizontalAlignment(HorizontalAlignType.Center);
            sheet.getRange().get("A1").setVerticalAlignment(VerticalAlignType.Center);
            sheet.getRange().get("A1").getStyle().getFont().isBold(true);
            sheet.getRange().get("A1").getStyle().getFont().setSize(13);
    
            //Set row height of the first row
            sheet.setRowHeight(1,30);
    
            //Create a two-dimensional array
            String[][] twoDimensionalArray = new String[][]{
                    {"Name", "Gender", "Birth Date", "Educational Background", "Contact Number", "Position", "ID"},
                    {"Allen", "Male", "1990-02-10", "Bachelor", "24756854", "Mechanic", "0021"},
                    {"Patrick", "Male", "1985-06-08", "Master", "59863247", "Mechanic", "0022"},
                    {"Jenna", "Female", "1989-11-25", "Bachelor", "79540352", "Sales", "0023"},
                    {"Tommy", "Male", "1988-04-16", "Master", "52014060", "Mechanic", "0024"},
                    {"Christina", "Female", "1998-01-21", "Bachelor", "35401489", "HR", "0025"}
            };
    
            //Import data from DataTable to worksheet
            sheet.insertArray(twoDimensionalArray,2,1);
    
            //Set row height of a range
            sheet.getRange().get("A2:G7").setRowHeight(15);
    
            //Set column width
            sheet.setColumnWidth(2,15);
            sheet.setColumnWidth(3,21);
            sheet.setColumnWidth(4,15);
    
            //Set border style of a range
            sheet.getRange().get("A2:G7").borderAround(LineStyleType.Medium);
            sheet.getRange().get("A2:G7").borderInside(LineStyleType.Thin);
            sheet.getRange().get("A2:G2").borderAround(LineStyleType.Medium);
            sheet.getRange().get("A2:G7").getBorders().setKnownColor(ExcelColors.Black);
    
            //Save to a .xlsx file
            wb.saveToFile("output/NewSpreadsheet.xlsx", FileFormat.Version2016);
        }
    }

Java: Create, Read, or Update Excel Documents

Lesen Sie Daten eines Arbeitsblatts in Java

Die Methode CellRange.getValue() gibt den Zahlenwert oder Textwert einer Zelle als Zeichenfolge zurück. Um Daten eines gesamten Arbeitsblatts oder eines Zellbereichs abzurufen, durchlaufen Sie die darin enthaltenen Zellen. Im Folgenden finden Sie die Schritte zum Abrufen von Daten eines Arbeitsblatts mit Spire.XLS for Java.

  • Erstellen Sie ein Workbook-Objekt.
  • Laden Sie ein Excel-Dokument mit der Methode Workbook.loadFromFile().
  • Rufen Sie ein bestimmtes Arbeitsblatt mit der Methode Workbook.getWorksheets().get(index) ab.
  • Rufen Sie den Zellbereich mit Daten mit der Methode Worksheet.getAllocatedRange() ab.
  • Durchlaufen Sie die Zeilen und Spalten, um Zellen innerhalb des Bereichs zu erhalten, und geben Sie den Wert jeder Zelle mithilfe der Methode CellRange.getValue() zurück.
  • Java
import com.spire.xls.CellRange;
    import com.spire.xls.Workbook;
    import com.spire.xls.Worksheet;
    
    public class ReadData {
    
        public static void main(String[] args) {
    
            //Create a Workbook object
            Workbook wb = new Workbook();
    
            //Load an existing Excel file
            wb.loadFromFile("C:/Users/Administrator/Desktop/NewSpreadsheet.xlsx");
    
            //Get the first worksheet
            Worksheet sheet = wb.getWorksheets().get(0);
    
            //Get the cell range containing data
            CellRange locatedRange = sheet.getAllocatedRange();
    
            //Iterate through the rows
            for (int i = 0; i < locatedRange.getRows().length; i++) {
    
                //Iterate through the columns
                for (int j = 0; j < locatedRange.getColumnCount(); j++) {
    
                    //Get data of a specific cell
                    System.out.print(locatedRange.get(i + 1, j + 1).getValue() + "  ");
                }
                System.out.println();
            }
        }
    }

Java: Create, Read, or Update Excel Documents

Aktualisieren Sie ein Excel-Dokument in Java

Um den Wert einer bestimmten Zelle zu ändern, weisen Sie ihr einfach mit der Methode Worksheet.getRange().setValue() einen neuen Wert zu. Im Folgenden finden Sie die detaillierten Schritte.

  • Erstellen Sie ein Workbook-Objekt.
  • Laden Sie ein Excel-Dokument mit der Methode Workbook.LoadFromFile().
  • Rufen Sie ein bestimmtes Arbeitsblatt über die Eigenschaft Workbook.Worksheets[index] ab.
  • Ändern Sie den Wert einer bestimmten Zelle mithilfe der Worksheet.Range.Value-Eigenschaft.
  • Speichern Sie die Arbeitsmappe mit der Methode Workbook.SaveToFile() in einer Excel-Datei.
  • Java
import com.spire.xls.ExcelVersion;
    import com.spire.xls.Workbook;
    import com.spire.xls.Worksheet;
    
    public class UpdateExcel {
    
        public static void main(String[] args) {
    
            //Create a Workbook object
            Workbook wb = new Workbook();
    
            //Load an existing Excel file
            wb.loadFromFile("C:/Users/Administrator/Desktop/NewSpreadsheet.xlsx");
    
            //Get the first worksheet
            Worksheet sheet = wb.getWorksheets().get(0);
    
            //Change the value of a specific cell
            sheet.getRange().get("A1").setValue("Updated Value");
    
            //Save to file
            wb.saveToFile("output/Updated.xlsx", ExcelVersion.Version2016);
        }
    }

Java: Create, Read, or Update Excel Documents

Beantragen Sie eine temporäre Lizenz

Wenn Sie die Bewertungsmeldung aus den generierten Dokumenten entfernen oder die Funktionseinschränkungen beseitigen möchten, wenden Sie sich bitte an uns Fordern Sie eine 30-Tage-Testlizenz an für sich selbst.

Siehe auch

Instalar con Maven

<dependency>
    <groupId>e-iceblue</groupId>
    <artifactId>spire.xls</artifactId>
    <version>14.1.3</version>
    </dependency>
    

enlaces relacionados

La hoja de cálculo de Excel es un formato de archivo popular que permite a los usuarios organizar, analizar y mostrar datos en tablas. La capacidad de interactuar mediante programación con archivos de Excel es valiosa para automatizar e integrar sus funciones en el software. Esto es especialmente beneficioso cuando se trata de conjuntos de datos extensos, cálculos complejos o generación/actualización dinámica de datos. En este artículo, aprenderá cómo cree, lea o actualice un documento de Excel en Java utilizando Spire.XLS for Java.

Instalar Spire.XLS for Java

Primero, debe agregar el archivo Spire.Xls.jar como una dependencia en su programa Java. El archivo JAR se puede descargar desde este enlace. Si usa Maven, puede importar fácilmente el archivo JAR en su aplicación agregando el siguiente código al archivo pom.xml de su proyecto.

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <name>e-iceblue</name>
        <url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls</artifactId>
        <version>14.3.2</version>
    </dependency>
</dependencies>
    

Crear un documento de Excel en Java

Spire.XLS for Java ofrece una variedad de clases e interfaces que puede utilizar para crear y editar documentos de Excel. Aquí hay una lista de clases, propiedades y métodos importantes involucrados en este artículo.

Miembro Descripción
clase de libro de trabajo Representa un modelo de libro de Excel.
Método Workbook.getWorksheets().add() Agrega una hoja de trabajo al libro de trabajo.
Método Workbook.saveToFile() Guarda el libro en un documento de Excel.
clase de hoja de trabajo Representa una hoja de trabajo en un libro de trabajo.
Método Worksheet.getRange() Obtiene una celda o rango de celdas específico de la hoja de trabajo.
Método Worksheet.insertArray() Importa datos de una matriz a una hoja de trabajo.
clase rango de celdas Representa una celda o rango de celdas en la hoja de trabajo.
Método CellRange.setValue() Establece el valor de una celda.
Método CellRange.getValue() Obtiene el valor de una celda.

Los siguientes son los pasos para crear un documento de Excel desde cero usando Spire.XLS for Java.

  • Cree un objeto de libro de trabajo.
  • Agregue una hoja de trabajo usando el método Workbook.getWorksheets().add().
  • Escriba datos en una celda específica utilizando el método Worksheet.getRange.setValue().
  • Importe datos de una matriz a la hoja de trabajo utilizando el método Worksheet.insertArray().
  • Guarde el libro de trabajo en un documento de Excel utilizando el método Workbook.saveToFile().
  • Java
import com.spire.xls.*;
    
    public class CreateSpreadsheet {
    
        public static void main(String[] args) {
    
            //Create a Workbook object
            Workbook wb = new Workbook();
    
            //Remove default worksheets
            wb.getWorksheets().clear();
    
            //Add a worksheet and name it "Employee"
            Worksheet sheet = wb.getWorksheets().add("Employee");
    
            //Merge the cells between A1 and G1
            sheet.getRange().get("A1:G1").merge();
    
            //Write data to A1 and apply formatting to it
            sheet.getRange().get("A1").setValue("Basic Information of Employees of Huanyu Automobile Company");
            sheet.getRange().get("A1").setHorizontalAlignment(HorizontalAlignType.Center);
            sheet.getRange().get("A1").setVerticalAlignment(VerticalAlignType.Center);
            sheet.getRange().get("A1").getStyle().getFont().isBold(true);
            sheet.getRange().get("A1").getStyle().getFont().setSize(13);
    
            //Set row height of the first row
            sheet.setRowHeight(1,30);
    
            //Create a two-dimensional array
            String[][] twoDimensionalArray = new String[][]{
                    {"Name", "Gender", "Birth Date", "Educational Background", "Contact Number", "Position", "ID"},
                    {"Allen", "Male", "1990-02-10", "Bachelor", "24756854", "Mechanic", "0021"},
                    {"Patrick", "Male", "1985-06-08", "Master", "59863247", "Mechanic", "0022"},
                    {"Jenna", "Female", "1989-11-25", "Bachelor", "79540352", "Sales", "0023"},
                    {"Tommy", "Male", "1988-04-16", "Master", "52014060", "Mechanic", "0024"},
                    {"Christina", "Female", "1998-01-21", "Bachelor", "35401489", "HR", "0025"}
            };
    
            //Import data from DataTable to worksheet
            sheet.insertArray(twoDimensionalArray,2,1);
    
            //Set row height of a range
            sheet.getRange().get("A2:G7").setRowHeight(15);
    
            //Set column width
            sheet.setColumnWidth(2,15);
            sheet.setColumnWidth(3,21);
            sheet.setColumnWidth(4,15);
    
            //Set border style of a range
            sheet.getRange().get("A2:G7").borderAround(LineStyleType.Medium);
            sheet.getRange().get("A2:G7").borderInside(LineStyleType.Thin);
            sheet.getRange().get("A2:G2").borderAround(LineStyleType.Medium);
            sheet.getRange().get("A2:G7").getBorders().setKnownColor(ExcelColors.Black);
    
            //Save to a .xlsx file
            wb.saveToFile("output/NewSpreadsheet.xlsx", FileFormat.Version2016);
        }
    }

Java: Create, Read, or Update Excel Documents

Leer datos de una hoja de trabajo en Java

El método CellRange.getValue() devuelve el valor numérico o el valor de texto de una celda como una cadena. Para obtener datos de una hoja de trabajo completa o un rango de celdas, recorra las celdas que contiene. Los siguientes son los pasos para obtener datos de una hoja de trabajo usando Spire.XLS for Java.

  • Cree un objeto de libro de trabajo.
  • Cargue un documento de Excel utilizando el método Workbook.loadFromFile().
  • Obtenga una hoja de trabajo específica utilizando el método Workbook.getWorksheets().get(index).
  • Obtenga el rango de celdas que contiene datos utilizando el método Worksheet.getAllocatedRange().
  • Itere a través de las filas y columnas para obtener celdas dentro del rango y devuelva el valor de cada celda usando el método CellRange.getValue().
  • Java
import com.spire.xls.CellRange;
    import com.spire.xls.Workbook;
    import com.spire.xls.Worksheet;
    
    public class ReadData {
    
        public static void main(String[] args) {
    
            //Create a Workbook object
            Workbook wb = new Workbook();
    
            //Load an existing Excel file
            wb.loadFromFile("C:/Users/Administrator/Desktop/NewSpreadsheet.xlsx");
    
            //Get the first worksheet
            Worksheet sheet = wb.getWorksheets().get(0);
    
            //Get the cell range containing data
            CellRange locatedRange = sheet.getAllocatedRange();
    
            //Iterate through the rows
            for (int i = 0; i < locatedRange.getRows().length; i++) {
    
                //Iterate through the columns
                for (int j = 0; j < locatedRange.getColumnCount(); j++) {
    
                    //Get data of a specific cell
                    System.out.print(locatedRange.get(i + 1, j + 1).getValue() + "  ");
                }
                System.out.println();
            }
        }
    }

Java: Create, Read, or Update Excel Documents

Actualizar un documento de Excel en Java

Para cambiar el valor de una determinada celda, simplemente vuelva a asignarle un valor utilizando el método Worksheet.getRange().setValue(). Los siguientes son los pasos detallados.

  • Cree un objeto de libro de trabajo.
  • Cargue un documento de Excel utilizando el método Workbook.LoadFromFile().
  • Obtenga una hoja de trabajo específica a través de la propiedad Workbook.Worksheets[index].
  • Cambie el valor de una celda en particular a través de la propiedad Worksheet.Range.Value.
  • Guarde el libro en un archivo de Excel utilizando el método Workbook.SaveToFile().
  • Java
import com.spire.xls.ExcelVersion;
    import com.spire.xls.Workbook;
    import com.spire.xls.Worksheet;
    
    public class UpdateExcel {
    
        public static void main(String[] args) {
    
            //Create a Workbook object
            Workbook wb = new Workbook();
    
            //Load an existing Excel file
            wb.loadFromFile("C:/Users/Administrator/Desktop/NewSpreadsheet.xlsx");
    
            //Get the first worksheet
            Worksheet sheet = wb.getWorksheets().get(0);
    
            //Change the value of a specific cell
            sheet.getRange().get("A1").setValue("Updated Value");
    
            //Save to file
            wb.saveToFile("output/Updated.xlsx", ExcelVersion.Version2016);
        }
    }

Java: Create, Read, or Update Excel Documents

Solicitar una licencia temporal

Si desea eliminar el mensaje de evaluación de los documentos generados o deshacerse de las limitaciones de la función, por favor solicitar una licencia de prueba de 30 días para ti.

Ver también

메이븐으로 설치

<dependency>
    <groupId>e-iceblue</groupId>
    <artifactId>spire.xls</artifactId>
    <version>14.1.3</version></dependency>
    

관련된 링크들

Excel 스프레드시트는 사용자가 테이블의 데이터를 정렬, 분석 및 표시할 수 있는 널리 사용되는 파일 형식입니다. Excel 파일과 프로그래밍 방식으로 상호 작용하는 기능은 해당 기능을 소프트웨어에 자동화하고 통합하는 데 유용합니다. 이는 광범위한 데이터 세트, 복잡한 계산을 처리하거나 데이터를 동적으로 생성/업데이트할 때 특히 유용합니다. 이 기사에서는 다음 방법을 배웁니다 Java로 Excel 문서 생성, 읽기 또는 업데이트 Spire.XLS for Java사용합니다.

Spire.XLS for Java 설치

먼저 Spire.Xls.jar 파일을 Java 프로그램의 종속성으로 추가해야 합니다. JAR 파일은 이 링크에서 다운로드할 수 있습니다. Maven을 사용하는 경우 프로젝트의 pom.xml 파일에 다음 코드를 추가하여 애플리케이션에서 JAR 파일을 쉽게 가져올 수 있습니다.

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <name>e-iceblue</name>
        <url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls</artifactId>
        <version>14.3.2</version>
    </dependency>
</dependencies>
    

Java로 Excel 문서 만들기

Spire.XLS for Java는 Excel 문서를 생성하고 편집하는 데 사용할 수 있는 다양한 클래스와 인터페이스를 제공합니다. 다음은 이 기사와 관련된 중요한 클래스, 속성 및 메서드 목록입니다.

회원 설명
워크북 수업 Excel 통합 문서 모델을 나타냅니다.
Workbook.getWorksheets().add() 메서드 통합 문서에 워크시트를 추가합니다.
Workbook.saveToFile() 메서드 통합 문서를 Excel 문서에 저장합니다.
워크시트 수업 통합 문서의 워크시트를 나타냅니다.
Worksheet.getRange() 메서드 워크시트에서 특정 셀이나 셀 범위를 가져옵니다.
Worksheet.insertArray() 메서드 배열의 데이터를 워크시트로 가져옵니다.
CellRange 클래스 워크시트의 셀 또는 셀 범위를 나타냅니다.
CellRange.setValue() 메서드 셀의 값을 설정합니다.
CellRange.getValue() 메서드 셀의 값을 가져옵니다.

다음은 Spire.XLS for Java 사용하여 처음부터 Excel 문서를 만드는 단계입니다.

  • 통합 문서 개체를 만듭니다.
  • Workbook.getWorksheets().add() 메서드를 사용하여 워크시트를 추가합니다.
  • Worksheet.getRange.setValue() 메서드를 사용하여 특정 셀에 데이터를 씁니다.
  • Worksheet.insertArray() 메서드를 사용하여 배열의 데이터를 워크시트로 가져옵니다.
  • Workbook.saveToFile() 메서드를 사용하여 통합 문서를 Excel 문서에 저장합니다.
  • Java
import com.spire.xls.*;
    
    public class CreateSpreadsheet {
    
        public static void main(String[] args) {
    
            //Create a Workbook object
            Workbook wb = new Workbook();
    
            //Remove default worksheets
            wb.getWorksheets().clear();
    
            //Add a worksheet and name it "Employee"
            Worksheet sheet = wb.getWorksheets().add("Employee");
    
            //Merge the cells between A1 and G1
            sheet.getRange().get("A1:G1").merge();
    
            //Write data to A1 and apply formatting to it
            sheet.getRange().get("A1").setValue("Basic Information of Employees of Huanyu Automobile Company");
            sheet.getRange().get("A1").setHorizontalAlignment(HorizontalAlignType.Center);
            sheet.getRange().get("A1").setVerticalAlignment(VerticalAlignType.Center);
            sheet.getRange().get("A1").getStyle().getFont().isBold(true);
            sheet.getRange().get("A1").getStyle().getFont().setSize(13);
    
            //Set row height of the first row
            sheet.setRowHeight(1,30);
    
            //Create a two-dimensional array
            String[][] twoDimensionalArray = new String[][]{
                    {"Name", "Gender", "Birth Date", "Educational Background", "Contact Number", "Position", "ID"},
                    {"Allen", "Male", "1990-02-10", "Bachelor", "24756854", "Mechanic", "0021"},
                    {"Patrick", "Male", "1985-06-08", "Master", "59863247", "Mechanic", "0022"},
                    {"Jenna", "Female", "1989-11-25", "Bachelor", "79540352", "Sales", "0023"},
                    {"Tommy", "Male", "1988-04-16", "Master", "52014060", "Mechanic", "0024"},
                    {"Christina", "Female", "1998-01-21", "Bachelor", "35401489", "HR", "0025"}
            };
    
            //Import data from DataTable to worksheet
            sheet.insertArray(twoDimensionalArray,2,1);
    
            //Set row height of a range
            sheet.getRange().get("A2:G7").setRowHeight(15);
    
            //Set column width
            sheet.setColumnWidth(2,15);
            sheet.setColumnWidth(3,21);
            sheet.setColumnWidth(4,15);
    
            //Set border style of a range
            sheet.getRange().get("A2:G7").borderAround(LineStyleType.Medium);
            sheet.getRange().get("A2:G7").borderInside(LineStyleType.Thin);
            sheet.getRange().get("A2:G2").borderAround(LineStyleType.Medium);
            sheet.getRange().get("A2:G7").getBorders().setKnownColor(ExcelColors.Black);
    
            //Save to a .xlsx file
            wb.saveToFile("output/NewSpreadsheet.xlsx", FileFormat.Version2016);
        }
    }

Java: Create, Read, or Update Excel Documents

Java에서 워크시트 데이터 읽기

CellRange.getValue() 메서드는 셀의 숫자 값이나 텍스트 값을 문자열로 반환합니다. 전체 워크시트나 셀 범위의 데이터를 얻으려면 그 안의 셀을 반복하세요. 다음은 Spire.XLS for Java를 사용하여 워크시트의 데이터를 가져오는 단계입니다.

  • 통합 문서 개체를 만듭니다.
  • Workbook.loadFromFile() 메서드를 사용하여 Excel 문서를 로드합니다.
  • Workbook.getWorksheets().get(index) 메서드를 사용하여 특정 워크시트를 가져옵니다.
  • Worksheet.getAllocationRange() 메서드를 사용하여 데이터가 포함된 셀 범위를 가져옵니다.
  • 행과 열을 반복하여 범위 내의 셀을 가져오고 CellRange.getValue() 메서드를 사용하여 각 셀의 값을 반환합니다.
  • Java
import com.spire.xls.CellRange;
    import com.spire.xls.Workbook;
    import com.spire.xls.Worksheet;
    
    public class ReadData {
    
        public static void main(String[] args) {
    
            //Create a Workbook object
            Workbook wb = new Workbook();
    
            //Load an existing Excel file
            wb.loadFromFile("C:/Users/Administrator/Desktop/NewSpreadsheet.xlsx");
    
            //Get the first worksheet
            Worksheet sheet = wb.getWorksheets().get(0);
    
            //Get the cell range containing data
            CellRange locatedRange = sheet.getAllocatedRange();
    
            //Iterate through the rows
            for (int i = 0; i < locatedRange.getRows().length; i++) {
    
                //Iterate through the columns
                for (int j = 0; j < locatedRange.getColumnCount(); j++) {
    
                    //Get data of a specific cell
                    System.out.print(locatedRange.get(i + 1, j + 1).getValue() + "  ");
                }
                System.out.println();
            }
        }
    }

Java: Create, Read, or Update Excel Documents

Java에서 Excel 문서 업데이트

특정 셀의 값을 변경하려면 Worksheet.getRange().setValue() 메서드를 사용하여 해당 셀에 값을 다시 할당하면 됩니다. 자세한 단계는 다음과 같습니다.

  • 통합 문서 개체를 만듭니다.
  • Workbook.LoadFromFile() 메서드를 사용하여 Excel 문서를 로드합니다.
  • Workbook.Worksheets[index] 속성을 통해 특정 워크시트를 가져옵니다.
  • Worksheet.Range.Value 속성을 통해 특정 셀의 값을 변경합니다.
  • Workbook.SaveToFile() 메서드를 사용하여 통합 문서를 Excel 파일에 저장합니다.
  • Java
import com.spire.xls.ExcelVersion;
    import com.spire.xls.Workbook;
    import com.spire.xls.Worksheet;
    
    public class UpdateExcel {
    
        public static void main(String[] args) {
    
            //Create a Workbook object
            Workbook wb = new Workbook();
    
            //Load an existing Excel file
            wb.loadFromFile("C:/Users/Administrator/Desktop/NewSpreadsheet.xlsx");
    
            //Get the first worksheet
            Worksheet sheet = wb.getWorksheets().get(0);
    
            //Change the value of a specific cell
            sheet.getRange().get("A1").setValue("Updated Value");
    
            //Save to file
            wb.saveToFile("output/Updated.xlsx", ExcelVersion.Version2016);
        }
    }

Java: Create, Read, or Update Excel Documents

임시 라이센스 신청

생성된 문서에서 평가 메시지를 제거하고 싶거나, 기능 제한을 없애고 싶다면 30일 평가판 라이센스 요청 자신을 위해.

또한보십시오

Wednesday, 06 December 2023 03:09

Java: crea, leggi o aggiorna documenti Excel

Installa con Maven

<dependency>
    <groupId>e-iceblue</groupId>
       <artifactId>spire.xls</artifactId>
    <version>14.1.3</version></dependency>
    

Link correlati

Il foglio di calcolo Excel è un formato di file popolare che consente agli utenti di organizzare, analizzare e visualizzare i dati in tabelle. La capacità di interagire a livello di codice con i file Excel è preziosa per automatizzare e integrare le sue funzionalità nel software. Ciò è particolarmente utile quando si ha a che fare con set di dati estesi, calcoli complessi o quando si generano/aggiornano dati in modo dinamico. In questo articolo imparerai come farlo creare, leggere o aggiornare documenti Excel in Java utilizzando Spire.XLS for Java.

Installa Spire.XLS for Java

Innanzitutto, devi aggiungere il file Spire.Xls.jar come dipendenza nel tuo programma Java. Il file JAR può essere scaricato da questo collegamento. Se utilizzi Maven, puoi importare facilmente il file JAR nella tua applicazione aggiungendo il seguente codice al file pom.xml del tuo progetto.

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <name>e-iceblue</name>
        <url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls</artifactId>
        <version>14.3.2</version>
    </dependency>
</dependencies>
    

Crea un documento Excel in Java

Spire.XLS for Java offre una varietà di classi e interfacce che è possibile utilizzare per creare e modificare documenti Excel. Di seguito è riportato un elenco di classi, proprietà e metodi importanti coinvolti in questo articolo.

Membro Descrizione
Classe cartella di lavoro Rappresenta un modello di cartella di lavoro di Excel.
Metodo Workbook.getWorksheets().add() Aggiunge un foglio di lavoro alla cartella di lavoro.
Metodo Workbook.saveToFile() Salva la cartella di lavoro in un documento Excel.
Classe di fogli di lavoro Rappresenta un foglio di lavoro in una cartella di lavoro.
Metodo Worksheet.getRange() Ottiene una cella o un intervallo di celle specifico dal foglio di lavoro.
Metodo Worksheet.insertArray() Importa i dati da una matrice al foglio di lavoro.
Classe CellRange Rappresenta una cella o un intervallo di celle nel foglio di lavoro.
Metodo CellRange.setValue() Imposta il valore di una cella.
Metodo CellRange.getValue() Ottiene il valore di una cella.

Di seguito sono riportati i passaggi per creare un documento Excel da zero utilizzando Spire.XLS for Java.

  • Creare un oggetto cartella di lavoro.
  • Aggiungi un foglio di lavoro utilizzando il metodo Workbook.getWorksheets().add().
  • Scrivi i dati in una cella specifica utilizzando il metodo Worksheet.getRange.setValue().
  • Importa i dati da un array al foglio di lavoro utilizzando il metodo Worksheet.insertArray().
  • Salva la cartella di lavoro in un documento Excel utilizzando il metodo Workbook.saveToFile().
  • Java
import com.spire.xls.*;
    
    public class CreateSpreadsheet {
    
        public static void main(String[] args) {
    
            //Create a Workbook object
            Workbook wb = new Workbook();
    
            //Remove default worksheets
            wb.getWorksheets().clear();
    
            //Add a worksheet and name it "Employee"
            Worksheet sheet = wb.getWorksheets().add("Employee");
    
            //Merge the cells between A1 and G1
            sheet.getRange().get("A1:G1").merge();
    
            //Write data to A1 and apply formatting to it
            sheet.getRange().get("A1").setValue("Basic Information of Employees of Huanyu Automobile Company");
            sheet.getRange().get("A1").setHorizontalAlignment(HorizontalAlignType.Center);
            sheet.getRange().get("A1").setVerticalAlignment(VerticalAlignType.Center);
            sheet.getRange().get("A1").getStyle().getFont().isBold(true);
            sheet.getRange().get("A1").getStyle().getFont().setSize(13);
    
            //Set row height of the first row
            sheet.setRowHeight(1,30);
    
            //Create a two-dimensional array
            String[][] twoDimensionalArray = new String[][]{
                    {"Name", "Gender", "Birth Date", "Educational Background", "Contact Number", "Position", "ID"},
                    {"Allen", "Male", "1990-02-10", "Bachelor", "24756854", "Mechanic", "0021"},
                    {"Patrick", "Male", "1985-06-08", "Master", "59863247", "Mechanic", "0022"},
                    {"Jenna", "Female", "1989-11-25", "Bachelor", "79540352", "Sales", "0023"},
                    {"Tommy", "Male", "1988-04-16", "Master", "52014060", "Mechanic", "0024"},
                    {"Christina", "Female", "1998-01-21", "Bachelor", "35401489", "HR", "0025"}
            };
    
            //Import data from DataTable to worksheet
            sheet.insertArray(twoDimensionalArray,2,1);
    
            //Set row height of a range
            sheet.getRange().get("A2:G7").setRowHeight(15);
    
            //Set column width
            sheet.setColumnWidth(2,15);
            sheet.setColumnWidth(3,21);
            sheet.setColumnWidth(4,15);
    
            //Set border style of a range
            sheet.getRange().get("A2:G7").borderAround(LineStyleType.Medium);
            sheet.getRange().get("A2:G7").borderInside(LineStyleType.Thin);
            sheet.getRange().get("A2:G2").borderAround(LineStyleType.Medium);
            sheet.getRange().get("A2:G7").getBorders().setKnownColor(ExcelColors.Black);
    
            //Save to a .xlsx file
            wb.saveToFile("output/NewSpreadsheet.xlsx", FileFormat.Version2016);
        }
    }

Java: Create, Read, or Update Excel Documents

Leggere i dati di un foglio di lavoro in Java

Il metodo CellRange.getValue() restituisce il valore numerico o il valore testo di una cella come stringa. Per ottenere i dati di un intero foglio di lavoro o di un intervallo di celle, scorrere le celle al suo interno. Di seguito sono riportati i passaggi per ottenere i dati di un foglio di lavoro utilizzando Spire.XLS for Java.

  • Creare un oggetto cartella di lavoro.
  • Carica un documento Excel utilizzando il metodo Workbook.loadFromFile().
  • Ottieni un foglio di lavoro specifico utilizzando il metodo Workbook.getWorksheets().get(index).
  • Ottieni l'intervallo di celle contenente i dati utilizzando il metodo Worksheet.getAllocatedRange().
  • Scorrere le righe e le colonne per ottenere le celle all'interno dell'intervallo e restituire il valore di ciascuna cella utilizzando il metodo CellRange.getValue().
  • Java
import com.spire.xls.CellRange;
    import com.spire.xls.Workbook;
    import com.spire.xls.Worksheet;
    
    public class ReadData {
    
        public static void main(String[] args) {
    
            //Create a Workbook object
            Workbook wb = new Workbook();
    
            //Load an existing Excel file
            wb.loadFromFile("C:/Users/Administrator/Desktop/NewSpreadsheet.xlsx");
    
            //Get the first worksheet
            Worksheet sheet = wb.getWorksheets().get(0);
    
            //Get the cell range containing data
            CellRange locatedRange = sheet.getAllocatedRange();
    
            //Iterate through the rows
            for (int i = 0; i < locatedRange.getRows().length; i++) {
    
                //Iterate through the columns
                for (int j = 0; j < locatedRange.getColumnCount(); j++) {
    
                    //Get data of a specific cell
                    System.out.print(locatedRange.get(i + 1, j + 1).getValue() + "  ");
                }
                System.out.println();
            }
        }
    }

Java: Create, Read, or Update Excel Documents

Aggiorna un documento Excel in Java

Per modificare il valore di una determinata cella, è sufficiente riassegnarle un valore utilizzando il metodo Worksheet.getRange().setValue(). Di seguito sono riportati i passaggi dettagliati.

  • Creare un oggetto cartella di lavoro.
  • Carica un documento Excel utilizzando il metodo Workbook.LoadFromFile().
  • Ottieni un foglio di lavoro specifico tramite la proprietà Workbook.Worksheets[index].
  • Modificare il valore di una cella particolare tramite la proprietà Worksheet.Range.Value.
  • Salva la cartella di lavoro in un file Excel utilizzando il metodo Workbook.SaveToFile().
  • Java
import com.spire.xls.ExcelVersion;
    import com.spire.xls.Workbook;
    import com.spire.xls.Worksheet;
    
    public class UpdateExcel {
    
        public static void main(String[] args) {
    
            //Create a Workbook object
            Workbook wb = new Workbook();
    
            //Load an existing Excel file
            wb.loadFromFile("C:/Users/Administrator/Desktop/NewSpreadsheet.xlsx");
    
            //Get the first worksheet
            Worksheet sheet = wb.getWorksheets().get(0);
    
            //Change the value of a specific cell
            sheet.getRange().get("A1").setValue("Updated Value");
    
            //Save to file
            wb.saveToFile("output/Updated.xlsx", ExcelVersion.Version2016);
        }
    }

Java: Create, Read, or Update Excel Documents

Richiedi una licenza temporanea

Se desideri rimuovere il messaggio di valutazione dai documenti generati o eliminare le limitazioni della funzione, per favore richiedere una licenza di prova di 30 giorni per te.

Guarda anche

Installer avec Maven

<dependency>
    <groupId>e-iceblue</groupId>
        <artifactId>spire.xls</artifactId>
    <version>14.1.3</version></dependency>
    

La feuille de calcul Excel est un format de fichier populaire qui permet aux utilisateurs d'organiser, d'analyser et d'afficher des données dans des tableaux. La possibilité d'interagir par programmation avec des fichiers Excel est précieuse pour automatiser et intégrer ses fonctionnalités dans le logiciel. Ceci est particulièrement utile lorsqu’il s’agit d’ensembles de données étendus, de calculs complexes ou de génération/mise à jour dynamique de données. Dans cet article, vous apprendrez comment créer, lire ou mettre à jour un document Excel en Java à l'aide de Spire.XLS for Java.

Installer Spire.XLS for Java

Tout d'abord, vous devez ajouter le fichier Spire.Xls.jar en tant que dépendance dans votre programme Java. Le fichier JAR peut être téléchargé à partir de ce lien. Si vous utilisez Maven, vous pouvez facilement importer le fichier JAR dans votre application en ajoutant le code suivant au fichier pom.xml de votre projet.

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <name>e-iceblue</name>
        <url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls</artifactId>
        <version>14.3.2</version>
    </dependency>
</dependencies>
    

Créer un document Excel en Java

Spire.XLS for Java propose une variété de classes et d'interfaces que vous pouvez utiliser pour créer et modifier des documents Excel. Voici une liste des classes, propriétés et méthodes importantes impliquées dans cet article.

Membre Description
Classe de classeur Représente un modèle de classeur Excel.
Méthode Workbook.getWorksheets().add() Ajoute une feuille de calcul au classeur.
Méthode Workbook.saveToFile() Enregistre le classeur dans un document Excel.
Classe de feuille de travail Représente une feuille de calcul dans un classeur.
Méthode Worksheet.getRange() Obtient une cellule ou une plage de cellules spécifique à partir d’une feuille de calcul.
Méthode Worksheet.insertArray() Importe les données d'un tableau vers une feuille de calcul.
Classe CellRange Représente une cellule ou une plage de cellules dans une feuille de calcul.
Méthode CellRange.setValue() Définit la valeur d'une cellule.
Méthode CellRange.getValue() Obtient la valeur d'une cellule.

Voici les étapes pour créer un document Excel à partir de zéro à l’aide de Spire.XLS for Java.

  • Créez un objet Workbook.
  • Ajoutez une feuille de calcul à l’aide de la méthode Workbook.getWorksheets().add().
  • Écrivez des données dans une cellule spécifique à l'aide de la méthode Worksheet.getRange.setValue().
  • Importez les données d'un tableau vers la feuille de calcul à l'aide de la méthode Worksheet.insertArray().
  • Enregistrez le classeur dans un document Excel à l'aide de la méthode Workbook.saveToFile().
  • Java
import com.spire.xls.*;
    
    public class CreateSpreadsheet {
    
        public static void main(String[] args) {
    
            //Create a Workbook object
            Workbook wb = new Workbook();
    
            //Remove default worksheets
            wb.getWorksheets().clear();
    
            //Add a worksheet and name it "Employee"
            Worksheet sheet = wb.getWorksheets().add("Employee");
    
            //Merge the cells between A1 and G1
            sheet.getRange().get("A1:G1").merge();
    
            //Write data to A1 and apply formatting to it
            sheet.getRange().get("A1").setValue("Basic Information of Employees of Huanyu Automobile Company");
            sheet.getRange().get("A1").setHorizontalAlignment(HorizontalAlignType.Center);
            sheet.getRange().get("A1").setVerticalAlignment(VerticalAlignType.Center);
            sheet.getRange().get("A1").getStyle().getFont().isBold(true);
            sheet.getRange().get("A1").getStyle().getFont().setSize(13);
    
            //Set row height of the first row
            sheet.setRowHeight(1,30);
    
            //Create a two-dimensional array
            String[][] twoDimensionalArray = new String[][]{
                    {"Name", "Gender", "Birth Date", "Educational Background", "Contact Number", "Position", "ID"},
                    {"Allen", "Male", "1990-02-10", "Bachelor", "24756854", "Mechanic", "0021"},
                    {"Patrick", "Male", "1985-06-08", "Master", "59863247", "Mechanic", "0022"},
                    {"Jenna", "Female", "1989-11-25", "Bachelor", "79540352", "Sales", "0023"},
                    {"Tommy", "Male", "1988-04-16", "Master", "52014060", "Mechanic", "0024"},
                    {"Christina", "Female", "1998-01-21", "Bachelor", "35401489", "HR", "0025"}
            };
    
            //Import data from DataTable to worksheet
            sheet.insertArray(twoDimensionalArray,2,1);
    
            //Set row height of a range
            sheet.getRange().get("A2:G7").setRowHeight(15);
    
            //Set column width
            sheet.setColumnWidth(2,15);
            sheet.setColumnWidth(3,21);
            sheet.setColumnWidth(4,15);
    
            //Set border style of a range
            sheet.getRange().get("A2:G7").borderAround(LineStyleType.Medium);
            sheet.getRange().get("A2:G7").borderInside(LineStyleType.Thin);
            sheet.getRange().get("A2:G2").borderAround(LineStyleType.Medium);
            sheet.getRange().get("A2:G7").getBorders().setKnownColor(ExcelColors.Black);
    
            //Save to a .xlsx file
            wb.saveToFile("output/NewSpreadsheet.xlsx", FileFormat.Version2016);
        }
    }

Java: Create, Read, or Update Excel Documents

Lire les données d'une feuille de calcul en Java

La méthode CellRange.getValue() renvoie la valeur numérique ou la valeur texte d'une cellule sous forme de chaîne. Pour obtenir les données d’une feuille de calcul entière ou d’une plage de cellules, parcourez les cellules qu’elle contient. Voici les étapes pour obtenir les données d'une feuille de calcul à l'aide de Spire.XLS for Java.

  • Créez un objet Workbook.
  • Chargez un document Excel à l'aide de la méthode Workbook.loadFromFile().
  • Obtenez une feuille de calcul spécifique à l’aide de la méthode Workbook.getWorksheets().get(index).
  • Obtenez la plage de cellules contenant les données à l’aide de la méthode Worksheet.getAllocatedRange().
  • Parcourez les lignes et les colonnes pour obtenir les cellules dans la plage et renvoyez la valeur de chaque cellule à l'aide de la méthode CellRange.getValue().
  • Java
import com.spire.xls.CellRange;
    import com.spire.xls.Workbook;
    import com.spire.xls.Worksheet;
    
    public class ReadData {
    
        public static void main(String[] args) {
    
            //Create a Workbook object
            Workbook wb = new Workbook();
    
            //Load an existing Excel file
            wb.loadFromFile("C:/Users/Administrator/Desktop/NewSpreadsheet.xlsx");
    
            //Get the first worksheet
            Worksheet sheet = wb.getWorksheets().get(0);
    
            //Get the cell range containing data
            CellRange locatedRange = sheet.getAllocatedRange();
    
            //Iterate through the rows
            for (int i = 0; i < locatedRange.getRows().length; i++) {
    
                //Iterate through the columns
                for (int j = 0; j < locatedRange.getColumnCount(); j++) {
    
                    //Get data of a specific cell
                    System.out.print(locatedRange.get(i + 1, j + 1).getValue() + "  ");
                }
                System.out.println();
            }
        }
    }

Java: Create, Read, or Update Excel Documents

Mettre à jour un document Excel en Java

Pour modifier la valeur d'une certaine cellule, réattribuez-lui simplement une valeur à l'aide de la méthode Worksheet.getRange().setValue(). Voici les étapes détaillées.

  • Créez un objet Workbook.
  • Chargez un document Excel à l'aide de la méthode Workbook.LoadFromFile().
  • Obtenez une feuille de calcul spécifique via la propriété Workbook.Worksheets[index].
  • Modifiez la valeur d’une cellule particulière via la propriété Worksheet.Range.Value.
  • Enregistrez le classeur dans un fichier Excel à l’aide de la méthode Workbook.SaveToFile().
  • Java
import com.spire.xls.ExcelVersion;
    import com.spire.xls.Workbook;
    import com.spire.xls.Worksheet;
    
    public class UpdateExcel {
    
        public static void main(String[] args) {
    
            //Create a Workbook object
            Workbook wb = new Workbook();
    
            //Load an existing Excel file
            wb.loadFromFile("C:/Users/Administrator/Desktop/NewSpreadsheet.xlsx");
    
            //Get the first worksheet
            Worksheet sheet = wb.getWorksheets().get(0);
    
            //Change the value of a specific cell
            sheet.getRange().get("A1").setValue("Updated Value");
    
            //Save to file
            wb.saveToFile("output/Updated.xlsx", ExcelVersion.Version2016);
        }
    }

Java: Create, Read, or Update Excel Documents

Demander une licence temporaire

Si vous souhaitez supprimer le message d'évaluation des documents générés ou vous débarrasser des limitations fonctionnelles, veuillez demander une licence d'essai de 30 jours pour toi.

Voir également

Wednesday, 06 December 2023 03:03

Java: Write Data to Excel Worksheets

Install with Maven

<dependency>
    <groupId>e-iceblue</groupId>
    <artifactId>spire.xls</artifactId>
    <version>14.1.3</version>
</dependency>
    

Related Links

Excel has the ability to store and organize large amounts of data into orderly spreadsheets quickly. But it is time-consuming if we manually enter data into cells one after another. Automating the creation of Excel files by programming can save us a lot of time and energy. This article introduces how to write data into Excel worksheets in Java by using Spire.XLS for Java.

Install Spire.XLS for Java

First, you're required to add the Spire.Xls.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in your application by adding the following code to your project's pom.xml file.

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <name>e-iceblue</name>
        <url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls</artifactId>
        <version>14.3.2</version>
    </dependency>
</dependencies>
    

Write Text or Number Values to Specific Cells

A certain cell in a worksheet can be accessed by Worksheet.get(int row, int column) method. Then, you can add a text value or a number value to the cell using the IXLSRange.setValue() or IXLSRange.setNumberValue() method. The following are the detailed steps.

  • Create a Workbook object.
  • Get the first worksheet using Workbook.getWorksheets().get() method.
  • Get a specific cell using Workhseet.get() method.
  • Add a text value or a number value to the cell using IXLSRange.setValue() or IXLSRange.setNumberValue() method.
  • Save the workbook to an Excel file using Workbook.saveToFile() method.
  • Java
import com.spire.xls.*;
    
    public class WriteToCells {
    
        public static void main(String[] args) {
    
            //Create a Workbook object
            Workbook workbook = new Workbook();
    
            //Get the first worksheet
            Worksheet worksheet = workbook.getWorksheets().get(0);
    
            //Write data to specific cells
            worksheet.get(1,1).setValue("Name");
            worksheet.get(1,2).setValue("Age");
            worksheet.get(1,3).setValue("Department");
            worksheet.get(1,4).setValue("Hiredate");
            worksheet.get(2,1).setValue("Hazel");
            worksheet.get(2,2).setNumberValue(29);
            worksheet.get(2,3).setValue("Marketing");
            worksheet.get(2,4).setValue("2019-07-01");
            worksheet.get(3,1).setValue("Tina");
            worksheet.get(3,2).setNumberValue(31);
            worksheet.get(3,3).setValue("Technical Support");
            worksheet.get(3,4).setValue("2015-04-27");
    
            //Auto fit column widths
            worksheet.getAllocatedRange().autoFitColumns();
    
            //Apply a style to the first row
            CellStyle style = workbook.getStyles().addStyle("newStyle");
            style.getFont().isBold(true);
            worksheet.getRange().get(1,1,1,4).setStyle(style);
    
            //Save to an Excel file
            workbook.saveToFile("output/WriteToCells.xlsx", ExcelVersion.Version2016);
        }
    }

Java: Write Data to Excel Worksheets

Write Arrays to a Worksheet

Spire.XLS for Java provides the Worksheet.insertArrary() method, allowing programmers to write one-dimensional array or two-dimensional array into the specified cell range of a worksheet. The steps to write arrays to a worksheet are as follows:

  • Create a Workbook object.
  • Get the first worksheet using Workbook.getWorksheets().get() method.
  • Create a one-dimensional array and a two-dimensional array.
  • Insert the arrays to worksheet using Worksheet.insertArray() method.
  • Save the workbook to an Excel file using Workbook.saveToFile() method.
  • Java
import com.spire.xls.*;
    
    public class WriteArrayToWorksheet {
    
        public static void main(String[] args) {
    
            //Create a Workbook instance
            Workbook workbook = new Workbook();
    
            //Get the first worksheet
            Worksheet worksheet = workbook.getWorksheets().get(0);
    
            //Create a one-dimensional array
            String[] oneDimensionalArray = new String[]{"January", "February", "March", "April","May", "June"};
    
            //Write the array to the first row of the worksheet
            worksheet.insertArray(oneDimensionalArray, 1, 1, false);
    
            //Create a two-dimensional array
            String[][] twoDimensionalArray = new String[][]{
                    {"Name", "Age", "Sex", "Dept.", "Tel."},
                    {"John", "25", "Male", "Development","654214"},
                    {"Albert", "24", "Male", "Support","624847"},
                    {"Amy", "26", "Female", "Sales","624758"}
            };
    
            //Write the array to the worksheet starting from the cell A3
            worksheet.insertArray(twoDimensionalArray, 3, 1);
    
            //Auto fit column width in the located range
            worksheet.getAllocatedRange().autoFitColumns();
    
            //Apply a style to the first and the third row
            CellStyle style = workbook.getStyles().addStyle("newStyle");
            style.getFont().isBold(true);
            worksheet.getRange().get(1,1,1,6).setStyle(style);
            worksheet.getRange().get(3,1,3,6).setStyle(style);
    
            //Save to an Excel file
            workbook.saveToFile("output/WriteArrays.xlsx", ExcelVersion.Version2016);
        }
    }

Java: Write Data to Excel Worksheets

Write a DataTable to a Worksheet

To import data from a DataTable to a worksheet, use the Worksheet.insertDataTable() method. The following are the detailed steps.

  • Create a Workbook object.
  • Get the first worksheet using Workbook.getWorksheets().get() method.
  • Create a DataTable, and write the DataTable to the worksheet at the specified location using Worksheet.insertDataTable() method.
  • Save the workbook to an Excel file using Workbook.saveToFile() method.
  • Java
import com.spire.data.table.DataRow;
    import com.spire.data.table.DataTable;
    import com.spire.xls.*;
    
    public class WriteDataTableToWorksheet {
    
        public static void main(String[] args) throws Exception {
    
            //Create a Workbook instance
            Workbook workbook = new Workbook();
    
            //Get the first worksheet
            Worksheet worksheet = workbook.getWorksheets().get(0);
    
            //Create a DataTable object
            DataTable dataTable = new DataTable();
            dataTable.getColumns().add("SKU", Integer.class);
            dataTable.getColumns().add("NAME", String.class);
            dataTable.getColumns().add("PRICE", String.class);
    
            //Create rows and add data
            DataRow dr = dataTable.newRow();
            dr.setInt(0, 512900512);
            dr.setString(1,"Wireless Mouse M200");
            dr.setString(2,"$85");
            dataTable.getRows().add(dr);
            dr = dataTable.newRow();
            dr.setInt(0,512900637);
            dr.setString(1,"B100 Cored Mouse ");
            dr.setString(2,"$99");
            dataTable.getRows().add(dr);
            dr = dataTable.newRow();
            dr.setInt(0,512901829);
            dr.setString(1,"Gaming Mouse");
            dr.setString(2,"$125");
            dataTable.getRows().add(dr);
            dr = dataTable.newRow();
            dr.setInt(0,512900386);
            dr.setString(1,"ZM Optical Mouse");
            dr.setString(2,"$89");
            dataTable.getRows().add(dr);
    
            //Write datatable to the worksheet
            worksheet.insertDataTable(dataTable,true,1,1,true);
    
            //Auto fit column width in the located range
            worksheet.getAllocatedRange().autoFitColumns();
    
            //Apply a style to the first row
            CellStyle style = workbook.getStyles().addStyle("newStyle");
            style.getFont().isBold(true);
            worksheet.getRange().get(1,1,1,3).setStyle(style);
    
            //Save to an Excel file
            workbook.saveToFile("output/WriteDataTable.xlsx", ExcelVersion.Version2016);
        }
    }

Java: Write Data to Excel Worksheets

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.

See Also

Page 9 of 66