folkcoder/openspreadsheet

OpenSpreadsheet provides an easy-to-use wrapper around the OpenXML spreadsheet SAX API. It specializes in efficiently reading and writing between strongly typed collections and worksheets.

openspreadsheet's Issues

[Feature] Conditional Formatting per C# Property (Column Span)

I currently have Aspose.Cells code that looks something nasty, like this DataTable code:

        private void CreateSpreadSheet(DataTable dtValue, string reportPath)
        {
            using (var ew = Office.OfficeServiceFactory.GetExcelWriter())
            {
                const string sheetName = "Sheet1";
                ew.AddWorkSheet(sheetName);
                ew.WriteDataTable(dtValue, sheetName, "A1");
                ew.FreezeAndAutoFilterFirstRow(sheetName);
                ew.SetRowStyle(sheetName, 0, true, true);
                ew.SetCellRangeStyle(sheetName, $"B2:K{dtValue.Rows.Count + 2}", "#,##0");
                ew.SetCellRangeStyle(sheetName, $"O2:O{dtValue.Rows.Count + 2}", "#,##0");
                ew.SetCellRangeStyle(sheetName, $"P2:P{dtValue.Rows.Count + 2}", "0.00%");
                ew.AutoFitColumns(sheetName);
                for (var i = 0; i < dtValue.Rows.Count; i++)
                {
                    var row = dtValue.Rows[i];
                    if (_greaterThanTenPercent(row))
                    {
                        ew.SetBackgroundColor(sheetName, "A" + (i + 2) + ":Q" + (i + 2), Color.Yellow);
                    }
                }
                ew.FreezePanes(sheetName, 1, 1, 0, 1);
                using (var stream = new FileStream(reportPath, FileMode.Create))
                {
                    ew.Save(stream);
                }
            }
        }

It would be awesome if I could just write:

   MyDataClassMap()
   {
      References<ComplexPropertyClassMap>(m => m.ComplexProperty);
   }

and then the ComplexPropertyClassMap would look something like this:

    ComplexPropertyClassMap()
    {
      Map(m => m.SomeDouble).FormatNumber();
      Map(m => m.SomeOtherDouble).ConditionalFormat(m => m > 0.1, cell => cell.BackgroundColor = BackgroundColor.Red).FormatPercent();
    }

Remove tight coupling to filepaths

As discussed on Reddit.

WorksheetStyle.ShouldFreezeTopRow not general enough

Not clear how it interacts with HeaderIndexRow property.

Problems arise when you have multi-row headers, such as when the first header row is used to span three columns of text to avoid repeating text in each column, such as "Total" top row span of "Cost | Profit"

Extract Benchmarks project into its own GitHub repo

This would help foster competition across different libraries

Document how to do single iteration of IEnumerable

From reddit:

using (var spreadsheet = new Spreadsheet(filepath))
{
    var records = spreadsheet.ReadWorksheet<TestClass, TestClassMap>("Sheet1");

    using (var writerNegative = spreadsheet.CreateWorksheetWriter<TestClass, TestClassMap>("Negative"))
    using (var writerPositive = spreadsheet.CreateWorksheetWriter<TestClass, TestClassMap>("Positive"))
    {
        foreach (var record in records)
        {
            if (record.Amount < 0)
            {
                writerNegative.WriteRecord(record);
            }
            else
            {
                writerPositive.WriteRecord(record);
            }
        }
    }
}

Add FAQ section to GitHub README

Example questions:

1. Why would I use OpenSpreadsheet over Microsoft's Open XML API?
2. Why would I use OpenSpreadsheet over ClosedXML?
   A: When the author tried using ClosedXML for his business purposes, it constantly ran out of memory when writing large datasets, and it seemed a lot of work to get it to write a simple IEnumerable. (Would be good to show comparison code.)
   A: ClosedXML has some performance issues saving large sheets, due to slow GetHashCode implementations that allocate objects on the heap.
   A: ClosedXML has a memory leak where on consecutive runs of an open-edit-save workflow, memory balloons until the program crashes. ClosedXML/ClosedXML#607
3. How do I write fluent tests with OpenSpreadsheet?
4. What runtimes does OpenSpreadsheet support?
5. This is a great idea. How can I help?
   -- suggest you request help for TravisCI automation or equivalent, so that you can add maintainers other than yourself going forward when life gets hard.

[Feature] Transpose columns and rows prior to applying reading rules

We have some spreadsheets where users would rather enter data as rows instead of columns, and our plan is to just use Excel Paste as Transpose manually for now, but it would be great if a library did this automatically. This is done for practical reasons: There are about 180 fields but only 2-3 data points per day, so it's easier for users to enter things vertically and use a freeze pane and scroll vertically than to scroll horizontally.

Here is a heavily blurred image to give you an idea of what I mean.