将数据网格视图导出到Excel时遇到问题
本文关键字：Excel 遇到 问题 数据 数据网 网格 视图 | 更新日期: 2023-09-27 18:27:37
我正在尝试创建一个应用程序，该应用程序可以查询SQL数据库，然后在单击excel按钮时导出网格视图，以便导入到我们的工资单系统。如果我去掉下面的代码行，我得到的唯一错误是Payroll.Form1.Submit_Click(object, System.EventArgs)必须声明一个主体，因为它没有标记为抽象、外部或部分。如果我把主体放回，那么我得到了22个错误。有人能告诉我正确的方向吗？我的完整代码如下：
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 Excel = Microsoft.Office.Interop.Excel;
using System.Data.SqlClient;
using Microsoft.Office.Interop.Excel;
using System.IO;
namespace Payroll
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void fillToolStripButton_Click(object sender, EventArgs e)
{
try
{
this.mIS_FTTIMECARTSQL1TableAdapter.Fill(this.fabTrolMRPDataSet.MIS_FTTIMECARTSQL1, new System.Nullable<System.DateTime>(((System.DateTime)(System.Convert.ChangeType(fromdateToolStripTextBox.Text, typeof(System.DateTime))))), new System.Nullable<System.DateTime>(((System.DateTime)(System.Convert.ChangeType(todateToolStripTextBox.Text, typeof(System.DateTime))))));
}
catch (System.Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message);
}
}
private void Submit_Click(object sender, EventArgs e);
}
DataTableToExcel();
{
public void DataTableToExcel()
{
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
try
{
// creating new WorkBook within Excel application
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
// creating new Excelsheet in workbook
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
// see the excel sheet behind the program
app.Visible = true;
// get the reference of first sheet. By default its name is Sheet1.
// store its reference to worksheet
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
// changing the name of active sheet
worksheet.Name = "Employees";
// storing header part in Excel
for (int i = 1; i < mIS_FTTIMECARTSQL1DataGridView.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = mIS_FTTIMECARTSQL1DataGridView.Columns[i - 1].HeaderText;
}
// storing Each row and column value to excel sheet
for (int i = 0; i <= mIS_FTTIMECARTSQL1DataGridView.Rows.Count - 1; i++)
{
for (int j = 0; j < mIS_FTTIMECARTSQL1DataGridView.Columns.Count; j++)
{
string values = string.Empty;
values = mIS_FTTIMECARTSQL1DataGridView.Rows[i].Cells[j].Value.ToString();
worksheet.Cells[i + 2, j + 1] = values;
}
}
}
finally
{
//Release the resources
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
}
}
}
}
这是一个方法声明，所以它必须有一个主体
更改private void Submit_Click(object sender, EventArgs e);，因为它是一个事件处理程序，所以应该受到保护（可从设计器访问）
至
protected void Submit_Click(object sender, EventArgs e)
{
//call your code to export to excel
}
完整代码
namespace Payroll
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void fillToolStripButton_Click(object sender, EventArgs e)
{
try
{
this.mIS_FTTIMECARTSQL1TableAdapter.Fill(this.fabTrolMRPDataSet.MIS_FTTIMECARTSQL1, new System.Nullable<System.DateTime>(((System.DateTime)(System.Convert.ChangeType(fromdateToolStripTextBox.Text, typeof(System.DateTime))))), new System.Nullable<System.DateTime>(((System.DateTime)(System.Convert.ChangeType(todateToolStripTextBox.Text, typeof(System.DateTime))))));
}
catch (System.Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message);
}
}
protected void Submit_Click(object sender, EventArgs e)
{
DataTableToExcel();
}
public void DataTableToExcel()
{
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
try
{
// creating new WorkBook within Excel application
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
// creating new Excelsheet in workbook
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
// see the excel sheet behind the program
app.Visible = true;
// get the reference of first sheet. By default its name is Sheet1.
// store its reference to worksheet
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
// changing the name of active sheet
worksheet.Name = "Employees";
// storing header part in Excel
for (int i = 1; i < mIS_FTTIMECARTSQL1DataGridView.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = mIS_FTTIMECARTSQL1DataGridView.Columns[i - 1].HeaderText;
}
// storing Each row and column value to excel sheet
for (int i = 0; i <= mIS_FTTIMECARTSQL1DataGridView.Rows.Count - 1; i++)
{
for (int j = 0; j < mIS_FTTIMECARTSQL1DataGridView.Columns.Count; j++)
{
string values = string.Empty;
values = mIS_FTTIMECARTSQL1DataGridView.Rows[i].Cells[j].Value.ToString();
worksheet.Cells[i + 2, j + 1] = values;
}
}
}
finally
{
//Release the resources
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
}
}
}
这里有另一种导出dataGrid视图的方法，效率稍高。完整代码如下。
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 Excel = Microsoft.Office.Interop.Excel;
using System.Data.SqlClient;
namespace Payroll
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void fillToolStripButton_Click(object sender, EventArgs e)
{
try
{
this.mIS_FTTIMECARTSQL1TableAdapter.Fill(this.fabTrolMRPDataSet.MIS_FTTIMECARTSQL1, new System.Nullable<System.DateTime>(((System.DateTime)(System.Convert.ChangeType(fromdateToolStripTextBox.Text, typeof(System.DateTime))))), new System.Nullable<System.DateTime>(((System.DateTime)(System.Convert.ChangeType(todateToolStripTextBox.Text, typeof(System.DateTime))))));
}
catch (System.Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message);
}
}
private void Submit_Click(object sender, EventArgs e)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
int i = 0;
int j = 0;
for (i = 0; i <= mIS_FTTIMECARTSQL1DataGridView.RowCount - 1; i++)
{
for (j = 0; j <= mIS_FTTIMECARTSQL1DataGridView.ColumnCount - 1; j++)
{
DataGridViewCell cell = mIS_FTTIMECARTSQL1DataGridView[j, i];
xlWorkSheet.Cells[i + 1, j + 1] = cell.Value;
}
}
xlWorkBook.SaveAs("C:''Time.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
MessageBox.Show("Excel file created , you can find the file c:''Time.xls");
}
private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
}
finally
{
GC.Collect();
}
}
}
}