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;
}