/// <summary>
/// 将DataGirdView数据,导出EXCEL文件.
/// </summary>
/// <param name="dgv">DataGridView控件名称</param>
/// <param name="name">导出excel文件名称</param>
public static void ToExcel(DataGridView dgv, string name)
{
try
{
//总可见列数,总可见行数
int colCount = dgv.Columns.GetColumnCount(DataGridViewElementStates.Visible);
int rowCount = dgv.Rows.GetRowCount(DataGridViewElementStates.Visible);
//dataGridView 没有数据提示
if (dgv.Rows.Count == 0 || rowCount == 0)
{
MessageBox.Show("列表中没有数据无法导出!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
SaveFileDialog save = new SaveFileDialog();
save.Filter = "excel files(*.xls)|*.xls";
save.Title = "请选择要导出数据的位置";
save.FileName = name + DateTime.Now.ToLongDateString();
save.InitialDirectory = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Desktop);
if (save.ShowDialog() == DialogResult.OK)
{
XlsDocument xls = new XlsDocument();//创建空xls文档
xls.FileName = save.FileName;//保存路径,如果直接发送到客户端的话只需要名称 生成名称
Worksheet sheet = xls.Workbook.Worksheets.Add(name); //创建一个工作页为Dome
//设置文档列属性
ColumnInfo cinfo = new ColumnInfo(xls, sheet);//设置xls文档的指定工作页的列属性
cinfo.Collapsed = true;
//设置列的范围 如 0列-10列
cinfo.ColumnIndexStart = 0;//列开始
cinfo.ColumnIndexEnd = 10;//列结束
cinfo.Collapsed = true;
cinfo.Width = 90 * 60;//列宽度
sheet.AddColumnInfo(cinfo);
//设置文档列属性结束
//设置指定工作页跨行跨列
MergeArea ma = new MergeArea(1, 1, 1, dgv.ColumnCount);//从第1行跨到第二行,从第一列跨到第5列
sheet.AddMergeArea(ma);
//设置指定工作页跨行跨列结束
//创建列样式创建列时引用
XF cellXF = xls.NewXF();
cellXF.VerticalAlignment = VerticalAlignments.Centered;
cellXF.HorizontalAlignment = HorizontalAlignments.Centered;
cellXF.Font.Height = 24 * 12;
cellXF.Font.Bold = true;
cellXF.Pattern = 0;//设定单元格填充风格。如果设定为0,则是纯色填充
cellXF.PatternBackgroundColor = Colors.Black;//填充的背景底色
cellXF.PatternColor = Colors.Black;//设定填充线条的颜色
//创建列样式结束
//创建列
Cells cells = sheet.Cells; //获得指定工作页列集合
//列操作基本
Cell cell = cells.Add(1, 1, name, cellXF);//添加标题列返回一个列 参数:行 列 名称 样式对象
//设置XY居中
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell.VerticalAlignment = VerticalAlignments.Centered;
//设置字体
cell.Font.Bold = true;//设置粗体
cell.Font.ColorIndex = 0;//设置颜色码
cell.Font.FontFamily = FontFamilies.Roman;//设置字体 默认为宋体
//创建列结束
//生成字段名称
int k = 0;
for (int i = 0; i < dgv.ColumnCount; i++)
{
if (dgv.Columns[i].Visible) //不导出隐藏的列
{
Cell title = cells.Add(2, k + 1, dgv.Columns[i].HeaderText);
title.HorizontalAlignment = HorizontalAlignments.Centered;
title.VerticalAlignment = VerticalAlignments.Centered;
k++;
}
}
//填充数据
for (int i = 0; i < dgv.RowCount; i++)
{
k = 1;
for (int j = 0; j < dgv.ColumnCount; j++)
{
if (dgv.Columns[j].Visible) //不导出隐藏的列
{
if (dgv[j, i].ValueType == typeof(string))
{
cells.Add(i + 3, k, "" + dgv[j, i].Value.ToString());
}
else
{
string str = dgv[j, i].Value.ToString();
if (str == "True")
{
str = "是";
}
else if(str =="False")
{
str = "否";
}
cells.Add(i + 3, k, str);
}
}
k++;
}
}
xls.Save(true);//保存
MessageBox.Show("Excel文件导出成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ce)
{
MessageBox.Show(ce.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}