PowerPoint presentations often contain sensitive or confidential information — business reports, marketing plans, or academic research that you don’t want others to access or modify freely. Fortunately, PowerPoint provides several ways to secure your presentations with passwords or encryption.

In this guide, you’ll learn five effective ways to password protect PowerPoint files, from built-in encryption to automated protection using C#. We’ll also compare the methods so you can choose the right level of security for your needs.

Method Overview:

• Method 1. Encrypt the Presentation with a Password
• Method 2. Add an Open or Modify Password via Save Options
• Method 3. Protect PowerPoint with a ZIP Password
• Method 4. Convert to PDF with Password
• Method 5. Automate Password Protection via Code

Why Protect PowerPoint with a Password

While PowerPoint is widely used for creating and sharing presentations, few users realize how easy it is for unauthorized people to open, edit, or copy content if the file isn’t protected. Password protection helps you control who can view, edit, or reuse your work.

Reasons to secure your PowerPoint files with a password:

• Confidentiality: Prevent unauthorized users from viewing sensitive data such as internal financial reports or strategic plans.
• Integrity: Stop others from modifying your slides or altering content without permission.
• Professionalism: Ensure that only finalized versions of presentations are distributed.
• Compliance: Some organizations or clients require password protection to meet data security policies.

PowerPoint protection levels:

• Open protection: Requires a password to open the file.
• Modify protection: Allows viewing but prevents editing without a password.

Now let’s explore five practical ways to apply these protections.

Method 1. Encrypt the Presentation with a Password

The simplest and most secure way to protect your PowerPoint file is by encrypting it with a password using PowerPoint’s built-in Encrypt with Password feature. This method ensures that no one can open the file without entering the correct password.

How to do it:

Step 1: Open your PowerPoint file.

Step 2: Click File → Info → Protect Presentation → Encrypt with Password .

Step 3: Enter a strong password combining uppercase and lowercase letters, numbers, and special characters.

Step 4: Click OK , then save your presentation to apply the password protection.

Next time anyone tries to open the presentation, PowerPoint will prompt for the password before loading any content.

Tips:

• Use a password that’s at least 8–12 characters long.
• Avoid saving the password in the same folder as your presentation.
• If you lose the password, Microsoft cannot recover it.

Method 2. Add an Open or Modify Password via Save Options

PowerPoint’s Save Options feature allows you to apply both open and modify passwords directly while saving your presentation. This built-in protection lets you decide whether others can view the file, edit it, or both.

Step-by-step instructions:

Step 1: Click File → Save As and select your desired location.

Step 2: In the Save As dialog, click Tools → General Options (Windows) or Options (Mac) .

Step 3: Enter a password in the Password to open field to restrict access entirely, or in Password to modify to allow viewing but prevent editing.

Step 4: Click OK , then save the file to apply the protection.

Tips:

• Use a strong, unique password for each presentation.
• Clearly differentiate between open and modify passwords to avoid confusion.

Method 3. Protect PowerPoint with a ZIP Password

If you frequently share presentations via email or cloud storage, compressing and encrypting them in a ZIP file adds an extra layer of protection. This method secures the file even before it’s opened in PowerPoint.

Steps (using 7-Zip or WinRAR):

Step 1: Select one or more PowerPoint files on your computer.

Step 2: Right-click on the selected files and select 7-Zip → Add to archive (or Compress on macOS).

Step 3: Choose the ZIP or RAR format.

Step 4: Enter a strong password, and enable AES-256 encryption if available.

Step 5: Save the archive and share it instead of the original file.

Tips:

• Keep the password separate from the file to prevent unauthorized access.
• For shared projects, create unique passwords per archive to minimize risk.

Method 4. Convert to PDF with Password

If you only need to share your PowerPoint slides in read-only form, converting them to a PDF with password protection is a practical choice. Recipients can view the slides but cannot edit or copy them without permission.

Follow these steps:

Step 1: Open your PowerPoint file and go to File → Export → Create PDF/XPS Document → Create PDF/XPS .

Step 2: Select export options and click Publish to generate the PDF.

Step 3: Open the PDF in Adobe Acrobat or another PDF editor.

Step 4: Select Tools , then click Protect .

Step 5: Click Protect Using Password to set a password to view or edit the PDF, and then click Apply .

Tips:

• Set both user (open) and owner (edit) passwords for stronger protection.
• If sharing widely, consider applying print and copy restrictions in your PDF tool.

If you frequently export presentations as encrypted PDFs, you can automate this process with code. Check out our tutorial: Convert PowerPoint to Password-Protected PDF in C#

Method 5. Automate Password Protection via Code

For organizations that generate presentations automatically — such as reports, dashboards, or client proposals — manual password setting isn’t scalable. Instead, you can automate PowerPoint encryption using C# .

With the Spire.Presentation for .NET library, you can apply password protection to one or multiple files programmatically.

Step-by-step instructions:

Step 1: Install Spire.Presentation via NuGet.

PM> Install-Package Spire.Presentation

Step 2: Load your PowerPoint file in C#.

Step 3: Apply password protection using the Encrypt() method.

Step 4: Save the encrypted presentation.

Full code example:

using Spire.Presentation;

namespace ProtectPPTWithPassword
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a new Presentation instance
            Presentation presentation = new Presentation();

            // Load the PowerPoint document from file
            presentation.LoadFromFile("sample.pptx");

            // Encrypt the presentation using a specified password
            presentation.Encrypt("your password");

            // Save the encrypted presentation to a new file in PPTX format
            presentation.SaveToFile("Encrypted.pptx", FileFormat.Pptx2013);
            presentation.Dispose();
        }
    }
}

Benefits:

Automating password protection with C# ensures consistency and accuracy, reduces the risk of human error, and allows batch encryption of multiple presentations. It also integrates easily with other automated tasks, streamlining your workflow and saving time.

Read further: Protect or Unprotect PowerPoint Presentations Using C#

Comparison Table: Choose the Right Way to Protect Your Presentation

Summary

Password protection is one of the most effective ways to safeguard PowerPoint presentations against unauthorized access or modification. Whether you need to encrypt confidential slides, control editing permissions, or distribute a secure read-only version, PowerPoint provides flexible options for every situation.

The built-in encryption feature offers strong, immediate protection; the Save As password options let you decide who can open or edit the file; third-party encryption tools add an extra layer of security for shared archives; PDF conversion ensures your audience can only view the content; and automation with C# simplifies protection for large-scale or repetitive tasks.

By choosing the right password protection method for your workflow, you can keep your presentations private, professional, and secure at every stage of use.

FAQs

Q1. Can I remove a password from a PowerPoint file?

Yes. Open the presentation using the correct password, go to File → Info → Protect Presentation → Encrypt with Password , and clear the password field, then save the file.

Q2. What happens if I forget my PowerPoint password?

Microsoft doesn’t offer a recovery method for encrypted presentations. You must re-create the file or restore a backup. Always store passwords securely in a password manager.

Q3. Is PowerPoint password protection secure?

Yes. Modern PowerPoint versions (2010 and later) use AES 128-bit encryption, which provides strong security when a complex password is used.

Q4. Can I use VBA or macros to set a PowerPoint password?

Yes, you can use VBA to set or remove passwords automatically, but C# libraries like Spire.Presentation offer more flexible and modern options.

Q5. What’s the difference between open and modify passwords?

An open password prevents anyone from opening the file without it, while a modify password allows opening but restricts editing.

See Also

• Remove Encryption on Password-Protected PowerPoint File in C#
• Add or Remove Digital Signatures in PowerPoint in C#
• How to Change PowerPoint Slide Backgrounds — 5 Methods
• 5 Easy Ways to Export PPT Slides as Images (PNG, JPG, TIFF)

PowerPoint is widely used for delivering ideas, reports, and marketing content. A slide’s background does more than decorate — it sets the tone, improves readability, and reinforces branding. Choosing the right background can make any presentation more professional and visually appealing.

Slide backgrounds can be customized manually using built-in tools like solid colors, gradients, patterns, or images. For advanced users, automation with VBA or Python (Spire.Presentation) helps save time on multiple slides or presentations. This article covers five practical methods for changing slide backgrounds, from manual edits to programmatic solutions.

Method Overview

• Method 1. Change Background Using “Format Background”
• Method 2. Use a Custom Background Image
• Method 3. Apply Background to All Slides via Slide Master
• Method 4. Change Background with VBA Macro
• Method 5. Change Background Programmatically with Python

Method 1. Change Background Using “Format Background”

One of the easiest ways to change a slide’s background is through PowerPoint’s Format Background feature. It offers several fill options — Solid, Gradient, Picture or Texture, and Pattern — all accessible from a single pane.

How to do it:

1. Open your PowerPoint presentation.
2. Select the slide you want to modify.
3. Go to the Design tab in the ribbon.



4. Click Format Background on the far right.



5. Choose your preferred fill type:
   • Solid Fill: Pick a single color for a clean and professional look.
   • Gradient Fill: Blend two or more colors smoothly for a dynamic effect.
   • Picture or Texture Fill: Apply an image or one of PowerPoint’s built-in textures.
   • Pattern Fill: Use repeating designs like stripes or dots for subtle backgrounds.
6. Click Apply to All to use the same background on every slide, or leave it unchecked to apply only to the current slide.

Tips:

• Choose colors that contrast well with your text and visuals. For readability, use light backgrounds with dark text or vice versa.
• Use the Format Painter to copy a background style from one slide to another quickly.

When to use it:

This method is ideal for most users who want a consistent, professional background without any effort. It’s quick, intuitive, and integrates seamlessly with PowerPoint’s built-in themes.

Method 2. Use a Custom Background Image

While Format Background supports picture fills, manually inserting an image gives you extra creative control. Use this approach when you need to position, crop, layer, or partially cover the slide with visuals — for example, placing a hero image, offsetting a photo behind text, or composing multiple images and shapes.

Quick guide:

1. Go to Insert → Pictures → This Device (or online source).



2. Choose and insert your image.
3. Resize or crop it to fit your design.
4. Right-click the image → Send to Back so that text and objects appear on top.



5. Adjust transparency if needed to prevent the background from overwhelming the slide content.

Tips:

• Use this method for standout slides, such as cover pages or transitions, where you want a distinct image separate from the rest of the deck.
• Keep file sizes small — compress large images to prevent slow presentation loading.

When to use it:

Choose this approach when you need precise placement, a unique composition, or layered visual effects beyond what Format Background provides.

Method 3. Apply Background to All Slides via Slide Master

If you want to apply a background consistently across every slide — including future ones you’ll add — the Slide Master is the best solution. This method ensures your presentation maintains uniform branding and saves time when editing large decks.

Step-by-step:

1. Go to View → Slide Master .



2. Select the topmost master slide in the left panel.
3. Click Background Styles → Format Background and choose your desired background (color, gradient, picture, or pattern).



4. Close the Slide Master view to return to the normal slide editor.

Tips:

• Create multiple Slide Master layouts, each with a different background, to visually separate presentation sections.
• Lock key elements (logos, background shapes) in the master to prevent accidental edits.

When to use it:

Use the Slide Master when creating templates, corporate presentations, or educational decks that require a consistent background across all slides and layouts.

Method 4. Change Background with VBA Macro

For power users comfortable with scripting, VBA (Visual Basic for Applications) provides a way to automate background changes directly within PowerPoint. It’s useful when you need to update multiple slides or presentations quickly with the same settings.

Example VBA script:

Sub SetSlideBackgroundColor()
    Dim sld As Slide
    For Each sld In ActivePresentation.Slides
        sld.FollowMasterBackground = False
        sld.Background.Fill.ForeColor.RGB = RGB(100, 149, 237) 'Sky blue
    Next sld
