Reading data from multiple Excel documents and writing it in another excel document
At the moment I can only read one Excel document and write the same with the code I got. Now I want to read from multiple Excel documents and write data into one. Now I have clear code to do this in one document, but this is not what I want. I understand the structure of the code I have received at the moment, so I prefer to stay with it. How would I do this with a function excel_init
and with a function excel_getValue
?
Here's what I have so far:
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();
}
}
}
Thank you in advance
Edit: I am now reading both excel files and putting the information into lists. Now I want to save the final excel document as another document named xxx.xlsx
Edit2: Fixed this with putting 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 pretty coding ... But it works ...
+3
source to share
2 answers
You will need the FileManager class to take care of reading and writing to the 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();
}
}
}
0
source to share