Npoi导入和导出帮助类

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

#region Npoi导入导出

/// < summary >

/// Excel导入成Datable

/// </ summary >

/// < param name = "file" > 导入路径(包含文件名与扩展名) </ param >

/// < returns ></ returns >

public static DataTable ExcelToTable( string file)

{

DataTable dt = new DataTable();

IWorkbook workbook;

string fileExt = Path.GetExtension(file).ToLower();

using ( FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))

{

//XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式

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 = workbook.GetSheetAt( 0);

//表头

IRow header = sheet.GetRow(sheet.FirstRowNum);

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() == string.Empty)

{

dt.Columns.Add( new DataColumn( "Columns" + i.ToString()));

}

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)

{

dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));

if (dr[j] != null && dr[j].ToString() != string.Empty)

{

hasValue = true;

}

}

if (hasValue)

{

dt.Rows.Add(dr);

}

}

}

return dt;

}

/// < summary >

/// Datable导出成Excel

/// </ summary >

/// < param name = "dt" ></ param >

/// < param name = "file" > 导出路径(包括文件名与扩展名) </ param >

public static void TableToExcel( DataTable dt, string file)

{

IWorkbook workbook;

string fileExt = Path.GetExtension(file).ToLower();

if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; }

if (workbook == null) { return; }

ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet( "Sheet1") : workbook.CreateSheet(dt.TableName);

//表头

IRow row = sheet.CreateRow( 0);

for ( int i = 0; i < dt.Columns.Count; i++)

{

ICell cell = row.CreateCell(i);

cell.SetCellValue(dt.Columns[i].ColumnName);

}

//列宽自适应，只对英文和数字有效

for ( int i = 0; i <= dt.Columns.Count; i++)

{

sheet.AutoSizeColumn(i);

}

//获取当前列的宽度，然后对比本列的长度，取最大值

for ( int columnNum = 0; columnNum <= dt.Columns.Count; columnNum++)

{

int columnWidth = sheet.GetColumnWidth(columnNum) / 256;

for ( int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++)

{

IRow currentRow;

//当前行未被使用过

if (sheet.GetRow(rowNum) == null)

{

currentRow = sheet.CreateRow(rowNum);

}

else

{

currentRow = sheet.GetRow(rowNum);

}

if (currentRow.GetCell(columnNum) != null)

{

ICell currentCell = currentRow.GetCell(columnNum);

int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;

if (columnWidth < length)

{

columnWidth = length;

}

}

}

sheet.SetColumnWidth(columnNum, columnWidth * 256);

}

//数据

for ( int i = 0; i < dt.Rows.Count; i++)

{

IRow row1 = sheet.CreateRow(i + 1);

for ( int j = 0; j < dt.Columns.Count; j++)

{

ICell cell = row1.CreateCell(j);

cell.SetCellValue(dt.Rows[i][j].ToString());

//单元格内文本居中

var cellStyle = workbook.CreateCellStyle();

cellStyle.Alignment = HorizontalAlignment.Center;

cell.CellStyle = cellStyle;

}

}

//转为字节数组

MemoryStream stream = new MemoryStream();

workbook.Write(stream);

var buf = stream.ToArray();

//保存为Excel文件

using ( FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))

{

fs.Write(buf, 0, buf.Length);

fs.Flush();

}

}

/// < summary >

/// 获取单元格类型

/// </ summary >

/// < param name = "cell" ></ param >

/// < returns ></ returns >

private static object GetValueType( ICell cell)

{

if (cell == null)

return null;

switch (cell.CellType)

{

case CellType.Blank: //BLANK:

return null;

case CellType.Boolean: //BOOLEAN:

return cell.BooleanCellValue;

case CellType.Numeric: //NUMERIC:

return cell.NumericCellValue;

case CellType.String: //STRING:

return cell.StringCellValue;

case CellType.Error: //ERROR:

return cell.ErrorCellValue;

case CellType.Formula: //FORMULA:

default:

return "=" + cell.CellFormula;

}

}

#endregion