API tools faq
paste
Advertisement
lilos404

Untitled

lilos404
Apr 24th, 2018
68
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 14.89 KB | None | 0 0
raw download clone embed print report
  1. using System;
  2. using System.Collections;
  3. using System.Linq;
  4. using System.Drawing;
  5. using System.Globalization;
  6. using System.Text.RegularExpressions;
  7. using System.Threading.Tasks;
  8. using DevExpress.Spreadsheet;
  9. using DevExpress.Web.Office;
  10. using DevExpress.XtraSpreadsheet;
  11.  
  12.  
  13. namespace DevExpress.Web.Demos
  14. {
  15.  
  16. public class CellValueValidationHelpers
  17. {
  18.  
  19. static Worksheet MPD_Page, Section_Page, ATA_Page, Revision_Page, Carte_Page, MRBR_Page, ACCESS_Page, ZONE_Page, SKILL_Page, TASK_Page ;
  20. public static void OnCellValueChanged(object sender, SpreadsheetCellEventArgs e)
  21. {
  22. SpreadsheetDocumentInfo documentInfo = sender as SpreadsheetDocumentInfo;
  23. revisionValidationCell(e.Cell);
  24. sectionValidationCell(e.Cell);
  25.  
  26. }
  27. // public static void PrepareWorksheet(Worksheet MPD_Page_in, Worksheet Revision_Page_in, Worksheet Section_Page_in, Worksheet ATA_Page_in, Worksheet Carte_Page_in)
  28. public static async Task PrepareWorksheet(DevExpress.Web.Mvc.MVCxSpreadsheet wb)
  29.  
  30. {
  31.  
  32. MPD_Page = wb.Document.Worksheets[0];
  33. Section_Page = wb.Document.Worksheets[2];
  34. Revision_Page = wb.Document.Worksheets[1];
  35. ATA_Page = wb.Document.Worksheets[3];
  36. Carte_Page = wb.Document.Worksheets[4];
  37. MRBR_Page = wb.Document.Worksheets[5];
  38. ACCESS_Page = wb.Document.Worksheets[6];
  39. ZONE_Page = wb.Document.Worksheets[7];
  40. SKILL_Page = wb.Document.Worksheets[9];
  41. TASK_Page = wb.Document.Worksheets[10];
  42. var tr = revisionValidation();
  43. var sr = sectionValidation();
  44. var tv = taskValidation();
  45. var mr = mrbrValidation();
  46. //accessValidation();
  47. //preparationValidation();
  48. //zoneValidation();
  49. // skillCodeValidaion();
  50. //taskCodeValidation();
  51. // await tr;
  52. // await sr;
  53. // await tv;
  54. // await mr;
  55.  
  56. }
  57.  
  58. private static void taskCodeValidation()
  59. {
  60. var cells = MPD_Page.Columns[9].ExistingCells.ToArray();
  61. string indice = "";
  62. var taskListes = TASK_Page.Columns[0].ExistingCells.ToArray();
  63. string[] taskValues = new string[taskListes.Length];
  64.  
  65. for (int i = 0; i < taskListes.Length; i++)
  66. {
  67. taskValues[i] = taskListes[i].Value.ToString();
  68.  
  69. }
  70.  
  71.  
  72. for (int i = 1; i < cells.Length; i++)
  73. {
  74.  
  75. indice = "J" + (i + 1).ToString();
  76. if (cells[i].Value.ToString() == "")
  77. {
  78. MPD_Page.Cells[indice].FillColor = Color.Red;
  79.  
  80. }
  81. else if (Array.IndexOf(taskValues, cells[i].Value.ToString()) == -1)
  82. {
  83. MPD_Page.Cells[indice].FillColor = Color.Red;
  84. }
  85.  
  86.  
  87.  
  88.  
  89.  
  90. }
  91. }
  92.  
  93. private static void skillCodeValidaion()
  94. {
  95.  
  96. var cells = MPD_Page.Columns[8].ExistingCells.ToArray();
  97. string indice = "";
  98. var skillListes = SKILL_Page.Columns[0].ExistingCells.ToArray();
  99. string[] skillValue = new string[skillListes.Length];
  100.  
  101. for (int i = 0; i < skillListes.Length; i++)
  102. {
  103. skillValue[i] = skillListes[i].Value.ToString();
  104.  
  105. }
  106.  
  107.  
  108. for (int i = 1; i < cells.Length; i++)
  109. {
  110.  
  111. indice = "I" + (i + 1).ToString();
  112. if (cells[i].Value.ToString() == "")
  113. {
  114. MPD_Page.Cells[indice].FillColor = Color.Red;
  115.  
  116. }
  117. else if (Array.IndexOf(skillValue, cells[i].Value.ToString()) == -1)
  118. {
  119. MPD_Page.Cells[indice].FillColor = Color.Red;
  120. }
  121.  
  122.  
  123.  
  124.  
  125.  
  126. }
  127. }
  128.  
  129. private static void zoneValidation()
  130. {
  131. var cells = MPD_Page.Columns[6].ExistingCells.ToArray();
  132. string indice = "";
  133. var zonesListes = ZONE_Page.Columns[0].ExistingCells.ToArray();
  134. string[] zonesValue = new string[zonesListes.Length];
  135. string[] zones;
  136. for (int i = 0; i < zonesListes.Length; i++)
  137. {
  138. zonesValue[i] = zonesListes[i].Value.ToString();
  139.  
  140. }
  141. for (int i = 1; i < cells.Length; i++)
  142. {
  143.  
  144. indice = "G" + (i + 1).ToString();
  145. if (cells[i].Value.ToString() == "")
  146. {
  147. continue;
  148. }
  149. zones = cells[i].Value.ToString().Split('\n');
  150. foreach (var zone in zones)
  151. {
  152. if (Array.IndexOf(zonesValue, zone) == -1)
  153. {
  154. MPD_Page.Cells[indice].FillColor = Color.Red;
  155. break;
  156. }
  157.  
  158. }
  159.  
  160.  
  161. }
  162. }
  163.  
  164. private static void preparationValidation()
  165. {
  166. var cells = MPD_Page.Columns[5].ExistingCells.ToArray();
  167. string indice = "";
  168. for (int i = 1; i < cells.Length; i++)
  169. {
  170.  
  171. indice = "F" + (i + 1).ToString();
  172. if (cells[i].Value.ToString() == "")
  173. {
  174. continue;
  175. }
  176.  
  177. if (cells[i].Value.ToString().IndexOf("\n")>0)
  178. {
  179. MPD_Page.Cells[indice].FillColor = Color.Red;
  180. }
  181.  
  182. }
  183.  
  184.  
  185.  
  186. }
  187.  
  188. static void accessValidation()
  189. {
  190. var accessListe = ACCESS_Page.Columns[0].ExistingCells.ToArray();
  191. var cells = MPD_Page.Columns[4].ExistingCells.ToArray();
  192. var zonesListes = MPD_Page.Columns[6].ExistingCells.ToArray();
  193. string[] accessValue = new string[accessListe.Length];
  194.  
  195. for (int i = 0; i < accessListe.Length; i++)
  196. {
  197. accessValue[i] = accessListe[i].Value.ToString();
  198.  
  199. }
  200.  
  201.  
  202. string indice = "";
  203.  
  204. for (int i = 1; i < cells.Length; i++)
  205. {
  206. indice = "E" + (i + 1).ToString();
  207. bool notFound = false;
  208. string[] ligneAccess , zones ;
  209. string[] access;
  210. string[] cellAccessValues , cellAccessValuesReversed;
  211.  
  212. if (cells[i].Value.ToString() == "")
  213. {
  214. continue;
  215. }
  216.  
  217. zones = zonesListes[i].Value.ToString().Split('\n');
  218. ligneAccess = cells[i].Value.ToString().Split('\n');
  219.  
  220. if (zones.Length < ligneAccess.Length)
  221. {
  222. MPD_Page.Cells[indice].FillColor = Color.Red;
  223. continue;
  224.  
  225. }
  226. cellAccessValues = cells[i].Value.ToString().Replace('\n', ';').Split(';');
  227. cellAccessValuesReversed = cells[i].Value.ToString().Replace('\n', ';').Split(';');
  228. Array.Reverse(cellAccessValuesReversed);
  229.  
  230.  
  231. foreach (var itemligne in ligneAccess)
  232. {
  233.  
  234. if (itemligne != "") {
  235. access = itemligne.Split(';');
  236. foreach (var itemaccess in access)
  237. {
  238.  
  239. if (Array.IndexOf(accessValue, itemaccess)== -1 || (Array.IndexOf(cellAccessValues, itemaccess) != (cellAccessValues.Length - Array.IndexOf(cellAccessValuesReversed, itemaccess)) - 1))
  240. {
  241. notFound = true;
  242. MPD_Page.Cells[indice].FillColor = Color.Red;
  243. break;
  244. }
  245. }
  246. if (notFound)
  247. {
  248. break;
  249. }
  250. }
  251.  
  252.  
  253.  
  254. }
  255.  
  256.  
  257.  
  258. }
  259. }
  260.  
  261. static async Task revisionValidation()
  262. {
  263. Column cc = MPD_Page.Columns[0];
  264. var cells = cc.ExistingCells.ToArray();
  265. string indice = "";
  266.  
  267. for (int i = 2; i < cells.Length; i++)
  268. {
  269. indice = "A" + (i + 1).ToString();
  270.  
  271. if (cells[i].Value.ToString() == "" || cells[i].Value.ToString() == "R" || cells[i].Value.ToString() == "N" || cells[i].Value.ToString() == "D")
  272. {
  273.  
  274. }
  275. else
  276. {
  277. MPD_Page.Cells[indice].FillColor = Color.Red;
  278.  
  279. }
  280. }
  281. }
  282. static async Task sectionValidation()
  283. {
  284. ArrayList arrSample = new ArrayList();
  285.  
  286.  
  287. Column cc = Section_Page.Columns[0];
  288. Column cc2 = ATA_Page.Columns[0];
  289.  
  290.  
  291. var cells = cc.ExistingCells.ToArray();
  292. var cells2 = cc2.ExistingCells.ToArray();
  293.  
  294. Regex regex = new Regex(@"^[0-9]?[0-9]-[0-9][0-9]$");
  295. string[] token;
  296. string indice = "";
  297. // var ar = MPD_Page.Columns[0].ExistingCells.ToArray() ;
  298. int colN = MPD_Page.Columns[0].ExistingCells.ToArray().Length;
  299. for (int i = 2; i < colN; i++)
  300. {
  301. indice = "B" + i.ToString();
  302.  
  303.  
  304.  
  305. if (regex.IsMatch(MPD_Page.Cells[indice].Value.ToString()))
  306. {
  307. token = MPD_Page.Cells[indice].Value.ToString().Split('-');
  308. if (!cells.Any(x => x.DisplayText == token[0]) || !cells2.Any(x => x.DisplayText == token[1]))
  309. {
  310.  
  311. MPD_Page.Cells[indice].FillColor = Color.Red;
  312. }
  313.  
  314. }
  315. else
  316. {
  317. MPD_Page.Cells[indice].FillColor = Color.Red;
  318. }
  319.  
  320. }
  321. }
  322. static async Task taskValidation()
  323. {
  324. Column cc = MPD_Page.Columns[2];
  325. var regexStrings = Carte_Page.Columns[0].ExistingCells.ToArray();
  326.  
  327. var cells = cc.ExistingCells.ToArray();
  328.  
  329.  
  330. string[] cellsValue = new string[cells.Length];
  331. string[] cellsValueReversed = new string[cells.Length];
  332. for (int i = 0; i < cells.Length; i++)
  333. {
  334. cellsValue[i] = cells[i].Value.ToString();
  335. cellsValueReversed[i] = cells[i].Value.ToString();
  336. }
  337.  
  338. Array.Reverse(cellsValueReversed);
  339.  
  340.  
  341. string indice = "";
  342.  
  343. Regex[] regexS = new Regex[regexStrings.Length];
  344. int regexS_length = 0;
  345. for (int i = 1; i < regexStrings.Length; i++)
  346. {
  347.  
  348. if (regexStrings[i].Value.ToString() != "")
  349. regexS[regexS_length++] = new Regex(regexStrings[i].Value.ToString());
  350. }
  351.  
  352.  
  353. // Regex regex = new Regex(@"^[0-9]{6}-[0-9]{2}-[0-9]{1}$");
  354. for (int i = 1; i < cells.Length; i++)
  355. {
  356. indice = "C" + (i + 1).ToString();
  357.  
  358. if (Array.IndexOf(cellsValue, cellsValue[i]) != (cells.Length - Array.IndexOf(cellsValueReversed, cellsValue[i])) - 1)
  359. {
  360. MPD_Page.Cells[indice].FillColor = Color.Red;
  361. continue;
  362. }
  363.  
  364.  
  365. MPD_Page.Cells[indice].FillColor = Color.Red;
  366. for (int j = 0; j < regexS_length; j++)
  367. {
  368. if (regexS[j].IsMatch(cells[i].Value.ToString()))
  369. {
  370. MPD_Page.Cells[indice].FillColor = Color.White;
  371. break;
  372. }
  373. }
  374.  
  375.  
  376.  
  377.  
  378.  
  379. }
  380.  
  381.  
  382. }
  383. static async Task mrbrValidation()
  384. {
  385. Column cc = MPD_Page.Columns[3];
  386. var regexStrings = MRBR_Page.Columns[5].ExistingCells.ToArray();
  387.  
  388. var cells = MPD_Page.Columns[3].ExistingCells.ToArray();
  389.  
  390. Regex[] regexS = new Regex[regexStrings.Length - 1];
  391. int regexS_length = 0;
  392. for (int i = 1; i < regexStrings.Length; i++)
  393. {
  394.  
  395. if (regexStrings[i].Value.ToString() != "")
  396. regexS[regexS_length++] = new Regex(regexStrings[i].Value.ToString());
  397. }
  398.  
  399. string indice = "";
  400.  
  401.  
  402. for (int i = 1; i < cells.Length; i++)
  403. {
  404. indice = "D" + (i + 1).ToString();
  405.  
  406. /*
  407. if (Array.IndexOf(cellsValue, cellsValue[i]) != (cells.Length - Array.IndexOf(cellsValueReversed, cellsValue[i])) - 1)
  408. {
  409. MPD_Page.Cells[indice].FillColor = Color.Red;
  410. continue;
  411. } */
  412.  
  413. string[] elemnts;
  414. MPD_Page.Cells[indice].FillColor = Color.Red;
  415.  
  416. for (int j = 0; j < regexS_length; j++)
  417. {
  418. elemnts = cells[i].Value.ToString().Split('\n');
  419. foreach (var item in elemnts)
  420. {
  421. if (regexS[j].IsMatch(item))
  422. {
  423. MPD_Page.Cells[indice].FillColor = Color.White;
  424. break;
  425. }
  426. }
  427.  
  428.  
  429.  
  430.  
  431. }
  432.  
  433.  
  434.  
  435.  
  436.  
  437. }
  438. }
  439.  
  440. static async Task revisionValidationCell(Cell cell)
  441. {
  442. if (cell.ColumnIndex != 0)
  443. return;
  444.  
  445. if (cell.Value.ToString() == "" || cell.Value.ToString() == "R" || cell.Value.ToString() == "N" || cell.Value.ToString() == "D")
  446. cell.FillColor = Color.White;
  447. else cell.FillColor = Color.Red;
  448. }
  449. static async Task sectionValidationCell(Cell cell)
  450. {
  451. if (cell.ColumnIndex != 1)
  452. return;
  453. Column cc = Section_Page.Columns[0];
  454. Column cc2 = ATA_Page.Columns[0];
  455.  
  456.  
  457. var cells = cc.ExistingCells.ToArray();
  458. var cells2 = cc2.ExistingCells.ToArray();
  459. Regex regex = new Regex(@"^[0-9]?[0-9]-[0-9][0-9]$");
  460.  
  461. string[] token;
  462. if (regex.IsMatch(cell.Value.ToString()))
  463. {
  464. token = cell.Value.ToString().Split('-');
  465. if (!cells.Any(x => x.DisplayText == token[0]) || !cells2.Any(x => x.DisplayText == token[1]))
  466. cell.FillColor = Color.Red;
  467. else cell.FillColor = Color.White;
  468. }
  469. else
  470. cell.FillColor = Color.Red;
  471. }
  472. }
  473. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!