How to Export Data from Excel to DataTable with C#

We often use MS Excel to save data because it is powerful to managing data and convenient for printing. But sometimes, we need to export data from Excel to DataTable for realizing some special requirements. About DataTable, it is a virtual table to save data temporarily. It is can be applied on VB and ASP. Also, it can bind to database without coding.

How to export data to datatable? In this post, I will introduce a method about how to export data from Excel to DataTable via DataGridView by using C#. 

In this example, I uses a .NET Excel component, Spire.XLS for .NET to realize this function quickly. So, I have add its dll file as reference in my project.

Detailed Steps: 

  1. This is a WinForm project. So I create a form and design it at the beginning. This form includes a label to shows what I want to do, a datagridview to save data temporarily and a Run button. 
  2. Double click button to write code.
  3. Load file which I want to export data to datatable.
  4. Declare sheet as the current worksheet.
  5. Define data source is that data in worksheet and export to gridview.

Designed Form

Designed Form 

Using the code:

using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using Spire.Xls;

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

        private void button1_Click(object sender, EventArgs e)
        {
            //Load Excel File
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@”D:\Work\My Documents\Sales Report.xlsx”);

            //Initialize worksheet
            Worksheet sheet = workbook.Worksheets[0];

            //Export Data
            this.DataGridView.DataSource = sheet.ExportDataTable();
        }
    }
}

 DOWNLOAD Spire.XLS for .NET

How to Write Validation in Excel with C#

Sometimes, although we have created a table template and set format well in Excel, we need others to provides data to fill cells, for example, an application form. When we write information in cells according to cue words, they need to pay attention that there may be some special requirements, for exmaple, date format must be mm-dd-yy. If we don’t follow requirements to fill cells, there will be a message to tell that you should input correct information. We can call this process Validation.

In this post, I will show the method how to write validation in Excel with C#. This method is based on a component: Spire.XLS. In project, I have added its dll file as reference.

STEPS

  1. Create a new workbook and initialize worksheet.
  2. Write cue words in cells and format these cells.
  3. Set validation, including number validation and date validation.
  4. Save and launch file.

CODING

using System;
using System.Drawing;
using Spire.Xls;

namespace ExcelValidation
{
    class Validation
    {
        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();

            //Initialize Worksheet
            workbook.CreateEmptySheets(1);
            Worksheet sheet = workbook.Worksheets[0];

            //Show Cue Words
            sheet.Range[“B2”].Text = “Input Number(5-9):”;
            sheet.Range[“B2”].Style.Font.FontName = “Calibri”;
            sheet.Range[“B2”].Style.Font.Size = 12;
            sheet.Range[“B2”].Style.Font.IsBold = true;
            sheet.Range[“B2”].Style.Color = Color.LightCyan;

            //Set Validation for Number
            CellRange rangeNumber = sheet.Range[“B3”];
            rangeNumber.DataValidation.CompareOperator = ValidationComparisonOperator.Between;
            rangeNumber.DataValidation.Formula1 = “5”;
            rangeNumber.DataValidation.Formula2 = “9”;
            rangeNumber.DataValidation.AllowType = CellDataType.Decimal;
            rangeNumber.DataValidation.ErrorMessage = “Please input correct number!”;
            rangeNumber.DataValidation.ShowError = true;
            rangeNumber.Style.KnownColor = ExcelColors.YellowCustom;

            //Show Cue Words
            sheet.Range[“B5”].Text = “Inputate:”;
            sheet.Range[“B5”].Style.Font.FontName = “Calibri”;
            sheet.Range[“B5”].Style.Font.Size = 12;
            sheet.Range[“B5”].Style.Font.IsBold = true;
            sheet.Range[“B5”].Style.Color = Color.LightCyan;

            //Set Validataion for Date
            CellRange rangeDate = sheet.Range[“B6”];
            rangeDate.DataValidation.AllowType = CellDataType.Date;
            rangeDate.DataValidation.ErrorMessage = “Please input correct date!”;
            rangeDate.DataValidation.ShowError = true;
            rangeDate.Style.KnownColor = ExcelColors.YellowCustom;
            sheet.AutoFitColumn(2);

            //Save and Launch File
            workbook.SaveToFile(“ExcelValidation.xlsx”, ExcelVersion.Version2010);
            System.Diagnostics.Process.Start(workbook.FileName);
        }
    }
}

 RESULT

 

_______________________________________________________________________________

Click Here to LEARN MORE about Spire.XLS.

Click Here to DOWNLOAD Spire.XLS.

How to Convert Excel to HTML with C#

 For MS Excel is very powerful on managing data, people would like to use it to display data. We can send with E-mail or print to share an Excel file. But if we want to display data with webpage, how should we do?

Actually, a webpage is based on HTML. Therefore, we can convert Excel to HTML file and display data by using browser.

In this post, I want to introduce the way to convert Excel to HTML with C#. And I use a component, Spire.XLS to realize this function more quickly. So, I have added its dll file as reference in project at the beginning. 

