예제 #1
0
        public void Test53965()
        {
            XSSFWorkbook wb = new XSSFWorkbook();

            try
            {
                XSSFSheet sheet            = wb.CreateSheet() as XSSFSheet;
                List <IDataValidation> lst = sheet.GetDataValidations();    //<-- works
                Assert.AreEqual(0, lst.Count);

                //create the cell that will have the validation applied
                sheet.CreateRow(0).CreateCell(0);

                IDataValidationHelper     dataValidationHelper = sheet.GetDataValidationHelper();
                IDataValidationConstraint constraint           = dataValidationHelper.CreateCustomConstraint("SUM($A$1:$A$1) <= 3500");
                CellRangeAddressList      addressList          = new CellRangeAddressList(0, 0, 0, 0);
                IDataValidation           validation           = dataValidationHelper.CreateValidation(constraint, addressList);
                sheet.AddValidationData(validation);

                // this line caused XmlValueOutOfRangeException , see Bugzilla 3965
                lst = sheet.GetDataValidations();
                Assert.AreEqual(1, lst.Count);
            }
            finally
            {
                wb.Close();
            }
        }
예제 #2
0
        private static void SetCodeColumn(XSSFSheet sheet, string columnLetter, int colindex, int elements, string sheetName, bool createCodeColumns)
        {
            if (createCodeColumns)
            {
                var row0         = sheet.GetRow(0);
                var lastColIndex = row0.LastCellNum;
                var headerCell   = row0.CreateCell(lastColIndex, CellType.String);
                headerCell.SetCellValue(sheetName + " Code");
                row0.Cells.Add(headerCell);
                headerCell.CellStyle = sheet.Workbook?.GetSheetAt(0)?.GetRow(0)?.GetCell(0)?.CellStyle;

                for (var i = 1; i <= elements; i++)
                {
                    var row     = sheet.GetRow(i) ?? sheet.CreateRow(i);
                    var cell    = row.CreateCell(lastColIndex, CellType.Formula);
                    var formula = $"INDEX('{sheetName}'!A1:D{elements},MATCH(${columnLetter}{i + 1},'{sheetName}'!D1:D{elements},0),1)";
                    cell.SetCellFormula(formula);
                }
                sheet.SetColumnHidden(row0.LastCellNum - 1, true);
            }

            var validationHelper = new XSSFDataValidationHelper(sheet);
            var addressList      = new CellRangeAddressList(0, elements - 1, colindex - 1, colindex - 1);
            var constraint       = validationHelper.CreateFormulaListConstraint($"'{sheetName}'!$D$2:$D$" + elements);
            var dataValidation   = validationHelper.CreateValidation(constraint, addressList);

            sheet.AddValidationData(dataValidation);
        }
예제 #3
0
        public void TestCreateMultipleRegionsValidation()
        {
            XSSFWorkbook wb = new XSSFWorkbook();

            try
            {
                XSSFSheet                 sheet = wb.CreateSheet() as XSSFSheet;
                IDataValidationHelper     dataValidationHelper = sheet.GetDataValidationHelper();
                IDataValidationConstraint constraint           = dataValidationHelper.CreateExplicitListConstraint(new string[] { "A" });
                CellRangeAddressList      cellRangeAddressList = new CellRangeAddressList();
                cellRangeAddressList.AddCellRangeAddress(0, 0, 0, 0);
                cellRangeAddressList.AddCellRangeAddress(0, 1, 0, 1);
                cellRangeAddressList.AddCellRangeAddress(0, 2, 0, 2);
                XSSFDataValidation dataValidation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList) as XSSFDataValidation;
                sheet.AddValidationData(dataValidation);

                Assert.AreEqual(new CellRangeAddress(0, 0, 0, 0), sheet.GetDataValidations()[0].Regions.CellRangeAddresses[0]);
                Assert.AreEqual(new CellRangeAddress(0, 0, 1, 1), sheet.GetDataValidations()[0].Regions.CellRangeAddresses[1]);
                Assert.AreEqual(new CellRangeAddress(0, 0, 2, 2), sheet.GetDataValidations()[0].Regions.CellRangeAddresses[2]);
                Assert.AreEqual("A1 B1 C1", dataValidation.GetCTDataValidation().sqref);
            }
            finally
            {
                wb.Close();
            }
        }
