public ActionResult <IEnumerable <Person> > Get(ExcelReaderRequest request) { string fileurl = System.IO.Directory.GetCurrentDirectory() + "\\Excel Document\\" + request.fileName; var results = new PersonAdaptor(fileurl, request.sheetName) .Execute(); return(Ok(results)); }
internal DataSet ReadExcelFile(ExcelReaderRequest excelReaderRequest) { try { String connectionString = string.Empty; if (excelReaderRequest.FileExtensiion.ToUpper().CompareTo(".XLS") == 0) { connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelReaderRequest.FilePath + @";Extended Properties=""Excel 8.0;HDR=YES;"""; } else if (excelReaderRequest.FileExtensiion.ToUpper().CompareTo(".XLSX") == 0) { connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelReaderRequest.FilePath + @";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"""; } OleDbConnection connection = new OleDbConnection(connectionString); OleDbDataAdapter adapter = new OleDbDataAdapter(); OleDbCommand selectCommand = new OleDbCommand(); connection.Open(); DataTable worksheets; worksheets = connection.GetSchema("Tables"); DataRow dr1 = worksheets.Rows[0]; Object[] rowitems = dr1.ItemArray; String item1 = ""; item1 = Convert.ToString(rowitems[2]); DataTable columns; String[] restrictions = { null, null, item1, null }; columns = connection.GetSchema("Columns", restrictions); //columns = connection.GetSchema("Columns"); int fieldcount = 0; fieldcount = columns.Rows.Count; int[] fieldnoarr = new int[fieldcount]; String[] fieldarr = new String[fieldcount]; String selectstr = "select "; DataRow drc; Object[] temparr; for (int jj = 0; jj <= fieldcount - 1; jj++) { //DataRow drc = columns.Rows[jj]; drc = columns.Rows[jj]; //Object[] temparr = drc.ItemArray; temparr = drc.ItemArray; fieldnoarr[jj] = Convert.ToInt32(temparr[6]); fieldarr[fieldnoarr[jj] - 1] = (String)temparr[3]; } for (int jj = 0; jj <= fieldcount - 1; jj++) { if (jj == 0) { selectstr += "[" + fieldarr[jj] + "]"; } else { selectstr += "," + "[" + fieldarr[jj] + "]"; } } selectstr += " FROM [" + item1 + "]"; selectCommand.CommandText = selectstr; selectCommand.Connection = connection; adapter.SelectCommand = selectCommand; DataSet transrows = new DataSet(); adapter.Fill(transrows, item1); return(transrows); } catch (Exception ex) { return(null); } }