C# Excel读写的通用(泛型)代码
转载请注明出处
本文实现了两种两种方式:COM和NPOI,其中COM库需要安装excel(即office),NPOI建议通过NuGet下载
COM相关的动态库:Microsoft.Office.Interop.Excel.dll
NPOI相关的动态库:ICSharpCode.SharpZipLib.dll,NPOI.dll, NPOI.OOXML.dll,NPOI.OpenXml4Net.dll,NPOI.OpenXmlFormats.dll
推荐使用NPOI方式,直接上代码,话不多说!!
using System;
using System. Collections. Generic;
using System. Linq;
using System. Text;
using System. Threading. Tasks;
using System. Data;
using System. IO;
using System. Windows. Forms;
using Microsoft. Office. Interop. Excel;
using System. Reflection;
using NPOI. SS. UserModel;
using NPOI. HSSF. UserModel;
using NPOI. XSSF. UserModel;
using System. Runtime. InteropServices;
using System. Diagnostics;
namespace Excel
{
public static class ExcelHelper
{
[ DllImport ( "User32.dll" , CharSet = CharSet. Auto) ]
public static extern int GetWindowThreadProcessId ( IntPtr hwnd, out int ID) ;
public static IList< T> TableToList < T > ( this System. Data. DataTable dt) where T : new ( )
{
List< T> result = new List < T > ( ) ;
var properties = typeof ( T) . GetProperties ( ) ;
foreach ( DataRow row in dt. Rows)
{
T t = new T ( ) ;
foreach ( var p in properties)
{
if ( dt. Columns. Contains ( p. Name) )
{
object value = row[ p. Name] ;
if ( value == DBNull. Value)
continue ;
if ( p. PropertyType. Name. Contains ( "Nullable" ) )
value = Convert. ChangeType ( value , Nullable. GetUnderlyingType ( p. PropertyType) ) ;
p. SetValue ( t, value , null ) ;
}
}
result. Add ( t) ;
}
return result;
}
public static void ListToExcel_COM < T > ( IList< T> data, string filePath) where T : new ( )
{
T t = new T ( ) ;
var type = t. GetType ( ) ;
var properties = type. GetProperties ( ) ;
StringBuilder str = new StringBuilder ( ) ;
#region 添加列标题
foreach ( var p in properties)
{
string columnName = p. Name;
var ignoreAtt = p. GetCustomAttributes ( typeof ( IgnoreColumnAttribute) , true ) ;
if ( ignoreAtt. Count ( ) != 0 )
continue ;
var columnNameAtt = p. GetCustomAttributes ( typeof ( ColumnNameAttribute) , true ) ;
if ( columnNameAtt. Count ( ) != 0 )
{
columnName = ( ( ColumnNameAttribute) columnNameAtt[ 0 ] ) . ColumnName;
}
str. Append ( columnName + "\t" ) ;
}
#endregion
str. Append ( Environment. NewLine) ;
#region 添加数据
foreach ( T row in data)
{
foreach ( var p in properties)
{
var ignoreAtt = p. GetCustomAttributes ( typeof ( IgnoreColumnAttribute) , true ) ;
if ( ignoreAtt. Count ( ) != 0 )
continue ;
var value = p. GetValue ( row) ;
str. Append ( value + "\t" ) ;
}
str. Append ( Environment. NewLine) ;
}
#endregion
if ( File. Exists ( filePath) )
File. Delete ( filePath) ;
Clipboard. SetText ( str. ToString ( ) ) ;
Microsoft. Office. Interop. Excel. Application xApp = new Microsoft. Office. Interop. Excel. Application ( ) ;
if ( xApp == null )
return ;
Workbooks workbooks = xApp. Workbooks;
Workbook book = workbooks. Add ( Missing. Value) ;
Worksheet sheet = ( Worksheet) book. Sheets[ 1 ] ;
sheet. Paste ( ) ;
book. SaveAs ( filePath, XlFileFormat. xlWorkbookDefault, Missing. Value, Missing. Value, Missing. Value, Missing. Value,
XlSaveAsAccessMode. xlExclusive, Missing. Value, Missing. Value, Missing. Value, Missing. Value, Missing. Value) ;
book. Close ( Missing. Value, Missing. Value, Missing. Value) ;
Marshal. ReleaseComObject ( book) ;
book = null ;
xApp. DisplayAlerts = false ;
xApp. Quit ( ) ;
KillExcel ( xApp) ;
Marshal. ReleaseComObject ( xApp) ;
xApp = null ;
GC. Collect ( ) ;
}
public static void ListToExcel_NPOI < T > ( IList< T> data, string filePath) where T : new ( )
{
IWorkbook workBook;
if ( filePath. Contains ( ".xlsx" ) )
workBook = new XSSFWorkbook ( ) ;
else
workBook = new HSSFWorkbook ( ) ;
ISheet sheet = workBook. CreateSheet ( "Sheet0" ) ;
T t = new T ( ) ;
var type = t. GetType ( ) ;
var properties = type. GetProperties ( ) ;
int columnIndex = 0 ;
#region 添加列标题
var columnNameRow = sheet. CreateRow ( 0 ) ;
foreach ( var p in properties)
{
string columnName = p. Name;
var ignoreAtt = p. GetCustomAttributes ( typeof ( IgnoreColumnAttribute) , true ) ;
if ( ignoreAtt. Count ( ) != 0 )
continue ;
var columnNameAtt = p. GetCustomAttributes ( typeof ( ColumnNameAttribute) , true ) ;
if ( columnNameAtt. Count ( ) != 0 )
{
columnName = ( ( ColumnNameAttribute) columnNameAtt[ 0 ] ) . ColumnName;
}
var columnNameCell = columnNameRow. CreateCell ( columnIndex++ ) ;
columnNameCell. SetCellValue ( columnName. ToString ( ) ) ;
}
#endregion
int rowIndex = 1 ;
#region 添加数据
foreach ( T row in data)
{
var dataRow = sheet. CreateRow ( rowIndex++ ) ;
columnIndex = 0 ;
foreach ( var p in properties)
{
var ignoreAtt = p. GetCustomAttributes ( typeof ( IgnoreColumnAttribute) , true ) ;
if ( ignoreAtt. Count ( ) != 0 )
continue ;
var value = p. GetValue ( row) ;
var dataCell = dataRow. CreateCell ( columnIndex++ ) ;
if ( value != null )
dataCell. SetCellValue ( value . ToString ( ) ) ;
}
}
#endregion
using ( FileStream stream = new FileStream ( filePath, FileMode. Create, FileAccess. ReadWrite) )
{
workBook. Write ( stream) ;
}
}
public static IList< T> ExcelToList_COM < T > ( string filePath, bool isExistsColumnName = true ) where T : new ( )
{
if ( ! File. Exists ( filePath) )
return null ;
List< T> result = null ;
Microsoft. Office. Interop. Excel. Application xApp = new Microsoft. Office. Interop. Excel. Application ( ) ;
Workbook workBook = xApp. Workbooks. Open ( filePath, Missing. Value, Missing. Value, Missing. Value, Missing. Value,
Missing. Value, Missing. Value, Missing. Value, Missing. Value, Missing. Value, Missing. Value, Missing. Value,
Missing. Value, Missing. Value) ;
if ( workBook != null )
{
Worksheet sheet = ( Worksheet) workBook. Worksheets. Item[ 1 ] ;
if ( sheet != null )
{
result = new List < T > ( ) ;
var properties = typeof ( T) . GetProperties ( ) ;
int rowIndex = 2 ;
if ( ! isExistsColumnName)
rowIndex = 1 ;
while ( rowIndex <= sheet. UsedRange. Rows. Count)
{
T t = new T ( ) ;
int columnIndex = 1 ;
foreach ( var p in properties)
{
var ignoreAtt = p. GetCustomAttributes ( typeof ( IgnoreColumnAttribute) , true ) ;
if ( ignoreAtt. Count ( ) != 0 )
continue ;
var cellValue = ( ( Range) sheet. Rows. Cells[ rowIndex, columnIndex++ ] ) . Text;
if ( string . IsNullOrWhiteSpace ( cellValue) )
continue ;
if ( p. PropertyType == typeof ( string ) )
p. SetValue ( t, cellValue, null ) ;
else
{
if ( p. PropertyType. Name. Contains ( "Nullable" ) )
{
var value = Convert. ChangeType ( cellValue, Nullable. GetUnderlyingType ( p. PropertyType) ) ;
p. SetValue ( t, value , null ) ;
}
else
{
p. SetValue ( t, cellValue, null ) ;
}
}
}
result. Add ( t) ;
rowIndex++ ;
}
}
workBook. Close ( false , Missing. Value, Missing. Value) ;
Marshal. ReleaseComObject ( workBook) ;
workBook = null ;
xApp. Workbooks. Close ( ) ;
xApp. Quit ( ) ;
KillExcel ( xApp) ;
Marshal. ReleaseComObject ( xApp) ;
xApp = null ;
GC. Collect ( ) ;
}
return result;
}
public static IList< T> ExcelToList_NPOI < T > ( string filePath, bool isExistsColumnName = true ) where T : new ( )
{
if ( ! File. Exists ( filePath) )
return null ;
List< T> result = null ;
IWorkbook workBook;
using ( FileStream stream = new FileStream ( filePath, FileMode. Open, FileAccess. Read, FileShare. Read) )
{
if ( filePath. Contains ( ".xlsx" ) )
workBook = new XSSFWorkbook ( stream) ;
else
workBook = new HSSFWorkbook ( stream) ;
}
var sheet = workBook. GetSheetAt ( 0 ) ;
if ( sheet != null )
{
result = new List < T > ( ) ;
var properties = typeof ( T) . GetProperties ( ) ;
int rowIndex = 1 ;
if ( ! isExistsColumnName)
rowIndex = 0 ;
while ( rowIndex <= sheet. LastRowNum)
{
var dataRow = sheet. GetRow ( rowIndex++ ) ;
T t = new T ( ) ;
int columnIndex = 0 ;
foreach ( var p in properties)
{
var ignoreAtt = p. GetCustomAttributes ( typeof ( IgnoreColumnAttribute) , true ) ;
if ( ignoreAtt. Count ( ) != 0 )
continue ;
var dataCell = dataRow. GetCell ( columnIndex++ ) ;
if ( string . IsNullOrWhiteSpace ( dataCell. StringCellValue) )
continue ;
if ( p. PropertyType == typeof ( string ) )
p. SetValue ( t, dataCell. StringCellValue, null ) ;
else
{
if ( p. PropertyType. Name. Contains ( "Nullable" ) )
{
var value = Convert. ChangeType ( dataCell. StringCellValue, Nullable. GetUnderlyingType ( p. PropertyType) ) ;
p. SetValue ( t, value , null ) ;
}
else
{
p. SetValue ( t, dataCell. StringCellValue, null ) ;
}
}
}
result. Add ( t) ;
}
}
return result;
}
private static void KillExcel ( Microsoft. Office. Interop. Excel. Application xApp)
{
IntPtr t = new IntPtr ( xApp. Hwnd) ;
int k = 0 ;
GetWindowThreadProcessId ( t, out k) ;
Process p = Process. GetProcessById ( k) ;
p. Kill ( ) ;
}
}
}
下面两个类是用于标记列标题的特性类
using System;
using System. Collections. Generic;
using System. Linq;
using System. Text;
using System. Threading. Tasks;
namespace Excel
{
[ AttributeUsage ( AttributeTargets. Property, AllowMultiple = false , Inherited = true ) ]
public class ColumnNameAttribute : Attribute
{
public string ColumnName { get ; set ; }
public ColumnNameAttribute ( string columnName)
{
ColumnName = columnName;
}
}
[ AttributeUsage ( AttributeTargets. Property, AllowMultiple = false , Inherited = true ) ]
public class IgnoreColumnAttribute : Attribute
{
public IgnoreColumnAttribute ( )
{ }
}
}
附个简单的使用样例
public class Student
{
[ ColumnName ( "姓名" ) ]
public string Name { get ; set ; }
[ ColumnName ( "年龄" ) ]
public int ? Age { get ; set ; }
[ IgnoreColumn ]
public int IgnoreField { get ; set ; }
public string ReserveField { get ; set ; }
}
class Program
{
[ STAThread ]
static void Main ( string [ ] args)
{
DataTable dt = new DataTable ( ) ;
dt. Columns. Add ( "Name" , typeof ( string ) ) ;
dt. Columns. Add ( "Age" , typeof ( int ) ) ;
dt. Columns. Add ( "ReserveField" , typeof ( string ) ) ;
DataRow dr1 = dt. NewRow ( ) ;
dr1[ "Name" ] = "小王" ;
dr1[ "Age" ] = 28 ;
dr1[ "ReserveField" ] = "保留字段1" ;
dt. Rows. Add ( dr1) ;
DataRow dr2 = dt. NewRow ( ) ;
dr2[ "Name" ] = "小李" ;
dr2[ "Age" ] = 27 ;
dr2[ "ReserveField" ] = "保留字段2" ;
dt. Rows. Add ( dr2) ;
List< Student> lst = new List < Student > ( )
{
new Student ( ) { Name = "小王" , IgnoreField = 1 , ReserveField = "保留字段1" } ,
new Student ( ) { Name = "小李" , Age = 27 , IgnoreField = 2 , ReserveField = "保留字段2" }
} ;
var result = dt. TableToList < Student > ( ) ;
ExcelHelper. ListToExcel_COM ( lst, Application. StartupPath + "\\COM.xls" ) ;
ExcelHelper. ListToExcel_COM ( lst, Application. StartupPath + "\\COM.xlsx" ) ;
var lst1 = ExcelHelper. ExcelToList_COM < Student > ( Application. StartupPath + "\\COM.xls" ) ;
var lst2 = ExcelHelper. ExcelToList_COM < Student > ( Application. StartupPath + "\\COM.xlsx" ) ;
ExcelHelper. ListToExcel_NPOI ( lst, Application. StartupPath + "\\NPOI.xls" ) ;
ExcelHelper. ListToExcel_NPOI ( lst, Application. StartupPath + "\\NPOI.xlsx" ) ;
var lst3 = ExcelHelper. ExcelToList_NPOI < Student > ( Application. StartupPath + "\\NPOI.xls" ) ;
var lst4 = ExcelHelper. ExcelToList_NPOI < Student > ( Application. StartupPath + "\\NPOI.xlsx" ) ;
}
}