Reputation: 958
I'm trying to build an installer project (windows forms) using which I can select multiple excel files from a directory, copy contents from each file and append it in a new single output file. I am using Microsoft.Office.Interop.Excel
package in VS 2017 Community edition. I'm still working on it but a bit push to the right direction will help a lot.
Say, I have 100's of excel files in a directory, each excel workbook contains at least 1 sheet. The structure of each sheet are identical. Using OpenFileDialog
:
listbox
. I don't know where exactly I'm going wrong, I'm not even able to create the output excel file. Here is my work till now:
Excel.Application excel;
Stream myStrm;
OpenFileDialog ofd = new OpenFileDialog();
private void button1_Click(object sender, EventArgs e)
{
ofd.InitialDirectory = "c:\\";
ofd.Title = "Please select the files to merge";
ofd.Filter = "Excel Files|*.xls;*.xlsx;*.xlsm";
//ofd.FileName = "ExportedData";
//ofd.DefaultExt = ".xlsx";
ofd.RestoreDirectory = true;
ofd.Multiselect = true;
//To store file path info
List<System.IO.FileInfo> fList = new List<System.IO.FileInfo>();
if (ofd.ShowDialog() == DialogResult.OK)
{
var fileName = ofd.FileName;
var tempFolderPath = Path.GetTempPath();
var templateFileLocation = Path.Combine(Directory.GetCurrentDirectory(), "Model", "ExcelTemplate.xlsx");
templateFileLocation = templateFileLocation.Replace("\\bin\\Debug\\", "\\");
foreach (String file in ofd.FileNames)
{
try
{
if((myStrm = ofd.OpenFile()) != null)
{
using (myStrm)
{
//to display file name in the 1st listbox
fileListBox.Items.Add(file.Substring(file.LastIndexOf('\\') + 1));
//to display the path of the file
pathListBox.Items.Add(file);
}
}
//to display the selected file count
fileCntLbl.Text = "You have selected " + ofd.FileNames.Length + " files";
ApplicationClass app = new ApplicationClass();
Workbook curWorkBook = null;
Workbook destWorkbook = null;
Worksheet workSheet = null;
Worksheet newWorksheet = null;
Object defaultArg = Type.Missing;
//FileInfo fi = null;
Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
try
{
// Copy the source sheet
curWorkBook = app.Workbooks.Open(file, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg);
workSheet = (Worksheet)curWorkBook.Sheets[1];
workSheet.UsedRange.Copy(defaultArg);
// Paste on destination sheet
destWorkbook = app.Workbooks.Open(@"c:\\Results.xlxs", defaultArg, false, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg);
newWorksheet = (Worksheet)destWorkbook.Worksheets.Add(defaultArg, defaultArg, defaultArg, defaultArg);
newWorksheet.UsedRange._PasteSpecial(XlPasteType.xlPasteValues, XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
}
}
catch (Exception exc)
{
System.Windows.Forms.MessageBox.Show(exc.Message);
}
finally
{
if (curWorkBook != null)
{
curWorkBook.Save();
curWorkBook.Close(defaultArg, defaultArg, defaultArg);
}
if (destWorkbook != null)
{
destWorkbook.Save();
destWorkbook.Close(defaultArg, defaultArg, defaultArg);
}
}
app.Quit();
}
catch (Exception ex)
{
MessageBox.Show("Error: Could not read file from disk. Original error: " + ex.Message);
}
}
}
private void button2_Click(object sender, EventArgs e)
{
System.Windows.Forms.Application.Exit();
}
This solution works but doesn't copy the contents and produces error. The error is like this:
Any help/suggestions appreciated. Thanks for help.
I have already tried all the solutions on stackexchange, codeproject and many other sites. So please do not mark it as duplicate or a homework stuff.
Upvotes: 1
Views: 7405
Reputation: 197
I know I'm late to answer this but still this could help someone.
You can have two functions here, one to open the files and the other to copy the contents.
In the first function, you can open as many files as you want through a for loop, something like this:
void OpenFiles()
{
foreach (string strFile in sourceFiles) //sourceFiles is a list containing the file paths
{
bool b = false;
Excel.Workbook bookSource = app.Workbooks._Open(strFile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
Excel.Worksheet sheetSource = bookSource.Worksheets[1] as Excel.Worksheet;
CopyData();
}
//finally save the file as your requirements and close all the open workbooks
}
void CopyData()
{
Excel.Worksheet lastsheet = null; //last sheet in the workbook
int limit = 1000000; //variable to check if your sheet has exceeded
try
{
var sheets = bookDest.Sheets;
lastsheet = (Excel.Worksheet)bookDest.Sheets[sheets.Count];
hc.ReleaseObject(sheets);
drc = lastsheet.UsedRange.Rows.Count; //no of rows used in result workbook
src = sheetSource.UsedRange.Rows.Count; //no of rows used in source workbook
//if else loop to check if you have exceeded 1st sheet limit before start copying
if ((drc + src) <= limit)
{
int sheetRowCount = sheetSource.UsedRange.Rows.Count;
Excel.Range range = sheetSource.get_Range(string.Format("A{0}", _headerRowCount), _columnEnd + sheetRowCount.ToString());
range.Copy(lastsheet.get_Range(string.Format("A{0}", _currentRowCount), Missing.Value));
_currentRowCount += range.Rows.Count;
}
else if ((drc >= limit && src >= limit) || drc >= limit || src >= limit || (drc + src) >= limit)
{
Excel.Worksheet newSheet = (Excel.Worksheet)bookDest.Worksheets.Add(After: lastsheet);
newSheet.Name = "Result " + (cnt++);
hc.ReleaseObject(lastsheet);
lastsheet = newSheet;
lastsheet.Activate();
CopyHeader(lastsheet);
//((Excel.Worksheet) this.app.ActiveWorkbook.Sheets[lastsheet]).Select();
int sheetRowCount = sheetSource.UsedRange.Rows.Count;
Excel.Range range = sheetSource.get_Range(string.Format("A{0}", _headerRowCount), _columnEnd + sheetRowCount.ToString());
range.Copy(lastsheet.get_Range(string.Format("A{0}", _currentRowCount), Missing.Value));
_currentRowCount += range.Rows.Count;
}
}
else
{
int sheetRowCount = sheetSource.UsedRange.Rows.Count;
Excel.Range range = sheetSource.get_Range(string.Format("A{0}", _headerRowCount), _columnEnd + sheetRowCount.ToString());
range.Copy(lastsheet.get_Range(string.Format("A{0}", _currentRowCount), Missing.Value));
_currentRowCount += range.Rows.Count;
}
}
catch (IndexOutOfRangeException)
{
MessageBox.Show("Some problem with the source file", "Copy error");
}
finally
{
ReleaseObject(lastsheet);
}
}
I have provided the limit to exactly a million. If you feel this limit might hang your excel than you can reduce it.
You're invited for any code changes.
Thank you.
Upvotes: 1
Reputation: 77
Alternatively, using System.IO you can use the following to copy an excel file into a new file:
File.WriteAllBytes(newFilePath, File.ReadAllBytes(initialFilePath));
Upvotes: 0
Reputation: 1126
Instead of copy/pasting UsedRange, try using the Sheet.Copy function. It will simplify your code substantially and reduce the potential error points. C# - How to copy a single Excel worksheet from one workbook to another?
Upvotes: 1