Click here to Skip to main content
15,885,767 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Im creating an application where i have to read and update data from Excel Spreadsheet.
Posted

Look at EPPlus[^]
 
Share this answer
 
If just read and update ,that's very simple,you can create your own operation surface,then in the part of realizing function,you can try this excel library[^] .Download a free edition (which can access most of it's supported funtion),and add reference to your project.
Read Excel:
//Create a new workbook
Workbook workbook = new Workbook();       
//Load a file and imports its data
workbook.LoadFromFile(@"..\FandH.xlsx");  

Edit cell:
C#
//Edit Cell Value
          sheet.Range["E2"].Value = "00-1-285-7901742";
          sheet.Range["E2"].Style.Font.FontName = "Book Antiqua";
          sheet.Range["E2"].Style.Font.Color = Color.DarkOrange;

Other functions as conversion or cell merge is included, more tutorials .
here[^]
 
Share this answer
 
Step1:Upload your spreadsheet in some temporary folder

C#
private void btnUpload_Click(object sender, EventArgs e)
       {
           string sourceFile = fileDlg.FileName;
           string[] fileName = sourceFile.Split('\\');

           if (!Directory.Exists(TempDirectory))
           {
               Directory.CreateDirectory(TempDirectory);
           }

           string destinationFile = TempDirectory + fileName[fileName.Length - 1];

           if (File.Exists(sourceFile) && !File.Exists(destinationFile))
           {
               File.Copy(sourceFile, destinationFile);
               MessageBox.Show("File copied...", "Notification");
           }
           else if (!File.Exists(destinationFile))
           {
               MessageBox.Show("Select a file...", "Error");
           }
           else
           {
               MessageBox.Show("File already exist...", "Notification");
           }

           txtBoxFile.Text = string.Empty;
           GetAvailableFiles();
       }






C#
private void GetAvailableFiles()
      {
          cbAllFiles.Items.Clear();
          cbSheets.Items.Clear();
          cbAllFiles.Text = "Select";
          cbSheets.Text = "Select";
          string[] allfiles = Directory.GetFiles(TempDirectory);
          var fileList = (from f in allfiles select f.Split('\\')[f.Split('\\').Length - 1]).ToArray<string>();
          var extList = new List<string>() { ".xls", ".xlsx" };
          fileList = fileList.Where(f => extList.Contains(Path.GetExtension(f)) && !f.StartsWith("~")).ToArray<string>();
          cbAllFiles.Items.AddRange(fileList);
      }

N.B:
cbAllfiles is ID of a combobox
cbSheets is ID of another combobox which tracks the sheet name

For getting the sheet name from cbAllFileName selected index changed event:


C#
private void cbAllFiles_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (((ComboBox)sender).SelectedIndex != -1)
            {
                string DB_Path = string.Format("{0}{1}", TempDirectory, cbAllFiles.SelectedItem.ToString());
                string fileExt = Path.GetExtension(DB_Path);

                if (fileExt == ".xlsx")
                    ExcelConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + DB_Path + ";Extended Properties=Excel 12.0;";
                else
                    ExcelConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DB_Path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";";

                try
                {
                    OleDbConnection connExcel = new OleDbConnection(ExcelConnection);
                    connExcel.Open();
                    DataTable schemaTable = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                    String sheetName;
                    List<string> lstsheetNames = new List<string>();

                    foreach (DataRow row in schemaTable.Rows)
                    {
                        sheetName = row.Field<string>("TABLE_NAME").Trim();

                        if (sheetName.Length > 1 && sheetName.EndsWith("$"))
                        {
                            
                                lstsheetNames.Add(sheetName.Substring(0, sheetName.Length - 1));
                        
                       
                    }

                    cbSheets.Items.Clear();
                    cbSheets.Items.AddRange(lstsheetNames.ToArray());
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message + " StackTrace: " + ex.StackTrace, "Exception");
                }
            }
        }



Step-2:

C#
string excelName = Path.GetFileNameWithoutExtension(cbAllFiles.SelectedItem.ToString());

 string sheetName = cbSheets.SelectedItem.ToString();


//This is where you can get all the rows details in a datatable

C#
DataTable dt = DataManager.GetSheetData(sheetName, ExcelConnection);


C#
public static DataTable GetSheetData(string sheetName, string excelConn)
        {
            DBAccess db = new DBAccess(excelConn);
            string cmdText = "Select * from [" + sheetName + "$]";
            db.CommandText = cmdText;
            return db.ExecuteExcelDataSet();
        }




If you feel this is a right solution then tick on green color button please.
Thanks,
 
Share this answer
 
v3

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