Asp.net: two ways to import multiple excel files to multiple datatables

Asp tutorial. net import multiple excel files to multiple datatables in two ways
Microsoft. Office. Interop. Excel. Application TheExcelApp = new Microsoft. Office. Interop. Excel. Application (); // three worksheets are created by default.

TheExcelApp. Visible = false;

Int colIndex, rowIndex
ColIndex = 1;
// Output field name in the first line
Foreach (DataColumn dc in dt. Columns) // assume that the data is in the dt table
{
TheExcelApp. Worksheets ("sheet1"). Activate (); // call sheet1 the current worksheet to write data.
TheExcelApp. Cells (1, colIndex). value = dc. ColumnName;
}
// Write data from the second row
For (colIndex = 0; colIndex <dt. Columns. Count-1; colIndex ++)
{
For (rowIndex = 0; rowIndex <dt. Rows. Count-1; rowIndex ++)
{
// Excle columns start from 1, so from row + 2
TheExcelApp. Cells (row + 2, colIndex + 1). value = dt. Rows [rowIndex] [colIndex];
}
TheExcelApp. ActiveSheet. columns (colIndex + 1). autofit (); // automatically adjust the width
}
TheExcelApp. Worksheets ("sheet1"). saveas (fileName); // save

// Method 2
Use using directly ).

HSSFWorkbook hssfworkbook;
Void InitializeWorkbook (string path)
{
// Read the template via FileStream, it is suggested to use FileAccess. Read to prevent file lock.
// Book1.xls is an Excel-2007-generated file, so some new unknown BIFF records are added.
Using (FileStream file = new FileStream (path, FileMode. Open, FileAccess. Read ))
{
Hssfworkbook = new HSSFWorkbook (file );
}
}
Next we will start to write the Most Important Function ConvertToDataTable, that is, put the HSSF data into a DataTable.
 
HSSFSheet sheet = hssfworkbook. GetSheetAt (0 );
System. Collections. IEnumerator rows = sheet. GetRowEnumerator ();

While (rows. MoveNext ())
{
HSSFRow row = (HSSFRow) rows. Current;
// TODO: Create DataTable row

For (int I = 0; I <row. LastCellNum; I ++)
{
HSSFCell cell = row. GetCell (I );
// TODO: set cell value to the cell of DataTables
}
We should be able to understand the above structure. It is nothing more than traversing the row first and then traversing each column in the row. This introduces a difficult point. Because there are several types of cells in Excel, different types of display are different, specific types include Boolean, numeric, text, formula, blank, and error.
Public enum HSSFCellType
{
Unknown =-1,
NUMERIC = 0,
STRING = 1,
FORMULA = 2,
BLANK = 3,
BOOLEAN = 4,
ERROR = 5,
}

Here, HSSFCellType describes all types, but careful friends may have discovered that there is no date type. Why? This is because the bottom layer of Excel does not have a certain date type, but is replaced by a numeric type. As to how to distinguish between dates and numbers, it is determined by the text display style, in NPOI, HSSFDataFormat is used for processing. To conveniently obtain the text of the desired type, we can use HSSFCell. ToString () for processing.
The code just now becomes like this:

HSSFCell cell = row. GetCell (I );

If (cell = null)
{
Dr [I] = null;
}
Else
{
Dr [I] = cell. ToString ();
}
}
Dt. Rows. Add (dr );
}