End Sub

Here's how to do it:

1. Press Alt + F11 to open the VBA editor in PowerPoint.
2. Right-click on the project name, and select Insert → Module .



3. Copy and paste the provided VBA code into the module.
4. Press F5 to execute the macro, which will change the background of all slides.

When to use it:

VBA is best for users who already work inside PowerPoint and need fast, repeatable automation — for example, corporate template updates or bulk formatting tasks.

Method 5. Change Background Programmatically with Python

For developers or analysts who want to generate presentations dynamically, Spire.Presentation for Python provides a powerful way to manipulate PowerPoint files, including changing slide backgrounds, without opening PowerPoint.

Installation:

pip install spire.presentation

Here is an example of how to apply a solid background color to all slides, using Spire.Presentation for Python:

from spire.presentation import *

# Create a Presentation object
presentation = Presentation()
# Load the PowerPoint file
presentation.LoadFromFile("Input.pptx")

# Loop through all slides
for slide in presentation.Slides:
    # Access and customize background
    background = slide.SlideBackground
    background.Type = BackgroundType.Custom
    background.Fill.FillType = FillFormatType.Solid
    background.Fill.SolidColor.Color = Color.get_LightYellow()

# Save the modified presentation
presentation.SaveToFile("AllSlidesBackground.pptx", FileFormat.Pptx2013)
presentation.Dispose()

Core steps explained:

1. Loads an existing PowerPoint file.
2. Iterates through all slides.
3. Sets a custom solid color as the background.
4. Saves the modified presentation.

Read Further: Set Background Color or Picture for PowerPoint Slides in Python

Output:

Why use Spire.Presentation:

• Works across platforms (Windows, macOS, Linux).
• Allows bulk automation (apply backgrounds to hundreds of slides).
• Can integrate with data sources (Excel, XML, databases) for dynamic slide creation.

Comparison Table: Which Method Should You Choose?

Summary

Changing the background of a PowerPoint slide can be simple or highly automated, depending on your needs. For everyday users , PowerPoint’s Format Background , custom images , and Slide Master provide flexible options for both single slides and entire presentations. For advanced users or developers , automation via VBA or Python (Spire.Presentation) makes it easy to apply backgrounds programmatically, saving time and ensuring consistency across large or multiple presentations.

By understanding these methods, you can choose the right approach for your workflow — whether it’s a quick manual tweak for one slide, a branded corporate template, or fully automated background updates for hundreds of slides.

FAQs About Changing Background of Slides

Q1. How can I reset the background of a slide?

Use Format Background → Reset Background to revert the slide to the theme’s default style.

Q2. Can I apply different backgrounds to different slides?

Yes, either manually via Format Background or by creating multiple layouts in the Slide Master.

Q3. What image resolution is best for slide backgrounds?

Use at least 1920×1080 pixels for full HD slides. Higher resolution is recommended for large screens or projectors.

Q4. Can I use Python to apply gradient or picture backgrounds?

Yes, Spire.Presentation for Python supports solid colors, gradients, and picture fills programmatically.

Q5. Is VBA safer than Python for automation?

VBA runs inside PowerPoint and is easier for non-developers but only works on Windows. Python is cross-platform and more flexible for large-scale automation.

See Also

• Remove Backgrounds from PowerPoint Slide or Slide Masters in Python
• How to Create PowerPoint Documents in Python
• 5 Easy Ways to Export PPT Slides as Images (PNG, JPG, TIFF)
• Convert PowerPoint to HTML: Simple Methods for Every User

In many business or academic settings, a Word document is more than just text — it often serves as a hub for related materials like reports, spreadsheets, charts, or reference PDFs. Instead of sending multiple files separately, attach them directly to your Word document to keep everything organized in one place.

This is particularly useful when preparing project reports, proposals, or technical documentation that requires supporting data. In this guide, you’ll learn five practical ways to attach a file to a Word document, ranging from simple manual approaches to automated methods using Python.

Method Overview:

• Method 1: Insert a File as an Embedded Object
• Method 2: Insert a File as a Linked Object
• Method 3: Add a File as a Hyperlink
• Method 4: Drag and Drop to Attach a File Quickly
• Method 5: Attach Files Programmatically with Python

Method 1: Insert a File as an Embedded Object

If you want the attached file to become a permanent and self-contained part of your Word document, embedding it as an object is the most reliable approach. This method stores the entire file inside the Word document itself, allowing you to share one single file that includes all related materials. It’s ideal when you need the document to remain fully functional even without access to external resources.

Steps:

1. Open your Word document.
2. Go to the Insert tab.
3. Click Object in the Text group.



4. Select Create from File .



5. Click Browse , select the file, and confirm.



6. (Optional) Check Display as icon for a tidy layout.
7. Click OK.

Your chosen file (e.g., PDF, Excel, or image) now becomes part of the document. You can double-click the inserted icon to open it at any time.

Pros:

• The attached file travels with the document.
• Works completely offline.

Cons:

• Increases file size.
• Doesn’t update automatically if the source changes.

Best for: finalized reports, archives, and official submissions.

Method 2: Insert a File as a Linked Object

When you want your Word document to stay lightweight while reflecting updates from an external source, inserting the file as a linked object is the smarter choice. Instead of embedding a copy, Word maintains a live link to the original file — so any changes made to the source are automatically reflected in your document. This is perfect for dynamic data, such as financial sheets or charts that evolve over time.

Steps:

1. Follow the same steps as in Method 1.
2. Before confirming, check Link to file .



3. Click OK .

You’ll see a file icon or preview in your Word document, but the content is stored externally.

Pros:

• Keeps your Word document lightweight.
• Automatically updates when the original file changes.
• Works offline if the linked file is stored locally.

Cons:

• The link breaks if the source file is moved, renamed, or deleted.
• Requires access to the linked file’s path or drive (e.g., local or network).

Best for: collaborative projects or documents that rely on frequently updated data.

Method 3: Add a File as a Hyperlink

If you only need to reference a file rather than include its contents, adding a hyperlink offers a clean and efficient solution. This method simply points readers to the file’s location — whether on your local drive, a shared server, or a cloud service like OneDrive or SharePoint — without embedding or linking the actual file. It’s lightweight, quick, and ideal for distributed teams.

Steps:

1. Highlight the text or image where you want to add the link.
2. Right-click on the selected text or image, and choose Link from the prompted menu.



3. Select your file or paste the full path/URL.



4. Click OK .

When clicked, the link opens the file directly in its associated program.

Pros:

• Doesn’t increase document size.
• Easy to edit or update the link target.
• Works offline if linked to a local file path.

Cons:

• Requires access to the file or network location.
• Cloud-based links (e.g., OneDrive or SharePoint) need an internet connection.

Best for: online collaboration, shared drives, or linking to local resources.

Method 4: Drag and Drop to Attach a File Quickly

When you need to attach a file on the fly without going through menus, the drag-and-drop method is the fastest and most intuitive approach. Simply drag a file from your desktop or file explorer directly into your open Word window — and Word will handle the rest.

Depending on the file type:

• Images are inserted inline automatically.
• Other file types (e.g., PDFs, Excel sheets) are displayed as icons that open with a double-click.

This method works best for quick edits or informal documents where convenience is key.

Pros:

• Incredibly fast and easy.
• Works for most common file formats.

Cons:

• Larger resulting file size.
• Less control over how the attachment appears or behaves.

Best for: quick one-time inserts or when you need to drop in a few files during editing.

Method 5: Attach Files Programmatically with Python

For advanced users and developers, automating file attachments using Python can save time and ensure consistency — especially when generating large numbers of documents or assembling reports programmatically. By using Spire.Doc for Python, you can embed external files as OLE objects directly into Word without manual steps.

This is ideal for automated workflows, such as generating weekly reports, attaching PDFs or charts, or bundling supporting documents dynamically.

Here’s an example of how to embed a PDF file into a Word document:

from spire.doc import *
from spire.doc.common import *

# Create a Document object
doc = Document()

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

# Get the first section
section = doc.Sections.get_Item(0)

# Add a paragraph to the section
para = section.AddParagraph()

# Load an image which will be used as the icon of the OLE object
picture = DocPicture(doc)
picture.LoadImage("C:\\Users\\Administrator\\Desktop\\PDF-Icon.png")
picture.Width = 60
picture.Height = 60

# Append an OLE object (a PDF file) to the paragraph
object = para.AppendOleObject("C:\\Users\\Administrator\\Desktop\\report.pdf", picture, OleObjectType.AdobeAcrobatDocument)

# Display the object as icon
object.DisplayAsIcon = True

# Add a caption under the icon
object.OlePicture.AddCaption("Attachment", CaptionNumberingFormat.Number, CaptionPosition.BelowItem)
doc.IsUpdateFields = True

# Save the document
doc.SaveToFile("AttachFile.docx", FileFormat.Docx2013)
doc.Close()

You can replace the file path and OLE object type for other formats such as Excel sheets, images, or Word documents. Refer to this guide for detailed instructions on inserting or extracting OLE objects in Word using Python.

Output:

In addition to embedding files in a Word document, Spire.Doc allows you to attach files as linked objects using the AppendOleObject(String, DocPicture, OleLinkType) method, or create hyperlinks from text or images that redirect to external files.

Why use automation?

• Perfect for batch processing or report generation.
• Integrates smoothly with data-driven systems.
• Ensures repeatable, error-free results.

Best for: professionals or teams that manage document generation at scale.

Comparison Table: Which Method Should You Use?

Conclusion

Attaching files to a Word document is a simple yet powerful way to keep related materials organized and accessible. The method you choose depends on your needs: embedding a file in Word ensures everything stays in one document, linking files in Word keeps documents lightweight and automatically updated, and adding hyperlinks makes sharing online resources effortless.

For quick edits, drag-and-drop attachments offer speed and convenience, while automating file attachments with Python provides efficiency at scale. Whether you’re compiling a single report or managing automated document generation, selecting the right way to attach files to Word helps you balance portability, accessibility, and performance for a more streamlined workflow.

FAQs

Q1. What types of files can I attach to a Word document?

You can attach almost any file type, including PDFs, Excel spreadsheets, images, and even other Word files. Some file types may require specific software to open.

Q2. Can I attach multiple files at once?

Yes. You can embed or link multiple files in one document. For bulk operations, using automation (like Python) is more efficient.

Q3. Why can’t I open the attached file in Word?

If the attachment was linked rather than embedded, Word needs access to the original file’s location. Check if it has been moved or renamed.

Q4. How to attach a PDF file inside a Word document?

Go to Insert → Object → Create from File → Browse , select your PDF, and click OK . Check Display as icon if you want it shown as an icon instead of inline content.

Q5. How to attach Excel file in Word document?

In Word, choose Insert → Object → Create from File → Browse , select the Excel file, and click OK . To keep it updated automatically, check Link to file before inserting.

See Also

• Integrate Excel Tables into Word Files in Python
• Insert Images to Word Documents in Python
• Add or Remove Shapes in Word in Python
• Attach Files to a PDF Document in Python

Excel is one of the most powerful tools for organizing and analyzing structured data — from financial models to department reports. However, as a workbook grows to include multiple worksheets for different teams or purposes, it can become difficult to manage, share, or extract specific information. In such cases, it’s often more practical to split a workbook by worksheets , turning each sheet into its own file for easier distribution and collaboration.

For example, you might want to send the Sales worksheet to the sales team, the HR worksheet to the HR department, and the Finance worksheet to your accountant. The simplest way to achieve this is to split Excel sheets into separate files , so each recipient receives only the data relevant to them. In this article, we’ll explore three effective methods: a quick manual process, a VBA solution for Excel users, and a Python-based approach ideal for automation.

