Force EPPLUS to read as text

Question

I'm developping an application to read xlsx files, do some validation and insert into database. Unfortunatelly when I try to read columns marked as numeric (fe with EAN-13 codes) I get miniumum value of an int. The user doesn't see this because Excel displays it properly.

How can I make it read the file as plain text? I know I can use OLEBD for it, but I also need to edit the file dynamically, so epplus ExcelPackage is the best choice.

Here is code im using:

 FileInfo file = new FileInfo(path);
 MainExcel = new OfficeOpenXml.ExcelPackage(file);
 {
   var ws = MainExcel.Workbook.Worksheets.First();
   DataTable tbl = new DataTable();
        for (var rowNum = 1; rowNum <= ws.Dimension.End.Row; rowNum++)      //currently loading all file
                {
                    var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
                    var row = tbl.NewRow();
                    foreach (var cell in wsRow)
                    {
                        row[cell.Start.Column - 1] = cell.Text;
                    }
                    tbl.Rows.Add(row);
                }          
 }

and that's how I enumerate columns

   foreach (var firstRowCell in ws.Cells[3, 1, 3, ws.Dimension.End.Column])
                {
                    System.Type typeString = System.Type.GetType("System.String") ;
                    tbl.Columns.Add( firstRowCell.Text , typeString );
                }

For people whom it might concern, here is the file (works also for non google users): https://drive.google.com/open?id=0B3kIzUcpOx-iMC1iY0VoLS1kU3M&authuser=0

I noticed that ExcelRange.value property is an array which contains all of the objects unformatted. But once you iterate over cells in ExcelRange and request cell.Text property, it has already been processed. Trying to modify ConditionalFormatting and DataValidation in ExcelRange does not help (f.e. AddContainsText()) - @EDIT--> Neither for an entire sheet :-(

I'd prefer NOT to cast ExcelRange.Value as Array, it's ugly and very conditional.

Can you please tell how is the column being marked as numeric? — SharpCoder, Apr 06 '15 at 08:34
I edited the question, still no idea how to do this properly. — Luntri, Apr 08 '15 at 12:05
Where is it you're reading the cell that is coming out as `int.MinValue`? I see the one line where you have `row[...] = cell.Text`, but that `Text` property should be as a string. I don't remember it being an object. — krillgar, Apr 08 '15 at 12:09
That's the problem, I am not. I think it's the Epplus preconverting the cells before I ever get the chance to read them properly. And cell.Text is string. Yet I receive -2147483648 1/ for every single value bigger than 4 bilions — Luntri, Apr 08 '15 at 12:42
Is it possible to post a small sample .xlsx file with a sample of the data that produces the issue (or perhaps explain how to recreate?). Can't seem to reproduce the issue... — Stewart_R, Apr 08 '15 at 18:36

score 18 · Accepted Answer · answered May 07 '15 at 08:15

Apparently this is the solution (not complete code though, you have to add columns to datatable). I couldn't find the format string which specifies 'no formatting' in Epplus, but here you have it.

 var ws = MainExcel.Workbook.Worksheets.First();
 DataTable tbl = new DataTable();
 for (var rowNum = 1; rowNum <= ws.Dimension.End.Row; rowNum++)      
 {
     var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
     var array = wsRow.Value as object[,];

     var row = tbl.NewRow();
     int hhh =0;

     foreach (var cell in wsRow)
          {
           cell.Style.Numberformat.Format = "@";
           row[cell.Start.Column - 1] = cell.Text;
          }
     tbl.Rows.Add(row);
 }

To add some clarification, `[cell/range].Numberformat.Format = "@";` formats the specified cell/range as Text in the generated worksheet (as opposed to General, Number, Currency, Accounting, Short Date, etc) — DaveD, Nov 16 '15 at 21:18

score 3 · Answer 2 · answered Apr 28 '15 at 14:29

3

The cells in your file are custom-formatted as a fraction. Have you done this on purpose? Anyway, if you want to keep this format, you can alternatively use cell.Value or cell.RichText.Text to get your 13-digit number.

Hope this helps.

answered Apr 28 '15 at 14:29

romar

804
7
17

It's the users that send me the files. I am not responsible for editing them at all. I found the way to do this properly now, BTW Cell.value is already processed when you request it :) – Luntri Apr 29 '15 at 11:56
1

Solved the problem for me. --> 'cell.Text' showed the VISIBLE part, but not the complete value in the cell, while 'cell.Value' gave me the full value. 'cell.RichText.Text' by the way seemed to cause strange issue while iterating over the cells of a row. – Anytoe May 13 '16 at 10:33

Force EPPLUS to read as text

2 Answers2

Linked