private void getAllSections(bool boo, IExcelDataReader reader)
{
if (boo)
{
Section newSection = new Section();
string class_description = reader.GetString(5);
Class c = Class.Where(s => s.Description.Equals(class_description)).FirstOrDefault <Class>();
newSection.ClassId = c.ClassId;
newSection.Type = reader.GetString(0);
newSection.StatusId = 1; // 'A'
newSection.Capacity = Convert.ToInt32((reader.GetDouble(22)));
newSection.RemainingSeats = Convert.ToInt32((reader.GetDouble(22))) - Convert.ToInt32((reader.GetDouble(8)));
string times = "";
if (reader.GetValue(12) != null)
{
if (reader.GetString(12).Equals("Y"))
{
times = times + "Mon ";
}
if (reader.GetString(13).Equals("Y"))
{
times = times + "Tue ";
}
if (reader.GetString(14).Equals("Y"))
{
times = times + "Wed ";
}
if (reader.GetString(15).Equals("Y"))
{
times = times + "Thu ";
}
if (reader.GetString(16).Equals("Y"))
{
times = times + "Fri ";
}
}
//
if (reader.GetValue(23) != null || reader.GetValue(24) != null)
{
times = times + reader.GetDateTime(23).Hour + ":" + reader.GetDateTime(23).Minute + "-" + reader.GetDateTime(24).Hour + ":" + reader.GetDateTime(24).Minute;
}
newSection.Times = times;
newSection.Room = reader.GetString(20);
Section.Add(newSection);
SaveChanges();
}
}
public static FxOperation MapForwardContract(this IExcelDataReader reader)
{
return(new FxForwardContract(
reader.GetDateTime(TradeDate),
Enum.Parse <CurrencyPair>(reader.GetValue(Symbol).ToString()),
reader.GetDouble(Notional),
reader.GetDouble(Price),
Enum.Parse <Direction>(reader.GetValue(BuyOrSell).ToString()),
reader.GetDateTime(WithdrawDate),
reader.GetDateTime(SettlementDate)));
}
public static FxOperation MapVanillaOption(this IExcelDataReader reader)
{
return(new FxVanillaOption(
reader.GetDateTime(TradeDate),
Enum.Parse <CurrencyPair>(reader.GetValue(Symbol).ToString()),
reader.GetDouble(Notional),
reader.GetDouble(Premium),
reader.GetDouble(Spot),
reader.GetDouble(Strike),
Enum.Parse <OptionType>(reader.GetValue(CallOrPut).ToString()),
Enum.Parse <ExecutionType>(reader.GetValue(AmericanOrEuropean).ToString()),
Enum.Parse <Direction>(reader.GetValue(BuyOrSell).ToString()),
reader.GetDateTime(WithdrawDate),
reader.GetDateTime(SettlementDate)));
}
public void readReport(string path)
{
bool secondLine = false;
FileStream fs = File.Open(path, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(fs);
do
{
while (excelReader.Read())
{
if (secondLine == true)
{
if (excelReader.GetString(1) != null)
{
string prsDetails = excelReader.GetString(1);
DateTime date = excelReader.GetDateTime(7);
string detection = excelReader.GetString(10);
string icdLine = excelReader.GetString(11);
compoundInfo temporary = new compoundInfo(prsDetails, icdLine, date, detection);
if (findDuplicatesPatients(temporary) == false)
{
patients.Add(temporary);
ConsoleOutputPatINF(temporary);
}
}
}
secondLine = true;
}
} while (excelReader.NextResult());
fs.Close();
}
private Transaction FromBuy(Dictionary <string, int> header, IExcelDataReader reader)
{
return(new Transaction
{
Timestamp = DateTime.SpecifyKind(reader.GetDateTime(header["Time (UTC)"]), DateTimeKind.Utc),
Debit = new Money("USD", reader.GetDouble(header["USD Amount"])),
Credit = new Money("ETH", reader.GetDouble(header["ETH Amount"])),
Fee = new Money("USD", reader.GetDouble(header["Trading Fee (USD)"]))
});
}
private string ParseCell(IExcelDataReader reader, int columnToParse)
{
//GetFieldType() returns the type of a value in the current row.Always one of the types supported by Excel:
//double, int, bool, DateTime, TimeSpan, string, or null if there is no value.
var fieldType = reader.GetFieldType(columnToParse);
if (fieldType == null)
{
return(null);
}
string result = null;
if (fieldType == typeof(double))
{
var value = reader.GetDouble(columnToParse);
result = value.ToString();
}
else if (fieldType == typeof(int))
{
var value = reader.GetInt32(columnToParse);
result = value.ToString();
}
else if (fieldType == typeof(bool))
{
var value = reader.GetBoolean(columnToParse);
result = value.ToString();
}
else if (fieldType == typeof(DateTime))
{
var value = reader.GetDateTime(columnToParse);
result = value.ToString();
}
else if (fieldType == typeof(TimeSpan))
{
Console.WriteLine("TimeSpan parsing is not implemented");
//var value = reader.GetTimeS(0);
//data[key] = value;
}
else if (fieldType == typeof(string))
{
var value = reader.GetString(columnToParse);
result = value;
}
else
{
Console.WriteLine($"unknown type from Excel field: {0}", fieldType);
}
return(result);
}
public static string GetDate(IExcelDataReader reader, int?index)
{
if (index == null || index == -1 || index >= reader.FieldCount)
{
return("");
}
try
{
var value = reader.GetDateTime(index.Value);
return(value != null?value.ToString("yyyy-MM-dd") : null);
}
catch
{
return(GetValue(reader, index));
}
}
public static Parcel GetParcel(IExcelDataReader reader)
{
string getaddress()
{
return(reader.GetString((int)FarEast.Address));
}
string getcategory() => reader.GetString((int)FarEast.Category);
string GetTrackID() => reader.GetValue((int)FarEast.TrackID).ToString();
DateTime getRegistrationTime() => reader.GetDateTime((int)FarEast.RegistrationTime);
int?getDestinationIndex() => Convert.ToInt32(reader.GetValue((int)FarEast.DestinationIndex));
string getType() => reader.GetString((int)FarEast.Type);
int getIndex() => (int)reader.GetDouble((int)FarEast.Index);
DateTime getPlannedDate() => Convert.ToDateTime(reader.GetValue((int)FarEast.PlannedDate));
int getUnsuccessfulDeliveryCount() => (int)reader.GetDouble((int)FarEast.UnsuccessfulDeliveryCount);
string getName() => reader.GetValue((int)FarEast.Name) == null ? string.Empty : reader.GetValue((int)FarEast.Name).ToString();
string getTelephoneNumber() => reader.GetValue((int)FarEast.Telephone) == null ? string.Empty : reader.GetValue((int)FarEast.Telephone).ToString();
IsPayneedResult getIsPayNeed()
{
return((int)reader.GetDouble((int)FarEast.IsNeedPay) == 1 ? IsPayneedResult.Need: IsPayneedResult.NotNeed);
}
Parcel _p = new Parcel
{
Address = getaddress(),
Category = getcategory(),
TrackID = GetTrackID(),
RegistrationTime = getRegistrationTime(),
DestinationIndex = getDestinationIndex(),
Type = getType(),
Index = getIndex(),
PlannedDate = getPlannedDate(),
UnsuccessfulDeliveryCount = getUnsuccessfulDeliveryCount(),
Name = getName(),
TelephoneNumber = getTelephoneNumber(),
IsPayNeed = getIsPayNeed()
};
_p.LastOperation = (reader.GetString((int)FarEast.LastOperation));
_p.LastZone = (reader.GetString((int)FarEast.LastZone));
return(_p);
}
private void GetQarterlyDeals(string filePath, object[,] table)
{
FileStream stream = System.IO.File.Open(filePath, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
excelReader.IsFirstRowAsColumnNames = true;
var isFirstRow = true;
while (excelReader.Read())
{
if (isFirstRow)
{
isFirstRow = false;
continue;
}
var value = excelReader.GetDecimal(7);
if (value < 100)
{
continue;
}
var numAdvisors = excelReader.GetInt32(12);
if (numAdvisors < 1)
{
continue;
}
var date = excelReader.GetDateTime(0);
var quarter = GetQuarter(date);
var tableRow = (date.Year - 2009) * quarter;
var tableCol = GetValueColumn(value);
((List <int>)table[tableRow, tableCol]).Add(numAdvisors);
}
excelReader.Close();
excelReader.Dispose();
}
public void DataReader_Read_Test()
{
IExcelDataReader r =
ExcelReaderFactory.CreateOpenXmlReader(Helper.GetTestWorkbook("xTest_num_double_date_bool_string"));
var table = new DataTable();
table.Columns.Add(new DataColumn("num_col", typeof(int)));
table.Columns.Add(new DataColumn("double_col", typeof(double)));
table.Columns.Add(new DataColumn("date_col", typeof(DateTime)));
table.Columns.Add(new DataColumn("boo_col", typeof(bool)));
int fieldCount = -1;
while (r.Read())
{
fieldCount = r.FieldCount;
table.Rows.Add(r.GetInt32(0), r.GetDouble(1), r.GetDateTime(2), r.IsDBNull(4));
}
r.Close();
Assert.AreEqual(6, fieldCount);
Assert.AreEqual(30, table.Rows.Count);
Assert.AreEqual(1, int.Parse(table.Rows[0][0].ToString()));
Assert.AreEqual(1346269, int.Parse(table.Rows[29][0].ToString()));
//double + Formula
Assert.AreEqual(1.02, double.Parse(table.Rows[0][1].ToString()));
Assert.AreEqual(4.08, double.Parse(table.Rows[2][1].ToString()));
Assert.AreEqual(547608330.24, double.Parse(table.Rows[29][1].ToString()));
//Date + Formula
Assert.AreEqual(new DateTime(2009, 5, 11).ToShortDateString(),
DateTime.Parse(table.Rows[0][2].ToString()).ToShortDateString());
Assert.AreEqual(new DateTime(2009, 11, 30).ToShortDateString(),
DateTime.Parse(table.Rows[29][2].ToString()).ToShortDateString());
}
public void DataReaderReadTest()
{
using (IExcelDataReader r = OpenReader("Test_num_double_date_bool_string"))
{
var table = new DataTable();
table.Columns.Add(new DataColumn("num_col", typeof(int)));
table.Columns.Add(new DataColumn("double_col", typeof(double)));
table.Columns.Add(new DataColumn("date_col", typeof(DateTime)));
table.Columns.Add(new DataColumn("boo_col", typeof(bool)));
int fieldCount = -1;
while (r.Read())
{
fieldCount = r.FieldCount;
table.Rows.Add(
Convert.ToInt32(r.GetValue(0)),
Convert.ToDouble(r.GetValue(1)),
r.GetDateTime(2),
r.IsDBNull(4));
}
Assert.AreEqual(6, fieldCount);
Assert.AreEqual(30, table.Rows.Count);
Assert.AreEqual(1, int.Parse(table.Rows[0][0].ToString()));
Assert.AreEqual(1346269, int.Parse(table.Rows[29][0].ToString()));
// double + Formula
Assert.AreEqual(1.02, double.Parse(table.Rows[0][1].ToString()));
Assert.AreEqual(4.08, double.Parse(table.Rows[2][1].ToString()));
Assert.AreEqual(547608330.24, double.Parse(table.Rows[29][1].ToString()));
// Date + Formula
Assert.AreEqual(new DateTime(2009, 5, 11).ToShortDateString(), DateTime.Parse(table.Rows[0][2].ToString()).ToShortDateString());
Assert.AreEqual(new DateTime(2009, 11, 30).ToShortDateString(), DateTime.Parse(table.Rows[29][2].ToString()).ToShortDateString());
}
}
public static List <Person> ExcelFileToListObject(string filePath)
{
FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
//1. Reading from a binary Excel file ('97-2003 format; *.xls)
IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
//DataSet - Create column names from first row
DataSet result = excelReader.AsDataSet();
List <Person> people = new List <Person>();
DataTable dt = new DataTable();
dt = result.Tables[0];
//5. Data Reader methods
while (excelReader.Read())
{
if (excelReader.GetString(0) != "OP_MAT")
{
Person p = new Person
{
OP_MAT = excelReader.GetString(0),
OP_NM = excelReader.GetString(1),
OP_CPF = excelReader.GetString(2),
DN = excelReader.GetDateTime(3),
PAI = excelReader.GetString(4),
MAE = excelReader.GetString(5),
PROFISSAO = excelReader.GetString(6),
};
people.Add(p);
}
}
excelReader.Close();
return(people);
}
public static void DoOpenOfficeTest(IExcelDataReader excelReader)
{
Assert.IsTrue(excelReader.IsValid);
excelReader.Read();
Assert.AreEqual(6, excelReader.FieldCount);
Assert.AreEqual("column a", excelReader.GetString(0));
Assert.AreEqual(" column b", excelReader.GetString(1));
Assert.AreEqual(" column b", excelReader.GetString(2));
Assert.IsNull(excelReader.GetString(3));
Assert.AreEqual("column e", excelReader.GetString(4));
Assert.AreEqual(" column b", excelReader.GetString(5));
excelReader.Read();
Assert.AreEqual(6, excelReader.FieldCount);
Assert.AreEqual(2, excelReader.GetInt32(0));
Assert.AreEqual("b", excelReader.GetString(1));
Assert.AreEqual("c", excelReader.GetString(2));
Assert.AreEqual("d", excelReader.GetString(3));
Assert.AreEqual(" e ", excelReader.GetString(4));
excelReader.Read();
Assert.AreEqual(6, excelReader.FieldCount);
Assert.AreEqual(3, excelReader.GetInt32(0));
Assert.AreEqual(2, excelReader.GetInt32(1));
Assert.AreEqual(3, excelReader.GetInt32(2));
Assert.AreEqual(4, excelReader.GetInt32(3));
Assert.AreEqual(5, excelReader.GetInt32(4));
excelReader.Read();
Assert.AreEqual(6, excelReader.FieldCount);
Assert.AreEqual(4, excelReader.GetInt32(0));
Assert.AreEqual(new DateTime(2012, 10, 13), excelReader.GetDateTime(1));
Assert.AreEqual(new DateTime(2012, 10, 14), excelReader.GetDateTime(2));
Assert.AreEqual(new DateTime(2012, 10, 15), excelReader.GetDateTime(3));
Assert.AreEqual(new DateTime(2012, 10, 16), excelReader.GetDateTime(4));
for (int i = 4; i < 34; i++)
{
excelReader.Read();
Assert.AreEqual(i + 1, excelReader.GetInt32(0));
Assert.AreEqual(i + 2, excelReader.GetInt32(1));
Assert.AreEqual(i + 3, excelReader.GetInt32(2));
Assert.AreEqual(i + 4, excelReader.GetInt32(3));
Assert.AreEqual(i + 5, excelReader.GetInt32(4));
}
excelReader.NextResult();
excelReader.Read();
Assert.AreEqual(0, excelReader.FieldCount);
excelReader.NextResult();
excelReader.Read();
Assert.AreEqual(0, excelReader.FieldCount);
//test dataset
DataSet result = excelReader.AsDataSet(true);
Assert.AreEqual(1, result.Tables.Count);
Assert.AreEqual(6, result.Tables[0].Columns.Count);
Assert.AreEqual(33, result.Tables[0].Rows.Count);
Assert.AreEqual("column a", result.Tables[0].Columns[0].ColumnName);
Assert.AreEqual(" column b", result.Tables[0].Columns[1].ColumnName);
Assert.AreEqual(" column b_1", result.Tables[0].Columns[2].ColumnName);
Assert.AreEqual("Column3", result.Tables[0].Columns[3].ColumnName);
Assert.AreEqual("column e", result.Tables[0].Columns[4].ColumnName);
Assert.AreEqual(" column b_2", result.Tables[0].Columns[5].ColumnName);
Assert.AreEqual(2, Convert.ToInt32(result.Tables[0].Rows[0][0]));
Assert.AreEqual("b", result.Tables[0].Rows[0][1]);
Assert.AreEqual("c", result.Tables[0].Rows[0][2]);
Assert.AreEqual("d", result.Tables[0].Rows[0][3]);
Assert.AreEqual(" e ", result.Tables[0].Rows[0][4]);
Assert.AreEqual(3, Convert.ToInt32(result.Tables[0].Rows[1][0]));
Assert.AreEqual(2, Convert.ToInt32(result.Tables[0].Rows[1][1]));
Assert.AreEqual(3, Convert.ToInt32(result.Tables[0].Rows[1][2]));
Assert.AreEqual(4, Convert.ToInt32(result.Tables[0].Rows[1][3]));
Assert.AreEqual(5, Convert.ToInt32(result.Tables[0].Rows[1][4]));
Assert.AreEqual(4, Convert.ToInt32(result.Tables[0].Rows[2][0]));
Assert.AreEqual(new DateTime(2012, 10, 13), result.Tables[0].Rows[2][1]);
Assert.AreEqual(new DateTime(2012, 10, 14), result.Tables[0].Rows[2][2]);
Assert.AreEqual(new DateTime(2012, 10, 15), result.Tables[0].Rows[2][3]);
Assert.AreEqual(new DateTime(2012, 10, 16), result.Tables[0].Rows[2][4]);
for (int i = 4; i < 33; i++)
{
Assert.AreEqual(i + 2, Convert.ToInt32(result.Tables[0].Rows[i][0]));
Assert.AreEqual(i + 3, Convert.ToInt32(result.Tables[0].Rows[i][1]));
Assert.AreEqual(i + 4, Convert.ToInt32(result.Tables[0].Rows[i][2]));
Assert.AreEqual(i + 5, Convert.ToInt32(result.Tables[0].Rows[i][3]));
Assert.AreEqual(i + 6, Convert.ToInt32(result.Tables[0].Rows[i][4]));
}
excelReader.Close();
}
public void FillData(IExcelDataReader reader, int?department, int?group)
{
var evaluator = new DataTable(); // Evaluator for mathematics equations
do //Loop through each sheet
{
while (reader.Read()) //Loop through each row if next row exists.
{
// Checks if this is the first row in the sheet, or the row isn't complete, or first cell is empty.
if (reader.GetString(0) == "اسم الطالب" || reader.FieldCount < 17 || String.IsNullOrEmpty(reader.GetValue(0)?.ToString()))
{
continue;
}
var student = new Student();
try
{
// TODO: YOU SHOULD FIX THIS.
try
{
if (!String.IsNullOrEmpty(reader.GetValue(0)?.ToString()))
{
student.Name = reader.GetString(0);
}
}
catch { }
try
{
if (!String.IsNullOrEmpty(reader.GetValue(1)?.ToString()))
{
student.Sex = (StudentSex)Enum.Parse(typeof(StudentSex), reader.GetString(1));
}
}
catch { }
try
{
if (!String.IsNullOrEmpty(reader.GetValue(2)?.ToString()))
{
student.Nationality = reader.GetString(2);
}
}
catch { }
try
{
if (!String.IsNullOrEmpty(reader.GetValue(3)?.ToString()))
{
student.Religion = (StudentReligion)Enum.Parse(typeof(StudentReligion), reader.GetString(3));
}
}
catch { }
try
{
if (!String.IsNullOrEmpty(reader.GetValue(4)?.ToString()))
{
student.BirthDate = reader.GetDateTime(4);
}
}
catch { }
try
{
if (!String.IsNullOrEmpty(reader.GetValue(5)?.ToString()))
{
student.BirthPlace = reader.GetValue(5).ToString();
}
}
catch { }
try
{
if (!String.IsNullOrEmpty(reader.GetValue(6)?.ToString()))
{
student.PersonalCardId = reader.GetValue(6).ToString();
}
}
catch { }
try
{
if (!String.IsNullOrEmpty(reader.GetValue(7)?.ToString()))
{
student.CivilRegistry = reader.GetValue(7).ToString();
}
}
catch { }
try
{
if (!String.IsNullOrEmpty(reader.GetValue(8)?.ToString()))
{
student.AcademicQualificationAndDate = reader.GetValue(8).ToString();
}
}
catch { }
try
{
if (!String.IsNullOrEmpty(reader.GetValue(9)?.ToString()))
{
try
{
student.Total = reader.GetDouble(9);
}
catch
{
student.Total = double.Parse(evaluator.Compute(reader.GetValue(9).ToString(), "").ToString());
}
}
}
catch { }
try
{
if (!String.IsNullOrEmpty(reader.GetValue(10)?.ToString()))
{
student.Speciality = reader.GetValue(10).ToString();
}
}
catch { }
try
{
if (!String.IsNullOrEmpty(reader.GetValue(11)?.ToString()))
{
student.StatusOfConstraint = reader.GetValue(11).ToString();
}
}
catch { }
try
{
if (!String.IsNullOrEmpty(reader.GetValue(12)?.ToString()))
{
student.ContantMethod = reader.GetValue(12).ToString();
}
}
catch { }
try
{
if (!String.IsNullOrEmpty(reader.GetValue(13)?.ToString()))
{
student.JoinDate = reader.GetDateTime(13);
}
}
catch { }
try
{
if (!String.IsNullOrEmpty(reader.GetValue(14)?.ToString()))
{
student.PlaceOfResidence = reader.GetValue(14).ToString();
}
}
catch { }
try
{
if (!String.IsNullOrEmpty(reader.GetValue(15)?.ToString()))
{
student.HomeNumber = GetIntFromString(reader.GetValue(15).ToString());
}
}
catch { }
try
{
if (!String.IsNullOrEmpty(reader.GetValue(16)?.ToString()))
{
student.StreetNumber = reader.GetValue(16).ToString();
}
}
catch { }
try
{
if (department != null)
{
student.Department = (Departments)department;
}
}
catch { }
try
{
if (group != null)
{
student.Group = (Groups)group;
}
}
catch { }
if (!String.IsNullOrEmpty(student?.Name))
{
var old_student = db.Students.Where(x => x.PersonalCardId == student.PersonalCardId).FirstOrDefault();
if (old_student != null)
{
student.ID = old_student.ID;
db.Entry(old_student).CurrentValues.SetValues(student);
}
else
{
db.Students.Add(student);
}
}
}
catch { }
}
} while (reader.NextResult());
db.SaveChanges();
}
public static void FileUploadComplete(object sender, DevExpress.Web.FileUploadCompleteEventArgs e)
{
#region Variables
tb_empresa_List ListaEmpresa = new tb_empresa_List();
List <tb_empresa_Info> Lista_Empresa = new List <tb_empresa_Info>();
tb_sucursal_List ListaSucursal = new tb_sucursal_List();
List <tb_sucursal_Info> Lista_Sucursal = new List <tb_sucursal_Info>();
tb_bodega_List ListaBodega = new tb_bodega_List();
List <tb_bodega_Info> Lista_Bodega = new List <tb_bodega_Info>();
int cont = 0;
decimal IdTransaccionSession = Convert.ToDecimal(SessionFixed.IdTransaccionSessionActual);
int IdEmpresa = Convert.ToInt32(SessionFixed.IdEmpresa);
#endregion
Stream stream = new MemoryStream(e.UploadedFile.FileBytes);
if (stream.Length > 0)
{
IExcelDataReader reader = null;
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
#region Empresa
while (reader.Read())
{
if (!reader.IsDBNull(0) && cont > 0)
{
tb_empresa_Info info = new tb_empresa_Info
{
IdEmpresa = Convert.ToInt32(reader.GetValue(0)),
codigo = Convert.ToString(reader.GetValue(1)),
em_nombre = Convert.ToString(reader.GetValue(2)),
RazonSocial = Convert.ToString(reader.GetValue(3)),
NombreComercial = Convert.ToString(reader.GetValue(4)),
ContribuyenteEspecial = Convert.ToString(reader.GetValue(5)),
em_ruc = Convert.ToString(reader.GetValue(6)),
em_gerente = Convert.ToString(reader.GetValue(7)),
em_contador = Convert.ToString(reader.GetValue(8)),
em_rucContador = Convert.ToString(reader.GetValue(9)),
em_telefonos = Convert.ToString(reader.GetValue(10)),
em_direccion = Convert.ToString(reader.GetValue(11)),
em_fechaInicioContable = reader.GetDateTime(12),
cod_entidad_dinardap = Convert.ToString(reader.GetValue(13)),
em_Email = Convert.ToString(reader.GetValue(14))
};
info.em_fechaInicioActividad = info.em_fechaInicioContable;
Lista_Empresa.Add(info);
}
else
{
cont++;
}
}
ListaEmpresa.set_list(Lista_Empresa, IdTransaccionSession);
#endregion
cont = 0;
reader.NextResult();
#region Sucursal
while (reader.Read())
{
if (!reader.IsDBNull(0) && cont > 0)
{
tb_sucursal_Info info = new tb_sucursal_Info
{
IdEmpresa = Convert.ToInt32(reader.GetValue(0)),
IdSucursal = Convert.ToInt32(reader.GetValue(1)),
codigo = Convert.ToString(reader.GetValue(2)),
Su_Descripcion = Convert.ToString(reader.GetValue(3)),
Su_CodigoEstablecimiento = Convert.ToString(reader.GetValue(4)),
Su_Ruc = Convert.ToString(reader.GetValue(5)),
Su_JefeSucursal = Convert.ToString(reader.GetValue(6)),
Su_Telefonos = Convert.ToString(reader.GetValue(7)),
Su_Direccion = Convert.ToString(reader.GetValue(8)),
IdUsuario = SessionFixed.IdUsuario
};
Lista_Sucursal.Add(info);
}
else
{
cont++;
}
}
ListaSucursal.set_list(Lista_Sucursal, IdTransaccionSession);
#endregion
cont = 0;
//Para avanzar a la siguiente hoja de excel
reader.NextResult();
#region Bodega
while (reader.Read())
{
if (!reader.IsDBNull(0) && cont > 0)
{
tb_bodega_Info info = new tb_bodega_Info
{
IdEmpresa = Convert.ToInt32(reader.GetValue(0)),
IdSucursal = Convert.ToInt32(reader.GetValue(1)),
IdBodega = Convert.ToInt32(reader.GetValue(2)),
cod_bodega = Convert.ToString(reader.GetValue(3)),
bo_Descripcion = Convert.ToString(reader.GetValue(4)),
IdCtaCtble_Inve = Convert.ToString(reader.GetValue(5)),
IdUsuario = SessionFixed.IdUsuario
};
Lista_Bodega.Add(info);
}
else
{
cont++;
}
}
ListaBodega.set_list(Lista_Bodega, IdTransaccionSession);
#endregion
}
}
public DateTime GetDateTime(int i) => _reader.GetDateTime(i);
public static ImportDataModel GetImportModels(string path)
{
try
{
var model = new ImportDataModel();
var stream = File.OpenRead(path);
IExcelDataReader excelReader = null;
//Reading from a binary Excel file ('97-2003 format; *.xls)
if (Path.GetExtension(path).Equals(".xls", StringComparison.InvariantCultureIgnoreCase))
{
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
//Reading from a OpenXml Excel file (2007 format; *.xlsx)
if (Path.GetExtension(path).Equals(".xlsx", StringComparison.InvariantCultureIgnoreCase))
{
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
if (excelReader == null)
{
return(model);
}
var pageIndex = 0;
do
{
pageIndex++;
var items = new List <ImportModel>();
var category = (CategoryCode)pageIndex;
var rowIndex = 0;
while (excelReader.Read())
{
rowIndex++;
if (rowIndex < StartRowIndex)
{
continue;
}
if (excelReader.IsDBNull(3))
{
continue;
}
var item = new ImportModel();
item.V1 = excelReader.IsDBNull(0) ? (int?)null : excelReader.GetInt32(0);
item.V2 = excelReader.GetString(1);
item.V3 = excelReader.GetString(2);
item.V4 = excelReader.GetString(3);
item.V5 = excelReader.GetString(4);
item.V6 = excelReader.GetString(5);
item.V7 = excelReader.GetString(6);
item.V8 = excelReader.GetString(7);
item.V9 = excelReader.GetString(8);
item.V10 = excelReader.GetString(9);
item.V11 = excelReader.IsDBNull(10) ? (DateTime?)null : excelReader.GetDateTime(10);
item.V12 = excelReader.IsDBNull(11) ? (double?)null : excelReader.GetDouble(11);
item.V13 = excelReader.IsDBNull(12) ? (double?)null : excelReader.GetDouble(12);
item.V14 = excelReader.IsDBNull(13) ? (double?)null : excelReader.GetDouble(13);
item.V15 = excelReader.IsDBNull(14) ? (double?)null : excelReader.GetDouble(14);
item.V16 = excelReader.IsDBNull(15) ? (double?)null : excelReader.GetDouble(15);
item.V17 = excelReader.IsDBNull(16) ? (double?)null : excelReader.GetDouble(16);
item.V18 = excelReader.IsDBNull(17) ? (double?)null : excelReader.GetDouble(17);
item.V19 = excelReader.IsDBNull(18) ? (double?)null : excelReader.GetDouble(18);
item.V20 = excelReader.IsDBNull(19) ? (double?)null : excelReader.GetDouble(19);
item.V21 = excelReader.IsDBNull(20) ? (double?)null : excelReader.GetDouble(20);
item.V22 = excelReader.IsDBNull(21) ? (double?)null : excelReader.GetDouble(21);
item.V23 = excelReader.IsDBNull(22) ? (double?)null : excelReader.GetDouble(22);
item.V24 = excelReader.IsDBNull(23) ? (double?)null : excelReader.GetDouble(23);
item.V25 = excelReader.IsDBNull(24) ? (DateTime?)null : excelReader.GetDateTime(24);
item.V26 = excelReader.IsDBNull(25) ? (double?)null : excelReader.GetDouble(25);
item.V27 = excelReader.IsDBNull(26) ? (DateTime?)null : excelReader.GetDateTime(26);
item.V28 = excelReader.IsDBNull(27) ? (DateTime?)null : excelReader.GetDateTime(27);
item.V29 = excelReader.IsDBNull(28) ? (DateTime?)null : excelReader.GetDateTime(28);
item.V30 = excelReader.IsDBNull(29) ? (DateTime?)null : excelReader.GetDateTime(29);
item.V31 = excelReader.IsDBNull(30) ? (DateTime?)null : excelReader.GetDateTime(30);
item.V32 = excelReader.IsDBNull(31) ? (DateTime?)null : excelReader.GetDateTime(31);
item.V33 = excelReader.IsDBNull(32) ? (DateTime?)null : excelReader.GetDateTime(32);
item.V34 = excelReader.GetString(33);
item.V35 = excelReader.GetString(34);
item.V36 = excelReader.GetString(35);
item.V37 = excelReader.IsDBNull(36) ? (DateTime?)null : excelReader.GetDateTime(36);
item.V38 = excelReader.GetString(37);
item.V39 = excelReader.IsDBNull(38) ? (DateTime?)null : excelReader.GetDateTime(38);
item.V40 = excelReader.IsDBNull(39) ? (double?)null : excelReader.GetDouble(39);
items.Add(item);
}
model.Items.Add(category, items);
model.PageRows.Add(category, rowIndex);
} while (excelReader.NextResult());
excelReader.Close();
return(model);
}
catch (Exception ex)
{
throw ex;
}
}
public static void DoOpenOfficeTest(IExcelDataReader excelReader)
{
excelReader.Read();
Assert.AreEqual(6, excelReader.FieldCount);
Assert.AreEqual("column a", excelReader.GetString(0));
Assert.AreEqual(" column b", excelReader.GetString(1));
Assert.AreEqual(" column b", excelReader.GetString(2));
Assert.IsNull(excelReader.GetString(3));
Assert.AreEqual("column e", excelReader.GetString(4));
Assert.AreEqual(" column b", excelReader.GetString(5));
excelReader.Read();
Assert.AreEqual(2, excelReader.GetDouble(0));
Assert.AreEqual("b", excelReader.GetString(1));
Assert.AreEqual("c", excelReader.GetString(2));
Assert.AreEqual("d", excelReader.GetString(3));
Assert.AreEqual(" e ", excelReader.GetString(4));
excelReader.Read();
Assert.AreEqual(6, excelReader.FieldCount);
Assert.AreEqual(3, excelReader.GetDouble(0));
Assert.AreEqual(2, excelReader.GetDouble(1));
Assert.AreEqual(3, excelReader.GetDouble(2));
Assert.AreEqual(4, excelReader.GetDouble(3));
Assert.AreEqual(5, excelReader.GetDouble(4));
excelReader.Read();
Assert.AreEqual(6, excelReader.FieldCount);
Assert.AreEqual(4, excelReader.GetDouble(0));
Assert.AreEqual(new DateTime(2012, 10, 13), excelReader.GetDateTime(1));
Assert.AreEqual(new DateTime(2012, 10, 14), excelReader.GetDateTime(2));
Assert.AreEqual(new DateTime(2012, 10, 15), excelReader.GetDateTime(3));
Assert.AreEqual(new DateTime(2012, 10, 16), excelReader.GetDateTime(4));
for (int i = 4; i < 34; i++)
{
excelReader.Read();
Assert.AreEqual(i + 1, excelReader.GetDouble(0));
Assert.AreEqual(i + 2, excelReader.GetDouble(1));
Assert.AreEqual(i + 3, excelReader.GetDouble(2));
Assert.AreEqual(i + 4, excelReader.GetDouble(3));
Assert.AreEqual(i + 5, excelReader.GetDouble(4));
}
excelReader.NextResult();
excelReader.Read();
Assert.AreEqual(0, excelReader.FieldCount);
excelReader.NextResult();
excelReader.Read();
Assert.AreEqual(0, excelReader.FieldCount);
// test dataset
DataSet result = excelReader.AsDataSet(Configuration.FirstRowColumnNamesConfiguration);
Assert.AreEqual(3, result.Tables.Count);
Assert.AreEqual(6, result.Tables[0].Columns.Count);
Assert.AreEqual(33, result.Tables[0].Rows.Count);
Assert.AreEqual("column a", result.Tables[0].Columns[0].ColumnName);
Assert.AreEqual(" column b", result.Tables[0].Columns[1].ColumnName);
Assert.AreEqual(" column b_1", result.Tables[0].Columns[2].ColumnName);
Assert.AreEqual("Column3", result.Tables[0].Columns[3].ColumnName);
Assert.AreEqual("column e", result.Tables[0].Columns[4].ColumnName);
Assert.AreEqual(" column b_2", result.Tables[0].Columns[5].ColumnName);
Assert.AreEqual(2, Convert.ToInt32(result.Tables[0].Rows[0][0]));
Assert.AreEqual("b", result.Tables[0].Rows[0][1]);
Assert.AreEqual("c", result.Tables[0].Rows[0][2]);
Assert.AreEqual("d", result.Tables[0].Rows[0][3]);
Assert.AreEqual(" e ", result.Tables[0].Rows[0][4]);
Assert.AreEqual(3, Convert.ToInt32(result.Tables[0].Rows[1][0]));
Assert.AreEqual(2, Convert.ToInt32(result.Tables[0].Rows[1][1]));
Assert.AreEqual(3, Convert.ToInt32(result.Tables[0].Rows[1][2]));
Assert.AreEqual(4, Convert.ToInt32(result.Tables[0].Rows[1][3]));
Assert.AreEqual(5, Convert.ToInt32(result.Tables[0].Rows[1][4]));
Assert.AreEqual(4, Convert.ToInt32(result.Tables[0].Rows[2][0]));
Assert.AreEqual(new DateTime(2012, 10, 13), result.Tables[0].Rows[2][1]);
Assert.AreEqual(new DateTime(2012, 10, 14), result.Tables[0].Rows[2][2]);
Assert.AreEqual(new DateTime(2012, 10, 15), result.Tables[0].Rows[2][3]);
Assert.AreEqual(new DateTime(2012, 10, 16), result.Tables[0].Rows[2][4]);
for (int i = 4; i < 33; i++)
{
Assert.AreEqual(i + 2, Convert.ToInt32(result.Tables[0].Rows[i][0]));
Assert.AreEqual(i + 3, Convert.ToInt32(result.Tables[0].Rows[i][1]));
Assert.AreEqual(i + 4, Convert.ToInt32(result.Tables[0].Rows[i][2]));
Assert.AreEqual(i + 5, Convert.ToInt32(result.Tables[0].Rows[i][3]));
Assert.AreEqual(i + 6, Convert.ToInt32(result.Tables[0].Rows[i][4]));
}
// Test default and overridden column name prefix
Assert.AreEqual("column a", result.Tables[0].Columns[0].ColumnName);
Assert.AreEqual("Column3", result.Tables[0].Columns[3].ColumnName);
DataSet prefixedResult = excelReader.AsDataSet(Configuration.FirstRowColumnNamesPrefixConfiguration);
Assert.AreEqual("column a", prefixedResult.Tables[0].Columns[0].ColumnName);
Assert.AreEqual("Prefix3", prefixedResult.Tables[0].Columns[3].ColumnName);
}
public static void FileUploadComplete_marcaciones(object sender, DevExpress.Web.FileUploadCompleteEventArgs e)
{
int cont = 0;
ro_empleado_info_list empleado_info_list = new ro_empleado_info_list();
ro_marcaciones_x_empleado_detLis_Info EmpleadoNovedadCargaMasiva_detLis_Info = new ro_marcaciones_x_empleado_detLis_Info();
List <ro_marcaciones_x_empleado_Info> lista_novedades = new List <ro_marcaciones_x_empleado_Info>();
decimal IdTransaccionSession = Convert.ToDecimal(SessionFixed.IdTransaccionSessionActual);
Stream stream = new MemoryStream(e.UploadedFile.FileBytes);
if (stream.Length > 0)
{
IExcelDataReader reader = null;
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
DateTime Fecha_registro;
DateTime marcacion;
while (reader.Read())
{
if (!reader.IsDBNull(0))
{
if (cont != 0)
{
string cedua = reader.GetString(0);
var empleado = empleado_info_list.get_list(IdTransaccionSession).Where(v => v.pe_cedulaRuc == cedua).FirstOrDefault();
if (empleado != null)
{
if (!reader.IsDBNull(2))// si tiene fehca de marcacion
{
Fecha_registro = Convert.ToDateTime(reader.GetDateTime(2));
if (!reader.IsDBNull(3))// si tiene entrada del primer turno
{
marcacion = Convert.ToDateTime(reader.GetValue(3));
if (marcacion.Hour != 0)
{
ro_marcaciones_x_empleado_Info info = new ro_marcaciones_x_empleado_Info
{
IdEmpleado = empleado.IdEmpleado,
IdEmpresa = empleado.IdEmpresa,
es_fechaRegistro = Fecha_registro,
IdCalendadrio = Convert.ToInt32(Fecha_registro.ToString("ddMMyyyy")),
IdNomina = empleado.IdTipoNomina,
IdUsuario = SessionFixed.IdUsuario,
es_Hora = new TimeSpan(marcacion.Hour, marcacion.Minute, 0),
IdTipoMarcaciones = cl_enumeradores.eTipoMarcacionRRHH.IN1.ToString(),
pe_NombreCompleato = empleado.Empleado,
pe_cedula = cedua,
EstadoBool = true,
IdRegistro = cont++
};
lista_novedades.Add(info);
}
}
if (!reader.IsDBNull(4))// si tiene salida del primer turno
{
marcacion = Convert.ToDateTime(reader.GetValue(4));
if (marcacion.Hour != 0)
{
ro_marcaciones_x_empleado_Info info = new ro_marcaciones_x_empleado_Info
{
IdEmpleado = empleado.IdEmpleado,
IdEmpresa = empleado.IdEmpresa,
es_fechaRegistro = Fecha_registro,
IdCalendadrio = Convert.ToInt32(Fecha_registro.ToString("ddMMyyyy")),
IdNomina = empleado.IdTipoNomina,
IdUsuario = SessionFixed.IdUsuario,
es_Hora = new TimeSpan(marcacion.Hour, marcacion.Minute, 0),
IdTipoMarcaciones = cl_enumeradores.eTipoMarcacionRRHH.OUT1.ToString(),
pe_NombreCompleato = empleado.Empleado,
pe_cedula = cedua,
EstadoBool = true,
IdRegistro = cont++
};
lista_novedades.Add(info);
}
}
if (!reader.IsDBNull(5))// si tiene entrada del segundo turno
{
marcacion = Convert.ToDateTime(reader.GetValue(5));
if (marcacion.Hour != 0)
{
ro_marcaciones_x_empleado_Info info = new ro_marcaciones_x_empleado_Info
{
IdEmpleado = empleado.IdEmpleado,
IdEmpresa = empleado.IdEmpresa,
es_fechaRegistro = Fecha_registro,
IdCalendadrio = Convert.ToInt32(Fecha_registro.ToString("ddMMyyyy")),
IdNomina = empleado.IdTipoNomina,
IdUsuario = SessionFixed.IdUsuario,
es_Hora = new TimeSpan(marcacion.Hour, marcacion.Minute, 0),
IdTipoMarcaciones = cl_enumeradores.eTipoMarcacionRRHH.IN2.ToString(),
pe_NombreCompleato = empleado.Empleado,
pe_cedula = cedua,
EstadoBool = true,
IdRegistro = cont++
};
lista_novedades.Add(info);
}
}
if (reader.IsDBNull(6))// si tiene salida del segundo turno
{
marcacion = Convert.ToDateTime(reader.GetValue(6));
if (marcacion.Hour != 0)
{
ro_marcaciones_x_empleado_Info info = new ro_marcaciones_x_empleado_Info
{
IdEmpleado = empleado.IdEmpleado,
IdEmpresa = empleado.IdEmpresa,
es_fechaRegistro = Fecha_registro,
IdCalendadrio = Convert.ToInt32(Fecha_registro.ToString("ddMMyyyy")),
IdNomina = empleado.IdTipoNomina,
IdUsuario = SessionFixed.IdUsuario,
es_Hora = new TimeSpan(marcacion.Hour, marcacion.Minute, 0),
IdTipoMarcaciones = cl_enumeradores.eTipoMarcacionRRHH.OUT2.ToString(),
pe_NombreCompleato = empleado.Empleado,
pe_cedula = cedua,
EstadoBool = true,
IdRegistro = cont++
};
lista_novedades.Add(info);
}
}
}
}
}
cont++;
}
}
EmpleadoNovedadCargaMasiva_detLis_Info.set_list(lista_novedades, Convert.ToDecimal(SessionFixed.IdTransaccionSession));
}
}
protected void HandleExcelFile(bool doImport)
{
int tenantId = (int)HttpContext.Current.Session["TenantID"];
if (ExcelFileUpload.HasFile)
{
using (var stream = ExcelFileUpload.PostedFile.InputStream)
{
IExcelDataReader excelReader = null;
try
{
if (ExcelFileUpload.FileName.EndsWith(".xlsx"))
{
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else if (ExcelFileUpload.FileName.EndsWith(".xls"))
{
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
this.Log().Info("Reading uploaded Excel file of events: {0}",
ExcelFileUpload.FileName);
if (excelReader == null)
{
throw new Exception("Could not parse Excel file, not .xls or .xlsx");
}
excelReader.IsFirstRowAsColumnNames = true;
var branchMap = new Dictionary <string, int>();
var codeTypes = DAL.CodeType.GetAll().Tables[0];
var branchCodeTypeId = (int)codeTypes
.Select("CodeTypeName = 'Branch'")
.First()["CTID"];
var branches = DAL.Codes.GetAlByTypeID(branchCodeTypeId).Tables[0];
foreach (DataRow branchRow in branches.Rows)
{
branchMap.Add(
((string)branchRow["Code"]).ToLower(),
(int)branchRow["CID"]
);
}
int?recordCount = null;
var problems = new List <string>();
while (excelReader.Read())
{
// skip column headings
if (recordCount == null)
{
recordCount = 0;
continue;
}
try
{
string name = null;
DateTime?date = null;
string description = null;
// can't be null for business rule validation
string secretCode = null;
int? pointsEarned = null;
string link = null;
bool hiddenFromPublic = false;
int? branchId = null;
try
{
name = excelReader.GetString(0);
if (string.IsNullOrWhiteSpace(name))
{
throw new Exception();
}
}
catch (Exception)
{
problems.Add(string.Format("Not inserting - empty event name on row {0}",
recordCount + 1));
continue;
}
try
{
date = excelReader.GetDateTime(1);
}
catch (Exception)
{
problems.Add(string.Format("Not inserting - invalid event date and time for event: {0}",
name));
continue;
}
try
{
description = excelReader.GetString(2);
if (string.IsNullOrWhiteSpace(description))
{
throw new Exception();
}
}
catch (Exception)
{
problems.Add(string.Format("Not inserting - missing description for event: {0}",
name));
continue;
}
if (excelReader.FieldCount >= 4)
{
try
{
secretCode = excelReader.GetString(3);
var lookupEvent = DAL.Event.GetEventByEventCode(secretCode.Trim());
if (lookupEvent.Tables.Count != 0 && lookupEvent.Tables[0].Rows.Count != 0)
{
problems.Add(string.Format("Skipping code - secret code {0} provided for event {1} is already in use.",
secretCode,
name));
}
}
catch (Exception)
{
}
}
if (excelReader.FieldCount >= 5)
{
try
{
var pointsEarnedString = excelReader.GetString(4);
if (!string.IsNullOrWhiteSpace(pointsEarnedString))
{
int pointsInt;
if (!int.TryParse(pointsEarnedString, out pointsInt))
{
problems.Add(string.Format("Skipping points earned - couldn't convert points earned to a number for: {0}",
name));
}
else
{
pointsEarned = pointsInt;
}
}
}
catch (Exception)
{
}
}
if (excelReader.FieldCount >= 6)
{
try
{
link = excelReader.GetString(5);
}
catch (Exception)
{
}
}
if (excelReader.FieldCount >= 7)
{
try
{
var hiddenFromString = excelReader.GetString(6);
string compareString = hiddenFromString.ToLower();
hiddenFromPublic = compareString.Contains("true") ||
compareString.Contains("yes") ||
compareString.Contains("1");
}
catch (Exception)
{
}
}
if (excelReader.FieldCount >= 8)
{
try
{
var branchString = excelReader.GetString(7);
if (branchString != null && branchString.Length > 0)
{
if (branchMap.ContainsKey(branchString.ToLower()))
{
branchId = branchMap[branchString.ToLower()];
}
else
{
problems.Add(string.Format("Skipping branch - couldn't find branch {0} specified for event {1}",
branchString,
name));
}
}
}
catch (Exception)
{
}
}
var newEvent = new DAL.Event
{
EventTitle = name,
EventDate = (DateTime)date,
HTML = description,
SecretCode = secretCode ?? string.Empty,
NumberPoints = pointsEarned ?? 0,
ExternalLinkToEvent = link ?? string.Empty,
HiddenFromPublic = hiddenFromPublic,
BranchID = branchId ?? 0
};
if (doImport)
{
DAL.Event.Insert(newEvent);
}
else
{
if (!newEvent.IsValid(BusinessRulesValidationMode.INSERT))
{
foreach (var errorCode in newEvent.ErrorCodes)
{
problems.Add(string.Format("Issue with event {0}: {1} {2}",
name,
errorCode.FieldName,
errorCode.ErrorMessage));
}
}
}
recordCount++;
}
catch (Exception ex)
{
// couldn't import this record
string problem = string.Format("Unable to import row {0}: {1}",
recordCount + 1,
ex.Message);
this.Log().Info(problem);
problems.Add(problem);
}
}
var result = new StringBuilder(string.Format("Read {0} records and encountered {1} errors in: {2}",
recordCount,
problems.Count,
ExcelFileUpload.FileName));
this.Log().Info(result.ToString());
if (problems != null && problems.Count > 0)
{
result.Append("<p>Problems with spreadsheet:<p><ul>");
foreach (var problem in problems)
{
result.Append("<li>");
result.Append(problem);
result.AppendLine("</li>");
}
result.AppendLine("</ul>");
}
PageMessage = result.ToString();
}
catch (Exception ex)
{
string result = string.Format("Error reading Excel file for event import: {0} - {1}",
ex.Message,
ex.StackTrace);
this.Log().Error(result);
PageError = result;
}
finally
{
if (excelReader != null)
{
if (!excelReader.IsClosed)
{
excelReader.Close();
}
excelReader.Dispose();
}
}
}
}
}
public DataTable GetSheetData(IExcelDataReader excelReader)
{
if (excelReader == null)
{
return(null);
}
// Create the table with the spreadsheet name
DataTable table = new DataTable(excelReader.Name);
table.Clear();
string value = null;
bool rowIsEmpty;
while (excelReader.Read())
{
DataRow row = table.NewRow();
rowIsEmpty = true;
for (int i = 0; i < excelReader.FieldCount; i++)
{
// If the column is null and this is the first row, skip
// to next iteration (do not want to include empty columns)
if (excelReader.IsDBNull(i) &&
(excelReader.Depth == 1 || i > table.Columns.Count - 1))
{
continue;
}
//value = excelReader.IsDBNull(i) ? "" : excelReader.GetString(i);
if (excelReader.GetFieldType(i).ToString() == "System.Double")
{
value = excelReader.IsDBNull(i) ? "" : excelReader.GetDouble(i).ToString();
}
if (excelReader.GetFieldType(i).ToString() == "System.Int")
{
value = excelReader.IsDBNull(i) ? "" : excelReader.GetInt32(i).ToString();
}
if (excelReader.GetFieldType(i).ToString() == "System.Bool")
{
value = excelReader.IsDBNull(i) ? "" : excelReader.GetBoolean(i).ToString();
}
if (excelReader.GetFieldType(i).ToString() == "System.DateTime")
{
value = excelReader.IsDBNull(i) ? "" : excelReader.GetDateTime(i).ToString();
}
if (excelReader.GetFieldType(i).ToString() == "System.TimeSpan")
{
value = excelReader.IsDBNull(i) ? "" : excelReader.GetDateTime(i).ToString();
}
if (excelReader.GetFieldType(i).ToString() == "System.String")
{
value = excelReader.IsDBNull(i) ? "" : excelReader.GetString(i).ToString();
}
if (excelReader.Depth == 0)
{
table.Columns.Add(value);
}
else
{
row[table.Columns[i]] = value;
}
if (!string.IsNullOrEmpty(value))
{
rowIsEmpty = false;
}
}
if (excelReader.Depth != 1 && !rowIsEmpty)
{
table.Rows.Add(row);
}
}
return(table);
}
public static void DoOpenOfficeTest(IExcelDataReader excelReader)
{
Assert.IsTrue(excelReader.IsValid);
excelReader.Read();
Assert.AreEqual(6, excelReader.FieldCount);
Assert.AreEqual("column a", excelReader.GetString(0));
Assert.AreEqual(" column b", excelReader.GetString(1));
Assert.AreEqual(" column b", excelReader.GetString(2));
Assert.IsNull(excelReader.GetString(3));
Assert.AreEqual("column e", excelReader.GetString(4));
Assert.AreEqual(" column b", excelReader.GetString(5));
excelReader.Read();
Assert.AreEqual(6, excelReader.FieldCount);
Assert.AreEqual(2, excelReader.GetInt32(0));
Assert.AreEqual("b", excelReader.GetString(1));
Assert.AreEqual("c", excelReader.GetString(2));
Assert.AreEqual("d", excelReader.GetString(3));
Assert.AreEqual(" e ", excelReader.GetString(4));
excelReader.Read();
Assert.AreEqual(6, excelReader.FieldCount);
Assert.AreEqual(3, excelReader.GetInt32(0));
Assert.AreEqual(2, excelReader.GetInt32(1));
Assert.AreEqual(3, excelReader.GetInt32(2));
Assert.AreEqual(4, excelReader.GetInt32(3));
Assert.AreEqual(5, excelReader.GetInt32(4));
excelReader.Read();
Assert.AreEqual(6, excelReader.FieldCount);
Assert.AreEqual(4, excelReader.GetInt32(0));
Assert.AreEqual(new DateTime(2012, 10, 13), excelReader.GetDateTime(1));
Assert.AreEqual(new DateTime(2012, 10, 14), excelReader.GetDateTime(2));
Assert.AreEqual(new DateTime(2012, 10, 15), excelReader.GetDateTime(3));
Assert.AreEqual(new DateTime(2012, 10, 16), excelReader.GetDateTime(4));
for (int i = 4; i < 34; i++)
{
excelReader.Read();
Assert.AreEqual(i + 1, excelReader.GetInt32(0));
Assert.AreEqual(i + 2, excelReader.GetInt32(1));
Assert.AreEqual(i + 3, excelReader.GetInt32(2));
Assert.AreEqual(i + 4, excelReader.GetInt32(3));
Assert.AreEqual(i + 5, excelReader.GetInt32(4));
}
excelReader.NextResult();
excelReader.Read();
Assert.AreEqual(0, excelReader.FieldCount);
excelReader.NextResult();
excelReader.Read();
Assert.AreEqual(0, excelReader.FieldCount);
//test dataset
DataSet result = excelReader.AsDataSet(true);
Assert.AreEqual(1, result.Tables.Count);
Assert.AreEqual(6, result.Tables[0].Columns.Count);
Assert.AreEqual(33, result.Tables[0].Rows.Count);
Assert.AreEqual("column a", result.Tables[0].Columns[0].ColumnName);
Assert.AreEqual(" column b", result.Tables[0].Columns[1].ColumnName);
Assert.AreEqual(" column b_1", result.Tables[0].Columns[2].ColumnName);
Assert.AreEqual("Column3", result.Tables[0].Columns[3].ColumnName);
Assert.AreEqual("column e", result.Tables[0].Columns[4].ColumnName);
Assert.AreEqual(" column b_2", result.Tables[0].Columns[5].ColumnName);
Assert.AreEqual(2, Convert.ToInt32(result.Tables[0].Rows[0][0]));
Assert.AreEqual("b", result.Tables[0].Rows[0][1]);
Assert.AreEqual("c", result.Tables[0].Rows[0][2]);
Assert.AreEqual("d", result.Tables[0].Rows[0][3]);
Assert.AreEqual(" e ", result.Tables[0].Rows[0][4]);
Assert.AreEqual(3, Convert.ToInt32(result.Tables[0].Rows[1][0]));
Assert.AreEqual(2, Convert.ToInt32(result.Tables[0].Rows[1][1]));
Assert.AreEqual(3, Convert.ToInt32(result.Tables[0].Rows[1][2]));
Assert.AreEqual(4, Convert.ToInt32(result.Tables[0].Rows[1][3]));
Assert.AreEqual(5, Convert.ToInt32(result.Tables[0].Rows[1][4]));
Assert.AreEqual(4, Convert.ToInt32(result.Tables[0].Rows[2][0]));
Assert.AreEqual(new DateTime(2012, 10, 13), result.Tables[0].Rows[2][1]);
Assert.AreEqual(new DateTime(2012, 10, 14), result.Tables[0].Rows[2][2]);
Assert.AreEqual(new DateTime(2012, 10, 15), result.Tables[0].Rows[2][3]);
Assert.AreEqual(new DateTime(2012, 10, 16), result.Tables[0].Rows[2][4]);
for (int i = 4; i < 33; i++)
{
Assert.AreEqual(i + 2, Convert.ToInt32(result.Tables[0].Rows[i][0]));
Assert.AreEqual(i + 3, Convert.ToInt32(result.Tables[0].Rows[i][1]));
Assert.AreEqual(i + 4, Convert.ToInt32(result.Tables[0].Rows[i][2]));
Assert.AreEqual(i + 5, Convert.ToInt32(result.Tables[0].Rows[i][3]));
Assert.AreEqual(i + 6, Convert.ToInt32(result.Tables[0].Rows[i][4]));
}
excelReader.Close();
}
protected void btnInsertExcel_Click(object sender, EventArgs e)
{
string[] validFileTypes = { "xlsx", "xls" };
string ext = System.IO.Path.GetExtension(FUExcel.PostedFile.FileName);
//bool isValidFile = false;
for (int i = 0; i < validFileTypes.Length; i++)
{
if (ext == "." + validFileTypes[i])
{
isValidFile = true;
break;
}
}
if (!isValidFile)
{
ScriptManager.GetCurrent(this.Page).SetFocus(this.FUExcel);
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('Please XLS or XLSX')", true);
return;
}
using (SqlConnection con = new SqlConnection(DatabaseManager.CONNECTION_STRING))
{
con.Open();
FileInfo fi = new FileInfo(FUExcel.FileName);
FUExcel.SaveAs(Server.MapPath("Upload/Excel/" + fi));
if (FUExcel.HasFile)
{
using (FileStream stream = File.Open(Server.MapPath("Upload/Excel/" + fi), FileMode.Open, FileAccess.Read))
{
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
//excelReader.IsFirstRowAsColumnNames = false;
int i = 0;
while (excelReader.Read())
{
if (i > 0)
{
using (SqlCommand com = new SqlCommand("INSERT INTO tb_equip (Equip_Rep ,Equip_Date ,Equip_location ,Equip_Name ,ID_Equip_Type ,Equip_Serial ,Equip_Asset ,Equip_Remark ,Date_Call_Claim ,Case_Claim ,Case_Brand ,Date_Claim ,Case_Remark ,Date_Sent ,Equip_Status ,Equip_File ,Created_Date ,Created_By) VALUES (@Equip_Rep ,@Equip_Date ,@Equip_location ,@Equip_Name ,@ID_Equip_Type ,@Equip_Serial ,@Equip_Asset ,@Equip_Remark ,@Date_Call_Claim ,@Case_Claim ,@Case_Brand ,@Date_Claim ,@Case_Remark ,@Date_Sent ,@Equip_Status ,@Equip_File ,@Created_Date ,@Created_By)", con))
{
if (string.IsNullOrEmpty(excelReader.GetString(0)))
{
com.Parameters.Add(new SqlParameter("Equip_Rep", DBNull.Value));
}
else
{
com.Parameters.Add(new SqlParameter("Equip_Rep", excelReader.GetString(0)));
}
//
if (excelReader.IsDBNull(1))
{
com.Parameters.Add(new SqlParameter("Equip_Date", DBNull.Value));
}
else
{
com.Parameters.Add(new SqlParameter("Equip_Date", excelReader.GetDateTime(1).AddYears(-543).ToString("MM/dd/yyyy")));
}
//
if (string.IsNullOrEmpty(excelReader.GetString(2)))
{
com.Parameters.Add(new SqlParameter("Equip_location", DBNull.Value));
}
else
{
com.Parameters.Add(new SqlParameter("Equip_location", excelReader.GetString(2)));
}
//
if (string.IsNullOrEmpty(excelReader.GetString(3)))
{
com.Parameters.Add(new SqlParameter("Equip_Name", DBNull.Value));
}
else
{
com.Parameters.Add(new SqlParameter("Equip_Name", excelReader.GetString(3)));
}
//
if (string.IsNullOrEmpty(excelReader.GetString(4)))
{
com.Parameters.Add(new SqlParameter("ID_Equip_Type", DBNull.Value));
}
else
{
com.Parameters.Add(new SqlParameter("ID_Equip_Type", excelReader.GetString(4)));
}
//
if (string.IsNullOrEmpty(excelReader.GetString(5)))
{
com.Parameters.Add(new SqlParameter("Equip_Serial", DBNull.Value));
}
else
{
com.Parameters.Add(new SqlParameter("Equip_Serial", excelReader.GetString(5)));
}
//
if (string.IsNullOrEmpty(excelReader.GetString(6)))
{
com.Parameters.Add(new SqlParameter("Equip_Asset", DBNull.Value));
}
else
{
com.Parameters.Add(new SqlParameter("Equip_Asset", excelReader.GetString(6)));
}
//
if (string.IsNullOrEmpty(excelReader.GetString(7)))
{
com.Parameters.Add(new SqlParameter("Equip_Remark", DBNull.Value));
}
else
{
com.Parameters.Add(new SqlParameter("Equip_Remark", excelReader.GetString(7)));
}
//
if (excelReader.IsDBNull(8))
{
com.Parameters.Add(new SqlParameter("Date_Call_Claim", DBNull.Value));
}
else
{
com.Parameters.Add(new SqlParameter("Date_Call_Claim", excelReader.GetDateTime(8).AddYears(-543).ToString("MM/dd/yyyy")));
}
//
if (excelReader.IsDBNull(9))
{
com.Parameters.Add(new SqlParameter("Case_Claim", DBNull.Value));
}
else
{
com.Parameters.Add(new SqlParameter("Case_Claim", excelReader.GetValue(9)));
}
//
if (string.IsNullOrEmpty(excelReader.GetString(10)))
{
com.Parameters.Add(new SqlParameter("Case_Brand", DBNull.Value));
}
else
{
com.Parameters.Add(new SqlParameter("Case_Brand", excelReader.GetString(10)));
}
//
if (excelReader.IsDBNull(11))
{
com.Parameters.Add(new SqlParameter("Date_Claim", DBNull.Value));
}
else
{
com.Parameters.Add(new SqlParameter("Date_Claim", excelReader.GetDateTime(11).AddYears(-543).ToString("MM/dd/yyyy")));
}
//
if (string.IsNullOrEmpty(excelReader.GetString(12)))
{
com.Parameters.Add(new SqlParameter("Case_Remark", DBNull.Value));
}
else
{
com.Parameters.Add(new SqlParameter("Case_Remark", excelReader.GetString(12)));
}
//
if (excelReader.IsDBNull(13))
{
com.Parameters.Add(new SqlParameter("Date_Sent", DBNull.Value));
}
else
{
com.Parameters.Add(new SqlParameter("Date_Sent", excelReader.GetDateTime(13).AddYears(-543).ToString("MM/dd/yyyy")));
}
//
com.Parameters.Add(new SqlParameter("Equip_Status", "N"));
com.Parameters.Add(new SqlParameter("Equip_File", fi.FullName));
com.Parameters.Add(new SqlParameter("Created_Date", DateTime.Now.AddYears(-543).ToString("MM/dd/yyyy hh:mm:ss.fff")));
com.Parameters.Add(new SqlParameter("Created_By", HttpContext.Current.Server.MachineName));
com.ExecuteNonQuery();
}
}
i++;
if (i == 0)
{
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('ERROR')", true);
}
else
{
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('Success')", true);
}
}
}
}
}
}