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?????????????????????????????dt.Columns.Add（Convert.ToChar（（（int）‘A‘）?+?Columns）.ToString（））;
????????????????????????/*?Rows?Value?Written?*/
????????????????????????for?（int?i?=?0;?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