NPOI操作C# ExcelToDataTable DataTableToExcel

1 Use VS2010 C# WinForm 2 Use NPOI 2 1 3 net2 3 ExcelToDataTable and DataTableToExcel

using System;
using System.Data;
using System.IO;
using NPOI.HSSF.UserModel;///Excel 2003: *.xls
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;///Excel 2007: *.xlsx

namespace ExcelHelper
{
    /// 

    ///  Written by Marvin at date:2015/2/7
    /// 

    public class ExcelClass
    {
        //private DataSet ds = new DataSet（）;
        public DataTable ExcelToDataTable（string path）
        {
            try
            {
                DataTable dt = new DataTable（）;
                using （FileStream fs = new FileStream（path FileMode.Open FileAccess.Read））
                {
                    /* Initialize Workbook */
                    IWorkbook workbook = null;
                    string ExtensionName = Path.GetExtension（path）.ToLower（）;
                    if （ExtensionName == “.xlsx“）
                        workbook = new XSSFWorkbook（fs）;
                    else if （ExtensionName == “.xls“）
                        workbook = new HSSFWorkbook（fs）;
                    /* Convert To DataTable */
                    ISheet sheet = workbook.GetSheetAt（0）;
                    System.Collections.IEnumerator rows = sheet.GetRowEnumerator（）;
                    int Columns = 0;
                    while （rows.MoveNext（））
                    {
                        IRow row = （IRow）rows.Current;
                        DataRow dr = dt.NewRow（）;
                        /* Columns Name Written */
                        for （; Columns < row.LastCellNum; Columns++）
                            dt.Columns.Add（Convert.ToChar（（（int）‘A‘） + Columns）.ToString（））;
                        /* Rows Value Written */
                        for （int i = 0; i < row.LastCellNum; i++）
                        {
                            ICell cell = row.GetCell（i）;
                            dr[i] = （cell == null） ? null : cell.ToString（）;
                        }
                        dt.Rows.Add（dr）;
                    }
                    //ds.Reset（）;///If you don‘t add this command each event will add a new table
                    //ds.Tables.Add（dt）;///You can choose to return a DataSet
                }
                return dt;
            }
            catch
            {
                /**********************************
                 * 1.This file has been opened.
                 * 2.Incompatible format.
                **********************************/
                return null;
            }
        }

        public bool DataTableToExcel（string path string SheetName DataTable dt bool ColumnWritten）
        {
            if （dt != null）
            {
                try
                {
                    using （FileStream fs = new FileStream（path FileMode.OpenOrCreate FileAccess.ReadWrite））
                    {
                        /* Initialize Workbook */
                        IWorkbook workbook = null;
            

 属性            大小     日期    时间   名称
----------- ---------  ---------- -----  ----
     目录           0  2015-02-07 13:25  ExcelFileLoadAndSave\
     目录           0  2015-02-07 13:25  ExcelFileLoadAndSave\ExcelFileLoadAndSave\
     文件         902  2015-02-06 09:28  ExcelFileLoadAndSave\ExcelFileLoadAndSave.sln
     文件       24064  2015-02-07 13:23  ExcelFileLoadAndSave\ExcelFileLoadAndSave.suo
     目录           0  2015-02-07 13:25  ExcelFileLoadAndSave\ExcelFileLoadAndSave\dotnet2\
     文件      200704  2014-07-03 10:56  ExcelFileLoadAndSave\ExcelFileLoadAndSave\dotnet2\ICSharpCode.SharpZipLib.dll
     文件     1602560  2014-12-11 05:49  ExcelFileLoadAndSave\ExcelFileLoadAndSave\dotnet2\NPOI.dll
     文件      421376  2014-12-11 05:50  ExcelFileLoadAndSave\ExcelFileLoadAndSave\dotnet2\NPOI.OOxml.dll
     文件       85504  2014-12-11 05:50  ExcelFileLoadAndSave\ExcelFileLoadAndSave\dotnet2\NPOI.Openxml4Net.dll
     文件     1871872  2014-12-11 05:50  ExcelFileLoadAndSave\ExcelFileLoadAndSave\dotnet2\NPOI.OpenxmlFormats.dll
     文件       67646  2015-02-07 11:36  ExcelFileLoadAndSave\ExcelFileLoadAndSave\excel.ico
     目录           0  2015-02-07 13:25  ExcelFileLoadAndSave\ExcelFileLoadAndSave\ExcelData\
     文件       26624  2015-02-07 11:50  ExcelFileLoadAndSave\ExcelFileLoadAndSave\ExcelData\MyTest.xls
     文件        9907  2015-02-07 11:50  ExcelFileLoadAndSave\ExcelFileLoadAndSave\ExcelData\MyTest.xlsx
     文件        4261  2015-02-07 11:56  ExcelFileLoadAndSave\ExcelFileLoadAndSave\ExcelFileLoadAndSave.csproj
     文件        4902  2015-02-07 11:49  ExcelFileLoadAndSave\ExcelFileLoadAndSave\ExcelHelper.cs
     文件        2990  2015-02-07 13:19  ExcelFileLoadAndSave\ExcelFileLoadAndSave\frmExcelFileLoadAndSave.cs
     文件        3943  2015-02-07 11:42  ExcelFileLoadAndSave\ExcelFileLoadAndSave\frmExcelFileLoadAndSave.Designer.cs
     文件        5817  2015-02-07 11:54  ExcelFileLoadAndSave\ExcelFileLoadAndSave\frmExcelFileLoadAndSave.resx
     文件         482  2015-02-07 11:08  ExcelFileLoadAndSave\ExcelFileLoadAndSave\Program.cs
     目录           0  2015-02-07 13:25  ExcelFileLoadAndSave\ExcelFileLoadAndSave\Properties\
     文件        1359  2015-02-07 11:49  ExcelFileLoadAndSave\ExcelFileLoadAndSave\Properties\AssemblyInfo.cs
     文件        2927  2015-02-06 09:27  ExcelFileLoadAndSave\ExcelFileLoadAndSave\Properties\Resources.Designer.cs
     文件        5612  2015-02-06 09:27  ExcelFileLoadAndSave\ExcelFileLoadAndSave\Properties\Resources.resx
     文件        1107  2015-02-06 09:27  ExcelFileLoadAndSave\ExcelFileLoadAndSave\Properties\Settings.Designer.cs
     文件         249  2015-02-06 09:27  ExcelFileLoadAndSave\ExcelFileLoadAndSave\Properties\Settings.settings