locked
Export Database Data In Excel File RRS feed

  • 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?

    Tuesday, February 12, 2019 3:52 PM

All replies

  • 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.

    Tuesday, February 12, 2019 7:56 PM
  • 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

    Wednesday, February 13, 2019 9:11 AM
  • 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 

    Saturday, February 23, 2019 11:53 AM
  • 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.

    Saturday, February 23, 2019 3:14 PM
  • 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

    Monday, February 25, 2019 7:34 AM