protected Dictionary <string, ICellStyle> GetDcCellStyle(IWorkbook workbook)
{
string dateFormat = "yyyy/MM/dd";
ICreationHelper creationHelper = workbook.GetCreationHelper();
ICellStyle cellStyleDate = workbook.CreateCellStyle();
cellStyleDate.DataFormat = creationHelper.CreateDataFormat().GetFormat(dateFormat);
ICellStyle cellStyleText = workbook.CreateCellStyle();
cellStyleText.DataFormat = creationHelper.CreateDataFormat().GetFormat("text");
ICellStyle cellStyleInteger = workbook.CreateCellStyle();
cellStyleInteger.DataFormat = creationHelper.CreateDataFormat().GetFormat("0");
ICellStyle cellStyleDecimal = workbook.CreateCellStyle();
cellStyleDecimal.DataFormat = workbook.CreateDataFormat().GetFormat("0.00");
var fontBold = workbook.CreateFont();
fontBold.FontHeightInPoints = 11;
fontBold.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
ICellStyle cellStyleHeader = workbook.CreateCellStyle();
cellStyleHeader.SetFont(fontBold);
cellStyleHeader.BorderBottom = BorderStyle.Thin;
cellStyleHeader.FillForegroundColor = IndexedColors.Grey25Percent.Index;
cellStyleHeader.FillPattern = FillPattern.SolidForeground;
Dictionary <string, ICellStyle> dcCellStyle = new Dictionary <string, ICellStyle>()
{
{ "header", cellStyleHeader },
{ "text", cellStyleText },
{ "number", cellStyleInteger },
{ "decimal", cellStyleDecimal },
{ "date", cellStyleDate }
};
return(dcCellStyle);
}
/// <summary>
/// 导出xls结尾的Excel数据，每个实体必须含有ExcelColNameAttr
/// </summary>
/// <param name="dataList"></param>
/// <param name="outputStream"></param>
public static void ListToExcelXlsx <T>(List <T>[] dataListArrays, Stream outputStream, string groupName = null)
{
XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
ICellStyle dateCellStyle = xssfWorkbook.CreateCellStyle();
ICreationHelper creationHelper = xssfWorkbook.GetCreationHelper();
dateCellStyle.DataFormat = creationHelper.CreateDataFormat().GetFormat("yyyy-MM-dd hh:mm:ss");
foreach (List <T> dataList in dataListArrays)
{
if (dataList == null || dataList.Count == 0)
{
throw new Exception("数据列表为空，未能导出数据！");
}
List <ExcelColInfo> excelColInfoList = ColFilter(typeof(T), groupName);
if (excelColInfoList.Count == 0)
{
throw new Exception("字段列表为空，不能导出数据！");
}
XSSFSheet xssfSheet = (XSSFSheet)xssfWorkbook.CreateSheet(GetSheetName(typeof(T), groupName));
XSSFRow xssfRow = (XSSFRow)xssfSheet.CreateRow(0);
ExcelCol excelCol;
int width;
int[] colWidths = new int[excelColInfoList.Count];
foreach (ExcelColInfo excelColInfo in excelColInfoList)
{
excelCol = excelColInfo.ExcelCol;
width = SetCellValue(xssfRow.CreateCell(excelCol.ColIndex), excelCol.ColName);
if (width > colWidths[excelCol.ColIndex])
{
colWidths[excelCol.ColIndex] = width;
}
}
object temp;
for (int i = 0, len = dataList.Count; i < len; i++)
{
temp = dataList[i];
xssfRow = (XSSFRow)xssfSheet.CreateRow(i + 1);
foreach (ExcelColInfo excelColInfo in excelColInfoList)
{
excelCol = excelColInfo.ExcelCol;
width = SetCellValue(
(XSSFCell)xssfRow.CreateCell(excelCol.ColIndex),
excelColInfo.PropertyInfo.GetValue(temp),
dateCellStyle
);
if (width > colWidths[excelCol.ColIndex])
{
colWidths[excelCol.ColIndex] = width;
}
}
}
UpdateColWidth(xssfSheet, colWidths);
}
xssfWorkbook.Write(outputStream);
}
/// <summary>
/// Get a specific cell style
/// </summary>
/// <param name="workbook">Workbook</param>
/// <param name="reportStyle">ReportStyleCell</param>
/// <returns>XSSFCellStyle</returns>
private XSSFCellStyle GetStyle(IWorkbook workbook, ReportStyleCell reportStyle)
{
ICreationHelper creationHelper = workbook.GetCreationHelper();
//Set up column style
XSSFCellStyle cellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
if (reportStyle == null)
{
return(cellStyle);
}
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.Boldweight = (short)reportStyle.FontWeight;
//DataFormat
if (!string.IsNullOrEmpty(reportStyle.DataFormat))
{
cellStyle.SetDataFormat(creationHelper.CreateDataFormat().GetFormat(reportStyle.DataFormat));
cellStyle.Alignment = reportStyle.HorizontalAlignment;
}
//BackgroundColor
if (!string.IsNullOrEmpty(reportStyle.BackgroundColor))
{
XSSFColor backgroundColor = new XSSFColor(Color.FromName(reportStyle.BackgroundColor));
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.SetFillForegroundColor(backgroundColor);
}
//Foreground Color
if (!string.IsNullOrEmpty(reportStyle.FontColor))
{
XSSFColor foregroundColor = new XSSFColor(Color.FromName(reportStyle.FontColor));
font.SetColor(foregroundColor);
}
//Wrap Text
cellStyle.SetFont(font);
cellStyle.WrapText = reportStyle.WrapText;
return(cellStyle);
}
public ActionResult RelatorioEntradasFiltro(DateTime txt_data_inicio, DateTime txt_data_fim, int cbx_tipo)
{
//cbx_tipo = 1, significa gerar PDF
//cbx_tipo = 2, significa gerar XLSX
if (cbx_tipo == 1)
{
//Criando o PDF
var relatorioPDF = new ViewAsPdf
{
ViewName = "RelatorioEntradasPDF",
IsGrayScale = false,
FileName = "RelatorioEntradasPDF.pdf",
Model = EntradaESaidaRelatorioDao.GetEntradasRelatorioFiltro(txt_data_inicio, txt_data_fim)
};
return(relatorioPDF);
}
else
{
List <EntradaGraficos> entradas = EntradaESaidaRelatorioDao.GetEntradasRelatorioFiltro(txt_data_inicio, txt_data_fim);
//Criando o XLSX
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet("Relatório Entradas");
int rowNumer = 0;
//---- HEADER
IRow row = sheet.CreateRow(rowNumer);
ICell cellData;
ICell cellTotal;
ICellStyle styleData = workbook.CreateCellStyle();
styleData.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Violet.Index;
styleData.Alignment = HorizontalAlignment.Center;
ICellStyle styleTotal = workbook.CreateCellStyle();
styleTotal.Alignment = HorizontalAlignment.Right;
ICreationHelper createHelper = workbook.GetCreationHelper();
styleTotal.DataFormat = (createHelper.CreateDataFormat().GetFormat("R$ #.##0;-R$ #.##0"));
cellData = row.CreateCell(0);
cellData.SetCellValue("Data");
cellData.CellStyle = styleData;
cellTotal = row.CreateCell(1);
cellTotal.SetCellValue("Total");
cellTotal.CellStyle = styleTotal;
if (entradas.Count > 0)
{
foreach (var item in entradas)
{
rowNumer++;
row = sheet.CreateRow(rowNumer);
cellData = row.CreateCell(0);
cellData.SetCellValue(item.Data.ToString("dd/MM/yyyy"));
cellData.CellStyle = styleData;
cellTotal = row.CreateCell(1);
cellTotal.SetCellValue((double)item.total);
cellTotal.CellStyle = styleTotal;
}
}
else
{
rowNumer++;
row = sheet.CreateRow(rowNumer);
cellData = row.CreateCell(0);
cellData.SetCellValue("SEM DADOS NESSE PERIODO");
cellData.CellStyle = styleData;
cellTotal = row.CreateCell(1);
cellTotal.SetCellValue(0.00);
cellTotal.CellStyle = styleTotal;
}
//Tamanho das colunas
sheet.SetColumnWidth(0, 40 * 256);
sheet.SetColumnWidth(1, 20 * 256);
MemoryStream stream = new MemoryStream();
workbook.Write(stream);
return(File(stream.ToArray(), //The binary data of the XLS file
"application/vnd.ms-excel", //MIME type of Excel files
string.Format("RelatorioEntradas_{0}.xlsx", DateTime.Now.ToString("yyyy-MM-dd_HH-mm-ss")))); //Suggested file name in the "Save as" dialog which will be displayed to the end user
}
}
public AlertMessage ExportTagihToExcel(JDatatableViewModel model)
{
AlertMessage alert = new AlertMessage();
model.Length = -1;
JDatatableResponse response = GetDatatableByQuery(model);
IWorkbook workbook = new XSSFWorkbook();
int rowIndex = 0;
ISheet sheet1 = workbook.CreateSheet("Sheet 1");
string dateFormat = "yyyy/MM/dd";
ICreationHelper creationHelper = workbook.GetCreationHelper();
ICellStyle cellStyleDate = workbook.CreateCellStyle();
cellStyleDate.DataFormat = creationHelper.CreateDataFormat().GetFormat(dateFormat);
ICellStyle cellStyleText = workbook.CreateCellStyle();
cellStyleText.DataFormat = creationHelper.CreateDataFormat().GetFormat("text");
ICellStyle cellStyleNumber = workbook.CreateCellStyle();
cellStyleNumber.DataFormat = creationHelper.CreateDataFormat().GetFormat("0");
var fontBold = workbook.CreateFont();
fontBold.FontHeightInPoints = 11;
fontBold.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
ICellStyle cellStyleHeader = workbook.CreateCellStyle();
cellStyleHeader.SetFont(fontBold);
cellStyleHeader.BorderBottom = BorderStyle.Thin;
cellStyleHeader.FillForegroundColor = IndexedColors.Grey25Percent.Index;
cellStyleHeader.FillPattern = FillPattern.SolidForeground;
IRow row = sheet1.CreateRow(rowIndex);
row = sheet1.CreateRow(rowIndex);
int cellIndex = 0;
ICell cell = null;
#region cell header
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("RayonCode");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("Plant");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("KaCab NIK");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("KaCab Name");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("ASM NIK");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("ASM Name");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("FSS NIK");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("FSS Name");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("SLM NIK");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("SLM Name");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("Collector NIK");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("Collector Name");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("AR Collector NIK");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("AR Collector Name");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("SPV AR Collector NIK");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("SPV AR Collector Name");
cell.CellStyle = cellStyleHeader;
#endregion
rowIndex++;
#region cell data
List <HierTagihViewModel> listData = response.Data as List <HierTagihViewModel>;
if (listData != null)
{
foreach (var item in listData)
{
row = sheet1.CreateRow(rowIndex);
cellIndex = 0;
// RayonCode
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.RayonCode);
cell.CellStyle = cellStyleText;
// Plant
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.PlantCode);
cell.CellStyle = cellStyleNumber;
// NSM NIK
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.NSMNik);
cell.CellStyle = cellStyleNumber;
// NSM Name
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.NSMFullname);
cell.CellStyle = cellStyleText;
// ASM NIK
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.ASMNik);
cell.CellStyle = cellStyleNumber;
// ASM Name
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.ASMFullname);
cell.CellStyle = cellStyleText;
// FSS NIK
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.FSSNik);
cell.CellStyle = cellStyleNumber;
// FSS Name
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.FSSFullname);
cell.CellStyle = cellStyleText;
// SLM NIK
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.SLMNik);
cell.CellStyle = cellStyleNumber;
// SLM Name
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.SLMFullname);
cell.CellStyle = cellStyleText;
// Collector NIK
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.CollectorNik ?? 0);
cell.CellStyle = cellStyleNumber;
// Collector Name
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.CollectorFullname);
cell.CellStyle = cellStyleText;
// AR Collector NIK
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.FakturisNik ?? 0);
cell.CellStyle = cellStyleNumber;
// AR Collector Name
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.FakturisFullname);
cell.CellStyle = cellStyleText;
// SPV AR Collector NIK
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.SPVFakturisNik ?? 0);
cell.CellStyle = cellStyleNumber;
// SPV AR Collector Name
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.SPVFakturisFullname);
cell.CellStyle = cellStyleText;
rowIndex++;
}
}
#endregion
#region auto size column
for (int i = 0; i < 16; i++)
{
sheet1.AutoSizeColumn(i);
}
#endregion
using (var ms = new MemoryStream())
{
workbook.Write(ms);
alert.Data = ms.ToArray();
}
alert.Status = 1;
return(alert);
}
/// <summary>
/// store the data into Excel format, Open Office XML, .xlsx
/// </summary>
private static void DataTable2ExcelWorksheet(DataTable table, IWorkbook AWorkbook, ISheet AWorksheet, bool AWithHashInCaption = true)
{
IRow wsrow = null;
ICell wscell = null;
Int32 rowCounter = 1;
Int16 colCounter = 1;
ICellStyle wsstyle_dateformat = AWorkbook.CreateCellStyle();
ICreationHelper createHelper = AWorkbook.GetCreationHelper();
wsstyle_dateformat.DataFormat = createHelper.CreateDataFormat().GetFormat("dd/mm/yyyy");
// first write the header of the csv file
wsrow = AWorksheet.CreateRow(rowCounter);
foreach (DataColumn col in table.Columns)
{
wscell = wsrow.CreateCell(colCounter);
wscell.SetCellValue((AWithHashInCaption?"#":"") + col.ColumnName);
colCounter++;
}
rowCounter++;
colCounter = 1;
foreach (DataRow row in table.Rows)
{
wsrow = AWorksheet.CreateRow(rowCounter);
foreach (DataColumn col in table.Columns)
{
wscell = wsrow.CreateCell(colCounter);
if (row.IsNull(col) || (row[col] == null))
{
wscell.SetCellValue("");
colCounter++;
continue;
}
object value = row[col];
if (value is DateTime)
{
wscell.SetCellValue((DateTime)value);
wscell.CellStyle = wsstyle_dateformat;
}
else if (value is Int32 || value is Int64 || value is Int16)
{
wscell.SetCellValue(Convert.ToInt64(value));
}
else
{
wscell.SetCellValue(value.ToString());
}
colCounter++;
}
rowCounter++;
colCounter = 1;
}
}
/// <summary>
/// store the data into Excel format, Open Office XML, .xlsx
/// </summary>
private static void Xml2ExcelWorksheet(XmlDocument ADoc, IWorkbook AWorkbook, ISheet AWorksheet, bool AWithHashInCaption = true)
{
Int32 rowCounter = 1;
Int16 colCounter = 1;
IRow wsrow = null;
ICell wscell = null;
ICellStyle wsstyle_dateformat = AWorkbook.CreateCellStyle();
ICreationHelper createHelper = AWorkbook.GetCreationHelper();
wsstyle_dateformat.DataFormat = createHelper.CreateDataFormat().GetFormat("dd/mm/yyyy");
// first write the header of the csv file
List <string> AllAttributes = new List <string>();
List <XmlNode> AllNodes = new List <XmlNode>();
GetAllAttributesAndNodes(ADoc.DocumentElement, ref AllAttributes, ref AllNodes);
wsrow = AWorksheet.CreateRow(rowCounter);
foreach (string attrName in AllAttributes)
{
wscell = wsrow.CreateCell(colCounter);
wscell.SetCellValue((AWithHashInCaption?"#":"") + attrName);
colCounter++;
}
rowCounter++;
colCounter = 1;
foreach (XmlNode node in AllNodes)
{
wsrow = AWorksheet.CreateRow(rowCounter);
foreach (string attrName in AllAttributes)
{
wscell = wsrow.CreateCell(colCounter);
if (attrName == "childOf")
{
wscell.SetCellValue(TXMLParser.GetAttribute(node.ParentNode, "name"));
}
else
{
string value = TXMLParser.GetAttribute(node, attrName);
if (value.StartsWith(eVariantTypes.eDateTime.ToString() + ":"))
{
wscell.SetCellValue(TVariant.DecodeFromString(value).ToDate());
wscell.CellStyle = wsstyle_dateformat;
}
else if (value.StartsWith(eVariantTypes.eInteger.ToString() + ":"))
{
wscell.SetCellValue(TVariant.DecodeFromString(value).ToInt64());
}
else if (value.StartsWith(eVariantTypes.eDecimal.ToString() + ":"))
{
wscell.SetCellValue((double)TVariant.DecodeFromString(value).ToDecimal());
}
else
{
wscell.SetCellValue(value);
}
}
colCounter++;
}
rowCounter++;
colCounter = 1;
}
}
public AlertMessage DownloadTemplate(int bulan, int tahun)
{
AlertMessage alert = new AlertMessage();
IWorkbook workbook = new XSSFWorkbook();
int rowIndex = 0;
ISheet sheet1 = workbook.CreateSheet("Sheet 1");
string dateFormat = "yyyy/MM/dd";
ICreationHelper creationHelper = workbook.GetCreationHelper();
ICellStyle cellStyleDate = workbook.CreateCellStyle();
cellStyleDate.DataFormat = creationHelper.CreateDataFormat().GetFormat(dateFormat);
ICellStyle cellStyleText = workbook.CreateCellStyle();
cellStyleText.DataFormat = creationHelper.CreateDataFormat().GetFormat("text");
ICellStyle cellStyleNumber = workbook.CreateCellStyle();
cellStyleNumber.DataFormat = creationHelper.CreateDataFormat().GetFormat("0");
var fontBold = workbook.CreateFont();
fontBold.FontHeightInPoints = 11;
fontBold.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
ICellStyle cellStyleHeader = workbook.CreateCellStyle();
cellStyleHeader.SetFont(fontBold);
cellStyleHeader.BorderBottom = BorderStyle.Thin;
cellStyleHeader.FillForegroundColor = IndexedColors.Grey25Percent.Index;
cellStyleHeader.FillPattern = FillPattern.SolidForeground;
IRow row = sheet1.CreateRow(rowIndex);
row = sheet1.CreateRow(rowIndex);
int cellIndex = 0;
ICell cell = null;
#region cell header
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("FSS");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("FSS_Name");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("SLM");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("SLM_Name");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("RayonCode");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("AchiGroup");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("Division");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("Material");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("Bulan");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("Tahun");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("Target");
cell.CellStyle = cellStyleHeader;
#endregion
rowIndex++;
#region cell data
List <RHHeader> rhHeader = GetRHHeaderByUserAuth();
string strTemp = "select distinct a.fss, a.fss_name, a.slm, a.slm_name, a.RayonCode, ";
strTemp += " b.achigroup, ";
strTemp += " b.division, ";
strTemp += " b.material ";
strTemp += "from v_rhheader_fullname a ";
strTemp += "join rhteam c on a.RayonCode = c.RayonCode ";
strTemp += " and CAST(GETDATE() as DATE) >= c.ValidFrom and CAST(GETDATE() as DATE) <= c.ValidTo ";
strTemp += " join rdetail b on a.RayonType = b.RayonType ";
strTemp += " and CAST(GETDATE() as DATE) >= b.ValidFrom and CAST(GETDATE() as DATE) <= b.ValidTo ";
strTemp += " where CAST(GETDATE() as DATE) >= a.ValidFrom and CAST(GETDATE() as DATE) <= a.ValidTo ";
string strRayonCode = string.Join("|", rhHeader.Select(x => x.RayonCode).ToArray());
strTemp += " and a.RayonCode in(SELECT value FROM STRING_SPLIT('" + strRayonCode + "', '|'))";
string connString = GetConnectionString();
DataTable dataTable = SqlHelper.ExecuteQuery(connString, strTemp, null);
//DateTime now = DateTime.UtcNow.ToUtcID();
//var rType = rhHeader.Select(r => r.RayonType).Distinct();
//IRepository<RDetail> repo = _unitOfWork.GetRepository<RDetail>();
//repo.Condition = PredicateBuilder.True<RDetail>();
//repo.Condition = repo.Condition.And(x => rType.Contains(x.RayonType));
//repo.Condition = repo.Condition.And(x => x.ValidTo >= now);
//List<RDetail> rDetail = repo.Find().ToList();
foreach (DataRow dr in dataTable.Rows)
{
row = sheet1.CreateRow(rowIndex);
cellIndex = 0;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(dr.IsNull("fss") ? null : dr["fss"].ToString());
cell.CellStyle = cellStyleNumber;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(dr.IsNull("fss_name") ? null : dr["fss_name"].ToString());
cell.CellStyle = cellStyleNumber;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(dr.IsNull("slm") ? null : dr["slm"].ToString());
cell.CellStyle = cellStyleNumber;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(dr.IsNull("slm_name") ? null : dr["slm_name"].ToString());
cell.CellStyle = cellStyleText;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(dr.IsNull("RayonCode") ? null : dr["RayonCode"].ToString());
cell.CellStyle = cellStyleText;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(dr.IsNull("achigroup") ? null : dr["achigroup"].ToString());
cell.CellStyle = cellStyleText;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(dr.IsNull("division") ? null : dr["division"].ToString());
cell.CellStyle = cellStyleText;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(dr.IsNull("material") ? null : dr["material"].ToString());
cell.CellStyle = cellStyleText;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(bulan);
cell.CellStyle = cellStyleNumber;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(tahun);
cell.CellStyle = cellStyleNumber;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(0);
cell.CellStyle = cellStyleNumber;
rowIndex++;
}
#endregion
#region auto size column
for (int i = 0; i < 13; i++)
{
sheet1.AutoSizeColumn(i);
}
#endregion
using (var ms = new MemoryStream())
{
workbook.Write(ms);
alert.Data = ms.ToArray();
}
alert.Status = 1;
return(alert);
}
public AlertMessage ExportTargetSalesToExcel(TargetSalesDatatableViewModel model)
{
AlertMessage alert = new AlertMessage();
model.Length = -1;
JDatatableResponse response = GetDatatable(model);
IWorkbook workbook = new XSSFWorkbook();
int rowIndex = 0;
ISheet sheet1 = workbook.CreateSheet("Sheet 1");
string dateFormat = "yyyy/MM/dd";
ICreationHelper creationHelper = workbook.GetCreationHelper();
ICellStyle cellStyleDate = workbook.CreateCellStyle();
cellStyleDate.DataFormat = creationHelper.CreateDataFormat().GetFormat(dateFormat);
ICellStyle cellStyleText = workbook.CreateCellStyle();
cellStyleText.DataFormat = creationHelper.CreateDataFormat().GetFormat("text");
ICellStyle cellStyleNumber = workbook.CreateCellStyle();
cellStyleNumber.DataFormat = creationHelper.CreateDataFormat().GetFormat("0");
var fontBold = workbook.CreateFont();
fontBold.FontHeightInPoints = 11;
fontBold.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
ICellStyle cellStyleHeader = workbook.CreateCellStyle();
cellStyleHeader.SetFont(fontBold);
cellStyleHeader.BorderBottom = BorderStyle.Thin;
cellStyleHeader.FillForegroundColor = IndexedColors.Grey25Percent.Index;
cellStyleHeader.FillPattern = FillPattern.SolidForeground;
IRow row = sheet1.CreateRow(rowIndex);
row = sheet1.CreateRow(rowIndex);
int cellIndex = 0;
ICell cell = null;
//
#region cell header
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("FSS");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("FSS_Name");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("SLM");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("SLM_Name");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("RayonCode");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("AchiGroup");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("Division");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("Material");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("Bulan");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("Tahun");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("Target");
cell.CellStyle = cellStyleHeader;
#endregion
rowIndex++;
#region cell data
List <TargetSalesViewModel> listData = response.Data as List <TargetSalesViewModel>;
if (listData != null)
{
foreach (var item in listData)
{
row = sheet1.CreateRow(rowIndex);
cellIndex = 0;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.FSS_NIK);
cell.CellStyle = cellStyleNumber;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.FSS_Name);
cell.CellStyle = cellStyleNumber;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.SLM_NIK);
cell.CellStyle = cellStyleNumber;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.SLM_Name);
cell.CellStyle = cellStyleText;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.RayonCode);
cell.CellStyle = cellStyleText;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.AchiGroup);
cell.CellStyle = cellStyleText;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.Division);
cell.CellStyle = cellStyleText;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.Material);
cell.CellStyle = cellStyleText;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.Bulan);
cell.CellStyle = cellStyleNumber;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.Tahun);
cell.CellStyle = cellStyleNumber;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(double.Parse(item.Target));
cell.CellStyle = cellStyleNumber;
rowIndex++;
}
}
#endregion
#region auto size column
for (int i = 0; i < 13; i++)
{
sheet1.AutoSizeColumn(i);
}
#endregion
using (var ms = new MemoryStream())
{
workbook.Write(ms);
alert.Data = ms.ToArray();
}
alert.Status = 1;
return(alert);
}
/// <summary>
/// Create an Excel file from the HTML
/// </summary>
public static XSSFWorkbook HTMLToCalc(HtmlDocument html)
{
XSSFWorkbook xssWorkbook = new XSSFWorkbook();
IRow wsrow = null;
ICell wscell = null;
ISheet worksheet = xssWorkbook.CreateSheet("Data Export");
ICellStyle wsstyle_bold = xssWorkbook.CreateCellStyle();
IFont wsfont = wsstyle_bold.GetFont(xssWorkbook);
wsfont.IsBold = true;
wsstyle_bold.SetFont(wsfont);
ICellStyle wsstyle_dateformat = xssWorkbook.CreateCellStyle();
ICreationHelper createHelper = xssWorkbook.GetCreationHelper();
wsstyle_dateformat.DataFormat = createHelper.CreateDataFormat().GetFormat("dd/mm/yyyy");
// write the column headings
var elements = HTMLTemplateProcessor.SelectNodes(html.DocumentNode, "//div[@id='column_headings']/div");
int colCounter = 1;
int rowCounter = 3;
wsrow = worksheet.CreateRow(rowCounter);
foreach (var element in elements)
{
wscell = wsrow.CreateCell(colCounter);
wscell.SetCellValue(element.InnerText);
wscell.CellStyle = wsstyle_bold;
colCounter++;
}
rowCounter += 2;
var rows = HTMLTemplateProcessor.SelectNodes(html.DocumentNode, "//div[@id='content']//div[contains(@class, 'row')]");
foreach (var row in rows)
{
wsrow = worksheet.CreateRow(rowCounter);
colCounter = 1;
elements = HTMLTemplateProcessor.SelectNodes(row, ".//div[contains(@class, 'col-')]");
foreach (var element in elements)
{
wscell = wsrow.CreateCell(colCounter);
string value = element.InnerText;
if (value == " ")
{
value = String.Empty;
}
if (element.HasClass("currency"))
{
TVariant v = new TVariant(value);
wscell.SetCellValue((double)v.ToDecimal());
}
else if (element.HasClass("date"))
{
TVariant v = new TVariant(value);
wscell.SetCellValue(v.ToDate());
wscell.CellStyle = wsstyle_dateformat;
}
else
{
wscell.SetCellValue(value);
}
if (element.InnerHtml.Contains("<strong>"))
{
wscell.CellStyle = wsstyle_bold;
}
colCounter++;
}
rowCounter++;
}
for (int colIndex = 1; colIndex < colCounter; colIndex++)
{
worksheet.AutoSizeColumn(colIndex);
}
return(xssWorkbook);
}
public MemoryStream Export_Search(JArray criterias, string[] headers1, string[] fields1, string view1,
string[] headers2 = null, string[] fields2 = null, string view2 = null
)
{
string result = "";
List <string> conditions = new List <string>();
//foreach (JObject obj in criterias)
//{
//}
string[] fieldType;
foreach (JObject content in criterias.Children <JObject>())
{
foreach (JProperty prop in content.Properties())
{
fieldType = prop.Name.Split('.');
switch (fieldType[1])
{
case "Period":
DateTime start = DateTime.ParseExact("01/" + prop.Value, "dd/MM/yyyy", CultureInfo.InvariantCulture);
conditions.Add(string.Format("({0} >= '{1}' and {0} < '{2}')", fieldType[0], start.ToString("yyyy-MM-dd"), start.AddMonths(1).ToString("yyyy-MM-dd")));
break;
case "StartDate":
DateTime startDate = DateTime.ParseExact(prop.Value.ToString(), "dd/MM/yyyy HH:mm:ss", CultureInfo.InvariantCulture);
conditions.Add(string.Format("{0} >= '{1}'", fieldType[0], startDate.ToString("yyyy-MM-dd")));
break;
case "EndDate":
DateTime endDate = DateTime.ParseExact(prop.Value.ToString(), "dd/MM/yyyy HH:mm:ss", CultureInfo.InvariantCulture);
conditions.Add(string.Format("{0} <= '{1}'", fieldType[0], endDate.ToString("yyyy-MM-dd")));
break;
case "StartString":
conditions.Add(string.Format("{0} >= '{1}'", fieldType[0], prop.Value));
break;
case "EndString":
conditions.Add(string.Format("{0} <= '{1}'", fieldType[0], prop.Value));
break;
case "Like":
conditions.Add(string.Format("{0} like '%{1}%'", fieldType[0], prop.Value));
break;
default:
conditions.Add(string.Format("{0} = '{1}'", fieldType[0], prop.Value));
break;
}
}
}
// dll refered NPOI.dll and NPOI.OOXML
IWorkbook workbook = new HSSFWorkbook();
int viewCount = -1;
string[] headers, fields;
foreach (var view in new string[] { view1, view2 })
{
if (string.IsNullOrEmpty(view))
{
continue;
}
viewCount++;
headers = new List <string[]> {
headers1, headers2
}[viewCount];
fields = new List <string[]> {
fields1, fields2
}[viewCount];
string query = string.Format(@"select distinct {1} from {0}", view, fields != null ? string.Join(", ", fields) : "*");
if (conditions.Count > 0)
{
query += " where " + string.Join(" and ", conditions);
}
db.Open();
var datas = db.Query <dynamic>(query);
db.Close();
ISheet sheet1 = workbook.CreateSheet(view);
//make a header row
IRow row1 = sheet1.CreateRow(0);
int j = 0; ICell cell;
int row = 0;
IDataFormat dataFormatCustom = workbook.CreateDataFormat();
ICellStyle cellStyle = workbook.CreateCellStyle();
ICreationHelper createHelper = workbook.GetCreationHelper();
cellStyle.DataFormat = createHelper.CreateDataFormat().GetFormat("dd MMM yyyy");
//cell.CellStyle = styles["cell"];
//string[] headers = new string[] { "Worker ID", "Client", "Client Staff No", "Attendance Date", "Total Hours", "TimeIn", "Time Out", "Hour Rate", "Amount", };
//string[] headers = new string[] { "Worker ID", "Payroll Group Desc", "Client", "Client Staff No", "Chinese Name", "Attendance Date", "Hours", "OT Hours", "Time In", "Time Out", "Hour Rate", "OT Hour Rate", "Amount", "Bank Name", "Beneficial Name", "Bank AC No", };
row1 = sheet1.CreateRow(row++);
for (int i = 0; i < headers.Length; i++)
{
cell = row1.CreateCell(i);
cell.SetCellValue(headers[i]);
sheet1.AutoSizeColumn(i);
}
foreach (var data in datas)
{
j = 0;
row1 = sheet1.CreateRow(row++);
foreach (KeyValuePair <string, dynamic> kvp in data)
{
//Console.WriteLine("{0} = {1}", kvp.Key, kvp.Value);
cell = row1.CreateCell(j++);
if (kvp.Value != null)
{
if (kvp.Value is decimal)
{
cell.SetCellValue((double)kvp.Value);
}
else if (kvp.Value is DateTime)
{
cell.CellStyle = cellStyle;
cell.SetCellValue(kvp.Value);
}
else
{
cell.SetCellValue(kvp.Value);
}
}
}
}
}
using (var exportData = new MemoryStream())
{
workbook.Write(exportData);
return(exportData);
}
}