How to fetch a excel file from a folder?

Question

I'm having a excel file in a folder. but i dont know to fetch that file from the folder.

but I'm checking whether the file exists.

here is my code:

protected void Page_Load(object sender, EventArgs e)
{
     string filePath = Server.MapPath("~/Upload/Sample.xlsx");
     bool fileexists = File.Exists(filePath); //Here fileexists = true    
}

I need to save that excel file in sql database.

I need to save the fileName(varchar(256)),Data(varbinary(max)),Path(varchar(256)) of that excel file into sql database.

please help me out

all you have to do is to convert your file in stream and then byte array and save it in db using any standard `ADO.NET` db class, did you face any problem doing so? did you even try it? — Manish Mishra, Dec 31 '13 at 08:02
Convert the file into byte[] (http://stackoverflow.com/questions/2030847/best-way-to-read-a-large-file-into-byte-array-in-c) and get the name and the path and save them. Personally i would suggest to save only the path and name if the is possibility to save a lot of files. — kostas ch., Dec 31 '13 at 08:03
Check this out: http://stackoverflow.com/questions/175763/resources-for-learning-c-sharp-excel-interop#176130 — Mateusz, Dec 31 '13 at 08:03

score 2 · Answer 1 · answered Dec 31 '13 at 08:09

Try this to get and read an xlsx file .

if (Directory.Exists(Server.MapPath("path")))
{
             string filename = Path.GetFileName("path");// to get filename
 string conStr = string.Empty;
        string extension = Path.GetExtension(filename);// get extension
        if (extension == ".xls" || extension == ".xlsx")
        {
            switch (extension)
            {
                case ".xls": //Excel 1997-2003
                    conStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source='" + mappingPath + "';" + "Extended Properties=Excel 8.0;";
                    break;
                case ".xlsx": //Excel 2007
                    conStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source='" + mappingPath + "';" + "Extended Properties=Excel 8.0;";
                    break;
                default:
                    break;
            }

            OleDbConnection connExcel = new OleDbConnection(conStr.ToString());
            OleDbCommand cmdExcel = new OleDbCommand();
            OleDbDataAdapter oda = new OleDbDataAdapter();
            connExcel.Open();

            DataTable dtExcelSchema;
            dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
            ViewState["SheetName"] = SheetName;
            //Selecting Values from the first sheet
            //Sheet name must be as Sheet1
            OleDbDataAdapter da = new OleDbDataAdapter("SELECT * From [" + SheetName + "]", conStr.ToString()); // to fetch data from excel 
            da.Fill(dtExcel);

}

Hmm? I think you've misread the question - "how to save a file as byte array to SQL's varbinary filed". Your answer is to totally different "how to read data from Excel file". — Alexei Levenkov, Dec 31 '13 at 08:27

score 0 · Accepted Answer · answered Dec 31 '13 at 10:51

This is much simpler than you think, it should be something like:

if (File.Exists(filePath)) {   
    byte[] data = File.ReadAllBytes(filePath);
    string fileName = Path.GetFileName(filePath);

    const string query = "INSERT INTO Files (FileName, Data, Path) VALUES (@FileName, @Data, @Path)";

    using (var connection = new SqlConnection(connectionString))
    using (var command = new SqlCommand(query, connection)) {
        command.Parameters.AddWithValue("@FileName", fileName);
        command.Parameters.AddWithValue("@Data", data);
        command.Parameters.AddWithValue("@Path", filePath);

        connection.Open();
        command.ExecuteNonQuery();
    }
}

How to fetch a excel file from a folder?

2 Answers2