Click here to Skip to main content
15,888,351 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
How to import a Excel File into DataTable in C# without using OLEDB. I searched google but didn't find a corect solution
Posted
Updated 25-May-21 20:15pm
v2

If you don't want to use OleDb then one way is to use Interop and loop through the data in the sheets. For interop, have a look at https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel(v=office.15).aspx[^] and a small tutorial, see http://www.dotnetperls.com/excel[^]
 
Share this answer
 
Comments
Maciej Los 18-Feb-15 3:44am    
+5
Wendelius 18-Feb-15 23:54pm    
Thanks Maciej
//create a new ExcelPackage
                using (ExcelPackage excelPackage = new ExcelPackage())
                {
                    //Writing Columns Name in Excel Sheet
                    ExcelWorksheet ExcelWorkSheet = null;
                    ExcelWorkSheet = excelPackage.Workbook.Worksheets.Add(dataTable.TableName);

                    //Set Column Name in Excel 
                    for (int col = 1; col <= dataTable.Columns.Count; col++)
                    {
                        //Write columns in excel sheet
                        ExcelWorkSheet.Cells[1, col].Value = dataTable.Columns[col - 1].ColumnName;
                    }

                    //Set Value in Excel
                    for (int row = 0; row < dataTable.Rows.Count; row++)
                    {
                        for (int col = 0; col < dataTable.Columns.Count; col++)
                        {
                            //Write columns in excel sheet
                            string rowData = dataTable.Rows[row][col].ToString();
                            List<string> strDocData = new List<string>();
                            string[] strtempData;
                            if (!string.IsNullOrEmpty(rowData) && rowData.StartsWith("Documents/"))
                            {
                                string[] documentData = rowData.Split(',');
                                documentData.ToList().ForEach(item =>
                                {
                                    strtempData = item.Split(new string[] { "@@" }, StringSplitOptions.None);
                                    strDocData.Add(strtempData[1]);
                                });
                                rowData = string.Join(", ", strDocData);
                            }
                            ExcelWorkSheet.Cells[row + 2, col + 1].Value = rowData;
                            ExcelWorkSheet.Cells[row + 2, col + 1].AutoFitColumns();
                        }
                    }
                    //convert the excel package to a byte array
                    byte[] byteArray = excelPackage.GetAsByteArray();
                    //clear the buffer stream
                    Response.ClearHeaders();
                    Response.Clear();
                    Response.Buffer = true;

                    string webURL = CacheHelper.GetItem("WebURL").ToString();
                    string Path = CacheHelper.GetItem("DownloadFilePath");
                    //Creating folder path to save file
                    string dataExportPath = System.Web.Hosting.HostingEnvironment.ApplicationPhysicalPath + "" + "SearchFilterDataExport" + "\\" + UserInfo.GetUserId();
                    if (!Directory.Exists(dataExportPath))
                    {
                        Directory.CreateDirectory(dataExportPath);
                    }

                    //Delete Existing File 
                    System.IO.DirectoryInfo di = new DirectoryInfo(dataExportPath);
                    foreach (FileInfo file in di.GetFiles())
                    {
                        file.Delete();
                    }

                    //WriteAllBytes in folder
                    excelName = WebHelper.ValidateFileName(excelName);
                    System.IO.File.WriteAllBytes(dataExportPath + "\\" + excelName + ".xlsx", byteArray);

                    //retrun path with file 
                    return webURL + "/SearchFilterDataExport/" + UserInfo.GetUserId() + "/" + excelName + ".xlsx";
                }
 
Share this answer
 
Comments
Richard Deeming 28-May-21 5:41am    
An unexplained code dump, using an unnamed third-party library - which I suspect is the one that used to be free, but now costs money - is not a solution to this already-answered question.

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