C#：导入Excel通用类，Xls格式
PS：在CSV格式和XLSX格式中有写到通用调用的接口和引用的插件，所以在这个xls格式里面并没有那么详细，只是配上xls通用类。
- 一、引用插件NPOI.dll、NPOI.OOXML.dll、NPOI.OpenXml4Net.dll、NPOI.OpenXmlFormats.dll
插件下载地址：https://pan.baidu.com/s/1ZTU90HUMu4AgMyIgmL-MaA 提取密码：jtjl
- 二、定义通用类XlsExcelData.cs
public class XlsExcelData : IExcelData
{
#region 属性值
private Dictionary<string, DataTable> m_tableDic;
public Dictionary<string, DataTable> DataTableDictionary
{
get { return m_tableDic; }
}
public List<string> SheetNames
{
get
{
if (m_tableDic == null)
return null;
return m_tableDic.Keys.ToList();
}
}
public List<DataTable> DataTableList
{
get
{
if (m_tableDic == null)
return null;
return m_tableDic.Values.ToList();
}
}
public int SheetCount
{
get
{
if (m_tableDic == null)
return 0;
return m_tableDic.Count;
}
}
private string m_filePath;
public string FilePath
{
get { return m_filePath; }
}
private Stream m_stream;
public Stream ExcelStream
{
get { return m_stream; }
}
public ExcelType ExcelType
{
get { return Interface.ExcelType.xls; }
}
#endregion
#region 构造
public XlsExcelData(string path)
{
m_filePath = path;
}
public XlsExcelData(Stream stream)
{
m_stream = stream;
}
#endregion
#region 方法
public List<Dictionary<string, string>> DataTableToDictionary(DataTable dt)
{
List<Dictionary<String, String>> dicList = new List<Dictionary<string, string>>();
foreach (DataRow row in dt.Rows)
{
Dictionary<String, String> dic = new Dictionary<String, String>();
foreach (DataColumn col in dt.Columns)
{
dic.Add(col.ColumnName, row[col].ToString());
}
dicList.Add(dic);
}
return dicList;
}
/// <summary>
/// 执行方法
/// </summary>
public void Load()
{
if (m_filePath != null)
Load(m_filePath);
else
Load(m_stream);
}
/// <summary>
/// 执行方法
/// </summary>
/// <param name="path">文件路径</param>
private void Load(string path)
{
m_filePath = path;
HSSFWorkbook workbook = null;
try
{
using (FileStream fs = File.Open(path, FileMode.OpenOrCreate, FileAccess.Read))
{
workbook = new HSSFWorkbook(fs);
}
}
catch
{
throw;
}
LoadExcel(workbook);
workbook.Dispose();
}
/// <summary>
/// 执行方法
/// </summary>
/// <param name="stream">文件流</param>
private void Load(Stream stream)
{
m_stream = stream;
HSSFWorkbook workbook = null;
try
{
workbook = new HSSFWorkbook(stream);
}
catch
{
throw;
}
LoadExcel(workbook);
workbook.Dispose();
}
/// <summary>
/// 获取Excel对应字典
/// </summary>
/// <param name="workbook"></param>
private void LoadExcel(HSSFWorkbook workbook)
{
m_tableDic = new Dictionary<string, DataTable>();
var sheets = workbook.GetEnumerator();
while (sheets.MoveNext())
{
DataTable dt = new DataTable();
int colCount = 0;
ISheet sheet = (ISheet)sheets.Current;
var rows = sheet.GetEnumerator();
while (rows.MoveNext())
{
IRow row = (IRow)rows.Current;
colCount = row.LastCellNum > colCount ? row.LastCellNum : colCount;
}
for (int i = 1; i <= colCount; i++)
{
string header = ExcelHeaderGenerator.GetExcelHeader(i);
DataColumn col = new DataColumn(header);
dt.Columns.Add(col);
}
var datarows = sheet.GetEnumerator();
while (datarows.MoveNext())
{
IRow row = (IRow)datarows.Current;
DataRow dRow = dt.NewRow();
for (int j = 0; j < row.LastCellNum; j++)
{
ICell cell = row.Cells[j];
if (cell.CellType == CellType.String)
dRow[j] = row.Cells[j].StringCellValue;
else if (cell.CellType == CellType.Numeric)
dRow[j] = row.Cells[j].NumericCellValue;
else
dRow[j] = "[Cell Type Unsuported]";
}
dt.Rows.Add(dRow);
}
m_tableDic.Add(sheet.SheetName, dt);
}
}
/// <summary>
/// 获取第一列
/// </summary>
/// <returns></returns>
public List<Dictionary<string, string>> GetFirstRecords()
{
List<Dictionary<string, string>> result = new List<Dictionary<string, string>>();
DataTable dt = GetDataTable(0, 1);
foreach (DataRow row in dt.Rows)
{
Dictionary<string, string> dic = new Dictionary<string, string>();
foreach (DataColumn column in dt.Columns)
{
dic.Add(column.ColumnName, row[column].ToString());
}
result.Add(dic);
}
return result;
}
/// <summary>
/// 获取DataTable
/// </summary>
/// <param name="sheetIndex">工作薄索引</param>
/// <returns></returns>
public DataTable GetDataTable(int sheetIndex)
{
if (m_tableDic == null)
return null;
if (sheetIndex >= SheetCount)
throw new Exception("表格索引超出序列，当前索引数量为" + SheetCount);
DataTable dt = DataTableList[sheetIndex];
DataTable dt_copy = new DataTable();
foreach (DataColumn col in dt.Columns)
{
dt_copy.Columns.Add(new DataColumn(col.ColumnName));
}
foreach (DataRow row in dt.Rows)
{
DataRow r = dt_copy.NewRow();
r.ItemArray = row.ItemArray;
dt_copy.Rows.Add(r);
}
return dt_copy;
}
/// <summary>
/// 获取DataTable
/// </summary>
/// <param name="sheetIndex">工作薄索引</param>
/// <param name="columnNum">列数</param>
/// <returns></returns>
public DataTable GetDataTable(int sheetIndex, int columnNum)
{
DataTable dt = GetDataTable(sheetIndex);
Convert(columnNum, ref dt);
return dt;
}
/// <summary>
/// 检测重复列
/// </summary>
/// <param name="colNum">列数</param>
/// <param name="dt">DataTable</param>
private void Convert(int colNum, ref DataTable dt)
{
if (colNum < 1)
throw new Exception("指定作为标题的行数必须是大于0");
if (colNum > dt.Rows.Count)
throw new Exception("指定作为标题的行数不能大于表格的总行数" + dt.Rows.Count);
List<string> columnArray = new List<string>();
for (int i = 0; i < dt.Columns.Count; i++)
{
if (columnArray.Contains(dt.Rows[colNum - 1].ItemArray[i].ToString()))
throw new Exception("指定为DataTable标题的行不能存在重复值，重复值为" + dt.Rows[colNum - 1].ItemArray[i].ToString());
columnArray.Add(dt.Rows[colNum - 1].ItemArray[i].ToString());
}
int r = 0;
for (int i = 0; i < colNum; i++)
{
dt.Rows.Remove(dt.Rows[i - r]);
r++;
}
for (int i = 0; i < columnArray.Count; i++)
{
dt.Columns[i].ColumnName = columnArray[i];
}
}
#endregion
}原著：清风一人醉 http://www.cnblogs.com/W--Jing/
以上方法可以个人分享研究！
不可做商业项目，违者必究！