NPOI读写Excel

1、整个Excel表格叫做工作表：WorkBook（工作薄），包含的叫页（工作表）：Sheet；行：Row；单元格Cell。

2、NPOI是POI的C#版本，NPOI的行和列的index都是从0开始

3、POI读取Excel有两种格式一个是HSSF，另一个是XSSF。 HSSF和XSSF的区别如下：
HSSF is the POI Project's pure Java implementation of the Excel '97(-2007) file format.
XSSF is the POI Project's pure Java implementation of the Excel 2007 OOXML (.xlsx) file format.
即：HSSF适用2007以前的版本,XSSF适用2007版本及其以上的。

下面是用NPOI读写Excel的例子：ExcelHelper封装的功能主要是把DataTable中数据写入到Excel中，或者是从Excel读取数据到一个DataTable中。

 ExcelHelper类：

按 Ctrl+C 复制代码

按 Ctrl+C 复制代码

测试代码：

View Code

签于这篇文章阅读量较高，更新一下我使用Aspose.Cells的另一个版本：

PS：Aspose是要收费的

using System;using System.Collections.Generic;using System.Data;using System.IO;using System.Linq;using System.Text;using Aspose.Cells;namespace NetUtilityLib{    public static class ExcelHelper    {        public static int DataTableToExcel(DataTable data, string fileName, string sheetName, bool isColumnNameWritten)        {            int num = -1;            try            {                Workbook workBook;                Worksheet worksheet = null;                if (File.Exists(fileName))                    workBook = new Workbook(fileName);                else                    workBook = new Workbook();                if (sheetName == null)                {                    if (workBook.Worksheets.Count > 0)                    {                        worksheet = workBook.Worksheets[0];                    }                    else                    {                        sheetName = "Sheet1";                        workBook.Worksheets.RemoveAt(sheetName);                        worksheet = workBook.Worksheets.Add(sheetName);                    }                }                if (worksheet != null)                {                    worksheet.Cells.Clear();                    num = worksheet.Cells.ImportDataTable(data, isColumnNameWritten, 0, 0, false);                    workBook.Save(fileName);                }            }            catch (Exception ex)            {                Console.WriteLine(ex.Message);            }            return num;        }        public static void AddOneRowToExcel(DataRow dataRow, string fileName, string sheetName)        {            try            {                Workbook workBook;                if (File.Exists(fileName))                    workBook = new Workbook(fileName);                else                    workBook = new Workbook();                Worksheet worksheet=null;                if (sheetName == null)                {                    worksheet = workBook.Worksheets[0];                }                else                {                    worksheet = workBook.Worksheets[sheetName];                }                if (worksheet != null)                {                    worksheet.Cells.ImportDataRow(dataRow, worksheet.Cells.MaxDataRow + 1,0);                    //worksheet.Cells.ImportArray(dataArray, worksheet.Cells.MaxDataRow+1, 0, false);                    workBook.Save(fileName);                }            }            catch (Exception ex)            {                Console.WriteLine(ex.Message);            }        }        public static DataTable ExcelToDataTable(string fileName, string sheetName, bool isFirstRowColumnName)        {            DataTable data = new DataTable();            try            {                Workbook workbook = null;                FileInfo fileInfo = new FileInfo(fileName);                if (fileInfo.Extension.ToLower().Equals(".xlsx"))                    workbook = new Workbook(fileName, new LoadOptions(LoadFormat.Xlsx));                else if (fileInfo.Extension.ToLower().Equals(".xls"))                    workbook = new Workbook(fileName, new LoadOptions(LoadFormat.Excel97To2003));                if (workbook != null)                {                    Worksheet worksheet = null;                    if (sheetName != null)                    {                        worksheet = workbook.Worksheets[sheetName];                    }                    else                    {                        worksheet = workbook.Worksheets[0];                    }                    if (worksheet != null)                    {                        data = worksheet.Cells.ExportDataTable(0, 0, worksheet.Cells.MaxRow+1, worksheet.Cells.MaxColumn+1,                            isFirstRowColumnName);                        return data;                    }                }                else                {                    return data;                }            }            catch (Exception ex)            {                Console.WriteLine(ex.Message);            }            return data;        }    }}

 

Excel相关DLL下载：NPOI-Lib.rar

 

1.NPOI下载地址：http://npoi.codeplex.com/releases/view/38113

2.NPOI学习系列教程推荐：http://www.cnblogs.com/tonyqus/archive/2009/04/12/1434209.html

 

参考：

http://www.cnblogs.com/Erik_Xu/archive/2012/06/08/2541957.html

http://www.cnblogs.com/linzheng/archive/2010/12/20/1912137.html

http://www.cnblogs.com/knowledgesea/archive/2012/11/16/2772547.html