none
How to prevent the dialog box while saving an excel file to CSV in C# RRS feed

  • Question

  • I have an excel file with multiple sheets, where in i search for the tab name and if the tab matches my criteria I save it as CSV file.

    var excelFile = Path.GetFullPath("E:\\ExcelSheet.xlsx");
                var saveLoc = Path.GetFullPath("E:\\ExcelModified.csv");
                var excel = new Excel.Application();
                var workbook = excel.Workbooks.Open(excelFile);
    
    
                foreach(Excel.Worksheet tempSheet in workbook.Worksheets)
                {
                    if(((Excel.Worksheet)(tempSheet)).Name.Contains("test"))
                    {
                        if (File.Exists(saveLoc))
                        {
                            File.Delete(saveLoc);
                        }
                        //File.Create(saveLoc);
                        tempSheet.Select();
                        var test = tempSheet.Cells[1, 14] ;
                       tempSheet.Select();
                    workbook.SaveAs(saveLoc, Excel.XlFileFormat.xlCSV, Excel.XlSaveAsAccessMode.xlNoChange);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(tempSheet);
    
    
                    }
                }
    
                workbook.Save();
    
                workbook.Close();
                excel.Quit();
    
                //System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);

    have this function automated. Is there a way I can prevent this prompt?


    • Moved by Andy ONeill Sunday, March 6, 2016 4:42 PM not wpf
    • Edited by PerplexedDev Sunday, March 6, 2016 11:13 PM
    • Moved by CoolDadTx Tuesday, March 8, 2016 3:49 PM Office related
    Sunday, March 6, 2016 7:29 AM