Methods Overview:

• Quick Manual Trick: Copy Sheets into New Workbooks
• Automate in Excel: VBA Macro to Split Sheets
• Automate with Python: Save Each Worksheet as a File

Why Split Excel Sheets into Separate Files?

There are several practical reasons why splitting a workbook into multiple files is helpful:

1. Selective Sharing : Not every stakeholder needs access to all the data. Splitting sheets lets you distribute relevant files only.
2. Improved Performance : Large workbooks with many sheets can become slow to open and process. Splitting them into smaller files improves performance.
3. Better Organization : Separate files can make project management and reporting more structured.
4. Automation and Reporting : Splitting is often part of automated workflows where different reports are generated for different departments.
5. Version Control : Smaller files are easier to track and maintain in version control systems compared to one giant workbook.

Whether you’re an everyday Excel user or a developer building automated reporting pipelines, splitting sheets is a task worth mastering.

Quick Manual Trick: Copy Sheets into New Workbooks

If you only need to split a few sheets and don’t mind a bit of clicking, Excel’s built-in interface provides a straightforward way to do this.

How it works:

1. Open your workbook using MS Excel.
2. Right-click on the sheet tab you want to separate and select Move or Copy... .



3. In the To book: dropdown, select (new book) . Check the Create a copy box, then click OK .



4. Repeat this process for every sheet you want to split into an individual file.

Pros:

• Requires no coding skills.
• Built directly into Excel — no installation needed.
• Simple and reliable for one-time tasks.

Cons:

• Time-consuming if you need to split many sheets.
• Prone to errors (forgetting to save or rename files properly).
• No automation — you must repeat the steps manually every time.

Best for:

• Users who rarely need to split sheets.
• Quick, one-off tasks where only a couple of sheets need separation.

Automate in Excel: VBA Macro to Split Sheets

For more frequent use, Excel’s built-in VBA (Visual Basic for Applications) editor provides a way to automate splitting. With a small macro, Excel can loop through every worksheet and save it as a new workbook — saving hours of manual work.

How it works:

1. Open Excel and press Alt + F11 to open the VBA editor.
2. Go to Insert > Module .



3. Paste the following code into the module window:

Sub SplitSheetsIntoWorkbooks()
    Dim ws As Worksheet
    Dim newWorkbook As Workbook
    Dim originalWorkbook As Workbook

    Set originalWorkbook = ThisWorkbook
    Application.ScreenUpdating = False

    For Each ws In originalWorkbook.Worksheets
        ws.Copy
        Set newWorkbook = ActiveWorkbook
        newWorkbook.SaveAs Filename:=originalWorkbook.Path & "\" & ws.Name & ".xlsx"
        newWorkbook.Close SaveChanges:=False
Next ws

MsgBox "All sheets have been saved as separate files!"
End Sub

4. Press F5 (or go to Run > Run Sub/UserForm) to execute the macro.
5. Excel will create separate files for each worksheet in the same folder as your original workbook.

Pros:

• Fully automated— one click and every sheet is exported.
• Built into Excel — no extra software needed.
• Saves significant time compared to the manual approach.

Cons:

• Requires enabling macros, which some organizations restrict for security reasons.Ÿ
• VBA is somewhat outdated, and debugging errors can be frustrating for beginners.
• Limited flexibility (e.g., handling very large workbooks or custom export rules requires editing the macro).

Best for:

• Intermediate to advanced Excel users.
• Scenarios where you frequently need to split sheets in workbooks.

Automate with Python: Save Each Worksheet as a File

If you’re a developer or need maximum flexibility, Python provides a modern approach. Using libraries like Spire.XLS for Python, you can process Excel files programmatically and split sheets in bulk. This is ideal for workflows involving large files, multiple workbooks, or integration with other systems.

How it works:

1. Install Python (if you don’t already have it).
2. Install the Spire.XLS for Python library:

pip install spire.xls

3. Use a script like the following:

from spire.xls import *
from spire.xls.common import *

# Create an object of the Workbook class
workbook = Workbook()

# Load an Excel file
workbook.LoadFromFile("Sample.xlsx")

# Specify the folder path for the generated Excel files
folderPath = "C:\\Users\\Administrator\\Desktop\\Output\\"

# Iterate through all worksheets in the Excel file
for worksheet in workbook.Worksheets:

    # For each worksheet, create a new Workbook object
    newWorkbook = Workbook()
    # Remove the worksheets from the new workbook
    newWorkbook.Worksheets.Clear()

    # Copy the worksheet from the Excel file to the new workbook
    newWorkbook.Worksheets.AddCopy(worksheet)

    # Save the new workbook to the specified folder
    newWorkbook.SaveToFile(folderPath + worksheet.Name + ".xlsx", FileFormat.Version2016)

workbook.Dispose()

Here is the full guide on how to split Excel by sheets, rows, and columns in Python.

Output:

Pros:

• Highly flexible — you can extend the script to filter sheets, split by row/column, or export to CSV/PDF.
• Perfect for batch processing and large-scale automation.
• Integrates with other systems and workflows.

Cons:

• Requires some coding knowledge.- Initial setup (Python + libraries) takes longer than VBA.

Best for:

• Developers automating data pipelines.Ÿ
• Businesses with large, repetitive splitting tasks.
• Advanced users who need more control than VBA offers.

Summary: Which Method Should You Choose?

Splitting Excel sheets into individual files is a common challenge, but the right method depends on your context:

• Quick Manual Trick : Perfect if you only need to separate a couple of sheets once in a while. It’s easy and doesn’t require any coding.
• VBA Macro : The go-to method for power Excel users. Once set up, it can save hours of manual work, especially if you frequently split workbooks.
• Python Script : The best option for developers or anyone building automated workflows. It provides full control, scalability, and the ability to extend the solution to fit complex business needs.

At the end of the day, the method you choose comes down to how often you need to split sheets and how comfortable you are with automation . Occasional users can rely on Excel’s interface, while professionals benefit more from VBA or Python automation.

See Also

• Split Excel Sheets into Separate Files in C#
• Merge Excel Files in Python - No Excel Required
• Python-Conversion Between Excel and CSV
• Convert Excel (XLS & XLSX) to PDF in Python

Чтение файлов Excel на C# — это обычное требование для многих приложений, будь то для анализа данных, создания отчетов или интеграции с базами данных. Хотя можно использовать библиотеки Microsoft Interop, у них есть ограничения (например, требование установленного Excel). Вместо этого мы рассмотрим более эффективный подход с использованием Spire.XLS, библиотеки .NET, которая позволяет читать и записывать файлы Excel без Interop. В этой статье рассматриваются:

• Библиотека C# .NET для чтения Excel без Interop
• Как прочитать файл Excel на C#
• Чтение данных Excel в DataTable
• Чтение данных Excel в базу данных
• Заключение
• Часто задаваемые вопросы

Библиотека C# .NET для чтения Excel без Interop

Excel Interop от Microsoft требует, чтобы Excel был установлен на компьютере, что делает его непригодным для серверных приложений. Вместо этого библиотеки, такие как Spire.XLS, предлагают легкое, высокопроизводительное решение без зависимостей от Excel.

Зачем использовать Spire.XLS?

• Не требуется установка Excel – работает независимо.
• Поддерживает .NET Core и .NET Framework – кроссплатформенная совместимость.
• Чтение/запись файлов Excel – поддерживает .xls, .xlsx и .xlsm.
• Импорт в DataTable и базы данных – бесшовная интеграция с ADO.NET.

Установка Spire.XLS

Для начала установите библиотеку через менеджер пакетов NuGet:

Install-Package Spire.XLS

Кроме того, вы можете скачать Spire.XLS для .NET с нашего официального сайта и вручную сослаться на DLL-файл.

Как прочитать файл Excel на C#

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

Шаг 1. Импортируйте необходимое пространство имен

Чтобы использовать функциональность Spire.XLS, вам необходимо импортировать его пространство имен. Это дает доступ к таким классам, как Workbook и Worksheet, которые необходимы для операций с файлами Excel.

using Spire.Xls;

Шаг 2. Загрузите файл Excel

Чтобы загрузить файл Excel, создайте объект Workbook и вызовите метод LoadFromFile. Это считывает файл в память, позволяя дальнейшие манипуляции.

Workbook wb = new Workbook();
wb.LoadFromFile("input.xlsx");

Шаг 3. Получите определенный рабочий лист

Файлы Excel могут содержать несколько рабочих листов. Вы можете получить доступ к определенному листу, индексируя коллекцию Worksheets (начиная с нуля). Первый лист находится по индексу 0, второй — по индексу 1 и так далее.

Worksheet sheet = wb.Worksheets[0]; //Первый лист

Шаг 4. Получение значения определенной ячейки

Чтобы получить значение ячейки, используйте свойство CellRange.Value. Укажите индексы строки и столбца (начиная с 1), чтобы найти ячейку. Это полезно для извлечения структурированных данных, таких как заголовки или отдельные записи.

CellRange cell = sheet.Range[1, 1]; // Строка1, Столбец 1 (A1)
string value = cell.Value;

Ниже приведен полный пример чтения данных со всего рабочего листа и вывода их в консоль:

using Spire.Xls;

namespace ReadExcelData
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a Workbook object
            Workbook wb = new Workbook();

            // Load an existing Excel file
            wb.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Input.xlsx");

            // Get the first worksheet
            Worksheet sheet = wb.Worksheets[0];

            // Get the cell range containing data
            CellRange locatedRange = sheet.AllocatedRange;

            // Iterate through the rows
            for (int i = 0; i < locatedRange.Rows.Length; i++)
            {
                // Iterate through the columns
                for (int j = 0; j < locatedRange.Rows[i].ColumnCount; j++)
                {
                    // Get data of a specific cell
                    string cellValue = locatedRange[i + 1, j + 1].Value?.ToString() ?? "N/A";

                    // Align output with a width of 22
                    Console.Write($"{cellValue,-22}");
                }
                Console.WriteLine();
            }
        }
    }
}

Результат:

Чтение данных Excel в DataTable

Экспорт данных Excel в DataTable обеспечивает бесшовную интеграцию с элементами управления пользовательского интерфейса, такими как DataGridView, или обработку данных на стороне сервера. Spire.XLS упрощает этот процесс с помощью встроенного метода ExportDataTable(), который автоматически преобразует данные рабочего листа в структурированный DataTable, сохраняя заголовки столбцов и типы данных.

Шаг 1. Импортируйте необходимое пространство имен

Включите пространство имен Spire.XLS для доступа к основным классам.

using Spire.Xls;

Шаг 2. Создайте форму и событие нажатия кнопки

Создайте форму (например, Form1) и добавьте кнопку с обработчиком событий для чтения файла Excel.

public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }

    private void button1_Click(object sender, EventArgs e)
    {
        // Код будет здесь
    }
}

Шаг 3. Загрузите рабочую книгу

В обработчике события нажатия кнопки создайте объект Workbook и загрузите файл Excel.

Workbook wb = new Workbook();
wb.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Input.xlsx");

Шаг 4. Экспорт данных в DataTable

Получите доступ к определенному рабочему листу по его индексу и экспортируйте его данные в DataTable с помощью метода ExportDataTable.

DataTable dataTable = wb.Worksheets[0].ExportDataTable();

Шаг 5. Привязка данных к DataGridView

Предполагая, что у вас есть элемент управления DataGridView на вашей форме, привяжите DataTable к DataGridView для отображения данных.

dataGridView1.DataSource = dataTable;

Ниже приведен полный код для чтения данных из файла Excel в DataTable и отображения их в элементе управления DataGridView в Windows Forms:

