.NET中的CSV导入导出
导入代码，从csv文件得到datatable
/// <summary>
/// Get Data From Csv File
/// (Through StreamReader)
/// </summary>
/// <returns></returns>
private bool GetData(Stream inputStream, out string errMessage, out DataTable dtFile)
{
errMessage = String.Empty;
dtFile = new DataTable();
//this.FileUploadImport.PostedFile.InputStream.CanSeek;
//StreamReader sr = new StreamReader(this.FileUploadImport.PostedFile.InputStream); //update by hwx 11/12/2010
StreamReader sr = new StreamReader(inputStream);
int iColumnCount = 19;//the column count in the file
int iRow = 1;// the row number is being read
int iColumn = 0;// the column number is being read
string strStandardTitle = string.Empty; // the title as it is
//read title row
string strTitle = sr.ReadLine();
//string[] strRowTitle = strTitle.Split(new char[] { ',' });
string[] strRowTitle = new string[iColumnCount];
string strCharTitle;
int iCellNumberTitle = 0;
bool blnQuoteTitle = false;
for (int i = 0; i < strTitle.Length; i++)
{
strCharTitle = strTitle.Substring(i, 1);
if (strCharTitle == ",")// "," is the seperation symbol of csv file,
{
if (!blnQuoteTitle)
{
iCellNumberTitle++;// out of the "" range, "," is the seperation symbol
if (iCellNumberTitle >= iColumnCount)// too many column in this line
{
break;
}
}
else
{
strRowTitle[iCellNumberTitle] += strCharTitle;
}
}
else if (strCharTitle == "\"")// "\"" is the transfer symbol of csv file,
{
blnQuoteTitle = !blnQuoteTitle;
if (blnQuoteTitle && i > 0 && strTitle.Substring(i - 1, 1) == "\"")//in the "" range and there is an transfer symbol before
{
strRowTitle[iCellNumberTitle] += strCharTitle;
}
}
else
{
strRowTitle[iCellNumberTitle] += strCharTitle;
}
}
//read the content
if (strRowTitle.Length == iColumnCount)
{
foreach (string strCell in strRowTitle)
{
iColumn++;
if (strCell.Trim() != string.Empty)
{
dtFile.Columns.Add(strCell);//add new column with name to the data table
}
else //file error:blank title
{
errMessage += "The cell " + iColumn.ToString() + " is blank in the header row.\r\n";
}
}
if (dtFile.Columns.Count == iColumnCount)//make sure that no blank header or error header
{
//read content row
string strLine;
while (!sr.EndOfStream)
{
iRow++;
iColumn = 0;
DataRow dr = dtFile.NewRow();
strLine = sr.ReadLine();
//read csv file line by line
string[] strRow = new string[iColumnCount];
string strChar;
int iCellNumber = 0;
bool blnQuote = false;//whether in the "" range
for (int i = 0; i < strLine.Length; i++)
{
strChar = strLine.Substring(i, 1);
if (strChar == ",")// "," is the seperation symbol of csv file,
{
if (!blnQuote)
{
iCellNumber++;// out of the "" range, "," is the seperation symbol
if (iCellNumber >= iColumnCount)// too many column in this line
{
break;
}
}
else
{
strRow[iCellNumber] += strChar;
}
}
else if (strChar == "\"")// "\"" is the transfer symbol of csv file,
{
blnQuote = !blnQuote;
if (blnQuote && i > 0 && strLine.Substring(i - 1, 1) == "\"")//in the "" range and there is an transfer symbol before
{
strRow[iCellNumber] += strChar;
}
}
else
{
strRow[iCellNumber] += strChar;
}
}
if (iCellNumber + 1 == iColumnCount)
{
foreach (string strCell in strRow)
{
iColumn++;
if (strCell != null && strCell.Trim() != string.Empty)
{
dr[strRowTitle[iColumn - 1]] = strCell.Trim();
}
else//file error:blank cell
{
dr[strRowTitle[iColumn - 1]] = String.Empty;
//errMessage += "The column \"" + strRowTitle[iColumn - 1] + "\" is blank in row " + iRow.ToString() + ".\r\n";
}
}
}
else// file error:the column count of current row do not equal to title's
{
errMessage += "There are more or less cells than title row in the row " + iRow.ToString() + ".\r\n";
}
dtFile.Rows.Add(dr);
}
}
}
else //file error:the count of columns in the file don't equal it should be
{
errMessage += "There are an incorrect number of columns in the header row compared to the template file.\r\n";
}
sr.Close();
sr.Dispose();
errMessage = errMessage.Replace("\r\n", "<br>");
return errMessage == String.Empty ? true : false;
}
/// <summary>
/// Get dataset from csv file.
/// </summary>
/// <param name="filepath"></param>
/// <param name="filename"></param>
/// <returns>Data Set</returns>
private DataSet GetDatasetFromCsv(string filepath, string filename)
{
string strconn = @"driver={microsoft text driver (*.txt; *.csv)};dbq=";
strconn += filepath; //filepath, for example: c:\
strconn += ";extensions=asc,csv,tab,txt;";
OdbcConnection objconn = new OdbcConnection(strconn);
DataSet dscsv = new DataSet();
try
{
string strsql = "select * from " + filename; //filename, for example: 1.csv
OdbcDataAdapter odbccsvdataadapter = new OdbcDataAdapter(strsql, objconn);
odbccsvdataadapter.Fill(dscsv);
return dscsv;
}
catch (Exception ex)
{
throw ex;
}
}
/// Get Data From Csv File
/// (Through StreamReader)
/// </summary>
/// <returns></returns>
private bool GetData(Stream inputStream, out string errMessage, out DataTable dtFile)
{
errMessage = String.Empty;
dtFile = new DataTable();
//this.FileUploadImport.PostedFile.InputStream.CanSeek;
//StreamReader sr = new StreamReader(this.FileUploadImport.PostedFile.InputStream); //update by hwx 11/12/2010
StreamReader sr = new StreamReader(inputStream);
int iColumnCount = 19;//the column count in the file
int iRow = 1;// the row number is being read
int iColumn = 0;// the column number is being read
string strStandardTitle = string.Empty; // the title as it is
//read title row
string strTitle = sr.ReadLine();
//string[] strRowTitle = strTitle.Split(new char[] { ',' });
string[] strRowTitle = new string[iColumnCount];
string strCharTitle;
int iCellNumberTitle = 0;
bool blnQuoteTitle = false;
for (int i = 0; i < strTitle.Length; i++)
{
strCharTitle = strTitle.Substring(i, 1);
if (strCharTitle == ",")// "," is the seperation symbol of csv file,
{
if (!blnQuoteTitle)
{
iCellNumberTitle++;// out of the "" range, "," is the seperation symbol
if (iCellNumberTitle >= iColumnCount)// too many column in this line
{
break;
}
}
else
{
strRowTitle[iCellNumberTitle] += strCharTitle;
}
}
else if (strCharTitle == "\"")// "\"" is the transfer symbol of csv file,
{
blnQuoteTitle = !blnQuoteTitle;
if (blnQuoteTitle && i > 0 && strTitle.Substring(i - 1, 1) == "\"")//in the "" range and there is an transfer symbol before
{
strRowTitle[iCellNumberTitle] += strCharTitle;
}
}
else
{
strRowTitle[iCellNumberTitle] += strCharTitle;
}
}
//read the content
if (strRowTitle.Length == iColumnCount)
{
foreach (string strCell in strRowTitle)
{
iColumn++;
if (strCell.Trim() != string.Empty)
{
dtFile.Columns.Add(strCell);//add new column with name to the data table
}
else //file error:blank title
{
errMessage += "The cell " + iColumn.ToString() + " is blank in the header row.\r\n";
}
}
if (dtFile.Columns.Count == iColumnCount)//make sure that no blank header or error header
{
//read content row
string strLine;
while (!sr.EndOfStream)
{
iRow++;
iColumn = 0;
DataRow dr = dtFile.NewRow();
strLine = sr.ReadLine();
//read csv file line by line
string[] strRow = new string[iColumnCount];
string strChar;
int iCellNumber = 0;
bool blnQuote = false;//whether in the "" range
for (int i = 0; i < strLine.Length; i++)
{
strChar = strLine.Substring(i, 1);
if (strChar == ",")// "," is the seperation symbol of csv file,
{
if (!blnQuote)
{
iCellNumber++;// out of the "" range, "," is the seperation symbol
if (iCellNumber >= iColumnCount)// too many column in this line
{
break;
}
}
else
{
strRow[iCellNumber] += strChar;
}
}
else if (strChar == "\"")// "\"" is the transfer symbol of csv file,
{
blnQuote = !blnQuote;
if (blnQuote && i > 0 && strLine.Substring(i - 1, 1) == "\"")//in the "" range and there is an transfer symbol before
{
strRow[iCellNumber] += strChar;
}
}
else
{
strRow[iCellNumber] += strChar;
}
}
if (iCellNumber + 1 == iColumnCount)
{
foreach (string strCell in strRow)
{
iColumn++;
if (strCell != null && strCell.Trim() != string.Empty)
{
dr[strRowTitle[iColumn - 1]] = strCell.Trim();
}
else//file error:blank cell
{
dr[strRowTitle[iColumn - 1]] = String.Empty;
//errMessage += "The column \"" + strRowTitle[iColumn - 1] + "\" is blank in row " + iRow.ToString() + ".\r\n";
}
}
}
else// file error:the column count of current row do not equal to title's
{
errMessage += "There are more or less cells than title row in the row " + iRow.ToString() + ".\r\n";
}
dtFile.Rows.Add(dr);
}
}
}
else //file error:the count of columns in the file don't equal it should be
{
errMessage += "There are an incorrect number of columns in the header row compared to the template file.\r\n";
}
sr.Close();
sr.Dispose();
errMessage = errMessage.Replace("\r\n", "<br>");
return errMessage == String.Empty ? true : false;
}
/// <summary>
/// Get dataset from csv file.
/// </summary>
/// <param name="filepath"></param>
/// <param name="filename"></param>
/// <returns>Data Set</returns>
private DataSet GetDatasetFromCsv(string filepath, string filename)
{
string strconn = @"driver={microsoft text driver (*.txt; *.csv)};dbq=";
strconn += filepath; //filepath, for example: c:\
strconn += ";extensions=asc,csv,tab,txt;";
OdbcConnection objconn = new OdbcConnection(strconn);
DataSet dscsv = new DataSet();
try
{
string strsql = "select * from " + filename; //filename, for example: 1.csv
OdbcDataAdapter odbccsvdataadapter = new OdbcDataAdapter(strsql, objconn);
odbccsvdataadapter.Fill(dscsv);
return dscsv;
}
catch (Exception ex)
{
throw ex;
}
}
csv导出代码 /// <summary>
/// Export to Csv File from dataset
/// </summary>
/// <param name="src"></param>
/// <param name="folderName">folderName</param>
/// <param name="strFileName">strFileName</param>
/// <returns></returns>
public bool ExportToCsv(DataSet src, string folderName, string strFileName)
{
string csv = String.Empty;
StreamWriter writer = null;
string fileName = Server.MapPath("/") + folderName + "\\" + strFileName;
try
{
if (src == null || src.Tables.Count == 0) throw new Exception("dataset is null or has not table in dataset");
for (int i = 0; i < src.Tables.Count; i++)
{
if (i > 0)
fileName = fileName.Substring(0, fileName.IndexOf('.')) + i + fileName.Substring(fileName.IndexOf("."));
writer = new StreamWriter(fileName);
DataTable dt = src.Tables[i];
StringBuilder sb = new StringBuilder();
for (int j = 0; j < dt.Columns.Count; j++)
{
string colName = dt.Columns[j].ColumnName;
if (colName.IndexOf(',') > -1)
colName = colName.Insert(0, "\"").Insert(colName.Length + 1, "\"");
sb.Append(colName);
if (!colName.Equals(""))
if (j != dt.Columns.Count - 1)
sb.Append(",");
}
writer.WriteLine(sb.ToString());
sb = new StringBuilder();
string temp = "";
for (int j = 0; j < dt.Rows.Count; j++)
{
DataRow dr = dt.Rows[j];
for (int k = 0; k < dt.Columns.Count; k++)
{
object o = dr[k];
if (o != null)
temp = o.ToString();
if (temp.IndexOf(',') > -1)
temp = temp.Insert(0, "\"").Insert(temp.Length + 1, "\"");
sb.Append(temp);
if (k != dt.Columns.Count - 1)
sb.Append(",");
}
writer.WriteLine(sb.ToString());
sb = new StringBuilder();
csv = sb.ToString();
}
writer.Close();
}
string strFilePath = Server.MapPath("/") + folderName;
if (!Directory.Exists(strFilePath))
{
Directory.CreateDirectory(strFilePath);
}
strFullFileName = Server.MapPath("/") + folderName + "\\" + fileName;
//FullFileName = Server.MapPath(FileName);
//FileName
FileInfo DownloadFile = new FileInfo(strFullFileName);
if (DownloadFile.Exists)
{
Response.Clear();
Response.ClearHeaders();
Response.Buffer = false;
Response.ContentType = "application/octet-stream";
//Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(DownloadFile.FullName, System.Text.Encoding.ASCII));
Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.ASCII));
Response.AppendHeader("Content-Length", DownloadFile.Length.ToString());
Response.WriteFile(DownloadFile.FullName);
Response.Flush();
//Response.End();
}
else
{
//not exist
throw new Exception("Export csv file does not exist!");
}
}
catch (Exception ex)
{
throw new Exception("Save csv error", ex);
}
finally
{
if (writer != null) writer.Close();
}
return true;
}
/// <summary>
/// List to DataTable
/// </summary>
/// <param name="entitys">entitys list</param>
/// <returns></returns>
public DataTable ListToDataTable(List<T> entitys)
{
//
if (entitys == null || entitys.Count < 1)
{
throw new Exception("list is null");
}
//get first Propertie
Type entityType = entitys[0].GetType();
PropertyInfo[] entityProperties = entityType.GetProperties();
//DataTable structure
//
DataTable dt = new DataTable();
for (int i = 0; i < entityProperties.Length; i++)
{
//dt.Columns.Add(entityProperties[i].Name, entityProperties[i].PropertyType);
dt.Columns.Add(entityProperties[i].Name);
}
//add entity to DataTable
foreach (object entity in entitys)
{
//check type
if (entity.GetType() != entityType)
{
throw new Exception("type not same");
}
object[] entityValues = new object[entityProperties.Length];
for (int i = 0; i < entityProperties.Length; i++)
{
entityValues[i] = entityProperties[i].GetValue(entity, null);
}
dt.Rows.Add(entityValues);
}
return dt;
}
/// Export to Csv File from dataset
/// </summary>
/// <param name="src"></param>
/// <param name="folderName">folderName</param>
/// <param name="strFileName">strFileName</param>
/// <returns></returns>
public bool ExportToCsv(DataSet src, string folderName, string strFileName)
{
string csv = String.Empty;
StreamWriter writer = null;
string fileName = Server.MapPath("/") + folderName + "\\" + strFileName;
try
{
if (src == null || src.Tables.Count == 0) throw new Exception("dataset is null or has not table in dataset");
for (int i = 0; i < src.Tables.Count; i++)
{
if (i > 0)
fileName = fileName.Substring(0, fileName.IndexOf('.')) + i + fileName.Substring(fileName.IndexOf("."));
writer = new StreamWriter(fileName);
DataTable dt = src.Tables[i];
StringBuilder sb = new StringBuilder();
for (int j = 0; j < dt.Columns.Count; j++)
{
string colName = dt.Columns[j].ColumnName;
if (colName.IndexOf(',') > -1)
colName = colName.Insert(0, "\"").Insert(colName.Length + 1, "\"");
sb.Append(colName);
if (!colName.Equals(""))
if (j != dt.Columns.Count - 1)
sb.Append(",");
}
writer.WriteLine(sb.ToString());
sb = new StringBuilder();
string temp = "";
for (int j = 0; j < dt.Rows.Count; j++)
{
DataRow dr = dt.Rows[j];
for (int k = 0; k < dt.Columns.Count; k++)
{
object o = dr[k];
if (o != null)
temp = o.ToString();
if (temp.IndexOf(',') > -1)
temp = temp.Insert(0, "\"").Insert(temp.Length + 1, "\"");
sb.Append(temp);
if (k != dt.Columns.Count - 1)
sb.Append(",");
}
writer.WriteLine(sb.ToString());
sb = new StringBuilder();
csv = sb.ToString();
}
writer.Close();
}
string strFilePath = Server.MapPath("/") + folderName;
if (!Directory.Exists(strFilePath))
{
Directory.CreateDirectory(strFilePath);
}
strFullFileName = Server.MapPath("/") + folderName + "\\" + fileName;
//FullFileName = Server.MapPath(FileName);
//FileName
FileInfo DownloadFile = new FileInfo(strFullFileName);
if (DownloadFile.Exists)
{
Response.Clear();
Response.ClearHeaders();
Response.Buffer = false;
Response.ContentType = "application/octet-stream";
//Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(DownloadFile.FullName, System.Text.Encoding.ASCII));
Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.ASCII));
Response.AppendHeader("Content-Length", DownloadFile.Length.ToString());
Response.WriteFile(DownloadFile.FullName);
Response.Flush();
//Response.End();
}
else
{
//not exist
throw new Exception("Export csv file does not exist!");
}
}
catch (Exception ex)
{
throw new Exception("Save csv error", ex);
}
finally
{
if (writer != null) writer.Close();
}
return true;
}
/// <summary>
/// List to DataTable
/// </summary>
/// <param name="entitys">entitys list</param>
/// <returns></returns>
public DataTable ListToDataTable(List<T> entitys)
{
//
if (entitys == null || entitys.Count < 1)
{
throw new Exception("list is null");
}
//get first Propertie
Type entityType = entitys[0].GetType();
PropertyInfo[] entityProperties = entityType.GetProperties();
//DataTable structure
//
DataTable dt = new DataTable();
for (int i = 0; i < entityProperties.Length; i++)
{
//dt.Columns.Add(entityProperties[i].Name, entityProperties[i].PropertyType);
dt.Columns.Add(entityProperties[i].Name);
}
//add entity to DataTable
foreach (object entity in entitys)
{
//check type
if (entity.GetType() != entityType)
{
throw new Exception("type not same");
}
object[] entityValues = new object[entityProperties.Length];
for (int i = 0; i < entityProperties.Length; i++)
{
entityValues[i] = entityProperties[i].GetValue(entity, null);
}
dt.Rows.Add(entityValues);
}
return dt;
}
