保存重新计算的Excel文件 [英] Saving recalculated Excel file

问题描述

我需要从工作表中删除所有Excel公式(将公式替换为其结果)，然后保存文件.例如-代替B3 + 13，我想在使用OpenXML打开文件时在单元格中放入2013-04-11

I need to remove all Excel formulas from my worksheet (replace formulas with its results) then save file. For example - instead B3+13 I want to have 2013-04-11 in cell when I open file using OpenXML

我找到了这篇文章 http://office .microsoft.com/en-us/excel-help/replace-a-formula-with-its-result-HP010066258.aspx

有效-保存后没有公式.我需要在C#中具有相同的效果.我在Internet上尝试了许多解决方案，但仍然没有希望.

It works - after saving there is no formula. I need the same effect in C#. I've tried many solutions in Internet but still no hope.

                string source = @"C:\Source\doc.xlsx";
            object Unknown = Type.Missing;

            Workbook doc = newApp.Workbooks.Open(
                source,
                 Unknown, 
                 Unknown, 
                 Unknown, 
                 Unknown, 
                 Unknown, 
                 Unknown, 
                 Unknown, 
                 Unknown, 
                 Unknown, 
                 Unknown, 
                 Unknown, 
                 Unknown, 
                 Unknown, 
                 Unknown);

            newApp.Calculation = Microsoft.Office.Interop.Excel.XlCalculation.xlCalculationManual;

            doc.ForceFullCalculation = true;
            newApp.CalculateFull();
            newApp.CalculateBeforeSave = true;

            doc.SaveAs("c:\\temp\\recalc.xlsx", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
            false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
            Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            doc.Close();

那是什么问题?为什么我可以看到"= DY3 + 1"而不是"2013-04-11"?

So what is the problem? Why I can see "=DY3+1" instead "2013-04-11"?

推荐答案

选择包含数据的范围，然后将值复制并粘贴到位:

Select the range that contains your data, then copy and paste the values in place:

using Excel = Microsoft.Office.Interop.Excel;
foreach (Excel.Worksheet ws in doc.Worksheets)
{
    Excel.Range targetRange = (Excel.Range)ws.UsedRange;
    targetRange.Copy(Type.Missing);
    targetRange.PasteSpecial(Excel.XlPasteType.xlPasteValues, 
        Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
}

这与您发布的文档的用公式的计算值替换公式"部分重复.可以选择所有工作表并在Excel本身中进行特殊的复制/粘贴以获得结果而不是公式，但是我在通过代码处理分组工作表时遇到了一些问题.

This duplicates the "Replace formulas with their calculated values" section of the document you posted. It's possible to select all the sheets and copy/paste special in Excel itself to get the results rather than the formulas, but I have had some issues working with grouped sheets through code.

这篇关于保存重新计算的Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！