Export Database Data In Excel File

Question

User-297906461 posted

hi 

i was building web application that connect to Database and this is my code 

[HttpPost]
        public IActionResult Index(int AccountID,DateTime StartDate, DateTime EndDate,string ReportNO)
        {
            var UserName = User.Identity.Name;
            SqlConnection connection = new SqlConnection(my connection string );
            connection.Open();
            SqlCommand command = new SqlCommand("SELECT * FROM [dbo].[tblReports]", connection);
            SqlDataReader reader = command.ExecuteReader();
            if (reader.HasRows)
            {
reader.read();

}
return view();
}
              

how i can export SqlDataReader in excel sheet file?

Answer 1

User-474980206 posted

you're in luck, the open xml sdk has a .net core version:

   https://github.com/OfficeDev/Open-XML-SDK

they say to use 2.5 docs:

  https://docs.microsoft.com/en-us/office/open-xml/open-xml-sdk

google for open xml examples for excel.

Answer 2

User-1764593085 posted

Hi ohoud,

For exporting data to excel, you could use OpenXml or NPOI.

I create a demo using OpenXml to export SqlDataReader data (one Table data) to excel. You could refer to How to: Insert a new worksheet into a spreadsheet

1.Install OpenXml SDK:

Install-Package DocumentFormat.OpenXml -Version 2.9.0

2.In controller:

 public IActionResult GetExcel()
        {
            
            SqlConnection connection = new SqlConnection("my connection string");
            connection.Open();
            SqlCommand command = new SqlCommand("SELECT * FROM [dbo].[tblReports]", connection);
            SqlDataReader objReader = command.ExecuteReader();

            string filepath = @"c:\myfolder\test.xlsx";
            using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook))
            {
                // Add a WorkbookPart to the document.
                WorkbookPart workbookpart = spreadSheet.AddWorkbookPart();
                workbookpart.Workbook = new Workbook();

                // Add a WorksheetPart to the WorkbookPart.
                WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
                worksheetPart.Worksheet = new Worksheet(new SheetData());

                // Add Sheets to the Workbook.
                Sheets sheets = spreadSheet.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());

                // Insert a new worksheet.
                WorksheetPart newworksheetPart = InsertWorksheet(spreadSheet.WorkbookPart);

                // Insert cell A1 into the new worksheet.
                
                //Add Header
                for (int count = 0; count < objReader.FieldCount; count++)
                {
                    String FieldName = objReader.GetName(count);

                    Cell cell = InsertCellInWorksheet(GetExcelColumnName(count + 1), 1, newworksheetPart);
                    cell.DataType = CellValues.String;
                    cell.CellValue = new CellValue(FieldName);
                   
                }
                uint rowIndex = 2;
                while (objReader.Read())
                {
                    //Add Body
                    for (int col = 0; col < objReader.FieldCount; col++)
                    {
                        String FieldValue = objReader.GetValue(col).ToString();
                        Cell cell = InsertCellInWorksheet(GetExcelColumnName(col+1), rowIndex, newworksheetPart);
                        cell.CellValue = new CellValue(FieldValue);
                        cell.DataType = CellValues.String;
                    }
                    rowIndex++;
                }
                // Save the new worksheet.
                newworksheetPart.Worksheet.Save();
            }

            
            return View();
        }

        private static string GetExcelColumnName(int columnNumber)
        {
            int dividend = columnNumber;
            string columnName = String.Empty;
            int modulo;

            while (dividend > 0)
            {
                modulo = (dividend - 1) % 26;
                columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
                dividend = (int)((dividend - modulo) / 26);
            }

            return columnName;
        }

        private static WorksheetPart InsertWorksheet(WorkbookPart workbookPart)
        {
            // Add a new worksheet part to the workbook.
            WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
            newWorksheetPart.Worksheet = new Worksheet(new SheetData());
            newWorksheetPart.Worksheet.Save();

            Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
            string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);

            // Get a unique ID for the new sheet.
            uint sheetId = 1;
            if (sheets.Elements<Sheet>().Count() > 0)
            {
                sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
            }

            string sheetName = "Sheet" + sheetId;

            // Append the new worksheet and associate it with the workbook.
            Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
            sheets.Append(sheet);
            workbookPart.Workbook.Save();

            return newWorksheetPart;
        }

        // Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet. 
        // If the cell already exists, returns it. 
        private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
        {
            Worksheet worksheet = worksheetPart.Worksheet;
            SheetData sheetData = worksheet.GetFirstChild<SheetData>();
            string cellReference = columnName + rowIndex;

            // If the worksheet does not contain a row with the specified row index, insert one.
            Row row;
            if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
            {
                row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
            }
            else
            {
                row = new Row() { RowIndex = rowIndex };
                sheetData.Append(row);
            }

            // If there is not a cell with the specified column name, insert one.  
            if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
            {
                return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
            }
            else
            {
                // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
                Cell refCell = null;
                foreach (Cell cell in row.Elements<Cell>())
                {
                    if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
                    {
                        refCell = cell;
                        break;
                    }
                }

                Cell newCell = new Cell() { CellReference = cellReference };
                row.InsertBefore(newCell, refCell);

                worksheet.Save();
                return newCell;
            }
        }

