/// <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 static List <TowerInfo> ReadExcel(string ePath)
{
List <TowerInfo> list = new List <TowerInfo>();
using (FileStream stream = File.Open(excelPath, FileMode.Open, FileAccess.Read, FileShare.Read))
{
using (IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream))
{
SetReader(excelReader);
excelReader.Read();
for (int i = 1; i < excelReader.RowCount; i++)
{
excelReader.Read();
TowerInfo info = new TowerInfo();
info.towerId = GetInt(0);
info.Name = GetString(1);
info.Introduce = GetString(2);
info.buildCoin = GetInt(3);
info.sellCoin = GetInt(4);
info.damageType = GetInt(5);
info.damageRange = GetFloat(6);
info.CD = GetFloat(7);
info.damage = GetInt(8);
info.Range = GetInt(9);
info.bullectPath = GetString(10);
info.nextTowerId = GetInt(11);
info.path = GetString(12);
info.audio = GetString(13);
info.helpSprite = GetString(14);
list.Add(info);
}
}
}
return(list);
}
public IEnumerator <T> GetEnumerator()
{
_dataReader.Reset();
EnsureCorrectSheetSelected();
EnsureColumnNamesResolved();
while (_dataReader.Read())
{
var instance = Activator.CreateInstance <T>();
foreach (var propertyMap in _configuration.PropertyMaps.Where(p => !p.Ignored))
{
var valueFromExcel = propertyMap.MapStrategy == ExcelIteratorPropertyMapStrategy.ByName
? _dataReader.GetValue(ResolveIndexByName(propertyMap))
: _dataReader.GetValue(propertyMap.ColumnIndex);
propertyMap.Property.SetValue(instance,
propertyMap.SourceValueConverter.ConvertValue(valueFromExcel));
}
yield return(instance);
}
}
public void UpdateMakes(IExcelDataReader reader)
{
reader.Read();
while (reader.Read())
{
int makeID = 0;
int.TryParse(reader[0].ToString(), out makeID);
if (makeID <= 0)
{
continue;
}
AutoMake make = AutoMakeService.GetByID(makeID);
if (make == null)
{
make = new AutoMake()
{
ID = makeID,
Name = reader[1].ToString(),
Name_ru = reader[2].ToString()
};
AutoMakeService.Create(make);
}
else
{
make.Name = reader[1].ToString();
make.Name_ru = reader[2].ToString();
AutoMakeService.Edit(make);
}
}
}
/// <summary>
/// 获取列名
/// </summary>
/// <param name="path"></param>
/// <param name="FirstRowIsColumnName"></param>
/// <returns></returns>
public static string[] GetColumnName(string path, bool FirstRowIsColumnName, int rowNumbeOfColumnNamer)
{
var file = new FileInfo(path);
string[] columnNames = null;
using (var stream = new FileStream(path, FileMode.Open))
{
IExcelDataReader reader = InitReader(path, stream);
reader.Read();
columnNames = new string[reader.FieldCount];
if (FirstRowIsColumnName)
{
for (int i = 1; i < rowNumbeOfColumnNamer; i++)
{
reader.Read();
}
for (int i = 0; i < reader.FieldCount; i++)
{
columnNames[i] = reader.GetString(i);
}
}
else
{
for (int i = 0; i < reader.FieldCount; i++)
{
columnNames[i] = string.Format("第" + i + "列");
}
}
}
return(columnNames);
}
public override bool Read()
{
if (ReferenceEquals(_reader, null))
{
return(false);
}
try
{
if (!_isFirstRowReaded)
{
var columnIndex = GetOriginalColumnIndex(0);
var readResult = _reader.Read() && _reader[columnIndex] is not null;
#if DEBUG
Log.Debug($"Read '{1}' rows with result: '{readResult}'");
#endif
return(readResult);
}
ReadFieldHeaders();
_isFirstRowReaded = false;
return(_reader.Read());
}
catch (Exception ex)
{
Log.Error(ex, $"Failed to read data from '{Source}'");
AddValidationError($"Failed to read data: '{ex.Message}'");
return(false);
}
}
private Task <bool> ReadRowSpecificSheet()
{
if (!inSpecificSheet)
{
if (specificSheetIndex != 0)
{
var currentSheetReaderIndex = 0;
do
{
reader.NextResult();
currentSheetReaderIndex++;
} while (specificSheetIndex == currentSheetReaderIndex);
}
inSpecificSheet = true;
}
var found = reader.Read();
if (!found)
{
return(Task.FromResult(false));
}
index++;
Current = new ExcelRow(index, CurrentSheet, reader);
return(Task.FromResult(true));
}
public static List <EnemyInfo> ReadExcel(string ePath)
{
List <EnemyInfo> list = new List <EnemyInfo>();
using (FileStream stream = File.Open(excelPath, FileMode.Open, FileAccess.Read, FileShare.Read))
{
using (IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream))
{
SetReader(excelReader);
excelReader.Read();
for (int i = 1; i < excelReader.RowCount; i++)
{
excelReader.Read();
EnemyInfo info = new EnemyInfo();
info.enemyId = GetInt(0);
info.Name = GetString(1);
info.Introduce = GetString(2);
info.killCoin = GetInt(3);
info.killDO = GetInt(4);
info.life = GetInt(5);
info.speed = GetInt(6);
info.Def = GetInt(7);
info.Mdef = GetInt(8);
info.heart = GetInt(9);
info.path = GetString(10);
info.audio = GetString(11);
info.helpSprite = GetString(12);
list.Add(info);
}
}
}
return(list);
}
public bool importData()
{
string filePath = @"C:\Users\Umapathy\Desktop\Book3.xlsx";
string DeviceID = "";
FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
excelReader.IsFirstRowAsColumnNames = true;
var MasterAppDataList = new List <MasterAppData>();
excelReader.Read();
while (excelReader.Read())
{
MasterAppDataList.Add(new MasterAppData
{
AppID = int.Parse(excelReader.GetString(0)),
AppDevice = excelReader.GetString(1),
Author = excelReader.GetString(3),
Title = excelReader.GetString(4),
Review = excelReader.GetString(5),
Version = excelReader.GetString(7),
Rating = int.Parse(excelReader.GetString(6)),
ReviewDate = DateTime.Parse(excelReader.GetString(2))
});
}
DataTable dt = MasterAppDataList.ToDataTable();
DBManager dbManager = new DBManager();
return(dbManager.UpdateMasterAppData(dt, DeviceID));
}
public void Issue_DateFormatButNotDate()
{
//we want to make sure that if a cell is formatted as a date but it's contents are not a date then
//the output is not a date (it was ending up as datetime.min)
IExcelDataReader excelReader =
ExcelReaderFactory.CreateOpenXmlReader(Helper.GetTestWorkbook("xTest_Issue_DateFormatButNotDate"));
excelReader.Read();
Assert.AreEqual("columna", excelReader.GetValue(0));
Assert.AreEqual("columnb", excelReader.GetValue(1));
Assert.AreEqual("columnc", excelReader.GetValue(2));
Assert.AreEqual("columnd", excelReader.GetValue(3));
Assert.AreEqual("columne", excelReader.GetValue(4));
excelReader.Read();
Assert.AreEqual(1D, excelReader.GetValue(0));
Assert.AreEqual(2D, excelReader.GetValue(1));
Assert.AreEqual(3D, excelReader.GetValue(2));
var value = excelReader.GetValue(3);
Assert.AreEqual(new DateTime(2013, 12, 10), value);
Assert.AreEqual("red", excelReader.GetValue(4));
excelReader.Read();
Assert.AreEqual("yellow", excelReader.GetValue(4));
excelReader.Close();
}
public static List <RoundData> ReadExcel(string ePath)
{
List <RoundData> list = new List <RoundData>();
using (FileStream stream = File.Open(excelPath, FileMode.Open, FileAccess.Read, FileShare.Read))
{
using (IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream))
{
excelReader.Read();
for (int i = 1; i < excelReader.RowCount; i++)
{
excelReader.Read();
RoundData rdata = new RoundData();
rdata.enemyList = new List <int>();
rdata.pathList = new List <Vector3>();
rdata.levelID = int.Parse(excelReader.GetString(0));
rdata.index = int.Parse(excelReader.GetString(1));
string[] enemyStrArr = excelReader.GetString(2).Split(',');
for (int j = 0; j < enemyStrArr.Length; j++)
{
rdata.enemyList.Add(int.Parse(enemyStrArr[j]));
}
string[] pathStrArr = excelReader.GetString(3).Split(',');
for (int k = 0; k < pathStrArr.Length; k++)
{
rdata.pathList.Add(PathPointRecord.instance.pathList[int.Parse(pathStrArr[k])]);
}
list.Add(rdata);
}
}
}
return(list);
}
private void ReadHeaderRow(IExcelDataReader rowReader, ref int skipRows)
{
var startingHeaderColumns = GetStartingHeaderColumns();
for (; skipRows > 0; --skipRows)
{
if (!rowReader.Read())
{
return;
}
}
if (!startingHeaderColumns.Any())
{
return;
}
while (true)
{
if (IsHeaderRowMatched(GetFields(rowReader), startingHeaderColumns))
{
break;
}
if (!rowReader.Read())
{
return;
}
}
}
public static List <UIPanelInfo> ReadExcel(string ePath)
{
List <UIPanelInfo> testDict = new List <UIPanelInfo>();
using (FileStream stream = File.Open(excelPath, FileMode.Open, FileAccess.Read, FileShare.Read))
{
using (IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream))
{
excelReader.Read();
//除了第一行的字段名 和最后一行空白
for (int i = 1; i < excelReader.RowCount; i++)
{
excelReader.Read();
UIPanelInfo pdata = new UIPanelInfo();
//数字类型需要转换
pdata.id = int.Parse(excelReader.GetString(0));
pdata.panelName = excelReader.GetString(1);
pdata.path = excelReader.GetString(2);
pdata.layer = excelReader.GetString(3);
testDict.Add(pdata);
}
}
}
return(testDict);
}
public void DataReader_NextResult_Test()
{
IExcelDataReader r = ExcelReaderFactory.CreateOpenXmlReader(Helper.GetTestWorkbook("xTestMultiSheet"));
Assert.AreEqual(3, r.ResultsCount);
var table = new DataTable();
table.Columns.Add("c1", typeof(int));
table.Columns.Add("c2", typeof(int));
table.Columns.Add("c3", typeof(int));
table.Columns.Add("c4", typeof(int));
int fieldCount = -1;
while (r.Read())
{
fieldCount = r.FieldCount;
table.Rows.Add(r.GetInt32(0), r.GetInt32(1), r.GetInt32(2), r.GetInt32(3));
}
Assert.AreEqual(12, table.Rows.Count);
Assert.AreEqual(4, fieldCount);
Assert.AreEqual(1, table.Rows[11][3]);
r.NextResult();
table.Rows.Clear();
table.TableName = r.Name;
while (r.Read())
{
fieldCount = r.FieldCount;
table.Rows.Add(r.GetInt32(0), r.GetInt32(1), r.GetInt32(2), r.GetInt32(3));
}
Assert.AreEqual(12, table.Rows.Count);
Assert.AreEqual(4, fieldCount);
Assert.AreEqual(2, table.Rows[11][3]);
r.NextResult();
table.TableName = r.Name;
table.Rows.Clear();
while (r.Read())
{
fieldCount = r.FieldCount;
table.Rows.Add(r.GetInt32(0), r.GetInt32(1));
}
Assert.AreEqual(5, table.Rows.Count);
Assert.AreEqual(2, fieldCount);
Assert.AreEqual(3, table.Rows[4][1]);
Assert.AreEqual(false, r.NextResult());
r.Close();
}
//[FUNCTION - ReadExcelData)
//Summarizes the proccess of excel data filtering
private void ReadExcelData()
{
excelReader.Read(); //skips first row
ProcessCourseData();
RemoveIrevSections();
SortCourses();
// WriteDebugFile();
}
public void TestBlankHeader()
{
using (IExcelDataReader excelReader = OpenReader("Test_BlankHeader"))
{
excelReader.Read();
Assert.AreEqual(6, excelReader.FieldCount);
excelReader.Read();
}
}
public List <object[]> ReadSheet(
int firstRowIndex = 0,
int?lastRowIndex = null,
int firstColumnIndex = 0,
int?lastColumnIndex = null,
int maxRows = int.MaxValue
)
{
List <object[]> list = new();
for (var i = 0; i < firstRowIndex; i++)
{
if (!_reader.Read())
{
return(new());
}
}
lastColumnIndex = Math.Min(
lastColumnIndex ?? int.MaxValue,
_reader.FieldCount - 1);
var columnCount = lastColumnIndex.Value - firstColumnIndex + 1;
if (columnCount < 0)
{
columnCount = 0;
}
for (var i = firstRowIndex;
(!lastRowIndex.HasValue || i <= lastRowIndex.Value) && list.Count < maxRows;
i++
)
{
if (!_reader.Read())
{
break;
}
var row = new object[columnCount];
for (var j = firstColumnIndex;
j <= (lastColumnIndex ?? (_reader.FieldCount - 1));
j++
)
{
if (j < _reader.FieldCount)
{
row[j - firstColumnIndex] = _reader.GetValue(j);
}
}
list.Add(row);
}
return(list);
}
private void button1_Click(object sender, EventArgs e)
{
const string FilePath = @"C:\mongodb\bin\TrafficStopsSample.xls";
FileStream stream = File.Open(FilePath, FileMode.Open, FileAccess.Read);
//1. Reading from a binary Excel file ('97-2003 format; *.xls)
using (IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream))
{
//...
//////2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
////IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
//...
//////3. DataSet - The result of each spreadsheet will be created in the result.Tables
////DataSet result = excelReader.AsDataSet();
//...
//4. DataSet - Create column names from first row
////excelReader.IsFirstRowAsColumnNames = true;
////DataSet result = excelReader.AsDataSet();
//this.textBox1.Text = string.Format("Rows imported: {0}", result.Tables[0].Rows.Count);
int i = 0;
//////5. Data Reader methods
var stopwatch = new System.Diagnostics.Stopwatch();
stopwatch.Start();
excelReader.Read(); //to get past header row
while (excelReader.Read())
{
var data = new CaryData
{
Id = i++,
CAD_Call = excelReader.GetString(0),
Call_Type = excelReader.GetString(1),
Address = excelReader.GetString(2),
Dt = new DateTime(2012, excelReader.GetInt32(5), excelReader.GetInt32(6)),
Time = excelReader.GetString(4),
Month = Int32.Parse(excelReader.GetString(5)),
Day = Int32.Parse(excelReader.GetString(6)),
Disposition = excelReader.GetString(7),
Streetno = excelReader.GetString(8),
Streetonly = excelReader.GetString(9),
Location = new[] { excelReader.GetDouble(10), excelReader.GetDouble(11) }
};
var col = database.GetCollection <CaryData>("trafficstops");
col.Save(data);
}
stopwatch.Stop();
this.textBox1.Text = "Done! Operation took " + stopwatch.Elapsed.TotalSeconds + " seconds.";
}
//6. Free resources (IExcelDataReader is IDisposable)
//excelReader.Close();
}
private List <ListItemModel> parseListExcel(ListImportFileModel file, UserProfile user, UserSelectedContext catalogInfo)
{
List <ListItemModel> returnValue = new List <ListItemModel>();
IExcelDataReader rdr = null;
if (Path.GetExtension(file.FileName)
.Equals(BINARY_EXCEL_EXTENSION, StringComparison.InvariantCultureIgnoreCase))
{
rdr = ExcelReaderFactory.CreateBinaryReader(file.Stream);
}
else
{
rdr = ExcelReaderFactory.CreateOpenXmlReader(file.Stream);
}
int itemNumberColumn = 0;
int labelColumn = -1;
if (file.IgnoreFirstLine)
{
rdr.Read(); // Skip the first line
for (int i = 0; i < rdr.FieldCount - 1; i++)
{
if (rdr.GetString(i)
.Equals("item", StringComparison.CurrentCultureIgnoreCase))
{
itemNumberColumn = i;
}
else if (rdr.GetString(i)
.Equals("label", StringComparison.CurrentCultureIgnoreCase))
{
labelColumn = i;
}
}
}
int itemPositionIndex = 0;
while (rdr.Read())
{
if (rdr.GetString(itemNumberColumn) != null)
{
returnValue.Add(new ListItemModel {
ItemNumber = rdr.GetString(itemNumberColumn)
.PadLeft(6, '0'),
Label = labelColumn == -1 ? string.Empty : rdr.GetString(labelColumn),
CatalogId = catalogInfo.BranchId,
Position = ++itemPositionIndex
});
}
}
return(returnValue);
}
public void Test_BlankHeader()
{
using (IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(Configuration.GetTestWorkbook("xTest_BlankHeader")))
{
excelReader.Read();
Assert.AreEqual(6, excelReader.FieldCount);
excelReader.Read();
for (int i = 0; i < excelReader.FieldCount; i++)
{
Console.WriteLine("{0}:{1}", i, excelReader.GetValue(i));
}
}
}
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);
}
}
public List <Competiteur> ImporterXLS(string cheminFichier)
{
List <Competiteur> ListeResult = new List <Competiteur>();
using (Stream stream = new FileStream(cheminFichier,
FileMode.Open,
FileAccess.Read,
FileShare.ReadWrite))
{
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
excelReader.IsFirstRowAsColumnNames = true;
//Skip First row
excelReader.Read();
while (excelReader.Read())
{
Competiteur competiteurTemporaire = new Competiteur
{
Nom = CultureInfo.CurrentCulture.TextInfo.ToTitleCase(excelReader.GetString(0).ToLower()),
Prenom = CultureInfo.CurrentCulture.TextInfo.ToTitleCase(excelReader.GetString(1).ToLower()),
Club = CultureInfo.CurrentCulture.TextInfo.ToTitleCase(excelReader.GetString(4).ToLower()),
};
Sexes sexOut;
if (Enum.TryParse(excelReader.GetString(2), out sexOut))
{
competiteurTemporaire.Sexe = (Sexes)Enum.Parse(typeof(Sexes), excelReader.GetString(2));
}
double doubleOut;
if (double.TryParse(excelReader.GetString(3), out doubleOut))
{
competiteurTemporaire.Poids = Convert.ToDouble(excelReader.GetString(3));
}
else
{
competiteurTemporaire.Poids = 0;
}
Categories categoriesOut;
if (Enum.TryParse(excelReader.GetString(5), out categoriesOut))
{
competiteurTemporaire.Categorie = (Categories)Enum.Parse(typeof(Categories), excelReader.GetString(5));
}
ListeResult.Add(competiteurTemporaire);
}
}
return(ListeResult);
}
private static string[] GetConcernNames(IExcelDataReader reader)
{
var concernNames = new string[reader.FieldCount - Offset];
reader.Read();
reader.Read();
for (var i = Offset; i < reader.FieldCount; i++)
{
var name = reader.GetString(i);
concernNames[i - Offset] = name;
}
return(concernNames);
}
public void Test_BlankHeader()
{
IExcelDataReader excelReader =
ExcelReaderFactory.CreateOpenXmlReader(Helper.GetTestWorkbook("xTest_BlankHeader"));
excelReader.Read();
Assert.AreEqual(6, excelReader.FieldCount);
excelReader.Read();
for (int i = 0; i < excelReader.FieldCount; i++)
{
Console.WriteLine("{0}:{1}", i, excelReader.GetString(i));
}
excelReader.Close();
}
public void Issue_12271_NextResultSet()
{
Excel.Log.Log.InitializeWith <Log4NetLog>();
IExcelDataReader excelReader =
ExcelReaderFactory.CreateOpenXmlReader(Helper.GetTestWorkbook("xTest_LotsOfSheets"));
//excelReader.IsFirstRowAsColumnNames = true;
do
{
excelReader.Read();
if (excelReader.FieldCount == 0)
{
continue;
}
//ignore sheets beginning with $e
if (excelReader.Name.StartsWith("$e", StringComparison.InvariantCultureIgnoreCase))
{
continue;
}
Assert.AreEqual("StaffName", excelReader.GetString(0));
} while (excelReader.NextResult());
excelReader.Close();
}
/// <summary>
/// 获得表格数据
/// </summary>
private static DataTable GetTable(IExcelDataReader excelReader)
{
DataTable dt = new DataTable();
dt.TableName = excelReader.Name;
bool isInit = false;
string[] ItemArray = null;
while (excelReader.Read())
{
if (!isInit)
{
isInit = true;
for (int i = 0; i < excelReader.FieldCount; i++)
{
dt.Columns.Add("", typeof(string));
}
ItemArray = new string[excelReader.FieldCount];
}
if (excelReader.IsDBNull(0))
{
continue;
}
for (int i = 0; i < excelReader.FieldCount; i++)
{
string value = excelReader.IsDBNull(i) ? "" : excelReader.GetString(i);
ItemArray[i] = value;
}
dt.Rows.Add(ItemArray);
}
return(dt);
}
static void Main(string[] args)
{
string filePath = "C:/Users/Admin/Downloads/test.xlsx";
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);
//...
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
//...
//3. DataSet - The result of each spreadsheet will be created in the result.Tables
//DataSet result = excelReader.AsDataSet();
//...
//4. DataSet - Create column names from first row
//excelReader.IsFirstRowAsColumnNames = true;
//excelReader.
//DataSet result = excelReader.AsDataSet();
//5. Data Reader methods
while (excelReader.Read())
{
int colCnt = excelReader.FieldCount;
Console.WriteLine(excelReader.GetString(colCnt - 1));
break;
//excelReader.GetInt32(0);
}
//6. Free resources (IExcelDataReader is IDisposable)
excelReader.Close();
}
private List <T> ParseFile <T>(IExcelDataReader reader) where T : Element
{
var data = new List <T>();
var columns = new List <string>();
while (reader.Read())
{
// The row type is determined by the value of the first column:
// #PROPERTY_ID --> header row
// empty --> data row
// other --> comment
var value = GetString(reader, 0);
if (value == "#PROPERTY_ID")
{
columns = ParseHeaders(reader);
}
else if (value.Length == 0)
{
data.Add(ParseElement <T>(reader, columns));
}
}
return(data);
}
internal SheetData(IExcelDataReader reader)
{
ColumnCount = reader.FieldCount;
Name = reader.Name;
while (reader.Read())
{
List <string> row = new List <string>(ColumnCount);
bool isNullRow = true;
for (int i = 0; i < ColumnCount; ++i)
{
var val = reader.GetValue(i);
string strVal = "";
if (val is DateTime)
{
var fmt = reader.GetNumberFormatString(i);
strVal = ((DateTime)val).ToString(fmt.Replace('h', 'H'));
}
else
{
strVal = val.ToString();
}
row.Add(strVal);
}
if (!isNullRow)
{
data.Add(row);
}
}
}
protected void btn_Import_Click(object sender, EventArgs e)
{
FileStream stream = File.Open("E:/Technovil/Managers.xls", FileMode.Open, FileAccess.Read);
// Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
//...
// DataSet - The result of each spreadsheet will be created in the result.Tables
// DataSet - Create column names from first row
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();
//5. Data Reader methods
while (excelReader.Read())
{
excelReader.GetString(0);
}
//6. Free resources (IExcelDataReader is IDisposable)
excelReader.Close();
//Show in GridView
gv.DataSource = result;
gv.DataBind();
}
public SalesReport ParseExcelDataReader(IExcelDataReader reader)
{
if (reader == null)
{
throw new ArgumentNullException(nameof(reader));
}
var sales = new List <Sale>();
while (reader.Read())
{
var computerId = reader.GetString(0);
var amount = reader.GetString(1);
var nextSale = new Sale();
nextSale.ComputerId = int.Parse(computerId);
nextSale.Amount = decimal.Parse(amount);
sales.Add(nextSale);
}
var salesReport = new SalesReport();
salesReport.Sales = sales;
return(salesReport);
}
protected virtual Document Parse(IExcelDataReader reader)
{
var doc = new Document();
var isFirst = true;
while (reader.Read())
{
if (isFirst && IsFirstRowHeaders)
doc.SetHeaders(ParseRow(reader, doc));
else
doc.Add(ParseRow(reader, doc));
isFirst = false;
}
return doc;
}
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>
/// 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();
}