如何将Excel列从一个文件复制到另一个文件? [英] How to copy excel columns from one file to another?

问题描述

我使用excel插件应用程序来创建功能，该功能可以将列从一个excel文件复制到另一个.到目前为止，这里是代码，但是当我渲染应用程序时，该程序将输出一个空白的book.xls文件.

I using excel addin application, to create function which can copy columns from one excel file to another. Here is the code so far but when I render the application, however the program outputs a blank book.xls file.

private void button1_Click(object sender, EventArgs e)
    {
        Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
        Excel.Workbook xlWorkBook;
        Excel.Workbook xlWorkBook2;

        Excel.Worksheet xlWorkSheet;
        Excel.Worksheet xlWsheet2;

        Excel.Range xlSourceRange;
        Excel.Range xlSourceRange1;

        Excel.Range xlDestRange;
        Excel.Range xlDestRange1;

        xlWorkBook = xlApp.Workbooks.Open("C:/../../../../../../../Test.xls");


        xlWorkBook2 = xlApp.Workbooks.Open("C:/../../../../../../../Book1.xls");

        //~~> Display Excel
        xlApp.Visible = true;

        //~~> Set the source worksheet
        xlWorkSheet = xlWorkBook.Sheets["Sheet1"];
        //~~> Set the destination worksheet
        xlWsheet2 = xlWorkBook2.Sheets["Sheet1"];

        //~~> Set the source range
        xlSourceRange = xlWorkSheet.Range["E15"].EntireColumn;
        xlSourceRange1 = xlWorkSheet.Range["D15"].EntireColumn;

        //~~> Set the destination range
        xlDestRange = xlWsheet2.Range["A2"];
        xlDestRange1 = xlWsheet2.Range["B2"];


        xlSourceRange.Copy(Type.Missing);

        xlDestRange.PasteSpecial(Excel.XlPasteType.xlPasteAll,
Excel.XlPasteSpecialOperation.xlPasteSpecialOperationAdd, false, false);


        xlSourceRange1.Copy(Type.Missing);

        xlDestRange1.PasteSpecial(Excel.XlPasteType.xlPasteAll,
Excel.XlPasteSpecialOperation.xlPasteSpecialOperationAdd, false, false);

    }

由于我现在是新手，我不太确定如何去跟踪错误-正在使用excel库.我们将不胜感激.
谢谢

I am little unsure how I to go about tracing for errors, as I am currently an novice user - working with excel libraries. Any further assistance would be most appreciated.
Thanks

推荐答案

朋友请使用下面的代码，使用此查询的列名实例从sheetName中选择列名"

friend please use below code,use your column name instance of this query "select columnname from sheetName"

static void Main(string[] args)
    {
        string sourceFileName = @"C:\Test.xls";
        DataTable dataTable = loadSingleSheet(sourceFileName, "Employee");
        dataTable.Columns.Add("COLUMN_A");
        dataTable.Columns.Remove("COLUMN_B");
    }

    private static OleDbConnection ReturnConnection(string fileName)
    {
        return new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + "; Jet OLEDB:Engine Type=5;Extended Properties=\"Excel 8.0;\"");
    }

    private static DataTable LoadSingleSheet(string fileName, string sheetName)
    {
        DataTable sheetData = new DataTable();
        using (OleDbConnection conn = ReturnConnection(fileName))
        {
            conn.Open();

            OleDbDataAdapter sheetAdapter = new OleDbDataAdapter("select columnname from [" + sheetName + "]", conn);
            sheetAdapter.Fill(sheetData);
        }
        return sheetData;
    }




private static void UpdateSingleSheet(string fileName, string sheetName, DataTable dataTable)
    {
        using (OleDbConnection conn = ReturnConnection(fileName))
        {
            conn.Open();

            OleDbDataAdapter adapter = new OleDbDataAdapter();
            adapter.SelectCommand = new OleDbCommand("SELECT columnname FROM [" + sheetName + "]", conn);

            OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);

            adapter.UpdateCommand = builder.GetUpdateCommand();
            adapter.Update(dataTable);
        }
    }

我这样称呼:

string destinationFileName = @"C:\TestNew.xls";
UpdateSingleSheet(destinationFileName, "Employee", dataTable);

有关更多信息，请找到此站点.

for more information find this site. http://www.codedisqus.com/CyVjkWkUeg/copying-data-from-one-excel-file-to-another-using-c-adonet-ms-jet-returns-error-about-key-column.html

这篇关于如何将Excel列从一个文件复制到另一个文件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！