Export Datatable to Excel from Database

2012-11-13 06:38:51 Written by Koohji

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.

datatable to excel

Here you can download Spire.XLS for .NET and start to perform the datatable to Excel task by below code.

Sample code:

[C#]
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");
        }
[VB.NET]
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

Remove Attachments from PDF in C#/VB.NET

2012-11-06 05:49:47 Written by Koohji

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.

Remove PDF Attachments

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.

[C#]
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");
            }
        }
    }
}
[VB.NET]
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.

Payment method we accept

We accept all the online payments supported by Paypal or 2Checkout. And we also accept the other payments. Such as Bank/Wire transfer, PO, Check/Money Order etc.

Online Payment Process

Paypal

  • Choose your item and click the button "BUY."
  • Enter the coupon code if you have.
  • In the "Please select Payment Method", Choose Paypal.
  • Proceed the payment by using Paypal.

2Checkout

  • Choose your item and click the button "BUY."
  • In the "Please select Payment Method", Choose 2Checkout.
  • Proceed the payment by using 2Checkout.
  • Enter the coupon code if you have.

Purchase Order payment Process

  • Customer sends inquiry and insists to purchase via Purchase Order
  • We send the product quotation sheet
  • Customer confirms and send purchase order sheet
  • We confirm and sends customer Bank information
  • Customer Pay via Bank Transfer
  • We check payment and send license file once we confirm the payment
  • We Send Invoice to customer

Purchase Security

It is 100% secure to do business with us. The Payment platform we utilize is Paypal and 2Checkout. They are the most trusted global online payment system in the world. The order of your private information, such as credit card number, addresses and so on will never be disclosed without your specific permission. Over the years, there have not been one single complaint so far against the account security.

How and When will the ordered software be sent out

Once you complete the online order, our support team will send the license file to your registered email address via email directly; usually it is less than 18 hours.

Regular file we provide

We provide the regular file including quotation sheet, sales contract and invoice to our customers. Please contact our sales team according to your own requirements.

Refund Policy

We hold the 14-day money back guarantee if our products cannot work normally with error or bug. But sometimes the software doesn't work well due to improper operation. We strongly suggest you contact our support team for help first with detailed problem description. In addition, we do not accept gratuitous refund and the purchase with discount.

page 284