Use NPOI2.0 to manipulate Excel2003 and Excel2007 in C #

Use NPOI2.0 to manipulate Excel2003 and Excel2007 in C #

EXCEL2003:

#region Excel2003
<summary>
To read data from an Excel file into a DataTable (XLS)
</summary>
<param name= "File" ></param>
<returns></returns>
public static DataTable Exceltotableforxls (string file)
{
DataTable dt = new DataTable ();
using (FileStream fs = new FileStream (file, FileMode.Open, FileAccess.Read))
{
Xls. Hssfworkbook Hssfworkbook = new XLS. Hssfworkbook (FS);
Isheet sheet = Hssfworkbook. Getsheetat (0);

//Header
IRow Header = sheet. GetRow (sheet. Firstrownum);
list<int> columns = new list<int> ();
for (int i = 0; i < header.) Lastcellnum; i++)
{
Object obj = Getvaluetypeforxls (header. Getcell (i) as XLS. Hssfcell);
if (obj = = NULL | | | obj. ToString () = = string. Empty)
{
dt. Columns.Add (New DataColumn ("Columns" + i.tostring ()));
//continue;
}
Else
dt. Columns.Add (New DataColumn (obj. ToString ()));
Columns. ADD (i);
}
//Data
for (int i = sheet. Firstrownum + 1; I <= sheet. Lastrownum; i++)
{
DataRow dr = dt. NewRow ();
bool HasValue = false;
foreach (int j in columns)
{
Dr[j] = Getvaluetypeforxls (sheet. GetRow (i). Getcell (j) as XLS. Hssfcell);
if (dr[j]! = null && dr[j]. ToString ()! = string. Empty)
{
HasValue = true;
}
}
if (HasValue)
{
dt. Rows.Add (DR);
}
}
}
return dt;
}

<summary>
Exporting DataTable data to an Excel file (XLS)
</summary>
<param name= "DT" ></param>
<param name= "File" ></param>
public static void Tabletoexcelforxls (DataTable dt, string file)
{
Xls. Hssfworkbook Hssfworkbook = new XLS. Hssfworkbook ();
Isheet sheet = Hssfworkbook. Createsheet ("Test");

Table header
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);
}

Data
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 ());
}
}

Convert to byte array
MemoryStream stream = new MemoryStream ();
Hssfworkbook. Write (stream);
var buf = stream. ToArray ();

Save As Excel file
using (FileStream fs = new FileStream (file, FileMode.Create, FileAccess.Write))
{
Fs. Write (buf, 0, buf. Length);
Fs. Flush ();
}
}

<summary>
Get cell type (XLS)
</summary>
<param name= "Cell" ></param>
<returns></returns>
Private static Object Getvaluetypeforxls (XLS. Hssfcell 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

EXCEL2007:

#region Excel2007
<summary>
To read data from an Excel file into a DataTable (xlsx)
</summary>
<param name= "File" ></param>
<returns></returns>
public static DataTable exceltotableforxlsx (string file)
{
DataTable dt = new DataTable ();
using (FileStream fs = new FileStream (file, FileMode.Open, FileAccess.Read))
{
Xssfworkbook Xssfworkbook = new Xssfworkbook (FS);
Isheet sheet = Xssfworkbook. Getsheetat (0);

//Header
IRow Header = sheet. GetRow (sheet. Firstrownum);
list<int> columns = new list<int> ();
for (int i = 0; i < header.) Lastcellnum; i++)
{
Object obj = getvaluetypeforxlsx (header. Getcell (i) as Xssfcell);
if (obj = = NULL | | | obj. ToString () = = string. Empty)
{
dt. Columns.Add (New DataColumn ("Columns" + i.tostring ()));
//continue;
}
Else
dt. Columns.Add (New DataColumn (obj. ToString ()));
Columns. ADD (i);
}
//Data
for (int i = sheet. Firstrownum + 1; I <= sheet. Lastrownum; i++)
{
DataRow dr = dt. NewRow ();
bool HasValue = false;
foreach (int j in columns)
{
Dr[j] = getvaluetypeforxlsx (sheet. GetRow (i). Getcell (j) as Xssfcell);
if (dr[j]! = null && dr[j]. ToString ()! = string. Empty)
{
HasValue = true;
}
}
if (HasValue)
{
dt. Rows.Add (DR);
}
}
}
return dt;
}

<summary>
Export DataTable data to an Excel file (xlsx)
</summary>
<param name= "DT" ></param>
<param name= "File" ></param>
public static void Tabletoexcelforxlsx (DataTable dt, string file)
{
Xssfworkbook Xssfworkbook = new Xssfworkbook ();
Isheet sheet = Xssfworkbook. Createsheet ("Test");

Table header
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);
}

Data
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 ());
}
}

Convert to byte array
MemoryStream stream = new MemoryStream ();
Xssfworkbook. Write (stream);
var buf = stream. ToArray ();

Save As Excel file
using (FileStream fs = new FileStream (file, FileMode.Create, FileAccess.Write))
{
Fs. Write (buf, 0, buf. Length);
Fs. Flush ();
}
}

<summary>
Get cell type (xlsx)
</summary>
<param name= "Cell" ></param>
<returns></returns>
Private static Object Getvaluetypeforxlsx (Xssfcell 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;
}
}

Note: Operation Excel2003 and Operation Excel2007 use content under different namespaces

Use the Hssfworkbook operation under NPOI.HSSF.UserModel space Excel2003

Use the Xssfworkbook operation under NPOI.XSSF.UserModel space Excel2007

Use NPOI2.0 to manipulate Excel2003 and Excel2007 in C #