直接上代码
首先定义一个接口
1
public
interface
ITransferData
2
{
3
Stream GetStream(DataTable table);
4
DataTable GetData(Stream stream);
5
}
如果需要直接操作文件的话，就自己在封装一次
然后定义csv类的具体实现
public
class
CsvTransferData : ITransferData
{
private
Encoding _encode;
public
CsvTransferData()
{
this
._encode = Encoding.GetEncoding(
"
utf-8
"
);
}
public
Stream GetStream(DataTable table)
{
StringBuilder sb
=
new
StringBuilder();
if
(table !=
null
&& table.Columns.Count >
0
&& table.Rows.Count >
0
)
{
foreach
(DataRow item
in
table.Rows)
{
for
(
int
i =
0
; i < table.Columns.Count; i++
)
{
if
(i >
0
)
{
sb.Append(
"
,
"
);
}
if
(item[i] !=
null
)
{
sb.Append(
"
\"
"
).Append(item[i].ToString().Replace(
"
\"
"
,
"
\"\"
"
)).Append(
"
\"
"
);
}
}
sb.Append(
"
\n
"
);
}
}
MemoryStream stream
=
new
MemoryStream(_encode.GetBytes(sb.ToString()));
return
stream;
}
public
DataTable GetData(Stream stream)
{
using
(stream)
{
using
(StreamReader input =
new
StreamReader(stream, _encode))
{
using
(CsvReader csv =
new
CsvReader(input,
false
))
{
DataTable dt
=
new
DataTable();
int
columnCount =
csv.FieldCount;
for
(
int
i =
0
; i < columnCount; i++
)
{
dt.Columns.Add(
"
col
"
+
i.ToString());
}
while
(csv.ReadNextRecord())
{
DataRow dr
=
dt.NewRow();
for
(
int
i =
0
; i < columnCount; i++
)
{
if
(!
string
.IsNullOrWhiteSpace(csv[i]))
{
dr[i]
=
csv[i];
}
}
dt.Rows.Add(dr);
}
return
dt;
}
}
}
}
}
这个需要引入命名空间LumenWorks.Framework.IO.Csv;
可以Nuget里面下
接着定义excel的实现类的基类
public
abstract
class
ExcelTransferData : ITransferData
{
protected
IWorkbook _workBook;
public
virtual
Stream GetStream(DataTable table)
{
var
sheet =
_workBook.CreateSheet();
if
(table !=
null
)
{
var
rowCount =
table.Rows.Count;
for
(
int
i =
0
; i < table.Rows.Count; i++
)
{
var
row =
sheet.CreateRow(i);
for
(
int
j =
0
; j < table.Columns.Count; j++
)
{
var
cell =
row.CreateCell(j);
if
(table.Rows[i][j] !=
null
)
{
cell.SetCellValue(table.Rows[i][j].ToString());
}
}
}
}
MemoryStream ms
=
new
MemoryStream();
_workBook.Write(ms);
return
ms;
}
public
virtual
DataTable GetData(Stream stream)
{
using
(stream)
{
var
sheet = _workBook.GetSheetAt(
0
);
if
(sheet !=
null
)
{
var
headerRow = sheet.GetRow(
0
);
DataTable dt
=
new
DataTable();
int
columnCount =
headerRow.Cells.Count;
for
(
int
i =
0
; i < columnCount; i++
)
{
dt.Columns.Add(
"
col_
"
+
i.ToString());
}
var
row =
sheet.GetRowEnumerator();
while
(row.MoveNext())
{
var
dtRow =
dt.NewRow();
var
excelRow = row.Current
as
IRow;
for
(
int
i =
0
; i < columnCount; i++
)
{
var
cell =
excelRow.GetCell(i);
if
(cell !=
null
)
{
dtRow[i]
=
GetValue(cell);
}
}
dt.Rows.Add(dtRow);
}
return
dt;
}
}
return
null
;
}
private
object
GetValue(ICell cell)
{
object
value =
null
;
switch
(cell.CellType)
{
case
CellType.BLANK:
break
;
case
CellType.BOOLEAN:
value
= cell.BooleanCellValue ?
"
1
"
:
"
0
"
;
break
;
case
CellType.ERROR:
value
= cell.ErrorCellValue;
break
;
case
CellType.FORMULA:
value
=
"
=
"
+ cell.CellFormula;
break
;
case
CellType.NUMERIC:
value
= cell.NumericCellValue.ToString();
break
;
case
CellType.STRING:
value
= cell.StringCellValue;
break
;
case
CellType.Unknown:
break
;
}
return
value;
}
}
这个需要下载NPOI 2.01版本
下载地址 http://npoi.codeplex.com/releases/view/92382
接着实现2003版本
public
class
XlsTransferData : ExcelTransferData
{
public
override
Stream GetStream(DataTable table)
{
base
._workBook =
new
HSSFWorkbook();
return
base
.GetStream(table);
}
public
override
DataTable GetData(Stream stream)
{
base
._workBook =
new
HSSFWorkbook(stream);
return
base
.GetData(stream);
}
}
接着实现2007版本
public
class
XlsxTransferData : ExcelTransferData
{
public
override
Stream GetStream(DataTable table)
{
base
._workBook =
new
XSSFWorkbook();
return
base
.GetStream(table);
}
public
override
DataTable GetData(Stream stream)
{
base
._workBook =
new
XSSFWorkbook(stream);
return
base
.GetData(stream);
}
}
然后定义一个枚举实现一个简单的工厂
public
enum
DataFileType
{
CSV,
XLS,
XLSX
}
public
class
TransferDataFactory
{
public
static
ITransferData GetUtil(
string
fileName)
{
var
array = fileName.Split(
'
.
'
);
var
dataType = (DataFileType)Enum.Parse(
typeof
(DataFileType), array[array.Length -
1
],
true
);
return
GetUtil(dataType);
}
public
static
ITransferData GetUtil(DataFileType dataType)
{
switch
(dataType)
{
case
DataFileType.CSV:
return
new
CsvTransferData();
case
DataFileType.XLS:
return
new
XlsTransferData();
case
DataFileType.XLSX:
return
new
XlsxTransferData();
default
:
return
new
CsvTransferData();
}
}
}
客户端调用代码
class
Program
{
static
void
Main(
string
[] args)
{
var
fileName =
@"
C:/Users/ranrx/Desktop/data.xlsx
"
;
FileStream stream
=
new
FileStream(fileName, FileMode.Open, FileAccess.Read);
var
util =
TransferDataFactory.GetUtil(fileName);
var
data =
util.GetData(stream);
var
mStream =
util.GetStream(data);
}
}
