注意,Excel读入DataTable需要使用NPOI包
public static DataTable ExcelToTable ( string file, string sheetName = "" )
{
DataTable dt = new DataTable ( ) ;
IWorkbook workbook = null ;
try
{
string fileExt = Path. GetExtension ( file) . ToLower ( ) ;
using ( FileStream fs = new FileStream ( file, FileMode. Open, FileAccess. Read) )
{
if ( fileExt == ".xlsx" ) { workbook = new XSSFWorkbook ( fs) ; } else if ( fileExt == ".xls" ) { workbook = new HSSFWorkbook ( fs) ; } else { workbook = null ; }
if ( workbook == null ) { return null ; }
ISheet sheet = string. IsNullOrWhiteSpace ( sheetName) ? workbook. GetSheetAt ( 0 ) : workbook. GetSheet ( sheetName) ;
IRow header = sheet. GetRow ( sheet. FirstRowNum) ;
if ( header == null )
{
throw new Exception ( "Excel表没有数据,请添加数据" ) ;
}
List< int> columns = new List < int> ( ) ;
for ( int i = 0 ; i < header. LastCellNum; i++ )
{
object obj = GetValueType ( header. GetCell ( i) ) ;
if ( obj == null || obj. ToString ( ) . Trim ( ) == string. Empty)
{
continue ;
}
else
{
dt. Columns. Add ( new DataColumn ( obj. ToString ( ) ) ) ;
}
columns. Add ( i) ;
}
for ( int i = sheet. FirstRowNum + 1 ; i <= sheet. LastRowNum; i++ )
{
DataRow dr = dt. NewRow ( ) ;
bool hasValue = false ;
foreach ( int j in columns )
{
if ( sheet. GetRow ( i) != null ) {
dr[ j] = GetValueType ( sheet. GetRow ( i) . GetCell ( j) ) ;
}
}
dt. Rows. Add ( dr) ;
}
dt. AcceptChanges ( ) ;
}
return dt;
}
catch ( Exception e)
{
return null ;
}
finally
{
if ( dt != null ) { dt. Dispose ( ) ; }
if ( workbook != null ) { workbook = null ; }
}
}
private static object GetValueType ( ICell cell )
{
if ( cell == null )
return null ;
switch ( cell. CellType)
{
case CellType. Blank:
return "" ;
case CellType. Boolean:
return cell. BooleanCellValue;
case CellType. Numeric:
return cell. NumericCellValue;
case CellType. String:
return cell. StringCellValue;
case CellType. Error:
return cell. ErrorCellValue;
case CellType. Formula:
default :
return "=" + cell. CellFormula;
}
}
DataTable读入Excel
public static string DataToExcel ( System. Data. DataTable m_DataTable, string s_FileName )
{
string FileName = s_FileName;
if ( System. IO . File. Exists ( FileName) )
{
System. IO . File. Delete ( FileName) ;
}
System. IO . FileStream objFileStream;
System. IO . StreamWriter objStreamWriter;
string strLine = "" ;
objFileStream = new System. IO. FileStream ( FileName, System. IO . FileMode. OpenOrCreate, System. IO . FileAccess. Write) ;
objStreamWriter = new System. IO. StreamWriter ( objFileStream, Encoding. Unicode) ;
for ( int i = 0 ; i < m_DataTable. Columns. Count; i++ )
{
strLine = strLine + m_DataTable. Columns[ i] . Caption. ToString ( ) + Convert. ToChar ( 9 ) ;
}
objStreamWriter. WriteLine ( strLine) ;
strLine = "" ;
for ( int i = 0 ; i < m_DataTable. Rows. Count; i++ )
{
for ( int j = 0 ; j < m_DataTable. Columns. Count; j++ )
{
if ( m_DataTable. Rows[ i] . ItemArray[ j] == null )
strLine = strLine + " " + Convert. ToChar ( 9 ) ;
else
{
string rowstr = "" ;
rowstr = m_DataTable. Rows[ i] . ItemArray[ j] . ToString ( ) ;
if ( rowstr. IndexOf ( "\r\n" ) > 0 )
rowstr = rowstr. Replace ( "\r\n" , " " ) ;
if ( rowstr. IndexOf ( "\t" ) > 0 )
rowstr = rowstr. Replace ( "\t" , " " ) ;
strLine = strLine + rowstr + Convert. ToChar ( 9 ) ;
}
}
objStreamWriter. WriteLine ( strLine) ;
strLine = "" ;
}
objStreamWriter. Close ( ) ;
objFileStream. Close ( ) ;
return FileName;
}