Knowledgebase (2311)
Children categories
A DataTable represents a table of in-memory relational data. It can be populated from a data source like Microsoft SQL Server or from a file like CSV or Excel. In this article, you will learn how to populate DataTable from CSV, or in other words, how to convert CSV to DataTable in C# and VB.NET using Spire.XLS for .NET.
Install Spire.XLS for .NET
To begin with, you need to add the DLL files included in the Spire.XLS for .NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.
PM> Install-Package Spire.XLS
Convert CSV to DataTable in C# and VB.NET
The following are the main steps to convert CSV to DataTable:
- Initialize an instance of Workbook class.
- Load a CSV file using Workbook.LoadFromFile() method and passing the file path and the delimiter/separator of the CSV file in the form of string as parameters.
- Get the desired worksheet by its index (zero-based) through Workbook.Worksheets[sheetIndex] property.
- Export data from the worksheet to a DataTable using Worksheet.ExportDataTable() method.
(The ExportDataTable() method has several overloads that can be used to control how the data will be exported, for example, ExportDataTable(CellRange range, bool exportColumnNames, bool computedFormulaValue): this overload allows you to specify the range to be exported along with whether to export columns names and calculated values of formulas.
- C#
- VB.NET
using Spire.Xls;
using System;
using System.Data;
using System.Windows.Forms;
namespace ConvertCsvToExcel
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
//Create a Workbook instance
Workbook workbook = new Workbook();
//Load a CSV file
workbook.LoadFromFile(@"C:\Users\Administrator\Desktop\Input.csv", ",");
//Get the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
//Export data from the worksheet to a DataTable
DataTable dt = worksheet.ExportDataTable();
//This overload enables you to specify the range to be exported along with whether to export column names and calculated values of formulas
//DataTable dt = worksheet.ExportDataTable(worksheet.Range["A1:C10"], true, true);
//Show the DataTable in a DataGridView control (optional)
dataGridView1.DataSource = dt;
}
}
}
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
This section will show you an easy solution to quickly export datatable from database to Excel via an Excel .NET component in C#, VB.NET.
Spire.XLS for .NET enables you to both export datatable to excel and import excel to datatable. This solution shows you two lines of key souce code for exporting data from datatable to Excel. One is XlsWorksheet.InsertDataTable(System.Data.DataTable dataTable, bool columnHeaders, int firstRow, int firstColumn) which is responsible for importing the data into worksheet. The other is Workbook.SaveToFile(string fileName) that is called to save the workbook to Excel file.
Here you can download Spire.XLS for .NET and start to perform the datatable to Excel task by below code.
Sample code:
private void button1_Click(object sender, EventArgs e)
{
//connect database
OleDbConnection connection = new OleDbConnection();
connection.ConnectionString @"Provider=""Microsoft.Jet.OLEDB.4.0"";Data Source=""demo.mdb"";User Id=;Password="
OleDbCommand command = new OleDbCommand();
command.CommandText = "select * from parts";
DataSet dataSet = new System.Data.DataSet();
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(command.CommandText,connection);
dataAdapter.Fill(dataSet);
DataTable t = dataSet.Tables[0];
//export datatable to excel
Workbook book = new Workbook();
Worksheet sheet = book.Worksheets[0];
sheet.InsertDataTable(t, true, 1, 1);
book.SaveToFile("insertTableToExcel.xls",ExcelVersion.Version97to2003);
System.Diagnostics.Process.Start("insertTableToExcel.xls");
}
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
//connect database
Dim connection As OleDbConnection = New OleDbConnection
connection.ConnectionString = “Provider=””Microsoft.Jet.OLEDB.4.0””;
Data Source=""demo.mdb""; User Id=;Password="
Dim command As OleDbCommand = New OleDbCommand
command.CommandText = "select * from parts"
Dim dataSet As DataSet = New System.Data.DataSet
Dim dataAdapter As OleDbDataAdapter = New OleDbDataAdapter(command.CommandText, connection)
dataAdapter.Fill(dataSet)
Dim t As DataTable = dataSet.Tables(0)
//export datatable to excel
Dim book As Workbook = New Workbook
Dim sheet As Worksheet = book.Worksheets(0)
sheet.InsertDataTable(t, True, 1, 1)
book.SaveToFile("insertTableToExcel.xls",ExcelVersion.Version97to2003)
System.Diagnostics.Process.Start("insertTableToExcel.xls")
End Sub
End Class
Different from Word and Excel, PDF enables people to attach various files in it. For the convenience of both protecting the confidential files and avoiding the PDF too large, PDF also allows developers to remove any attachment. This section will introduce a solution to remove attachments from PDF via a PDF component in C#, VB.NET.
Spire.PDF for .NET, a .NET component with rich capabilities in manipulating PDF, enables you to quickly remove attachments from your PDF document. Now please see below picture and view the whole attachments remove solution after it.
In the solution, first you need a decision procedure to see whether this PDF file has any attachment, then, remove attachments. There are two situations here. One is to remove all the attachments. You can call this method: Document.Attachments.Clear(). The other is to remove a certain attachment or attachments. You can remove them either by pointing file name for example filename=="e-iceblue.html " or by the file extension such as filename.Contains(".html"). Below solution directly find the PDF via file name. Please remember to download Spire.PDF for .NET before performing the project.
using Spire.Pdf;
using Spire.Pdf.Attachments;
using System;
using System.Windows.Forms;
namespace RemoveAttachments
{
class Program
{
private void button1_Click(object sender, EventArgs e)
{
OpenFileDialog dialog = new OpenFileDialog();
dialog.Filter = "PDF document(*.pdf)|*.pdf";
DialogResult result = dialog.ShowDialog();
if (result == DialogResult.OK)
{
PdfDocument doc = new PdfDocument();
doc.LoadFromFile(dialog.FileName);
PdfAttachmentCollection attachments = doc.Attachments;
PdfAttachment attachment = null;
if (doc.Attachments.Count > 0)
{
foreach (PdfAttachment oneAttachment in doc.Attachments)
{
string filename = oneAttachment.FileName;
if (filename == "excel.xlsx")
{
attachment = oneAttachment;
break;
}
}
}
if (attachment != null)
{
doc.Attachments.Remove(attachment);
}
doc.SaveToFile("test.pdf");
System.Diagnostics.Process.Start("test.pdf");
}
}
}
}
Imports Spire.Pdf
Imports Spire.Pdf.Attachments
Imports System.Windows.Forms
Namespace RemoveAttachments
Class Program
Private Sub button1_Click(sender As Object, e As EventArgs)
Dim dialog As New OpenFileDialog()
dialog.Filter = "PDF document(*.pdf)|*.pdf"
Dim result As DialogResult = dialog.ShowDialog()
If result = DialogResult.OK Then
Dim doc As New PdfDocument()
doc.LoadFromFile(dialog.FileName)
Dim attachments As PdfAttachmentCollection = doc.Attachments
Dim attachment As PdfAttachment = Nothing
If doc.Attachments.Count > 0 Then
For Each oneAttachment As PdfAttachment In doc.Attachments
Dim filename As String = oneAttachment.FileName
If filename = "excel.xlsx" Then
attachment = oneAttachment
Exit For
End If
Next
End If
If attachment IsNot Nothing Then
doc.Attachments.Remove(attachment)
End If
doc.SaveToFile("test.pdf")
System.Diagnostics.Process.Start("test.pdf")
End If
End Sub
End Class
End Namespace
Spire.PDF is a .NET PDF component, which enables users to perform a wide range of PDF processing tasks directly, such as generate, read, write and modify PDF document in WPF, .NET and Silverlight.
