Esempio n. 1
0
        public XSSFDialogsheet(XSSFSheet sheet)
            : base(sheet.GetPackagePart(), sheet.GetPackageRelationship())
        {

            this.dialogsheet = new CT_Dialogsheet();
            this.worksheet = new CT_Worksheet();
        }
Esempio n. 2
0
            public Excel(string path)
            {
                this._Path = path;

                using (FileStream fs = new FileStream(this._Path, FileMode.Open, FileAccess.Read))
                {
                    this._Workbook = WorkbookFactory.Create(fs) as XSSFWorkbook;
                }

                this._Sheet = this._Workbook.GetSheetAt(0) as XSSFSheet;
            }
            public WorkerRoleExcel(string path)
            {
                this._Path = path;

                this._RowIndexResume = 2;
                this._RowIndex = 0;
                this._WriteRowIndex = 6;

                using (FileStream fs = new FileStream(this._Path, FileMode.Open, FileAccess.Read))
                {
                    this._Workbook = WorkbookFactory.Create(fs) as XSSFWorkbook;
                }

                this._Sheet = this._Workbook.GetSheetAt(0) as XSSFSheet;
            }
Esempio n. 4
0
    public void TestShouldReadWorkbookProtection(){
		Assert.IsFalse(sheet.IsAutoFilterLocked());
		Assert.IsFalse(sheet.IsDeleteColumnsLocked());
		Assert.IsFalse(sheet.IsDeleteRowsLocked());
		Assert.IsFalse(sheet.IsFormatCellsLocked());
		Assert.IsFalse(sheet.IsFormatColumnsLocked());
		Assert.IsFalse(sheet.IsFormatRowsLocked());
		Assert.IsFalse(sheet.IsInsertColumnsLocked());
		Assert.IsFalse(sheet.IsInsertHyperlinksLocked());
		Assert.IsFalse(sheet.IsInsertRowsLocked());
		Assert.IsFalse(sheet.IsPivotTablesLocked());
		Assert.IsFalse(sheet.IsSortLocked());
		Assert.IsFalse(sheet.IsObjectsLocked());
		Assert.IsFalse(sheet.IsScenariosLocked());
		Assert.IsFalse(sheet.IsSelectLockedCellsLocked());
		Assert.IsFalse(sheet.IsSelectUnlockedCellsLocked());
		Assert.IsFalse(sheet.IsSheetLocked());

		sheet = XSSFTestDataSamples.OpenSampleWorkbook("sheetProtection_allLocked.xlsx").GetSheetAt(0);

		Assert.IsTrue(sheet.IsAutoFilterLocked());
		Assert.IsTrue(sheet.IsDeleteColumnsLocked());
		Assert.IsTrue(sheet.IsDeleteRowsLocked());
		Assert.IsTrue(sheet.IsFormatCellsLocked());
		Assert.IsTrue(sheet.IsFormatColumnsLocked());
		Assert.IsTrue(sheet.IsFormatRowsLocked());
		Assert.IsTrue(sheet.IsInsertColumnsLocked());
		Assert.IsTrue(sheet.IsInsertHyperlinksLocked());
		Assert.IsTrue(sheet.IsInsertRowsLocked());
		Assert.IsTrue(sheet.IsPivotTablesLocked());
		Assert.IsTrue(sheet.IsSortLocked());
		Assert.IsTrue(sheet.IsObjectsLocked());
		Assert.IsTrue(sheet.IsScenariosLocked());
		Assert.IsTrue(sheet.IsSelectLockedCellsLocked());
		Assert.IsTrue(sheet.IsSelectUnlockedCellsLocked());
		Assert.IsTrue(sheet.IsSheetLocked());
	}
 /* namespace */
 internal XSSFSheetConditionalFormatting(XSSFSheet sheet)
 {
     _sheet = sheet;
 }
            public void Save()
            {
                using (FileStream fs = File.Create(this._Path))
                {
                    this._Workbook.Write(fs);
                }

                this._Workbook = null;
                this._Sheet = null;
            }
Esempio n. 7
0
 public XSSFAutoFilter(XSSFSheet sheet)
 {
     _sheet = sheet;
 }
Esempio n. 8
0
 public void SetParentSheet(XSSFSheet parentSheet)
 {
     this.parentSheet = parentSheet;
 }
 /*package*/
 internal XSSFConditionalFormatting(XSSFSheet sh)
 {
     _cf = new CT_ConditionalFormatting();
     _sh = sh;
 }