using Spire.Xls;
using System.Data;

namespace ReadExcelIntoDataTable
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            // Create a Workbook object
            Workbook wb = new Workbook();

            // Load an existing Excel file
            wb.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Input.xlsx");

            // Get the first worksheet
            Worksheet sheet = wb.Worksheets[0];

            // Export data from worksheet into a DataTable
            DataTable dataTable = sheet.ExportDataTable();

            // Bind DataTable to DataGridView
            dataGridView1.DataSource = dataTable;

            // Dispose resources
            wb.Dispose();
        }
    }
}

Результат:

Чтение данных Excel в базу данных

Интеграция данных Excel с базой данных может упростить управление данными. Ниже мы рассмотрим процесс чтения файла Excel и импорта его содержимого в базу данных MySQL. Этот метод идеально подходит для автоматизации миграции данных, создания отчетов или синхронизации данных Excel со структурированной базой данных.

Шаг 1. Установите библиотеку данных MySQL

Для взаимодействия с базами данных MySQL в ваших .NET-приложениях вам необходимо установить библиотеку MySql.Data. Этот пакет NuGet предоставляет необходимые классы и методы для подключения к базам данных MySQL и управления ими.

Install-Package MySql.Data

Шаг 2. Импортируйте необходимые пространства имен

Перед работой с файлами Excel и MySQL необходимо включить требуемые пространства имен. Spire.XLS используется для операций с Excel, а MySql.Data.MySqlClient обеспечивает подключение к базе данных MySQL.

using Spire.Xls;
using MySql.Data.MySqlClient;

Шаг 3. Извлечение заголовков и данных из Excel

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

// Create a Workbook object
Workbook wb = new Workbook();

// Load an Excel document
wb.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Input.xlsx");

// Get a specific sheet
Worksheet sheet = wb.Worksheets[0];

// Retrieve headers
List<string> headers = new List<string>();
for (int col = 1; col <= sheet.LastColumn; col++)
{
    string header = sheet.Range[1, col].Value?.ToString();
    // Removing spaces to avoid conflicts with MySQL column names
    string cleanHeader = header?.Replace(" ", "");
    headers.Add($"`{cleanHeader}`");
}

// Retrieve data
List<List<string>> data = new List<List<string>>();
for (int row = 2; row <= sheet.LastRow; row++) {
    List<string> record = new List<string>();
    for (int col = 1; col <= sheet.LastColumn; col++)
    {
        record.Add(sheet.Range[row, col].Value?.ToString() ?? string.Empty);
    }
    data.Add(record);
}

Шаг 4. Подключение к базе данных MySQL

Соединение с базой данных MySQL устанавливается с помощью строки подключения, которая включает сведения о сервере, учетные данные и имя целевой базы данных. Оператор using обеспечивает надлежащее освобождение ресурсов.

string connectionString = "server=localhost;user=root;password=yourpassword;database=yourdatabase;";
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
    connection.Open();
    // Connection is established; perform database operations here
}

Шаг 5. Динамическое создание таблицы в MySQL

На этом шаге динамически создается таблица MySQL со столбцами, соответствующими заголовкам Excel. для простоты все столбцы установлены как VARCHAR(255), но типы данных можно настроить в соответствии с требованиями.

// Create a table with dynamic columns based on headers
List<string> columns = new List<string>();
foreach (string header in headers)
{
    // Assuming all header values are VARCHAR for simplicity; adjust types as needed
    columns.Add($"{header} VARCHAR(255)");
}

// Create a table in database
string columnsSql = string.Join(", ", columns);
string createTableQuery = $ @"
    CREATE TABLE IF NOT EXISTS my_table (
        id INT AUTO_INCREMENT PRIMARY KEY,
        {columnsSql}
)";

// Execute the create table query
using (MySqlCommand createCommand = new MySqlCommand(createTableQuery, connection))
{
    createCommand.ExecuteNonQuery();
}

Шаг 6. Заполнение таблицы данными

Извлеченные данные Excel вставляются в таблицу MySQL с использованием параметризованных запросов для предотвращения SQL-инъекций. Каждая строка из файла Excel сопоставляется с соответствующей записью в базе данных.

// Prepare the SQL INSERT statement
string placeholders = string.Join(", ", new string[headers.Count].Select(h => "?"));
string insertQuery = $"INSERT INTO my_table ({string.Join(", ", headers.Select(h => h.Trim('`')))}) VALUES ({placeholders})";

// Insert data into the table
foreach (List<string> record in data)
{
    using (MySqlCommand insertCommand = new MySqlCommand(insertQuery, connection))
    {
        for (int i = 0; i < record.Count; i++)
        {
            insertCommand.Parameters.AddWithValue($" @2024\本地文件打包__20180302\Spire.Web\trunk\src\website\components\com_virtuemart\themes\default\templates\browse\includes\browse_searchparameter_form.tpl.php{i}", record[i]);
        }
        insertCommand.ExecuteNonQuery();
    }
}

Вот полный код для импорта данных из файла Excel в таблицу MySQL:

using Spire.Xls;
using MySql.Data.MySqlClient;

namespace ExcelToMySQL
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a Workbook object
            Workbook wb = new Workbook();

            // Load an Excel document
            wb.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Input.xlsx");

            // Get a specific sheet
            Worksheet sheet = wb.Worksheets[0];

            // Retrieve headers
            List<string> headers = new List<string>();
            for (int col = 1; col <= sheet.LastColumn; col++)
            {
                string header = sheet.Range[1, col].Value?.ToString();
                // Removing spaces to avoid conflicts with MySQL column names
                string cleanHeader = header?.Replace(" ", "");
                headers.Add($"`{cleanHeader}`");
            }

            // Retrieve data
            List<List<string>> data = new List<List<string>>();
            for (int row = 2; row <= sheet.LastRow; row++)
            {
                List<string> record = new List<string>();
                for (int col = 1; col <= sheet.LastColumn; col++)
                {
                    record.Add(sheet.Range[row, col].Value?.ToString() ?? string.Empty);
                }
                data.Add(record);
            }

            // Establish a connection to the MySQL database
            string connectionString = "server=localhost;user=root;password=admin;database=excel_db;";
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();

                // Create a table with dynamic columns based on headers
                List<string> columns = new List<string>();
                foreach (string header in headers)
                {
                    // Assuming all header values are VARCHAR for simplicity; adjust types as needed
                    columns.Add($"{header} VARCHAR(255)");
                }

                // Create a table in database
                string columnsSql = string.Join(", ", columns);
                string createTableQuery = $ @"
                    CREATE TABLE IF NOT EXISTS my_table (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        {columnsSql}
                    )";

                // Execute the create table query
                using (MySqlCommand createCommand = new MySqlCommand(createTableQuery, connection))
                {
                    createCommand.ExecuteNonQuery();
                }

                // Prepare the SQL INSERT statement
                string placeholders = string.Join(", ", new string[headers.Count].Select(h => "?"));
                string insertQuery = $"INSERT INTO my_table ({string.Join(", ", headers.Select(h => h.Trim('`')))}) VALUES ({placeholders})";

                // Insert data into the table
                foreach (List<string> record in data)
                {
                    using (MySqlCommand insertCommand = new MySqlCommand(insertQuery, connection))
                    {
                        for (int i = 0; i < record.Count; i++)
                        {
                            insertCommand.Parameters.AddWithValue($" @2024\本地文件打包__20180302\Spire.Web\trunk\src\website\components\com_virtuemart\themes\default\templates\browse\includes\browse_searchparameter_form.tpl.php{i}", record[i]);
                        }
                        insertCommand.ExecuteNonQuery();
                    }
                }
            }

            Console.WriteLine("Данные успешно экспортированы!");
        }
    }
}

Результат:

Заключение

Чтение файлов Excel на C# никогда не было таким простым благодаря таким библиотекам, как Spire.XLS. В этом руководстве вы ознакомились с процессом загрузки файлов Excel, чтения их содержимого и даже импорта данных в базу данных MySQL. С помощью этих методов вы можете значительно расширить возможности обработки данных в своих приложениях.

Часто задаваемые вопросы

В1: Могу ли я читать защищенные паролем файлы Excel?

О: Да, Spire.XLS поддерживает чтение зашифрованных файлов Excel с помощью:

wb.OpenPassword = "psd";
wb.LoadFromFile("file.xlsx");

В2: Как мне прочитать результаты формулы вместо самой формулы?

О: У вас есть два варианта получения результатов формулы:

Для отдельных ячеек:

Проверьте, содержит ли ячейка формулу, с помощью CellRange.HasFormula, и получите значение с помощью CellRange.FormulaValue:

CellRange cell = sheet.Range[1, 1];
if (cell.HasFormula)
{
    string result = cell.FormulaValue.ToString();
}

Для массового экспорта в DataTable:

Используйте Worksheet.ExportDataTable() с computedFormulaValue: true для экспорта вычисленных значений:

DataTable data = sheet.ExportDataTable(range, exportColumnNames: true, computedFormulaValue: true);

В3: Как я могу прочитать данные Excel в DataTable?

О: Используйте метод Worksheet.ExportDataTable(), предоставляемый Spire.XLS.

В4: Как я могу прочитать файл Excel построчно?

О: Обратитесь к следующему коду:

Workbook workbook = new Workbook();
workbook.LoadFromFile("input.xlsx");
Worksheet sheet = workbook.Worksheets[0];

for (int row = 1; row <= sheet.LastRow; row++)
{
    for (int col = 1; col <= sheet.LastColumn; col++)
    {
        string cellValue = sheet.Range[row, col].Value?.ToString() ?? string.Empty;
        Console.WriteLine(cellValue);
    }
}

Получить бесплатную лицензию

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

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

• Конвертировать Excel в CSV и CSV в Excel на C#
• Создание, чтение и обновление Excel на C#
• Извлечение изображений из Excel на C#
• Чтение свойств документа в Excel на C#

C#에서 Excel 파일을 읽는 것은 데이터 분석, 보고 또는 데이터베이스 통합 등 많은 애플리케이션의 일반적인 요구 사항입니다. Microsoft의 Interop 라이브러리를 사용할 수 있지만, Excel 설치가 필요하다는 등의 제한이 있습니다. 대신, Interop 없이 Excel 파일을 읽고 쓸 수 있는 .NET 라이브러리인 Spire.XLS를 사용하는 더 효율적인 접근 방식을 살펴보겠습니다. 이 문서에서는 다음 내용을 다룹니다.

• Interop 없이 Excel을 읽기 위한 C# .NET 라이브러리
• C#에서 Excel 파일을 읽는 방법
• Excel 데이터를 DataTable로 읽기
• Excel 데이터를 데이터베이스로 읽기
• 결론
• 자주 묻는 질문

Interop 없이 Excel을 읽기 위한 C# .NET 라이브러리

Microsoft의 Excel Interop은 컴퓨터에 Excel이 설치되어 있어야 하므로 서버 측 애플리케이션에는 적합하지 않습니다. 대신 Spire.XLS와 같은 라이브러리는 Excel에 대한 종속성 없이 가볍고 고성능의 솔루션을 제공합니다.

Spire.XLS를 사용하는 이유

• Excel 설치 필요 없음 – 독립적으로 작동합니다.
• .NET Core 및 .NET Framework 지원 – 플랫폼 간 호환성.
• Excel 파일 읽기/쓰기 – .xls, .xlsx 및 .xlsm을 지원합니다.
• DataTable 및 데이터베이스로 가져오기 – ADO.NET과의 원활한 통합.

Spire.XLS 설치

시작하려면 NuGet 패키지 관리자를 통해 라이브러리를 설치하십시오.

