将数据输出到现有Excel工作簿的特定工作表

Question

我有一个带有两张纸的Excel工作簿。第二张表包含两个列，这些列为Sheet1中的下拉列表提供了数据。现在，我想从数据库生成第二纸的数据。因此，基本上，我想在数据库/GridView的特定表中插入Excel文件中的数据。是否有更轻松的方法可以使用 itextSharp 在ASP.NET中。

Answer 1

您需要为Office安装了Microsoft Visual Studio工具。

之后，创建了Common .NET项目，然后将参考添加到COM OBOCT MICROSOFT.OFFICE.INTEROP.EXCEL.DLL通过'添加引用..'对话框。

免责声明：我不检查代码。

//open the workbook and set the worksheet 
Application excel = new Application();
Workbook wb = excel.Workbooks.Open(filePath);
Worksheeet ws = wb.sheets("WorkSheetName");
int i = 1; //used to track the column sin the excel sheet
//loop through datagrid
foreach (DataGridViewRow row in yourDataGrid)
{
    //Cell[row, col]
    ws.Cell[i, 1] = row.Cells[1]  //assuming the location of item1
    ws.Cell[i, 2] = row.Cells[2]  //assuming the location of item2
    i++;
}
//save and close 
wb.Close(true);

Answer 2

这将把gridview的内容转移到您的Excel文件中。

using System;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
using Excel = Microsoft.Office.Interop.Excel; 
namespace WindowsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection cnn ;
            string connectionString = null;
            string sql = null;
            connectionString = "data source=servername;initial catalog=databasename;user id=username;password=password;";
            cnn = new SqlConnection(connectionString);
            cnn.Open();
            sql = "SELECT * FROM Product";
            SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
            DataSet ds = new DataSet();
            dscmd.Fill(ds);
            dataGridView1.DataSource = ds.Tables[0];
        }
        private void button2_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 <= dataGridView1.RowCount  - 1; i++)
            {
                for (j = 0; j <= dataGridView1.ColumnCount  - 1; j++)
                {
                    DataGridViewCell cell = dataGridView1[j, i];
                    xlWorkSheet.Cells[i + 1, j + 1] = cell.Value;
                }
            }
            xlWorkBook.SaveAs("csharp.net-informations.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:\csharp.net-informations.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();
            }
        }
    }
}

您可以以以下方式从SQL Server加载GridView。

using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
namespace WindowsApplication1
{
    public partial class Form1 : Form
    {
        string connetionString;
        SqlConnection connection;
        SqlDataAdapter adapter;
        SqlCommandBuilder cmdBuilder;
        DataSet ds = new DataSet();
        DataSet changes;
        string Sql;
        Int32 i; 
        public Form1()
        {
            InitializeComponent();
        }
        private void button1_Click(object sender, EventArgs e)
        {
            connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password";
            connection = new SqlConnection(connetionString);
            Sql = "select * from Product";
            try
            {
                connection.Open();
                adapter = new SqlDataAdapter(Sql, connection);
                adapter.Fill(ds);
                connection.Close();
                dataGridView1.DataSource = ds.Tables[0];
            }
            catch (Exception ex)
            {
                MessageBox.Show (ex.ToString());
            }
        }
        private void button2_Click(object sender, EventArgs e)
        {
            try
            {
                cmdBuilder = new SqlCommandBuilder(adapter);
                changes = ds.GetChanges();
                if (changes != null)
                {
                    adapter.Update(changes);
                }
                MessageBox.Show("Changes Done");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
    }
}

当然，您可以将数据从SQL Server直接推入Excel(或将其从SQL Server中拉入Excel(。

Function ImportSQLtoRange
The function inserts SQL Server data to the target Excel range using ADO.
Function ImportSQLtoRange(ByVal conString As String, ByVal query As String, _
    ByVal target As Range) As Integer
    On Error Resume Next
    ' Object type and CreateObject function are used instead of ADODB.Connection,
    ' ADODB.Command for late binding without reference to
    ' Microsoft ActiveX Data Objects 2.x Library
    ' ADO API Reference
    ' http://msdn.microsoft.com/en-us/library/ms678086(v=VS.85).aspx
    ' Dim con As ADODB.Connection
    Dim con As Object
    Set con = CreateObject("ADODB.Connection")
    con.ConnectionString = conString
    ' Dim cmd As ADODB.Command
    Dim cmd As Object
    Set cmd = CreateObject("ADODB.Command")
    cmd.CommandText = query
    cmd.CommandType = 1         ' adCmdText
    ' The Open method doesn't actually establish a connection to the server
    ' until a Recordset is opened on the Connection object
    con.Open
    cmd.ActiveConnection = con
    ' Dim rst As ADODB.Recordset
    Dim rst As Object
    Set rst = cmd.Execute
    If rst Is Nothing Then
        con.Close
        Set con = Nothing
        ImportSQLtoRange = 1
        Exit Function
    End If
    Dim ws As Worksheet
    Dim col As Integer
    Set ws = target.Worksheet
    ' Column Names
    For col = 0 To rst.Fields.Count - 1
        ws.Cells(target.row, target.Column + col).Value = rst.Fields(col).Name
    Next
    ws.Range(ws.Cells(target.row, target.Column), _
        ws.Cells(target.row, target.Column + rst.Fields.Count)).Font.Bold = True
    ' Data from Recordset
    ws.Cells(target.row + 1, target.Column).CopyFromRecordset rst
    rst.Close
    con.Close
    Set rst = Nothing
    Set cmd = Nothing
    Set con = Nothing
    ImportSQLtoRange = 0
End Function

代码注释：

The query parameter can contain a SELECT or EXECUTE query.
The resulting data will be inserted starting from the top left cell of the target range.
Using Object types and the CreateObject function instead of direct use of ADO types
lets to avoid setting ActiveX Data Objects 2.x Library references on user computers.
This code works in Microsoft Excel 2003-2016.
Always use Set Nothing statements for ADODB.Connection and ADODB.Recordset objects to free resources.

有关如何做类似类型的事情的更多想法，请参见下面的链接。

https://www.excel-sql-server.com/excel-sql-server-import-export-usish-using-using-vba.htm#sqlserverdataimporttoexcelusedado