using NPOI.HPSF; |
using NPOI.SS.UserModel; |
using NPOI.HSSF.UserModel; |
namespace RExcelLib |
{ |
public class ExcelHleper |
{ |
/* |
将Excel中的内容当作纯数据读取,无表说明 |
*/ |
static public DataTable ReadAsPureData( string fileName, int feetPage) |
{ |
ISheet sheet; |
sheet = GetSheet(fileName, feetPage); |
|
System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); |
using ( DataTable dataTable = new DataTable()) |
{ |
for ( int j = 0; j < (sheet.GetRow(0).LastCellNum); j++) |
{ |
dataTable.Columns.Add( "A" + j.ToString()); |
} |
while (rows.MoveNext()) |
{ |
HSSFRow row = (HSSFRow)rows.Current; |
DataRow dr = dataTable.NewRow(); |
for ( int i = 0; i < row.LastCellNum; i++) |
{ |
var cell = row.GetCell(i); |
if (cell == null ) |
{ |
dr[i] = null ; |
} |
else |
{ |
dr[i] = cell.ToString(); |
} |
} |
dataTable.Rows.Add(dr); |
} |
return dataTable; |
} |
} |
static public DataTable ReadDataFirsLineAsHead( string fileName, int feetPage, bool isAbandonHead = true ) |
{ |
ISheet sheet; |
sheet = GetSheet(fileName, feetPage); |
System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); |
using (DataTable dataTable = new DataTable()) |
{ |
var headRow = sheet.GetRow(0); |
for ( int j = 0; j < headRow.LastCellNum; j++) |
{ |
var headName = headRow.Cells.ElementAt(j); |
dataTable.Columns.Add(headName.ToString()); |
} |
if (isAbandonHead) |
{ |
rows.MoveNext(); |
|
} |
|
while (rows.MoveNext()) |
{ |
HSSFRow row = (HSSFRow)rows.Current; |
DataRow dr = dataTable.NewRow(); |
|
for ( int i = 0; i < row.LastCellNum; i++) |
{ |
var cell = row.GetCell(i); |
if (cell == null ) |
{ |
dr[i] = null ; |
} |
else |
{ |
dr[i] = cell.ToString(); |
} |
} |
dataTable.Rows.Add(dr); |
} |
return dataTable; |
} |
} |
private static ISheet GetSheet( string fileName, int feetPage) |
{ |
ISheet sheet; |
try |
{ |
using (FileStream fs = File.OpenRead(fileName)) |
{ |
var wk = new HSSFWorkbook(fs); |
sheet = wk.GetSheetAt(feetPage); |
} |
} |
catch (Exception e) |
{ |
throw e; |
} |
return sheet; |
} |
|
} |
} |