Scribd
Upload
34 views2 pages

Read As Data Table

The provided code defines a method to read data from an Excel file and populate a DataTable. It checks the order of the columns against a predefined header format and throws an exception if they do not match. The method processes each row of the worksheet, extracting cell values and adding them to the DataTable, while also handling empty rows appropriately.

Uploaded by

Jair Rojas Garcia
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
34 views2 pages

Read As Data Table

The provided code defines a method to read data from an Excel file and populate a DataTable. It checks the order of the columns against a predefined header format and throws an exception if they do not match. The method processes each row of the worksheet, extracting cell values and adding them to the DataTable, while also handling empty rows appropriately.

Uploaded by

Jair Rojas Garcia
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 2

public static DataTable ReadAsDataTable(string fileName)

{
DataTable dataTable = new DataTable();
using (SpreadsheetDocument spreadSheetDocument =
[Link](fileName, false))
{
WorkbookPart workbookPart = [Link];
IEnumerable<Sheet> sheets =
[Link]<Sheets>().Elements<Sheet>()
;
string relationshipId = [Link]().[Link];
WorksheetPart worksheetPart =
(WorksheetPart)[Link](relationshipId);
Worksheet workSheet = [Link];
SheetData sheetData = [Link]<SheetData>();
datau

foreach (Cell cell in [Link](0))


{

[Link](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 = [Link]('|');

for (int i = 0; i < [Link]; i++)


{
if (!quitarTildes([Link][i].[Link](" ",
"").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 = [Link]();
int columnIndex = 0;
foreach (Cell cell in [Link]<Cell>())
{
// Gets the column index of the cell with data
int cellColumnIndex =
(int)GetColumnIndexFromName(GetColumnName([Link]));
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 (![Link]([Link][0].ToString()))
[Link](tempRow);
else
break;
}

}
[Link](0); //...so i'm taking it out here.

return dataTable;
}

You might also like