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 octets1730806016 05/11/2024 12:26:56
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.

Deutsche Übersetzung

Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.

Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.

Vielen Dank im Voraus.

Dokument erstellt 16/10/2009, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/cs-broldev-source-rf-view/utils/dgvFactory/ExcelDgv.cs.html

Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.