Esempio n. 10
0
 /*package*/
 internal XSSFConditionalFormattingRule(XSSFSheet sh)
 {
     _cfRule = new CT_CfRule();
     _sh = sh;
 }
Esempio n. 11
0
        /// <summary>
        /// 將Excel檔案轉成資料表陣列
        /// </summary>
        /// <param name="strFilePath">檔案路徑</param>
        /// <returns>DataTable Array</returns>
        public static DataTable[] ExcelToDataTable(string strFilePath)
        {
            DataTable[] result;
            IWorkbook wb;
            ISheet hst;
            IRow HeaderRow;
            IRow row;
            using (FileStream fs = new FileStream(strFilePath, FileMode.Open))
            {
                if (strFilePath.Contains(".xlsx"))
                {
                    wb = new XSSFWorkbook(fs);
                    hst = new XSSFSheet();
                }
                else
                {
                    wb = new HSSFWorkbook(fs);
                    hst = new HSSFSheet((HSSFWorkbook)wb);
                }
                result = new DataTable[wb.NumberOfSheets];
                try
                {
                    PGB pgb = new PGB();
                    pgb.Text = "滙入資料";
                    pgb.label1.Text = "滙入資料中,請稍候.....";
                    pgb.Show();
                    Application.DoEvents();
                    for (int i = 0; i < wb.NumberOfSheets; i++)
                    {
                        hst = wb.GetSheetAt(i);
                        HeaderRow = hst.GetRow(0);
                        result[i] = new DataTable();
                        pgb.progressBar1.Minimum = 0;
                        pgb.progressBar1.Value = 0;
                        if (HeaderRow == null & i == wb.NumberOfSheets)
                        {
                            break;
                        }
                        else if (HeaderRow == null & i < wb.NumberOfSheets)
                        {
                            goto Next;
                        }
                        pgb.progressBar1.Maximum = HeaderRow.LastCellNum + hst.LastRowNum;
                        pgb.progressBar1.Step = 1;
                        //寫入欄位標題
                        for (int h = HeaderRow.FirstCellNum; h < HeaderRow.LastCellNum; h++)
                        {
                            if (HeaderRow.GetCell(h) != null)
                            {
                                result[i].Columns.Add(new DataColumn(HeaderRow.GetCell(h).StringCellValue));
                            }
                            else
                            {
                                result[i].Columns.Add(new DataColumn(""));
                            }
                            pgb.progressBar1.Value++;
                        }

                        //寫入欄位資料
                        for (int j = (hst.FirstRowNum + 1); j <= hst.LastRowNum; j++)
                        {
                            row = hst.GetRow(j);
                            if (row == null) continue;
                            DataRow dtRow = result[i].NewRow();
                            for (int x = row.FirstCellNum; x < row.LastCellNum; x++)
                            {
                                if (row.GetCell(x) != null)
                                {
                                    //由NPOI內提供的類別庫判斷公式類型
                                    IFormulaEvaluator iFormula = WorkbookFactory.CreateFormulaEvaluator(wb);
                                    var formulaType = iFormula.Evaluate(row.GetCell(x)).CellType;

                                    //若判斷結果類型為數值(注意:日期結果會被轉為數值)
                                    if (formulaType == CellType.Numeric)
                                    {
                                        ICell cell = iFormula.EvaluateInCell(row.GetCell(x));

                                        // 判斷結果類型是否為日期
                                        if (DateUtil.IsCellDateFormatted(cell))
                                        {
                                            //設定日期格式
                                            //ICellStyle cellStyle = wb.CreateCellStyle();
                                            //IDataFormat format = wb.CreateDataFormat();
                                            //cellStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
                                            //row.GetCell(x).CellStyle = cellStyle;
                                            //dtRow[x] = row.GetCell(x).ToString();
                                            dtRow[x] = row.GetCell(x).DateCellValue.ToString("yyyy-MM-dd");
                                        }
                                        else
                                        {
                                            dtRow[x] = row.GetCell(x).NumericCellValue;
                                        }
                                    }

                                    //判斷結果類型是否為字串
                                    else if (formulaType == CellType.String)
                                    {
                                        dtRow[x] = row.GetCell(x).StringCellValue;
                                    }
                                    else
                                    {
                                        dtRow[x] = row.GetCell(x).ToString();
                                    }
                                }
                            }
                            result[i].Rows.Add(dtRow);
                            pgb.progressBar1.Value++;
                        }
                        Next:;
                    }
                    pgb.Dispose();
                }
                catch (Exception ex)
                {
                    sysMessage.SystemEx(ex.Message);
                }
                return result;
            }
        }
