ExcelDgv.cs

Description du code

ExcelDgv.cs est un fichier du projet BrolDev.
Ce fichier est situé dans /var/www/bin/sniplets/bibliobrol/broldev/src/.

Projet BrolDev : Librairie de composants réutilisables pour les applications BrolDev en CSharp.

Code source ou contenu du fichier

  1. using System;
  2. using System.IO;
  3. using System.Windows.Forms;
  4. using be.gaudry.view.dialogs;
  5.  
  6. namespace be.gaudry.view.utils.dgvFactory
  7. {
  8. public class ExcelDgv : AbstractDgv
  9. {
  10. #region declarations and constructors
  11. private enum SS_STYLE
  12. {
  13. titleHeaderCell,
  14. headerCell,
  15. rowCell,
  16. bottomCell,
  17. stringLiteral,
  18. decimalLiteral,
  19. integerLiteral,
  20. dateLiteral
  21. }
  22. private SaveFileDialog saveFileDialog;
  23. private string workBookDef, docPropsDef, stylesDef;
  24.  
  25. private string startExcelXML;
  26. private string endExcelXML = "</Workbook>";
  27.  
  28. private int rowCount = 0;
  29. private int sheetCount = 1;
  30.  
  31. public ExcelDgv()
  32. {
  33. setXMLStructure();
  34. saveFileDialog = new SaveFileDialog();
  35. saveFileDialog.CheckPathExists = true;
  36. saveFileDialog.CheckFileExists = false;
  37. saveFileDialog.AddExtension = true;
  38. saveFileDialog.CreatePrompt = true;
  39. saveFileDialog.DefaultExt = ".csv";
  40. saveFileDialog.Filter = "Excel (*.xls)|*.xls|Tous les fichiers (*.*)|*.*";
  41. saveFileDialog.FilterIndex = 1;
  42. }
  43.  
  44. private void setXMLStructure()
  45. {
  46. workBookDef = "<?xml version=\"1.0\"?>" +
  47. "\r\n<?mso-application progid=\"Excel.Sheet\"?>" +
  48. "\r\n<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"" +
  49. "\r\n xmlns:o=\"urn:schemas-microsoft-com:office:office\"" +
  50. "\r\n xmlns:x=\"urn:schemas-microsoft-com:office:excel\"" +
  51. "\r\n xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"" +
  52. "\r\n xmlns:html=\"http://www.w3.org/TR/REC-html40\"" +
  53. "\r\n xmlns:u1=\"urn:schemas- microsoft-com:office:excel\">";
  54. string xlsDate = getExcelDate(DateTime.Now);
  55. string bordersStyle = "\r\n <Borders>" +
  56. "\r\n <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>" +
  57. "\r\n <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>" +
  58. "\r\n <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>" +
  59. "\r\n <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>" +
  60. "\r\n </Borders>";
  61. docPropsDef = "\r\n<DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">" +
  62. //"\r\n <Author>" + authorName + "</Author>" + //todo : check if valid
  63. "\r\n <LastAuthor>St.GAUDRY</LastAuthor>" +
  64. "\r\n <Created>" + xlsDate + "</Created>" + //2007-11-22T14:39:06Z
  65. "\r\n <LastSaved>" + xlsDate + "</LastSaved>" + //2007-11-22T14:28:04Z
  66. "\r\n <Version>11.5606</Version>" + //11.5606
  67. "\r\n </DocumentProperties>";
  68. stylesDef = "\r\n<ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">" +
  69. "\r\n <WindowHeight>10005</WindowHeight>" +
  70. "\r\n <WindowWidth>10005</WindowWidth>" +
  71. "\r\n <WindowTopX>120</WindowTopX>" +
  72. "\r\n <WindowTopY>135</WindowTopY>" +
  73. "\r\n <ProtectStructure>False</ProtectStructure>" +
  74. "\r\n <ProtectWindows>False</ProtectWindows>" +
  75. "\r\n </ExcelWorkbook>" +
  76. "\r\n <Styles>" +
  77.  
  78. "\r\n <Style ss:ID=\"Default\" ss:Name=\"Normal\">" +
  79. "\r\n <Alignment ss:Vertical=\"Bottom\"/>" +
  80. "\r\n <Borders/>" +
  81. "\r\n <Font/>" +
  82. "\r\n <Interior/>" +
  83. "\r\n <NumberFormat/>" +
  84. "\r\n <Protection/>" +
  85. "\r\n </Style>" +
  86.  
  87. #region not used styles
  88. /*
  89.   "\r\n <Style ss:ID=\"" + getStyle(SS_STYLE.rowCell) + "\">" +
  90.   bordersStyle +
  91.   "\r\n <NumberFormat ss:Format=\"0\"/>" +
  92.   "\r\n </Style>" +
  93.  
  94.   "\r\n <Style ss:ID=\"" + getStyle(SS_STYLE.bottomCell) + "\">" +
  95.   bordersStyle +
  96.   "\r\n <NumberFormat ss:Format=\"0\"/>" +
  97.   "\r\n </Style>" +
  98.   */
  99. #endregion
  100.  
  101. "\r\n <Style ss:ID=\"" + getStyle(SS_STYLE.titleHeaderCell) + "\">" +
  102. "\r\n <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Bottom\"/>" +
  103. bordersStyle +
  104. "\r\n <Font ss:Size=\"12\" ss:Color=\"#000000\" ss:Bold=\"1\"/>" +
  105. "\r\n <Interior ss:Color=\"#FF9900\" ss:Pattern=\"Solid\"/>" +
  106. "\r\n </Style>" +
  107.  
  108. "\r\n <Style ss:ID=\"" + getStyle(SS_STYLE.headerCell) + "\">" +
  109. bordersStyle +
  110. "\r\n <Font ss:Color=\"#000000\" ss:Bold=\"1\"/>" +
  111. "\r\n <Interior ss:Color=\"#FF9900\" ss:Pattern=\"Solid\"/>" +
  112. "\r\n </Style>" +
  113.  
  114. "\r\n <Style ss:ID=\"" + getStyle(SS_STYLE.stringLiteral) + "\">" +
  115. bordersStyle +
  116. "\r\n <NumberFormat ss:Format=\"@\"/>" +
  117. "\r\n </Style>" +
  118.  
  119. "\r\n <Style ss:ID=\"" + getStyle(SS_STYLE.decimalLiteral) + "\">" +
  120. bordersStyle +
  121. "\r\n <NumberFormat ss:Format=\"0.0000\"/>" +
  122. "\r\n </Style>" +
  123.  
  124. "\r\n <Style ss:ID=\"" + getStyle(SS_STYLE.integerLiteral) + "\">" +
  125. bordersStyle +
  126. "\r\n <NumberFormat ss:Format=\"0\"/>" +
  127. "\r\n </Style>" +
  128.  
  129. "\r\n <Style ss:ID=\"" + getStyle(SS_STYLE.dateLiteral) + "\">" +
  130. bordersStyle +
  131. "\r\n <NumberFormat ss:Format=\"mm/dd/yyyy;@\"/>" +
  132. "\r\n </Style>" +
  133.  
  134. "\r\n </Styles>";
  135.  
  136. startExcelXML = workBookDef + docPropsDef + stylesDef;
  137. }
  138. private string getWorkSheetOptions()
  139. {
  140. return "\r\n <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">" +
  141. "\r\n <PageSetup>" +
  142. "\r\n <Header x:Margin=\"0.4921259845\"/>" +
  143. "\r\n <Footer x:Margin=\"0.4921259845\"/>" +
  144. "\r\n <PageMargins x:Bottom=\"0.984251969\" x:Left=\"0.78740157499999996\" x:Right=\"0.78740157499999996\" x:Top=\"0.984251969\"/>" +
  145. "\r\n </PageSetup>" +
  146. /*
  147.   "\r\n <Print>" +
  148.   "\r\n <ValidPrinterInfo/>" +
  149.   "\r\n <PaperSizeIndex>9</PaperSizeIndex>" +
  150.   "\r\n <HorizontalResolution>300</HorizontalResolution>" +
  151.   "\r\n <VerticalResolution>300</VerticalResolution>" +
  152.   "\r\n </Print>" +
  153.   */
  154. "\r\n <TabColorIndex>52</TabColorIndex>" +
  155. "\r\n <Selected/>" +
  156.  
  157. //freeze headers
  158. "\r\n <FreezePanes/>" +
  159. "\r\n <FrozenNoSplit/>" +
  160. "\r\n <SplitHorizontal>2</SplitHorizontal>" +//1 if no titleHeaderCell
  161. "\r\n <TopRowBottomPane>2</TopRowBottomPane>" +//1 if no titleHeaderCell
  162. "\r\n <ActivePane>2</ActivePane>" +
  163. "\r\n <Panes>" +
  164. "\r\n <Pane>" +
  165. "\r\n <Number>3</Number>" +
  166. "\r\n </Pane>" +
  167. "\r\n <Pane>" +
  168. "\r\n <Number>2</Number>" +
  169. /*
  170.   "\r\n <ActiveRow>429</ActiveRow>" +
  171.   "\r\n <ActiveCol>3</ActiveCol>" +
  172.   */
  173. "\r\n </Pane>" +
  174. "\r\n </Panes>" +
  175. "\r\n <ProtectObjects>False</ProtectObjects>" +
  176. "\r\n <ProtectScenarios>False</ProtectScenarios>" +
  177. "\r\n </WorksheetOptions>" +
  178.  
  179. //alternate rows
  180. //may add here some conditional formating
  181. "\r\n <ConditionalFormatting xmlns=\"urn:schemas-microsoft-com:office:excel\">" +
  182. "\r\n <Range>R3C1:R" + (dgv.RowCount + 3) + "C" + SelectedColumns.Count + "</Range>" +//R2C1:R" + dgv.RowCount + 2 if no titleHeaderCell
  183. "\r\n <Condition>" +
  184. "\r\n <Value1>MOD(ROW(),2)=1</Value1>" +
  185. "\r\n <Format Style='color:black;font-weight:700;mso-background-source:auto;mso-pattern:#FFFFCC'/>" +
  186. "\r\n </Condition>" +
  187. "\r\n <Condition>" +
  188. "\r\n <Value1>MOD(ROW(),2)=0</Value1>" +
  189. "\r\n <Format Style='color:black;font-weight:700;background:#FFCC99;mso-pattern:none'/>" +
  190. "\r\n </Condition>" +
  191. "\r\n </ConditionalFormatting>";
  192. }
  193. private string getExcelDate(DateTime dateTime)
  194. {
  195. return dateTime.Year.ToString() +
  196. "-" +
  197. (dateTime.Month < 10 ? "0" +
  198. dateTime.Month.ToString() : dateTime.Month.ToString()) +
  199. "-" +
  200. (dateTime.Day < 10 ? "0" +
  201. dateTime.Day.ToString() : dateTime.Day.ToString()) +
  202. "T" +
  203. (dateTime.Hour < 10 ? "0" +
  204. dateTime.Hour.ToString() : dateTime.Hour.ToString()) +
  205. ":" +
  206. (dateTime.Minute < 10 ? "0" +
  207. dateTime.Minute.ToString() : dateTime.Minute.ToString()) +
  208. ":" +
  209. (dateTime.Second < 10 ? "0" +
  210. dateTime.Second.ToString() : dateTime.Second.ToString()) +
  211. ".000";
  212. }
  213.  
  214. private string getStyle(SS_STYLE style)
  215. {
  216. return "s" + (int)style;
  217. }
  218. #endregion
  219.  
  220. public override void print(DataGridView dgv)
  221. {
  222. this.dgv = dgv;
  223. //setLayoutOptions(outputTitle);
  224.  
  225. DialogResult dr;
  226. if (dialogOwner != null)
  227. {
  228. dr = saveFileDialog.ShowDialog(dialogOwner);
  229. }
  230. else
  231. {
  232. dr = saveFileDialog.ShowDialog();
  233. }
  234. if (dr == DialogResult.OK)
  235. {
  236. print(saveFileDialog.FileName);
  237. }
  238. }
  239.  
  240. private void print(string destinationFile)
  241. {
  242. StreamWriter writer = new StreamWriter(destinationFile, false);
  243. bool writed = false;
  244. try
  245. {
  246. writer.Write(startExcelXML);
  247. //Cute sheetName because Excel don't allow more than 31 chars.
  248. string sheetName = (PrintTitle.Length<31)?PrintTitle:(PrintTitle.Remove(25)+"...");//25 to keep place to number of following sheets
  249. writer.Write("\r\n<Worksheet ss:Name=\"" + sheetName + "\">");
  250. string tableXMLItem = "\r\n<Table ss:ExpandedColumnCount=\"" + SelectedColumns.Count + "\" ss:ExpandedRowCount=\"" + (dgv.RowCount + 2) + "\" x:FullColumns=\"1\" x:FullRows=\"1\" ss:DefaultColumnWidth=\"100\">";
  251. writer.Write(tableXMLItem);
  252.  
  253. foreach (DataGridViewColumn dgvCol in SelectedColumns)
  254. {
  255. writer.Write("\r\n <Column ss:AutoFitWidth=\"1\" ss:Width=\"100\"/>");
  256. }
  257.  
  258. writer.Write("\r\n<Row ss:Height=\"17\">");//increase it if titleHeaderCell is modified
  259. writer.Write(
  260. "\r\n <Cell ss:MergeAcross=\"{0}\" ss:StyleID=\"{1}\"><Data ss:Type=\"String\">{2} {3}</Data></Cell>",
  261. new string[]
  262. {
  263. (SelectedColumns.Count-1).ToString(),
  264. getStyle(SS_STYLE.titleHeaderCell),
  265. PrintTitle,
  266. DateTime.Now.ToLongDateString()
  267. }
  268. );
  269. writer.Write("\r\n</Row>");
  270.  
  271.  
  272. writer.Write("\r\n<Row>");// ss:Height="13.5"
  273. foreach (DataGridViewColumn dgvCol in SelectedColumns)
  274. {
  275. writer.Write("\r\n <Cell ss:StyleID=\"{0}\"><Data ss:Type=\"String\">{1}</Data></Cell>", new string[] { getStyle(SS_STYLE.headerCell), dgvCol.HeaderText });
  276. }
  277. writer.Write("\r\n</Row>");
  278.  
  279.  
  280. foreach (DataGridViewRow dgvRow in dgv.Rows)
  281. {
  282. rowCount++;
  283. //if the number of rows is > 60000 create a new page to continue output
  284. if (rowCount == 60000)
  285. {
  286. rowCount = 0;
  287. sheetCount++;
  288. writer.Write("</Table>");
  289. writer.Write(" </Worksheet>");
  290. writer.Write("<Worksheet ss:Name=\"{0} {1}\">", new string[] { sheetName, sheetCount.ToString() });
  291. writer.Write(tableXMLItem);
  292. }
  293. writer.Write("\r\n<Row>");
  294.  
  295. foreach (DataGridViewCell dgvCell in dgvRow.Cells)
  296. {
  297. if (!SelectedColumns.Contains(dgvCell.OwningColumn))
  298. continue;
  299. System.Type rowType = dgvCell.ValueType;//dgvCell.OwningColumn.GetType();
  300.  
  301. switch (rowType.ToString())
  302. {
  303. #region case string
  304. case "System.String":
  305. string XMLstring = dgvCell.EditedFormattedValue.ToString();
  306. XMLstring = XMLstring.Trim();
  307. XMLstring = XMLstring.Replace("&", "&");
  308. XMLstring = XMLstring.Replace(">", ">");
  309. XMLstring = XMLstring.Replace("<", "<");
  310. writer.Write("\r\n <Cell ss:StyleID=\"{0}\"><Data ss:Type=\"String\">{1}</Data></Cell>", new string[] { getStyle(SS_STYLE.stringLiteral), XMLstring });
  311. break;
  312. #endregion
  313.  
  314. #region case DateTime
  315. case "System.DateTime":
  316. //Excel has a specific Date Format of YYYY-MM-DD followed by
  317. //the letter 'T' then hh:mm:sss.lll Example 2005-01-31T24:01:21.000
  318. //The Following Code puts the date stored in XMLDate
  319. //to the format above
  320.  
  321. string XMLDatetoString = ""; //Excel Converted Date
  322. try
  323. {
  324. DateTime XMLDate = (DateTime)dgvCell.Value;
  325.  
  326. XMLDatetoString = getExcelDate(XMLDate);
  327. writer.Write("\r\n <Cell ss:StyleID=\"{0}\"><Data ss:Type=\"DateTime\">{1}</Data></Cell>", new string[] { getStyle(SS_STYLE.dateLiteral), XMLDatetoString });
  328. }
  329. catch
  330. {
  331. writer.Write("\r\n <Cell ss:StyleID=\"{0}\"><Data ss:Type=\"String\"></Data></Cell>", new string[] { getStyle(SS_STYLE.stringLiteral) });
  332. }
  333. break;
  334. #endregion
  335.  
  336. #region case bool
  337. case "System.Boolean":
  338. writer.Write("\r\n <Cell ss:StyleID=\"{0}\"><Data ss:Type=\"String\">{1}</Data></Cell>", new string[] { getStyle(SS_STYLE.stringLiteral), getBoolText((bool)dgvCell.Value) });
  339. break;
  340. #endregion
  341.  
  342. #region case numeric values
  343. case "System.Int16":
  344. case "System.Int32":
  345. case "System.Int64":
  346. case "System.Byte":
  347. writer.Write("\r\n <Cell ss:StyleID=\"{0}\"><Data ss:Type=\"Number\">{1}</Data></Cell>", new string[] { getStyle(SS_STYLE.dateLiteral), dgvCell.Value.ToString() });
  348. break;
  349. case "System.Decimal":
  350. case "System.Double":
  351. writer.Write("\r\n <Cell ss:StyleID=\"{0}\"><Data ss:Type=\"Number\">{1}</Data></Cell>", new string[] { getStyle(SS_STYLE.decimalLiteral), dgvCell.Value.ToString() });
  352. break;
  353. #endregion
  354.  
  355. #region case null
  356. case "System.DBNull":
  357. writer.Write("\r\n <Cell ss:StyleID=\"{0}\"><Data ss:Type=\"String\"></Data></Cell>", new string[] { getStyle(SS_STYLE.stringLiteral) });
  358. break;
  359. #endregion
  360.  
  361. default:
  362. writer.Write("\r\n <Cell ss:StyleID=\"{0}\"><Data ss:Type=\"String\">{1}</Data></Cell>", new string[] { getStyle(SS_STYLE.stringLiteral),dgvCell.FormattedValue.ToString() });
  363. break;
  364. }
  365. }
  366. writer.Write("</Row>");
  367. }
  368. writer.Write("\r\n</Table>");
  369. writer.Write(getWorkSheetOptions());
  370. writer.Write("\r\n</Worksheet>");
  371. writer.Write(endExcelXML);
  372. writed = true;
  373. }
  374. catch (Exception e)
  375. {
  376. ExceptionDialog.ShowDialog(e, this.dialogOwner);
  377. }
  378. finally
  379. {
  380. writer.Close();
  381. }
  382. if (writed)
  383. {
  384. CustomOpenResultFileDialog.Show(
  385. this.dialogOwner,
  386. destinationFile,
  387. "Le fichier est sauvé.\nVous pouvez sélectionner une action à effectuer.",
  388. "Sauvegarde",
  389. MessageBoxIcon.Information
  390. );
  391. }
  392. }
  393.  
  394.  
  395. private string getBoolText(bool dgvCell)
  396. {
  397. //todo : add 2 strings to use as replacement string of bool values
  398. //and add textboxes to set it on the layout options dialog
  399. return (dgvCell) ? "VRAI" : "FAUX";
  400. }
  401. }
  402. }

