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 | 1732283602 22/11/2024 14:53:22 |
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.
Nederlandse vertaling
U hebt gevraagd om deze site in het Nederlands te bezoeken. Voor nu wordt alleen de interface vertaald, maar nog niet alle inhoud.Als je me wilt helpen met vertalingen, is je bijdrage welkom. Het enige dat u hoeft te doen, is u op de site registreren en mij een bericht sturen waarin u wordt gevraagd om u toe te voegen aan de groep vertalers, zodat u de gewenste pagina's kunt vertalen. Een link onderaan elke vertaalde pagina geeft aan dat u de vertaler bent en heeft een link naar uw profiel.
Bij voorbaat dank.
Document heeft de 16/10/2009 gemaakt, de laatste keer de 26/10/2018 gewijzigd
Bron van het afgedrukte document:https://www.gaudry.be/nl/cs-broldev-source-rf-view/utils/dgvFactory/ExcelDgv.cs.html
De infobrol is een persoonlijke site waarvan de inhoud uitsluitend mijn verantwoordelijkheid is. De tekst is beschikbaar onder CreativeCommons-licentie (BY-NC-SA). Meer info op de gebruiksvoorwaarden en de auteur.