Last post 5 minutes ago by yogyogi
None
0 Points
24 Posts
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
50854 Points
12097 Posts
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.
Participant
1230 Points
437 Posts
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:
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
3 hours, 27 minutes ago|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
Member
530 Points
355 Posts
5 minutes ago|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.
None
0 Points
24 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
50854 Points
12097 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.
Participant
1230 Points
437 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
None
0 Points
24 Posts
Re: Export Database Data In Excel File
3 hours, 27 minutes ago|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
Member
530 Points
355 Posts
Re: Export Database Data In Excel File
5 minutes ago|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.