DOT NET CORE

 

Excel reading - by -- Microsoft default openxml package. -- - --- -

using DocumentFormat.OpenXml.Packaging;

using DocumentFormat.OpenXml.Spreadsheet;

using Microsoft.AspNetCore.Http;

using System.Data;

 

namespace AM.ASHEET

{

    public class ESheet

    {

        public static DataTable ReadExcelToDTable(IFormFile file, int col)

        {

            DataTable dt = new DataTable();

            using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(file.OpenReadStream(), false))

            {

                WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;

                Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().First();

                WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id);

                SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();

 

                IEnumerable<Row> rows = sheetData.Descendants<Row>(); 

                int icnt = 0;

                foreach (Cell cell in rows.ElementAt(0))

                {

                    if (icnt < col)

                        dt.Columns.Add(GetCellValue(spreadsheetDocument, cell));

                    icnt++;

                }

                foreach (Row row in rows) //this will also include your header row...

                {

                    DataRow tempRow = dt.NewRow();

 

                    for (int i = 0; i < col; i++)

                    {

                        tempRow[i] = GetCellValue(spreadsheetDocument, row.Descendants<Cell>().ElementAt(i));

                    }

                    dt.Rows.Add(tempRow);

                }

            }

            dt.Rows.RemoveAt(0);

            return dt;

        }

        public static string GetCellValue(SpreadsheetDocument document, Cell cell)

        {

            SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;

            string value = cell.CellValue.InnerXml;

 

            if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)

            {

                return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;

            }

            else

            {

                return value;

            }

        }

    }

}

 [HttpPost]

[Route("IProjUpload")]

public Result InvProjAdminTslUpload(IFormFile file)

{

    Result er = new Result();

    try

    {

        if (file != null || file.Length > 0)

        {

            var httpRequest = _iHttpContextAccessor.HttpContext.Request;

            DataTable dt = new ESheet().ReadExcelToDTable(file, 5);

        er.Message += "ash";

    }

}

catch (Exception ex)

{

    //Helper.WriteToLog("API", " -> IProjUpload ()", ex);

    er.IsSuccess = AConst.FAIL;

    er.Message = ex.Message;

}

return er;

PROGRAM .CS 

using Microsoft.AspNetCore.Authentication.Negotiate;

using Microsoft.Extensions.DependencyInjection.Extensions;

 

internal class Program

{

    private static void Main(string[] args)

    {

        var builder = WebApplication.CreateBuilder(args);

        builder.Services.AddCors(options =>

        {

            options.AddPolicy("p1",

                                  policy =>

                                  {

                                      policy.WithOrigins("http://localhost:8080",

                                                          "https://localhost:8080")

                                                          .AllowAnyHeader()

                                                          .AllowAnyMethod();

                                  });

        });// ash: for cors api and site access allowing

        // Add services to the container. //"https://dca-dev-997",

        //windows authentication

        builder.Services.AddAuthentication(NegotiateDefaults.AuthenticationScheme).AddNegotiate();

        builder.Services.AddAuthorization(options =>

        {

            options.FallbackPolicy = options.DefaultPolicy;

        }); //ash: for windows authentication

        //builder.Services.AddAuthentication(NegotiateDefaults.AuthenticationScheme).AddNegotiate();

        //builder.Services.AddAuthorization(options =>

        //{

        //    // By default, all incoming requests will be authorized according to the default policy.

        //    options.FallbackPolicy = options.DefaultPolicy;

        //});

        builder.Services.TryAddSingleton<IHttpContextAccessor, HttpContextAccessor>();//ash: for http context

 

        builder.Services.AddControllers();

        // Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle

        builder.Services.AddEndpointsApiExplorer();

        builder.Services.AddSwaggerGen();

      

        var app = builder.Build();

        // Configure the HTTP request pipeline.

        if (app.Environment.IsDevelopment() || app.Environment.IsProduction()) //ash:swagger in prod

        {

            app.UseSwagger();

            app.UseSwaggerUI();

        }

 

        app.UseRouting();

        app.UseCors("p1"); //app.UseCors();

        app.UseAuthentication();

        app.UseAuthorization();

        //app.UseAuthentication(WindowsPrincipal);

        app.MapControllers();

 

        app.Run();

    }

}