c# - Using Open Xml to create xlsx file for Excel 2013 -
i need create excel file xlsx. so, decided use open xml (open xml 2.0 because have use .net 3.5).
after research open xml, created class :
public class excelfilemanager : filemanager { private spreadsheetdocument _package; private row _currentrow; private sheetdata _sheetdata; private int? _cellcolumnn2; private int _cellcolumnn1; private int _cellrow; public excelfilemanager(string path) : base(path) { _package = spreadsheetdocument.create(path, documentformat.openxml.spreadsheetdocumenttype.workbook); _cellcolumnn1 = 65; _cellrow = 1; createspreadsheet(); } private void createspreadsheet() { var workbookpart1 = _package.addworkbookpart(); workbookpart1.workbook = new workbook(); var worksheetpart1 = workbookpart1.addnewpart<worksheetpart>(); worksheetpart1.worksheet = new worksheet(); var sheets = _package.workbookpart.workbook.appendchild<sheets>(new sheets()); var sheet = new sheet() { id = _package.workbookpart. getidofpart(worksheetpart1), sheetid = 1, name = "users" }; sheets.append(sheet); _sheetdata = new sheetdata(); _currentrow = new row(); _sheetdata.append(_currentrow); worksheetpart1.worksheet.append(_sheetdata); } public override void addheader(ienumerable<string> columnsname) { if (columnsname == null || !columnsname.any()) throw new argumentnullexception("columnsname"); try { addrow(columnsname.toarray()); } catch (exception e) { tracing.application.traceerror(-1, "an error occured when adding header excel file", e); throw; } } public override void addrow(params object[] values) { if (values == null || !values.any()) throw new argumentnullexception("values"); try { var listcells = new list<openxmlelement>(); foreach (var item in values) { listcells.add(createcellrow(item)); } _currentrow.append(listcells); addnewrow(); } catch (exception e) { tracing.application.traceerror(-1, "an error occured when adding row excel file", e); throw; } } public override void addcell(object value) { try { var cell = createcellrow(value); _currentrow.append(cell); } catch (exception e) { tracing.application.traceerror(-1, "an error occured when adding cell excel file", e); throw; } } public override void addnewrow() { _currentrow = new row(); _sheetdata.append(_currentrow); increaserow(); } #region utils private cell createcellrow(object value) { tracing.application.traceinformation("add value \"{0}\" in cell {1}", value, getcelllocation()); cell cell = new cell(); if (value == null) { cell.cellvalue = new cellvalue(string.empty); cell.datatype = cellvalues.string; } else { cell.cellvalue = new cellvalue(value.tostring()); cell.datatype = getcelltype(value); } cell.cellreference = getcelllocation(); increasecellcolumn(); return cell; } private cellvalues getcelltype(object value) { cellvalues type = cellvalues.string; if (value.gettype() == typeof(datetime) || value.gettype() == typeof(datetime?)) type = cellvalues.date; if (value.gettype() == typeof(bool) || value.gettype() == typeof(bool?)) type = cellvalues.boolean; if (isnumerictype(value.gettype())) type = cellvalues.number; return type; } public bool isnumerictype(type type) { typecode typecode = type.gettypecode(type); //the typecode of numerical types between sbyte (5) , decimal (15). return (int)typecode >= 5 && (int)typecode <= 15; } private void increasecellcolumn() { if (_cellcolumnn1 < 90) _cellcolumnn1++; else { _cellcolumnn1 = 65; if (!_cellcolumnn2.hasvalue) _cellcolumnn2 = 65; else _cellcolumnn2++; } } private void increaserow() { _cellrow++; _cellcolumnn1 = 65; _cellcolumnn2 = null; } private string getcelllocation() { char? c1 = null; if (_cellcolumnn2.hasvalue) c1 = (char)_cellcolumnn2; char c2 = (char)_cellcolumnn1; return string.format("{0}{1}{2}", c1, c2, _cellrow); } #endregion #region idisposable public override void dispose() { if (_package != null) { _package.workbookpart.workbook.save(); _package.close(); } } #endregion } when create excel file using class, error message displayed : "we found error in content..." excel repairs file , become openable. why have error in excel ?
i looked bit in xml file sheet.xml contained in xlsx file, there few differences :
generated file :
<x:worksheet xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> <x:sheetdata> <x:row> <x:c r="a1" t="str"> <x:v>a string</x:v> </x:c> <x:c r="b1" t="str"> <x:v>another string</x:v> </x:c> </x:row> ... </x:sheetdata> </x:worksheet> repaired file :
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officedocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:ignorable="x14ac" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac"> <dimension ref="a1:s12"/> <sheetviews> <sheetview tabselected="1" workbookviewid="0"> <selection activecell="i11" sqref="i11"/> </sheetview> </sheetviews> <sheetformatpr basecolwidth="10" defaultrowheight="15" x14ac:dydescent="0.25"/> <sheetdata> <row r="1" spans="1:19" x14ac:dydescent="0.25"> <c r="a1" t="s"> <v>0</v> </c> <c r="b1" t="s"> <v>1</v> </c> <c r="c1" t="s"> <v>2</v> </c> </row> ... </sheetdata> <pagemargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/> </worksheet> i tried change namespaces, didn't work. know shared strings used in second xml, don't think it's problem. ^^ don't care style page margin, font,... (may auto adjust width columns useful ^^)
i want formed xlsx file.
do have mistake or omission in code ?
sorry english way '^^
thanks.
dates stored numbers type number, style applied them. booleans stored numbers, type boolean.
private cell createcellrow(object value) { cell cell = new cell(); cell.cellreference = getcelllocation(); increasecellcolumn(); if (value == null) { cell.datatype = cellvalues.inlinestring; cell.inlinestring = new inlinestring { text = new text("") }; return cell; } switch (type.gettypecode(value.gettype())) { case typecode.datetime: cell.styleindex = 1; cell.cellvalue = new cellvalue(((datetime)value).tooadate().tostring(cultureinfo.invariantculture)); break; case typecode.string: cell.datatype = cellvalues.inlinestring; cell.inlinestring = new inlinestring {text = new text((string) value)}; break; case typecode.boolean: cell.datatype = cellvalues.boolean; cell.cellvalue = new cellvalue((bool)value ? "1" : "0"); break; case typecode.byte: case typecode.int16: case typecode.int32: case typecode.int64: case typecode.sbyte: case typecode.uint16: case typecode.uint32: case typecode.uint64: case typecode.decimal: case typecode.double: case typecode.single: cell.cellvalue = new cellvalue(((iformattable)value).tostring(null, cultureinfo.invariantculture)); break; default: throw new exception("unrecognized type: " + value.gettype().fullname); } return cell; } to create style needed (snipped another answer):
var stylespart = workbookpart.addnewpart<workbookstylespart>(); stylespart.stylesheet = createstylesheet(); private stylesheet createstylesheet() { var stylesheet = new stylesheet(); // default font var fonts = new fonts() { count = 1, knownfonts = booleanvalue.fromboolean(true) }; var font = new font { fontsize = new fontsize() { val = 11 }, fontname = new fontname() { val = "calibri" }, fontfamilynumbering = new fontfamilynumbering() { val = 2 }, fontscheme = new fontscheme() { val = new enumvalue<fontschemevalues>(fontschemevalues.minor) } }; fonts.append(font); stylesheet.append(fonts); // default fill var fills = new fills() { count = 1 }; var fill = new fill(); fill.patternfill = new patternfill() { patterntype = new enumvalue<patternvalues>(patternvalues.none) }; fills.append(fill); stylesheet.append(fills); // default border var borders = new borders() { count = 1 }; var border = new border { leftborder = new leftborder(), rightborder = new rightborder(), topborder = new topborder(), bottomborder = new bottomborder(), diagonalborder = new diagonalborder() }; borders.append(border); stylesheet.append(borders); // default cell format , date cell format var cellformats = new cellformats() { count = 2 }; var cellformatdefault = new cellformat { numberformatid = 0, formatid = 0, fontid = 0, borderid = 0, fillid = 0 }; cellformats.append(cellformatdefault); var cellformatdate = new cellformat { numberformatid = 22, formatid = 0, fontid = 0, borderid = 0, fillid = 0, applynumberformat = booleanvalue.fromboolean(true) }; cellformats.append(cellformatdate); stylesheet.append(cellformats); return stylesheet; }
Comments
Post a Comment