Esempio n. 12
0
 public void Dispose()
 {
     this._Path = null;
     this._Workbook = null;
     this._Sheet = null;
 }
Esempio n. 13
0
 private void validateCells(XSSFSheet sheet)
 {
     foreach (IRow row in sheet)
     {
         // trigger handling
         ((XSSFRow)row).OnDocumentWrite();
     }
 }
Esempio n. 14
0
        public XSSFDataValidationHelper(XSSFSheet xssfSheet)
            : base()
        {

            this.xssfSheet = xssfSheet;
        }
Esempio n. 15
0
        public ActionResult DownloadFile(IEnumerable<long> idList)
        {
            try
            {
                wb = new XSSFWorkbook();
                sh = (XSSFSheet)wb.CreateSheet("Sheet1");
                List<CARDCHEREUISITION> list = new List<CARDCHEREUISITION>();
                var row = sh.CreateRow(0);

                row.CreateCell(0).SetCellValue("Bank");

                row.CreateCell(1).SetCellValue("Card No");

                row.CreateCell(2).SetCellValue("No of Books/Leaves");

                row.CreateCell(3).SetCellValue("Delivery Brn/Channel");

                row.CreateCell(4).SetCellValue("RoutingNo?");

                row.CreateCell(5).SetCellValue("Transaction Code");
                foreach (var id in idList)
                {
                    var aItem = db.CARDCHEREUISITION.FirstOrDefault(x => x.ID==id);
                    list.Add(aItem);
                }
               // var list = db.CARDCHEREUISITION.Where(x => x.STATUS == 7).ToList();

                foreach (var card in list.Select((value, index) => new { value, index }))
                {
                    row = sh.CreateRow(card.index +1);

                    row.CreateCell(0).SetCellValue("OBL");
                    row.CreateCell(1).SetCellValue(card.value.CARDNO);
                    row.CreateCell(2).SetCellValue(card.value.LEAFNO);
                    row.CreateCell(3).SetCellValue(card.value.BRANCHINFO.BRANCHNAME);
                    row.CreateCell(4).SetCellValue("Test Routing");
                    row.CreateCell(5).SetCellValue("Test Transaction");
                }
                //using (var fs = new FileStream(@"~/test.xls", FileMode.Create, FileAccess.Write))
                //{
                //    wb.Write(fs);
                //}
                using (var exportData = new MemoryStream())
                {

                    wb.Write(exportData);
                    exportData.Close();

                    var buffer = exportData.GetBuffer();
                    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    Response.BinaryWrite(buffer);

                    Response.End();

                    //string saveAsFileName = string.Format("Cheque_Requisition{0:d}.xls", DateTime.Now).Replace("/", "-");
                    //Response.Clear();
                    ////Response.ContentType = "application/octet-stream";
                    //Response.ContentType = "application/vnd.ms-excel";
                    //Response.Cache.SetCacheability(HttpCacheability.NoCache);
                    //Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
                    //Response.BinaryWrite(exportData.GetBuffer());
                    //Response.End();

                }

                return RedirectToAction("Index");

            }
            catch (Exception exception)
            {

                return RedirectToAction("Error", "Home", new { Area = "" });
            }
        }
