using NPOI.HSSF.UserModel;
|
using NPOI.SS.UserModel;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.IO;
|
using System.Linq;
|
|
namespace JavaCode.cs
|
{
|
/// <summary>
|
/// 读取Excel类
|
/// </summary>
|
public class ReadExcel
|
{
|
private string intName = typeof(int).Name;
|
|
private string doubleName = typeof(double).Name;
|
|
private string stringName = typeof(string).Name;
|
|
/// <summary>
|
/// 读取Excel
|
/// </summary>
|
public static DataTable ReadXls(string file, int index = 0)
|
{
|
if (!File.Exists(file)) return null;
|
|
string ext = Path.GetExtension(file).ToLower();
|
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
|
{
|
//IWorkbook wk = ext == ".xls" ? new HSSFWorkbook(fs) : new XSSFWorkbook();
|
IWorkbook wk = new HSSFWorkbook(fs);
|
ISheet sheet = wk.GetSheetAt(index);
|
if (sheet == null)
|
{
|
return null;
|
}
|
|
return SheetToDataTable(sheet);
|
}
|
}
|
|
/// <summary>
|
/// Sheet转换为DataTable
|
/// </summary>
|
public static DataTable SheetToDataTable(ISheet sheet)
|
{
|
IRow firstRow = sheet.GetRow(0);
|
int cellCount = firstRow.LastCellNum;
|
int rowCount = sheet.LastRowNum;
|
|
DataTable dt = CreateDataTable(cellCount, sheet.GetRow(sheet.FirstRowNum));
|
//for (int i = sheet.FirstRowNum; i <= rowCount; i++)
|
for (int i = sheet.FirstRowNum + 1; i <= rowCount; i++)
|
{
|
IRow row = sheet.GetRow(i);
|
if (row == null)
|
{
|
continue;
|
}
|
|
DataRow dtRow = dt.NewRow();
|
for (int j = 0; j < cellCount; j++)
|
{
|
ICell cell = row.GetCell(j);
|
dtRow[j] = cell == null ? string.Empty : cell.ToString();
|
}
|
|
dt.Rows.Add(dtRow);
|
}
|
|
return dt;
|
}
|
|
/// <summary>
|
/// 创建DataTable
|
/// </summary>
|
public static DataTable CreateDataTable(int columns, IRow row)
|
{
|
DataTable dt = new DataTable();
|
for (int i = 0; i < columns; i++)
|
{
|
//DataColumn dc = new DataColumn("Col_" + i, typeof(string));
|
string colName = null == row || null == row.GetCell(i) ? "Col_" + i : row.GetCell(i).ToString();
|
DataColumn dc = new DataColumn(colName, typeof(string));
|
dt.Columns.Add(dc);
|
}
|
|
return dt;
|
}
|
|
// 查找列位置
|
private int FindColumn(DataRow firstRow, int columns, string name)
|
{
|
for (int i = 0, c = columns; i < c; i++)
|
{
|
string colName = firstRow[i] == null ? null : firstRow[i].ToString().Trim();
|
if (string.IsNullOrEmpty(colName))
|
{
|
break;
|
}
|
|
if (name == colName)
|
{
|
return i;
|
}
|
}
|
|
return -1;
|
}
|
}
|
}
|