Xing

Answer 3

User-297906461 posted

thank you for your help  

after reading your code and do a lot of research

this is the code i end with

var UserName = User.Identity.Name;
            if (UserName == "MYPC\\Abeer")
            {
                SqlConnection connection = new SqlConnection("my connection string");
                connection.Open();
                SqlCommand command = new SqlCommand("SELECT * FROM [dbo].[tblReports] ", connection);

                SqlDataReader reader = command.ExecuteReader();
                List<Report> Reportinfo = new List<Report>();
                while (reader.Read())
                {
                    Reportinfo.Add(new Report()
                    {
                        ID = reader.GetInt32(reader.GetOrdinal("ID")),
                        StartDate = reader.GetString(reader.GetOrdinal("StartDate")),
                        EndDate = reader.GetString(reader.GetOrdinal("EndDate")),
                        AccountID = reader.GetString(reader.GetOrdinal("AccountID"))
                    });
                }
                connection.Close();
                
                ExcelPackage pck = new ExcelPackage();
                SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
                ExcelFile ef = new ExcelFile();
                ExcelWorksheet ws = ef.Worksheets.Add("DataTable to Sheet");
                // ws.InsertDataTable(dataTable,
                //new InsertDataTableOptions()
                //{
                //    ColumnHeaders = true

                //});

                //Header Row
                ws.Cells["A1"].Value = "ID";
                ws.Cells["B1"].Value = "StartDate";
                ws.Cells["C1"].Value = "EndDate";
                ws.Cells["D1"].Value = "AccountID";
                // in which row the information will start fill
                int rowStart = 2;
                foreach (var item in Reportinfo)
                {
                    ws.Cells[string.Format("A{0}", rowStart)].Value = item.ID;
                    ws.Cells[string.Format("B{0}", rowStart)].Value = item.StartDate;
                    ws.Cells[string.Format("C{0}", rowStart)].Value = item.EndDate;
                    ws.Cells[string.Format("D{0}", rowStart)].Value = item.AccountID;
                    rowStart++;
                }
                // save the file 
                ef.Save("DataTable to Sheet.xlsx");

                ws.Cells.AutoFitColumns();
                Response.Clear();
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.Headers.Add("content-disposition", "attachment: filename=" + "ExcelReport.xlsx");
                Response.BinaryWrite(pck.GetAsByteArray());
                Response.End();
                return Json(new
                {
                    success = true,
                    responseText = "S"
                });

            }
            return Json(new
            {
                success = true,
                responseText = "S"
            });
        }

but i am facing multiple errors  i don't know how to solve it 

1) ExcelPaxkge doesn't have constructor

2)ExcelPaxkge doesn't contain definition  GetAsByteArray

3)response  doesn't contain definition BinaryWrite , AutoFitColumns , End

can you help me to solve it 

Answer 4

User-2054057000 posted

You can use Microsoft.Office.Interop.Excel DLL to export data to excel format. This tutorial: How to Export Database Records into Excel File in ASP.NET MVC will help you in solving your problem.

Answer 5

User-1764593085 posted

Hi ohoud,

From your code snippet, it seems that you are using EEPlus, GemBox and other packages or components and use the non-existing method in your code.You need to install all the packages and check what methods they contains.

Since this is no related to asp.net core development, you could ask how to use these packages on stackoverflow:

https://stackoverflow.com/questions/tagged/epplus

Xing