Esempio n. 16
0
        /// <summary>
        /// XSSFSheet获取指定区域包含图片的信息列表
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="minRow"></param>
        /// <param name="maxRow"></param>
        /// <param name="minCol"></param>
        /// <param name="maxCol"></param>
        /// <param name="onlyInternal"></param>
        /// <returns></returns>
        private static List<PictureInfo> GetAllPictureInfos(XSSFSheet sheet, int? minRow, int? maxRow, int? minCol, int? maxCol, bool onlyInternal)
        {
            List<PictureInfo> picturesInfoList = new List<PictureInfo>();

            var documentPartList = sheet.GetRelations();
            foreach (var documentPart in documentPartList)
            {
                if (documentPart is XSSFDrawing)
                {
                    var drawing = (XSSFDrawing)documentPart;
                    var shapeList = drawing.GetShapes();
                    foreach (var shape in shapeList)
                    {
                        if (shape is XSSFPicture)
                        {
                            var picture = (XSSFPicture)shape;
                            var anchor = picture.GetPreferredSize();

                            if (IsInternalOrIntersect(minRow, maxRow, minCol, maxCol, anchor.Row1, anchor.Row2, anchor.Col1, anchor.Col2, onlyInternal))
                            {
                                var picStyle = new PictureStyle
                                {
                                    AnchorDx1 = anchor.Dx1,
                                    AnchorDx2 = anchor.Dx2,
                                    AnchorDy1 = anchor.Dy1,
                                    AnchorDy2 = anchor.Dy2,
                                    IsNoFill = picture.IsNoFill,
                                    LineStyle = picture.LineStyle,
                                    LineStyleColor = picture.LineStyleColor,
                                    LineWidth = picture.LineWidth,
                                    FillColor = picture.FillColor
                                };
                                picturesInfoList.Add(new PictureInfo(anchor.Row1, anchor.Row2, anchor.Col1, anchor.Col2, picture.PictureData.Data, picStyle));
                            }
                        }
                    }
                }
            }

            return picturesInfoList;
        }
Esempio n. 17
0
        /// <summary>
        /// XSSFSheet清除指定区域的图片
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="minRow"></param>
        /// <param name="maxRow"></param>
        /// <param name="minCol"></param>
        /// <param name="maxCol"></param>
        /// <param name="onlyInternal"></param>
        private static void RemovePictures(XSSFSheet sheet, int? minRow, int? maxRow, int? minCol, int? maxCol, bool onlyInternal)
        {
            var documentPartList = sheet.GetRelations();
            foreach (var documentPart in documentPartList)
            {
                if (documentPart is XSSFDrawing)
                {
                    var drawing = (XSSFDrawing)documentPart;
                    var shapeList = drawing.GetShapes();

                    for (int i = 0; i < shapeList.Count; i++)
                    {
                        var shape = shapeList[i];
                        if (shape is XSSFPicture)
                        {
                            var picture = (XSSFPicture)shape;
                            var anchor = picture.GetPreferredSize();

                            if (IsInternalOrIntersect(minRow, maxRow, minCol, maxCol, anchor.Row1, anchor.Row2, anchor.Col1, anchor.Col2, onlyInternal))
                            {
                                throw new NotImplementedException("XSSFSheet未实现ClearPictures()方法!");
                            }
                        }
                    }

                }
            }
        }
Esempio n. 18
0
        /**
         * Only for WithMoreVariousData.xlsx !
         */
        private static void doTestHyperlinkContents(XSSFSheet sheet)
        {
            Assert.IsNotNull(sheet.GetRow(3).GetCell(2).Hyperlink);
            Assert.IsNotNull(sheet.GetRow(14).GetCell(2).Hyperlink);
            Assert.IsNotNull(sheet.GetRow(15).GetCell(2).Hyperlink);
            Assert.IsNotNull(sheet.GetRow(16).GetCell(2).Hyperlink);

            // First is a link to poi
            Assert.AreEqual(HyperlinkType.Url,
                    sheet.GetRow(3).GetCell(2).Hyperlink.Type);
            Assert.AreEqual(null,
                    sheet.GetRow(3).GetCell(2).Hyperlink.Label);
            Assert.AreEqual("http://poi.apache.org/",
                    sheet.GetRow(3).GetCell(2).Hyperlink.Address);

            // Next is an internal doc link
            Assert.AreEqual(HyperlinkType.Document,
                    sheet.GetRow(14).GetCell(2).Hyperlink.Type);
            Assert.AreEqual("Internal hyperlink to A2",
                    sheet.GetRow(14).GetCell(2).Hyperlink.Label);
            Assert.AreEqual("Sheet1!A2",
                    sheet.GetRow(14).GetCell(2).Hyperlink.Address);

            // Next is a file
            Assert.AreEqual(HyperlinkType.File,
                    sheet.GetRow(15).GetCell(2).Hyperlink.Type);
            Assert.AreEqual(null,
                    sheet.GetRow(15).GetCell(2).Hyperlink.Label);
            Assert.AreEqual("WithVariousData.xlsx",
                    sheet.GetRow(15).GetCell(2).Hyperlink.Address);

            // Last is a mailto
            Assert.AreEqual(HyperlinkType.Email,
                    sheet.GetRow(16).GetCell(2).Hyperlink.Type);
            Assert.AreEqual(null,
                    sheet.GetRow(16).GetCell(2).Hyperlink.Label);
            Assert.AreEqual("mailto:[email protected]?subject=XSSF Hyperlinks",
                    sheet.GetRow(16).GetCell(2).Hyperlink.Address);
        }
