0

Hello Everyone i have a problem with my code. when i try to run it i got an error 101590. This is my code where i try to create new excel file and read from a.xml file. I have no idea what am i doing wrong, i have tried to follow instructions that i found on net but it seems like i am missing something. C# :

public HttpResponseMessage PrintDevices()
{
    String path = $"{Folder}/{Properties.Settings.Default.Documents_Folder_Template}";
    String template = $"{path}/a.xml";
    StreamReader sr = File.OpenText(template);
    string strSheetData = sr.ReadToEnd();

    HttpResponseMessage response = new HttpResponseMessage(HttpStatusCode.OK);

    using (MemoryStream output = new MemoryStream())
    {
        using (SpreadsheetDocument excelDoc = SpreadsheetDocument.Create(output, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
        {                 

            WorkbookPart workbookPart1 = excelDoc.AddWorkbookPart();
            WorksheetPart worksheetPart1 = workbookPart1.AddNewPart<WorksheetPart>();
            string sheetId = workbookPart1.GetIdOfPart(worksheetPart1);

            string XML = @"<?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""><sheets><sheet name=""{1}"" sheetId=""1"" r:id=""{0}"" /></sheets></workbook>";
            XML = string.Format(XML, sheetId, "Sheet1");
            using (Stream stream = workbookPart1.GetStream())
            {
                byte[] buffer = (new UTF8Encoding()).GetBytes(XML);
                stream.Write(buffer, 0, buffer.Length);
            }

            XML = @"<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes""?><worksheet xmlns=""http://schemas.openxmlformats.org/spreadsheetml/2006/main"" >{0}</worksheet>";
            XML = string.Format(XML, strSheetData);
            using (Stream stream = worksheetPart1.GetStream())
            {
                byte[] buffer = (new UTF8Encoding()).GetBytes(XML);
                stream.Write(buffer, 0, buffer.Length);
            }               
            excelDoc.Close();
        }
    }
    return response;
}

And this is my xml data inside a.xml:

<?xml version="1.0" encoding="utf-8"?>
  <sheetData>
  <row r="1">
  <c r="A1" t="inlineStr">
  <is>
    <t>Table</t>
   </is>
  </c>
  <c r="B1" t="inlineStr">
   <is>
    <t>150</t>
  </is>
    </c>
    <c r="C1" t="inlineStr">
   <is>
    <t>1</t>
   </is>
 </c>
 </row>
  ...
  ...
</sheetData>
FortyTwo
  • 2,414
  • 3
  • 22
  • 33
VasilijZ
  • 35
  • 8
  • 3
    *Don't* try to write XML by hand, there are libraries like EPPlus for this. – Panagiotis Kanavos Mar 08 '18 at 13:50
  • Did you really try to write it by hand ? https://stackoverflow.com/questions/151005/create-excel-xls-and-xlsx-file-from-c-sharp – Drag and Drop Mar 08 '18 at 13:52
  • I found this example on internet and used it as a template. – VasilijZ Mar 08 '18 at 13:56
  • With EPPlus, you can use methods like `sheet.LoadFromCollection()` or `LoadFromDatatable()`, `LoadFromReader` to load data from a collection of object, datatable or data reader directly into a sheet, using column names derived from the object properties or table fields. You can write the output directly to a stream like the web request's output stream – Panagiotis Kanavos Mar 08 '18 at 13:58
  • @VasilijZ don't use things without actually understanding what they do. Most likely this is a 10+ years old snippet - back in 2003 Excel used an intermediate single-file format, as a *very limited* alternative to the old proprietary format. It's been abandoned since 2007 when `xlsx` became the standard. – Panagiotis Kanavos Mar 08 '18 at 14:00
  • Check [EPPlus](https://github.com/JanKallman/EPPlus/wiki/Getting-Started) and the various ways it offers to [read data](https://github.com/JanKallman/EPPlus/wiki/Reading-and-Writing-Data) into a sheet – Panagiotis Kanavos Mar 08 '18 at 14:02
  • Ok, but i still have a problem when i try to create empty xlsx i got an error. I will load my data from database, but first i am trying to make it work with empty excel file. – VasilijZ Mar 08 '18 at 14:10

0 Answers0