读取上传的CSV为DataTable
csv导入文件会把每列的数据用英文逗号分割开来，如果遇到某列中包含英文逗号，则会把该列用英文双引号进行包装。
如果csv文件中某列的数据本身包含英文逗号,应该使用读取字符串的方式进行解析数据，如csv的数据如下，ReGoodsCode,SKUCode,PlatformCode,GoodsName,GoodsCount,RefundAmount,Phone,MallID,UserName,ReTime
1.56E+14,xg003809216-z,4M天猫旗舰店,"4M ,,高曼",5,500,13907927272,"匿名用户,lisi","张三,xxx,ooo",2014/12/23 14:10
这时应用如下方法进行解析数据，
private IEnumerable<string[]> ExCsv1(string path)
{
var sr = File.ReadAllLines(path, System.Text.Encoding.UTF8);
int i = 0, m = 0;
for (var a = 1; a < sr.Length; a++)
{
string[] split = sr[a].Split(',');
string[] newsplit = new string[split.Length];
var fix = 0;
for (i = 0; i + fix < split.Length; i++)
{
newsplit[i] = split[i + fix];
if (newsplit[i].StartsWith("\"") && !newsplit[i].EndsWith("\""))
{
for (var j = i + fix + 1; j < split.Length; j++, fix++)
{
newsplit[i] += "," + split[j];
if (split[j].EndsWith("\"")) break;
}
}
}
yield return newsplit;
}
}
使用时，
if (FileUpload1.HasFile)
{
try
{
//FileUpload1.SaveAs(Server.MapPath("upload") + "\\" + FileUpload1.FileName);
//这里永远只保存一个文件
string path = ConfigurationManager.AppSettings["ReturnGoodsPath"];
FileUpload1.SaveAs(path + FileUpload1.FileName);
var dt = ExCsv1(path + FileUpload1.FileName);
//DataTable dt = csv2dt(path + FileUpload1.FileName, 1);
//var codeisInt = dt.Columns[0].DataType == typeof(int) || dt.Columns[0].DataType == typeof(double);
//绑定数据源
//GridView1.DataSource = dt;
//GridView1.DataBind();
var msg = "客户端路径：" + FileUpload1.PostedFile.FileName + "<br>" +
"文件名：" + System.IO.Path.GetFileName(FileUpload1.FileName) + "<br>" +
"文件扩展名：" + System.IO.Path.GetExtension(FileUpload1.FileName) + "<br>" +
"文件大小：" + FileUpload1.PostedFile.ContentLength + " KB<br>" +
"文件MIME类型：" + FileUpload1.PostedFile.ContentType + "<br>" +
"保存路径：" + Server.MapPath("upload") + "\\" + FileUpload1.FileName;
List<ReOrder> r = new List<ReOrder>();
foreach (var item in dt)
{
string orderCode = item[0].ToString();// codeisInt ? ((double)item[0]).ToString("D") : item[0].ToString();
string productCode = item[1].ToString();
ReOrder ord = r.Where(t => t.Code == orderCode).FirstOrDefault();
if (ord == null)
{
ord = new ReOrder();
ReOrderDetail det = new ReOrderDetail();
ord.Code = orderCode;// item[0].ToString();
det.SKUCODE = item[1].ToString();
ord.PlatformCode = item[2].ToString();
det.GoodsName = item[3].ToString();
det.GoodsCount = int.Parse(item[4].ToString());
ord.TotalAmount = decimal.Parse(item[5].ToString());
det.TotalAmount = decimal.Parse(item[5].ToString());
ord.phone = item[6].ToString();
ord.MallID = item[7].ToString();
ord.UserName = item[8].ToString();
ord.ReGoodsTime = DateTime.Parse(item[9].ToString());
ord.ReOrderDetails.Add(det);
r.Add(ord);
}
else if (!ord.ReOrderDetails.Any(d => d.SKUCODE == productCode))
{
ReOrderDetail det = new ReOrderDetail();
det.SKUCODE = item[1].ToString();
det.GoodsName = item[3].ToString();
det.GoodsCount = int.Parse(item[4].ToString());
det.TotalAmount = decimal.Parse(item[5].ToString());
ord.TotalAmount = ord.TotalAmount + decimal.Parse(item[5].ToString());
ord.ReOrderDetails.Add(det);
}
}
var returnGoodsGroups = r.GroupBy(x => x.phone).ToArray();
foreach (var item in returnGoodsGroups)
{
LogEntry entry = new LogEntry("返回退货", 1);
LogStub.Log(entry);
var res = await LogicStub.ReturnGoodsHandler.HandleReturnGoods(new SDK.ReturnGoodsInfo
{
Orders = item.ToList(),
MemberKeyValue = item.Key
});
msg += "<br/>" + res;// == "{}" ? "<br/>OK!" : "<br/>" + res;
}
Label1.Text = msg;
}
catch (Exception ex)
{
Label1.Text = "发生错误：" + ex.Message.ToString();
}
}
也可以使用以下方法获取数据，但此方法可能无法解决列数据中包含英文逗号的问题：
/// <summary>
/// CSV文件转换
/// 作为数据源
/// </summary>
/// <returns></returns>
private DataTable ExCsv(string path)
{
// csv file:HDR=Yes-- first line is header
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path
+ ";Extended Properties='TEXT;HDR=Yes;FMT=Delimited;CharacterSet=65001;'";
OleDbConnection oleConn = new OleDbConnection(strConn);
oleConn.Open();
DataTable sheets = oleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
DataTable dt = new DataTable();
if (sheets != null && sheets.Rows.Count > 0)
{
String fileName = sheets.Rows[0]["TABLE_NAME"].ToString(); // sheets.Rows[0] -- first sheet of excel
string olestr = "select * from [" + fileName + "]";
OleDbCommand oleComm = new OleDbCommand(olestr, oleConn);
oleComm.Connection = oleConn;
OleDbDataAdapter oleDa = new OleDbDataAdapter();
oleDa.SelectCommand = oleComm;
oleDa.Fill(dt);
oleConn.Close();
}
return dt;
}
调用时，
if (FileUpload1.HasFile)
{
//FileUpload1.SaveAs(Server.MapPath("upload") + "\\" + FileUpload1.FileName);
//这里永远只保存一个文件
string path = ConfigurationManager.AppSettings["ReturnGoodsPath"];
FileUpload1.SaveAs(path + FileUpload1.FileName);
DataTable dt = ExCsv(path);
//绑定数据源
//GridView1.DataSource = dt;
//GridView1.DataBind();
Label1.Text = "客户端路径：" + FileUpload1.PostedFile.FileName + "<br>" +
"文件名：" + System.IO.Path.GetFileName(FileUpload1.FileName) + "<br>" +
"文件扩展名：" + System.IO.Path.GetExtension(FileUpload1.FileName) + "<br>" +
"文件大小：" + FileUpload1.PostedFile.ContentLength + " KB<br>" +
"文件MIME类型：" + FileUpload1.PostedFile.ContentType + "<br>" +
"保存路径：" + Server.MapPath("upload") + "\\" + FileUpload1.FileName;
}