Esempio n. 19
0
 /*package*/
 internal XSSFConditionalFormattingRule(XSSFSheet sh, CT_CfRule cfRule)
 {
     _cfRule = cfRule;
     _sh = sh;
 }
Esempio n. 20
0
        private m_project FillDataFromExcel(m_project insertData,XSSFSheet sheet, int row, int colNum,ref List<string> messageList, ref bool allowSave)
        {
            bool test;
            var dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum);

                                //required non-foreign key Name (string)
                                                colNum=0;
                                dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum);
                                if (dt != null)
                                {
                                    sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String);
                                    if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty)
                                    {
                                        insertData.name = sheet.GetRow(row).GetCell(colNum).StringCellValue;
                                    }
                                    else
                                    {
                                        allowSave = false;
                                        messageList.Add(GetExcelMessage(row, "Name", "Must be filled"));
                                    }

                                }
                                       				//optional foreign key Contractor Id
                colNum = 1;
            dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum);
            if (dt != null)
            {
                sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String);
                if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty)
                {
                    //insertData.contractor_id = RepoContractor.GetIdByNameAndInsert(sheet.GetRow(row).GetCell(colNum).StringCellValue);
                    //if(insertData.contractor_id == 0)
                    //{
                    //    allowSave = false;
                    //    messageList.Add(GetExcelMessage(row, "Contractor", "Does not exist"));
                    //}
                }
            }
                            //required non-foreign key Photo (string)
                                                colNum=2;
                                dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum);
                                if (dt != null)
                                {
                                    sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String);
                                    if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty)
                                    {
                                        insertData.photo = sheet.GetRow(row).GetCell(colNum).StringCellValue;
                                    }
                                    else
                                    {
                                        allowSave = false;
                                        messageList.Add(GetExcelMessage(row, "Photo", "Must be filled"));
                                    }

                                }
                                       				//required non-foreign key Description (string)
                                                colNum=3;
                                dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum);
                                if (dt != null)
                                {
                                    sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String);
                                    if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty)
                                    {
                                        insertData.description = sheet.GetRow(row).GetCell(colNum).StringCellValue;
                                    }
                                    else
                                    {
                                        allowSave = false;
                                        messageList.Add(GetExcelMessage(row, "Description", "Must be filled"));
                                    }

                                }
                                       				//required non-foreign key Start Date (System.DateTime)
                                                    colNum=4;
                                    dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum);
                                    if (dt != null)
                                    {
                                        sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String);
                                        if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty)
                                        {
                                            DateTime dateVal;
                                            double dateOA;

                                            test = Double.TryParse(sheet.GetRow(row).GetCell(colNum).StringCellValue, out dateOA);
                                            if (test == false)
                                            {
                                                allowSave = false;
                                                messageList.Add(GetExcelMessage(row, "Start Date", "Invalid value"));
                                            }
                                            else
                                            {
                                                test = DateTime.TryParse(DateTime.FromOADate(dateOA).ToString(), out dateVal);
                                                insertData.start_date = dateVal;
                                            }

                                        }
                                        else
                                        {
                                            allowSave = false;
                                            messageList.Add(GetExcelMessage(row, "Start Date", "Must be filled"));
                                        }

                                    }

                                               				//required non-foreign key Finish Date (System.DateTime)
                                                    colNum=5;
                                    dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum);
                                    if (dt != null)
                                    {
                                        sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String);
                                        if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty)
                                        {
                                            DateTime dateVal;
                                            double dateOA;

                                            test = Double.TryParse(sheet.GetRow(row).GetCell(colNum).StringCellValue, out dateOA);
                                            if (test == false)
                                            {
                                                allowSave = false;
                                                messageList.Add(GetExcelMessage(row, "Finish Date", "Invalid value"));
                                            }
                                            else
                                            {
                                                test = DateTime.TryParse(DateTime.FromOADate(dateOA).ToString(), out dateVal);
                                                insertData.finish_date = dateVal;
                                            }

                                        }
                                        else
                                        {
                                            allowSave = false;
                                            messageList.Add(GetExcelMessage(row, "Finish Date", "Must be filled"));
                                        }

                                    }

                                               				//optional non-foreign key Highlight (bool?)
                                            colNum=6;
                            dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum);
                            if (dt != null)
                            {
                                sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String);
                                if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty)
                                {
                                    string boolValue = sheet.GetRow(row).GetCell(colNum).StringCellValue;
                                    if(boolValue.ToLower() != "yes" && boolValue.ToLower() != "no")
                                    {
                                        allowSave = false;
                                        messageList.Add(GetExcelMessage(row, "Highlight", "Must be filled with Yes or No"));
                                    }
                                    else
                                    {
                                        bool val = false;
                                        if(boolValue.ToLower() == "yes")
                                        {
                                            val = true;
                                        }
                                        insertData.highlight = val;
                                    }

                                }

                            }
                                   				//required non-foreign key Project Stage (string)
                                                colNum=7;
                                dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum);
                                if (dt != null)
                                {
                                    sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String);
                                    if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty)
                                    {
                                        insertData.project_stage = sheet.GetRow(row).GetCell(colNum).StringCellValue;
                                    }
                                    else
                                    {
                                        allowSave = false;
                                        messageList.Add(GetExcelMessage(row, "Project Stage", "Must be filled"));
                                    }

                                }
                                       				//optional non-foreign key Status (byte?)
                                                    colNum=8;
                                    dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum);
                                    if (dt != null)
                                    {
                                        sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String);
                                        if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty)
                                        {
                                            int val = 0;
                                            test = Int32.TryParse(sheet.GetRow(row).GetCell(colNum).StringCellValue, out val);
                                            if (test == false)
                                            {
                                                allowSave = false;
                                                messageList.Add(GetExcelMessage(row, "Status", "Invalid value"));
                                            }
                                            else
                                            {
                                                insertData.status= (byte)val;
                                            }

                                        }

                                    }
                                               				//optional non-foreign key Budget (double?)
                                                    colNum=9;
                                    dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum);
                                    if (dt != null)
                                    {
                                        sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String);
                                        if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty)
                                        {
                                            double val = 0;
                                            test = Double.TryParse(sheet.GetRow(row).GetCell(colNum).StringCellValue, out val);
                                            if (test == false)
                                            {
                                                allowSave = false;
                                                messageList.Add(GetExcelMessage(row, "Budget", "Invalid value"));
                                            }
                                            else
                                            {
                                                insertData.budget= val;
                                            }

                                        }

                                    }
                                               				//required non-foreign key Currency (string)
                                                colNum=10;
                                dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum);
                                if (dt != null)
                                {
                                    sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String);
                                    if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty)
                                    {
                                        insertData.currency = sheet.GetRow(row).GetCell(colNum).StringCellValue;
                                    }
                                    else
                                    {
                                        allowSave = false;
                                        messageList.Add(GetExcelMessage(row, "Currency", "Must be filled"));
                                    }

                                }
                                       				//optional non-foreign key Num (int?)
                                                    colNum=11;
                                    dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum);
                                    if (dt != null)
                                    {
                                        sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String);
                                        if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty)
                                        {
                                            int val = 0;
                                            test = Int32.TryParse(sheet.GetRow(row).GetCell(colNum).StringCellValue, out val);
                                            if (test == false)
                                            {
                                                allowSave = false;
                                                messageList.Add(GetExcelMessage(row, "Num", "Invalid value"));
                                            }
                                            else
                                            {
                                                insertData.num= val;
                                            }

                                        }

                                    }
                                               				//optional foreign key Pmc Id
                colNum = 12;
            dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum);
            if (dt != null)
            {
                sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String);
                if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty)
                {
                    //insertData.pmc_id = RepoPmc.GetIdByNameAndInsert(sheet.GetRow(row).GetCell(colNum).StringCellValue);
                    //if(insertData.pmc_id == 0)
                    //{
                    //    allowSave = false;
                    //    messageList.Add(GetExcelMessage(row, "Pmc", "Does not exist"));
                    //}
                }
            }
                            //required non-foreign key Summary (string)
                                                colNum=13;
                                dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum);
                                if (dt != null)
                                {
                                    sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String);
                                    if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty)
                                    {
                                        insertData.summary = sheet.GetRow(row).GetCell(colNum).StringCellValue;
                                    }
                                    else
                                    {
                                        allowSave = false;
                                        messageList.Add(GetExcelMessage(row, "Summary", "Must be filled"));
                                    }

                                }
                                       				//optional foreign key Company Id
                colNum = 14;
            dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum);
            if (dt != null)
            {
                sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String);
                if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty)
                {
                    //insertData.company_id = RepoCompany.GetIdByNameAndInsert(sheet.GetRow(row).GetCell(colNum).StringCellValue);
                    //if(insertData.company_id == 0)
                    //{
                    //    allowSave = false;
                    //    messageList.Add(GetExcelMessage(row, "Company", "Does not exist"));
                    //}
                }
            }
                            //required non-foreign key Status Non Technical (string)
                                                colNum=15;
                                dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum);
                                if (dt != null)
                                {
                                    sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String);
                                    if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty)
                                    {
                                        insertData.status_non_technical = sheet.GetRow(row).GetCell(colNum).StringCellValue;
                                    }
                                    else
                                    {
                                        allowSave = false;
                                        messageList.Add(GetExcelMessage(row, "Status Non Technical", "Must be filled"));
                                    }

                                }
                                       				//required non-foreign key Is Completed (bool)
                                            colNum=16;
                            dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum);
                            if (dt != null)
                            {
                                sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String);
                                if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty)
                                {
                                    string boolValue = sheet.GetRow(row).GetCell(colNum).StringCellValue;
                                    if(boolValue.ToLower() != "yes" && boolValue.ToLower() != "no")
                                    {
                                        allowSave = false;
                                        messageList.Add(GetExcelMessage(row, "Is Completed", "Must be filled with Yes or No"));
                                    }
                                    else
                                    {
                                        bool val = false;
                                        if(boolValue.ToLower() == "yes")
                                        {
                                            val = true;
                                        }
                                        insertData.is_completed = val;
                                    }

                                }
                                else
                                {
                                    allowSave = false;
                                    messageList.Add(GetExcelMessage(row, "Is Completed", "Must be filled"));
                                }

                            }
                                   				//optional non-foreign key Completed Date (System.DateTime?)
                                                    colNum=17;
                                    dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum);
                                    if (dt != null)
                                    {
                                        sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String);
                                        if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty)
                                        {
                                            DateTime dateVal;
                                            double dateOA;

                                            test = Double.TryParse(sheet.GetRow(row).GetCell(colNum).StringCellValue, out dateOA);
                                            if (test == false)
                                            {
                                                allowSave = false;
                                                messageList.Add(GetExcelMessage(row, "Completed Date", "Invalid value"));
                                            }
                                            else
                                            {
                                                test = DateTime.TryParse(DateTime.FromOADate(dateOA).ToString(), out dateVal);
                                                insertData.completed_date = dateVal;
                                            }

                                        }

                                    }

                                               				//required foreign key Project Id
                colNum = 18;
            dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum);
            if (dt != null)
            {
                sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String);
                if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty)
                {
                    insertData.project_id = RepoProject.GetIdByNameAndInsert(sheet.GetRow(row).GetCell(colNum).StringCellValue);
                    if(insertData.project_id == 0)
                    {
                        allowSave = false;
                        messageList.Add(GetExcelMessage(row, "Project", "Does not exist"));
                    }
                }
                else
                {
                    allowSave = false;
                    messageList.Add(GetExcelMessage(row, "Project", "Must be filled"));
                }
            }
                            //required non-foreign key Submit For Approval Time (System.DateTime)
                                                    colNum=19;
                                    dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum);
                                    if (dt != null)
                                    {
                                        sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String);
                                        if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty)
                                        {
                                            DateTime dateVal;
                                            double dateOA;

                                            test = Double.TryParse(sheet.GetRow(row).GetCell(colNum).StringCellValue, out dateOA);
                                            if (test == false)
                                            {
                                                allowSave = false;
                                                messageList.Add(GetExcelMessage(row, "Submit For Approval Time", "Invalid value"));
                                            }
                                            else
                                            {
                                                test = DateTime.TryParse(DateTime.FromOADate(dateOA).ToString(), out dateVal);
                                                insertData.submit_for_approval_time = dateVal;
                                            }

                                        }
                                        else
                                        {
                                            allowSave = false;
                                            messageList.Add(GetExcelMessage(row, "Submit For Approval Time", "Must be filled"));
                                        }

                                    }

                                               				//required non-foreign key Approval Status (string)
                                                colNum=20;
                                dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum);
                                if (dt != null)
                                {
                                    sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String);
                                    if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty)
                                    {
                                        insertData.approval_status = sheet.GetRow(row).GetCell(colNum).StringCellValue;
                                    }
                                    else
                                    {
                                        allowSave = false;
                                        messageList.Add(GetExcelMessage(row, "Approval Status", "Must be filled"));
                                    }

                                }
                                       				//optional non-foreign key Approval Time (System.DateTime?)
                                                    colNum=21;
                                    dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum);
                                    if (dt != null)
                                    {
                                        sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String);
                                        if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty)
                                        {
                                            DateTime dateVal;
                                            double dateOA;

                                            test = Double.TryParse(sheet.GetRow(row).GetCell(colNum).StringCellValue, out dateOA);
                                            if (test == false)
                                            {
                                                allowSave = false;
                                                messageList.Add(GetExcelMessage(row, "Approval Time", "Invalid value"));
                                            }
                                            else
                                            {
                                                test = DateTime.TryParse(DateTime.FromOADate(dateOA).ToString(), out dateVal);
                                                insertData.approval_time = dateVal;
                                            }

                                        }

                                    }

                                               				//required non-foreign key Deleted (bool)
                                            colNum=22;
                            dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum);
                            if (dt != null)
                            {
                                sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String);
                                if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty)
                                {
                                    string boolValue = sheet.GetRow(row).GetCell(colNum).StringCellValue;
                                    if(boolValue.ToLower() != "yes" && boolValue.ToLower() != "no")
                                    {
                                        allowSave = false;
                                        messageList.Add(GetExcelMessage(row, "Deleted", "Must be filled with Yes or No"));
                                    }
                                    else
                                    {
                                        bool val = false;
                                        if(boolValue.ToLower() == "yes")
                                        {
                                            val = true;
                                        }
                                        insertData.deleted = val;
                                    }

                                }
                                else
                                {
                                    allowSave = false;
                                    messageList.Add(GetExcelMessage(row, "Deleted", "Must be filled"));
                                }

                            }
                                   				//required non-foreign key Approval Message (string)
                                                colNum=23;
                                dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum);
                                if (dt != null)
                                {
                                    sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String);
                                    if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty)
                                    {
                                        insertData.approval_message = sheet.GetRow(row).GetCell(colNum).StringCellValue;
                                    }
                                    else
                                    {
                                        allowSave = false;
                                        messageList.Add(GetExcelMessage(row, "Approval Message", "Must be filled"));
                                    }

                                }
                                       				//required non-foreign key Status Technical (string)
                                                colNum=24;
                                dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum);
                                if (dt != null)
                                {
                                    sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String);
                                    if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty)
                                    {
                                        insertData.status_technical = sheet.GetRow(row).GetCell(colNum).StringCellValue;
                                    }
                                    else
                                    {
                                        allowSave = false;
                                        messageList.Add(GetExcelMessage(row, "Status Technical", "Must be filled"));
                                    }

                                }
                                       				//required non-foreign key Scurve Data (string)
                                                colNum=25;
                                dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum);
                                if (dt != null)
                                {
                                    sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String);
                                    if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty)
                                    {
                                        insertData.scurve_data = sheet.GetRow(row).GetCell(colNum).StringCellValue;
                                    }
                                    else
                                    {
                                        allowSave = false;
                                        messageList.Add(GetExcelMessage(row, "Scurve Data", "Must be filled"));
                                    }

                                }

            return insertData;
        }
Esempio n. 21
0
 /*package*/
 internal XSSFConditionalFormatting(XSSFSheet sh, CT_ConditionalFormatting cf)
 {
     _cf = cf;
     _sh = sh;
 }
Esempio n. 22
0
	protected void SetUp()  {
		XSSFWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook("sheetProtection_not_protected.xlsx");
		sheet = workbook.GetSheetAt(0);
	}
Esempio n. 23
0
 public XSSFEvaluationSheet(ISheet sheet)
 {
     _xs = (XSSFSheet)sheet;
 }
Esempio n. 24
0
        private XSSFDataValidation CreateValidation(XSSFSheet sheet)
        {
            //create the cell that will have the validation applied
            IRow row = sheet.CreateRow(0);
            row.CreateCell(0);

            IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();

            IDataValidationConstraint constraint = dataValidationHelper.CreateCustomConstraint("true");
            XSSFDataValidation validation = (XSSFDataValidation)dataValidationHelper.CreateValidation(constraint, new CellRangeAddressList(0, 0, 0, 0));
            return validation;
        }