C#:导入Excel通用类(CSV格式)

  • 一、引用插件LumenWorks.Framework.IO.dll(CsvReader)

     插件下载地址:https://pan.baidu.com/s/1X9Sl4bWTNHJOJb_v-KUndA  提取密码 0az3

  • 二、定义通用接口IExcelData.cs和IDataTransform.cs

  IDataTransform.cs

public interface IDataTransform { List<Dictionary<string, string>> DataTableToDictionary(DataTable dt); }文章地址https://www.yii666.com/article/754353.html

  IExcelData.cs    网址:yii666.com<

public interface IExcelData : IDataTransform
{
/// <summary>
/// Excel工作薄所有表格转化成DataTable对应字典
/// </summary>
Dictionary<string, DataTable> DataTableDictionary { get; } /// <summary>
/// Excel工作薄所有表格的名称集合
/// </summary>
List<string> SheetNames { get; } /// <summary>
/// Excel工作薄所有表格数据集合
/// </summary>
List<DataTable> DataTableList { get; } /// <summary>
/// 表格数量
/// </summary>
int SheetCount { get; } /// <summary>
/// Excel文件路径
/// </summary>
string FilePath { get; } /// <summary>
/// Excel流
/// </summary>
Stream ExcelStream { get; } /// <summary>
/// Excel文件类型
/// </summary>
ExcelType ExcelType { get; } /// <summary>
/// 获取Excel第一列数据集合
/// </summary>
List<Dictionary<string, string>> GetFirstRecords(); /// <summary>
/// 执行方法
/// </summary>
void Load();
}
  • 三、定义通用类CsvData.cs

     CsvData.cs

public class CsvData : IExcelData
{
#region 属性值
public Dictionary<string, DataTable> DataTableDictionary
{
get { throw new NotImplementedException(); }
}
public List<string> SheetNames
{
get { throw new NotImplementedException(); }
}
public List<DataTable> DataTableList
{
get { throw new NotImplementedException(); }
}
public int SheetCount
{
get { throw new NotImplementedException(); }
}
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 { throw new NotImplementedException(); }
}
private List<Dictionary<string, string>> m_Records;
public List<Dictionary<string, string>> Records { get { return m_Records; } }
#endregion
#region 构造
public CsvData(string path)
{
m_filePath = path;
}
public CsvData(Stream stream)
{
m_stream = stream;
}
#endregion
#region 方法
public List<Dictionary<string, string>> DataTableToDictionary(DataTable dt)
{
throw new NotImplementedException();
} public List<Dictionary<string, string>> GetFirstRecords()
{
return m_Records;
} /// <summary>
/// 执行方法
/// </summary>
public void Load()
{
if (!string.IsNullOrEmpty(m_filePath))
Load(m_filePath);
else
Load(m_stream);
} /// <summary>
/// 执行方法
/// </summary>
/// <param name="m_filePath">文件路径</param>
private void Load(string m_filePath)
{
using (FileStream fs=File.Open(m_filePath,FileMode.Open,FileAccess.Read))
{
using (StreamReader sr=new StreamReader(fs))
{
using (CsvReader csvReader=new CsvReader(sr,false))
{
m_Records = new List<Dictionary<string, string>>();
int count = csvReader.FieldCount;
string[] keys = new string[count];
string[] array = new string[count];
IEnumerator rows = csvReader.GetEnumerator();
try
{
while (rows.MoveNext())
{
if (csvReader.CurrentRecordIndex==)
{
keys = (string[])rows.Current;
for (int i = ; i < keys.Length; i++)
{
string s = keys[i].Replace('"', ' ').Trim();
keys[i] = s;
}
}
else
{
array = (string[])rows.Current;
Dictionary<string, string> dic = new Dictionary<string, string>();
for (int i = ; i < keys.Length; i++)
{
dic.Add(keys[i], array[i]);
}
m_Records.Add(dic);
}
}
}
catch
{
throw;
}
}
}
}
} /// <summary>
/// 执行方法
/// </summary>
/// <param name="m_stream">文件流</param>
private void Load(Stream m_stream)
{
using (StreamReader sr=new StreamReader(m_stream))
{
while (true)
{
var line = sr.ReadLine();
if (line=="")
{
break;
}
}
for (int i = ; i < ; i++)
{
sr.Read();
}
using (CsvReader csvReader=new CsvReader(sr,false))
{
m_Records = new List<Dictionary<string, string>>();
int count = csvReader.FieldCount;
string[] keys = new string[count];
string[] array = new string[count];
IEnumerator rows = csvReader.GetEnumerator();
try
{
while (rows.MoveNext())
{
if (csvReader.CurrentRecordIndex==)
{
keys = (string[])rows.Current;
for (int i = ; i < keys.Length; i++)
{
string s = keys[i].Replace('"', ' ').Trim();
keys[i] = s;
}
}
else
{
array = (string[])rows.Current;
Dictionary<string, string> dic = new Dictionary<string, string>();
for (int i = ; i < keys.Length; i++)
{
dic.Add(keys[i], array[i]);
}
m_Records.Add(dic);
}
}
}
catch
{ throw;
}
}
}
}
#endregion
}

注:以上的通用类将CSV格式的Excel转换成DataTable,识别Excel中的首列为数据库表的列项,会提取这一列如果判断匹配,这个就是业务代码了,以上未提供,根据需求自己编写就好。文章来源地址https://www.yii666.com/article/754353.html网址:yii666.com

原著:清风一人醉http://www.cnblogs.com/W--Jing/

以上方法可以个人分享研究!

不可做商业项目,违者必究!文章来源地址:https://www.yii666.com/article/754353.html

版权声明:本文内容来源于网络,版权归原作者所有,此博客不拥有其著作权,亦不承担相应法律责任。文本页已经标记具体来源原文地址,请点击原文查看来源网址,站内文章以及资源内容站长不承诺其正确性,如侵犯了您的权益,请联系站长如有侵权请联系站长,将立刻删除

C#:导入Excel通用类(CSV格式)-相关文章

  1. Excel和CSV格式文件的不同之处

  2. HighCharts-动态配置csv格式数据

  3. C#:导入Excel通用类(CSV格式)

  4. vcf格式文件转化为Excel(csv)格式文件(R语言的write.csv,write.table功能,Excel表的文件导入功能)

    最近在整理文件,准备把vcf文件转化为Excel格式,或者CSV格式,网上搜了一堆资料,还真有人专门开发出转化格式的工具:叫vcf2csv(下载地址http://vcf2csv.sourceforge.net/#links),赶紧下载安装,开始转化,结果弹出行列不一致的错误,遂放弃。尝试了一堆无用的工具以后,想着能

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信图片_20190322181744_03.jpg

微信扫一扫打赏

请作者喝杯咖啡吧~

支付宝扫一扫领取红包,优惠每天领

二维码1

zhifubaohongbao.png

二维码2

zhifubaohongbao2.png