C# dataGridView导出EXCEL,保存前显示全部数据
C# dataGridView导出EXCEL,保存前显示全部数据 ,因为导出的EXCEL不需要保存的只作临时文件
原来在网上找到的代码点击按钮后导出EXCEL之前要填写文件名及路径, 但现在想要的是导出EXCEL后暂时不保存而显示EXCEL里面的所有内容项.
private void btnExcel_Click(object sender, EventArgs e)
{
string fileName = "Book1";
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = fileName;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
//取消
int r = 0;
if (saveFileName.IndexOf(":") < 0)
{
return;
}
//创建Excel对象
Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的电脑未安装Excel");
return;
}
Excel.Workbooks workbooks = xlApp.Workbooks;
//工作簿
Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
//Sheet
Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
//写入Excel第一行
Excel.Range Range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 3]);//Range对象
Range.MergeCells = true;//合并单元格
worksheet.Cells[1, 1] = "销售订单展开表";//单元格内文本
//Range.Font.Name = "黑体";//字体
Range.Font.Size = 12;//字体大小
//标题格式
Microsoft.Office.Interop.Excel.Range Range2 = worksheet.get_Range("A2", "C2");
//Range2.Font.Name = "黑体";
Range2.Font.Size = 12;
Range2.Interior.Pattern = Excel.XlSortType.xlSortValues;
//上边框加粗
Range2.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;
Range2.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft).Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;
Range2.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight).Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;
Range2.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;
//写入标题
for (int i = 0; i < dataGridView1.ColumnCount; i++)
{
worksheet.Cells[2, i + 1] = dataGridView1.Columns[i].HeaderText;
}
//写入数值
for (r = 0; r < dataGridView1.Rows.Count; r++)
{
for (int i = 0; i < dataGridView1.ColumnCount; i++)
{
worksheet.Cells[r + 3, i + 1] = dataGridView1.Rows[r].Cells[i].Value;
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
//保存
workbook.Saved = true;
//获得保存路径
workbook.SaveCopyAs(saveFileName);
object TM = System.Type.Missing;
Excel.Application excel = new Excel.Application();
//打开已经写入值的EXCEL进行操作
//Excel.Workbook book = excel.Application.Workbooks.Add(saveFileName);
//Excel.Worksheet sheet = (Excel.Worksheet)book.Sheets[1];
//要汇总的列
//int[] totile = new int[1] { 3 };
//以第二列分组汇总
//sheet.get_Range(sheet.Cells[2, 1], sheet.Cells[r + 3, 3]).Subtotal(2, Excel.XlConsolidationFunction.xlSum, totile, TM, TM, Excel.XlSummaryRow.xlSummaryBelow);
//获得写入的总行
//int count = sheet.UsedRange.Rows.Count;
//获得A、B、C列
//string A = "A" + count;
//string B = "B" + count;
//string C = "C" + count;
//Excel.Range RangeA = sheet.get_Range("A2", A);
//Excel.Range RangeB = sheet.get_Range("B2", B);
//Excel.Range RangeC = sheet.get_Range("C2", C);
//字体
//RangeA.Font.Name = "Arial";
//字体大小
//RangeA.Font.Size = 8;
//RangeA.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft).Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
//RangeA.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft).Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;
//RangeC.NumberFormatLocal = "#,##0.00_);(#,##0.00)";//格式
//RangeC.Font.ColorIndex = 5;//字体颜色
//边框
//RangeC.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft).Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
//RangeC.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight).Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;
//sheet.get_Range(A, C).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;
//是否打开
excel.Visible = true;
//保存
//book.Saved = true;
//book.SaveCopyAs(saveFileName);
excel.Quit();
xlApp.Quit();
if (excel != null)
{
excel.Quit();
}
if (xlApp != null)
{
xlApp.Quit();
}
GC.Collect();//强行销毁
//MessageBox.Show("Excel导出成功!" + "共写入" + count + "行");
MessageBox.Show("Excel导出成功!");
}
[解决办法]
C# DataGridView控件中数据导出到Excel
方法一：
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Reflection;
using Microsoft.Office.Interop.Excel;
namespace TestEXCLE
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
_DataGridView = this.dataGridView1;
ExportToExcel("www");
}
private DataGridView _DataGridView;
private string _FileName = "";
///// <summary>
///// 构造函数
///// </summary>
///// <param name="ownerForm">拥用者窗体</param>
///// <param name="dataGridView">DataGridView对象</param>
public Form1(Form ownerForm, DataGridView dataGridView)
{
_DataGridView = dataGridView;
}
/// <summary>
/// 导出到Excel
/// </summary>
/// <param name="pfileName">文件名,不需要带扩展名</param>
public void ExportToExcel(string pfileName)
{
_FileName = pfileName == null ? "未命名" : pfileName.Trim();
string fileName = ShowSaveFileDialog("Microsoft Excel Document", "Microsoft Excel
------解决方案--------------------
*.xls");
if (fileName != "")
{
try
{
ExportTo(fileName);
OpenFile(fileName);
}
catch (System.Exception err)
{
MessageBox.Show(err.Message);
}
}
}
/// <summary>
/// 导出到
/// </summary>
/// <param name="fileName">文件名</param>
private void ExportTo(string fileName)
{
//
//获取指定文件是否存在
bool isExist = System.IO.File.Exists(fileName);
//
//定义一个缺少的object对象
object oMis = System.Reflection.Missing.Value;
//
//定义一个Excel区域对象,用于保存选择的区域
Microsoft.Office.Interop.Excel.Range selectRange;
//
//声明一Excel Application 对象
Microsoft.Office.Interop.Excel.ApplicationClass App = null;
//
//声明一Excel Workbook 对象
Microsoft.Office.Interop.Excel.Workbook wb = null;
//
//声明一Excel Worksheet 对象
Microsoft.Office.Interop.Excel.Worksheet ws = null;
//
//将当前水标状态保存到临时变量中后将光标置为忙状态
Cursor currentCursor = Cursor.Current;
Cursor.Current = Cursors.WaitCursor;
try
{
App = new Microsoft.Office.Interop.Excel.ApplicationClass();
//
//判断指定的文件是否存在
if (isExist)
{
//
//打开已存在的工作薄 Workbook
wb = App.Workbooks.Open(fileName, oMis, oMis, oMis, oMis, oMis, oMis, oMis, oMis, oMis, oMis, oMis, oMis, oMis, oMis);
//新建工作表
ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.Add(oMis, oMis, 1, oMis);
//指定工作表名
ws.Name = this.Text + wb.Sheets.Count; //加 wb.Sheets.Count 防止重名
}
else
{
//
//增加一工作薄 Workbook
wb = App.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
//新建工作薄后默认有一个工作表,取得第一个工作表
ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Sheets[1];
//指定工作表名
ws.Name = this.Text;
}
//
//增加一工作表 Worksheet
//
//声明两个变量记录Excle当前操作的行号与列号
int rown = 0;
int coln = 0;
//
//声明一变量用于记录当前DatagridView的总行数
int colCount = _DataGridView.Columns.Count;
_DataGridView.SuspendLayout();
//
//将DataGirdView列头写入Excel中
foreach (DataGridViewColumn dgvc in _DataGridView.Columns)
{
selectRange = ws.get_Range(ws.Cells[rown + 1, ++coln], ws.Cells[rown + 1, coln]);
selectRange.Columns.ColumnWidth = dgvc.Width / 10;
selectRange.Interior.ColorIndex = 16;
selectRange.Interior.Pattern = Microsoft.Office.Interop.Excel.Constants.xlSolid;
selectRange.Font.ColorIndex = 2;
selectRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
selectRange.VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
selectRange.set_Item(1, 1, dgvc.HeaderText);
}
//
//行号增加一
rown++;
//
//将DataGridView中所有的行数写入Excel中
foreach (DataGridViewRow dgvr in _DataGridView.Rows)
{
for (int i = 0; i < colCount; i++)
{
selectRange = ws.get_Range(ws.Cells[rown + 1, i + 1], ws.Cells[rown + 1, i + 1]);
selectRange.set_Item(1, 1, dgvr.Cells[i].Value);
}
rown++;
}
//
//写入完成后将有数据的范围内设置其边框与内部线条
ws.UsedRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Weight = 3;
ws.UsedRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight = 3;
ws.UsedRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight = 3;
ws.UsedRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight = 3;
ws.UsedRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = 2;
ws.UsedRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = 2;
//
//设置Wookbook为已保存状态
wb.Saved = true;
if (isExist)
{
wb.Save();
}
else
{
//
//将当前工作薄保存为指定的文件名
wb.SaveCopyAs(fileName);
}
}
catch (Exception exp)
{
MessageBox.Show(exp.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
_DataGridView.ResumeLayout();
wb.Close(false, null, null);
//
//退出Excel程序
App.Quit();
//
//将当前光标更改回原来的状态
Cursor.Current = currentCursor;
}
}
/// <summary>
/// 打开文件
/// </summary>
/// <param name="fileName">文件名</param>
private void OpenFile(string fileName)
{
if (MessageBox.Show("你想打开这个文件吗?", "导出到...", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
try
{
System.Diagnostics.Process process = new System.Diagnostics.Process();
process.StartInfo.FileName = fileName;
process.StartInfo.Verb = "Open";
process.StartInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Normal;
process.Start();
}
catch
{
MessageBox.Show(this, "你的计算机中未安装Excel,不能打开该文档!" , this.ProductName
, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
/// <summary>
/// 显示保存文件对话框,并返回选择的文件路径
/// </summary>
/// <param name="title">对话框的标题</param>
/// <param name="filter">过滤器</param>
/// <returns>选择的文件路径</returns>
private string ShowSaveFileDialog(string title, string filter)
{
SaveFileDialog dlg = new SaveFileDialog();
string name = _FileName;
int n = name.LastIndexOf(".") + 1;
if (n > 0) name = name.Substring(n, name.Length - n);
dlg.Title = "导出到" + title;
dlg.FileName = name;
dlg.Filter = filter;
if (dlg.ShowDialog() == DialogResult.OK) return dlg.FileName;
return "";
}
private void Form1_Load(object sender, EventArgs e)
{
// TODO: 这行代码将数据加载到表“testDataSet2.chengji”中。您可以根据需要移动或移除它。
this.chengjiTableAdapter.Fill(this.testDataSet2.chengji);
}
}
}
