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