Here's the problem, I have several excel files that are generated, each has 1 tab (but that's not to say some won't have more in the future).
What I need to write, is a process that can open each file, and copy all of it's worksheets (tabs) into a new file.
In the end, that new file should contain the worksheets of all the other files.
Currently, I have created the following to accomplish converting between excel formats.
I'm not too sure where to go from here, I don't have any of my sources I used to create this... and am not too sure on the object model (in order to copy the tabs to a new file) or the issues I'll face with com and making sure I keep everything cleaned up.
object excelApplication = null;
object workbook = null;
object workbooks = null;
try
{
// Get the Remote Type
var excelType = Type.GetTypeFromProgID("Excel.Application", Properties.Settings.Default.ExcelServer, true);
// Instantiate the type
excelApplication = Activator.CreateInstance(excelType);
// Turn off Prompts
excelApplication.GetType().InvokeMember(
"DisplayAlerts",
BindingFlags.SetProperty,
null,
excelApplication,
new Object[] { false });
// Get a reference to the workbooks object
workbooks = excelApplication.GetType().InvokeMember(
"Workbooks",
BindingFlags.GetProperty,
null,
excelApplication,
null);
// Open the input file
workbook = workbooks.GetType().InvokeMember(
"Open",
BindingFlags.InvokeMethod,
null,
workbooks,
new object[] { inputFilePath });
// If overwrite is turned off, and the file exist, the save as line will throw an error
if (File.Exists(outputFilePath) && overwriteIfExists)
{
File.Delete(outputFilePath);
}
// Save the workbook
workbook.GetType().InvokeMember(
"SaveAs",
BindingFlags.InvokeMethod,
null,
workbook,
new object[] { outputFilePath, saveAsFileFormat, null, null, null, null, 1, null, null, null, null, null });
}
finally
{
// Cleanup all created COM objects
if (workbook != null)
{
workbook.GetType().InvokeMember(
"Close",
BindingFlags.InvokeMethod,
null,
workbook,
null);
Marshal.ReleaseComObject(workbook);
workbook = null;
}
if (workbooks != null)
{
Marshal.ReleaseComObject(workbooks);
workbooks = null;
}
if (excelApplication != null)
{
excelApplication.GetType().InvokeMember(
"Quit",
BindingFlags.InvokeMethod,
null,
excelApplication,
null);
Marshal.ReleaseComObject(excelApplication);
excelApplication = null;
}
}
EDIT: This code is nearly working, the issue is on the line that should actually perform the copy...
public byte[] MergeFiles(FileFormat saveAsFileFormat, List<byte[]> inputFileBytesList)
{
var outputFilePath = Path.Combine(Properties.Settings.Default.WorkingFolder, Guid.NewGuid() + ".target.xls");
Impersonate(
Properties.Settings.Default.ImpersonationUser.Decrypt(),
Properties.Settings.Default.ImpersonationDomain,
Properties.Settings.Default.ImpersonationPassword.Decrypt()
);
var convertedFileList = new List<string>();
foreach (var inputFileBytes in inputFileBytesList)
{
var inputFileExtension = GetExtension(inputFileBytes);
var inputFilePath = Path.Combine(Properties.Settings.Default.WorkingFolder, Guid.NewGuid() + inputFileExtension);
var convertedFileBytes = SaveAs(saveAsFileFormat, inputFileBytes);
File.WriteAllBytes(inputFilePath, convertedFileBytes);
convertedFileList.Add(inputFilePath);
}
// Target Excel File
object targetExcelApplication = null;
object targetWorkbook = null;
object targetWorkbooks = null;
object targetWorksheets = null;
object targetWorksheet = null;
try
{
// Get the Remote Type
var excelType = Type.GetTypeFromProgID("Excel.Application", Properties.Settings.Default.ExcelServer, true);
// Instantiate the type
targetExcelApplication = Activator.CreateInstance(excelType);
// Turn off Prompts
targetExcelApplication.GetType().InvokeMember(
"DisplayAlerts",
BindingFlags.SetProperty,
null,
targetExcelApplication,
new Object[] { false });
// Get a reference to the workbooks object
targetWorkbooks = targetExcelApplication.GetType().InvokeMember(
"Workbooks",
BindingFlags.GetProperty,
null,
targetExcelApplication,
null);
// Create a workbook to add the sheets to
targetWorkbook = targetWorkbooks.GetType().InvokeMember(
"Add",
BindingFlags.InvokeMethod,
null,
targetWorkbooks,
new object[] { 1 });
// Get a reference to the worksheets object
targetWorksheets = targetWorkbook.GetType().InvokeMember(
"Sheets",
BindingFlags.GetProperty,
null,
targetExcelApplication,
null
);
foreach (var inputFilePath in convertedFileList)
{
// Open each File, grabbing all tabs
object sourceExcelApplication = null;
object sourceWorkbooks = null;
object sourceWorkbook = null;
object sourceWorksheets = null;
try
{
// Instantiate the type
sourceExcelApplication = Activator.CreateInstance(excelType);
// Turn off Prompts
sourceExcelApplication.GetType().InvokeMember(
"DisplayAlerts",
BindingFlags.SetProperty,
null,
sourceExcelApplication,
new Object[] {false});
// Get a reference to the workbooks object
sourceWorkbooks = sourceExcelApplication.GetType().InvokeMember(
"Workbooks",
BindingFlags.GetProperty,
null,
sourceExcelApplication,
null);
// Open the input file
sourceWorkbook = sourceWorkbooks.GetType().InvokeMember(
"Open",
BindingFlags.InvokeMethod,
null,
sourceWorkbooks,
new object[] {inputFilePath});
// Get a reference to the worksheets object
sourceWorksheets = sourceWorkbook.GetType().InvokeMember(
"Sheets",
BindingFlags.GetProperty,
null,
sourceExcelApplication,
null);
var sourceSheetCount = (int)(sourceWorksheets.GetType().InvokeMember(
"Count",
BindingFlags.GetProperty,
null,
sourceWorksheets,
null));
for (var i = 1; i <= sourceSheetCount; i++)
{
var targetSheetCount = (int)(targetWorksheets.GetType().InvokeMember(
"Count",
BindingFlags.GetProperty,
null,
targetWorksheets,
null));
var sourceWorksheet = sourceWorksheets.GetType().InvokeMember(
"Item",
BindingFlags.GetProperty,
null,
sourceWorksheets,
new Object[] { i });
targetWorksheet = targetWorksheets.GetType().InvokeMember(
"Item",
BindingFlags.GetProperty,
null,
targetWorksheets,
new Object[] { targetSheetCount });
// TODO: Copy into target file
sourceWorksheet.GetType().InvokeMember(
"Copy",
BindingFlags.InvokeMethod,
null,
sourceWorksheet,
new[] { Type.Missing, targetWorksheet }
);
if (sourceWorksheet != null)
{
Marshal.ReleaseComObject(sourceWorksheet);
sourceWorksheet = null;
}
}
}
finally
{
// Cleanup all created COM objects
if (sourceWorksheets != null)
{
Marshal.ReleaseComObject(sourceWorksheets);
sourceWorksheets = null;
}
if (sourceWorkbook != null)
{
sourceWorkbook.GetType().InvokeMember(
"Close",
BindingFlags.InvokeMethod,
null,
sourceWorkbook,
null);
Marshal.ReleaseComObject(sourceWorkbook);
sourceWorkbook = null;
}
if (sourceWorkbooks != null)
{
Marshal.ReleaseComObject(sourceWorkbooks);
sourceWorkbooks = null;
}
if (sourceExcelApplication != null)
{
sourceExcelApplication.GetType().InvokeMember(
"Quit",
BindingFlags.InvokeMethod,
null,
sourceExcelApplication,
null);
Marshal.ReleaseComObject(sourceExcelApplication);
sourceExcelApplication = null;
}
}
}
// If overwrite is turned off, and the file exist, the save as line will throw an error
if (File.Exists(outputFilePath))
{
File.Delete(outputFilePath);
}
// Save the workbook
targetWorkbook.GetType().InvokeMember(
"SaveAs",
BindingFlags.InvokeMethod,
null,
targetWorkbook,
new object[] { outputFilePath, saveAsFileFormat, null, null, null, null, 1, null, null, null, null, null });
}
finally
{
// Cleanup all created COM objects
if (targetWorksheets != null)
{
Marshal.ReleaseComObject(targetWorksheets);
targetWorksheets = null;
}
if (targetWorkbook != null)
{
targetWorkbook.GetType().InvokeMember(
"Close",
BindingFlags.InvokeMethod,
null,
targetWorkbook,
null);
Marshal.ReleaseComObject(targetWorkbook);
targetWorkbook = null;
}
if (targetWorkbooks != null)
{
Marshal.ReleaseComObject(targetWorkbooks);
targetWorkbooks = null;
}
if (targetExcelApplication != null)
{
targetExcelApplication.GetType().InvokeMember(
"Quit",
BindingFlags.InvokeMethod,
null,
targetExcelApplication,
null);
Marshal.ReleaseComObject(targetExcelApplication);
targetExcelApplication = null;
}
}
// Read target file bytes
var resultBytes = (File.Exists(outputFilePath))
? File.ReadAllBytes(outputFilePath)
: new byte[] { };
// Delete working files
if (File.Exists(outputFilePath))
File.Delete(outputFilePath);
foreach (var inputFilePath in convertedFileList.Where(File.Exists))
{
File.Delete(inputFilePath);
}
Repersonate();
// Return result
return resultBytes;
}
I get the error System.Runtime.InteropServices.COMException: Copy method of Worksheet class failed, which doesn't help much...I don't know why it failed...
This seems to work, just need to add a little cleanup to remove the blank worksheets that are initially created, then to active the first sheet in the file before saving
[WebMethod]
public byte[] MergeFiles(FileFormat saveAsFileFormat, List<byte[]> inputFileBytesList)
{
//var outputFilePath = Path.Combine(Properties.Settings.Default.WorkingFolder, Guid.NewGuid() + ".xls");
var outputFilePath = Path.Combine(Properties.Settings.Default.WorkingFolder, "target.xls");
Impersonate(
Properties.Settings.Default.ImpersonationUser.Decrypt(),
Properties.Settings.Default.ImpersonationDomain,
Properties.Settings.Default.ImpersonationPassword.Decrypt()
);
var convertedFileList = new List<string>();
foreach (var inputFileBytes in inputFileBytesList)
{
var inputFileExtension = GetExtension(inputFileBytes);
var inputFilePath = Path.Combine(Properties.Settings.Default.WorkingFolder, Guid.NewGuid() + inputFileExtension);
File.WriteAllBytes(inputFilePath, inputFileBytes);
var convertedFilePath = Path.Combine(Properties.Settings.Default.WorkingFolder, Guid.NewGuid() + inputFileExtension);
SaveAsInternal(saveAsFileFormat, inputFilePath, convertedFilePath, true);
convertedFileList.Add(convertedFilePath);
}
// Target Excel File
object excelApplication = null;
object excelWorkbooks = null;
object targetWorkbook = null;
object targetWorksheets = null;
object targetWorksheet = null;
try
{
// Get the Remote Type
var excelType = Type.GetTypeFromProgID("Excel.Application", Properties.Settings.Default.ExcelServer, true);
// Instantiate the type
excelApplication = Activator.CreateInstance(excelType);
// Turn off Prompts
excelApplication.GetType().InvokeMember(
"DisplayAlerts",
BindingFlags.SetProperty,
null,
excelApplication,
new Object[] { false });
// Get a reference to the workbooks object
excelWorkbooks = excelApplication.GetType().InvokeMember(
"Workbooks",
BindingFlags.GetProperty,
null,
excelApplication,
null);
// Create a workbook to add the sheets to
targetWorkbook = excelWorkbooks.GetType().InvokeMember(
"Add",
BindingFlags.InvokeMethod,
null,
excelWorkbooks,
new object[] { 1 });
// Get a reference to the worksheets object
targetWorksheets = targetWorkbook.GetType().InvokeMember(
"Sheets",
BindingFlags.GetProperty,
null,
excelApplication,
null
);
// Open each File, grabbing all tabs
foreach (var inputFilePath in convertedFileList)
{
object sourceWorkbook = null;
object sourceWorksheets = null;
try
{
// Open the input file
sourceWorkbook = excelWorkbooks.GetType().InvokeMember(
"Open",
BindingFlags.InvokeMethod,
null,
excelWorkbooks,
new object[] {inputFilePath});
// Get a reference to the worksheets object
sourceWorksheets = sourceWorkbook.GetType().InvokeMember(
"Sheets",
BindingFlags.GetProperty,
null,
excelApplication,
null);
var sourceSheetCount = (int)(sourceWorksheets.GetType().InvokeMember(
"Count",
BindingFlags.GetProperty,
null,
sourceWorksheets,
null));
for (var i = 1; i <= sourceSheetCount; i++)
{
var targetSheetCount = (int)(targetWorksheets.GetType().InvokeMember(
"Count",
BindingFlags.GetProperty,
null,
targetWorksheets,
null));
var sourceWorksheet = sourceWorksheets.GetType().InvokeMember(
"Item",
BindingFlags.GetProperty,
null,
sourceWorksheets,
new Object[] { i });
targetWorksheet = targetWorksheets.GetType().InvokeMember(
"Item",
BindingFlags.GetProperty,
null,
targetWorksheets,
new Object[] { targetSheetCount });
// TODO: Copy into target file
sourceWorksheet.GetType().InvokeMember(
"Copy",
BindingFlags.InvokeMethod,
null,
sourceWorksheet,
new[] { Type.Missing, targetWorksheet }
);
if (sourceWorksheet != null)
{
Marshal.ReleaseComObject(sourceWorksheet);
sourceWorksheet = null;
}
}
}
finally
{
// Cleanup all created COM objects
if (sourceWorksheets != null)
{
Marshal.ReleaseComObject(sourceWorksheets);
sourceWorksheets = null;
}
if (sourceWorkbook != null)
{
sourceWorkbook.GetType().InvokeMember(
"Close",
BindingFlags.InvokeMethod,
null,
sourceWorkbook,
null);
Marshal.ReleaseComObject(sourceWorkbook);
sourceWorkbook = null;
}
}
}
// If overwrite is turned off, and the file exist, the save as line will throw an error
if (File.Exists(outputFilePath))
{
File.Delete(outputFilePath);
}
// Save the workbook
targetWorkbook.GetType().InvokeMember(
"SaveAs",
BindingFlags.InvokeMethod,
null,
targetWorkbook,
new object[] { outputFilePath, saveAsFileFormat, null, null, null, null, 1, null, null, null, null, null });
}
finally
{
// Cleanup all created COM objects
if (targetWorksheets != null)
{
Marshal.ReleaseComObject(targetWorksheets);
targetWorksheets = null;
}
if (targetWorkbook != null)
{
targetWorkbook.GetType().InvokeMember(
"Close",
BindingFlags.InvokeMethod,
null,
targetWorkbook,
null);
Marshal.ReleaseComObject(excelWorkbooks);
excelWorkbooks = null;
}
if (excelWorkbooks != null)
{
Marshal.ReleaseComObject(excelWorkbooks);
excelWorkbooks = null;
}
if (excelApplication != null)
{
excelApplication.GetType().InvokeMember(
"Quit",
BindingFlags.InvokeMethod,
null,
excelApplication,
null);
Marshal.ReleaseComObject(excelApplication);
excelApplication = null;
}
}
// Read target file bytes
var resultBytes = (File.Exists(outputFilePath))
? File.ReadAllBytes(outputFilePath)
: new byte[] { };
// Delete working files
if (File.Exists(outputFilePath))
File.Delete(outputFilePath);
foreach (var inputFilePath in convertedFileList.Where(File.Exists))
{
File.Delete(inputFilePath);
}
Repersonate();
// Return result
return resultBytes;
}