winform 导出excel文件
//1） 文本：vnd.ms-excel.numberformat:@
//2） 日期：vnd.ms-excel.numberformat:yyyy/mm/dd
//3） 数字：vnd.ms-excel.numberformat:#,##0.00
//4） 货币：vnd.ms-excel.numberformat:￥#,##0.00
//5） 百分比：vnd.ms-excel.numberformat: #0.00%
/// <summary>
/// 导出Excel(按照拖到的列的顺序)(流)
/// </summary>
/// <param name="fileName">窗体的文本名称</param>
/// <param name="myDGV">要导出的DatagridView数据</param>
public static void PrintDataGridViewSave(string fileName, DataGridView myDGV)//另存新档按钮 导出成Excel
{
if (myDGV.Rows.Count > 0)
{
SaveFileDialog saveFileDialog = new SaveFileDialog(); //保存文件
//设置文件类型
//saveFileDialog.Filter = " txt files(*.txt)|*.txt|All files(*.*)|*.*";
saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
saveFileDialog.FilterIndex = 0; //设置文件显示顺序；
saveFileDialog.RestoreDirectory = true; //保存对话框是否记忆上次打开的目录
saveFileDialog.CreatePrompt = true;
saveFileDialog.Title = "保存为Excel文件";
saveFileDialog.FileName = fileName;
saveFileDialog.ShowDialog();
if (saveFileDialog.FileName.IndexOf(":") < 0) return; //被点了"取消"
Thread.Sleep(1000); //休眠1秒
Stream myStream;
myStream = saveFileDialog.OpenFile();
StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0)); //生成文件
//StreamWriter sw = new StreamWriter(fileName, false, System.Text.Encoding.GetEncoding("gb2312"));
StringBuilder sb = new StringBuilder();
try
{
string[] array = new string[myDGV.Columns.Count];
//获取Visble =true 的列
foreach (DataGridViewColumn column in myDGV.Columns)
{
if (column.Visible == true)
{
array[column.DisplayIndex] = column.HeaderText + '|' + column.Name;
}
}
int RowsCount = myDGV.Rows.Count;
int ColumnsCount = array.Length;
//取列名称
for (int i = 0; i < ColumnsCount; i++)
{
string[] str = new string[2];
try
{
str = array.GetValue(i).ToString().Split('|');
sb.Append(@"=""" + str[0] + @"""" + "\t"); //加"''"防止科 学计数法
}
catch
{
continue;
}
}
sb.Append(Environment.NewLine); //换行字符
//导出列内容
for (int j = 0; j < RowsCount; j++)
{
//int mm = 0;
System.Windows.Forms.Application.DoEvents();
for (int k = 0; k < ColumnsCount; k++)
{
string[] str = new string[2];
if (array.GetValue(k) == null)
continue;
str = array.GetValue(k).ToString().Split('|');
sb.Append(@"=""" + myDGV.Rows[j].Cells[str[1]].FormattedValue.ToString().Trim() + @"""" + "\t");
}
sb.Append(Environment.NewLine);
}
sw.WriteLine(sb.ToString());
sw.Flush();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
finally
{
sw.Close();
myStream.Close();
}
}
}
//数据源，文件名称
public void WriteExcel(DataTable ds, string path)
{
try
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true;
saveFileDialog.Title = "保存为Excel文件";
saveFileDialog.FileName = path;
saveFileDialog.ShowDialog();
if (saveFileDialog.FileName.IndexOf(":") < 0) return; //被点了"取消"
StreamWriter sw = new StreamWriter(path, false, Encoding.GetEncoding("gb2312"));
StringBuilder sb = new StringBuilder();
for (int k = 0; k < ds.Columns.Count; k++)
{
sb.Append(ds.Columns[k].ColumnName.ToString() + "\t");
}
sb.Append(Environment.NewLine);
for (int i = 0; i < ds.Rows.Count; i++)
{
for (int j = 0; j < ds.Columns.Count; j++)
{
sb.Append(ds.Rows[i][j].ToString() + "\t");
}
sb.Append(Environment.NewLine);//每写一行数据后换行
}
sw.Write(sb.ToString());
sw.Flush();
sw.Close();//释放资源
MessageBox.Show("已经生成指定Excel文件!");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
