Helping you always. Don't forget to click "Mark as Answer" on the post that helped you.
♠ ASP.NET Core Tutorials → Start from the Beginning and become an Expert in 30 days time ♠
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:
Member
1 Points
48 Posts
Export Database Data In Excel File
Feb 12, 2019 03:52 PM|ohoud|LINK
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?
All-Star
58124 Points
15633 Posts
Re: Export Database Data In Excel File
Feb 12, 2019 07:56 PM|bruce (sqlwork.com)|LINK
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.
Contributor
2253 Points
735 Posts
Re: Export Database Data In Excel File
Feb 13, 2019 09:11 AM|Xing Zou|LINK
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:
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
Member
1 Points
48 Posts
Re: Export Database Data In Excel File
Feb 23, 2019 11:53 AM|ohoud|LINK
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
Participant
1253 Points
935 Posts
Re: Export Database Data In Excel File
Feb 23, 2019 03:14 PM|yogyogi|LINK
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.
♠ ASP.NET Core Tutorials → Start from the Beginning and become an Expert in 30 days time ♠
Contributor
2253 Points
735 Posts
Re: Export Database Data In Excel File
Feb 25, 2019 07:34 AM|Xing Zou|LINK
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