注意:当Excel需要引用的时候请:项目--菜单 --- 添加引用 ---COM ---Microsoft Excel 11.0 Object Library --- 确定
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Diagnostics;
using System.Threading;
namespace CsharpTest
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void btnAdd_Click(object sender, EventArgs e)
{
foreach(Control c in this.panelEx1.Controls)
{
if(c is DevComponents.DotNetBar.Controls.TextBoxX)
{
c.Text = "123";
}else
{
c.Text = "名称!";
}
}
}
private void btnClear_Click(object sender, EventArgs e)
{
this.Cursor = Cursors.WaitCursor;//鼠标沙漏形状
string templetFile = Application.StartupPath + "\\Excel模板文件\\农林牧渔业洪涝灾害统计表.xls";
if (File.Exists(templetFile))
{
string outputFile = GetOutputFile("农林牧渔业洪涝灾害统计表.xls");//文件另存为
if (outputFile == "")
{
this.Cursor = Cursors.Default;
return;
}
Excel.Application App = null;
Excel.Workbook Wb = null;
DateTime beforeTime = DateTime.Now; //用于记录Excel进程打开之前的时间
DateTime afterTime = DateTime.Now; //用于记录Excel进程打开之后的时间
try
{
beforeTime = DateTime.Now;
App = new Excel.Application();
App.Visible = true;
afterTime = DateTime.Now;
Wb = GetWorkBook(App, templetFile);
if (Wb != null)
{
Excel.Worksheet Wsheet1 = (Excel.Worksheet)Wb.Worksheets.get_Item(1);
WriteDataToExcel(Wsheet1);
SaveData(Wb, outputFile);
}
else
{
MessageBox.Show(templetFile + "文件打开失败!", "提示");
}
}
catch (SystemException ex)
{
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
this.Cursor = Cursors.Default;
}
}
else
{
MessageBox.Show(templetFile + "文件不存在!", "提示");
}
}
/// <summary>
/// 保存数据
/// </summary>
private void SaveData(Excel.Workbook workBook, string outputFile)
{
try
{
//保存文件
workBook.SaveAs(outputFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 获得保存文件
/// </summary>
private string GetOutputFile(string saveFileName)
{
string outputFile = "";
SaveFileDialog mySaveFileDialog = new SaveFileDialog();
mySaveFileDialog.FileName = saveFileName;
mySaveFileDialog.OverwritePrompt = false;
mySaveFileDialog.Filter = "Excel文件|*.xls|所有文件|*.*";
mySaveFileDialog.DefaultExt = "xls";
mySaveFileDialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
if (mySaveFileDialog.ShowDialog() == DialogResult.OK)
{
outputFile = mySaveFileDialog.FileName;
}
return outputFile;
}
/// <summary>
/// 获得workBook
/// </summary>
private Excel.Workbook GetWorkBook(Excel.Application app, string templetFile)
{
try
{
Excel.Workbook wb = app.Workbooks.Open(templetFile, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlPlatform.xlWindows,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
return wb;
}
catch
{
return null;
}
}
/// <summary>
/// 写入数据
/// </summary>
private void WriteDataToExcel(Excel.Worksheet workSheet)
{
try
{
//填充数据
int i = 0;
for (i = 0; i < dgv.dgvInfo.RowCount; i++)
{
int j = 1;
foreach (DataGridViewColumn col in dgv.dgvInfo.Columns)
{
if (col.Visible == true)
{
if (dgv.dgvInfo[col.Name, i] != null && dgv.dgvInfo[col.Name, i].Value.ToString() != "")
{
workSheet.Cells[i + 11, j] = dgv.dgvInfo[col.Name, i].Value.ToString();
}
j++;
}
}
}
workSheet.Cells[22, 2] = this.labDWFZR.Text;
workSheet.Cells[22, 5] = this.labTJFZR.Text;
workSheet.Cells[22, 9] = this.labTBR.Text;
if (this.tv.SelectedNode != null)
{
workSheet.Cells[22, 12] = this.tv.SelectedNode.Text;
}
}
catch (Exception ex)
{
throw ex;
}
}
}
}