Format OpenXml et Date dans la cellule Excel
j'essaie de créer un fichier Excel au format xlsx en utilisant OpenXML parce que j'ai besoin de l'utiliser sur un serveur web.
Je n'ai aucun problème pour remplir les valeurs dans les feuilles; cependant, j'ai du mal à définir le format de Date classique dans une cellule.
en dessous D'un test rapide utilisant DocumentFormat.OpenXml et WindowsBase références.
class Program
{
static void Main(string[] args)
{
BuildExel(@"C:test.xlsx");
}
public static void BuildExel(string fileName)
{
using (SpreadsheetDocument myWorkbook =
SpreadsheetDocument.Create(fileName,
SpreadsheetDocumentType.Workbook))
{
// Workbook Part
WorkbookPart workbookPart = myWorkbook.AddWorkbookPart();
var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
string relId = workbookPart.GetIdOfPart(worksheetPart);
// File Version
var fileVersion = new FileVersion { ApplicationName = "Microsoft Office Excel" };
// Style Part
WorkbookStylesPart wbsp = workbookPart.AddNewPart<WorkbookStylesPart>();
wbsp.Stylesheet = CreateStylesheet();
wbsp.Stylesheet.Save();
// Sheets
var sheets = new Sheets();
var sheet = new Sheet { Name = "sheetName", SheetId = 1, Id = relId };
sheets.Append(sheet);
// Data
SheetData sheetData = new SheetData(CreateSheetData1());
// Add the parts to the workbook and save
var workbook = new Workbook();
workbook.Append(fileVersion);
workbook.Append(sheets);
var worksheet = new Worksheet();
worksheet.Append(sheetData);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
myWorkbook.WorkbookPart.Workbook = workbook;
myWorkbook.WorkbookPart.Workbook.Save();
myWorkbook.Close();
}
}
private static Stylesheet CreateStylesheet()
{
Stylesheet ss = new Stylesheet();
var nfs = new NumberingFormats();
var nformatDateTime = new NumberingFormat
{
NumberFormatId = UInt32Value.FromUInt32(1),
FormatCode = StringValue.FromString("dd/mm/yyyy")
};
nfs.Append(nformatDateTime);
ss.Append(nfs);
return ss;
}
private static List<OpenXmlElement> CreateSheetData1()
{
List<OpenXmlElement> elements = new List<OpenXmlElement>();
var row = new Row();
// Line 1
Cell[] cells = new Cell[2];
Cell cell1 = new Cell();
cell1.DataType = CellValues.InlineString;
cell1.InlineString = new InlineString { Text = new Text { Text = "Daniel" } };
cells[0] = cell1;
Cell cell2 = new Cell();
cell2.DataType = CellValues.Number;
cell2.CellValue = new CellValue((50.5).ToString());
cells[1] = cell2;
row.Append(cells);
elements.Add(row);
// Line 2
row = new Row();
cells = new Cell[1];
Cell cell3 = new Cell();
cell3.DataType = CellValues.Date;
cell3.CellValue = new CellValue(DateTime.Now.ToOADate().ToString());
cell3.StyleIndex = 1; // <= here I try to apply the style...
cells[0] = cell3;
row.Append(cells);
elements.Add(row);
return elements;
}
le code exécuté crée le document Excel. Cependant lorsque j'essaie d'ouvrir le document, je reçois ce message: " Excel trouvé du contenu illisible à l'essai.xlsx". Voulez-vous récupérer le contenu de ce classeur? Si vous faites confiance à la source de ce cahier, cliquez Oui."
Si je supprime la ligne:
cell3.StyleIndex = 1;
je peux ouvrir le document mais la date si elle n'est pas formatée, seul le numéro de la date apparaît.
merci de votre aide pour formater la date.
11 réponses
ce blog m'a aidé: http://polymathprogrammer.com/2009/11/09/how-to-create-stylesheet-in-excel-open-xml/
mon problème était que je voulais ajouter des Formatsnuméreux à la feuille de style plutôt que d'ajouter une nouvelle feuille de style tout à fait. Si vous le souhaitez, utiliser
Stylesheet.InsertAt<NumberingFormats>(new NumberingFormats(), 0);
plutôt que
Stylesheet.AppendChild<NumberingFormats>(new NumberingFormats(), 0);
surprise, l'ordre compte..
https://github.com/closedxml/closedxml est, fondamentalement, la réponse correcte je pense.
un Autre gros GROS vote pour: https://github.com/closedxml/closedxml
après avoir essayé de construire ma propre classe à partir des bits et des morceaux répartis autour du net, y compris StackOverFlow, j'ai trouvé la bibliothèque mentionnée ci-dessus et en quelques instants avait un fichier Excel entièrement fonctionnel.
j'ai collé ma tentative ci-dessous pour l'édification de quiconque ressent l'envie de la compléter. Il est partiellement terminé et a des problèmes avec la date et chaîne de cellules création.
avant d'essayer d'utiliser cette classe, téléchargez d'abord closedXML et essayez d'abord.
Considérez-vous avertis.
/// <summary>
/// This class allows for the easy creation of a simple Excel document who's sole purpose is to contain some export data.
/// The document is created using OpenXML.
/// </summary>
internal class SimpleExcelDocument : IDisposable
{
SheetData sheetData;
/// <summary>
/// Constructor is nothing special because the work is done at export.
/// </summary>
internal SimpleExcelDocument()
{
sheetData = new SheetData();
}
#region Get Cell Reference
public Cell GetCell(string fullAddress)
{
return sheetData.Descendants<Cell>().Where(c => c.CellReference == fullAddress).FirstOrDefault();
}
public Cell GetCell(uint rowId, uint columnId, bool autoCreate)
{
return GetCell(getColumnName(columnId), rowId, autoCreate);
}
public Cell GetCell(string columnName, uint rowId, bool autoCreate)
{
return getCell(sheetData, columnName, rowId, autoCreate);
}
#endregion
#region Get Cell Contents
// See: http://msdn.microsoft.com/en-us/library/ff921204.aspx
//
#endregion
#region Set Cell Contents
public void SetValue(uint rowId, uint columnId, bool value)
{
Cell cell = GetCell(rowId, columnId, true);
cell.DataType = CellValues.Boolean;
cell.CellValue = new CellValue(BooleanValue.FromBoolean(value));
}
public void SetValue(uint rowId, uint columnId, double value)
{
Cell cell = GetCell(rowId, columnId, true);
cell.DataType = CellValues.Number;
cell.CellValue = new CellValue(DoubleValue.FromDouble(value));
}
public void SetValue(uint rowId, uint columnId, Int64 value)
{
Cell cell = GetCell(rowId, columnId, true);
cell.DataType = CellValues.Number;
cell.CellValue = new CellValue(IntegerValue.FromInt64(value));
}
public void SetValue(uint rowId, uint columnId, DateTime value)
{
Cell cell = GetCell(rowId, columnId, true);
//cell.DataType = CellValues.Date;
cell.CellValue = new CellValue(value.ToOADate().ToString());
cell.StyleIndex = 1;
}
public void SetValue(uint rowId, uint columnId, string value)
{
Cell cell = GetCell(rowId, columnId, true);
cell.InlineString = new InlineString(value.ToString());
cell.DataType = CellValues.InlineString;
}
public void SetValue(uint rowId, uint columnId, object value)
{
bool boolResult;
Int64 intResult;
DateTime dateResult;
Double doubleResult;
string stringResult = value.ToString();
if (bool.TryParse(stringResult, out boolResult))
{
SetValue(rowId, columnId, boolResult);
}
else if (DateTime.TryParse(stringResult, out dateResult))
{
SetValue(rowId, columnId,dateResult);
}
else if (Int64.TryParse(stringResult, out intResult))
{
SetValue(rowId, columnId, intResult);
}
else if (Double.TryParse(stringResult, out doubleResult))
{
SetValue(rowId, columnId, doubleResult);
}
else
{
// Just assume that it is a plain string.
SetValue(rowId, columnId, stringResult);
}
}
#endregion
public SheetData ExportAsSheetData()
{
return sheetData;
}
public void ExportAsXLSXStream(Stream outputStream)
{
// See: http://blogs.msdn.com/b/chrisquon/archive/2009/07/22/creating-an-excel-spreadsheet-from-scratch-using-openxml.aspx for some ideas...
// See: http://stackoverflow.com/questions/1271520/opening-xlsx-in-office-2003
using (SpreadsheetDocument package = SpreadsheetDocument.Create(outputStream, SpreadsheetDocumentType.Workbook))
{
// Setup the basics of a spreadsheet document.
package.AddWorkbookPart();
package.WorkbookPart.Workbook = new Workbook();
WorksheetPart workSheetPart = package.WorkbookPart.AddNewPart<WorksheetPart>();
workSheetPart.Worksheet = new Worksheet(sheetData);
workSheetPart.Worksheet.Save();
// create the worksheet to workbook relation
package.WorkbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet {
Id = package.WorkbookPart.GetIdOfPart(workSheetPart),
SheetId = 1,
Name = "Sheet 1"
};
package.WorkbookPart.Workbook.GetFirstChild<Sheets>().AppendChild<Sheet>(sheet);
package.WorkbookPart.Workbook.Save();
package.Close();
}
}
#region Internal Methods
private static string getColumnName(uint columnId)
{
if (columnId < 1)
{
throw new Exception("The column # can't be less then 1.");
}
columnId--;
if (columnId >= 0 && columnId < 26)
return ((char)('A' + columnId)).ToString();
else if (columnId > 25)
return getColumnName(columnId / 26) + getColumnName(columnId % 26 + 1);
else
throw new Exception("Invalid Column #" + (columnId + 1).ToString());
}
// Given a worksheet, a column name, and a row index,
// gets the cell at the specified column
private static Cell getCell(SheetData worksheet,
string columnName, uint rowIndex, bool autoCreate)
{
Row row = getRow(worksheet, rowIndex, autoCreate);
if (row == null)
return null;
Cell foundCell = row.Elements<Cell>().Where(c => string.Compare
(c.CellReference.Value, columnName +
rowIndex, true) == 0).FirstOrDefault();
if (foundCell == null && autoCreate)
{
foundCell = new Cell();
foundCell.CellReference = columnName;
row.AppendChild(foundCell);
}
return foundCell;
}
// Given a worksheet and a row index, return the row.
// See: http://msdn.microsoft.com/en-us/library/bb508943(v=office.12).aspx#Y2142
private static Row getRow(SheetData worksheet, uint rowIndex, bool autoCreate)
{
if (rowIndex < 1)
{
throw new Exception("The row # can't be less then 1.");
}
Row foundRow = worksheet.Elements<Row>().Where(r => r.RowIndex == rowIndex).FirstOrDefault();
if (foundRow == null && autoCreate)
{
foundRow = new Row();
foundRow.RowIndex = rowIndex;
worksheet.AppendChild(foundRow);
}
return foundRow;
}
#endregion
#region IDisposable Stuff
private bool _disposed;
//private bool _transactionComplete;
/// <summary>
/// This will dispose of any open resources.
/// </summary>
public void Dispose()
{
Dispose(true);
// Use SupressFinalize in case a subclass
// of this type implements a finalizer.
GC.SuppressFinalize(this);
}
protected virtual void Dispose(bool disposing)
{
// If you need thread safety, use a lock around these
// operations, as well as in your methods that use the resource.
if (!_disposed)
{
if (disposing)
{
//if (!_transactionComplete)
// Commit();
}
// Indicate that the instance has been disposed.
//_transaction = null;
_disposed = true;
}
}
#endregion
}
Voici comment appliquer un format de date personnalisé sur une cellule. Tout d'abord, nous devons chercher ou créer le format dans la feuille de style du classeur:
// get the stylesheet from the current sheet
var stylesheet = spreadsheetDoc.WorkbookPart.WorkbookStylesPart.Stylesheet;
// cell formats are stored in the stylesheet's NumberingFormats
var numberingFormats = stylesheet.NumberingFormats;
// cell format string
const string dateFormatCode = "dd/mm/yyyy";
// first check if we find an existing NumberingFormat with the desired formatcode
var dateFormat = numberingFormats.OfType<NumberingFormat>().FirstOrDefault(format => format.FormatCode == dateFormatCode);
// if not: create it
if (dateFormat == null)
{
dateFormat = new NumberingFormat
{
NumberFormatId = UInt32Value.FromUInt32(164), // Built-in number formats are numbered 0 - 163. Custom formats must start at 164.
FormatCode = StringValue.FromString(dateFormatCode)
};
numberingFormats.AppendChild(dateFormat);
// we have to increase the count attribute manually ?!?
numberingFormats.Count = Convert.ToUInt32(numberingFormats.Count());
// save the new NumberFormat in the stylesheet
stylesheet.Save();
}
// get the (1-based) index of the dateformat
var dateStyleIndex = numberingFormats.ToList().IndexOf(dateFormat) + 1;
ensuite, nous pouvons appliquer notre format à une cellule, en utilisant le styleindex résolu:
cell.StyleIndex = Convert.ToUInt32(dateStyleIndex);
je crois que votre problème est sur NumberFormatId. Les formats de nombre intégrés sont numérotés de 0 à 163. Formats personnalisés doivent commencer à 164.
votre réponse se trouve à qu'est-ce qui indique qu'une cellule XML ouverte contient une Date/heure?
le truc est que le StyleIndex (s-attribut) de la cellule est littéralement un index dans la liste des styles de cellules (XF-elements) dans la partie styles de votre tableur. Chacun de ceux-ci pointera vers le format prédéfini de nombre ids que Samuel mentionne. Si je me souviens bien, le numéro de format id que vous recherchez est soit 14 ou 15.
j'ai eu le même problème et j'ai fini par écrire mon propre export vers Excel writer. Le code est là pour résoudre ce problème, mais vous serait vraiment préférable d'utiliser simplement l'ensemble de l'exportateur. Il est rapide et permet un formatage substantiel des cellules. Vous pouvez revoir au
https://openxmlexporttoexcel.codeplex.com/
j'espère que cela aide.
pour comprendre pourquoi les valeurs cellulaires.Type de données de Date ne fonctionne pas (du moins pas dans toutes les versions d'Excel paraît-il), veuillez vous référer à ceci:
ajouter une date dans une cellule Excel en utilisant OpenXML
Pour une complète, de travail, et bien expliqué solution veuillez vous référer à ceci:
OpenXML-écrire une date dans une feuille de calcul Excel donne un contenu illisible
après avoir essayé de nombreux messages, j'ai découvert cela .ToOADate () et CellValues.Le numéro et la cellule.StyleIndex = 4 étaient tous nécessaires...PLUS! Toutes les colonnes de date du modèle doivent être formatées selon le style de date par défaut pour que les dates puissent être filtrées en tant que dates. Sans cela, une erreur est apparue lors de l'ouverture du fichier Excel ou les valeurs ont été affichées sous forme de nombre.
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
// IMPORTANT! All template date columns MUST be formatted to the default date style for the dates to be filterable as dates
Cell cell = new Cell();
dataMember = dataMember.ToOADate().ToString(); //OA Date needed to export number as Date
cell.DataType = CellValues.Number;
cell.CellValue = new CellValue(dataMember);
cell.StyleIndex = 4; // Date format: M/d/yyyy
j'espère que les liens suivants seront utiles aux futurs visiteurs.
tout d'Abord, Obtenir de la documentation sur les normes de.
ECMA-376 4ème Édition de la Partie 1 est le plus utile du document. Les Sections du présent document qui se rapportent à cette question sont les suivantes:
18.8.30
18.8.31 (sematics de cette merde de merde)
18.8.45 (définition d'un style tel que compris par excel)
L. 2.7.3.6 (Comment les styles sont référenced)
j'ai rencontré le même problème concernant le formatage du champ date après le document de sauvegarde. Et la solution est d'ajouter le format de nombre comme suit:
new NumberingFormat() { NumberFormatId = 164, FormatCode = StringValue.FromString($"[$-409]d\-mmm\-yyyy;@") }
et ajouter de la cellule comme ceci:
cell.CellValue = new CellValue(date.ToOADate().ToString());
cell.StyleIndex = 1; // your style index using numbering format above
cell.DataType = CellValues.Number;