The following steps shows details.

  1. Load file which we want to convert to HTML with workbook.LoadFromFile(). Then, initialize worksheet.
  2.  Save Excel to HTML by using sheet.SaveToHTML()
  3.  Launch the converted HTML file.

Main Coding:

using System;
using Spire.Xls;

namespace ExceltoHTML
{
    class toHTML
    {
        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@”D:\Work\My Documents\vip.xlsx”);

            //Initailize worksheet
            Worksheet sheet = workbook.Worksheets[0];

            //Save Excel to HTML
            sheet.SaveToHtml(“toHTML.html”);
            System.Diagnostics.Process.Start(“toHTML.html”);
        }
    }
}

Result Shown as Following:

___________________________________________________________________________________________

Click Here to LEARN MORE about Spire.XLS

Click Here to DOWNLOAD Spire.XLS

How to Insert Rows and Columns in Worksheet with C#

Although the data has been imported to an Excel worksheet, sometimes, we need to insert rows or columns for writing additional data information. For example, there is an Excel file about employees contact information with several groups, each of which shows information in one department. If a new employee joins one department, we need to insert a new row in the corresponding group and writing his/her contact information.

This post focuses on how to insert rows and columns in Excel worksheet with C#. Because Spire.XLS for .NET is used in this example, so please download and install it on your system at the beginning. Then, add necessary reference, Spire.XLS dll file and System.Drawing.

STEPS

  1. Load file which we want to insert rows and columns and initialize Worksheet.
  2. Insert row with worksheet.InsertRow(). If we just want to add one row, pass one parameter to this method. This parameter sepcifies where to insert the row.
  3. Copy contents from other rows to new rows by using worksheet.Copy(). There are three parameters in this method, the source range and a bool value.
  4. Save and launch file.

Main Coding:

using System;
using System.Drawing;
using Spire.Xls;

namespace InsertRowandColumn
{
    class InsertRC
    {
        static void Main(string[] args)
        {

            //Load File and Initialize Worksheet
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@”D:\Work\My Documents\Student Info.xlsx”);
            Worksheet worksheet = workbook.Worksheets[0];

            //Insert Blank Row
            worksheet.InsertRow(3);

            //Insert Row with Copying Data from Other Rows
            worksheet.InsertRow(13, 2);
            worksheet.Copy(worksheet.Range[“A11:E12”],worksheet.Range[“A13:E14”],true);

            //Set Style for New Rows
            worksheet.Range[“A13:E14”].Style.Color = Color.LightCyan;
            worksheet.Range[“A13:E14”].Style.Font.IsBold = true;

            //Insert Blank Column
            worksheet.InsertColumn(1);

            //Insert Column with Copying Data from Other Columns
            worksheet.InsertColumn(7, 2);
            worksheet.Copy(worksheet.Range[“D1:E21”], worksheet.Range[“G1:H21”], true);

            //Set Style for New Columns
            worksheet.Range[“G1:H21”].Style.Font.IsBold = true;
            worksheet.Range[“G1:H21”].Style.Color = Color.LightCyan;

            //Save and Launch File
            workbook.SaveToFile(“InsertRC.xlsx”,ExcelVersion.Version2010);
            System.Diagnostics.Process.Start(workbook.FileName);
        }
    }
}

Result Shown As Following:

Freely Download Spire.XLS for .NET

How to Set Group by Rows and Columns in Excel with C#

Introduction

Excel Group is used to classify data information with categories. If information in a workbook has been divided into several parts, we can form each part as a group. For example, each product information will be set as a group in a sales report. 

Generally speaking, group is created by rows or columns. Therefore, it is very important to confirm data range before creating group. Also,  data in one group can be hide or display. After setting, we can find a “-” symbol and the group data is presented. Click it. It will become “+” and the data is hidden.

In this post, I will show the method about how to create Excel group according to data information by using C#. 

Background

In my example, I have an Excel file which saves personal information of vip customers. I will separate information from two groups based on rows and columns. The following steps show details.

And, Spire.XLS, a component to operate Excel for .Net, so I add its dll file as reference in project.

Steps

  1. Load file which I want to create group.
  2. Initialize worksheet. In order to make the appearance be more wonderful, set the grid lines as invisible.
  3. Create Groups. There are two methods, GroupByRows, GroupByColumns. For each method, we need to pass three parameters: first row/column, last row/column and a bool value which defines if the group will be folded.
  4. Save and Launch file.

Main Coding

using System;
using Spire.Xls;

namespace Excel_Group
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@”D:\Work\My Documents\VIP.xlsx”);

            //Initialize worksheet
            Worksheet sheet = workbook.Worksheets[0];
            sheet.GridLinesVisible = false;

            //Set Group by Rows and Columns
            sheet.GroupByRows(1, 10, false);
            sheet.GroupByColumns(1, 5, false);

            //Save and Launch File
            workbook.SaveToFile(“Group.xlsx”,ExcelVersion.Version2010);
            System.Diagnostics.Process.Start(workbook.FileName);
        }
    }
}

Result Shown by Following:

_____________________________________________________________________________________________

Click Here to LEARN MORE about Spire.XLS

Click Here to DOWNLOAD Spire.XLS