C#：导入Excel通用类（Xlsx格式）
标签：工作薄 name vbr 方法 tostring ext nbsp c# range
- 一、引用插件NPOI.dll、NPOI.OOXML.dll、NPOI.OpenXml4Net.dll、NPOI.OpenXmlFormats.dll
插件下载地址：https://pan.baidu.com/s/1nw2mvBr 提取密码：kg32
public class XlsxExcelData:IExcelData
{
#region 属性值
private Dictionarystring, DataTable> m_tableDic;
public Dictionarystring, DataTable> DataTableDictionary
{
get { return m_tableDic; }
}
public Liststring> SheetNames
{
get
{
if (m_tableDic == null)
return null;
return m_tableDic.Keys.ToList();
}
}
public List DataTableList
{
get
{
if (m_tableDic == null)
return null;
return m_tableDic.Values.ToList();
}
}
public int SheetCount
{
get
{
if (m_tableDic == null)
return 0;
return SheetNames.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.xlsx; }
}
#endregion
#region 构造
public XlsxExcelData(string path)
{
m_filePath = path;
}
public XlsxExcelData(Stream stream)
{
m_stream = stream;
}
#endregion
#region 方法
public Liststring, string>> DataTableToDictionary(DataTable dt)
{
List> dicList = new Liststring, string>>();
foreach (DataRow row in dt.Rows)
{
Dictionary dic = new Dictionary();
foreach (DataColumn col in dt.Columns)
{
dic.Add(col.ColumnName, row[col].ToString());
}
dicList.Add(dic);
}
return dicList;
}
///
/// 执行方法
///
public void Load()
{
if (m_filePath != null)
Load(m_filePath);
else
Load(m_stream);
}
///
/// 执行方法
///
/// 文件路径
public void Load(string path)
{
m_filePath = path;
ExcelPackage package = null;
try
{
using (FileStream fs=File.Open(path,FileMode.OpenOrCreate,FileAccess.Read))
{
package = new ExcelPackage(fs);
}
}
catch
{
throw;
}
LoadExcel(package);
package.Dispose();
}
///
/// 执行方法
///
/// 文件流
private void Load(Stream stream)
{
m_stream = stream;
ExcelPackage package = null;
try
{
package = new ExcelPackage(stream);
}
catch
{
throw;
}
LoadExcel(package);
package.Dispose();
}
///
/// 获取Excel对应字典
///
///
private void LoadExcel(ExcelPackage package)
{
m_tableDic = new Dictionarystring, DataTable>();
var sheets = package.Workbook.Worksheets.GetEnumerator();
while (sheets.MoveNext())
{
DataTable dt = new DataTable();
var sheet = sheets.Current;
string name = sheet.Name;
if (sheet.Dimension!=null)
{
for (int i = 1; i )
{
string header = ExcelHeaderGenerator.GetExcelHeader(i);
DataColumn col = new DataColumn(header);
dt.Columns.Add(col);
}
for (int i = 1; i )
{
try
{
ExcelRow excelRow = sheet.Row(i);
DataRow dRow = dt.NewRow();
for (int j = 1; j )
{
ExcelRange range = sheet.Cells[i, j];
if (string.IsNullOrEmpty(range.Text))
{
dRow[j - 1] = string.Empty;
}
else
{
dRow[j - 1] = range.Text;
}
}
dt.Rows.Add(dRow);
}
catch (Exception e)
{
throw;
}
}
}
m_tableDic.Add(name, dt);
}
}
///
/// 获取第一列
///
///
public Liststring, string>> GetFirstRecords()
{
Liststring, string>> result = new Liststring, string>>();
DataTable dt = GetDataTable(0, 1);
foreach (DataRow row in dt.Rows)
{
Dictionarystring, string> dic = new Dictionarystring, string>();
foreach (DataColumn column in dt.Columns)
{
dic.Add(column.ColumnName, row[column].ToString());
}
result.Add(dic);
}
return result;
}
///
/// 获取DataTable
///
/// 工作薄索引
/// 列数
///
public DataTable GetDataTable(int sheetIndex, int columnNum)
{
DataTable dt = GetDataTable(sheetIndex);
Convert(columnNum, ref dt);
return dt;
}
///
/// 获取DataTable
///
/// 工作薄索引
///
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.Rows)
{
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;
}
///
/// 检测重复列
///
/// 列数
/// DataTable
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);
Liststring> columnArray = new Liststring>();
for (int i = 0; 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 )
{
dt.Rows.RemoveAt(i - r);
r++;
}
for (int i = 0; i )
{
dt.Columns[i].ColumnName = columnArray[i];
}
}
#endregion
}
原著：清风一人醉 http://www.cnblogs.com/W--Jing/
以上方法可以个人分享研究！
不可做商业项目，违者必究！
C#：导入Excel通用类（Xlsx格式）
标签：工作薄 name vbr 方法 tostring ext nbsp c# range
原文地址：https://www.cnblogs.com/W--Jing/p/8436122.html
评论