Click here to Skip to main content
15,912,329 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Dear friends i have an excel sheet in which some column,
i am saving that excel file into my database.

i am validating file extension like .xls, .xlsx

but i want to know that the file which user select have the sheet with name "DATA" or not

if the sheet exist than i want to check

that the column which are there in the excel sheet is same like the required format?
Posted

 
Share this answer
 
Comments
prince_rumeel 8-Jul-13 3:22am    
Dear i don't think that you read my question carefully

dear my data already inserting in database,

my question is that how can i check the file which selected by user is in well format,

i want to check that user not select any empty file, or not well formated file
XML
<form enctype="multipart/form-data">
    <input type="file" id="excel-file" name="excel-file" />
    <input type="submit" />
</form>


C#
protected void Page_Load(object sender, EventArgs e)
{
    if(this.IsPostback)
    {
        var yourFile = this.Request.Files["excel-file"];
    }
}
 
Share this answer
 
You will have to append your file consistency check with the following logicas steps:
1. Open the created excel file
2. Check if sheet, with the specific name, exists.
if it exists, then:
2.1 Check the columns (more precise it would be to say cells) for what data type they are.

Here is an example you can use:
C#
private void Excel_Inspector(string Path_to_your_selected_file)
        {
try{
            //create excel application object
            Microsoft.Office.Interop.Excel.Application Excel_App = new Microsoft.Office.Interop.Excel.Application();
            //create excel workbook object and open the excel file for inspection
            Microsoft.Office.Interop.Excel.Workbook Excel_Wbk = Excel_App.Workbooks.Open(Path_to_your_selected_file);
            //this will make excel visible (you can set it to false if you like)
            Excel_App.Visible = true;
            //this will count how many sheets your excel has
            int sheet_number = Excel_Wbk.Sheets.Count;
            //loop through all excel sheets reading how the sheets are named
            while (sheet_number >= 1)
            {
                //check if sheet name is "DATA"
                if (Excel_Wbk.Sheets[sheet_number].Name == "DATA")
                {
                    /* create string var and assign the value to it - in this case I am reading
                     * cells "A1" number format. Please note that if you have date value it will
                     * NOT say "short date" or "long date". instead it will show you the actual format.
                     * For example "yyyy/dd/M"
                     */
                    string Number_Format = Excel_App.Range["A1"].NumberFormat.ToString();
                     //do something with number format (for now - we're just displaying it)
                    MessageBox.Show(Number_Format);
                }
                sheet_number--;
            }
            /* closing and cleanup part */
            //close and don't save the file (true = save, false = don't)
            Excel_Wbk.Close(false);
            Excel_App.Workbooks.Close();
            Excel_App.Quit();
            //release objects
            System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel_Wbk);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel_App);
}catch (Exception ex) 
{MessageBox.Show(ex.Message); }


Hope this puts you on the track.
Good luck!

Modestas
 
Share this answer
 
v2
Comments
prince_rumeel 8-Jul-13 6:08am    
Nice thanks you give me a way.

but i am not using Interop

i want to use sql bulk copy to save my data.
MK-Gii 8-Jul-13 6:13am    
Aha.... then you gonna have to find some alternative as really... I am not sure if there is another way of checking excel content without using interop... But would be glad to know if there is one :)
prince_rumeel 8-Jul-13 9:26am    
any suggestion from others????

please guide me
C#
private bool sheetNameValidation()
        {
            ////
            string FileName = Path.GetFileName(FileUpload.PostedFile.FileName);
            string Extension = Path.GetExtension(FileUpload.PostedFile.FileName);
            string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
            string FilePath = Server.MapPath(FolderPath + FileName);
            ////
            //string ExcelConnectionString = Convert.ToString(Session["ExcelConnectionPath"]);
            string ExcelConnectionString = FileExtension(FileName, FilePath);

            string myExcelDataQuery = "Select top 1 * from [Data$]";
            OleDbConnection OleDbConn = new OleDbConnection(ExcelConnectionString);
            OleDbCommand OleDbCmd = new OleDbCommand(myExcelDataQuery, OleDbConn);
            OleDbConn.Open();

            DataTable dt = new DataTable();
            dt = OleDbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            if (dt == null)
            {
                return false;
            }

            String[] excelSheets = new String[dt.Rows.Count];
            int i = 0;

            // Add the sheet name to the string array.
            foreach (DataRow row in dt.Rows)
            {
                excelSheets[i] = row["TABLE_NAME"].ToString();
                i++;
            }            
            OleDbConn.Close();

            if (!excelSheets.Contains("Data$"))
            {
                Master.showError = "Invalid excel document. Please select correct file.";
                return false;
            }
            else
            {
                return true;
            }
        }


this is a simple way.i solved it my ownself.
and posting reply for help of others.

For any query please ping me

rumeel2004@gmail.com
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900