/**
* Creates new XSSFSheet - called by XSSFWorkbook to create a sheet from scratch.
*
* @see NPOI.XSSF.usermodel.XSSFWorkbook#CreateSheet()
*/
public XSSFSheet()
: base()
{
dataValidationHelper = new XSSFDataValidationHelper(this);
OnDocumentCreate();
}
/**
* Creates an XSSFSheet representing the given namespace part and relationship.
* Should only be called by XSSFWorkbook when Reading in an exisiting file.
*
* @param part - The namespace part that holds xml data represenring this sheet.
* @param rel - the relationship of the given namespace part in the underlying OPC namespace
*/
internal XSSFSheet(PackagePart part, PackageRelationship rel)
: base(part, rel)
{
dataValidationHelper = new XSSFDataValidationHelper(this);
}
//private BaseForm getCurrentForm()
//{
// BaseForm baseForm = null;
// using (Repositories.BaseFormRepository repository = new Repositories.BaseFormRepository(null))
// {
// baseForm = repository.getInstanceByPeriodDate(DateTime.Now);
// }
// return baseForm;
//}
/// <summary>
/// Traduz de Questionário da Amarribo para arquivo excel (Workbook)
/// </summary>
/// <param name="baseForm">Questionário de transparência</param>
/// <param name="protectSheets">Flag de proteção do questionário</param>
/// <returns></returns>
private XSSFWorkbook translateFormToWorkBook(BaseForm baseForm, bool protectSheets, long rfId = 0)
{
try
{
XSSFWorkbook wb = null;
if (baseForm != null)
{
wb = createWorkBook();
IFont titleFont = wb.CreateFont();
IFont subTitleFont = wb.CreateFont();
IFont titleQuestion = wb.CreateFont();
titleFont.FontHeight = 20;
subTitleFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
subTitleFont.FontHeight = 15;
titleQuestion.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
ICellStyle cellTitleStyle = wb.CreateCellStyle();
//cellTitleStyle.Alignment = HorizontalAlignment.Center;
cellTitleStyle.SetFont(titleFont);
ICellStyle cellSubTitleStyle = wb.CreateCellStyle();
cellSubTitleStyle.SetFont(subTitleFont);
//cellSubTitleStyle.Alignment = HorizontalAlignment.Center;
ICellStyle cellTitleQuestionStyle = wb.CreateCellStyle();
cellTitleQuestionStyle.SetFont(titleQuestion);
ICellStyle cellQuestion = wb.CreateCellStyle();
cellQuestion.WrapText = true;
ICellStyle cellAnswer = wb.CreateCellStyle();
cellAnswer.IsLocked = false;
ICellStyle cellHidden = wb.CreateCellStyle();
cellHidden.IsHidden = true;
if (baseForm.BaseBlocks != null)
{
//Será criada uma sheet única para todas as perguntas
XSSFSheet sheet = null;
//Total rows no atual bloco
int row = 0;
//Titulo da aba tem limite de 30 caracteres.
var sheetName = baseForm.Name.Length > 30 ? baseForm.Name.Substring(0, 30) : baseForm.Name;
sheet = (XSSFSheet)wb.CreateSheet(Commons.StringUtils.removeSpecialCaracters(sheetName));
if (protectSheets)
sheet.ProtectSheet(PROTECT_SHEET_PASSWORD);
if (baseForm.BaseBlocks != null)
{
if (baseForm.BaseBlocks.Count > 0)
{
int count = 1;
foreach (var bb in baseForm.BaseBlocks)
{
if (bb.BaseSubBlocks != null && bb.BaseSubBlocks.Count > 0)
{
sheet.CreateRow(row).CreateCell(0).SetCellValue(bb.Name);
sheet.GetRow(row).GetCell(0).CellStyle = cellTitleStyle;
sheet.GetRow(row).CreateCell(4).SetCellValue("B");
//Merge celula do titulo
sheet.AddMergedRegion(new CellRangeAddress(row, row, 0, 3));
row++;
//Para cada subbloco
foreach (var bsb in bb.BaseSubBlocks)
{
sheet.CreateRow(row).CreateCell(0).SetCellValue(bsb.Name);
sheet.GetRow(row).GetCell(0).CellStyle = cellSubTitleStyle;
sheet.GetRow(row).CreateCell(4).SetCellValue("S");
//Merge celula do titulo
sheet.AddMergedRegion(new CellRangeAddress(row, row, 0, 3));
row++;
//Adiciona os subtitulos
sheet.CreateRow(row).CreateCell(0).SetCellValue("Índice");
sheet.GetRow(row).GetCell(0).CellStyle = cellTitleQuestionStyle;
sheet.GetRow(row).CreateCell(1).SetCellValue("Pergunta");
sheet.GetRow(row).GetCell(1).CellStyle = cellTitleQuestionStyle;
sheet.GetRow(row).CreateCell(2).SetCellValue("Resposta");
sheet.GetRow(row).GetCell(2).CellStyle = cellTitleQuestionStyle;
sheet.GetRow(row).CreateCell(3).SetCellValue("Observações");
sheet.GetRow(row).GetCell(3).CellStyle = cellTitleQuestionStyle;
sheet.GetRow(row).CreateCell(4).SetCellValue("TP");
row++;
if (bsb.BaseQuestions != null && bsb.BaseQuestions.Count > 0)
{
//Utilizado para gerar com respostas
//var rand = new Random(DateTime.Now.Millisecond);
int initRow = row;
foreach (var bq in bsb.BaseQuestions)
{
Entities.Answer answer = null;
if (rfId > 0)
{
answer = bq.Answers.Where(f => f.ResponseFormId == rfId).FirstOrDefault();
}
//Indice da pergunta
sheet.CreateRow(row).CreateCell(0).SetCellValue(count);
//Pergunta
//sheet.GetRow(row).CreateCell(1).SetCellValue(bq.Question);
ICell cellQ = sheet.GetRow(row).CreateCell(1);
cellQ.CellStyle = cellQuestion;
formatStyleQuestion(bq.Question, ref cellQ, ref wb);
//Adiciona o campo com dropdown e libera para edição
//int r = rand.Next(1, 4);
if (answer != null && rfId > 0)
{
if (answer.Score.HasValue)
{
sheet.GetRow(row).CreateCell(2).SetCellValue(answer.Score.Value.ToString());//("");//(actions[r])
}
else
{
sheet.GetRow(row).CreateCell(2).SetCellValue("N/A");//("");//(actions[r])
}
}
else
{
sheet.GetRow(row).CreateCell(2).SetCellValue("");//("");//(actions[r])
}
sheet.GetRow(row).GetCell(2).CellStyle = cellAnswer;
//observações.
sheet.GetRow(row).CreateCell(3).SetCellValue("");
sheet.GetRow(row).GetCell(3).CellStyle = cellAnswer;
//Tipo da linha
sheet.GetRow(row).CreateCell(4).SetCellValue("P");
sheet.GetRow(row).GetCell(4).CellStyle = cellHidden;
//Adiciona o id da pergunta
sheet.GetRow(row).CreateCell(5).SetCellValue(bq.Id);
sheet.GetRow(row).GetCell(5).CellStyle = cellHidden;
//Id do formulario
sheet.GetRow(row).CreateCell(6).SetCellValue(bq.BaseSubBlock.BaseBlock.BaseFormId);
sheet.GetRow(row).GetCell(6).CellStyle = cellHidden;
//Id do responseForm
sheet.GetRow(row).CreateCell(7).SetCellValue(rfId);
sheet.GetRow(row).GetCell(7).CellStyle = cellHidden;
row++;
count++;
}
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
XSSFDataValidationConstraint constraint = (XSSFDataValidationConstraint)dvHelper.CreateExplicitListConstraint(actions);
CellRangeAddressList addressList = new CellRangeAddressList(initRow, row - 1, 2, 2);
XSSFDataValidation validation = (XSSFDataValidation)dvHelper.CreateValidation(constraint, addressList);
validation.ShowErrorBox = true;
validation.CreateErrorBox("Valor inválido", "Valor inserido não é permitido");
sheet.AddValidationData(validation);
}
}
}
}
}
}
sheet.SetColumnWidth(1, 25500);
sheet.SetColumnWidth(3, 25500);
sheet.SetColumnWidth(4, 0); //0 de width para esconder a coluna do id, NPOI não tem solução para hide de coluna
sheet.SetColumnWidth(5, 0); //0 de width para esconder a coluna do id, NPOI não tem solução para hide de coluna
sheet.SetColumnWidth(6, 0); //0 de width para esconder a coluna do id, NPOI não tem solução para hide de coluna
sheet.SetColumnWidth(7, 0); //0 de width para esconder a coluna do id, NPOI não tem solução para hide de coluna
}
}
return wb;
}
catch (Exception ex)
{
Lib.Log.ErrorLog.saveError("Lib.Utils.ExcelUtils.translateFormToWorkBook", ex);
throw ex;
}
}
public static byte[] GenerateTemplate(List<Business.Entities.company> listCompany,List<Business.Entities.contractor> listContractor,List<Business.Entities.project> listProject)
{
//culture
Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); //supaya file tidak corrupt
int parseRecordNumber = 100; // number of rows that has style or validation
int startRowIndex = 3;
XSSFCellStyle styleCurrency;
XSSFCellStyle styleDate;
XSSFCellStyle styleNumeric;
XSSFCellStyle styleDecimal;
//kamus
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet; XSSFRow row; XSSFCell cell;
XSSFCellStyle style; XSSFFont font;
CellRangeAddressList addressList; XSSFDataValidationHelper dvHelper; XSSFDataValidationConstraint dvConstraint; XSSFDataValidation validation;
List<string> listCompanyString = new List<string>();
foreach(var data in listCompany)
{
listCompanyString.Add(data.name);
}
List<string> listContractorString = new List<string>();
foreach(var data in listContractor)
{
listContractorString.Add(data.name);
}
List<string> listProjectString = new List<string>();
foreach(var data in listProject)
{
listProjectString.Add(data.name);
}
styleCurrency = (XSSFCellStyle)workbook.CreateCellStyle();
styleCurrency.DataFormat = workbook.CreateDataFormat().GetFormat("$#,##0.00_);($#,##0.00)");
styleNumeric = (XSSFCellStyle)workbook.CreateCellStyle();
styleNumeric.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0");
styleDate = (XSSFCellStyle)workbook.CreateCellStyle();
styleDate.DataFormat = workbook.CreateDataFormat().GetFormat("mm/dd/yyyy");
styleDecimal = (XSSFCellStyle)workbook.CreateCellStyle();
styleDecimal.DataFormat = workbook.CreateDataFormat().GetFormat("0.00");
List<string> columnList = new List<string>();
columnList.Add("Name");
int ContractorStringLocation = 1;
columnList.Add("Contractor");
columnList.Add("Photo");
columnList.Add("Description");
columnList.Add("Start Date");
columnList.Add("Finish Date");
columnList.Add("Highlight");
columnList.Add("Project Stage");
columnList.Add("Status");
columnList.Add("Budget");
columnList.Add("Currency");
columnList.Add("Num");
int PmcStringLocation = 12;
columnList.Add("Pmc");
columnList.Add("Summary");
int CompanyStringLocation = 14;
columnList.Add("Company");
columnList.Add("Status Non Technical");
columnList.Add("Is Completed");
columnList.Add("Completed Date");
int ProjectStringLocation = 18;
columnList.Add("Project");
columnList.Add("Submit For Approval Time");
columnList.Add("Approval Status");
columnList.Add("Approval Time");
columnList.Add("Deleted");
columnList.Add("Approval Message");
columnList.Add("Status Technical");
columnList.Add("Scurve Data");
sheet = (XSSFSheet)workbook.CreateSheet("Data");
int col = 0;
int rowNumber = 0;
//create row (header)
row = (XSSFRow)sheet.CreateRow((short)rowNumber);
dvHelper = new XSSFDataValidationHelper(sheet);
//header data
style = (XSSFCellStyle)workbook.CreateCellStyle();
cell = (XSSFCell)row.CreateCell(col);
cell.SetCellValue("M Project");
font = (XSSFFont)workbook.CreateFont();
font.FontHeight = 24;
style.SetFont(font);
cell.CellStyle = style;
rowNumber++;
row = (XSSFRow)sheet.CreateRow((short)rowNumber);
style = (XSSFCellStyle)workbook.CreateCellStyle();
font = (XSSFFont)workbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.Bold;
style.SetFont(font);
rowNumber++;
row = (XSSFRow)sheet.CreateRow((short)rowNumber);
//header data
foreach (string data in columnList)
{
cell = (XSSFCell)row.CreateCell(col);
cell.SetCellValue(data);
cell.CellStyle = style;
//cell.CellStyle.IsLocked = true;
//column width
sheet.SetColumnWidth(col, (30 * 256));
++col;
}
//sheet.CreateFreezePane(0, 4);
//dropdownlist Company
if(listCompanyString.Count > 0)
{
XSSFSheet hidden = (XSSFSheet)workbook.CreateSheet("MasterCompany");
int i=0;
foreach(string a in listCompanyString)
{
row = (XSSFRow)hidden.CreateRow(i);
cell = (XSSFCell)row.CreateCell(0);
cell.SetCellValue(a);
i++;
}
validation = null;
dvConstraint = null;
dvHelper = null;
dvHelper=new XSSFDataValidationHelper(sheet);
addressList = new CellRangeAddressList(startRowIndex,parseRecordNumber,CompanyStringLocation,CompanyStringLocation);
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint("MasterCompany!$A$1:$A$" + listCompanyString.Count);
validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.SuppressDropDownArrow = true;
validation.ShowErrorBox = true;
workbook.SetSheetHidden(1, true);
sheet.AddValidationData(validation);
}
//dropdownlist Contractor
if(listContractorString.Count > 0)
{
XSSFSheet hidden = (XSSFSheet)workbook.CreateSheet("MasterContractor");
int i=0;
foreach(string a in listContractorString)
{
row = (XSSFRow)hidden.CreateRow(i);
cell = (XSSFCell)row.CreateCell(0);
cell.SetCellValue(a);
i++;
}
validation = null;
dvConstraint = null;
dvHelper = null;
dvHelper=new XSSFDataValidationHelper(sheet);
addressList = new CellRangeAddressList(startRowIndex,parseRecordNumber,ContractorStringLocation,ContractorStringLocation);
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint("MasterContractor!$A$1:$A$" + listContractorString.Count);
validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.SuppressDropDownArrow = true;
validation.ShowErrorBox = true;
workbook.SetSheetHidden(2, true);
sheet.AddValidationData(validation);
}
//dropdownlist Project
if(listProjectString.Count > 0)
{
XSSFSheet hidden = (XSSFSheet)workbook.CreateSheet("MasterProject");
int i=0;
foreach(string a in listProjectString)
{
row = (XSSFRow)hidden.CreateRow(i);
cell = (XSSFCell)row.CreateCell(0);
cell.SetCellValue(a);
i++;
}
validation = null;
dvConstraint = null;
dvHelper = null;
dvHelper=new XSSFDataValidationHelper(sheet);
addressList = new CellRangeAddressList(startRowIndex,parseRecordNumber,ProjectStringLocation,ProjectStringLocation);
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint("MasterProject!$A$1:$A$" + listProjectString.Count);
validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.SuppressDropDownArrow = true;
validation.ShowErrorBox = true;
workbook.SetSheetHidden(3, true);
sheet.AddValidationData(validation);
}
/*Cell formatting*/
for (int i = startRowIndex; i <= parseRecordNumber; i++)
{
rowNumber++;
row = (XSSFRow)sheet.CreateRow((short)rowNumber);
//start_date
col = 4;
cell = (XSSFCell)row.CreateCell((short)col);
cell.CellStyle = styleDate;
if(i==startRowIndex)
{
addressList = new CellRangeAddressList(startRowIndex, parseRecordNumber, 4, 4);
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateDateConstraint(OperatorType.GREATER_THAN, "01/01/1900", "", "mm/dd/yyyy");
validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.ShowErrorBox = true;
validation.CreateErrorBox("Input Error", "Value must be a date, example 12/30/2000");
sheet.AddValidationData(validation);
}
//finish_date
col = 5;
cell = (XSSFCell)row.CreateCell((short)col);
cell.CellStyle = styleDate;
if(i==startRowIndex)
{
addressList = new CellRangeAddressList(startRowIndex, parseRecordNumber, 5, 5);
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateDateConstraint(OperatorType.GREATER_THAN, "01/01/1900", "", "mm/dd/yyyy");
validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.ShowErrorBox = true;
validation.CreateErrorBox("Input Error", "Value must be a date, example 12/30/2000");
sheet.AddValidationData(validation);
}
//budget
col = 9;
cell = (XSSFCell)row.CreateCell((short)col);
cell.CellStyle = styleDecimal;
if(i==startRowIndex)
{
}
//num
col = 11;
cell = (XSSFCell)row.CreateCell((short)col);
cell.CellStyle = styleNumeric;
if(i==startRowIndex)
{
addressList = new CellRangeAddressList(startRowIndex, parseRecordNumber, 11, 11);
dvHelper = new XSSFDataValidationHelper(sheet);
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateNumericConstraint(ValidationType.INTEGER, OperatorType.BETWEEN, "0", "1000000000000000000");
validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.ShowErrorBox = true;
validation.CreateErrorBox("Input Error", "Value must be a number, maximum 1.000.000.000.000.000.000");
sheet.AddValidationData(validation);
}
//completed_date
col = 17;
cell = (XSSFCell)row.CreateCell((short)col);
cell.CellStyle = styleDate;
if(i==startRowIndex)
{
addressList = new CellRangeAddressList(startRowIndex, parseRecordNumber, 17, 17);
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateDateConstraint(OperatorType.GREATER_THAN, "01/01/1900", "", "mm/dd/yyyy");
validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.ShowErrorBox = true;
validation.CreateErrorBox("Input Error", "Value must be a date, example 12/30/2000");
sheet.AddValidationData(validation);
}
//submit_for_approval_time
col = 19;
cell = (XSSFCell)row.CreateCell((short)col);
cell.CellStyle = styleDate;
if(i==startRowIndex)
{
addressList = new CellRangeAddressList(startRowIndex, parseRecordNumber, 19, 19);
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateDateConstraint(OperatorType.GREATER_THAN, "01/01/1900", "", "mm/dd/yyyy");
validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.ShowErrorBox = true;
validation.CreateErrorBox("Input Error", "Value must be a date, example 12/30/2000");
sheet.AddValidationData(validation);
}
//approval_time
col = 21;
cell = (XSSFCell)row.CreateCell((short)col);
cell.CellStyle = styleDate;
if(i==startRowIndex)
{
addressList = new CellRangeAddressList(startRowIndex, parseRecordNumber, 21, 21);
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateDateConstraint(OperatorType.GREATER_THAN, "01/01/1900", "", "mm/dd/yyyy");
validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.ShowErrorBox = true;
validation.CreateErrorBox("Input Error", "Value must be a date, example 12/30/2000");
sheet.AddValidationData(validation);
}
}
//write to byte[]
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
return ms.ToArray();
}
