ASP .Net C# ---Excel导入导出方法

宦海逐流,苏教版五年级上册语文教学计划,各色夫郎齐上堂

导入导出的方法以及引用，可以自行创建一个帮助类

using system;
using npoi.ss.usermodel;
using npoi.xssf.usermodel;
using npoi.hssf.usermodel;
using system.io;
using system.data;
using system.data.oledb;

namespace excelhelper
{
public class excelhelper : idisposable
{
private string filename = null; //文件名
private iworkbook workbook = null;
private filestream fs = null;
private bool disposed;

public excelhelper(string filename)
{
this.filename = filename;
disposed = false;
}

/// <summary>
/// 将datatable数据导入到excel中
/// </summary>
/// <param name="data">要导入的数据</param>
/// <param name="iscolumnwritten">datatable的列名是否要导入</param>
/// <param name="sheetname">要导入的excel的sheet的名称</param>
/// <returns>导入数据行数(包含列名那一行)</returns>
public int datatabletoexcel(datatable data, string sheetname, bool iscolumnwritten)
{
int i = 0;
int j = 0;
int count = 0;
isheet sheet = null;

fs = new filestream(filename, filemode.openorcreate, fileaccess.readwrite);
if (filename.indexof(".xlsx") > 0) // 2007版本
workbook = new xssfworkbook();
else if (filename.indexof(".xls") > 0) // 2003版本
workbook = new hssfworkbook();

try
{
if (workbook != null)
{
sheet = workbook.createsheet(sheetname);
}
else
{
return -1;
}

if (iscolumnwritten == true) //写入datatable的列名
{
irow row = sheet.createrow(0);
for (j = 0; j < data.columns.count; ++j)
{
row.createcell(j).setcellvalue(data.columns[j].columnname);
}
count = 1;
}
else
{
count = 0;
}

for (i = 0; i < data.rows.count; ++i)
{
irow row = sheet.createrow(count);
for (j = 0; j < data.columns.count; ++j)
{
row.createcell(j).setcellvalue(data.rows[i][j].tostring());
}
++count;
}
workbook.write(fs); //写入到excel
return count;
}
catch (exception ex)
{
console.writeline("exception: " + ex.message);
return -1;
}
}

/// <summary>
/// 将excel中的数据导入到datatable中
/// </summary>
/// <param name="sheetname">excel工作薄sheet的名称</param>
/// <param name="isfirstrowcolumn">第一行是否是datatable的列名</param>
/// <returns>返回的datatable</returns>
public datatable exceltodatatable(string sheetname, bool isfirstrowcolumn)
{
isheet sheet = null;
datatable data = new datatable();
int startrow = 0;
try
{
fs = new filestream(filename, filemode.open, fileaccess.read);
if (filename.indexof(".xlsx") > 0) // 2007版本
workbook = new xssfworkbook(fs);
else if (filename.indexof(".xls") > 0) // 2003版本
workbook = new hssfworkbook(fs);

if (sheetname != null)
{
sheet = workbook.getsheet(sheetname);
if (sheet == null) //如果没有找到指定的sheetname对应的sheet，则尝试获取第一个sheet
{
sheet = workbook.getsheetat(0);
}
}
else
{
sheet = workbook.getsheetat(0);
}
if (sheet != null)
{
irow firstrow = sheet.getrow(0);
int cellcount = firstrow.lastcellnum; //一行最后一个cell的编号 即总的列数

if (isfirstrowcolumn)
{
for (int i = firstrow.firstcellnum; i < cellcount; ++i)
{
icell cell = firstrow.getcell(i);
dynamic cellvalue = string.empty;
switch (cell.celltype)
{
case celltype.numeric:
//npoi中数字和日期都是numeric类型的，这里对其进行判断是否是日期类型
if (hssfdateutil.iscelldateformatted(cell))//日期类型
{
cellvalue = cell.datecellvalue;
}
else//其他数字类型
{
cellvalue = cell.numericcellvalue;
}
break;
case celltype.formula:
hssfformulaevaluator eva = new hssfformulaevaluator(workbook);
cellvalue = eva.evaluate(cell).stringvalue;
break;
case celltype.blank:
cellvalue = "";
break;
default:
cellvalue = cell.stringcellvalue;
break;
}
if (cellvalue != null)
{
datacolumn column = new datacolumn(cellvalue);
data.columns.add(column);
}
}
startrow = sheet.firstrownum + 1;
}
else
{
for (int i = firstrow.firstcellnum; i < cellcount; ++i)
{
datacolumn column = new datacolumn("number" + i);
data.columns.add(column);
}
startrow = sheet.firstrownum;
}

//最后一列的标号
int rowcount = sheet.lastrownum;
for (int i = startrow; i <= rowcount; ++i)
{
irow row = sheet.getrow(i);
if (row == null) continue; //没有数据的行默认是null　　　　　　　

datarow datarow = data.newrow();
for (int j = row.firstcellnum; j < cellcount; ++j)
{
icell cell = row.getcell(j);
if (cell != null) //同理，没有数据的单元格都默认是null
{
dynamic cellvalue = string.empty;
switch (cell.celltype)
{
case celltype.numeric:
//npoi中数字和日期都是numeric类型的，这里对其进行判断是否是日期类型
if (hssfdateutil.iscelldateformatted(cell))//日期类型
{
cellvalue = cell.datecellvalue.tostring("yyyy/mm/dd");
}
else//其他数字类型
{
cellvalue = convert.todouble(cell.tostring());
}
break;
case celltype.blank:
cellvalue = "";
break;
default:
cellvalue = cell.tostring();
break;
}
datarow[j] = cellvalue;
}

}
data.rows.add(datarow);
}
}

return data;
}
catch (exception ex)
{
console.writeline("exception: " + ex.message);
return null;
}
}

public void dispose()
{
dispose(true);
gc.suppressfinalize(this);
}

protected virtual void dispose(bool disposing)
{
if (!this.disposed)
{
if (disposing)
{
if (fs != null)
fs.close();
}

fs = null;
disposed = true;
}
}
}
}

 

如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复