Я пытаюсь создать файл Excel в формате xlsx с помощью OpenXML, потому что мне нужно использовать его на веб-сервере.
У меня нет проблем с заполнением значений в листах; однако я изо всех сил пытаюсь установить классический формат даты в ячейке.
Ниже быстрого теста с использованием ссылок DocumentFormat.OpenXml и WindowsBase.
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;
}
Выполненный код создает документ Excel. Однако, когда я пытаюсь открыть документ, я получаю это сообщение: "Excel нашел нечитаемый контент в" test.xlsx ". Вы хотите восстановить содержимое этой книги? Если вы доверяете источнику этой книги, нажмите" Да ".
Если я удалю строку:
cell3.StyleIndex = 1;
Я могу открыть документ, но дату, если он не отформатирован, появляется только номер даты.
Благодарим вас за помощь в форматировании даты.
Этот блог мне помог: http://polymathprogrammer.com/2009/11/09/how-to-create-stylesheet-in-excel-open-xml/
Моя проблема заключалась в том, что я хотел добавить NumingFormats в таблицу стилей, а не добавлять новую таблицу стилей. Если вы хотите, используйте
Stylesheet.InsertAt<NumberingFormats>(new NumberingFormats(), 0);
а не
Stylesheet.AppendChild<NumberingFormats>(new NumberingFormats(), 0);
удивление, количество заказов.
https://github.com/closedxml/closedxml - это, по сути, правильный ответ.
Еще один БОЛЬШОЙ БОЛЬШОЙ голос за: https://github.com/closedxml/closedxml
После того, как я попытался создать собственный класс из битов и частей, распространенных по сети, включая StackOverFlow, я нашел вышеупомянутую библиотеку и через несколько мгновений имел полностью функциональный файл Excel.
Я приложил свою попытку ниже для назидания любого, кто чувствует желание завершить его. Он частично завершен и имеет проблемы с созданием ячейки даты и строки.
Прежде чем пытаться использовать этот класс, сначала загрузите closedXML и попробуйте это сначала.
Считайте себя предупрежденным.
/// <summary>
/// This class allows for the easy creation of a simple Excel document who 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
}
Вот как применить пользовательский формат даты в ячейке. Во-первых, нам нужно искать или создавать формат в таблице стилей рабочего стола:
// get the stylesheet from the current sheet
var stylesheet = spreadsheetDoc.WorkbookPart.WorkbookStylesPart.Stylesheet;
// cell formats are stored in the stylesheet 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;
Затем мы можем применить наш формат к ячейке, используя разрешенный стиль indexex:
cell.StyleIndex = Convert.ToUInt32(dateStyleIndex);
Я считаю, что ваша проблема находится на NumberFormatId. Встроенные форматы номеров пронумерованы 0 - 163. Пользовательские форматы должны начинаться с 164.
Ваш ответ можно найти на Что указывает, что ячейка Office Open XML содержит значение Date/Time?
Фокус в том, что StyleIndex (s-attribute) ячейки буквально является индексом в списке стилей ячеек (XF-элементов) в части стилей вашей таблицы. Каждый из них укажет на предопределенные идентификаторы формата номера, которые упоминает Самуэль. Если я правильно помню, идентификатор формата номера, который вы ищете, - это 14 или 15.
У меня была такая же проблема, и я написал собственный экспорт в Excel. Код здесь, чтобы решить эту проблему, но вам действительно будет лучше, просто используя весь экспортер. Он быстрый и позволяет существенно форматировать ячейки. Вы можете просмотреть его на
https://openxmlexporttoexcel.codeplex.com/
Надеюсь, это поможет.
Я надеюсь, что следующие ссылки будут полезны для будущих посетителей.
Сначала Получить документацию по стандартам.
ECMA-376 4th Edition Часть 1 является наиболее полезным документом. Разделы этого документа, которые относятся к этому вопросу:
18.8.30
18.8.31 (семантика этого дерьмового дерьма)
18.8.45 (определение стиля, понятное для excel)
L.2.7.3.6 (Как ссылаются стили)
Я столкнулся с той же проблемой, что и поле даты форматирования после сохранения документа. И решение состоит в том, чтобы добавить формат номера следующим образом:
new NumberingFormat() { NumberFormatId = 164, FormatCode = StringValue.FromString($"[$-409]d\\-mmm\\-yyyy;@") }
и добавьте ячейку следующим образом:
cell.CellValue = new CellValue(date.ToOADate().ToString());
cell.StyleIndex = 1; // your style index using numbering format above
cell.DataType = CellValues.Number;