Answers

  • Yes there is. I don't have an e drive or your file so that part is slightly different.

    I also notice you're saving as .xlsx rather than .csv and you would over-write if you had more than one file.

    So there are a couple more changes in this:

                var excelFile = Path.GetFullPath("D:\\Biz\\XLTest.xlsx");
                string saveLoc =  "D:\\Biz\\XLTestModified";
                var excel = new Microsoft.Office.Interop.Excel.Application();
                var workbook = excel.Workbooks.Open(excelFile);
                workbook.Application.DisplayAlerts = false;
    
                var sheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Item[1]; // 1 is the first item, this is NOT a zero-based collection
                string saveLocN;
                int i = 1;
                foreach (Microsoft.Office.Interop.Excel.Worksheet tempSheet in workbook.Worksheets)
                {
                    if (((Microsoft.Office.Interop.Excel.Worksheet)(tempSheet)).Name.Contains("test"))
                    {
                        saveLocN = saveLoc + i.ToString() + ".csv";
                        if (File.Exists(saveLocN))
                        {
                            File.Delete(saveLocN);
                        }
                        //File.Create(saveLoc);
                        tempSheet.Select();
                        var test = tempSheet.Cells[1, 14];
                      //  tempSheet.SaveAs(saveLoc);
                        //workbook.SaveAs(saveLoc);//, Excel.XlFileFormat.xlExcel12, Excel.XlSaveAsAccessMode.xlNoChange);
    
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(tempSheet);
    
                        tempSheet.SaveAs(saveLocN, Microsoft.Office.Interop.Excel.XlFileFormat.xlCSVWindows, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                        i++;
                    }
    
                }
    
                workbook.Save();
    
                workbook.Close();
                excel.Quit();
    
                //System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);

    .

    Please notice that there is no wpf specific content in this question so you should really have posted to the c# forum.

    .

    Please don't forget to mark a post or posts as answer and consider upvoting those which you considered helped or you like.

    Thanks in advance.


    Hope that helps.

    Technet articles: WPF: Layout Lab; All my Technet Articles

    • Marked as answer by David_JunFeng Sunday, March 13, 2016 1:01 PM
    Sunday, March 6, 2016 4:42 PM
  • This is the answer I give everyone asking for working with Office Formats from C#:

    If you can limit to working with the newer formats (.xlsx, .docx) you can use the OpenXML SDK. Or ZipArchieve and XMLReader classes.
    They are fully known open formats, that are just a bunch of XML files in a renamed .zip container.

    If you have to support the older formats, you have to use the (t)rusty Office COM interop.
    Wich requires Office to be installed on the computer, does not work from non-interactive sessions (Windows Services, Webservers) and has all the headaches of working with COM.
    There might be ways to do specific jobs (reading, limited exporting) for your Dispaly technology of choice with the old formats. But since we have the COM interop to fall back on, we never developed a complete solution for those older formats.

    • Marked as answer by David_JunFeng Sunday, March 13, 2016 1:02 PM
    Tuesday, March 8, 2016 1:08 PM
  • You don't indicate which dialog box is being displayed but I try either setting Application.DisplayAlerts to False before saving the file and then True after saving the file, or, setting ActiveWorkbook.Saved to True after saving the file.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by David_JunFeng Sunday, March 13, 2016 1:02 PM
    Tuesday, March 8, 2016 1:58 PM

All replies

  • Yes there is. I don't have an e drive or your file so that part is slightly different.

    I also notice you're saving as .xlsx rather than .csv and you would over-write if you had more than one file.

    So there are a couple more changes in this:

                var excelFile = Path.GetFullPath("D:\\Biz\\XLTest.xlsx");
                string saveLoc =  "D:\\Biz\\XLTestModified";
                var excel = new Microsoft.Office.Interop.Excel.Application();
                var workbook = excel.Workbooks.Open(excelFile);
                workbook.Application.DisplayAlerts = false;
    
                var sheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Item[1]; // 1 is the first item, this is NOT a zero-based collection
                string saveLocN;
                int i = 1;
                foreach (Microsoft.Office.Interop.Excel.Worksheet tempSheet in workbook.Worksheets)
                {
                    if (((Microsoft.Office.Interop.Excel.Worksheet)(tempSheet)).Name.Contains("test"))
                    {
                        saveLocN = saveLoc + i.ToString() + ".csv";
                        if (File.Exists(saveLocN))
                        {
                            File.Delete(saveLocN);
                        }
                        //File.Create(saveLoc);
                        tempSheet.Select();
                        var test = tempSheet.Cells[1, 14];
                      //  tempSheet.SaveAs(saveLoc);
                        //workbook.SaveAs(saveLoc);//, Excel.XlFileFormat.xlExcel12, Excel.XlSaveAsAccessMode.xlNoChange);
    
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(tempSheet);
    
                        tempSheet.SaveAs(saveLocN, Microsoft.Office.Interop.Excel.XlFileFormat.xlCSVWindows, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                        i++;
                    }
    
                }
    
                workbook.Save();
    
                workbook.Close();
                excel.Quit();
    
                //System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);

    .

    Please notice that there is no wpf specific content in this question so you should really have posted to the c# forum.

    .

    Please don't forget to mark a post or posts as answer and consider upvoting those which you considered helped or you like.

    Thanks in advance.


    Hope that helps.

    Technet articles: WPF: Layout Lab; All my Technet Articles

    • Marked as answer by David_JunFeng Sunday, March 13, 2016 1:01 PM
    Sunday, March 6, 2016 4:42 PM
  • Hi, here is a simply solution which you may find it interesting. No SaveAsFile dialog box will prompt.

    using Spire.Xls;
    namespace xls2csv
    {
        class Program
        {
            static void Main(string[] args)
            {
                Workbook workbook = new Workbook();
                workbook.LoadFromFile(@"..\ExceltoCSV.xls");
                Worksheet sheet = workbook.Worksheets[0];
                sheet.SaveToFile("sample.csv", ",", Encoding.UTF8);
            }
        }
    }

    See more details from here.

    Tuesday, March 8, 2016 8:10 AM
  • This is the answer I give everyone asking for working with Office Formats from C#:

    If you can limit to working with the newer formats (.xlsx, .docx) you can use the OpenXML SDK. Or ZipArchieve and XMLReader classes.
    They are fully known open formats, that are just a bunch of XML files in a renamed .zip container.

    If you have to support the older formats, you have to use the (t)rusty Office COM interop.
    Wich requires Office to be installed on the computer, does not work from non-interactive sessions (Windows Services, Webservers) and has all the headaches of working with COM.
    There might be ways to do specific jobs (reading, limited exporting) for your Dispaly technology of choice with the old formats. But since we have the COM interop to fall back on, we never developed a complete solution for those older formats.

    • Marked as answer by David_JunFeng Sunday, March 13, 2016 1:02 PM
    Tuesday, March 8, 2016 1:08 PM
  • You don't indicate which dialog box is being displayed but I try either setting Application.DisplayAlerts to False before saving the file and then True after saving the file, or, setting ActiveWorkbook.Saved to True after saving the file.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by David_JunFeng Sunday, March 13, 2016 1:02 PM
    Tuesday, March 8, 2016 1:58 PM
  • >>>Is there a way I can prevent this prompt?

    According to your description, you could refer to How Can I Save a Single Excel Worksheet to a CSV File?

    Wednesday, March 9, 2016 7:40 AM