I could not find a single mention of read from Excel file in the project document, nor from the samples. All use cases of spoiwo are for write-only. Is this project only able to create Excel files but not able to read from it?

Hi,I think add a gitter chat room for this project would be great,better for communicating and collaborating:)

I just come to this project,and want to make use of it.

seems currently it did not support convert from xlsx to model.I think it maybe more target on generating things currently.

I have a netmate in China who have a project do the generating things too,here is the link:
https://github.com/scalax/poi-collection

Is there a way to specify which actions are allowed in Excel when protecting it with a password? I can locked the cells but then I want to allow the user to perform other things like when you protect the worksheet in Excel, you can specify which ones are allowed.

Hi ,

any estimation when will be next release?(v1.0.7)
i would like to use the password protection feature(not in 1.0.6)

The library lacks image support, in particular inserting image in header.

Hi.

I read in the docs that spoiwo provides spreadsheet HTML representation but I cant "find it"
Can you provide an example?

Thanks a lot!

in com.norbitltd.spoiwo.natures.xlsx.Model2XlsxConversions
val dateTime = LocalDateTime.fromDateFields(value) blows up because LocalDateTime.fromDateFields is deprecated.

I was trying to draw a border for a region and came across the RegionUtil which is not implemented here. Maybe it is possible to expose the underlying java object for some classes (workbook, sheet) to enable easy use of Java utils.

The Apache POI team want to remove any public APIs that expose the underlying xmlbeans generated classes for dealing with the OOXML format.
Model2XlsxConversions.scala has some usages of classes like CTTable.
If there are missing APIs that the POI team need to create to facilitate this, please open an issue on bugzilla.
Relates to the discussion in https://bz.apache.org/bugzilla/show_bug.cgi?id=59268 and https://bz.apache.org/bugzilla/show_bug.cgi?id=61949

I'm unable to create spreadsheet cells with date contents, without explicitly applying a CellStyle to them.

Expecting:

• Creating a Cell from a value of java.unit.Date (or JodaTime DateTime value, seems to get converted) should show up in Excel and OpenOffice as a date, with default formatting (depending on the locale)

Actual:

• The above causes dates to be shown as numbers s.a. 41928,5678755903