Install-Package Spire.XLS

또는 공식 웹사이트에서 .NET용 Spire.XLS를 다운로드하고 DLL 파일을 수동으로 참조할 수 있습니다.

C#에서 Excel 파일을 읽는 방법

이 섹션에서는 Spire.XLS 라이브러리를 사용하여 C#에서 Excel 파일을 읽는 방법을 보여줍니다. 이 프로세스에는 파일 로드, 워크시트 액세스 및 프로그래밍 방식으로 셀 값 검색이 포함됩니다. 이는 데이터 추출 자동화, Excel 보고서 처리 또는 스프레드시트 데이터를 애플리케이션에 통합하는 데 유용합니다.

1단계. 필요한 네임스페이스 가져오기

Spire.XLS 기능을 활용하려면 해당 네임스페이스를 가져와야 합니다. 이를 통해 Excel 파일 작업에 필수적인 Workbook 및 Worksheet와 같은 클래스에 액세스할 수 있습니다.

using Spire.Xls;

2단계. Excel 파일 로드

Excel 파일을 로드하려면 Workbook 개체를 만들고 LoadFromFile 메서드를 호출합니다. 이렇게 하면 파일이 메모리로 읽혀 추가 조작이 가능합니다.

Workbook wb = new Workbook();
wb.LoadFromFile("input.xlsx");

3단계. 특정 워크시트 가져오기

Excel 파일에는 여러 워크시트가 포함될 수 있습니다. Worksheets 컬렉션(0부터 시작)을 인덱싱하여 특정 시트에 액세스할 수 있습니다. 첫 번째 시트는 인덱스 0, 두 번째 시트는 1에 있는 식입니다.

Worksheet sheet = wb.Worksheets[0]; //First sheet

4단계. 특정 셀의 값 검색

셀 값을 검색하려면 CellRange.Value 속성을 사용합니다. 행 및 열 인덱스(1부터 시작)를 지정하여 셀을 찾습니다. 이는 헤더나 개별 레코드와 같은 구조화된 데이터를 추출하는 데 유용합니다.

CellRange cell = sheet.Range[1, 1]; // Row1, Column 1 (A1)
string value = cell.Value;

다음은 전체 워크시트에서 데이터를 읽어 콘솔에 인쇄하는 전체 예제입니다.

using Spire.Xls;

namespace ReadExcelData
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a Workbook object
            Workbook wb = new Workbook();

            // Load an existing Excel file
            wb.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Input.xlsx");

            // Get the first worksheet
            Worksheet sheet = wb.Worksheets[0];

            // Get the cell range containing data
            CellRange locatedRange = sheet.AllocatedRange;

            // Iterate through the rows
            for (int i = 0; i < locatedRange.Rows.Length; i++)
            {
                // Iterate through the columns
                for (int j = 0; j < locatedRange.Rows[i].ColumnCount; j++)
                {
                    // Get data of a specific cell
                    string cellValue = locatedRange[i + 1, j + 1].Value?.ToString() ?? "N/A";

                    // Align output with a width of 22
                    Console.Write($"{cellValue,-22}");
                }
                Console.WriteLine();
            }
        }
    }
}

결과:

Excel 데이터를 DataTable로 읽기

Excel 데이터를 DataTable로 내보내면 DataGridView와 같은 UI 컨트롤 또는 백엔드 데이터 처리와 원활하게 통합할 수 있습니다. Spire.XLS는 워크시트 데이터를 열 헤더와 데이터 유형을 유지하면서 구조화된 DataTable로 자동 변환하는 내장 ExportDataTable() 메서드를 사용하여 이 프로세스를 단순화합니다.

1단계. 필요한 네임스페이스 가져오기

필수 클래스에 액세스하려면 Spire.XLS 네임스페이스를 포함하십시오.

using Spire.Xls;

2단계. 양식 및 버튼 클릭 이벤트 만들기

양식(예: Form1)을 만들고 Excel 파일을 읽기 위한 이벤트 처리기가 있는 버튼을 추가합니다.

public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }

    private void button1_Click(object sender, EventArgs e)
    {
        // Code will go here
    }
}

3단계. 통합 문서 로드

버튼 클릭 이벤트 내에서 Workbook 개체를 만들고 Excel 파일을 로드합니다.

Workbook wb = new Workbook();
wb.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Input.xlsx");

4단계. 데이터를 DataTable로 내보내기

인덱스로 특정 워크시트에 액세스하고 ExportDataTable 메서드를 사용하여 해당 데이터를 DataTable로 내보냅니다.

DataTable dataTable = wb.Worksheets[0].ExportDataTable();

5단계. 데이터를 DataGridView에 바인딩

양식에 DataGridView 컨트롤이 있다고 가정하고 DataTable을 DataGridView에 바인딩하여 데이터를 표시합니다.

dataGridView1.DataSource = dataTable;

다음은 Excel 파일에서 데이터를 읽어 DataTable에 넣고 Windows Forms DataGridView 컨트롤에 표시하는 전체 코드입니다.

using Spire.Xls;
using System.Data;

namespace ReadExcelIntoDataTable
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            // Create a Workbook object
            Workbook wb = new Workbook();

            // Load an existing Excel file
            wb.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Input.xlsx");

            // Get the first worksheet
            Worksheet sheet = wb.Worksheets[0];

            // Export data from worksheet into a DataTable
            DataTable dataTable = sheet.ExportDataTable();

            // Bind DataTable to DataGridView
            dataGridView1.DataSource = dataTable;

            // Dispose resources
            wb.Dispose();
        }
    }
}

결과:

Excel 데이터를 데이터베이스로 읽기

Excel 데이터를 데이터베이스와 통합하면 데이터 관리를 간소화할 수 있습니다. 아래에서는 Excel 파일을 읽고 그 내용을 MySQL 데이터베이스로 가져오는 과정을 안내합니다. 이 방법은 데이터 마이그레이션, 보고 또는 Excel 데이터를 구조화된 데이터베이스와 동기화하는 작업을 자동화하는 데 이상적입니다.

1단계. MySQL 데이터 라이브러리 설치

.NET 애플리케이션에서 MySQL 데이터베이스와 상호 작용하려면 MySql.Data 라이브러리를 설치해야 합니다. 이 NuGet 패키지는 MySQL 데이터베이스에 연결하고 조작하는 데 필요한 클래스와 메서드를 제공합니다.

Install-Package MySql.Data

2단계. 필요한 네임스페이스 가져오기

Excel 파일 및 MySQL로 작업하기 전에 필요한 네임스페이스를 포함해야 합니다. Spire.XLS는 Excel 작업에 사용되고 MySql.Data.MySqlClient는 MySQL 데이터베이스 연결을 활성화합니다.

using Spire.Xls;
using MySql.Data.MySqlClient;

3단계. Excel에서 헤더 및 데이터 추출

다음 코드 조각은 Excel 파일에서 헤더와 데이터를 추출하는 방법을 보여줍니다. 헤더는 MySQL 열 이름 충돌을 피하기 위해 정리되고 데이터는 나중에 삽입할 수 있도록 구조화된 형식으로 저장됩니다.

// Create a Workbook object
Workbook wb = new Workbook();

// Load an Excel document
wb.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Input.xlsx");

// Get a specific sheet
Worksheet sheet = wb.Worksheets[0];

// Retrieve headers
List<string> headers = new List<string>();
for (int col = 1; col <= sheet.LastColumn; col++)
{
    string header = sheet.Range[1, col].Value?.ToString();
    // Removing spaces to avoid conflicts with MySQL column names
    string cleanHeader = header?.Replace(" ", "");
    headers.Add($"`{cleanHeader}`");
}

// Retrieve data
List<List<string>> data = new List<List<string>>();
for (int row = 2; row <= sheet.LastRow; row++) {
    List<string> record = new List<string>();
    for (int col = 1; col <= sheet.LastColumn; col++)
    {
        record.Add(sheet.Range[row, col].Value?.ToString() ?? string.Empty);
    }
    data.Add(record);
}

4단계. MySQL 데이터베이스에 연결

연결 문자열을 사용하여 MySQL 데이터베이스에 대한 연결이 설정됩니다. 여기에는 서버 세부 정보, 자격 증명 및 대상 데이터베이스 이름이 포함됩니다. using 문은 적절한 리소스 폐기를 보장합니다.

string connectionString = "server=localhost;user=root;password=yourpassword;database=yourdatabase;";
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
    connection.Open();
    // Connection is established; perform database operations here
}

5단계. MySQL에서 동적으로 테이블 만들기

이 단계에서는 Excel 헤더와 일치하는 열이 있는 MySQL 테이블을 동적으로 생성합니다. 단순화를 위해 모든 열은 VARCHAR(255)로 설정되지만 요구 사항에 따라 데이터 유형을 조정할 수 있습니다.

// Create a table with dynamic columns based on headers
List<string> columns = new List<string>();
foreach (string header in headers)
{
    // Assuming all header values are VARCHAR for simplicity; adjust types as needed
    columns.Add($"{header} VARCHAR(255)");
}

// Create a table in database
string columnsSql = string.Join(", ", columns);
string createTableQuery = $ @"
    CREATE TABLE IF NOT EXISTS my_table (
        id INT AUTO_INCREMENT PRIMARY KEY,
        {columnsSql}
)";

// Execute the create table query
using (MySqlCommand createCommand = new MySqlCommand(createTableQuery, connection))
{
    createCommand.ExecuteNonQuery();
}

6단계. 데이터로 테이블 채우기

추출된 Excel 데이터는 SQL 삽입을 방지하기 위해 매개변수화된 쿼리를 사용하여 MySQL 테이블에 삽입됩니다. Excel 파일의 각 행은 해당 데이터베이스 레코드에 매핑됩니다.

// Prepare the SQL INSERT statement
string placeholders = string.Join(", ", new string[headers.Count].Select(h => "?"));
string insertQuery = $"INSERT INTO my_table ({string.Join(", ", headers.Select(h => h.Trim('`')))}) VALUES ({placeholders})";

// Insert data into the table
foreach (List<string> record in data)
{
    using (MySqlCommand insertCommand = new MySqlCommand(insertQuery, connection))
    {
        for (int i = 0; i < record.Count; i++)
        {
            insertCommand.Parameters.AddWithValue($" @2024\本地文件打包__20180302\Spire.Web\trunk\src\website\components\com_virtuemart\themes\default\templates\browse\includes\browse_searchparameter_form.tpl.php{i}", record[i]);
        }
        insertCommand.ExecuteNonQuery();
    }
}

다음은 Excel 파일에서 MySQL 테이블로 데이터를 가져오는 전체 코드입니다.

using Spire.Xls;
using MySql.Data.MySqlClient;

