Clearing Excel Sheet

Question

_{Friday, April 6, 2012 2:30 PM}

Hey guys I'm writing an app that imports text file entries into Excel sheet and I got the import part working.  However since its always being imported into same worksheet is there a way I can clear the contents of that worksheet prior to importing?

Basically what happens user imports text file data into the single Excel worksheet and then uploads that worksheet to a database.  So the Excel worksheet is always the same, I just need to wipe all the data that was previously stored in there when importing.

I'm using:

using Excel = Microsoft.Office.Interop.Excel;

All replies (11)

_{Friday, April 6, 2012 3:23 PM ✅Answered | 1 vote}

Hello, you can use this method to clear the whole excel file contents:

    public static void ClearExcelFile(string path)
    {
        var excel = new Microsoft.Office.Interop.Excel.Application();
        var workbook = excel.Workbooks.Open(path);

        try
        {
            foreach (dynamic worksheet in workbook.Worksheets)
            {
                worksheet.Cells.ClearContents();
            }

            workbook.Save();
        }
        finally
        {
            workbook.Close();
            excel.Quit();
        }
    }

Hope this helps,

Miguel.

_{Friday, April 6, 2012 4:16 PM ✅Answered}

Hello, imho you are making a mistake erasing the data before, because is a work you can easily avoid. You can use Workbooks.Add method and pass your empty (but pre-formatted) excel file as a template. Then populate the excel sheets and call the SaveAs method. Then you always work with a ready-to-use workbook.

Regards,

Miguel.

_{Saturday, April 7, 2012 6:15 AM ✅Answered}

Get only the range of cells that have data (numbers) on it

http://stackoverflow.com/questions/9312310/get-only-the-range-of-cells-that-have-data-numbers-on-it

Regards,

Narendran Ponpandiyan

_{Friday, April 6, 2012 2:34 PM}

Hello, instead of clear the excel file, why dont you work always with a copy of your blank excel file? If you have concurrency is the best practice to avoid unwanted mixture of data.

Hope this helps,

Miguel.

_{Friday, April 6, 2012 2:38 PM}

I'm aware of the alternative ways, right now I just want to know if there is a way to clear all the data in the worksheet. 

Thanks

_{Friday, April 6, 2012 2:42 PM}

Just to add to this.

Excel.Application excelApp = new Excel.Application();

excelApp.Workbooks.Open(data);  //data is file name path to the excel sheet

Is there a way that I can invoke some method through excelApp that will help me do this?  I'm new to C# and couldn't find anything so far to help me with this.

_{Friday, April 6, 2012 3:36 PM}

Thanks Miguel for your responses but I figured out a shorter way to clear it after some poking around : )

Excel.Range rng = (Excel.Range)excelApp.Range["A1:F30"]; //Specify the rage
rng.Clear(); //Clears it

_{Friday, April 6, 2012 3:39 PM | 1 vote}

Yes, only if you know the range... and not for clear the contents of a sheet as you asked in your first thread :).

Regards,

Miguel.

_{Friday, April 6, 2012 3:42 PM}

Now would it be possible to select range of cells that only have data in them?

_{Friday, April 6, 2012 3:44 PM}

...

Basically what happens user imports text file data into the single Excel worksheet and then uploads that worksheet to a database.  

...

why don't you skip the step of importing the text data into Excel and import it directly to the database??

_{Friday, April 6, 2012 3:48 PM}

...

Basically what happens user imports text file data into the single Excel worksheet and then uploads that worksheet to a database.  

...

why don't you skip the step of importing the text data into Excel and import it directly to the database??

Not my job : )

Also data won't be always uploaded to the database.