Save an excel file as values using C#

Question

I have an excel file that has formulas in them. The formulas uses some functions that requires some third party add ins. I am trying to save the excel file as csv using the following code in C#. But the formula fields show up as an error. How can I save the formula fields as values and then save it in a csv.

 public static void SaveExcelFileAsCsvAllSheets(string fileName)
    {
        var app = new Microsoft.Office.Interop.Excel.Application();
        try
        {
            Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Open(fileName);
            app.DisplayAlerts = false;
            app.ScreenUpdating = false;
            int sheetCount = 1;
            foreach (Microsoft.Office.Interop.Excel.Worksheet sht in wb.Worksheets)
            {
                if (sht.Visible == XlSheetVisibility.xlSheetVisible)
                {
                    sht.Select();
                    wb.SaveAs(Path.GetDirectoryName(fileName) + @"\" + Path.GetFileNameWithoutExtension(fileName) + "-" + sheetCount.ToString() + ".csv", Microsoft.Office.Interop.Excel.XlFileFormat.xlCSV, AccessMode: XlSaveAsAccessMode.xlNoChange);
                    sheetCount++;
                }

            }

            wb.Close(false);
            app.DisplayAlerts = true;
            app.ScreenUpdating = true;
        }
        catch (Exception ex)
        {
            throw;
        }
        finally
        {
            app.Quit();

        }
    }

Yuri Kopylovski · Accepted Answer · 2013-03-18 18:14:56Z

2

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

using Excel = Microsoft.Office.Interop.Excel;
Excel.Range targetRange = (Excel.Range)CurrentSheet.UsedRange;
targetRange.Copy(Type.Missing);
targetRange.Paste(Excel.XlPasteType.xlPasteValues, 
    Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);

answered Mar 18, 2013 at 18:14

Yuri Kopylovski

3802 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Save an excel file as values using C#

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related