No matter how much I looked for, everything was somehow painfully complex and redundantly functional ...

And I wanted maximum simplicity - to work with the table as with a two-dimensional array of rows.

The answer to which I came is below :)

Full CSV support should include:

  1. ability to change delimiter
  2. reading cells between delimiters
  3. read lines
  4. if the delimiter character is in the cell, the cell must be enclosed in quotes and normally processed by the reader itself.
  5. if the sign of the transition to the next line is in the cell, the cell must be enclosed in quotes and normally processed by the reader itself.
  6. if a cell is highlighted with quotes, and inside there are quotes, then they must also be processed without errors.
  • works well only with no more than 26 speakers - Konstantin Prosekin
  • ... if by work with Excel you understand the simple reading / saving of data is one thing, if charting is something else completely - Ev_Hyper
  • @KonstantinProsekin did not check, there were no need for so many columns somehow. If I do not forget, I will check and correct it in a couple of weeks. Now there is no possibility :) - Andrew
  • @Ev_Hyper most people under the most frequent tasks is enough to read / save data. :) - Andrew

1 answer 1

EXCEL:

Here I have written a very simple ClosedXML-based library in order to be able to work with MS Excel tables as a two-dimensional array without thinking: what could be easier to use?

An example of the final code for working with my class:

Excel xl = new Excel(); //создаем инстанс xl.FileOpen("c:\\file1.xlsx"); //открываем файл var row1Cell6Value = xl.Rows[0][5]; //вытягиваем значение из 1 строки 6й ячейки xl.AddRow("asdf","asdffffff","5"); //добавляем еще одну строку с 3мя ячейками xl.FileSave("c:\\file2.xlsx"); //сохраняем файл

In fact, this is all the methods - ascetic minimalism :)

If you need to write a formula, you can use the following code:

 var widthAdress = Excel.GetExcelPos(0, 1); var heightAdress = Excel.GetExcelPos(0, 2); xl.Rows[0][0] = String.Format("={0}*{1}", widthAdress , heightAdress);

CSV:

And with absolutely the same approach, asceticism is at least as simple as working with CSV files as with a two-dimensional data array.

 Csv csv = new Csv(); //создаем инстанс читалки csv.FileOpen("c:\\file1.csv"); //открываем файл var row1Cell6Value = csv.Rows[0][5]; //читаем 6ю ячейку 1й строки csv.AddRow("asdf","asdffffff","5"); // добавляем строку из 3х ячеек csv.FileSave("c:\\file2.csv"); // сохраняем файл

The actual code for both classes can be found here: https://github.com/ukushu/DataExporter

Now let's back down a bit to tell why my code is written this way and not otherwise.

This block will be useful to those who will be of little to me, and who want more opportunities in working with Microsoft Excel.

At first I tried to work with Excel-files using Microsoft.Office.Interop.Excel , which in fact:

  • Slow (I save a table of only 11,250 cells for 22 seconds !!!) .
  • It has a lot of memory leaks (in no case do not use this library for websites or applications where Office-format files are generated on the server: you run out of memory pretty quickly and the site or application will crash).
  • Requires an MS Office version. On each machine where your application will work.
  • Inconvenient to use.
  • It has many pitfalls because of which the extra Excel services will remain running ...

I consider it a very unfortunate decision to interact through it.

Then I tried to work through OleDB. This path led me to nowhere simply because it is impossible to work with formulas there. You can’t count a formula - just write it down. In general, this path is clearly better, faster and more enjoyable, but the lack of the ability to edit formulas made me very sad.

And so I came to openxml. As a result - also a relatively sad experience. Working with him is simply inconvenient. I do not know what the authors thought.

And I came to the final decision - wrappers around OpenXML - ClosedXML. This solution has already allowed to write:

  • Convenient and easy to read code.
  • At the same time, the code is quite fast. (a save of 20,000 cells costs 00: 00: 00.6787608, which is more than 57 times faster than the Interop path).
  • And also does not require MS Excel. :)

Important minuses:

  • you need to clarify that it works only with ".xlsx" files! But not with ".xls"!
  • Solution for working with Excel requires pulling up as much as 2 libraries (OpenXML, ClosedXML)
  • a solution for working with CSV will not work, for example, on Unity. This is due to the fact that you had to use the library with VB.
  • one
    Similar searches led to reflection in the implementation :) - NewView
  • Also helpful, thanks for the addition) - Andrew