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());
}
