/// <summary>
/// Gets the Excel data from current spreadsheet
/// </summary>
/// <returns>The spreadsheet data table.</returns>
/// <param name="excelReader">Excel Reader.</param>
private DataTable GetExcelSheetData(IExcelDataReader excelReader)
{
if (excelReader == null)
{
Debug.LogError("Excel To Json Converter: Excel Reader is null. Cannot read data");
return null;
}
// Ignore sheets which start with ~
Regex sheetNameRegex = new Regex(@"^~.*$");
if (sheetNameRegex.IsMatch(excelReader.Name))
{
return null;
}
// Create the table with the spreadsheet name
DataTable table = new DataTable(excelReader.Name);
table.Clear();
string value = "";
bool rowIsEmpty;
// Read the rows and columns
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 this is the first row, add the values as columns
if (excelReader.Depth == 1)
{
table.Columns.Add(value);
}
else // Otherwise, add as rows
{
row[table.Columns[i]] = value;
}
if (!string.IsNullOrEmpty(value))
{
rowIsEmpty = false;
}
}
// Add the row to the table if it was not column headers and
// the row was not empty
if (excelReader.Depth != 1 && !rowIsEmpty)
{
table.Rows.Add(row);
}
}
return table;
}
public void Excel(string path)
{
try
{
FileStream stream = File.Open(path, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
// DataSet result = excelReader.AsDataSet();
// var n = result.Tables;
//var listss = new List<Listingss>();
while (excelReader.Read())
{
if (excelReader.GetValue(0) == null)
{
break;
}
else
{
var temp = new Listingss()
{
Title = excelReader?.GetString(0),
Price = excelReader?.GetValue(1)?.ToString(),
Description = excelReader?.GetString(2),
Brand = excelReader?.GetString(3),
Model = excelReader?.GetString(4),
Internal = excelReader?.GetValue(5)?.ToString(),
Image_1 = excelReader?.GetString(6),
Image_2 = excelReader?.GetString(7),
Image_3 = excelReader?.GetString(8),
Image_4 = excelReader?.GetString(9),
InternalStorage = 0
};
if (temp.Title.ToLower() != "title")
{
DataBasee.Lister.Add(temp);
}
else
{
}
}
}
stream.Dispose();
Console.WriteLine($"{DataBasee.Lister[0].Title} Read");
TestArea();
}
catch (IOException)
{
MessageBox.Show("The Sheet you're trying to view is already in use", "File Already Open", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
Clear();
}
catch (Exception ex)
{
MessageBox.Show("Unknown Error", "Error 0", MessageBoxButtons.OK, MessageBoxIcon.Error);
Clear();
}
}
public void Issue11435Colors()
{
using (IExcelDataReader excelReader = OpenReader("Test_Issue_11435_Colors"))
{
DataSet dataSet = excelReader.AsDataSet();
Assert.AreEqual("test1", dataSet.Tables[0].Rows[0][0].ToString());
Assert.AreEqual("test2", dataSet.Tables[0].Rows[0][1].ToString());
Assert.AreEqual("test3", dataSet.Tables[0].Rows[0][2].ToString());
excelReader.Read();
Assert.AreEqual("test1", excelReader.GetString(0));
Assert.AreEqual("test2", excelReader.GetString(1));
Assert.AreEqual("test3", excelReader.GetString(2));
}
}
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 void Issue_11435_Colors()
{
using (IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(Configuration.GetTestWorkbook("xTest_Issue_11435_Colors")))
{
excelReader.Read();
Assert.AreEqual("test1", excelReader.GetString(0));
Assert.AreEqual("test2", excelReader.GetString(1));
Assert.AreEqual("test3", excelReader.GetString(2));
DataSet dataSet = excelReader.AsDataSet();
Assert.AreEqual("test1", dataSet.Tables[0].Rows[0][0].ToString());
Assert.AreEqual("test2", dataSet.Tables[0].Rows[0][1].ToString());
Assert.AreEqual("test3", dataSet.Tables[0].Rows[0][2].ToString());
}
}
public IEnumerable <ReadCellValueResult> GetValues(ExcelSheet sheet, int rowIndex, IExcelDataReader reader)
{
return(ColumnNames.Select(columnName =>
{
int index = sheet.Heading.GetColumnIndex(columnName);
return new ReadCellValueResult(index, reader.GetString(index));
}));
}
/// <summary>
/// Parses the named indexes from the header record.
/// </summary>
private void ParseHeaderRecord()
{
// We assume all columns contain headers by default
_columnCount = _reader.FieldCount;
// First make sure we have a header record
if (!_reader.Read())
{
throw new ExcelReaderException("No header record was found.");
}
// Process each column in the header row
for (var i = 0; i < _columnCount; i++)
{
// Get the header name
var name = _reader.GetString(i);
if (string.IsNullOrEmpty(name))
{
// Header is null or empty, so we are done. This can happen if the file has more total columns
// in it than header rows, which can happen if some white space ends up in a right column
// or there are extra rows below the records
_columnCount = i;
break;
}
// Now store the named index for later for this header column
if (!_configuration.IsHeaderCaseSensitive)
{
name = name.ToLower();
}
if (_namedIndexes.ContainsKey(name))
{
_namedIndexes[name].Add(i);
}
else
{
_namedIndexes[name] = new List <int> {
i
};
}
}
// Move to the next row
_row++;
}
/// <summary>
/// Carga los puntajes de los usuarios desde el archivo excel
/// </summary>
/// <param name="excelReader">Componente encargado de deserializar el archivo excel</param>
/// <returns>Retorna lista de beneficiarios con sus puntajes pertenecientes al archivo</returns>
private List <UsuarioEntity> CargarPuntajesArchivo(IExcelDataReader excelReader)
{
List <UsuarioEntity> lista = new List <UsuarioEntity>();
while (excelReader.Read())
{
if (excelReader.Depth > 1 && excelReader.GetString(0) != null) // ignorar la fila 1 de cabeceras
{
UsuarioEntity registroArchivo = new UsuarioEntity();
registroArchivo.IdSolicitante = Convert.ToInt32(excelReader.GetString(0));
registroArchivo.PuntajeParticipacion = excelReader.GetString(1);
registroArchivo.CumplePuntaje = excelReader.GetString(2).Equals("NO") ? "0" : "1";
lista.Add(registroArchivo);
}
}
return(lista);
}
public void Load(string filename)
{
using (Stream stream = new FileStream(filename, FileMode.Open, FileAccess.Read))
{
IExcelDataReader reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
reader.IsFirstRowAsColumnNames = true;
reader.Read(); // Read first row
// Initialize languages
Dictionary <string, Dictionary <string, string> > languages = new Dictionary <string, Dictionary <string, string> >();
List <Dictionary <string, string> > languageList = new List <Dictionary <string, string> >();
for (int i = 1; i < reader.FieldCount; i++)
{
string language = reader.GetString(i);
Dictionary <string, string> dict;
if (!this.data.TryGetValue(language, out dict))
{
dict = new Dictionary <string, string>();
this.data[language] = dict;
}
languageList.Add(dict);
}
while (reader.Read())
{
string key = reader.GetString(0);
if (!string.IsNullOrEmpty(key))
{
for (int i = 0; i < Math.Min(languageList.Count, reader.FieldCount); i++)
{
try
{
languageList[i].Add(key, reader.GetString(i + 1));
}
catch (ArgumentException e)
{
throw new Exception(string.Format("Duplicate localization key: \"{0}\"", key), e);
}
}
}
}
}
}
public bool ReadType(IExcelDataReader reader, int i)
{
if (reader == null)
{
return(false);
}
Name = reader.GetString(i);
return(true);
}
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);
}
public static int GetIntFromString(this IExcelDataReader reader, int col)
{
var s = reader.GetString(col);
if (int.TryParse(s, out int r))
{
return(r);
}
return(0);
}
private static DocumentRow ParseRow(IExcelDataReader reader, Document parent)
{
var docRow = new DocumentRow(parent);
for (var i = 0; i < reader.FieldCount; i++)
{
docRow.Add(reader.GetString(i));
}
return(docRow);
}
public void cargaMasivaUsuarios(string path)
{
FileStream stream = File.Open(path, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
try
{
excelReader.Read();
while (excelReader.Read())
{
String idRol = "";
switch (excelReader.GetString(0))
{
case "Administrador":
idRol = "1";
break;
case "Operador del Sistema":
idRol = "2";
break;
case "Catedratico":
idRol = "3";
break;
case "Estudiante":
idRol = "4";
break;
}
insertarUsuarioCargaMasiva(idRol, excelReader.GetString(2), excelReader.GetString(4), excelReader.GetString(6), excelReader.GetString(7), Convert.ToString(excelReader.GetDouble(1)), Convert.ToString(excelReader.GetDouble(5)), excelReader.GetString(8));
}
excelReader.Close();
HttpContext.Current.Response.Redirect("../Administrador/Usuario/AdministrarUsuarios.aspx", true);
}
catch
{
HttpContext.Current.Response.Write("<script>window.alert('Error al leer archivo!');</script>");
excelReader.Close();
}
}
//Satır boyunca en az bir adet data mevcut mu kontrol ediyoruz.
public static bool AnyData(IExcelDataReader reader, int groupSize)
{
for (int i = 0; i < groupSize; i++)
{
if (reader.GetString(i) != null)
{
return(true);
}
}
return(false);
}
bool auth(ImapX.Message x, IExcelDataReader excelReader)
{
while (excelReader.Read())
{
if (excelReader.GetString(0) == x.From.Address)
{
return(true);
}
}
return(false);
}
private Dictionary <string, int> ReadHeader(IExcelDataReader rdr)
{
var header = new Dictionary <string, int>();
for (var i = 0; i < rdr.FieldCount; i++)
{
header.Add(rdr.GetString(i), i);
}
return(header);
}
public void readPrices(string path)
{
FileStream fs = File.Open(path, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(fs);
do
{
while (excelReader.Read())
{
foreach (string item in exceptions)
{
Details temporary = new Details(excelReader.GetString(0), excelReader.GetString(1), excelReader.GetDouble(2));
lines.Add(temporary);
rows++;
ConsoleOutputPrcINF(temporary, rows);
}
}
} while (excelReader.NextResult());
fs.Close();
}
private static List <string> ReadHeaderList(IExcelDataReader reader)
{
reader.Read();
var ListOfHeaders = new List <string>();
for (int i = 0; i < reader.FieldCount; i++)
{
ListOfHeaders.Add(reader.GetString(i));
}
return(ListOfHeaders);
}
public void Issue_4031_NullColumn()
{
using (IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(Configuration.GetTestWorkbook("xTest_Issue_4031_NullColumn")))
{
// DataSet dataSet = excelReader.AsDataSet(true);
excelReader.Read();
Assert.IsNull(excelReader.GetValue(0));
Assert.AreEqual("a", excelReader.GetString(1));
Assert.AreEqual("b", excelReader.GetString(2));
Assert.IsNull(excelReader.GetValue(3));
Assert.AreEqual("d", excelReader.GetString(4));
excelReader.Read();
Assert.IsNull(excelReader.GetValue(0));
Assert.IsNull(excelReader.GetValue(1));
Assert.AreEqual("Test", excelReader.GetString(2));
Assert.IsNull(excelReader.GetValue(3));
Assert.AreEqual(1, excelReader.GetDouble(4));
}
}
public void Issue4031NullColumn()
{
using (IExcelDataReader excelReader = OpenReader("Test_Issue_4031_NullColumn"))
{
// DataSet dataSet = excelReader.AsDataSet(true);
excelReader.Read();
Assert.IsNull(excelReader.GetValue(0));
Assert.AreEqual("a", excelReader.GetString(1));
Assert.AreEqual("b", excelReader.GetString(2));
Assert.IsNull(excelReader.GetValue(3));
Assert.AreEqual("d", excelReader.GetString(4));
excelReader.Read();
Assert.IsNull(excelReader.GetValue(0));
Assert.IsNull(excelReader.GetValue(1));
Assert.AreEqual("Test", excelReader.GetString(2));
Assert.IsNull(excelReader.GetValue(3));
Assert.AreEqual(1, excelReader.GetDouble(4));
}
}
private Dictionary <string, string> GetQuestionsFromFile(List <int> questionsNumbers)
{
FileStream stream = File.Open(excelFile, FileMode.Open, FileAccess.Read);
// Reading from a OpenXml Excel file (2003 format; *.xls)
//IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
// Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
Dictionary <string, string> QAndA = new Dictionary <string, string>();
int curr = 1;
totalQuestions = 0;
// Data Reader methods
while (excelReader.Read())
{
if (questionsNumbers.Contains(curr) && curr <= questionsNumbers.Max())
{
var cols = excelReader.FieldCount;
var t1 = excelReader.GetValue(0);
string q = excelReader.GetString(0);
string a = excelReader.GetString(1);
if (!QAndA.ContainsKey(q))
{
QAndA.Add(q, a);
}
}
curr++;
totalQuestions++;
}
// Free resources (IExcelDataReader is IDisposable)
excelReader.Close();
return(QAndA);
}
public List <object> Parse(string filePath, bool notDelSep, bool isAll)
{
var extension = Path.GetExtension(filePath);
IExcelDataReader excelReader = null;
var stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
switch (extension)
{
case ".xls":
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
break;
case ".xlsm":
case ".xlsx":
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
break;
}
if (excelReader == null)
{
return(null);
}
var excelDoc = new List <object>();
var index = 0;
while (excelReader.Read() && (isAll || index < 20))
{
index++;
var count = excelReader.FieldCount;
var obj = new object[count];
for (var i = 0; i < obj.Length; i++)
{
var value = excelReader.GetString(i);
if (value == null)
{
obj[i] = "";
continue;
}
obj[i] = notDelSep ? value : value.Replace(',', ' ');
}
excelDoc.Add(obj);
}
excelReader.Close();
return(excelDoc);
}
public void Issue_11435_Colors()
{
IExcelDataReader excelReader =
ExcelReaderFactory.CreateBinaryReader(Helper.GetTestWorkbook("Test_Issue_11435_Colors"));
DataSet dataSet = excelReader.AsDataSet(true);
Assert.AreEqual("test1", dataSet.Tables[0].Rows[0][0].ToString());
Assert.AreEqual("test2", dataSet.Tables[0].Rows[0][1].ToString());
Assert.AreEqual("test3", dataSet.Tables[0].Rows[0][2].ToString());
excelReader.Read();
Assert.AreEqual("test1", excelReader.GetString(0));
Assert.AreEqual("test2", excelReader.GetString(1));
Assert.AreEqual("test3", excelReader.GetString(2));
excelReader.Close();
}
private static HoldingList ReadHoldingList(IExcelDataReader reader, Book book, int target)
{
HoldingList holdingList = new HoldingList();
holdingList.LibraryId = DataRepository.Library.GetName(Constant.TargetLibraries[target]).LibraryId;
holdingList.BookId = DataRepository.Book.GetbyISBN(book.ISBN).BookId;
holdingList.Count = reader.GetInt32(10);
holdingList.ReceiptDate = reader.GetString(12).CleanNULL();
holdingList.Classification = book.KDCId == "K1000" ? true : false;
return(holdingList);
}
static void Main(string[] args)
{
//string imageBasePath = @"C:\Users\vd2\Source\Repos\negoshoe-inventory\NegoshoeInventory\ImagesUpload";
string imageBasePath = @"C:\Users\Valiant\Desktop\Item Pictures";
//string filePath = "Upload_Template_N.xlsx";
string filePath = "test1.xlsx";
FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
//IExcelDataReader reader = ExcelReaderFactory.CreateBinaryReader(stream);
IExcelDataReader reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
reader.IsFirstRowAsColumnNames = true;
int ctr = 0;
while (reader.Read())
{
if (ctr == 0)
{
ctr++; reader.Read();
}
string itemName = reader.GetString(0);
string description = reader.GetString(1);
string brand = reader.GetString(2);
string type = reader.GetString(3);
string totalQuantity = reader.GetString(4);
string officeQuantity = reader.GetString(5);
string houseQuantity = reader.GetString(6);
string filename = reader.GetString(7);
string remarks = reader.GetString(8);
byte[] imgBinary = ImageResizeHelper.ProcessResizeImage(Path.Combine(imageBasePath, filename));
var base64image = Convert.ToBase64String(imgBinary);
//string imageBase64 = reader.GetString(6);
string sql =
"INSERT INTO Items([ItemName],[Brand],[ProductType],[Description],[TotalQuantity],[OfficeQuantity],[HouseQuantity],[Filename],[ImageBase64],[Remarks]) " +
"VALUES('" + itemName + "'," + brand + "," + type + ",'" + description + "'," + totalQuantity + "," + officeQuantity + "," + houseQuantity + ",'" + filename + "','" + base64image + "','" + remarks + "');";
File.AppendAllText("output.sql", sql);
ctr++;
}
reader.Close();
}
private static void ParseAndPrintTransactions(IExcelDataReader reader, Dictionary <string, int> columns)
{
Thread.CurrentThread.CurrentCulture = new CultureInfo("ru-RU");
var date = DateTime.Parse(reader.GetString(columns[nameof(Transaction.Date)]));
var typeRaw = reader.GetString(columns[nameof(Transaction.Type)]);
TransactionType type = TransactionType.Unknown;
if (typeRaw.Contains("Покупка"))
{
type = TransactionType.Buy;
}
else if (typeRaw.Contains("Продажа"))
{
type = TransactionType.Sell;
}
var name = reader.GetString(columns[nameof(Transaction.Name)]);
var ticker = reader.GetString(columns[nameof(Transaction.Ticker)]);
var price = decimal.Parse(reader.GetString(columns[nameof(Transaction.Price)]));
var priceCur = reader.GetString(columns[nameof(Transaction.Currency)]);
var amount = int.Parse(reader.GetString(columns[nameof(Transaction.Amount)]));
var accumulatedCoupon = decimal.Parse(reader.GetString(columns[nameof(Transaction.AccumulatedCoupon)]));
var comission = decimal.Parse(reader.GetString(columns[nameof(Transaction.Comission)]));
var mode = reader.GetString(columns[nameof(Transaction.Mode)]);
PrintTransaction(new Transaction
{
AccumulatedCoupon = accumulatedCoupon,
Amount = amount,
Comission = comission,
Date = date,
Price = price,
Ticker = ticker,
Type = type,
Name = name.Replace("\"", ""),
Currency = priceCur,
Mode = mode
});
}
public void TestGitIssue145()
{
using (IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(Configuration.GetTestWorkbook("Test_Git_Issue_145.xls")))
{
excelReader.Read();
excelReader.Read();
excelReader.Read();
string value = excelReader.GetString(3);
Assert.AreEqual("Japanese Government Bonds held by the Bank of Japan", value);
}
}
private IEnumerable <ExcelVehicleViewModel> ParseExcelDocument(Stream inputStream)
{
var parsedData = new List <ExcelVehicleViewModel>();
IExcelDataReader excelDataReader = ExcelReaderFactory.CreateBinaryReader(inputStream);
excelDataReader.IsFirstRowAsColumnNames = true;
bool firstRow = true;
while (excelDataReader.Read())
{
if (firstRow)
{
firstRow = false;
continue;
}
var vehicle = new ExcelVehicleViewModel()
{
CarType = excelDataReader.GetString(0),
CarBrand = excelDataReader.GetString(1),
CarModel = excelDataReader.GetString(2),
CarModelVersion = excelDataReader.GetString(3),
Engine = excelDataReader.GetString(4),
EngineType = excelDataReader.GetString(5),
KW = excelDataReader.GetString(6),
HP = excelDataReader.GetString(7),
Fuel = excelDataReader.GetString(8),
Year = excelDataReader.GetString(9)
};
parsedData.Add(vehicle);
}
excelDataReader.Close();
return(parsedData);
}
public bool ReadName(IExcelDataReader reader, int i)
{
if (reader == null)
{
return(false);
}
if (reader.Read())
{
Name = reader.GetString(i);
}
return(true);
}
public IEnumerable <EnterpriseIndex> GetEnterpriseIndices(IExcelDataReader reader)
{
reader.Read();
while (reader.Read())
{
var enterprise = new Enterprise {
Id = reader.GetString(7), Name = reader.GetString(8)
};
yield return
(new EnterpriseIndex
{
Y = reader.GetDouble(0),
X1 = reader.GetDouble(1),
X2 = reader.GetDouble(2),
X3 = reader.GetDouble(3),
X4 = reader.GetDouble(4),
X5 = reader.GetDouble(5),
X6 = reader.GetDouble(6),
Enterprise = _context.Enterprises.Find(enterprise.Id) ?? enterprise
});
}
}
/// <summary>
/// Reads the first row to determine which column is located on which column-index.
/// After that HostEntries will be created using those indexes and added to the internal
/// list of HostEntries.
/// </summary>
private void parse(IExcelDataReader reader)
{
int ipIndex = -1;
int urlIndex = -1;
int protocolIndex = -1;
int rankingIndex = -1;
int fingerPrintIndex = -1;
int expirationIndex = -1;
int protocolVersionsIndex = -1;
int RC4Index = -1;
int beastIndex = -1;
int forwardSecrecyIndex = -1;
int heartbleedIndex = -1;
int signatureAlgoIndex = -1;
int poodleIndex = -1;
int extendedValidIndex = -1;
int openSSLCCSIndex = -1;
int HTTPServerSigIndex = -1;
int serverHostnameIndex = -1;
int _3DESCipherIndex = -1;
// Get headers
reader.Read();
int columnIndex = 0;
try
{
while (reader.GetString(columnIndex) != null)
{
string cmp = reader.GetString(columnIndex);
#region Column finding
if (cmp.Equals("IP") && ipIndex == -1) ipIndex = columnIndex;
else if (cmp.Contains("URL") && urlIndex == -1) urlIndex = columnIndex;
else if (cmp.ToLower().Contains("protocol versions") && protocolVersionsIndex == -1)
protocolVersionsIndex = columnIndex;
else if (cmp.Contains("RC4") && RC4Index == -1) RC4Index = columnIndex;
else if (cmp.ToLower().Contains("ranking") && rankingIndex == -1)
rankingIndex = columnIndex;
else if (cmp.ToLower().Equals("protocol") && protocolIndex == -1)
protocolIndex = columnIndex;
else if (cmp.ToLower().Contains("fingerprint") && fingerPrintIndex == -1)
fingerPrintIndex = columnIndex;
else if (cmp.ToLower().Contains("expiration") && expirationIndex == -1)
expirationIndex = columnIndex;
else if (cmp.ToLower().Contains("beast") && beastIndex == -1)
beastIndex = columnIndex;
else if (cmp.ToLower().Contains("forward secrecy") && forwardSecrecyIndex == -1)
forwardSecrecyIndex = columnIndex;
else if (cmp.ToLower().Contains("heartbleed") && heartbleedIndex == -1)
heartbleedIndex = columnIndex;
else if (cmp.ToLower().Contains("signature algorithm") && signatureAlgoIndex == -1)
signatureAlgoIndex = columnIndex;
else if (cmp.ToLower().Contains("poodle") && poodleIndex == -1)
poodleIndex = columnIndex;
else if (cmp.ToLower().Contains("extended validation") && extendedValidIndex == -1)
extendedValidIndex = columnIndex;
else if (cmp.ToLower().Contains("openssl ccs") && openSSLCCSIndex == -1)
openSSLCCSIndex = columnIndex;
else if (cmp.ToLower().Contains("http server sig") && HTTPServerSigIndex == -1)
HTTPServerSigIndex = columnIndex;
else if (cmp.ToLower().Contains("server host name") && serverHostnameIndex == -1)
serverHostnameIndex = columnIndex;
else if (cmp.ToLower().Contains("3des cipher presence") && _3DESCipherIndex == -1)
_3DESCipherIndex = columnIndex;
else
{
_customAttributes[columnIndex] = cmp;
}
#endregion
columnIndex += 1;
}
}
catch (Exception ex)
{
Debug.WriteLine(string.Format("Excel header reading touched outer bounds: {0}", ex.Message));
}
// Get rows and add them as children of each header
while (reader.Read())
{
HostEntry h = new HostEntry(getColumn(reader, urlIndex),
getColumn(reader, protocolIndex));
h.SetIP(getColumn(reader, ipIndex));
h.SetRanking(getColumn(reader, rankingIndex));
h.SetFingerPrintCert(getColumn(reader, fingerPrintIndex));
h.SetExpirationDate(getColumn(reader, expirationIndex));
h.SetProtocolVersions(getColumn(reader, protocolVersionsIndex));
h.SetBeastVulnerarbility(getColumn(reader, beastIndex));
h.SetForwardSecrecy(getColumn(reader, forwardSecrecyIndex));
h.SetHeartbleedVulnerability(getColumn(reader, heartbleedIndex));
h.SetSignatureAlgorithm(getColumn(reader, signatureAlgoIndex));
h.SetPoodleVulnerability(getColumn(reader, poodleIndex));
h.SetExtendedValidation(getColumn(reader, extendedValidIndex));
h.SetOpenSSLCCSVulnerable(getColumn(reader, openSSLCCSIndex));
h.SetHTTPServerSignature(getColumn(reader, HTTPServerSigIndex));
h.SetServerHostName(getColumn(reader, serverHostnameIndex));
h.Set3DESPresence(getColumn(reader, _3DESCipherIndex));
foreach (DictionaryEntry entry in _customAttributes)
{
h.AddCustomAttribute((string) entry.Value,
getColumn(reader, (int) entry.Key));
}
if (!h.IsEmpty()) entries.Add(h);
}
reader.Close();
ParserDelegator.CallOnParseComplete();
}
private static DocumentRow ParseRow(IExcelDataReader reader, Document parent)
{
var docRow = new DocumentRow(parent);
for (var i = 0; i < reader.FieldCount; i++)
docRow.Add(reader.GetString(i));
return docRow;
}
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();
}
/// <summary>
/// Will try to get the content of the passed column.
/// If it is empty or an error occurs, null will be returned.
/// </summary>
private string getColumn(IExcelDataReader reader, int index)
{
try
{
return reader.GetString(index);
}
catch (Exception)
{
return null;
}
}