public ActionResult Export() { ExportExcelResult ExportExcel = new ExportExcelResult(); var 匯出的客戶資料 = 客戶資料repo.Export(客戶資料repo.All()); DataTable dt客戶資料 = ExportExcel.LinqQueryToDataTable(匯出的客戶資料); ExportExcel.ExportData = dt客戶資料; ExportExcel.FileName = "客戶資料"; ExportExcel.SheetName = "客戶資料"; return(ExportExcel.ExportExcel()); }
public JsonResult ExportReportTrip() { JsonResultBO result = new JsonResultBO(true); ReportTripViewModel reportResult = (ReportTripViewModel)SessionManager.GetValue("ReportTripSession"); ExportExcelSupplier <SelectListItem> helper = new ExportExcelSupplier <SelectListItem>(); //thư mục chứa file kết quả helper.outputFolderPath = OUTPUT_EXCEL_FOLDER_PATH; //đường dẫn file biểu mẫu helper.templateFilePath = Path.Combine(UPLOAD_PATH, TEMPLATE_REPORT_PATH); //dòng bắt đầu helper.startRow = 6; //tên file muốn kết xuất helper.fileName = "Báo cáo công tác vận chuyển.xlsx"; int rowHeight = 50; var borderCellColor = Color.FromArgb(17, 138, 203); bool isWorkBookOpen = helper.OpenWorkBook(); if (isWorkBookOpen) { var entities = reportResult.reportEntity; int currentRowIndex = helper.startRow; Range titleRange = helper.workSheet.Range["A4", "G4"]; titleRange.Value = string.Format("({0})", reportResult.timeLine); titleRange.Font.Bold = true; titleRange.Font.Color = borderCellColor; foreach (var entity in entities) { var trips = entity.groupOfTrips; Range userRange = helper.workSheet.Range["A" + currentRowIndex, "H" + currentRowIndex]; userRange.Value = null; userRange.Merge(Type.Missing); userRange.Value = entity.TEN_CANBO; userRange.Borders.LineStyle = XlLineStyle.xlContinuous; userRange.Borders.Color = borderCellColor; userRange.Font.Bold = true; userRange.Interior.Color = Color.FromArgb(255, 234, 167); userRange.RowHeight = rowHeight; userRange.HorizontalAlignment = XlHAlign.xlHAlignLeft; userRange.VerticalAlignment = XlHAlign.xlHAlignCenter; currentRowIndex++; int tripIndex = 1; foreach (var trip in trips) { helper.workSheet.Range["A" + currentRowIndex, "A" + currentRowIndex].Value = tripIndex.ToString(); helper.workSheet.Range["B" + currentRowIndex, "B" + currentRowIndex].Value = trip.TEN_CHUYEN; if (trip.NGAY_XUATPHAT != null) { helper.workSheet.Range["C" + currentRowIndex, "C" + currentRowIndex].Value = string.Format("{0} lúc {1}h{2}", string.Format("{0:dd/MM/yyyy}", trip.NGAY_XUATPHAT.Value), trip.GIO_XUATPHAT.GetValueOrDefault().ToString("D2"), trip.PHUT_XUATPHAT.GetValueOrDefault().ToString("D2")); } helper.workSheet.Range["D" + currentRowIndex, "D" + currentRowIndex].Value = trip.DIEM_XUATPHAT; helper.workSheet.Range["E" + currentRowIndex, "E" + currentRowIndex].Value = trip.DIEM_KETTHUC; if (trip.NGAYSUA != null) { helper.workSheet.Range["F" + currentRowIndex, "F" + currentRowIndex].Value = string.Format("{0} lúc {1}h{2}", string.Format("{0:dd/MM/yyyy}", trip.NGAYSUA.Value), trip.NGAYSUA.Value.Hour.ToString("D2"), trip.NGAYSUA.Value.Minute.ToString("D2")); } helper.workSheet.Range["G" + currentRowIndex, "G" + currentRowIndex].Value = trip.QUANGDUONG_DICHUYEN.GetValueOrDefault().ToString("#,#0"); helper.workSheet.Range["H" + currentRowIndex, "H" + currentRowIndex].Value = trip.TONG_CHIPHI.GetValueOrDefault().ToString("#,#0"); var dataRange = helper.workSheet.Range["A" + currentRowIndex, "H" + currentRowIndex]; dataRange.Borders.LineStyle = XlLineStyle.xlContinuous; dataRange.Borders.Color = borderCellColor; dataRange.RowHeight = rowHeight; dataRange.HorizontalAlignment = XlHAlign.xlHAlignLeft; dataRange.VerticalAlignment = XlHAlign.xlHAlignCenter; dataRange.WrapText = true; tripIndex++; currentRowIndex++; } } ExportExcelResult exportResult = helper.SaveAndCloseWorkBook(); if (exportResult.exportSuccess) { result.Message = "/Uploads/Output/Excel/" + exportResult.exportResultFileName; } else { result.Status = false; result.Message = exportResult.exportResultMessage; } } return(Json(result)); }
public JsonResult ExportExcelReportResult(int reportType) { JsonResultBO result = new JsonResultBO(true); string fileName = string.Empty; ReportVanBanDiResultViewModel data = (ReportVanBanDiResultViewModel)SessionManager.GetValue("ReportVanBanDi" + reportType); if (reportType == LOAI_BAOCAO_VANBAN_CONSTANT.BAOCAO_LINHVUC_VANBANDI) { fileName = "Thống kê văn bản đi theo lĩnh vực.xlsx"; } else if (reportType == LOAI_BAOCAO_VANBAN_CONSTANT.BAOCAO_HINHTHUC_VANBANDI) { fileName = "Thống kê văn bản đi theo hình thức.xlsx"; } else { fileName = "Thống kê văn bản đi đã gửi cho đơn vị.xlsx"; } ExportExcelSupplier <SelectListItem> helper = new ExportExcelSupplier <SelectListItem>(); //thư mục chứa file kết quả helper.outputFolderPath = OUTPUT_EXECL_FOLDER_PATH; //đường dẫn file biểu mẫu helper.templateFilePath = Path.Combine(UPLOAD_PATH, TEMPLATE_REPORT_PATH); //tên file muốn kết xuất helper.fileName = fileName; //cột bắt đầu helper.startCell = 1; //dòng bắt đầu helper.startRow = 5; helper.propertyColumns = new List <string>() { "STT", "Text", "Value" }; bool isWorkBookOpen = helper.OpenWorkBook(); if (isWorkBookOpen) { int dataSize = data.groupOfReportResultItems.Count; //điền dữ liệu vào bảng helper.FillTableData(data.groupOfReportResultItems); //điền thông tin đặc thù vào bảng helper.workSheet.Range["A3", "C3"].Value = data.title; helper.workSheet.Range["A3", "C3"].Font.Bold = true; helper.workSheet.Range["A5", "A" + (5 + dataSize - 1)].Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; helper.workSheet.Range["A5", "A" + (5 + dataSize - 1)].Cells.VerticalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; helper.workSheet.Range["A" + (5 + dataSize), "A" + (5 + dataSize)].Value = null; helper.workSheet.Range["A" + (5 + dataSize), "B" + (5 + dataSize)].Merge(Type.Missing); helper.workSheet.Range["A" + (5 + dataSize), "B" + (5 + dataSize)].Value = "Tổng"; helper.workSheet.Range["A" + (5 + dataSize), "B" + (5 + dataSize)].Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; helper.workSheet.Range["A" + (5 + dataSize), "B" + (5 + dataSize)].Cells.VerticalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; helper.workSheet.Range["C" + (5 + dataSize), "C" + (5 + dataSize)].Value = data.groupOfReportResultItems.Select(x => int.Parse(x.Value)).Sum(); var borderRange = helper.workSheet.Range["A1", "C" + (5 + dataSize)]; helper.SetBorderRange(borderRange); var centerRange = helper.workSheet.Range["C5", "C" + (5 + dataSize)]; helper.SetTextCenterRange(centerRange); ExportExcelResult exportResult = helper.SaveAndCloseWorkBook(); if (exportResult.exportSuccess) { result.Message = "/Uploads/Output/Excel/" + exportResult.exportResultFileName; } else { result.Status = false; result.Message = exportResult.exportResultMessage; } } return(Json(result)); }
public ActionResult Export() { JObject jo = new JObject(); var hasData = !_context.Students.Count().Equals(0); if (!hasData) { jo.Add("Msg", hasData.ToString()); return(Content(JsonConvert.SerializeObject(jo), "application/json")); } else { var exportFileName = string.Concat( "Students_", DateTime.Now.ToString("yyyyMMddHHmmss"), ".xlsx"); var exportData = new ExportExcelResult { SheetName = "outputfile", FileName = exportFileName, ExportData = _context.Students.ToList() }; //_exportExcelResult.ExecuteResult(exportData); try { var workbook = new XLWorkbook(); IXLWorksheet worksheet = workbook.Worksheets.Add("outputfile"); //worksheet.Cell(1, 1).Value = "Id"; worksheet.Cell(1, 1).Value = "LastName"; worksheet.Cell(1, 2).Value = "FirstMidName"; worksheet.Cell(1, 3).Value = "EnrollmentDate"; for (int index = 1; index <= exportData.ExportData.Count; index++) { //worksheet.Cell(index + 1, 1).Value = exportData.ExportData[index - 1].ID; worksheet.Cell(index + 1, 1).Value = exportData.ExportData[index - 1].LastName; worksheet.Cell(index + 1, 2).Value = exportData.ExportData[index - 1].FirstMidName; worksheet.Cell(index + 1, 3).Value = exportData.ExportData[index - 1].EnrollmentDate; } var contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; using (var stream = new MemoryStream()) { workbook.SaveAs(stream); var content = stream.ToArray(); return(File(content, contentType, exportData.FileName)); } //workbook.SaveAs(@"C:\Users\v-ivowan\Desktop\" + dt.FileName); } catch (Exception ex) { throw (ex); } //return RedirectToAction(nameof(Index)); } //var exportSpource = this.GetExportData(); //var dt = JsonConvert.DeserializeObject<Student>(exportSpource.ToString()); }