Structure et Fichiers du projet

Afficher/masquer...


Répertoires contenus dans /var/www/bin/sniplets/bibliobrol/broldev/src/view/utils/dgvFactory/ 
IcôneNomTailleModification
Pas de sous-répertoires.
IcôneNomTailleModification
| _ Répertoire parent0 octets1730798793 05/11/2024 10:26:33
Fichiers contenus dans /var/www/bin/sniplets/bibliobrol/broldev/src/view/utils/dgvFactory/ 
IcôneNomTailleModificationAction
IcôneNomTailleModificationAction
Afficher le fichier .cs|.csHtmlDgv.cs5.03 Ko31/10/2018 18:33:22-refusé-
Afficher le fichier .cs|.csExcelDgv.cs18.77 Ko31/10/2018 18:33:22-refusé-
Afficher le fichier .cs|.csXMLDgv.cs3.02 Ko31/10/2018 18:33:23-refusé-
Afficher le fichier .cs|.csCSVDgv.cs5.02 Ko31/10/2018 18:33:22-refusé-
Afficher le fichier .cs|.csPrintDgv.cs18.02 Ko31/10/2018 18:33:23-refusé-
Afficher le fichier .cs|.csIDgv.cs271 octets31/10/2018 18:33:23-refusé-
Afficher le fichier .cs|.csTextDgv.cs4.02 Ko31/10/2018 18:33:23-refusé-
Afficher le fichier .cs|.csAbstractDgv.cs3.46 Ko31/10/2018 18:33:22-refusé-

Utilisation de l'explorateur de code

  • Navigation :
    • Un clic sur une icône de répertoire ouvre ce répertoire pour en afficher les fichiers.
    • Lorsque le répertoire en cours ne contient pas de sous-répertoires il est possible de remonter vers le répertoire parent.
    • La structure de répertoires en treetable (tableau en forme d'arborescence) n'est plus possibledans cette version.
    • Un clic sur une icône de fichier ouvre ce fichier pour en afficher le code avec la coloration syntaxique adaptée en fonction du langage principal utilisé dans le fichier.
  • Affichage :
    • Il est possible de trier les répertoires ou les fichiers selon certains critères (nom, taille, date).
  • Actions :
    • Les actions possible sur les fichiers dépendent de vos droits d'utilisateur sur le site. Veuillez activer le mode utilisateur pour activer les actions.

Version en cache

05/11/2024 10:26:33 Cette version de la page est en cache (à la date du 05/11/2024 10:26:33) afin d'accélérer le traitement. Vous pouvez activer le mode utilisateur dans le menu en haut pour afficher la dernère version de la page.

Document créé le 16/10/2009, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/cs-broldev-source-rf-view/utils/dgvFactory/ExcelDgv.cs.html

L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.