I need to export dataset data to Excel into multiple worksheets without using Excel Interop, ClosedXML, or any other component and without JavaScript. For single worksheet using StreamWriter.
Thanks.
Trying to export dataset to Excel without any external components and without javascript.
New contributor
exp is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
2
Here the sample code to write some information:
using System.Reflection;
using System.IO.Compression;
using System.Text;
class Aclass {
public int I { get; }
public double D { get; }
public string S { get; }
public DateTime T { get; }
public Aclass(int i, double d, string s, DateTime t) { I = i; D = d; S = s; T = t; }
}
static class Program {
static void Main() {
List<Aclass> objs = [new Aclass(1, 1.6, "Gc", new DateTime(1940, 6, 12)),
new Aclass(2, 2.7, "E", new DateTime(2000, 1, 22, 16, 25, 17)),
new Aclass(3, 3.1, "Pi", new DateTime(1920, 9, 4))];
CsvToXlsx(objs);
}
public static bool CsvToXlsx(IEnumerable<object> data, string fileName = "file.xlsx") {
var sheetXml = new StringBuilder();
bool result = false;
sheetXml.Append("""
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<sheetData>
""");
var asd = data.First();
PropertyInfo[] props = data.First().GetType().GetProperties();
sheetXml.Append("rn<row>");
for (int i = 0; i < props.Length; i++) {
sheetXml.Append($"""<c r="{(char)('A' + i)}1" t="inlineStr"><is><t>{props[i].Name}</t></is></c>""");
}
sheetXml.Append("</row>rn");
int indexRow = 1;
foreach (var dataItem in data) {
IEnumerable<object> values =
props.Select(x => dataItem.GetType().GetProperty(x.Name)?.GetValue(dataItem, null));
sheetXml.Append("<row>");
int indexCol = 0;
foreach (var value in values) {
var val = "";
switch (props[indexCol].PropertyType.Name) {
case "String": val = $""" t="inlineStr"><is><t>{value}</t></is>"""; break;
case "DateTime": val = $""" s="2"><v>{((DateTime)value).ToOADate()}</v>"""; break;
case "Int32": case "Double": val = $"""><v>{value}</v>"""; break;
}
sheetXml.Append(
$"""<c r="{(char)('A' + indexCol)}{indexRow + 1}"{val}</c>""");
indexCol++;
}
sheetXml.Append("</row>rn");
indexRow++;
}
sheetXml.Append("</sheetData></worksheet>");
// Console.WriteLine(sheetXml.ToString());
using (var memoryStream = new MemoryStream()) {
using (var archive = new ZipArchive(memoryStream, ZipArchiveMode.Create, true)) {
// Add xl/worksheets/sheet1.xml
var sheetXmlEntry = archive.CreateEntry("xl/worksheets/sheet1.xml");
using (var stream = sheetXmlEntry.Open())
using (var writer = new StreamWriter(stream, Encoding.UTF8)) {
writer.Write(sheetXml.ToString());
}
// Add xl/worksheets/_rels/sheet1.xml.rels
var worksheetRelsXml = archive.CreateEntry("xl/worksheets/_rels/sheet1.xml.rels");
using (var stream = worksheetRelsXml.Open())
using (var writer = new StreamWriter(stream, Encoding.UTF8)) {
writer.WriteLine(@"<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes""?>
<Relationships xmlns=""http://schemas.openxmlformats.org/package/2006/relationships""></Relationships>");
}
// Add xl/_rels/workbook.xml.rels
var workbookRelsXml = archive.CreateEntry("xl/_rels/workbook.xml.rels");
using (var stream = workbookRelsXml.Open())
using (var writer = new StreamWriter(stream, Encoding.UTF8)) {
writer.WriteLine(@"<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes""?>
<Relationships xmlns=""http://schemas.openxmlformats.org/package/2006/relationships"">
<Relationship Target=""styles.xml"" Type=""http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles"" Id=""rId3""/>
<Relationship Target=""worksheets/sheet1.xml"" Type=""http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"" Id=""rId1""/>
</Relationships>");
}
// Add xl/workbook.xml
var workbookXml = archive.CreateEntry("xl/workbook.xml");
using (var stream = workbookXml.Open())
using (var writer = new StreamWriter(stream, Encoding.UTF8)) {
writer.WriteLine(@"<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes""?>
<workbook 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"" xmlns:x15=""http://schemas.microsoft.com/office/spreadsheetml/2010/11/main"" xmlns:xr=""http://schemas.microsoft.com/office/spreadsheetml/2014/revision"" xmlns:xr6=""http://schemas.microsoft.com/office/spreadsheetml/2016/revision6"" xmlns:xr10=""http://schemas.microsoft.com/office/spreadsheetml/2016/revision10"" xmlns:xr2=""http://schemas.microsoft.com/office/spreadsheetml/2015/revision2"" mc:Ignorable=""x15 xr xr6 xr10 xr2"">
<fileVersion appName=""xl"" lastEdited=""7"" lowestEdited=""7"" rupBuild=""27628""/>
<workbookPr defaultThemeVersion=""202300""/>
<mc:AlternateContent xmlns:mc=""http://schemas.openxmlformats.org/markup-compatibility/2006"">
<mc:Choice Requires=""x15"">
<x15ac:absPath xmlns:x15ac=""http://schemas.microsoft.com/office/spreadsheetml/2010/11/ac"" url=""C:UsersashatikhinDocumentsMy ProjectsStackoverflowExcel""/>
</mc:Choice>
</mc:AlternateContent>
<xr:revisionPtr revIDLastSave=""0"" documentId=""13_ncr:1_{6061A8F0-A696-4E68-B28C-0B5D47BE4AA6}"" xr6:coauthVersionLast=""47"" xr6:coauthVersionMax=""47"" xr10:uidLastSave=""{00000000-0000-0000-0000-000000000000}""/>
<bookViews>
<workbookView xWindow=""-120"" yWindow=""-120"" windowWidth=""29040"" windowHeight=""18240"" xr2:uid=""{E848225D-D00B-4CE1-9C05-A44E208BC384}""/>
</bookViews>
<sheets>
<sheet name=""Sheet1"" sheetId=""1"" r:id=""rId1""/>
</sheets>
<calcPr calcId=""191029""/>
<extLst>
<ext xmlns:x15=""http://schemas.microsoft.com/office/spreadsheetml/2010/11/main"" uri=""{140A7094-0E35-4892-8432-C4D2E57EDEB5}"">
<x15:workbookPr chartTrackingRefBase=""1""/>
</ext>
<ext xmlns:xcalcf=""http://schemas.microsoft.com/office/spreadsheetml/2018/calcfeatures"" uri=""{B58B0392-4F1F-4190-BB64-5DF3571DCE5F}"">
<xcalcf:calcFeatures>
<xcalcf:feature name=""microsoft.com:RD""/>
<xcalcf:feature name=""microsoft.com:Single""/>
<xcalcf:feature name=""microsoft.com:FV""/>
<xcalcf:feature name=""microsoft.com:CNMTM""/>
<xcalcf:feature name=""microsoft.com:LET_WF""/>
<xcalcf:feature name=""microsoft.com:LAMBDA_WF""/>
<xcalcf:feature name=""microsoft.com:ARRAYTEXT_WF""/>
</xcalcf:calcFeatures>
</ext>
</extLst>
</workbook>");
}
// Add xl/workbook.xml
var styleSheetXml = archive.CreateEntry("xl/styles.xml");
using (var stream = styleSheetXml.Open())
using (var writer = new StreamWriter(stream, Encoding.UTF8)) {
writer.WriteLine(@"<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes""?>
<styleSheet xmlns=""http://schemas.openxmlformats.org/spreadsheetml/2006/main"" xmlns:mc=""http://schemas.openxmlformats.org/markup-compatibility/2006"" xmlns:x14ac=""http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac"" xmlns:x16r2=""http://schemas.microsoft.com/office/spreadsheetml/2015/02/main"" xmlns:xr=""http://schemas.microsoft.com/office/spreadsheetml/2014/revision"" mc:Ignorable=""x14ac x16r2 xr"">
<fonts count=""1"" x14ac:knownFonts=""1"">
<font>
<sz val=""11""/>
<color theme=""1""/>
<name val=""Aptos Narrow""/>
<family val=""2""/>
<charset val=""204""/>
<scheme val=""minor""/>
</font>
</fonts>
<fills count=""2"">
<fill>
<patternFill patternType=""none""/>
</fill>
<fill>
<patternFill patternType=""gray125""/>
</fill>
</fills>
<borders count=""1"">
<border>
<left/>
<right/>
<top/>
<bottom/>
<diagonal/>
</border>
</borders>
<cellStyleXfs count=""1"">
<xf numFmtId=""0"" fontId=""0"" fillId=""0"" borderId=""0""/>
</cellStyleXfs>
<cellXfs count=""4"">
<xf numFmtId=""0"" fontId=""0"" fillId=""0"" borderId=""0"" xfId=""0""/>
<xf numFmtId=""16"" fontId=""0"" fillId=""0"" borderId=""0"" xfId=""0"" applyNumberFormat=""1""/>
<xf numFmtId=""14"" fontId=""0"" fillId=""0"" borderId=""0"" xfId=""0"" applyNumberFormat=""1""/>
<xf numFmtId=""20"" fontId=""0"" fillId=""0"" borderId=""0"" xfId=""0"" applyNumberFormat=""1""/>
</cellXfs>
<cellStyles count=""1"">
<cellStyle name=""Normal"" xfId=""0"" builtinId=""0""/>
</cellStyles>
<dxfs count=""0""/>
<tableStyles count=""0"" defaultTableStyle=""TableStyleMedium2"" defaultPivotStyle=""PivotStyleLight16""/>
<extLst>
<ext xmlns:x14=""http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"" uri=""{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}"">
<x14:slicerStyles defaultSlicerStyle=""SlicerStyleLight1""/>
</ext>
<ext xmlns:x15=""http://schemas.microsoft.com/office/spreadsheetml/2010/11/main"" uri=""{9260A510-F301-46a8-8635-F512D64BE5F5}"">
<x15:timelineStyles defaultTimelineStyle=""TimeSlicerStyleLight1""/>
</ext>
</extLst>
</styleSheet>");
}
// Add _rels/.rels
var relsXml = archive.CreateEntry("_rels/.rels");
using (var stream = relsXml.Open())
using (var writer = new StreamWriter(stream, Encoding.UTF8)) {
writer.WriteLine(@"<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes""?>
<Relationships xmlns=""http://schemas.openxmlformats.org/package/2006/relationships"">
<Relationship Target=""xl/workbook.xml"" Type=""http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument"" Id=""rId1""/>
</Relationships>");
}
// Add [Content_Types].xml
var contentTypesXml = archive.CreateEntry("[Content_Types].xml");
using (var stream = contentTypesXml.Open())
using (var writer = new StreamWriter(stream, Encoding.UTF8)) {
writer.WriteLine(@"<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes""?>
<Types xmlns=""http://schemas.openxmlformats.org/package/2006/content-types"">
<Default Extension=""bin"" ContentType=""application/vnd.openxmlformats-officedocument.spreadsheetml.printerSettings""/>
<Default Extension=""rels"" ContentType=""application/vnd.openxmlformats-package.relationships+xml""/>
<Default Extension=""xml"" ContentType=""application/xml""/>
<Override PartName=""/xl/workbook.xml"" ContentType=""application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml""/>
<Override PartName=""/xl/worksheets/sheet1.xml"" ContentType=""application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml""/>
<Override PartName=""/xl/styles.xml"" ContentType=""application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml""/>
</Types>");
}
}
memoryStream.Flush(); memoryStream.Position = 0;
using (var fs = new FileStream(fileName, FileMode.Create)) memoryStream.WriteTo(fs);
}
result = true;
return result;
}
}