第一步:添加Microsoft.Office.Interop
第二步:写代码(...刚接触WPF 菜鸟一个,不知道咋描述)
private void button1_Click(object sender, RoutedEventArgs e)
{
//if (this.lvBaoj.ItemsSource == null)
//{
// MessageBox.Show("没有可导出的数据!");
// return;
//}
//
System.Data.DataTable dt = new System.Data.DataTable();
dt.Columns.Add("报警点");
dt.Columns.Add("区域");
dt.Columns.Add("类型");
dt.Columns.Add("主监控摄像机");
dt.Columns.Add("短信通知模板");
dt.Columns.Add("短信通知人员");
for (int i = 0; i < bjds.ToArray().Length; i++)
{
DataRow dr = dt.NewRow();
dr[0] = bjds[i].Name;
dr[1] = bjds[i].Qy;
dr[2] = bjds[i].Lx;
dr[3] = bjds[i].Zjkxj;
dr[4] = bjds[i].Dxmb;
dr[5] = bjds[i].Dxtzry;
dt.Rows.Add(dr);
}
//MessageBox.Show(dt.Rows[0][0].ToString());
///
//System.Data.DataTable dtt = (this.lvBaoj.ItemsSource as DataView).Table;
//创建Excel
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
Workbook excelWB = excelApp.Workbooks.Add(System.Type.Missing); //创建工作簿(WorkBook:即Excel文件主体本身)
Worksheet excelWS = (Worksheet)excelWB.Worksheets[1]; //创建工作表(即Excel里的子表sheet) 1表示在子表sheet1里进行数据导出
//excelWS.Cells.NumberFormat = "@"; // 如果数据中存在数字类型 可以让它变文本格式显示
//将数据导入到工作表的单元格
for (int i = 0; i < dt.Columns.Count; i++)
{
excelWS.Cells[1, i + 1] = dt.Columns[i].ColumnName;
}
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
excelWS.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString(); //Excel单元格第一个从索引1开始
}
}
//使用对话框让用户选择保存位置和填写保存的文件名
Microsoft.Win32.SaveFileDialog sfd = new Microsoft.Win32.SaveFileDialog();
sfd.Filter = "Excel工作簿(*.xlsx)|*.xlsx";
sfd.FilterIndex = 1;
sfd.RestoreDirectory = true;
if (sfd.ShowDialog() == true)
{
string localFilePath = sfd.FileName.ToString(); //获得文件路径
string fileNameExt = localFilePath.Substring(localFilePath.LastIndexOf("\\") + 1); //获取文件名,不带路径
//获取文件路径,不带文件名
//FilePath = localFilePath.Substring(0, localFilePath.LastIndexOf("\\"));
//给文件名前加上时间
//newFileName = DateTime.Now.ToString("yyyyMMdd") + fileNameExt;
//在文件名里加字符
//saveFileDialog1.FileName.Insert(1,"dameng");
//System.IO.FileStream fs = (System.IO.FileStream)sfd.OpenFile();//输出文件
fs输出带文字或图片的文件,就看需求了
excelWB.SaveAs(localFilePath); //将其进行保存到指定的路径
excelWB.Close();
excelApp.Quit(); //KillAllExcel(excelApp); 释放可能还没释放的进程
MessageBox.Show("导出表格成功!");
System.Diagnostics.Process.Start(localFilePath);//保存完成后 打开文件
}
}
部分内容来自:小和尚-移动互联网的C# SaveFileDialog的用法
附加:
*Excel 输出部分样式设置
#region 新方法
Worksheet excelWS = (Worksheet)excelWB.Worksheets[k + 1]; //创建工作表(即Excel里的子表sheet) 1表示在子表sheet1里进行数据导出
//表头
Range range;//获取range对象
range = excelWS.get_Range("A1", "F1");
range.Font.Size = 24;//表头字体大小
range.Merge(0);//合并单元格
excelWS.Cells[1][1] = "个人考勤记录核对表";//表头单元格内容
excelWS.Rows[1].RowHeight = 44;//表头单元格行高
excelWS.Cells[1][1].VerticalAlignment = XlVAlign.xlVAlignCenter;//垂直居中
excelWS.Cells[1][1].HorizontalAlignment = XlHAlign.xlHAlignCenter;//水平居中
//设置列宽
excelWS.StandardWidth = 14;//全局列宽
excelWS.Columns[1].ColumnWidth = 12;//设置单列列宽
excelWS.Columns[2].ColumnWidth = 6;//设置单列列宽
excelWS.Columns[3].ColumnWidth = 18;//设置单列列宽
excelWS.Columns[4].ColumnWidth = 18;//设置单列列宽
excelWS.Columns[5].ColumnWidth = 12;//设置单列列宽
excelWS.Columns[6].ColumnWidth = 12;//设置单列列宽
//二级表头
Range r1;//获取range对象
r1 = excelWS.get_Range("A2", "B2");
r1.Merge(0);//合并单元格
r1.Font.Size = 20;//二级表头字体大小
excelWS.Cells[1][2] = name;//姓名
excelWS.Cells[1][2].VerticalAlignment = XlVAlign.xlVAlignCenter;//垂直居中
excelWS.Cells[1][2].HorizontalAlignment = XlHAlign.xlHAlignCenter;//水平居中
excelWS.Cells[3][2] = "部门 " + company;//部门
excelWS.Cells[3][2].VerticalAlignment = XlVAlign.xlVAlignCenter;//垂直居中
excelWS.Cells[4][2] = "员工号 " + number;
excelWS.Cells[4][2].VerticalAlignment = XlVAlign.xlVAlignCenter;//垂直居中
excelWS.Cells[5][2] = "异常情况说明";
excelWS.Cells[5][2].VerticalAlignment = XlVAlign.xlVAlignCenter;//垂直居中
excelWS.Cells[6][2] = "部门主管签字";
excelWS.Cells[6][2].VerticalAlignment = XlVAlign.xlVAlignCenter;//垂直居中
Range r2;//给A2 - F2的单元格加边框
r2 = excelWS.get_Range("A2:F2");
r2.Borders.LineStyle = "1";
#endregion