public XSSFDialogsheet(XSSFSheet sheet) : base(sheet.GetPackagePart(), sheet.GetPackageRelationship()) { this.dialogsheet = new CT_Dialogsheet(); this.worksheet = new CT_Worksheet(); }
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; }
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; }
public XSSFAutoFilter(XSSFSheet sheet) { _sheet = sheet; }
public void SetParentSheet(XSSFSheet parentSheet) { this.parentSheet = parentSheet; }
/*package*/ internal XSSFConditionalFormatting(XSSFSheet sh) { _cf = new CT_ConditionalFormatting(); _sh = sh; }
/*package*/ internal XSSFConditionalFormattingRule(XSSFSheet sh) { _cfRule = new CT_CfRule(); _sh = sh; }
/// <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; } }
public void Dispose() { this._Path = null; this._Workbook = null; this._Sheet = null; }
private void validateCells(XSSFSheet sheet) { foreach (IRow row in sheet) { // trigger handling ((XSSFRow)row).OnDocumentWrite(); } }
public XSSFDataValidationHelper(XSSFSheet xssfSheet) : base() { this.xssfSheet = xssfSheet; }
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 = "" }); } }
/// <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; }
/// <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()方法!"); } } } } } }
/** * 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); }
/*package*/ internal XSSFConditionalFormattingRule(XSSFSheet sh, CT_CfRule cfRule) { _cfRule = cfRule; _sh = sh; }
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; }
/*package*/ internal XSSFConditionalFormatting(XSSFSheet sh, CT_ConditionalFormatting cf) { _cf = cf; _sh = sh; }
protected void SetUp() { XSSFWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook("sheetProtection_not_protected.xlsx"); sheet = workbook.GetSheetAt(0); }
public XSSFEvaluationSheet(ISheet sheet) { _xs = (XSSFSheet)sheet; }
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; }