예제 #4
0
        /// <summary>
        /// EXCEL2007下拉值
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="data"></param>
        /// <param name="StarCol"></param>
        /// <param name="EndCol"></param>
        private static void CreateDropDwonListForXLSX(XSSFSheet sheet, List <string> data, int StarCol, int EndCol)
        {
            XSSFDataValidationHelper     Validation  = new XSSFDataValidationHelper(sheet);
            XSSFDataValidationConstraint Constraint  = (XSSFDataValidationConstraint)Validation.CreateExplicitListConstraint(data.ToArray());
            CellRangeAddressList         AddressList = new CellRangeAddressList(1, 65535, StarCol, EndCol);
            var XSSF = Validation.CreateValidation(Constraint, AddressList);

            sheet.AddValidationData(XSSF);
        }
예제 #5
0
        public void CreateSheet <T>(IEnumerable <T> data, XSSFSheet sheet)
        {
            //CreateHeader
            var header = sheet.CreateRow(0);

            header.CreateCell(0);
            PropertyDescriptorCollection props = TypeDescriptor.GetProperties(typeof(T));
            int i = 0, j = 0;

            foreach (PropertyDescriptor prop in props)
            {
                var cell = header.CreateCell(i);
                cell.SetCellValue(prop.Name);
                i++;
            }


            //CreateRows
            i = 1;
            foreach (T item in data)
            {
                var row = sheet.CreateRow(i);
                j = 0;
                foreach (PropertyDescriptor prop in props)
                {
                    if (prop.Converter.ConvertToString(prop.GetValue(item)).Contains("$"))
                    {
                        XSSFDataValidationHelper     dvHelper    = new XSSFDataValidationHelper(sheet);
                        XSSFDataValidationConstraint constraint  = (XSSFDataValidationConstraint)dvHelper.CreateExplicitListConstraint(DropDownList);
                        CellRangeAddressList         addressList = new CellRangeAddressList(i, i, j, j);
                        XSSFDataValidation           validation  = (XSSFDataValidation)dvHelper.CreateValidation(constraint, addressList);
                        sheet.AddValidationData(validation);
                        // row.CreateCell(j).SetCellValue(prop.Converter.ConvertToString(prop.GetValue(item)));
                    }
                    else
                    {
                        row.CreateCell(j).SetCellValue(prop.Converter.ConvertToString(prop.GetValue(item)));
                    }
                    j++;
                }

                i++;
            }
        }
예제 #6
0
        public void TestDefaultAllowBlank()
        {
            XSSFWorkbook wb = new XSSFWorkbook();

            try
            {
                XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;

                XSSFDataValidation validation = CreateValidation(sheet);
                sheet.AddValidationData(validation);

                List <IDataValidation> dataValidations = sheet.GetDataValidations();
                Assert.AreEqual(true, (dataValidations[0] as XSSFDataValidation).GetCTDataValidation().allowBlank);
            }
            finally
            {
                wb.Close();
            }
        }
예제 #7
0
 public void AddValidationData(IDataValidation dataValidation)
 {
     _sh.AddValidationData(dataValidation);
 }
