Skip to main content

Read and Write Excel Files without MS Office in C#

 

Read or write Excel files are common tasks for developers in office document development. In this article, I am going to share a simple approach to read and write Excel files in C# by using Free Spire.XLS for .NET API.

Microsoft Office is not needed

Free Spire.XLS for .NET is a free and independent API for creating, reading, writing, converting and printing Excel files in both XLS (Excel 97-2003) and XLSX (Excel 2007 and above) formats. It has no dependency on Microsoft Office, in other words, you can use it to read and write Excel files without installing Microsoft Office in your system.

Get Free Spire.XLS for .NET

You can either download Free Spire.XLS DLLs from this website or install Free Spire.XLS using NuGet Package Manager through the following steps:

  • In your project’s Solution Explorer, right-click the project or “Dependencies” and select “Manage NuGet Packages”.
  • Click “Browse” tab and search Free Spire.XLS.
  • Install Free Spire.XLS.

Write Excel Files

You can generate an Excel file by writing data cell to cell or by importing data from other data sources such as DataTable, DataView, DataColumn, Array, Array List etc.

Write data cell-by-cell

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
using Spire.Xls;
using System;
 
namespace WriteExcelFiles
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Workbook instance
            Workbook workbook = new Workbook();
            //Add a worksheet
            Worksheet sheet = workbook.Worksheets[0];
 
            //Add text to the worksheet
            sheet.Range["A1"].Text = "ID";
            sheet.Range["B1"].Text = "Name";
            sheet.Range["C1"].Text = "Birthday";
            sheet.Range["D1"].Text = "Age";
 
            //Add number to the worksheet
            sheet.Range["A2"].NumberValue = 1;
            sheet.Range["A3"].NumberValue = 2;
            sheet.Range["A4"].NumberValue = 3;
 
            //Add text to the worksheet
            sheet.Range["B2"].Text = "John";
            sheet.Range["B3"].Text = "Glen";
            sheet.Range["B4"].Text = "Amy";
 
            //Add date time to the worksheet
            sheet.Range["C2"].DateTimeValue = new DateTime(1989, 9, 12);
            sheet.Range["C3"].DateTimeValue = new DateTime(1976, 3, 20);
            sheet.Range["C4"].DateTimeValue = new DateTime(1996, 10, 29);
 
            //Add formula to the worksheet
            sheet.Range["D2"].Formula = "=INT(YEARFRAC(C2, TODAY()))";
            sheet.Range["D3"].Formula = "=INT(YEARFRAC(C3, TODAY()))";
            sheet.Range["D4"].Formula = "=INT(YEARFRAC(C4, TODAY()))";
 
 
            //Set column width
            for (int col = 1; col <= sheet.LastColumn; col++)
            {
                sheet.Columns[col].ColumnWidth = 12;
            }
             
            //Save the result file
            workbook.SaveToFile("WriteExcel.xlsx", FileFormat.Version2013);
        }      
    }
}

Write data from DataTable

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
using Spire.Xls;
using System.Data;
 
namespace ReadExcelFiles
{
    class Program
    {
        static void Main(string[] args)
        {
            //Load an Excel file
            Workbook workbook = new Workbook();
            workbook.LoadFromFile("WriteExcel.xlsx");
            //Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];
            //Import data from DataTable to Excel
            sheet.InsertDataTable(GetDataTable(), true, 1, 1);
 
            //Save the result file
            workbook.SaveToFile("WriteDataTableToExcel.xlsx", ExcelVersion.Version2013);
        }
 
        private static DataTable GetDataTable()
        {
            DataTable country = new DataTable();
            country.Columns.Add("Name");
            country.Columns.Add("Capital");
            country.Columns.Add("Continent");
            country.Columns.Add("Area");
            country.Columns.Add("Population");
 
            country.Rows.Add("Argentina", "Buenos Aires", "South America", "2777815", "32300003");
            country.Rows.Add("Bolivia", "La Paz", "South America", "1098575", "7300000");
            country.Rows.Add("Brazil", "Brasilia", "South America", "8511196", "150400000");
            country.Rows.Add("Canada", "Ottawa", "North America", "9976147", "26500000");
            country.Rows.Add("Chile", "Santiago", "South America", "756943", "13200000");
 
            return country;
        }
    }
}

