Can't write multiple sheets

[tgulacsi]

The second ww.NewSheetWriter fails with "zip: write to closed file".

I don't know where's the error, but for example neither have found the pont where ww.headerWritten becomes true...

See https://github.com/tgulacsi/csv2xlsx for example, with more than one csv as input.

[tgulacsi]

Ok, I've digged a little bit, and don't like what I've found: after fixing headerWritten (04ba22239b31c666d5178ff00773d675d8d73466), and leaving out sw.WriteHeaders (as NewSheetWriter does it), the xlsx is written, but just unusable.

The sheets are there (after fixing the written zip archive name), has data, but can't be seen. Maybe xl/worksheets.xml should contain all of them, not just the first?

For this, a big rewrite is needed: ww.WriteHeader should be called only in Close, and it shall remember all the sheet ids, and fill worksheet.xml accordingly.

[tgulacsi]

tgulacsi@tgulacsi-Aspire-V3-371:~/src/github.com/tgulacsi/csv2xlsx/x$ unzip /tmp/x.xlsx
Archive:  /tmp/x.xlsx
  inflating: [Content_Types].xml
  inflating: docProps/app.xml
  inflating: docProps/core.xml
  inflating: _rels/.rels
  inflating: xl/workbook.xml
  inflating: xl/_rels/workbook.xml.rels
  inflating: xl/styles.xml
  inflating: xl/sharedStrings.xml
  inflating: xl/worksheets/sheet001.xml
  inflating: xl/worksheets/sheet002.xml
  inflating: xl/worksheets/sheet003.xml
  inflating: xl/worksheets/sheet004.xml
  inflating: xl/worksheets/sheet005.xml
  inflating: xl/worksheets/sheet006.xml

tgulacsi@tgulacsi-Aspire-V3-371:~/src/github.com/tgulacsi/csv2xlsx/x$ cat xl/workbook.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"><fileVersion appName="xl" lastEdited="5" lowestEdited="5" rupBuild="9303"/><workbookPr defaultThemeVersion="124226"/><bookViews><workbookView xWindow="480" yWindow="60" windowWidth="18195" windowHeight="8505"/></bookViews><sheets><sheet name="" sheetId="1" r:id="rId1"/></sheets><calcPr calcId="145621"/></workbook>

tgulacsi@tgulacsi-Aspire-V3-371:~/src/github.com/tgulacsi/csv2xlsx/x$ cat xl/_rels/workbook.xml.rels
<?xml version="1.0" encoding="UTF-8" standalone="yes"?><Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/><Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings" Target="sharedStrings.xml"/><Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/></Relationships>tgulacsi@tgulacsi-Aspire-V3-371:~/src/github.com/tgulacsi/csv2xlsx/x$

So xl/workbook.xml and xl/_rels/workbook.xml.rels needs to be adjusted, too.

[psmithuk]

I don't actually use the streaming methods of the library. That was kindly implemented by @sean-duffy and you might find it more reliable to use his fork?

[pwaller]

Hi! I'm mostly responsible for the fork and apologies that we weren't able to reconcile them before we ran out of time. You might have a bit more luck with scraperwiki/xlsx, however, we still have problems with the file not working well in Apple Numbers and other deficiencies, so we're looking at using tealg/xlsx.

[tgulacsi]

Thanks pwaller, works better, but still can't see anything in the result. What am I doing wrong?

        // first row                                                                                                                           row, err := inCSV.Read()                                                                                                               if err == io.EOF {                                                                                                                         continue
        } else if err != nil {                                                                                                                     log.Fatalf("read csv %q: %v", in, err)                                                                                             }                                                                                                                                      cols := make([]xlsx.Column, len(row))
        for i, r := range row {                                                                                                                    cols[i].Width = 15                                                                                                                     if noHeading {                                                                                                                             cols[i].Name = fmt.Sprintf("Col%d", i)
            } else {                                                                                                                                   cols[i].Name = r                                                                                                                   }                                                                                                                                  }
        sh := xlsx.NewSheetWithColumns(cols)                                                                                                   sh.Title = SheetTitles[n]                                                                                                              sw, err := outX.NewSheetWriter(&sh)                                                                                                    if err != nil {
            log.Fatalf("create new sheet for %s: %v", in, err)                                                                                 }                                                                                                                                      if err = sw.WriteHeader(&sh); err != nil {                                                                                                 log.Fatalf("write sheet header: %v", err)
        }                                                                                                                                                                                                                                                                             // remaining rows                                                                                                                      for {
            row, err := inCSV.Read()
            if err == io.EOF {                                                                                                                         break                                                                                                                              } else if err != nil {                                                                                                                     log.Fatalf("read csv %q: %v", in, err)
            }                                                                                                                                                                                                                                                                             xRow := sh.NewRow()
            for i, r := range row {
                xRow.Cells[i] = xlsx.Cell{
                    Type:  xlsx.CellTypeInlineString,
                    Value: strings.Trim(r, `"`),
                }
            }
            if err = sw.WriteRows([]xlsx.Row{xRow}); err != nil {
                log.Fatalf("write rows: %v", err)
            }
        }

Full code: https://github.com/tgulacsi/csv2xlsx/blob/scraperwiki/main.go

(No success with tealeg/xlsx, so probably I'm doing something wrong).

[pwaller]

Where can you "not see anything"? In Excel? I don't see the problem leaping out at me.

[tgulacsi]

LibreOffice, MS Excel 2013.

I've sidestepped the problem, and hacked together an ods solution: github.com/tgulacsi/csv2ods

2015-06-17 11:53 GMT+02:00 Peter Waller notifications@github.com:

Where can you "not see anything"? In Excel? I don't see the problem leaping out at me.

— Reply to this email directly or view it on GitHub https://github.com/psmithuk/xlsx/issues/8#issuecomment-112741649.