Npoi Importing Excel data

Using Npoi. HSSF. Usermodel;
Using Npoi. Ss. Usermodel;
Using Npoi. XSSF. Usermodel;
Using System;
Using System.Collections.Generic;
Using System.Data;
Using System.IO;
Using System.Linq;
Using System.Text;

Namespace Common
{

Use this code to add a Npoi DLL reference

public class Excelhelper:idisposable
{

private string fileName = null; Filename

Private Iworkbook workbook = null;
Private FileStream fs = null;
private bool disposed;

Public Excelhelper (String fileName)
{
This.filename = filename;//relative path: ~/uploads/test.xls
disposed = false;
}

<summary>
Import DataTable data into Excel
</summary>
<param name= "Data" > What to import </param>
<param name= "Iscolumnwritten" >datatable column name to import </param>
<param name= "SheetName" > Name of the sheet of Excel to import </param>
<returns> Import rows of data (contains column names that row) </returns>
public int Npoidatatabletoexcel (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 version
workbook = new Xssfworkbook ();
else if (Filename.indexof (". xls") > 0)//2003 version
workbook = new Hssfworkbook ();

Try
{
if (workbook! = null)
{
Sheet = workbook. Createsheet (SheetName);
}
Else
{
return-1;
}

if (Iscolumnwritten = = true)//write the column name of the 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); Writing to Excel
return count;
}
catch (Exception ex)
{
Console.WriteLine ("Exception:" + ex.) Message);
return-1;
}
}

<summary>
Import data from Excel into a DataTable
</summary>
<param name= "sheetname" >excel Workbook sheet name </param>
<param name= "Isfirstrowcolumn" > whether the first row is a DataTable column name </param>
<returns> Return of Datatable</returns>
Public DataTable npoiexceltodatatable (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 version
{
workbook = new Xssfworkbook (FS);
}
else if (Filename.indexof (". xls") > 0)//2003 version
{
workbook = new Hssfworkbook (FS);
}
if (sheetname! = null)
{
Sheet = workbook. Getsheet (SheetName);
if (sheet = = null)//If the specified sheetname corresponding sheet is not found, try to get the first sheet
{
Sheet = workbook. Getsheetat (0);
}
}
Else
{
Sheet = workbook. Getsheetat (0);
}
if (sheet! = null)
{
IRow firstrow = sheet. GetRow (0);
int cellcount = Firstrow.lastcellnum; The number of the last cell in a row is the total number of columns

if (Isfirstrowcolumn)
{
for (int i = firstrow.firstcellnum; i < cellcount; ++i)
{
Icell cell = Firstrow.getcell (i);
if (cell! = NULL)
{
String cellvalue = cell. Stringcellvalue;
if (cellvalue! = null)
{
DataColumn column = new DataColumn (cellvalue);
Data. Columns.Add (column);
}
}
}
StartRow = sheet. Firstrownum + 1;
}
Else
{
StartRow = sheet. Firstrownum;
}
StartRow = sheet. Firstrownum + 1;

The label of the last column
int rowCount = sheet. Lastrownum;
for (int i = startrow; I <= rowCount; ++i)
{
IRow row = sheet. GetRow (i);
if (row = = null) continue; Rows with no data default is NULL

DataRow datarow = data. NewRow ();
for (int j = row. Firstcellnum; J < Cellcount; ++J)
{
if (row. Getcell (j)! = NULL)//Similarly, cells with no data default to NULL
DATAROW[J] = row. Getcell (j). ToString ();
}
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;
}
}
}
}

Npoi Importing Excel data