Read Excel files

You can also read Excel data by cell-to-cell iteration or read Excel data to a DataTable as shown in the following examples.

Read Excel by cell-to-cell iteration      

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
using Spire.Xls;
using System;
 
namespace ReadExcelFiles
{
    class Program
    {
        static void Main(string[] args)
        {
            //Load an Excel file
            Workbook workbook = new Workbook();
            workbook.LoadFromFile("sample.xlsx");
            //Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];
 
            int maxRow = sheet.LastRow;
            int maxColumn = sheet.LastColumn;
 
            //Read data by cell-to-cell iteration
            for (int row = 1; row <= maxRow; row++)
            {
                for (int col = 1; col <= maxColumn; col++)
                {
                    Console.Write(sheet[row, col].Value);
                    Console.Write("\t\t");
                }
                Console.Write("\n");
            }
 
            Console.ReadKey();
        }
    }
}

Read Excel to DataTable

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
using Spire.Xls;
using System;
using System.Data;
 
namespace ReadExcelFiles
{
    class Program
    {
        static void Main(string[] args)
        {
            //Load an Excel file
            Workbook workbook = new Workbook();
            workbook.LoadFromFile("sample.xlsx");
            //Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];
 
            //Export to DataTable
            DataTable dt = sheet.ExportDataTable();
        }
    }
}
Labels:

Comments

Post a Comment

Popular posts from this blog

3 Ways to Generate Word Documents from Templates in Java

A template is a document with pre-applied formatting like styles, tabs, line spacing and so on. You can quickly generate a batch of documents with the same structure based on the template. In this article, I am going to show you the different ways to generate Word documents from templates programmatically in Java using Free Spire.Doc for Java library. Prerequisite First of all, you need to add needed dependencies for including Free Spire.Doc for Java into your Java project. There are two ways to do that. If you use maven, you need to add the following code to your project’s pom.xml file. <repositories>               <repository>                   <id>com.e-iceblue</id>                   <name>e-iceblue</name>                   <url>http: //repo.e-iceblue.com/nexus/content/groups/public/</url>                </repository>       </repositories>       <dependencies>           <dependency>               <g
Post a Comment
Read more

Simple Java Code to Convert Excel to PDF in Java

This article demonstrates a simple solution to convert an Excel file to PDF in Java by using free Excel API – Free Spire.XLS for Java . The following examples illustrate two possibilities to convert Excel to PDF:      Convert the whole Excel file to PDF     Convert a particular Excel Worksheet to PDF Before start with coding, you need to Download Free Spire.XLS for Java package , unzip it and import Spire.Xls.jar file from the lib folder in your project as a denpendency. 1. Convert the whole Excel file to PDF Spire.XLS for Java provides saveToFile method in Workbook class that enables us to easily save a whole Excel file to PDF. import com.spire.xls.FileFormat; import com.spire.xls.Workbook; public class ExcelToPDF {     public static void main(String[] args){         //Create a Workbook         Workbook workbook = new Workbook();         workbook.loadFromFile( "Sample.xlsx" );         //Fit to page         workbook.getConverterSetting().setShee
Post a Comment
Read more

Insert and Extract OLE objects in Word in Java

You can use OLE (Object Linking and Embedding) to include content from other programs, such as another Word document, an Excel or PowerPoint document to an existing Word document. This article demonstrates how to insert and extract embedded OLE objects in a Word document in Java by using Free Spire.Doc for Java API.   Add dependencies First of all, you need to add needed dependencies for including Free Spire.Doc for Java into your Java project. There are two ways to do that. If you use maven, you need to add the following code to your project’s pom.xml file.     <repositories>               <repository>                   <id>com.e-iceblue</id>                   <name>e-iceblue</name>                   <url>http://repo.e-iceblue.com/nexus/content/groups/public/</url>               </repository>       </repositories>       <dependencies>           <dependency>               <groupId>
Post a Comment
Read more