namespace ExcelToMySQL
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a Workbook object
            Workbook wb = new Workbook();

            // Load an Excel document
            wb.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Input.xlsx");

            // Get a specific sheet
            Worksheet sheet = wb.Worksheets[0];

            // Retrieve headers
            List<string> headers = new List<string>();
            for (int col = 1; col <= sheet.LastColumn; col++)
            {
                string header = sheet.Range[1, col].Value?.ToString();
                // Removing spaces to avoid conflicts with MySQL column names
                string cleanHeader = header?.Replace(" ", "");
                headers.Add($"`{cleanHeader}`");
            }

            // Retrieve data
            List<List<string>> data = new List<List<string>>();
            for (int row = 2; row <= sheet.LastRow; row++)
            {
                List<string> record = new List<string>();
                for (int col = 1; col <= sheet.LastColumn; col++)
                {
                    record.Add(sheet.Range[row, col].Value?.ToString() ?? string.Empty);
                }
                data.Add(record);
            }

            // Establish a connection to the MySQL database
            string connectionString = "server=localhost;user=root;password=admin;database=excel_db;";
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();

                // Create a table with dynamic columns based on headers
                List<string> columns = new List<string>();
                foreach (string header in headers)
                {
                    // Assuming all header values are VARCHAR for simplicity; adjust types as needed
                    columns.Add($"{header} VARCHAR(255)");
                }

                // Create a table in database
                string columnsSql = string.Join(", ", columns);
                string createTableQuery = $ @"
                    CREATE TABLE IF NOT EXISTS my_table (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        {columnsSql}
                    )";

                // Execute the create table query
                using (MySqlCommand createCommand = new MySqlCommand(createTableQuery, connection))
                {
                    createCommand.ExecuteNonQuery();
                }

                // Prepare the SQL INSERT statement
                string placeholders = string.Join(", ", new string[headers.Count].Select(h => "?"));
                string insertQuery = $"INSERT INTO my_table ({string.Join(", ", headers.Select(h => h.Trim('`')))}) VALUES ({placeholders})";

                // Insert data into the table
                foreach (List<string> record in data)
                {
                    using (MySqlCommand insertCommand = new MySqlCommand(insertQuery, connection))
                    {
                        for (int i = 0; i < record.Count; i++)
                        {
                            insertCommand.Parameters.AddWithValue($" @2024\本地文件打包__20180302\Spire.Web\trunk\src\website\components\com_virtuemart\themes\default\templates\browse\includes\browse_searchparameter_form.tpl.php{i}", record[i]);
                        }
                        insertCommand.ExecuteNonQuery();
                    }
                }
            }

            Console.WriteLine("데이터를 성공적으로 내보냈습니다!");
        }
    }
}

결과:

결론

Spire.XLS와 같은 라이브러리 덕분에 C#에서 Excel 파일을 읽는 것이 그 어느 때보다 쉬워졌습니다. 이 가이드에서는 Excel 파일을 로드하고, 내용을 읽고, 데이터를 MySQL 데이터베이스로 가져오는 과정을 안내했습니다. 이러한 기술을 사용하면 애플리케이션의 데이터 처리 기능을 크게 향상시킬 수 있습니다.

자주 묻는 질문

Q1: 비밀번호로 보호된 Excel 파일을 읽을 수 있나요?

A: 예, Spire.XLS는 다음을 사용하여 암호화된 Excel 파일을 읽는 것을 지원합니다.

wb.OpenPassword = "psd";
wb.LoadFromFile("file.xlsx");

Q2: 수식 자체가 아닌 수식 결과를 읽으려면 어떻게 해야 하나요?

A: 수식 결과를 검색하는 두 가지 옵션이 있습니다.

개별 셀의 경우:

CellRange.HasFormula를 사용하여 셀에 수식이 포함되어 있는지 확인하고 CellRange.FormulaValue로 값을 가져옵니다.

CellRange cell = sheet.Range[1, 1];
if (cell.HasFormula)
{
    string result = cell.FormulaValue.ToString();
}

DataTable로 대량 내보내기의 경우:

계산된 값을 내보내려면 computedFormulaValue: true와 함께 Worksheet.ExportDataTable()을 사용하십시오.

DataTable data = sheet.ExportDataTable(range, exportColumnNames: true, computedFormulaValue: true);

Q3: Excel 데이터를 DataTable로 어떻게 읽을 수 있나요?

A: Spire.XLS에서 제공하는 Worksheet.ExportDataTable() 메서드를 사용하십시오.

Q4: Excel 파일을 한 줄씩 어떻게 읽을 수 있나요?

A: 다음 코드를 참조하십시오.

Workbook workbook = new Workbook();
workbook.LoadFromFile("input.xlsx");
Worksheet sheet = workbook.Worksheets[0];

for (int row = 1; row <= sheet.LastRow; row++)
{
    for (int col = 1; col <= sheet.LastColumn; col++)
    {
        string cellValue = sheet.Range[row, col].Value?.ToString() ?? string.Empty;
        Console.WriteLine(cellValue);
    }
}

무료 라이선스 받기

평가 제한 없이 .NET용 Spire.XLS의 모든 기능을 경험하려면 30일 무료 평가판 라이선스를 요청할 수 있습니다.

참고 항목

• C#에서 Excel을 CSV로, CSV를 Excel로 변환
• C#에서 Excel 생성, 읽기 및 업데이트
• C#에서 Excel 이미지 추출
• C#에서 Excel 문서 속성 읽기

La lecture de fichiers Excel en C# est une exigence courante pour de nombreuses applications, que ce soit pour l'analyse de données, la création de rapports ou l'intégration de bases de données. Bien que les bibliothèques Interop de Microsoft puissent être utilisées, elles présentent des limitations (comme la nécessité d'installer Excel). Au lieu de cela, nous explorerons une approche plus efficace en utilisant Spire.XLS, une bibliothèque .NET qui permet de lire et d'écrire des fichiers Excel sans Interop. Cet article couvre :

• Bibliothèque C# .NET pour lire Excel sans Interop
• Comment lire un fichier Excel en C#
• Lire les données Excel dans un DataTable
• Lire les données Excel dans une base de données
• Conclusion
• FAQ

Bibliothèque C# .NET pour lire Excel sans Interop

L'Interop Excel de Microsoft nécessite l'installation d'Excel sur la machine, ce qui le rend inadapté aux applications côté serveur. Au lieu de cela, des bibliothèques comme Spire.XLS offrent une solution légère et performante sans dépendance vis-à-vis d'Excel.

Pourquoi utiliser Spire.XLS ?

• Aucune installation d'Excel requise – Fonctionne de manière indépendante.
• Prend en charge .NET Core & .NET Framework – Compatibilité multiplateforme.
• Lire/Écrire des fichiers Excel – Prend en charge .xls, .xlsx et .xlsm.
• Importer vers DataTable & Bases de données – Intégration transparente avec ADO.NET.

Installation de Spire.XLS

Pour commencer, installez la bibliothèque via le Gestionnaire de paquets NuGet :

Install-Package Spire.XLS

Alternativement, vous pouvez télécharger Spire.XLS pour .NET depuis notre site officiel et référencer le fichier DLL manuellement.

Comment lire un fichier Excel en C#

Cette section montre comment lire un fichier Excel en C# à l'aide de la bibliothèque Spire.XLS. Le processus implique le chargement du fichier, l'accès aux feuilles de calcul et la récupération des valeurs des cellules par programmation. Ceci est utile pour automatiser l'extraction de données, traiter des rapports Excel ou intégrer des données de feuilles de calcul dans des applications.

Étape 1. Importer l'espace de noms nécessaire

Pour utiliser les fonctionnalités de Spire.XLS, vous devez importer son espace de noms. Cela donne accès à des classes comme Workbook et Worksheet, qui sont essentielles pour les opérations sur les fichiers Excel.

using Spire.Xls;

Étape 2. Charger un fichier Excel

Pour charger un fichier Excel, créez un objet Workbook et appelez la méthode LoadFromFile. Cela lit le fichier en mémoire, permettant une manipulation ultérieure.

Workbook wb = new Workbook();
wb.LoadFromFile("input.xlsx");

Étape 3. Obtenir une feuille de calcul spécifique

Les fichiers Excel peuvent contenir plusieurs feuilles de calcul. Vous pouvez accéder à une feuille spécifique en indexant la collection Worksheets (base zéro). La première feuille est à l'index 0, la deuxième à 1, et ainsi de suite.

Worksheet sheet = wb.Worksheets[0]; //Première feuille

Étape 4. Récupérer la valeur d'une cellule spécifique

Pour récupérer la valeur d'une cellule, utilisez la propriété CellRange.Value. Spécifiez les indices de ligne et de colonne (à partir de 1) pour localiser la cellule. Ceci est utile pour extraire des données structurées comme des en-têtes ou des enregistrements individuels.

CellRange cell = sheet.Range[1, 1]; // Ligne 1, Colonne 1 (A1)
string value = cell.Value;

Voici un exemple complet de lecture de données à partir d'une feuille de calcul entière et de leur impression dans la console :

using Spire.Xls;

namespace ReadExcelData
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a Workbook object
            Workbook wb = new Workbook();

            // Load an existing Excel file
            wb.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Input.xlsx");

            // Get the first worksheet
            Worksheet sheet = wb.Worksheets[0];

            // Get the cell range containing data
            CellRange locatedRange = sheet.AllocatedRange;

            // Iterate through the rows
            for (int i = 0; i < locatedRange.Rows.Length; i++)
            {
                // Iterate through the columns
                for (int j = 0; j < locatedRange.Rows[i].ColumnCount; j++)
                {
                    // Get data of a specific cell
                    string cellValue = locatedRange[i + 1, j + 1].Value?.ToString() ?? "N/A";

                    // Align output with a width of 22
                    Console.Write($"{cellValue,-22}");
                }
                Console.WriteLine();
            }
        }
    }
}

Résultat :

Lire les données Excel dans un DataTable

L'exportation de données Excel dans un DataTable permet une intégration transparente avec des contrôles d'interface utilisateur comme DataGridView ou le traitement de données backend. Spire.XLS simplifie ce processus avec sa méthode intégrée ExportDataTable(), qui convertit automatiquement les données de la feuille de calcul en un DataTable structuré tout en préservant les en-têtes de colonne et les types de données.

Étape 1. Importer l'espace de noms nécessaire

Incluez l'espace de noms Spire.XLS pour accéder aux classes essentielles.

using Spire.Xls;

Étape 2. Créer un formulaire et un événement de clic de bouton

Créez un formulaire (par exemple, Form1) et ajoutez un bouton avec un gestionnaire d'événements pour lire le fichier Excel.

public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }

    private void button1_Click(object sender, EventArgs e)
    {
        // Le code ira ici
    }
}

Étape 3. Charger le classeur

Dans l'événement de clic du bouton, créez un objet Workbook et chargez le fichier Excel.

Workbook wb = new Workbook();
wb.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Input.xlsx");

Étape 4. Exporter les données vers DataTable

Accédez à une feuille de calcul spécifique par son index et exportez ses données vers un DataTable à l'aide de la méthode ExportDataTable.

DataTable dataTable = wb.Worksheets[0].ExportDataTable();

Étape 5. Lier les données à DataGridView

En supposant que vous ayez un contrôle DataGridView sur votre formulaire, liez le DataTable au DataGridView pour afficher les données.

dataGridView1.DataSource = dataTable;

Voici le code complet pour lire les données d'un fichier Excel dans un DataTable et les afficher dans un contrôle DataGridView de Windows Forms :

using Spire.Xls;
using System.Data;

namespace ReadExcelIntoDataTable
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            // Create a Workbook object
            Workbook wb = new Workbook();

            // Load an existing Excel file
            wb.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Input.xlsx");

            // Get the first worksheet
            Worksheet sheet = wb.Worksheets[0];

            // Export data from worksheet into a DataTable
            DataTable dataTable = sheet.ExportDataTable();

            // Bind DataTable to DataGridView
            dataGridView1.DataSource = dataTable;

            // Dispose resources
            wb.Dispose();
        }
    }
}

Résultat:

Lire les données Excel dans une base de données

L'intégration des données Excel avec une base de données peut rationaliser la gestion des données. Ci-dessous, nous allons parcourir le processus de lecture d'un fichier Excel et d'importation de son contenu dans une base de données MySQL. Cette méthode est idéale pour automatiser la migration de données, la création de rapports ou la synchronisation des données Excel avec une base de données structurée.

Étape 1. Installer la bibliothèque de données MySQL

Pour interagir avec les bases de données MySQL dans vos applications .NET, vous devrez installer la bibliothèque MySql.Data. Ce paquet NuGet fournit les classes et méthodes nécessaires pour se connecter et manipuler les bases de données MySQL.

Install-Package MySql.Data

Étape 2. Importer les espaces de noms nécessaires

Avant de travailler avec des fichiers Excel et MySQL, vous devez inclure les espaces de noms requis. Spire.XLS est utilisé pour les opérations Excel, tandis que MySql.Data.MySqlClient permet la connectivité à la base de données MySQL.

using Spire.Xls;
using MySql.Data.MySqlClient;

Étape 3. Extraire les en-têtes et les données d'Excel

L'extrait de code suivant montre comment extraire les en-têtes et les données du fichier Excel. Les en-têtes sont nettoyés pour éviter les conflits de nommage des colonnes MySQL, tandis que les données sont stockées dans un format structuré pour une insertion ultérieure.

// Create a Workbook object
Workbook wb = new Workbook();

// Load an Excel document
wb.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Input.xlsx");

// Get a specific sheet
Worksheet sheet = wb.Worksheets[0];

// Retrieve headers
List<string> headers = new List<string>();
for (int col = 1; col <= sheet.LastColumn; col++)
{
    string header = sheet.Range[1, col].Value?.ToString();
    // Removing spaces to avoid conflicts with MySQL column names
    string cleanHeader = header?.Replace(" ", "");
    headers.Add($"`{cleanHeader}`");
}

// Retrieve data
List<List<string>> data = new List<List<string>>();
for (int row = 2; row <= sheet.LastRow; row++) {
    List<string> record = new List<string>();
    for (int col = 1; col <= sheet.LastColumn; col++)
    {
        record.Add(sheet.Range[row, col].Value?.ToString() ?? string.Empty);
    }
    data.Add(record);
}

Étape 4. Se connecter à une base de données MySQL

Une connexion à la base de données MySQL est établie à l'aide d'une chaîne de connexion, qui inclut les détails du serveur, les informations d'identification et le nom de la base de données cible. L'instruction using garantit une élimination correcte des ressources.

string connectionString = "server=localhost;user=root;password=yourpassword;database=yourdatabase;";
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
    connection.Open();
    // Connection is established; perform database operations here
}

Étape 5. Créer dynamiquement une table dans MySQL

Cette étape génère dynamiquement une table MySQL avec des colonnes correspondant aux en-têtes Excel. Pour plus de simplicité, toutes les colonnes sont définies comme VARCHAR(255), mais les types de données peuvent être ajustés en fonction des besoins.

// Create a table with dynamic columns based on headers
List<string> columns = new List<string>();
foreach (string header in headers)
{
    // Assuming all header values are VARCHAR for simplicity; adjust types as needed
    columns.Add($"{header} VARCHAR(255)");
}

// Create a table in database
string columnsSql = string.Join(", ", columns);
string createTableQuery = $ @"
    CREATE TABLE IF NOT EXISTS my_table (
        id INT AUTO_INCREMENT PRIMARY KEY,
        {columnsSql}
)";

// Execute the create table query
using (MySqlCommand createCommand = new MySqlCommand(createTableQuery, connection))
{
    createCommand.ExecuteNonQuery();
}

Étape 6. Remplir la table avec des données

Les données Excel extraites sont insérées dans la table MySQL à l'aide de requêtes paramétrées pour empêcher l'injection SQL. Chaque ligne du fichier Excel est mappée à un enregistrement de base de données correspondant.

// Prepare the SQL INSERT statement
string placeholders = string.Join(", ", new string[headers.Count].Select(h => "?"));
string insertQuery = $"INSERT INTO my_table ({string.Join(", ", headers.Select(h => h.Trim('`')))}) VALUES ({placeholders})";

// Insert data into the table
foreach (List<string> record in data)
{
    using (MySqlCommand insertCommand = new MySqlCommand(insertQuery, connection))
    {
        for (int i = 0; i < record.Count; i++)
        {
            insertCommand.Parameters.AddWithValue($" @2024\本地文件打包__20180302\Spire.Web\trunk\src\website\components\com_virtuemart\themes\default\templates\browse\includes\browse_searchparameter_form.tpl.php{i}", record[i]);
        }
        insertCommand.ExecuteNonQuery();
    }
}

Voici le code complet pour importer des données d'un fichier Excel dans une table MySQL :

using Spire.Xls;
using MySql.Data.MySqlClient;

namespace ExcelToMySQL
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a Workbook object
            Workbook wb = new Workbook();

            // Load an Excel document
            wb.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Input.xlsx");

            // Get a specific sheet
            Worksheet sheet = wb.Worksheets[0];

            // Retrieve headers
            List<string> headers = new List<string>();
            for (int col = 1; col <= sheet.LastColumn; col++)
            {
                string header = sheet.Range[1, col].Value?.ToString();
                // Removing spaces to avoid conflicts with MySQL column names
                string cleanHeader = header?.Replace(" ", "");
                headers.Add($"`{cleanHeader}`");
            }

            // Retrieve data
            List<List<string>> data = new List<List<string>>();
            for (int row = 2; row <= sheet.LastRow; row++)
            {
                List<string> record = new List<string>();
                for (int col = 1; col <= sheet.LastColumn; col++)
                {
                    record.Add(sheet.Range[row, col].Value?.ToString() ?? string.Empty);
                }
                data.Add(record);
            }

            // Establish a connection to the MySQL database
            string connectionString = "server=localhost;user=root;password=admin;database=excel_db;";
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();

                // Create a table with dynamic columns based on headers
                List<string> columns = new List<string>();
                foreach (string header in headers)
                {
                    // Assuming all header values are VARCHAR for simplicity; adjust types as needed
                    columns.Add($"{header} VARCHAR(255)");
                }

                // Create a table in database
                string columnsSql = string.Join(", ", columns);
                string createTableQuery = $ @"
                    CREATE TABLE IF NOT EXISTS my_table (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        {columnsSql}
                    )";

                // Execute the create table query
                using (MySqlCommand createCommand = new MySqlCommand(createTableQuery, connection))
                {
                    createCommand.ExecuteNonQuery();
                }

                // Prepare the SQL INSERT statement
                string placeholders = string.Join(", ", new string[headers.Count].Select(h => "?"));
                string insertQuery = $"INSERT INTO my_table ({string.Join(", ", headers.Select(h => h.Trim('`')))}) VALUES ({placeholders})";

                // Insert data into the table
                foreach (List<string> record in data)
                {
                    using (MySqlCommand insertCommand = new MySqlCommand(insertQuery, connection))
                    {
                        for (int i = 0; i < record.Count; i++)
                        {
                            insertCommand.Parameters.AddWithValue($" @2024\本地文件打包__20180302\Spire.Web\trunk\src\website\components\com_virtuemart\themes\default\templates\browse\includes\browse_searchparameter_form.tpl.php{i}", record[i]);
                        }
                        insertCommand.ExecuteNonQuery();
                    }
                }
            }

            Console.WriteLine("Données exportées avec succès !");
        }
    }
}

Résultat:

Conclusion

La lecture de fichiers Excel en C# n'a jamais été aussi simple, grâce à des bibliothèques comme Spire.XLS. Ce guide vous a expliqué le processus de chargement de fichiers Excel, de lecture de leur contenu et même d'importation des données dans une base de données MySQL. Avec ces techniques, vous pouvez améliorer considérablement les capacités de gestion des données de vos applications.

FAQ

Q1 : Puis-je lire des fichiers Excel protégés par mot de passe ?

R : Oui, Spire.XLS prend en charge la lecture de fichiers Excel cryptés en utilisant :

wb.OpenPassword = "psd";
wb.LoadFromFile("file.xlsx");

Q2 : Comment puis-je lire les résultats des formules au lieu de la formule elle-même ?

R : Vous avez deux options pour récupérer les résultats des formules :

Pour les cellules individuelles :

Vérifiez si une cellule contient une formule à l'aide de CellRange.HasFormula, et obtenez la valeur avec CellRange.FormulaValue :

CellRange cell = sheet.Range[1, 1];
if (cell.HasFormula)
{
    string result = cell.FormulaValue.ToString();
}

Pour l'exportation en masse vers DataTable :

Utilisez Worksheet.ExportDataTable() avec computedFormulaValue: true pour exporter les valeurs calculées :

DataTable data = sheet.ExportDataTable(range, exportColumnNames: true, computedFormulaValue: true);

Q3 : Comment puis-je lire les données Excel dans un DataTable ?

R : Utilisez la méthode Worksheet.ExportDataTable() fournie par Spire.XLS.

Q4 : Comment puis-je lire un fichier Excel ligne par ligne ?

R : Référez-vous au code suivant :

Workbook workbook = new Workbook();
workbook.LoadFromFile("input.xlsx");
Worksheet sheet = workbook.Worksheets[0];

for (int row = 1; row <= sheet.LastRow; row++)
{
    for (int col = 1; col <= sheet.LastColumn; col++)
    {
        string cellValue = sheet.Range[row, col].Value?.ToString() ?? string.Empty;
        Console.WriteLine(cellValue);
    }
}

Obtenez une licence gratuite

Pour profiter pleinement des capacités de Spire.XLS pour .NET sans aucune limitation d'évaluation, vous pouvez demander une licence d'essai gratuite de 30 jours.

Voir aussi

• Convertir Excel en CSV et CSV en Excel en C#
• Créer, lire et mettre à jour Excel en C#
• Extraire des images d'Excel en C#
• Lire les propriétés du document dans Excel en C#

Reading Excel files in C# is a common requirement for many applications, whether for data analysis, reporting, or database integration. While Microsoft’s Interop libraries can be used, they have limitations (such as requiring Excel to be installed). Instead, we’ll explore a more efficient approach using Spire.XLS , a .NET library that allows reading and writing Excel files without Interop . This article covers:

• C# .NET Library for Reading Excel Without Interop
• How to Read an Excel File in C#
• Read Excel Data into a DataTable
• Read Excel Data into a DataBase
• Conclusion
• FAQs

C# .NET Library for Reading Excel Without Interop

Microsoft's Excel Interop requires Excel to be installed on the machine, making it unsuitable for server-side applications. Instead, libraries like Spire.XLS offer a lightweight, high-performance solution without dependencies on Excel.

Why Use Spire.XLS?

• No Excel Installation Required – Works independently.
• Supports .NET Core & .NET Framework – Cross-platform compatibility.
• Read/Write Excel Files – Supports .xls, .xlsx, and .xlsm.
• Import to DataTable & Databases – Seamless integration with ADO.NET.

Installation of Spire.XLS

To get started, install the library via NuGet Package Manager:

Install-Package Spire.XLS

Alternatively, you can download Spire.XLS for .NET from our official website and reference the DLL file manually.

How to Read an Excel File in C#

This section demonstrates how to read an Excel file in C# using the Spire.XLS library. The process involves loading the file, accessing worksheets, and retrieving cell values programmatically. This is useful for automating data extraction, processing Excel reports, or integrating spreadsheet data into applications.

Step 1. Import Necessary Namespace

To utilize the Spire.XLS functionality, you need to import its namespace. This gives access to classes like Workbook and Worksheet , which are essential for Excel file operations.

using Spire.Xls;

Step 2. Load an Excel File

To load an Excel file, create a Workbook object and call the LoadFromFile method. This reads the file into memory, allowing further manipulation.

Workbook wb = new Workbook();
wb.LoadFromFile("input.xlsx");

Step 3. Get a Specific Worksheet

