Read CSV and XLSB file in ASP.Net Core

Last Reply 3 months ago By dharmendr

Posted 3 months ago

Hello Sir ,

I am reading xls and xlsx file using openxml library its working fine but in case of csv and xlsb file is not reading

how can i read these two file also in openxml llibrary using ASP.NET Core

please help us

                    using DocumentFormat.OpenXml.Packaging;
                    using DocumentFormat.OpenXml.Spreadsheet;

                    var path = Path.Combine(
                           Directory.GetCurrentDirectory(), "Data",
                          Uploadedfilename);
						  
                    using (SpreadsheetDocument doc = SpreadsheetDocument.Open(path, false))
                    {
                        using (DbContext db = new DbContext())
                        {
                            WorkbookPart wbPart = doc.WorkbookPart;
                            string cs = db.CONNECTION_STRING;
                            
                            int worksheetcount = doc.WorkbookPart.Workbook.Sheets.Count();
                            Sheet sheet = (Sheet)doc.WorkbookPart.Workbook.Sheets.ChildElements.GetItem(SheetNumber-1);
                            
                            Worksheet worksheet = (doc.WorkbookPart.GetPartById(sheet.Id) as WorksheetPart).Worksheet;
                            IEnumerable<Row> rows = worksheet.GetFirstChild<SheetData>().Descendants<Row>();
                            int colCnt = rows.ElementAt(0).Count();
                            
                            var Rnumber = map.RowNumber;
                            DataTable dt = new DataTable();

                            List<string> columnRef = new List<string>();
                            
                            foreach (Row row in rows)
                            {
                                if (row.RowIndex.Value == Rnumber)
                                {
                                    RCount = 2;
                                    map.Count = RCount;
                                    
                                    AddQuery += "IF OBJECT_ID('dbo." + map.FileName + "', 'U') IS NULL ";
                                    AddQuery += "BEGIN ";
                                    AddQuery += "CREATE TABLE [dbo].[" + map.FileName + "](";
                                    
                                    try
                                    {
                                        foreach (Cell cell in row.Descendants<Cell>())
                                        {
                                            dt.Columns.Add(RemoveSpecialCharacters(GetValue(doc, cell)));
                                            AddQuery += "[" + RemoveSpecialCharacters(GetValue(doc, cell)).ToString() + "]" + " VARCHAR(MAX),";
                                            var DtMsrcFld = obj.InsertMainSrcField(map.FileName, RemoveSpecialCharacters(GetValue(doc, cell)).ToString(), GetValue(doc, cell));
                                        }
                                        AddQuery = AddQuery.TrimEnd(',');
                                        AddQuery += ")";
                                        AddQuery += " END";
                                    }
                                    catch (Exception e)
                                    {
                                        var response = e.Message;
                                        //return response;
                                    }
                                }
                                else
                                {
                                    dt.Rows.Add();
                                    int i = 0;
                                    try
                                    {
                                        foreach (Cell cell in row.Descendants<Cell>())
                                        {

                                            i = (int)GetColumnIndexFromName(GetColumnName(cell.CellReference));
                                            
                                            dt.Rows[dt.Rows.Count - 1][i - 1] = GetValue(doc, cell).ToString();
                                            
                                        }
                                    }
                                    catch (Exception ee)
                                    {
                                        ee.ToString();
                                    }
                                }
                            }                                                                             
                        }                        
                    }                                     

 

 

You are viewing reply posted by: dharmendr 3 months ago.