public void ExportToExcel(int page, int size, int store, string department, string user, string enable)
{
// Get the data to report on
var masters = _service.ListCondition(page, size, store, department, user, enable);
// Create a new workbook
var workbook = new HSSFWorkbook();
#region Cell Styles
#region HeaderLabel Cell Style
var headerLabelCellStyle = workbook.CreateCellStyle();
headerLabelCellStyle.Alignment = HorizontalAlignment.CENTER;
headerLabelCellStyle.BorderBottom = CellBorderType.THIN;
var headerLabelFont = workbook.CreateFont();
headerLabelFont.Boldweight = (short)FontBoldWeight.BOLD;
headerLabelCellStyle.SetFont(headerLabelFont);
#endregion
#region RightAligned Cell Style
var rightAlignedCellStyle = workbook.CreateCellStyle();
rightAlignedCellStyle.Alignment = HorizontalAlignment.RIGHT;
#endregion
#region Currency Cell Style
var currencyCellStyle = workbook.CreateCellStyle();
currencyCellStyle.Alignment = HorizontalAlignment.RIGHT;
var formatId = HSSFDataFormat.GetBuiltinFormat("$#,##0.00");
if (formatId == -1)
{
var newDataFormat = workbook.CreateDataFormat();
currencyCellStyle.DataFormat = newDataFormat.GetFormat("$#,##0.00");
}
else
{
currencyCellStyle.DataFormat = formatId;
}
#endregion
#region Detail Subtotal Style
var detailSubtotalCellStyle = workbook.CreateCellStyle();
detailSubtotalCellStyle.BorderTop = CellBorderType.THIN;
detailSubtotalCellStyle.BorderBottom = CellBorderType.THIN;
var detailSubtotalFont = workbook.CreateFont();
detailSubtotalFont.Boldweight = (short)FontBoldWeight.BOLD;
detailSubtotalCellStyle.SetFont(detailSubtotalFont);
#endregion
#region Detail Currency Subtotal Style
var detailCurrencySubtotalCellStyle = workbook.CreateCellStyle();
detailCurrencySubtotalCellStyle.BorderTop = CellBorderType.THIN;
detailCurrencySubtotalCellStyle.BorderBottom = CellBorderType.THIN;
var detailCurrencySubtotalFont = workbook.CreateFont();
detailCurrencySubtotalFont.Boldweight = (short)FontBoldWeight.BOLD;
detailCurrencySubtotalCellStyle.SetFont(detailCurrencySubtotalFont);
formatId = HSSFDataFormat.GetBuiltinFormat("$#,##0.00");
if (formatId == -1)
{
var newDataFormat = workbook.CreateDataFormat();
detailCurrencySubtotalCellStyle.DataFormat = newDataFormat.GetFormat("$#,##0.00");
}
else
{
detailCurrencySubtotalCellStyle.DataFormat = formatId;
}
#endregion
#endregion
#region Master sheet
var sheet = workbook.CreateSheet("User");
// Add header labels
var rowIndex = 0;
// Undestand as row in excel. row + 3 = xuong 3 row.
var row = sheet.CreateRow(rowIndex);
var cell = row.CreateCell(0);
cell.SetCellValue("User Id");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(1);
cell.SetCellValue("User Name");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(2);
cell.SetCellValue("First Name");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(3);
cell.SetCellValue("Last Name");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(4);
cell.SetCellValue("Email");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(5);
cell.SetCellValue("Tel.");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(6);
cell.SetCellValue("Mobile");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(7);
cell.SetCellValue("Department");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(8);
cell.SetCellValue("Right User");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(9);
cell.SetCellValue("Right Store");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(10);
cell.SetCellValue("Right Project");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(11);
cell.SetCellValue("Right Stock");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(12);
cell.SetCellValue("Right Stock Out");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(13);
cell.SetCellValue("Right Stock In");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(14);
cell.SetCellValue("Right Stock Return");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(15);
cell.SetCellValue("Right Stock-Reactive");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(16);
cell.SetCellValue("Right Service");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(17);
cell.SetCellValue("Right Requisition");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(18);
cell.SetCellValue("Right PE");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(19);
cell.SetCellValue("Right Accounting");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(20);
cell.SetCellValue("Right Supplier");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(21);
cell.SetCellValue("Right Price");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(22);
cell.SetCellValue("Created Date");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(23);
cell.SetCellValue("Created By");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(24);
cell.SetCellValue("Modified Date");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(25);
cell.SetCellValue("Modified By");
cell.CellStyle = headerLabelCellStyle;
rowIndex++;
// Add data rows
foreach (var master in masters)
{
row = sheet.CreateRow(rowIndex);
row.CreateCell(0).SetCellValue(master.Id.ToString());
row.CreateCell(1).SetCellValue(master.UserName);
row.CreateCell(2).SetCellValue(master.FirstName);
row.CreateCell(3).SetCellValue(master.LastName);
row.CreateCell(4).SetCellValue(master.Email);
row.CreateCell(5).SetCellValue(master.Telephone);
row.CreateCell(6).SetCellValue(master.Mobile);
row.CreateCell(7).SetCellValue(master.Department);
row.CreateCell(8).SetCellValue(Constants.Action(master.UserR));
row.CreateCell(9).SetCellValue(Constants.Action(master.StoreR));
row.CreateCell(10).SetCellValue(Constants.Action(master.ProjectR));
row.CreateCell(11).SetCellValue(Constants.Action(master.StockR));
row.CreateCell(12).SetCellValue(Constants.Action(master.StockOutR));
row.CreateCell(13).SetCellValue(Constants.Action(master.StockInR));
row.CreateCell(14).SetCellValue(Constants.Action(master.StockReturnR));
row.CreateCell(15).SetCellValue(Constants.Action(master.ReActiveStockR));
row.CreateCell(16).SetCellValue(Constants.Action(master.StockServiceR));
row.CreateCell(17).SetCellValue(Constants.Action(master.RequisitionR));
row.CreateCell(18).SetCellValue(Constants.Action(master.PER));
row.CreateCell(19).SetCellValue(Constants.Action(master.AccountingR));
row.CreateCell(20).SetCellValue(Constants.Action(master.SupplierR));
row.CreateCell(21).SetCellValue(Constants.Action(master.PriceR));
row.CreateCell(22).SetCellValue(master.Created != null
? master.Created.Value.ToString("dd/MM/yyyy")
: master.Created.ToString());
row.CreateCell(23).SetCellValue(master.CreatedBy.ToString());
row.CreateCell(24).SetCellValue(master.Modified != null
? master.Modified.Value.ToString("dd/MM/yyyy")
: master.Modified.ToString());
row.CreateCell(25).SetCellValue(master.ModifiedBy.ToString());
rowIndex++;
}
// Auto-size each column
for (var i = 0; i < sheet.GetRow(0).LastCellNum; i++)
{
sheet.AutoSizeColumn(i);
// Bump up with auto-sized column width to account for bold headers
sheet.SetColumnWidth(i, sheet.GetColumnWidth(i) + 1024);
}
// Add row indicating date/time report was generated...
sheet.CreateRow(rowIndex + 1).CreateCell(0).SetCellValue("Report generated on " + DateTime.Now.ToString("dd/MM/yyyy"));
#endregion
// Save the Excel spreadsheet to a MemoryStream and return it to the client
using (var exportData = new MemoryStream())
{
workbook.Write(exportData);
var saveAsFileName = string.Format("User-{0}.xls", DateTime.Now.ToString("ddMMyyyyHHmmss")).Replace("/", "-");
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
Response.Clear();
Response.BinaryWrite(exportData.GetBuffer());
Response.End();
}
}
