DataTable转Csv,Excel(转)
通过DataTable或DataSet转出
//DataTable --> Csv
此处可以转出例如(*.CVS,*.TXT,*.DOC) A 等文件，但是由于excel的格式问题，所以无法转换。转换过程中只需要增加保存文件的过滤格式即可。
this.xxx.Filter = "A 文件|A";
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
private void SaveCsv(string filename , DataTable dt)
{
FileStream fs = new FileStream(filename, FileMode.Create, FileAccess.Write); // 获取filename的路径，给予创建和写入的权利
StreamWriter sw = new StreamWriter(fs,Encoding.Default);//streamreader允许将字符和字符串写入filestream，使用filestream创建streamwriter
string data = "";
for (int i = 0; i < dt.Columns.Count; i++)
{
data += dt.Columns[i].ColumnName;
if (i < dt.Columns.Count - 1)
{
data += ",";
}
}
sw.WriteLine(data);
for (int i = 0; i < dt.Rows.Count; i++)
{
data ="";
for (int j = 0; j < dt.Columns.Count; j++)
{
data += dt.Rows[i][j].ToString();
if (j < dt.Columns.Count - 1)
{
data += ",";
}
}
}
sw.WriteLine(data,false);//写入数据进入文件，false(没有创建新的，有就覆盖)true（保留原数据，没有则新建）
sw.Close();//不忘释放资源
fs.Close();
}
//DataTable-->Excel
//DataTable-->Excel
此方法速度较慢，难以处理大数据
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
public void Export_Excel(DataSet ds)
{
DataTable dt = ds.Tables[0];
int row = dt.Rows.Count;
if (row == 0)
{
MessageBox.Show("There is no Data found !");
}
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Add(true);
excel.Visible = true;
for (int i = 0; i < dt.Columns.Count ; i++)
{
excel.Cells[1, i + 1] = dt.Columns[i].ColumnName.ToString();
}
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
excel.Cells[i + 2, j + 1] = "'" + dt.Rows[i].ItemArray[j];
}
}
} 