从多个excel文档中读取数据，并将其写入另一个excel文档 [英] Reading data out of multiple excel docs and writing them in another excel doc

问题描述

目前，我只能读入一本excel文档，并且用相同的代码编写。现在我想读出多个excel文档并将数据写入一个。现在我有一个明确的代码，这样做到一个文档，但这不是我想要的。我理解现在的代码的结构，所以我宁愿留下来。如何使用 excel_init 函数和 excel_getValue 函数来执行此操作？

At the moment I can only read in one excel doc and write in the same with the code I got. Now I want to read out of multiple excel documents and write the data into one. Now I got a clear code of doing this into one document, but this is not what I want. I understand the tructure of the code I got at the moment, so I prefer staying with it. How am I going to do this with the excel_init function and with the excel_getValue function?

这是我到现在为止：

static void Main(string[] args)
        {
            excel_init("C:\\Users\\Admin\\Desktop\\excel1.xlsx");
            List<string> list = new List<string>();

            for (int i = 1; i <= 10; i++)
            {
                string firstColomExcelFile1 = "A" + i;
                string allExcelDataFile1 = excel_getValue(firstColomExcelFile1);

                excel_setValue("B" + i, allExcelDataFile1); //this has to happen in a different excel doc, on sheet 2

                list.Add(allExcelDataFile1);
                Console.WriteLine(allExcelDataFile1);
            }

            excel_close();
            excel_init("C:\\Users\\Admin\\Desktop\\excel1.xlsx");
            for (int j = 1; j < 5; j++) // loop for other excel document
            {
                string firstColomExcelFile2 = "A" + i;
                string allExcelDataFile2 = excel_getValue(firstColomExcelFile2);

                excel_setValue("C" + i, allExcelDataFile2);
                Console.WriteLine(allExcelDataFile2);
            } 
            excel_close();

           // here I want to paste my lists in another doc file. 

            Console.WriteLine("Press key to continue");
            Console.ReadKey();
        }

        private static Microsoft.Office.Interop.Excel.ApplicationClass appExcel;
        private static Workbook newWorkbook = null;
        private static _Worksheet objsheet = null;

        //Method to initialize opening Excel
        static void excel_init(String path)
        {
            appExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();

            if (System.IO.File.Exists(path))
            {
                // then go and load this into excel
                newWorkbook = appExcel.Workbooks.Open(path, true, true);
                objsheet = (_Worksheet)appExcel.ActiveWorkbook.ActiveSheet;
            }
            else
            {
                Console.WriteLine("Unable to open file!");
                System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel);
                appExcel = null;
            }

        }
        static void excel_setValue(string cellname, string value)
        {
            objsheet.get_Range(cellname).set_Value(Type.Missing, value);
        }

        //Method to get value; cellname is A1,A2, or B1,B2 etc...in excel.
        static string excel_getValue(string cellname)
        {
            string value = string.Empty;
            try
            {
                value = objsheet.get_Range(cellname).get_Value().ToString();
            }
            catch
            {
                value = "";
            }

            return value;
        }

        //Method to close excel connection
        static void excel_close()
        {
            if (appExcel != null)
            {
                try
                {
                    newWorkbook.Close();



         System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel.ActiveWorkbook.ActiveSheet);   
               System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel.ActiveWorkbook);

   System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel);
                    appExcel = null;
                    objsheet = null;
                }
                catch (Exception ex)
                {
                    appExcel = null;
                    Console.WriteLine("Unable to release the Object " + ex.ToString());
                }
                finally
                {
                    GC.Collect();
                }
            }
        }

提前感谢

编辑：我现在读取两个excel文件，并将信息放在列表中。现在我只想将最终的excel文件保存为另一个名为xxx.xlsx的文档

I now read both excel files apart and put the information in lists. Now I only want to save the final excel doc as another doc named xxx.xlsx

Edit2：将其放在我的excel_init函数中：

Fixed it with putting this in my excel_init function:

static void excel_init(String path)
        {
            appExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();

            if (System.IO.File.Exists(path))
            {
                // then go and load this into excel
                //newWorkbook_Second = appExcel.Workbooks.Open(path, true, true);
                newWorkbook_First = appExcel.Workbooks.Open(path, true, true);
                objsheet = (_Worksheet)appExcel.ActiveWorkbook.ActiveSheet;
            }
            else
            {
                try
                {
                    appExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();
                    appExcel.Visible = true;
                    newWorkbook_First = appExcel.Workbooks.Add(1);
                    objsheet = (Microsoft.Office.Interop.Excel.Worksheet)newWorkbook_First.Sheets[1];
                }
                catch (Exception e)
                {
                    Console.Write("Error");
                }
                finally
                {
                }
            }

        }

不是很好的编码...但它的工作原理...

Not so nice coding... But it works...

推荐答案

您将需要一个FileManager类来处理文件的读取和写入。然后使用文件管理器的一个实例来读取多个文件并写入一个文件。但是，读取路径和写入路径必须不同。

You would need a FileManager class that would take care of reading and writing to a file. Then use an instance of the file manager to read multiple files and write to a file. However, the read path and the write path must be different.

using System;
using System.Collections.Generic;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;

