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.
Answer
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);
}