예제 #8
0
        /// <summary>
        /// 导出期初模板
        /// </summary>
        /// <param name="dtSource"></param>
        /// <param name="exportTemplateFilePath"></param>
        /// <param name="fillRow"></param>
        /// <param name="replaceCells"></param>
        /// <returns></returns>
        private static MemoryStream ExportOpeningTemplate(DataTable dtSource, string[] dropDowndtSource, string exportTemplateFilePath, int fillRow, int dropDownFillStartCell, int dropDownFillEndCell)
        {
            try
            {
                //打开Excle模板文件
                IWorkbook workbook = null;
                using (FileStream fileOne = new FileStream(exportTemplateFilePath, FileMode.Open, FileAccess.Read))
                {
                    workbook = new XSSFWorkbook(fileOne);            //获取第一个工作表
                }
                XSSFSheet sheet = (XSSFSheet)workbook.GetSheetAt(0); //获取第一个sheet
                XSSFDataValidationHelper     dvHelper     = new XSSFDataValidationHelper(sheet);
                XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)dvHelper
                                                            .CreateExplicitListConstraint(dropDowndtSource);
                CellRangeAddressList addressList = new CellRangeAddressList(1, dtSource.Rows.Count, dropDownFillStartCell, dropDownFillEndCell);
                XSSFDataValidation   validation  = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
                sheet.AddValidationData(validation);



                //格式日期
                XSSFCellStyle  dateStyle = workbook.CreateCellStyle() as XSSFCellStyle;
                XSSFDataFormat format    = workbook.CreateDataFormat() as XSSFDataFormat;
                dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
                //格式数字
                XSSFCellStyle  decimelStyle  = workbook.CreateCellStyle() as XSSFCellStyle;
                XSSFDataFormat decimelformat = workbook.CreateDataFormat() as XSSFDataFormat;
                decimelStyle.DataFormat = decimelformat.GetFormat("0.00####");
                //单元格样式
                ICellStyle style = workbook.CreateCellStyle();
                //style.BorderBottom = BorderStyle.Thin;
                //style.BorderLeft = BorderStyle.Thin;
                //style.BorderRight = BorderStyle.Thin;
                //style.BorderTop = BorderStyle.Thin;

                int rowIndex = fillRow;

                foreach (DataRow row in dtSource.Rows)
                {
                    #region 填充内容
                    //sheet.ShiftRows(rowIndex, sheet.LastRowNum, 1, true, false);
                    XSSFRow dataRow = sheet.CreateRow(rowIndex) as XSSFRow;
                    foreach (DataColumn column in dtSource.Columns)
                    {
                        XSSFCell newCell = dataRow.CreateCell(column.Ordinal) as XSSFCell;
                        string   drValue = row[column].ToString();
                        switch (column.DataType.ToString())
                        {
                        case "System.String":    //字符串类型
                            newCell.SetCellValue(drValue);
                            break;

                        case "System.DateTime":    //日期类型
                            if (drValue.Length > 0)
                            {
                                DateTime dateV;
                                DateTime.TryParse(drValue, out dateV);
                                newCell.SetCellValue(dateV);

                                newCell.CellStyle = dateStyle;    //格式化显示
                            }
                            break;

                        case "System.Boolean":    //布尔型
                            bool boolV = false;
                            bool.TryParse(drValue, out boolV);
                            newCell.SetCellValue(boolV);
                            break;

                        case "System.Int16":    //整型
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            int intV = 0;
                            int.TryParse(drValue, out intV);
                            newCell.SetCellValue(intV);
                            break;

                        case "System.Decimal":    //浮点型
                        case "System.Double":
                            Double doubV = 0;
                            Double.TryParse(drValue, out doubV);
                            newCell.SetCellValue(doubV);
                            newCell.CellStyle = decimelStyle;
                            break;

                        case "System.DBNull":    //空值处理
                            newCell.SetCellValue("");
                            break;

                        default:
                            newCell.SetCellValue("");
                            break;
                        }


                        newCell.CellStyle = style;
                    }

                    #endregion 填充内容

                    rowIndex++;
                }

                NpoiMemoryStream ms = new NpoiMemoryStream();
                ms.AllowClose = false;
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                ms.Seek(0, SeekOrigin.Begin);
                ms.AllowClose = true;
                return(ms);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }