Spire.XLS for .NET is a professional Excel component which enables developers/programmers to fast generate, read, write and modify Excel document for .NET. Spire.XLS for .NET doesn't need Microsoft Office Excel Automation. It allows user to operate Excel document directly such as save to stream, save as web response, copy, lock/unlock worksheet, set up workbook properties, etc. As a professional .NET Excel component, it also includes many useful features, for example, functionalities of importing data from Excel to dataTable and exporting dataTable to Excel from Database.
In this article introduces a method of updating excel data by dataTable via using sheet.ExportDataTable() method and sheet.InsertDataTable() method to import data from excel to dataTable and export dataTable to excel from Database.
The main steps of method are:
Step 1: Load the excel document and use sheet.ExportDataTable() method extract data to dataTable and show by dataGridView control.
private void Form1_Load(object sender, EventArgs e)
{
//load excel document to workbook
workbook.LoadFromFile(@"DatatableSample.xls");
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Original table";
//extract data to dataTable from sheet
DataTable dataTable = sheet.ExportDataTable();
//show the data to dataGridView
this.dataGridView.DataSource = dataTable;
}
The effect screenshot:
Step 2: Create a new sheet to save the updata data and use sheet.InsertDataTable() method to insert dataTable to the sheet.
//create a new sheet to save Updata data.
Worksheet sheet = workbook.CreateEmptySheet("Updata Table");
//extract data from dataGridView
DataTable dataTable = this.dataGridView.DataSource as DataTable;
// insert dataTable to sheet
sheet.InsertDataTable(dataTable, true, 1, 1);
Step 3: Save the result excel document.
workbook.SaveToFile("result.xlsx", ExcelVersion.Version2007);
The effect screenshot:
Download and install Spire.XLS for .NET and use below code to experience this method to update excel data by dataTable.
The full code:
using System;
using System.Data;
using System.Windows.Forms;
using Spire.Xls;
namespace UpdataExcelDataByDataTable
{
public partial class UpdataExcelData : Form
{
private Workbook workbook = new Workbook();
private void Form1_Load(object sender, EventArgs e)
{
workbook.LoadFromFile(@"DatatableSample.xls");
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Original table";
DataTable dataTable = sheet.ExportDataTable();
this.dataGridView.DataSource = dataTable;
}
private void Updata_Click(object sender, EventArgs e)
{
Worksheet sheet = workbook.CreateEmptySheet("Updata Table");
DataTable dataTable = this.dataGridView.DataSource as DataTable;
sheet.InsertDataTable(dataTable, true, 1, 1);
workbook.SaveToFile("result.xlsx", ExcelVersion.Version2007);
System.Diagnostics.Process.Start("result.xlsx");
}
}
}
Imports System.Data
Imports System.Windows.Forms
Imports Spire.Xls
Public Class Form1
Private workbook As New Workbook()
Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
'load excel document to workbook
workbook.LoadFromFile("DatatableSample.xls")
Dim sheet As Worksheet = workbook.Worksheets(0)
sheet.Name = "Original table"
'extract data to dataTable from sheet
Dim dataTable As DataTable = sheet.ExportDataTable()
'show the data to dataGridView
Me.DataGridView.DataSource = dataTable
End Sub
If you couldn't successfully use the Spire.Xls, please refer Spire.XLS Quick Start which can guide you quickly use the Spire.Xls.