namespace MultipleExcelReadWriteExample
{
    public class Program
    {
        private static void Main(string[] args)
        {
            // create a instance of the file manager
            var fileManager = new FileManager();

            // add the list of file paths to collection
            fileManager.ListOfWorkbooksPath.Add("workBookToRead1", @"C:\ExcelFiles\WorkbookToRead1.xlsx");
            fileManager.ListOfWorkbooksPath.Add("workBookToRead2", @"C:\ExcelFiles\WorkbookToRead2.xlsx");
            fileManager.ListOfWorkbooksPath.Add("workBookToRead3", @"C:\ExcelFiles\WorkbookToRead3.xlsx");
            fileManager.ListOfWorkbooksPath.Add("workBookToWrite1", @"C:\ExcelFiles\WorkbookToWrite1.xlsx");

            // Open the excel app
            fileManager.OpenExcelApp();
            // open all the workbooks
            fileManager.OpenWorkbooks();

            // Do some data transfer here!
            int index = 1;
            foreach (var workbook in fileManager.ListOfWorkbooks)
            {
                if (workbook.Key.Contains("workBookToRead"))
                {
                    // get the worksheet to read
                    var readWorksheet = workbook.Value.Worksheets["Sheet1"] as Worksheet;

                    // get the writing workbook
                    Workbook workbookToWrite = fileManager.ListOfWorkbooks["workBookToWrite1"];
                    // get the worksheet to write
                    var writeWorksheet = workbookToWrite.Worksheets["Sheet" + index] as Worksheet;
                    //TODO: create a new sheet if doesn't exist

                    for (int column = 1; column <= 10; column++)
                    {
                        for (int row = 1; row <= 10; row++)
                        {
                            // read the data from the worksheet
                            Tuple<dynamic, dynamic> data = fileManager.ReadFromCell(readWorksheet, column, row);

                            // write the data to the worksheet
                            fileManager.WriteToCell(writeWorksheet, column, row, data);
                        }
                    }
                }

                index++;
            }

            // save all workbooks
            fileManager.SaveAllWorkbooks();
            // close all workbooks
            fileManager.CloseAllWorkbooks();
            // close the excel app
            fileManager.CloseExcelApp();

            Console.WriteLine("Press key to continue");
            Console.ReadKey();
        }
    }


    public class FileManager
    {
        private Application _excelApp;

        /// <summary>
        ///     Basic c'tor
        /// </summary>
        public FileManager()
        {
            ListOfWorkbooksPath = new Dictionary<string, string>();
            ListOfWorkbooks = new Dictionary<string, Workbook>();
        }

        /// <summary>
        ///     List of workbook to read, with their name and path
        /// </summary>
        public Dictionary<string, string> ListOfWorkbooksPath { get; set; }

        public Dictionary<string, Workbook> ListOfWorkbooks { get; set; }

        /// <summary>
        ///     Finalizer
        /// </summary>
        ~FileManager()
        {
            if (_excelApp != null)
            {
                _excelApp.Quit();
                Marshal.ReleaseComObject(_excelApp);
            }

            _excelApp = null;
        }

        /// <summary>
        ///     Open the Excel application
        /// </summary>
        public void OpenExcelApp()
        {
            _excelApp = new Application();
        }

        /// <summary>
        ///     Open list of workbooks for given path
        /// </summary>
        public void OpenWorkbooks()
        {
            foreach (var item in ListOfWorkbooksPath)
            {
                if (!ListOfWorkbooks.ContainsKey(item.Key))
                {
                    Workbook workbook = _excelApp.Workbooks.Open(item.Value);
                    ListOfWorkbooks.Add(item.Key, workbook);
                }
            }
        }

        /// <summary>
        ///     Read a cell and return the value and the cell format
        /// </summary>
        /// <param name="worksheet">The worksheet to read the value from.</param>
        /// <param name="column">The column number to read the value from.</param>
        /// <param name="row">The row number to read the value from.</param>
        /// <returns>The value and cell format.</returns>
        public Tuple<dynamic, dynamic> ReadFromCell(Worksheet worksheet, int column, int row)
        {
            var range = worksheet.Cells[row, column] as Range;

            if (range != null)
            {
                dynamic value = range.Value2; // get the value of the cell
                dynamic format = range.NumberFormat; // get the format of the cell
                return new Tuple<dynamic, dynamic>(value, format);
            }

            return null;
        }

        /// <summary>
        ///     Write the data to a cell in worksheet.
        /// </summary>
        /// <param name="worksheet">The worksheet to write the value.</param>
        /// <param name="column">The column number to write the value.</param>
        /// <param name="row">The row number to write the value.</param>
        /// <param name="data">The data to be written to a cell; this is a Tuple that contains the value and the cell format.</param>
        public void WriteToCell(Worksheet worksheet, int column, int row, Tuple<dynamic, dynamic> data)
        {
            var range = worksheet.Cells[row, column] as Range;

            if (range != null)
            {
                range.NumberFormat = data.Item2; // set the format of the cell
                range.Value2 = data.Item1; // set the value of the cell
            }
        }


        /// <summary>
        ///     Save all workbooks
        /// </summary>
        public void SaveAllWorkbooks()
        {
            foreach (var workbook in ListOfWorkbooks)
            {
                SaveWorkbook(workbook.Value);
            }
        }

        /// <summary>
        ///     Save single workbook
        /// </summary>
        /// <param name="workbook"></param>
        public void SaveWorkbook(Workbook workbook)
        {
            workbook.Save();
        }

        /// <summary>
        ///     Close all workbooks
        /// </summary>
        public void CloseAllWorkbooks()
        {
            foreach (var workbook in ListOfWorkbooks)
            {
                CloseWorkbook(workbook.Value);
            }

            ListOfWorkbooks.Clear();
        }

        /// <summary>
        ///     Close single workbook
        /// </summary>
        /// <param name="workbook"></param>
        public void CloseWorkbook(Workbook workbook)
        {
            workbook.Close();
        }

        /// <summary>
        ///     Close the Excel Application
        /// </summary>
        public void CloseExcelApp()
        {
            if (_excelApp != null)
            {
                _excelApp.Quit();
            }

            _excelApp = null;
            ListOfWorkbooksPath.Clear();
        }
    }
}

这篇关于从多个excel文档中读取数据，并将其写入另一个excel文档的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！