Excel files can contain multiple worksheets. You can access a specific sheet by indexing the Worksheets collection (zero-based). The first sheet is at index 0, the second at 1, and so on.

Worksheet sheet = wb.Worksheets[0]; //First sheet

Step 4. Retrieve Value of a Specific Cell

To retrieve a cell’s value, use the CellRange.Valueproperty. Specify the row and column indices (starting from 1) to locate the cell. This is useful for extracting structured data like headers or individual records.

CellRange cell = sheet.Range[1, 1]; // Row1, Column 1 (A1)
string value = cell.Value;

Below is a complete example of reading data from an entire worksheet and printing it to the console:

using Spire.Xls;

namespace ReadExcelData
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a Workbook object
            Workbook wb = new Workbook();

            // Load an existing Excel file
            wb.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Input.xlsx");

            // Get the first worksheet
            Worksheet sheet = wb.Worksheets[0];

            // Get the cell range containing data
            CellRange locatedRange = sheet.AllocatedRange;

            // Iterate through the rows
            for (int i = 0; i < locatedRange.Rows.Length; i++)
            {
                // Iterate through the columns
                for (int j = 0; j < locatedRange.Rows[i].ColumnCount; j++)
                {
                    // Get data of a specific cell
                    string cellValue = locatedRange[i + 1, j + 1].Value?.ToString() ?? "N/A";

                    // Align output with a width of 22
                    Console.Write($"{cellValue,-22}");
                }
                Console.WriteLine();
            }
        }
    }
}

Result:

Read Excel Data into a DataTable

Exporting Excel data into a DataTable enables seamless integration with UI controls like DataGridView or backend data processing. Spire.XLS simplifies this process with its built-in ExportDataTable() method, which automatically converts worksheet data into a structured DataTable while preserving column headers and data types.

Step 1. Import Necessary Namespace

Include the Spire.XLS namespace to access essential classes.

using Spire.Xls;

Step 2. Create a Form and Button Click Event

Create a form (e.g., Form1) and add a button with an event handler for reading the Excel file.

public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }

    private void button1_Click(object sender, EventArgs e)
    {
        // Code will go here
    }
}

Step 3. Load the Workbook

Within the button click event, create a Workbook object and load the Excel file.

Workbook wb = new Workbook();
wb.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Input.xlsx");

Step 4. Export Data to DataTable

Access a specific worksheet by its index and export its data to a DataTable using the ExportDataTable method.

DataTable dataTable = wb.Worksheets[0].ExportDataTable();

Step 5. Bind Data to DataGridView

Assuming you have a DataGridView control on your form, bind the DataTable to the DataGridView to display the data.

dataGridView1.DataSource = dataTable;

The following is the full code for reading data from an Excel file into a DataTable and displaying it in a Windows Forms DataGridView control:

using Spire.Xls;
using System.Data;

namespace ReadExcelIntoDataTable
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            // Create a Workbook object
            Workbook wb = new Workbook();

            // Load an existing Excel file
            wb.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Input.xlsx");

            // Get the first worksheet
            Worksheet sheet = wb.Worksheets[0];

            // Export data from worksheet into a DataTable
            DataTable dataTable = sheet.ExportDataTable();

            // Bind DataTable to DataGridView
            dataGridView1.DataSource = dataTable;

            // Dispose resources
            wb.Dispose();
        }
    }
}

Result:

Read Excel Data into a DataBase

Integrating Excel data with a database can streamline data management. Below, we will walk through the process of reading an Excel file and importing its contents into a MySQL database. This method is ideal for automating data migration, reporting, or synchronizing Excel data with a structured database.

Step 1. Install MySQL Data Library

To interact with MySQL databases in your .NET applications, you’ll need to install MySql.Data library. This NuGet package provides the necessary classes and methods to connect to and manipulate MySQL databases.

Install-Package MySql.Data

Step 2. Import Necessary Namespaces

Before working with Excel files and MySQL, you must include the required namespaces. Spire.XLS is used for Excel operations, while MySql.Data.MySqlClient enables MySQL database connectivity.

using Spire.Xls;
using MySql.Data.MySqlClient;

Step 3. Extract Headers and Data from Excel

The following code snippet demonstrates how to extract headers and data from the Excel file. Headers are cleaned to avoid MySQL column naming conflicts, while data is stored in a structured format for later insertion.

// Create a Workbook object
Workbook wb = new Workbook();

// Load an Excel document
wb.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Input.xlsx");

// Get a specific sheet
Worksheet sheet = wb.Worksheets[0];

// Retrieve headers
List<string> headers = new List<string>();
for (int col = 1; col <= sheet.LastColumn; col++)
{
    string header = sheet.Range[1, col].Value?.ToString();
    // Removing spaces to avoid conflicts with MySQL column names
    string cleanHeader = header?.Replace(" ", "");
    headers.Add($"`{cleanHeader}`");
}

// Retrieve data
List<List<string>> data = new List<List<string>>();
for (int row = 2; row <= sheet.LastRow; row++) {
    List<string> record = new List<string>();
    for (int col = 1; col <= sheet.LastColumn; col++)
    {
        record.Add(sheet.Range[row, col].Value?.ToString() ?? string.Empty);
    }
    data.Add(record);
}

Step 4. Connect to a MySQL Database

A connection to the MySQL database is established using a connection string, which includes server details, credentials, and the target database name. The using statement ensures proper resource disposal.

string connectionString = "server=localhost;user=root;password=yourpassword;database=yourdatabase;";
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
    connection.Open();
    // Connection is established; perform database operations here
}

Step 5. Dynamically Create a Table in MySQL

This step dynamically generates a MySQL table with columns matching the Excel headers. For simplicity, all columns are set as VARCHAR(255), but data types can be adjusted based on requirements.

// Create a table with dynamic columns based on headers
List<string> columns = new List<string>();
foreach (string header in headers)
{
    // Assuming all header values are VARCHAR for simplicity; adjust types as needed
    columns.Add($"{header} VARCHAR(255)");
}

// Create a table in database
string columnsSql = string.Join(", ", columns);
string createTableQuery = $@"
    CREATE TABLE IF NOT EXISTS my_table (
        id INT AUTO_INCREMENT PRIMARY KEY,
        {columnsSql}
)";

// Execute the create table query
using (MySqlCommand createCommand = new MySqlCommand(createTableQuery, connection))
{
    createCommand.ExecuteNonQuery();
}

Step 6. Populate the Table with Data

The extracted Excel data is inserted into the MySQL table using parameterized queries to prevent SQL injection. Each row from the Excel file is mapped to a corresponding database record.

// Prepare the SQL INSERT statement
string placeholders = string.Join(", ", new string[headers.Count].Select(h => "?"));
string insertQuery = $"INSERT INTO my_table ({string.Join(", ", headers.Select(h => h.Trim('`')))}) VALUES ({placeholders})";

// Insert data into the table
foreach (List<string> record in data)
{
    using (MySqlCommand insertCommand = new MySqlCommand(insertQuery, connection))
    {
        for (int i = 0; i < record.Count; i++)
        {
            insertCommand.Parameters.AddWithValue($"@param{i}", record[i]);
        }
        insertCommand.ExecuteNonQuery();
    }
}

Here’s the full code for importing data from an Excel file into a MySQL table:

using Spire.Xls;
using MySql.Data.MySqlClient;

namespace ExcelToMySQL
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a Workbook object
            Workbook wb = new Workbook();

            // Load an Excel document
            wb.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Input.xlsx");

            // Get a specific sheet
            Worksheet sheet = wb.Worksheets[0];

            // Retrieve headers
            List<string> headers = new List<string>();
            for (int col = 1; col <= sheet.LastColumn; col++)
            {
                string header = sheet.Range[1, col].Value?.ToString();
                // Removing spaces to avoid conflicts with MySQL column names
                string cleanHeader = header?.Replace(" ", "");
                headers.Add($"`{cleanHeader}`");
            }

            // Retrieve data
            List<List<string>> data = new List<List<string>>();
            for (int row = 2; row <= sheet.LastRow; row++)
            {
                List<string> record = new List<string>();
                for (int col = 1; col <= sheet.LastColumn; col++)
                {
                    record.Add(sheet.Range[row, col].Value?.ToString() ?? string.Empty);
                }
                data.Add(record);
            }

            // Establish a connection to the MySQL database
            string connectionString = "server=localhost;user=root;password=admin;database=excel_db;";
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();

                // Create a table with dynamic columns based on headers
                List<string> columns = new List<string>();
                foreach (string header in headers)
                {
                    // Assuming all header values are VARCHAR for simplicity; adjust types as needed
                    columns.Add($"{header} VARCHAR(255)");
                }

                // Create a table in database
                string columnsSql = string.Join(", ", columns);
                string createTableQuery = $@"
                    CREATE TABLE IF NOT EXISTS my_table (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        {columnsSql}
                    )";

                // Execute the create table query
                using (MySqlCommand createCommand = new MySqlCommand(createTableQuery, connection))
                {
                    createCommand.ExecuteNonQuery();
                }

                // Prepare the SQL INSERT statement
                string placeholders = string.Join(", ", new string[headers.Count].Select(h => "?"));
                string insertQuery = $"INSERT INTO my_table ({string.Join(", ", headers.Select(h => h.Trim('`')))}) VALUES ({placeholders})";

                // Insert data into the table
                foreach (List<string> record in data)
                {
                    using (MySqlCommand insertCommand = new MySqlCommand(insertQuery, connection))
                    {
                        for (int i = 0; i < record.Count; i++)
                        {
                            insertCommand.Parameters.AddWithValue($"@param{i}", record[i]);
                        }
                        insertCommand.ExecuteNonQuery();
                    }
                }
            }

            Console.WriteLine("Data exported successfully!");
        }
    }
}

Result:

Conclusion

Reading Excel files in C# has never been easier, thanks to libraries like Spire.XLS. This guide has walked you through the process of loading Excel files, reading their contents, and even importing the data into a MySQL database. With these techniques, you can enhance your applications' data handling capabilities significantly.

FAQs

Q1: Can I read password-protected Excel files?

A: Yes, Spire.XLS supports reading encrypted Excel files using:

wb.OpenPassword = "psd";
wb.LoadFromFile("file.xlsx");

Q2: How do I read formula results instead of the formula itself?

A: You have two options to retrieve formula results:

For individual cells:

Check if a cell contains a formula using CellRange.HasFormula , and get the value with CellRange.FormulaValue :

CellRange cell = sheet.Range[1, 1];
if (cell.HasFormula)
{
    string result = cell.FormulaValue.ToString();
}

For bulk export to DataTable:

Use Worksheet.ExportDataTable() with computedFormulaValue: true to export calculated values:

DataTable data = sheet.ExportDataTable(range, exportColumnNames: true, computedFormulaValue: true);

Q3: How can I read Excel data into a DataTable?

A: Use the Worksheet.ExportDataTable() method provided by Spire.XLS.

Q4: How can I read an Excel file line by line?

A: Refer to the following code:

Workbook workbook = new Workbook();
workbook.LoadFromFile("input.xlsx");
Worksheet sheet = workbook.Worksheets[0];

for (int row = 1; row <= sheet.LastRow; row++)
{
    for (int col = 1; col <= sheet.LastColumn; col++)
    {
        string cellValue = sheet.Range[row, col].Value?.ToString() ?? string.Empty;
        Console.WriteLine(cellValue);
    }
}

Get a Free License

To fully experience the capabilities of Spire.XLS for .NET without any evaluation limitations, you can request a free 30-day trial license.

See Also

• Convert Excel to CSV and CSV to Excel in C#
• Create, Read, and Update Excel in C#
• Extract Images from Excel in C#
• Read Document Properties in Excel in C#