SetSheetHidden() public méthode

public SetSheetHidden ( int sheetIx, SheetState state ) : void
sheetIx int
state SheetState
Résultat void
Exemple #1
0
        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();
        }