我曾尝试使用COM互操作的各种方法来合并工作表。
以下是我最终得出的代码（为了简洁起见省略了不必要的部分）：
public bool CombineWorkBooks(string exportFilePath, IEnumerable<string> filesToMergeFrom, bool deleteRawFiles)
{
var success = false;
Excel.Application xlApp = null;
Excel.Workbook mergeToWorkbook = null;
Excel.Workbooks mergeToWorkbooks = null;
Excel.Sheets mergeToWorksheets = null;
Excel.Worksheet defaultWorksheet = null;
try
{
if (filesToMergeFrom == null)
{
return false;
}
xlApp = new Excel.Application
{
DisplayAlerts = false,
Visible = false,
CutCopyMode = Excel.XlCutCopyMode.xlCopy
};
mergeToWorkbooks = xlApp.Workbooks;
mergeToWorkbook = mergeToWorkbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
mergeToWorksheets = mergeToWorkbook.Worksheets;
// Get the reference for the [first] empty default worksheet
if (mergeToWorksheets.Count > 0)
{
defaultWorksheet = mergeToWorksheets[1] as Excel.Worksheet;
}
if (defaultWorksheet == null)
{
return false;
}
var reportSheetIndex = 1;
var fileMergeCount = 0;
foreach (var mergeFromFilename in filesToMergeFrom)
{
fileMergeCount++;
Excel.Workbook sourceWorkbook = null;
Excel.Sheets childSheets = null;
// Make sure file is still there...
if (!File.Exists(mergeFromFilename))
{
continue;
}
try
{
// Open source file
sourceWorkbook = mergeToWorkbooks.Open(mergeFromFilename,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing);
childSheets = sourceWorkbook.Worksheets;
if (childSheets != null)
{
var sheetsCopiedFromSource = 0;
// childSheets.Copy(defaultWorksheet, Type.Missing);
for (int iChildSheet = 1; iChildSheet <= childSheets.Count; iChildSheet++)
{
Excel.Worksheet sourceWorksheet = null;
try
{
sourceWorksheet = childSheets[iChildSheet] as Excel.Worksheet;
if (sourceWorksheet != null)
{
string name = string.Format(baseSheetName, reportSheetIndex.ToString("D4", new CultureInfo(CultureInfo.CurrentCulture.Name)));
// Assigning the worksheet name
sourceWorksheet.Name = Truncate(name, 31); // only 31 char max
// Copy the worksheet before the default sheet
sourceWorksheet.Copy(defaultWorksheet, Type.Missing);
reportSheetIndex++;
sheetsCopiedFromSource++;
}
}
finally
{
disposeCOMObject(sourceWorksheet);
}
}
// Close the childbook - for some reason, calling close below may cause an
// exception -> System.Runtime.InteropServices.COMException (0x80010108): The object invoked has disconnected from its clients.
try
{
sourceWorkbook.Close(false, Type.Missing, Type.Missing);
}
catch (COMException comException)
{
if (comException.ErrorCode != 0x80010108)
{
throw;
}
_messages.Add("Caught COMException, discarding it.");
}
_messages.Add(string.Format("Successfully copied {0} worksheets from report file: '{1}'", sheetsCopiedFromSource, mergeFromFilename));
}
}
catch (Exception ex)
{
_messages.Add(string.Format("An error occurred processing file '{0}'.", mergeFromFilename));
}
finally
{
disposeCOMObject(childSheets);
disposeCOMObject(sourceWorkbook);
}
}
// Delete the empty default worksheet
defaultWorksheet.Delete();
// Select the first sheet.
if (mergeToWorksheets.Count > 0)
{
Excel.Worksheet firstSheet = null;
try
{
firstSheet = mergeToWorksheets[1] as Excel.Worksheet;
if (firstSheet != null)
{
firstSheet.Select(Type.Missing);
firstSheet.Range["A1"].Select();
}
}
finally
{
disposeCOMObject(firstSheet);
}
}
// Determine the file extension
var fileExt = GetExcelExtension();
if (string.IsNullOrEmpty(Path.GetExtension(exportFilePath)))
{
exportFilePath = string.Format("{0}.{1}", exportFilePath, fileExt);
}
else if (Path.GetExtension(exportFilePath) != string.Format(".{0}", fileExt))
{
exportFilePath = Path.ChangeExtension(exportFilePath, fileExt);
}
_mergeOutputFile = exportFilePath;
// Save the merged output
mergeToWorkbook.SaveAs(exportFilePath,
Excel.XlFileFormat.xlWorkbookDefault,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Excel.XlSaveAsAccessMode.xlExclusive,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing);
// Close file
mergeToWorkbooks.Close();
xlApp.DisplayAlerts = true;
success = true;
return true;
}
catch (Exception ex)
{
return false;
}
finally
{
disposeCOMObject(defaultWorksheet);
disposeCOMObject(mergeToWorksheets);
disposeCOMObject(mergeToWorkbook);
disposeCOMObject(mergeToWorkbooks);
// Quit Excel.
if (xlApp != null)
{
xlApp.Quit();
disposeCOMObject(xlApp);
}
// Only delete source files upon success...
if (success &&
deleteRawFiles)
{
deleteTemporaryFiles(filesToMergeFrom);
}
// Clean up remaining resources
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
现在 - 这一切都运行得很好。但是......合并文件中的单元格/行内颜色都混乱了（通常是黑色和紫色 - 一个3岁的孩子可能会喜欢）。源工作表中有一些带有突出显示颜色或背景（内部）颜色不是白色的行。
如果我添加以下代码，我可以将合并文件中所有单元格的颜色重置为白色，但我尚未成功保留原始源表的颜色。
// Reset the background colour in the cells to white. For some reason the worksheet copy
// operation screws up the cell backgrounds - pinks, purples and other business-like
// colours.
foreach (Excel.Worksheet targetWorksheet in mergeToWorkbook.Worksheets)
{
var usedRange = targetWorksheet.UsedRange;
usedRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
}