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
Code c# (ExcelDgv.cs) (402 lignes)
using System; using System.IO; using System.Windows.Forms; using be.gaudry.view.dialogs; namespace be.gaudry.view.utils.dgvFactory { public class ExcelDgv : AbstractDgv { #region declarations and constructors private enum SS_STYLE { titleHeaderCell, headerCell, rowCell, bottomCell, stringLiteral, decimalLiteral, integerLiteral, dateLiteral } private SaveFileDialog saveFileDialog; private string workBookDef, docPropsDef, stylesDef; private string startExcelXML; private string endExcelXML = "</Workbook>"; private int rowCount = 0; private int sheetCount = 1; public ExcelDgv() { setXMLStructure(); saveFileDialog.CheckPathExists = true; saveFileDialog.CheckFileExists = false; saveFileDialog.AddExtension = true; saveFileDialog.CreatePrompt = true; saveFileDialog.DefaultExt = ".csv"; saveFileDialog.Filter = "Excel (*.xls)|*.xls|Tous les fichiers (*.*)|*.*"; saveFileDialog.FilterIndex = 1; } private void setXMLStructure() { workBookDef = "<?xml version=\"1.0\"?>" + "\r\n<?mso-application progid=\"Excel.Sheet\"?>" + "\r\n<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"" + "\r\n xmlns:o=\"urn:schemas-microsoft-com:office:office\"" + "\r\n xmlns:x=\"urn:schemas-microsoft-com:office:excel\"" + "\r\n xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"" + "\r\n xmlns:html=\"http://www.w3.org/TR/REC-html40\"" + "\r\n xmlns:u1=\"urn:schemas- microsoft-com:office:excel\">"; string xlsDate = getExcelDate(DateTime.Now); string bordersStyle = "\r\n <Borders>" + "\r\n <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>" + "\r\n <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>" + "\r\n <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>" + "\r\n <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>" + "\r\n </Borders>"; docPropsDef = "\r\n<DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">" + //"\r\n <Author>" + authorName + "</Author>" + //todo : check if valid "\r\n <LastAuthor>St.GAUDRY</LastAuthor>" + "\r\n <Created>" + xlsDate + "</Created>" + //2007-11-22T14:39:06Z "\r\n <LastSaved>" + xlsDate + "</LastSaved>" + //2007-11-22T14:28:04Z "\r\n <Version>11.5606</Version>" + //11.5606 "\r\n </DocumentProperties>"; stylesDef = "\r\n<ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">" + "\r\n <WindowHeight>10005</WindowHeight>" + "\r\n <WindowWidth>10005</WindowWidth>" + "\r\n <WindowTopX>120</WindowTopX>" + "\r\n <WindowTopY>135</WindowTopY>" + "\r\n <ProtectStructure>False</ProtectStructure>" + "\r\n <ProtectWindows>False</ProtectWindows>" + "\r\n </ExcelWorkbook>" + "\r\n <Styles>" + "\r\n <Style ss:ID=\"Default\" ss:Name=\"Normal\">" + "\r\n <Alignment ss:Vertical=\"Bottom\"/>" + "\r\n <Borders/>" + "\r\n <Font/>" + "\r\n <Interior/>" + "\r\n <NumberFormat/>" + "\r\n <Protection/>" + "\r\n </Style>" + #region not used styles /* "\r\n <Style ss:ID=\"" + getStyle(SS_STYLE.rowCell) + "\">" + bordersStyle + "\r\n <NumberFormat ss:Format=\"0\"/>" + "\r\n </Style>" + "\r\n <Style ss:ID=\"" + getStyle(SS_STYLE.bottomCell) + "\">" + bordersStyle + "\r\n <NumberFormat ss:Format=\"0\"/>" + "\r\n </Style>" + */ #endregion "\r\n <Style ss:ID=\"" + getStyle(SS_STYLE.titleHeaderCell) + "\">" + "\r\n <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Bottom\"/>" + bordersStyle + "\r\n <Font ss:Size=\"12\" ss:Color=\"#000000\" ss:Bold=\"1\"/>" + "\r\n <Interior ss:Color=\"#FF9900\" ss:Pattern=\"Solid\"/>" + "\r\n </Style>" + "\r\n <Style ss:ID=\"" + getStyle(SS_STYLE.headerCell) + "\">" + bordersStyle + "\r\n <Font ss:Color=\"#000000\" ss:Bold=\"1\"/>" + "\r\n <Interior ss:Color=\"#FF9900\" ss:Pattern=\"Solid\"/>" + "\r\n </Style>" + "\r\n <Style ss:ID=\"" + getStyle(SS_STYLE.stringLiteral) + "\">" + bordersStyle + "\r\n <NumberFormat ss:Format=\"@\"/>" + "\r\n </Style>" + "\r\n <Style ss:ID=\"" + getStyle(SS_STYLE.decimalLiteral) + "\">" + bordersStyle + "\r\n <NumberFormat ss:Format=\"0.0000\"/>" + "\r\n </Style>" + "\r\n <Style ss:ID=\"" + getStyle(SS_STYLE.integerLiteral) + "\">" + bordersStyle + "\r\n <NumberFormat ss:Format=\"0\"/>" + "\r\n </Style>" + "\r\n <Style ss:ID=\"" + getStyle(SS_STYLE.dateLiteral) + "\">" + bordersStyle + "\r\n <NumberFormat ss:Format=\"mm/dd/yyyy;@\"/>" + "\r\n </Style>" + "\r\n </Styles>"; startExcelXML = workBookDef + docPropsDef + stylesDef; } private string getWorkSheetOptions() { return "\r\n <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">" + "\r\n <PageSetup>" + "\r\n <Header x:Margin=\"0.4921259845\"/>" + "\r\n <Footer x:Margin=\"0.4921259845\"/>" + "\r\n <PageMargins x:Bottom=\"0.984251969\" x:Left=\"0.78740157499999996\" x:Right=\"0.78740157499999996\" x:Top=\"0.984251969\"/>" + "\r\n </PageSetup>" + /* "\r\n <Print>" + "\r\n <ValidPrinterInfo/>" + "\r\n <PaperSizeIndex>9</PaperSizeIndex>" + "\r\n <HorizontalResolution>300</HorizontalResolution>" + "\r\n <VerticalResolution>300</VerticalResolution>" + "\r\n </Print>" + */ "\r\n <TabColorIndex>52</TabColorIndex>" + "\r\n <Selected/>" + //freeze headers "\r\n <FreezePanes/>" + "\r\n <FrozenNoSplit/>" + "\r\n <SplitHorizontal>2</SplitHorizontal>" +//1 if no titleHeaderCell "\r\n <TopRowBottomPane>2</TopRowBottomPane>" +//1 if no titleHeaderCell "\r\n <ActivePane>2</ActivePane>" + "\r\n <Panes>" + "\r\n <Pane>" + "\r\n <Number>3</Number>" + "\r\n </Pane>" + "\r\n <Pane>" + "\r\n <Number>2</Number>" + /* "\r\n <ActiveRow>429</ActiveRow>" + "\r\n <ActiveCol>3</ActiveCol>" + */ "\r\n </Pane>" + "\r\n </Panes>" + "\r\n <ProtectObjects>False</ProtectObjects>" + "\r\n <ProtectScenarios>False</ProtectScenarios>" + "\r\n </WorksheetOptions>" + //alternate rows //may add here some conditional formating "\r\n <ConditionalFormatting xmlns=\"urn:schemas-microsoft-com:office:excel\">" + "\r\n <Range>R3C1:R" + (dgv.RowCount + 3) + "C" + SelectedColumns.Count + "</Range>" +//R2C1:R" + dgv.RowCount + 2 if no titleHeaderCell "\r\n <Condition>" + "\r\n <Value1>MOD(ROW(),2)=1</Value1>" + "\r\n <Format Style='color:black;font-weight:700;mso-background-source:auto;mso-pattern:#FFFFCC'/>" + "\r\n </Condition>" + "\r\n <Condition>" + "\r\n <Value1>MOD(ROW(),2)=0</Value1>" + "\r\n <Format Style='color:black;font-weight:700;background:#FFCC99;mso-pattern:none'/>" + "\r\n </Condition>" + "\r\n </ConditionalFormatting>"; } private string getExcelDate(DateTime dateTime) { return dateTime.Year.ToString() + "-" + (dateTime.Month < 10 ? "0" + dateTime.Month.ToString() : dateTime.Month.ToString()) + "-" + (dateTime.Day < 10 ? "0" + dateTime.Day.ToString() : dateTime.Day.ToString()) + "T" + (dateTime.Hour < 10 ? "0" + dateTime.Hour.ToString() : dateTime.Hour.ToString()) + ":" + (dateTime.Minute < 10 ? "0" + dateTime.Minute.ToString() : dateTime.Minute.ToString()) + ":" + (dateTime.Second < 10 ? "0" + dateTime.Second.ToString() : dateTime.Second.ToString()) + ".000"; } private string getStyle(SS_STYLE style) { return "s" + (int)style; } #endregion public override void print(DataGridView dgv) { this.dgv = dgv; //setLayoutOptions(outputTitle); DialogResult dr; if (dialogOwner != null) { dr = saveFileDialog.ShowDialog(dialogOwner); } else { dr = saveFileDialog.ShowDialog(); } if (dr == DialogResult.OK) { print(saveFileDialog.FileName); } } private void print(string destinationFile) { bool writed = false; try { writer.Write(startExcelXML); //Cute sheetName because Excel don't allow more than 31 chars. string sheetName = (PrintTitle.Length<31)?PrintTitle:(PrintTitle.Remove(25)+"...");//25 to keep place to number of following sheets writer.Write("\r\n<Worksheet ss:Name=\"" + sheetName + "\">"); string tableXMLItem = "\r\n<Table ss:ExpandedColumnCount=\"" + SelectedColumns.Count + "\" ss:ExpandedRowCount=\"" + (dgv.RowCount + 2) + "\" x:FullColumns=\"1\" x:FullRows=\"1\" ss:DefaultColumnWidth=\"100\">"; writer.Write(tableXMLItem); foreach (DataGridViewColumn dgvCol in SelectedColumns) { writer.Write("\r\n <Column ss:AutoFitWidth=\"1\" ss:Width=\"100\"/>"); } writer.Write("\r\n<Row ss:Height=\"17\">");//increase it if titleHeaderCell is modified writer.Write( "\r\n <Cell ss:MergeAcross=\"{0}\" ss:StyleID=\"{1}\"><Data ss:Type=\"String\">{2} {3}</Data></Cell>", { (SelectedColumns.Count-1).ToString(), getStyle(SS_STYLE.titleHeaderCell), PrintTitle, DateTime.Now.ToLongDateString() } ); writer.Write("\r\n</Row>"); writer.Write("\r\n<Row>");// ss:Height="13.5" foreach (DataGridViewColumn dgvCol in SelectedColumns) { writer.Write("\r\n <Cell ss:StyleID=\"{0}\"><Data ss:Type=\"String\">{1}</Data></Cell>", new string[] { getStyle(SS_STYLE.headerCell), dgvCol.HeaderText }); } writer.Write("\r\n</Row>"); foreach (DataGridViewRow dgvRow in dgv.Rows) { rowCount++; //if the number of rows is > 60000 create a new page to continue output if (rowCount == 60000) { rowCount = 0; sheetCount++; writer.Write("</Table>"); writer.Write(" </Worksheet>"); writer.Write(tableXMLItem); } writer.Write("\r\n<Row>"); foreach (DataGridViewCell dgvCell in dgvRow.Cells) { if (!SelectedColumns.Contains(dgvCell.OwningColumn)) continue; System.Type rowType = dgvCell.ValueType;//dgvCell.OwningColumn.GetType(); switch (rowType.ToString()) { #region case string case "System.String": string XMLstring = dgvCell.EditedFormattedValue.ToString(); XMLstring = XMLstring.Trim(); XMLstring = XMLstring.Replace("&", "&"); XMLstring = XMLstring.Replace(">", ">"); XMLstring = XMLstring.Replace("<", "<"); writer.Write("\r\n <Cell ss:StyleID=\"{0}\"><Data ss:Type=\"String\">{1}</Data></Cell>", new string[] { getStyle(SS_STYLE.stringLiteral), XMLstring }); break; #endregion #region case DateTime case "System.DateTime": //Excel has a specific Date Format of YYYY-MM-DD followed by //the letter 'T' then hh:mm:sss.lll Example 2005-01-31T24:01:21.000 //The Following Code puts the date stored in XMLDate //to the format above string XMLDatetoString = ""; //Excel Converted Date try { DateTime XMLDate = (DateTime)dgvCell.Value; XMLDatetoString = getExcelDate(XMLDate); writer.Write("\r\n <Cell ss:StyleID=\"{0}\"><Data ss:Type=\"DateTime\">{1}</Data></Cell>", new string[] { getStyle(SS_STYLE.dateLiteral), XMLDatetoString }); } catch { writer.Write("\r\n <Cell ss:StyleID=\"{0}\"><Data ss:Type=\"String\"></Data></Cell>", new string[] { getStyle(SS_STYLE.stringLiteral) }); } break; #endregion #region case bool case "System.Boolean": 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) }); break; #endregion #region case numeric values case "System.Int16": case "System.Int32": case "System.Int64": case "System.Byte": writer.Write("\r\n <Cell ss:StyleID=\"{0}\"><Data ss:Type=\"Number\">{1}</Data></Cell>", new string[] { getStyle(SS_STYLE.dateLiteral), dgvCell.Value.ToString() }); break; case "System.Decimal": case "System.Double": writer.Write("\r\n <Cell ss:StyleID=\"{0}\"><Data ss:Type=\"Number\">{1}</Data></Cell>", new string[] { getStyle(SS_STYLE.decimalLiteral), dgvCell.Value.ToString() }); break; #endregion #region case null case "System.DBNull": writer.Write("\r\n <Cell ss:StyleID=\"{0}\"><Data ss:Type=\"String\"></Data></Cell>", new string[] { getStyle(SS_STYLE.stringLiteral) }); break; #endregion default: writer.Write("\r\n <Cell ss:StyleID=\"{0}\"><Data ss:Type=\"String\">{1}</Data></Cell>", new string[] { getStyle(SS_STYLE.stringLiteral),dgvCell.FormattedValue.ToString() }); break; } } writer.Write("</Row>"); } writer.Write("\r\n</Table>"); writer.Write(getWorkSheetOptions()); writer.Write("\r\n</Worksheet>"); writer.Write(endExcelXML); writed = true; } catch (Exception e) { ExceptionDialog.ShowDialog(e, this.dialogOwner); } finally { writer.Close(); } if (writed) { CustomOpenResultFileDialog.Show( this.dialogOwner, destinationFile, "Le fichier est sauvé.\nVous pouvez sélectionner une action à effectuer.", "Sauvegarde", MessageBoxIcon.Information ); } } private string getBoolText(bool dgvCell) { //todo : add 2 strings to use as replacement string of bool values //and add textboxes to set it on the layout options dialog return (dgvCell) ? "VRAI" : "FAUX"; } } }
Structure et Fichiers du projet
Afficher/masquer...Icône | Nom | Taille | Modification |
Pas de sous-répertoires. | |||
Icône | Nom | Taille | Modification |
| _ | Répertoire parent | 0 octets | 1730798793 05/11/2024 10:26:33 |
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.