C uses NOPI library to import Excel documents
- 2021-12-12 09:29:53
- OfStack
Import an Excel document using NOPI
NOPI version: 2.3. 0, SharpZipLib version dependent on NPOI: 0.86, tested for. net 4.0 +
Record several problems encountered
1. IWorkbook interface in NOPI: xls is implemented using HSSFWorkbook class and xlsx is implemented using XSSFWorkbook class
2. Date conversion, judging row. GetCell (j). CellType = = NPOI. SS. UserModel. CellType. Numeric
&
&
HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)
You cannot use row. GetCell (j). DateCellValue directly, it will throw an exception directly
1. Convert file stream to DataTable
/// <summary>
/// According to Excel Format reading Excel
/// </summary>
/// <param name="stream"> File stream </param>
/// <param name="type">Excel Format enumeration type, xls/xlsx</param>
/// <param name="sheetName"> Table name, default number 1 Zhang </param>
/// <returns>DataTable</returns>
private static DataTable ImportExcel(Stream stream, ExcelExtType type, string sheetName)
{
DataTable dt = new DataTable();
IWorkbook workbook;
try
{
//xls Use HSSFWorkbook Class implementation, xlsx Use XSSFWorkbook Class implementation
switch (type)
{
case ExcelExtType.xlsx:
workbook = new XSSFWorkbook(stream);
break;
default:
workbook = new HSSFWorkbook(stream);
break;
}
ISheet sheet = null;
// Get a worksheet Default rank 1 Zhang
if (string.IsNullOrWhiteSpace(sheetName))
sheet = workbook.GetSheetAt(0);
else
sheet = workbook.GetSheet(sheetName);
if (sheet == null)
return null;
IEnumerator rows = sheet.GetRowEnumerator();
#region Get header
IRow headerRow = sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
ICell cell = headerRow.GetCell(j);
if (cell != null)
{
dt.Columns.Add(cell.ToString());
}
else
{
dt.Columns.Add("");
}
}
#endregion
#region Get content
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
// Determine whether the cell is in date format
if (row.GetCell(j).CellType == NPOI.SS.UserModel.CellType.Numeric && HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)))
{
if (row.GetCell(j).DateCellValue.Year >=1970)
{
dataRow[j] = row.GetCell(j).DateCellValue.ToString();
}
else
{
dataRow[j] = row.GetCell(j).ToString();
}
}
else
{
dataRow[j] = row.GetCell(j).ToString();
}
}
}
dt.Rows.Add(dataRow);
}
#endregion
}
catch (Exception ex)
{
dt=null;
}
finally
{
//if (stream != null)
//{
// stream.Close();
// stream.Dispose();
//}
}
return dt;
}
2. File upload and import
/// <summary>
/// Upload Excel Import
/// </summary>
/// <param name="file"> Upload file object </param>
/// <param name="errorMsg"> Error message </param>
/// <param name="sheetName"> Table name, default number 1 Zhang </param>
/// <returns></returns>
public static DataTable Import(System.Web.HttpPostedFileBase file, ref string errorMsg, string sheetName = "")
{
if (file == null || file.InputStream == null || file.InputStream.Length == 0)
{
errorMsg = " Please select the Excel Documents ";
return null;
}
var excelType = GetExcelFileType(file.FileName);
if (excelType == null)
{
errorMsg = " Please select the correct Excel Documents ";
return null;
}
using (var stream = new MemoryStream())
{
file.InputStream.Position = 0;
file.InputStream.CopyTo(stream);
var dt = ImportExcel(stream, excelType.Value, sheetName);
if (dt == null)
errorMsg = " Import failed , Please select the correct Excel Documents ";
return dt;
}
}
3. Local path read import
/// <summary>
/// Import according to file path Excel
/// </summary>
/// <param name="filePath"></param>
/// <param name="errorMsg"> Error message </param>
/// <param name="sheetName"> Table name, default number 1 Zhang </param>
/// <returns> Could be null Adj. DataTable</returns>
public static DataTable Import(string filePath, ref string errorMsg, string sheetName = "")
{
var excelType = GetExcelFileType(filePath);
if (GetExcelFileType(filePath) == null)
{
errorMsg = " Please select the correct Excel Documents ";
return null;
}
if (!File.Exists(filePath))
{
errorMsg = " Is not found to import Excel Documents ";
return null;
}
DataTable dt;
using (var stream = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
dt = ImportExcel(stream, excelType.Value, sheetName);
}
if (dt == null)
errorMsg = " Import failed , Please select the correct Excel Documents ";
return dt;
}
4. Complete demo
One Demo with winform introduced into Excel was added.
https://github.com/yimogit/NopiExcelDemo