public static DataTable ReadAsDataTable(string fileName)
{
DataTable dataTable = new DataTable();
using (SpreadsheetDocument spreadSheetDocument =
SpreadsheetDocument.Open(fileName, false))
{
WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
IEnumerable<Sheet> sheets =
spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>()
;
string relationshipId = sheets.First().Id.Value;
WorksheetPart worksheetPart =
(WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
Worksheet workSheet = worksheetPart.Worksheet;
SheetData sheetData = workSheet.GetFirstChild<SheetData>();
datau
foreach (Cell cell in rows.ElementAt(0))
{
dataTable.Columns.Add(quitarTildes(GetCellValue(spreadSheetDocument,
cell).Replace(" ", "").Replace("(DDMMAAA)", "").Replace("(AAAAMMDD)",
"").Replace(".", "").Trim().ToLower()));
}
string headers = "numeroguiarecibodecaja|FechaGuiaReciboDeCaja|
Producto|Subproducto|CiudadDestino|MedioTransporte|TiempoEntrega|FormaPago|
ValorDeclarado|PesoFisico|Largo|Alto|Ancho|DiceContener|ObservacionesparalaEntrega|
IdentificationTypeR|IdentificationR|NameR|SurnameR|PhoneNumberR|TipoPersonaR|
RegimenR|AddressR|EmailR|IdentificationType|Identification|Name|Surname|
PhoneNumber|Address|Email|VrFlete|VrSobreflete|VrTotal";
string[] headerSeparado = headers.Split('|');
for (int i = 0; i < dataTable.Columns.Count; i++)
{
if (!quitarTildes(dataTable.Columns[i].ColumnName.Replace(" ",
"").Replace("(DDMMAAA)", "")).Replace("(AAAAMMDD)", "").Replace(".",
"").Trim().Equals(headerSeparado[i].ToLower()))
{
throw new Exception("El orden de las columnas es:
NumeroguiaReciboDeCaja|FechaGuiaReciboDeCaja|Producto|Subproducto|CiudadDestino|
MedioTransporte|TiempoEntrega|FormaPago|ValorDeclarado|PesoFisico|Largo|Alto|Ancho|
DiceContener|ObservacionesparalaEntrega|IdentificationTypeR|IdentificationR|NameR|
SurnameR|PhoneNumberR|TipoPersonaR|RegimenR|AddressR|EmailR|IdentificationType|
Identification|Name|Surname|PhoneNumber|Address|Email|VrFlete|VrSobreflete|
VrTotal");
}
foreach (Row row in rows) //this will also include your header
row...
{
DataRow tempRow = dataTable.NewRow();
int columnIndex = 0;
foreach (Cell cell in row.Descendants<Cell>())
{
// Gets the column index of the cell with data
int cellColumnIndex =
(int)GetColumnIndexFromName(GetColumnName(cell.CellReference));
cellColumnIndex--; //zero based index
if (columnIndex < cellColumnIndex)
{
do
{
tempRow[columnIndex] = ""; //Insert blank data
here;
columnIndex++;
}
while (columnIndex < cellColumnIndex);
}
tempRow[columnIndex] = GetCellValue(spreadSheetDocument,
cell);
columnIndex++;
}
if (!string.IsNullOrEmpty(tempRow.ItemArray[0].ToString()))
dataTable.Rows.Add(tempRow);
else
break;
}
}
dataTable.Rows.RemoveAt(0); //...so i'm taking it out here.
return dataTable;
}