If I apply (to the cell or the resulting column): CellStyle( dataFormat = CellDataFormat( "m/d/yy h:mm:ss" ), the dates show with the provided format. However, this causes weird effects based on locale: e.g. for the Finnish locale, the above gets changed to "m.d.yy h:mm:ss" which is not acceptable (our default would be "d.m.yyyy h:mm:ss").

This is with Spoiwo 1.0.6, Scala 2.11.5 and JRE 8.0u25

resolved

Thank you for maintaining such an amazing library.

I've tried to insert images, the images are there but not with the size expected.
Changing the line

by :

pict.resize(1.0)

it works quite as I expected, tested on MS Excel and Calc LibreOffice.

Changing a little SpoiwoExamples.sc should insert 8 images, each on 1 line and column increasing by 1

  def insertImage() = Sheet(name = "Sheet1",
    images =(1 to 8).map(i =>
        Image(CellRange(i -> (i+1), 1 -> i), "examples/src/main/resources/einstein.jpg")
      ).toList
  ).saveAsXlsx("workbook.xlsx")

gives:
with just resize():

with resize(1.0):

Repro1:
Create a line, and a cell, add a border to the cell, merge the cell with others, the border will be only around the original cell.

Repro2:
Create a line, add multiple cells all with bordered, merge them, still only the first cells original borders are appear, the other cells borders are missing.

I didn't look into the code yet, but this is hurting my sheets :D

Hi Norbert

Under Mac OS X Yosemite the Model2XlsxConversionsForColumnSpec fails with the following message:

- should resize column width to the content when auto-sized set to true *** FAILED ***
  2157 did not equal 2129 (Model2XlsxConversionsForColumnSpec.scala:84)

This doesn't seem to be an issue with spoiwo itself : Apache POI seems to defer the choice of default font to the OS which is going to be different depending on platform, as such the widths of the characters will be different.

The quick fix is to change the test semantics to just ensure the width has changed from the default and hope the test data is wide enough to trigger a change. An alternative is to change the font used for the test to be fixed between platforms.

I'll put together a fix branch and raise a pull request.

I have a problem that may be related with spoiwo.

Here is the Stackoverflow question I created:

https://stackoverflow.com/questions/55899139/excel-poi-we-found-a-problem-with-some-content-in-when-the-excel-has-les

There is suggested that I need to close the Workbook - which is not done in the spoiwo code.

There is a super function on the HSSFSheet:
http://poi.apache.org/apidocs/dev/org/apache/poi/hssf/usermodel/HSSFSheet.html#autoSizeColumn-int-

Would be nice to add this to the sheet.

My current workaround if sb interested:

    import collection.JavaConverters._
    Future {
      val output = new ByteArrayOutputStream()
      val cols   = grid.width
      val wb = Sheet(name = sheetName)
        .withGrid(grid)
        .convertAsXlsx
      wb.sheetIterator.asScala
        .foreach(s => (0 to cols).foreach(i => s.autoSizeColumn(i, true)))
      wb.write(output)
      output.toByteArray
    }

Hi,

I am a noob with sbt. I installed the last sbt version 0.11.3. When I tried to run the build using sbt, I get an error:

cdelarue@host /cygdrive/u/spoiwo-master
$ "d:/Program Files (x86)/sbt/bin/sbt"
[error]
[error] [U:\\spoiwo-master\build.sbt]:22: ';' expected but 'val' found.
[error] Probably problem with parsing xml group, please add parens or semicolons                                                                                          :
[error] Replace:
[error] val xmlGroup = <a/><b/>
[error] with:
[error] val xmlGroup = (<a/><b/>)
[error] or
[error] val xmlGroup = <a/><b/>;
[error]
[error]
Project loading failed: (r)etry, (q)uit, (l)ast, or (i)gnore? q

After adding the requested trailing ; at the end of pomdetails, sbt becomes must more happy :D

Pattern matching is not exhaustive when calling Row.withCellValues and throws a exception when passed an HyperLinkUrl type: java.lang.UnsupportedOperationException: Unable to construct cell from class com.norbitltd.spoiwo.model.HyperLinkUrl type value!

Apache POI has a feature to create a hyperlink to a cell in the same document. This feature is enabled using enum value Hyperlink.LINK_DOCUMENT:

Hyperlink link2 = createHelper.createHyperlink(Hyperlink.LINK_DOCUMENT);
link2.setAddress("'Target Sheet'!A1");
cell.setHyperlink(link2);
cell.setCellStyle(hlink_style);

(see http://poi.apache.org/components/spreadsheet/quick-guide.html#Hyperlinks)

It looks like in this line

protected[natures] def setHyperLinkUrlCell(cell: usermodel.Cell, value: HyperLinkUrl, row: usermodel.Row): Unit = {
    val link = row.getSheet.getWorkbook.getCreationHelper.createHyperlink(HyperlinkType.URL)
    link.setAddress(value.address)
    cell.setCellValue(value.text)
    cell.setHyperlink(link)
  }

only supports URL hyperlinks.
Am I missing something?

What is the best way to add support of different links to the model?

Is there support for SXSSF? I may need to generate large files for many users, so really would like to use as little RAM as possible.

http://poi.apache.org/spreadsheet/how-to.html#SXSSF

First of all great work!!!

Is it possible to head Column filters in the columns headers?

Thanks

This is due to the fact that Option(style.withWrapText) can be called on the null style. Cell:31

I wanted to set cell direction (not CellHorizontalAlignment, I am looking for something like Sheet(properties = SheetProperties(rightToLeft = true)) for cells), but I could not find anything to do that.
I created a file and checked the styles. I think readingOrder in the alignment element does this.
I also found this class org.apache.poi.ss.usermodel.ReadingOrder (hope it is useful).

It is not clear why the dependency is there. I managed to run sbt +test and sbt +publishLocal w/ it being removed.

The reason why the change is viable - it makes library incompatible (build failures because of "incompatible dependency version") with play-framework using SBT versions > 1.5.0.

No default date style format is applied to xlsx cells from the models, so DateUtil.isCellDateFormatted(xlsx) cannot detect that cell is in date format

val model = Cell(new LocalDate(2011, 11, 13).toDate)
val xlsx = convert(model)

assert(DateUtil.isCellDateFormatted(xlsx))  // fails

Hi Norbert,

Thanks for the library. We have discovered that cellStyleCache,dataFormatCache, fontCache in Model2XlsxConversions are not getting cleaned up and after workbook got created.

I would expect that empty cells have their cell type set to
org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK

But this does not seem to be the case so I end up doing something like this:
import com.norbitltd.spoiwo.natures.xlsx.Model2XlsxConversions._

    //wb is a spoiwo workbook 
    val rawPoiWorkbook = wb.convertAsXlsx()

        rawPoiWorkbook.foreach(sheet =>
            sheet.foreach(row =>
                row.foreach(cell =>
                    if (cell != null && 
                        cell.getCellType == org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING &&
                        cell.getStringCellValue == "")
                        cell.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK)
                )
            )
        )

At the moment style can be only set on Row, Column or Cell level. Defaulting the whole sheet would be greatly beneficial.

Warning:scalac: Class org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSortState not found - continuing with a stub.
Warning:scalac: Class org.openxmlformats.schemas.spreadsheetml.x2006.main.CTExtensionList not found - continuing with a stub.
Warning:scalac: Class org.openxmlformats.schemas.spreadsheetml.x2006.main.STTableType not found - continuing with a stub.

What does it means? how can i avoid these messages?

Any releases planned soon?

Using Spoiwo 1.6.0, I noticed that giving Color(0,0,0) causes a font to get white, and Color(255,255,255) to get black.

Other values (s.a. (0,0,1)) work as they should.

This looks like a bug.

    font = Font( color = Color(0,0,0), bold =true )