clearing excel file before upload

[cyberdaemon]

Good day,

I have a module that upload a record using excel file, and that excel file will be save into the table.. actually i already created that but one ofmy requirement is to clear first the record in excel before it will be uploaded.. how could i clear the sheet of an excel file using C#.

here my code..

string conn = "";
            if (FileUpload1.HasFile)
            {
                string fname = Path.GetFileName(FileUpload1.PostedFile.FileName);
                string fExtension = Path.GetExtension(FileUpload1.PostedFile.FileName);
                string fLocation = Server.MapPath("/ExcelFileUpload/" + fname);
                FileUpload1.SaveAs(fLocation);

                if (fExtension == ".xls")
                {
                    conn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                }
                else if (fExtension == ".xlsx")
                {
                    conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                }

                OleDbConnection con = new OleDbConnection(conn);
                OleDbCommand cmd = new OleDbCommand();
                cmd.CommandType = CommandType.Text;
                cmd.Connection = con;
                OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
                DataTable dtCOO9Report = new DataTable();
                con.Open();
                DataTable dtExcelsheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                string getExcelSheetName = dtExcelsheetName.Rows[0]["TABLE_NAME"].ToString();
                cmd.CommandText = "Select * from [" + getExcelSheetName + "]";
                dAdapter.SelectCommand = cmd;

                DataSet ds = new DataSet("dsExcelC009ReportRecords");
                dAdapter.Fill(ds, "dtExcelC009ReportRecords");

                foreach (DataColumn dc in ds.Tables["dtExcelC009ReportRecords"].Columns)
                {
                    dc.ColumnMapping = MappingType.Attribute;
                }


                con.Close();
                GridView1.DataSource = ds;
                GridView1.DataBind();



                _c009ReportEntities.xmlC009Report = ds.GetXml();
                if (_c009ReportBll.InsertXMLC009Report(_c009ReportEntities) > 0)
                {
                }

for uploading excel file..
once the excel file upload it will converted into xml then that xml will be inserted in the table..

any suggestion on how could i clear my excel file before uploading

thanks

[deceptikon]

I'm not sure I understand the requirement. You want to clear the Excel workbook before uploading it to the database? That sounds like a no-op, since the upload on an empty file will do nothing. Perhaps you mean clear the file after uploading? But if that's the case, why not just delete the file?

[Ketsuekiame]

I think he means that the Workbook contains many Worksheets and he needs to delete the first worksheet before uploading it to the database. (Maybe for privacy concerns?)

Unfortunately I don't know anything about using Excel in C# so beyond my interpretation I can't offer anything else ^^

[deceptikon]

I think he means that the Workbook contains many Worksheets and he needs to delete the first worksheet before uploading it to the database.

That still doesn't make much sense. Just don't upload the first worksheet.

Unfortunately I don't know anything about using Excel in C# so beyond my interpretation I can't offer anything else ^^

He's accessing Excel through an OleDB connection, so aside from a few sticking points like the worksheet name, it's just straight ADO.NET. I like to use this method as well for when installing Office on a machine is prohibitive (the "official" Excel interop requires office to be installed...), even though it's not as convenient for more advanced tasks.

[Ketsuekiame]

That still doesn't make much sense. Just don't upload the first worksheet.

I didn't realise you could be that selective. In my mind I was using the concept of a workbook and uploading this container, rather than uploading the individual worksheets.

He's accessing Excel through an OleDB connection, so aside from a few sticking points like the worksheet name, it's just straight ADO.NET. I like to use this method as well for when installing Office on a machine is prohibitive (the "official" Excel interop requires office to be installed...), even though it's not as convenient for more advanced tasks.

Believe it or not, I actually have little to no experience in this area. I work with straight SQL connections usually and have done some very basic Data Grid View binding, but OleDb is something I seemed to have avoided over the past 10 years. I don't know the capabilities of ADO.NET nor how they link with the Excel data structures.

I was only hoping to clarify his question so you could give the answer sooner rather than later. Although if what you're saying is correct (which I have no doubt) then yeah, seems like this is a bit of a No-Op...

[deceptikon]

In my mind I was using the concept of a workbook and uploading this container, rather than uploading the individual worksheets.

From an OleDB perspective, an Excel spreadsheet is just a collection of database tables (aka. worksheets) that are named something like "Sheet1$" (default), "Sheet2$", etc... So just like in any database, if it doesn't make sense to query a table, don't query the table. ;)

[cyberdaemon]

Good day..

Here i want to do.. first my application will upload an excel file for the report.. and that excel file will be converted into a record in the database, assuming i want to upload another report.. so first there's is old report right.. so before i upload the new report in excel i will clear first the data in the previous excel.. before the uploading will continue..

Actually in my Stored procedure.. before the report will converted into database it will delete the previous record in it.. then insert the new value..

[deceptikon]

Are you wanting to clear data from the database or the actual Excel file?

[cyberdaemon]

the actual excel file...

[Ketsuekiame]

If you're given another report to upload, surely this is another separate Excel file?

Otherwise, deceptikon is correct, simply don't upload the existing report. If you want to delete it from the database, send an SQL query to do that for you.

Why you'd want to delete from the database I don't know, seems kind of pointless...

[deceptikon]

the actual excel file...

Okay, so within the Excel file are you trying to remove a single worksheet or does the file only consist of one worksheet? The ideal situation would be if you could just delete the file since that's the simplest. If you really need more control over the spreadsheet than insert, update, and select, you'll have to move to a more specialized library than ADO.NET such as the Microsoft Excel interop or any number of third party libraries that support Excel automation (NPOI, for example).

Why you'd want to delete from the database I don't know, seems kind of pointless...

I can imagine a "latest report" table where the previous report would need to be removed before inserting the new one. It's not completely unreasonable, but in that case unless there are static rows that can't be removed a simple truncate command would suffice:

public void ClearLatest(SqlConnection connection)
{
    new SqlCommand("truncate table latest_report", connection).ExecuteNonQuery();
}

Foreign keys would throw a wrench into a simple truncation, but then I'd question why such a transient table has foreign keys in the first place.

[cyberdaemon]

thanks for the advice..