public IEnumerable<IDictionary<string, object>> ExtractData(Stream fileStream)
{
try
{
Reader = ExcelReaderFactory.CreateBinaryReader(fileStream);
}
catch (ArgumentOutOfRangeException)
{
//Known bug with excel reader sometimes will throw argument out of range exception http://exceldatareader.codeplex.com/discussions/431882
var contents = ((MemoryStream) (fileStream)).ToArray();
TempFileName = Path.GetTempFileName();
File.WriteAllBytes(TempFileName, contents);
TempFileStream = new FileStream(TempFileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
Reader = ExcelReaderFactory.CreateBinaryReader(TempFileStream);
}
ReadHeader();
return ReadFileContents();
}
public ExcelToCsv(string ipfilename)
{
try
{
inputStream = File.Open(ipfilename, FileMode.Open, FileAccess.Read);
// Read from a *.xls file (97-2003 format)
if (Path.GetExtension(ipfilename) == ".xls")
excelReader = ExcelReaderFactory.CreateBinaryReader(inputStream);
// Read from a *.xlsx file (2007 format)
else
excelReader = ExcelReaderFactory.CreateOpenXmlReader(inputStream);
// DataSet - The result of each spreadsheet will be created in the result.Tables
result = excelReader.AsDataSet();
}
catch (Exception e)
{
Console.WriteLine("\nAn exception occured while trying to read the input file.");
Console.WriteLine(e.ToString());
}
}
private IEnumerable<SaleReportDto> CreateSalesReports(DateTime date, IExcelDataReader dataReader)
{
var reports = new List<SaleReportDto>();
var worksheet = dataReader.AsDataSet().Tables["Sales"];
IList<DataRow> rows = (from DataRow r in worksheet.Rows select r).ToList();
var location = rows[1].ItemArray[1].ToString();
var rowsCount = rows.Count();
for (int i = 3; i < rowsCount - 1; i++)
{
var productName = rows[i].ItemArray[1].ToString();
var quantity = Convert.ToInt32(rows[i].ItemArray[2]);
var price = Convert.ToDecimal(rows[i][3]);
var report = new SaleReportDto(productName, quantity, price, date, location);
reports.Add(report);
}
return reports;
}
private static void AddSalesToDb(IExcelDataReader excelReader, DateTime currentDate)
{
using (var db = new ChainOfSupermarketsContext())
{
var salesTable = excelReader.AsDataSet().Tables["Sales"];
var locationName = (string)salesTable.Rows[1].ItemArray[1];
var currentLocation = GetOrCreateLocation(locationName, db);
for (var i = 3; i < salesTable.Rows.Count; i++)
{
if (((string)salesTable.Rows[i].ItemArray[1]).Contains("Total sum"))
{
break;
}
var productName = (string)salesTable.Rows[i].ItemArray[1];
productName = Regex.Replace(productName, @"[^\w'\. ]", string.Empty);
var currentProduct = GetOrCreateProduct(productName, db);
var quantity = (double)salesTable.Rows[i].ItemArray[2];
var pricePerUnit = (double)salesTable.Rows[i].ItemArray[3];
db.Sales.Add(new Sale
{
Location = currentLocation,
DateOfSale = currentDate,
Product = currentProduct,
Quantity = (decimal)quantity,
PricePerUnit = (decimal)pricePerUnit
});
}
db.SaveChanges();
}
}
private Dictionary<string, string> FetchData(IExcelDataReader excelData)
{
DataTable excelDataTable = excelData.AsDataSet().Tables[0];
int columnCount = excelDataTable.Columns.Count;
var query = from a in excelDataTable.AsEnumerable()
select a;
Dictionary<string, string> ColumnValues = new Dictionary<string, string>();
foreach (var item in query)
{
ColumnValues.Add(ValidateStudentNumber(item.Field<double>(0)),
item.Field<string>(1));
}
return ColumnValues;
}
/// <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;
}
private static void ResetFromFixtureSheet(SqlConnectionAdapter conn, IExcelDataReader excelReader)
{
var schemas = ReadSchemas(excelReader);
PopulateAll(conn, schemas);
}
public static void setExcelFilePath(String filePath)
{
FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
excelReader.IsFirstRowAsColumnNames = true;
result = excelReader.AsDataSet();
}
public IExcelDataReader getExcelReader()
{
// ExcelDataReader works with the binary Excel file, so it needs a FileStream
// to get started. This is how we avoid dependencies on ACE or Interop:
FileStream stream = File.Open(_path, FileMode.Open, FileAccess.Read);
// We return the interface, so that
IExcelDataReader reader = null;
try
{
if (_path.EndsWith(".xls"))
{
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
if (_path.EndsWith(".xlsx"))
{
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
return reader;
}
catch (Exception)
{
throw;
}
}
private void AddSalesToDB(IExcelDataReader excelReader, DateTime currentDate)
{
var salesTable = excelReader.AsDataSet().Tables["Sales"];
var locationName = (string)salesTable.Rows[1].ItemArray[1];
var currentLocation = GetOrCreateLocation(locationName);
for (var i = 3; i < salesTable.Rows.Count; i++)
{
if (((string)salesTable.Rows[i].ItemArray[1]).Contains("Total sum"))
{
break;
}
var productName = (string)salesTable.Rows[i].ItemArray[1];
var quantity = (double)salesTable.Rows[i].ItemArray[2];
var pricePerUnit = (double)salesTable.Rows[i].ItemArray[3];
var currentProduct = GetOrCreateProduct(productName,pricePerUnit);
Sale currentSale = new Sale
{
LocationId = currentLocation.Id,
DateOfSale = currentDate,
ProductId = currentProduct.Id,
Quantity = (decimal) quantity,
PricePerUnit = (decimal) pricePerUnit
};
this.db.Sales.Add(currentSale);
}
this.db.SaveChanges();
}
public void Initialize()
{
try
{
FileStream excelWorkBookStream = File.Open(_excelDocumentName, FileMode.Open, FileAccess.Read);
int lastIndexOfExtension = _excelDocumentName.LastIndexOf(".");
string workBookExtension = _excelDocumentName.Substring(lastIndexOfExtension + 1);
//the mode in which the workbook needs to be opened is dependent on the version of MS Excel that
//created the workbook. Excel 97-2003 write the excel in binary format while Excel 2007 writes
//out in the OpenXML format.
if (0 == string.Compare(workBookExtension, "xls", StringComparison.InvariantCultureIgnoreCase))
_excelDataReader = Factory.CreateReader(excelWorkBookStream, ExcelFileType.Binary);
else if (0 == string.Compare(workBookExtension, "xlsx", StringComparison.InvariantCultureIgnoreCase))
_excelDataReader = Factory.CreateReader(excelWorkBookStream, ExcelFileType.OpenXml);
else
throw new ApplicationException("Invalid or unsupported workbook format");
}
catch (Exception ex)
{
Close();
throw new ApplicationException(ex.Message);
}
}
public IBaseMessage GetNext(IPipelineContext pContext)
{
if (_reader == null)
{
return null;
}
if (!_reader.Read())
{
_reader.Dispose();
_reader = null;
return null;
}
StringBuilder xmlBuilder = new StringBuilder();
xmlBuilder.Append(@"<?xml version=""1.0"" ?>");
xmlBuilder.Append(@"<Messages xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns=""http://tempuri.net/ExcelUpload.Messages"">");
xmlBuilder.Append(@"<AddProduct>");
xmlBuilder.Append(@"<BatchId></BatchId>");
xmlBuilder.Append(@"<RegistrationIndex></RegistrationIndex>");
xmlBuilder.Append(@"<RegistrationsInBatch></RegistrationsInBatch>");
xmlBuilder.Append(@"<BatchSourcePath></BatchSourcePath>");
xmlBuilder.Append(@"<OriginatorDestination></OriginatorDestination>");
xmlBuilder.AppendFormat(@"<Name>{0}</Name>", _reader.GetString(0));
xmlBuilder.AppendFormat(@"<ProductNumber>{0}</ProductNumber>", _reader.GetString(1));
xmlBuilder.AppendFormat(@"<SafetyStockLevel>{0}</SafetyStockLevel>", _reader.GetInt32(2));
xmlBuilder.AppendFormat(@"<ReorderPoint>{0}</ReorderPoint>", _reader.GetInt32(3));
xmlBuilder.AppendFormat(@"<StandardCost>{0}</StandardCost>", _reader.GetDecimal(4));
xmlBuilder.AppendFormat(@"<ListPrice>{0}</ListPrice>", _reader.GetDecimal(5));
xmlBuilder.AppendFormat(@"<DaysToManufacture>{0}</DaysToManufacture>", _reader.GetInt32(6));
//write date in XSD format:
xmlBuilder.AppendFormat(@"<SellStartDate>{0}</SellStartDate>", DateTime.FromOADate(_reader.GetDouble(7)).ToString("yyyy-MM-ddTHH:mm:ss.fffffff"));
xmlBuilder.Append(@"</AddProduct>");
xmlBuilder.Append(@"</Messages>");
byte[] data = Encoding.UTF8.GetBytes(xmlBuilder.ToString());
MemoryStream memStream = new MemoryStream(data);
IBaseMessagePart messagePart = pContext.GetMessageFactory().CreateMessagePart();
messagePart.Data = memStream;
messagePart.ContentType = "text/xml";
messagePart.Charset = "utf-8";
IBaseMessage outMsg = pContext.GetMessageFactory().CreateMessage();
outMsg.AddPart("Body", messagePart, true);
foreach (ContextProperty property in _contextProperties)
{
if (property.IsPromoted)
outMsg.Context.Promote(property.Name, property.Namespace, property.Value);
else
outMsg.Context.Write(property.Name, property.Namespace, property.Value);
}
outMsg.Context.Promote("MessageType", "http://schemas.microsoft.com/BizTalk/2003/system-properties", "http://tempuri.net/ExcelUpload.Messages#Messages");
return outMsg;
}
private IExcelDataReader SwitchToWorksheet(IExcelDataReader excelDataReader, int index)
{
var current = 0;
while (current < index)
{
excelDataReader.NextResult();
current++;
}
return excelDataReader;
}
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;
}
private static IExcelDataReader GetExcelReader(string xlPath, string sheetName)
{
if (cache.ContainsKey(sheetName))
{
reader = cache[sheetName];
}
else
{
stream = new FileStream(xlPath, FileMode.Open, FileAccess.Read);
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
cache.Add(sheetName, reader);
}
return reader;
}
public ObjImport(string filePath, string fileExtension)
{
_filePath = filePath;
_fileExtension = fileExtension;
FileStream stream = File.Open(_filePath, FileMode.Open, FileAccess.Read);
if (_fileExtension == ".xls")
{
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else
{
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
}
private void Init()
{
FileStream stream = File.Open(Path, FileMode.Open, FileAccess.Read);
// Make a decision on which reader to use
if (Path.Contains("xlsx"))
{
// OpenXml Excel file (2007 format; *.xlsx)
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else
{
//binary Excel file ('97-2003 format; *.xls)
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
}
public void Disassemble(IPipelineContext pContext, IBaseMessage pInMsg)
{
//load up the Excel reader:
Stream originalStream = pInMsg.BodyPart.GetOriginalDataStream();
string fileName = (string) pInMsg.Context.Read("ReceivedFileName", "http://schemas.microsoft.com/BizTalk/2003/file-properties");
_reader = GetDataReader(fileName, originalStream);
//store the incoming context properties to write out again:
_contextProperties = new List<ContextProperty>();
for (int i = 0; i < pInMsg.Context.CountProperties; i++)
{
ContextProperty property = new ContextProperty();
property.Value = pInMsg.Context.ReadAt(i, out property.Name, out property.Namespace);
property.IsPromoted = pInMsg.Context.IsPromoted(property.Name, property.Namespace);
_contextProperties.Add(property);
}
}
public List <CustomExcelData> GetExcelData()
{
List <CustomExcelData> excelDataList = new List <CustomExcelData>();
OpenFileDialog opn = new OpenFileDialog();
opn.Filter = "Excel Files|*.xls;*.xlsx;*.xlsm";
if (opn.ShowDialog() == DialogResult.Cancel)
{
return(null);
}
FileStream strm = new FileStream(opn.FileName, FileMode.Open);
IExcelDataReader excldr = ExcelReaderFactory.CreateOpenXmlReader(strm);
DataSet rslt = excldr.AsDataSet();
DataTable dt = rslt.Tables[0];
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
excelDataList.Add(new CustomExcelData(row));
}
}
return(excelDataList);
}
public DataTable Import(string filePath)
{
var stream = System.IO.File.Open(filePath, FileMode.Open, FileAccess.Read);
var file = new FileInfo(filePath);
IExcelDataReader excelReader = null;
//1. Reading from a binary Excel file ('97-2003 format; *.xls)
if (file.Extension == ".xls")
{
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
else if (file.Extension == ".xlsx")
{
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
//4. DataSet - Create column names from first row
if (excelReader != null)
{
excelReader.IsFirstRowAsColumnNames = true;
var result = excelReader.AsDataSet();
//5. Data Reader methods
if (result != null && result.Tables.Count > 0 && result.Tables[0].Rows.Count > 0)
{
return(result.Tables[0]);
}
//6. Free resources (IExcelDataReader is IDisposable)
excelReader.Close();
}
return(null);
}
public static void DealExcel(params IDealExcel[] deal)
{
var selects = Selection.GetFiltered <UnityEngine.Object>(SelectionMode.Assets);
foreach (var item in selects)
{
string path = AssetDatabase.GetAssetPath(item);
string suffix = GetSuffix(path);
FileStream stream = File.Open(path, FileMode.Open, FileAccess.Read, FileShare.Read);
IExcelDataReader excelReader = null;
//Debug.Log(suffix);
if (suffix.Equals("xlsx"))
{
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else if (suffix.Equals("xls"))
{
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
if (excelReader != null)
{
var set = excelReader.AsDataSet();
for (int i = 0; i < deal.Length; i++)
{
var dealer = deal[i];
var savePath = $"{Application.dataPath} /{dealer.floader}";
if (!Directory.Exists(savePath))
{
Directory.CreateDirectory(savePath);
}
File.WriteAllText($"{savePath}/{dealer.fileName}", dealer.Run(set.Tables[i]));
}
AssetDatabase.Refresh();
}
}
}
//click event handler allows user to choose the Excel file
private void btnBrowse_Click(object sender, EventArgs e)
{
// opens the window to browse through and choose file
using (OpenFileDialog openFileDialog = new OpenFileDialog()
{
Filter = "Excel Document|*.xls; *.xlsx"
})
{
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
textFilename.Text = openFileDialog.FileName;
using (var stream = File.Open(openFileDialog.FileName, FileMode.Open, FileAccess.Read))
{
// CreateReader method allows user to read the excel file, the data is returned as a DataSet
using (IExcelDataReader reader = ExcelReaderFactory.CreateReader(stream))
{
DataSet result = reader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
});
// read all the table names in the DataSet
tableCollection = result.Tables;
cboSheet.Items.Clear();
// add table to the combobox
foreach (DataTable myTable in tableCollection)
{
cboSheet.Items.Add(myTable.TableName);
}
}
}
}
}
}
static Dictionary <int, List <string[]> > ReadExcelData(TextAsset excelAsset, bool readExtraSheet)
{
IExcelDataReader reader = ExcelReaderFactory.CreateBinaryReader(new MemoryStream(excelAsset.bytes));
Dictionary <int, List <string[]> > result = new Dictionary <int, List <string[]> >();
do
{
result.Add(result.Count, new List <string[]>());
while (reader.Read())
{
string[] row = new string[reader.FieldCount];
for (int i = 0; i < row.Length; i++)
{
string data = reader.GetString(i);
row[i] = data == null ? "" : data;
}
if (row[0] != "")
{
result[result.Count - 1].Add(row);
}
}
} while (readExtraSheet && reader.NextResult());
return(result);
}
private static ExcelFileReader ReadDataFromFile(string path)
{
try {
using (Stream stream = File.Open(path, FileMode.Open, FileAccess.Read)) {
using (IExcelDataReader reader = ExcelReaderFactory.CreateReader(stream)) {
List <WorksheetReader> worksheets = new List <WorksheetReader>();
do
{
WorksheetReader ws = WorksheetReader.ParseFromFile(reader);
if (ws != null)
{
worksheets.Add(ws);
}
} while (reader.NextResult());
return(new ExcelFileReader(path, worksheets.ToArray()));
}
}
} catch (Exception e) {
Console.Error.WriteLine(e.Message);
Console.Error.WriteLine(e.StackTrace);
return(null);
}
}
public List <Product> GetListOfClothFromCSV_File(string pathToExcelFile, int charge = 0)
{
var clothesList = new List <Product>();
using (var stream = File.Open(pathToExcelFile, FileMode.Open, FileAccess.Read))
{
// Auto-detect format, supports:
// - Binary Excel files (2.0-2003 format; *.xls)
// - OpenXml Excel files (2007 format; *.xlsx)
using (IExcelDataReader reader = ExcelReaderFactory.CreateCsvReader(stream, new ExcelReaderConfiguration()
{
FallbackEncoding = Encoding.GetEncoding(1251)
}))
{
bool firstRow = true;
do
{
while (reader.Read())
{
if (firstRow)
{
firstRow = false;
continue;
}
Product elementOfCloth = GetModel(reader, charge);
clothesList.Add(elementOfCloth);
}
} while (reader.NextResult());
}
}
return(clothesList);
}
public bool ReadExcel(string path)
{
if (string.IsNullOrEmpty(path))
{
return(false);
}
try
{
using (FileStream s = new FileStream(path, FileMode.Open))
{
IExcelDataReader excelReader = null;
string extension = Path.GetExtension(path);
if (extension == ".xls")
{
excelReader = ExcelReaderFactory.CreateBinaryReader(s);
}
else if (extension == ".xlsx")
{
excelReader = ExcelReaderFactory.CreateOpenXmlReader(s);
}
if (excelReader != null)
{
using (excelReader)
{
datatable = excelReader.AsDataSet().Tables[0];
datatable.Rows.RemoveAt(0);
return(datatable != null);
}
}
}
}
catch (Exception ex)
{
}
return(false);
}
private void button4_Click(object sender, EventArgs e)
{
OpenFileDialog ope = new OpenFileDialog();
ope.Filter = "Excel Files | *.xls;*.xlsx;*.xlsm";
if (ope.ShowDialog() == DialogResult.OK)
{
FileStream stream = new FileStream(ope.FileName, FileMode.Open);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
DataSet result = excelReader.AsDataSet();
foreach (DataTable table in result.Tables)
{
foreach (DataRow dr in table.Rows)
{
new DataAccess.EtudiantDA().Create(
Convert.ToString(dr[0]),
Convert.ToString(dr[2]),
Convert.ToString(dr[7]),
Convert.ToString(dr[1]),
Convert.ToString(dr[4]),
Convert.ToString(dr[5]),
DateTime.Parse(dr[3].ToString()),
new DataAccess.GroupeDA().Get(Convert.ToString(dr[8])).Id,
Convert.ToString(dr[6]),
DateTime.Now.Year + " - " + (DateTime.Now.Year + 1)
);
}
excelReader.Close();
stream.Close();
MessageBox.Show("Les Etudiants Sont Ajoutès");
dataGridViewEtudiant.DataSource = new DataAccess.EtudiantDA().Get();
}
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
Categoryinfo caInfo = new Categoryinfo();
CategoryController caCon = new CategoryController();
string fn = @"E:\CategoryList.xlsx";
FileStream fs = File.Open(fn, FileMode.Open, FileAccess.Read);
IExcelDataReader ir = ExcelReaderFactory.CreateOpenXmlReader(fs);
DataSet ds = ir.AsDataSet();
foreach (DataTable tobj in ds.Tables)
{
foreach (DataRow robj in tobj.Rows)
{
caInfo.Type = Convert.ToString(robj[0]);
caInfo.Description = Convert.ToString(robj[1]);
caCon.InsertCategory(caInfo);
}
}
ir.Close();
fs.Close();
}
public static OneToManyMap <T, T1> Read(string excelfile, string sheet, string keyField, string [] skippedFields)
{
FileStream stream = File.Open(excelfile, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
excelReader.IsFirstRowAsColumnNames = true;
System.Data.DataSet result = excelReader.AsDataSet();
var dt = result.Tables[sheet];
var dtEnum = dt.AsEnumerable();
int num = dt.Rows.Count;
var varNames = new List <string>();
var varColIndex = new List <int>();
for (int c = 0; c < dt.Columns.Count; c++)
{
var coln = dt.Columns[c].ColumnName;
if (!skippedFields.Contains(coln) && coln != keyField)
{
varNames.Add(dt.Columns[c].ColumnName);
varColIndex.Add(c);
}
}
T[] ids = (from r in dtEnum select TypeConverterEx.ChangeType <T>(r.Field <string>(keyField))).ToArray();
T1[,] values = new T1[num, varNames.Count];
for (int i = 0; i < num; i++)
{
for (int c = 0; c < varNames.Count; c++)
{
values[i, c] = TypeConverterEx.ChangeType <T1>(dt.Rows[i][varColIndex[c]].ToString());
}
}
return(new OneToManyMap <T, T1>(ids, varNames.ToArray(), values));
}
private static List <PartNumberDetailedDao> GetPartNumberDetailsDao()
{
var excelfileExtension = Path.GetExtension(BOM_MASTER_FILE_LOCATION);
using (FileStream stream = File.Open(BOM_MASTER_FILE_LOCATION, FileMode.Open, FileAccess.Read))
{
using (IExcelDataReader excelReader = getExcelReader(excelfileExtension, stream))
{
//excelReader.isfiIsFirstRowAsColumnNames = true;
DataSet familyDs = excelReader.AsDataSet();
DataTable familyDT = familyDs.Tables[0];
List <PartNumberDetailedDao> partNumberDetailsDAO = new List <PartNumberDetailedDao>();
foreach (DataRow row in familyDT.Rows)
{
partNumberDetailsDAO.Add(MapFamilyPartNumbersDAO(row));
}
return(partNumberDetailsDAO);
}
}
}
public int ProcesarDocumento(String origen, int idCliente, int idDepartamento)
{
IExcelDataReader reader = null;
FileStream stream = File.Open(origen, FileMode.Open, FileAccess.Read);
if (System.IO.Path.GetExtension(origen).Equals(".xls"))
{
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
if (reader != null)
{
System.Data.DataSet result = reader.AsDataSet();
DataTable hoja = result.Tables[0];
var obra = IniciarObra(hoja, idCliente, idDepartamento);
AnalizarPrecios(hoja, obra, GetCeroYExcel(hoja));
return(1);
}
else
{
return(-1); // Error -1: No se encuentra el archivo.
}
}
public static DataTable ExcelToDataTable(String FileName, String SheetName)
{
using (System.IO.FileStream stream = File.Open(FileName, FileMode.Open, FileAccess.Read))
{
using (IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream))
{
DataSet result = excelReader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
}
);
//Get all the tables
DataTableCollection table = result.Tables;
//storing in a DataTable
DataTable resultTable = table[SheetName];
return(resultTable);
}
}
}
public void UseExcelDataReader()
{
string outputName = @"C:\Users\RomanBushuev\YandexDisk\MarketData\MOEX\raw\2018.03.12\rates.xlsx";
FileStream stream = File.Open(outputName, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
ExcelDataSetConfiguration configureation = new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true,
}
};
DataSet dataSet = excelReader.AsDataSet(configureation);
foreach (DataTable datatable in dataSet.Tables)
{
foreach (DataRow dataRow in datatable.Rows)
{
object var = dataRow["MATDATE"];
Console.WriteLine(dataRow["MATDATE"].GetType());
}
}
}
public bool TryGetDataFile(int contentLength, string fileName, Stream inputStream, out IEnumerable <DataRow> dataRows, bool isFirstRowAsColumNames = true)
{
dataRows = new List <DataRow>();
if (contentLength > 0)
{
string fileExtension = System.IO.Path.GetExtension(fileName);
if (".xls".Equals(fileExtension) || ".xlsx".Equals(fileExtension) || ".csv".Equals(fileExtension))
{
IExcelDataReader dataReader = null;
IEnumerable <string> sheets = ExcelExtension.GetWorksheetNames(inputStream, fileExtension, out dataReader, isFirstRowAsColumNames);
if (!sheets.Any())
{
return(false);
}
dataRows = ExcelExtension.GetData(sheets.First(), dataReader, fileExtension, isFirstRowAsColumNames, this.GetHeaders);
if (dataRows != null)
{
return(true);
}
}
}
return(false);
}
protected void Button1_Click(object sender, EventArgs e)
{
DataSet result = new DataSet();
FileUpload1.SaveAs(System.IO.Path.Combine(Server.MapPath("Data"), FileUpload1.FileName));
FileStream stream = File.Open(System.IO.Path.Combine(Server.MapPath("Data"), FileUpload1.FileName), FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
result = excelReader.AsDataSet();
excelReader.Close();
string a = "";
int row_no = 1;
// result.Tables[0].Columns.Add("SubBroker");
result.Tables[0].Columns.Add("Net Risk");
DataTable dtnew = new DataTable();
dtnew.Columns.Add("ClientCode");
dtnew.Columns.Add("ClientName");
dtnew.Columns.Add("Value");
dtnew.Columns.Add("DematCode");
for (int i = 1; i < result.Tables[0].Rows.Count; i++)
{
DataRow drw = dtnew.NewRow();
drw["ClientCode"] = result.Tables[0].Rows[i][3].ToString();
drw["ClientName"] = result.Tables[0].Rows[i][1].ToString();
drw["Value"] = result.Tables[0].Rows[i][2].ToString();
drw["DematCode"] = result.Tables[0].Rows[i][0].ToString();
dtnew.Rows.Add(drw);
}
GridView1.DataSource = dtnew;
GridView1.DataBind();
}
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 void loadDataFromExcel()
{
Debug.Log(getExcelPath());
FileStream stream = File.Open(getExcelPath(), FileMode.Open, FileAccess.Read);
//FileStream stream = File.Open("D:/3/1.xlsx", FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
DataSet result = excelReader.AsDataSet();
//Debug.Log(result == null);
int columns = result.Tables[0].Columns.Count;
int rows = result.Tables[0].Rows.Count;
excelData = new string[rows - 1, columns];
paramNames = new string[columns];
string s;
for (int i = 0; i < columns; i++)
{
paramNames[i] = result.Tables[0].Rows[0][i].ToString();
}
for (int i = 1; i < rows; i++)
{
s = "";
for (int j = 0; j < columns; j++)
{
excelData[i - 1, j] = result.Tables[0].Rows[i][j].ToString();
s += result.Tables[0].Rows[i][j].ToString() + " ";
}
//Debug.Log(s);
}
Debug.Log("导入数据完毕！！");
}
public ExcelReader(string excelPath)
{
_excelPath = excelPath;
if (!string.IsNullOrEmpty(_excelPath))
{
FileStream stream = File.Open(_excelPath, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
if (excelReader.ResultsCount > 0)
{
_excelData = excelReader.AsDataSet();
_dataTable = _excelData.Tables;
_rows = _dataTable[0].Rows;
_colus = _dataTable[0].Columns;
_rowCount = _rows.Count;
_coluCount = _colus.Count;
if (excelReader != null)
{
excelReader.Close();
}
if (stream != null)
{
stream.Close();
}
}
else
{
LogQueue.Add("表格读取错误...");
}
}
else
{
LogQueue.Add("表格存储路径错误...");
}
}
private static DataTable ExcelToDataTable(string strFileName, string strSheetName)
{
//Open excel file and return a stream
FileStream stream = File.Open(strFileName, FileMode.Open, FileAccess.Read);
//Create oepnxmlreader via ExcelReaderFactory
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
//Set the first row as column name
//excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
});
//Get All the tables
DataTableCollection tables = result.Tables;
//store it in Datatable
DataTable resultTable = tables[strSheetName];
//return Table
stream.Close();
return(resultTable);
}
public void Read(OnRowRead onRowRead)
{
using (FileStream stream = File.Open(_path, FileMode.Open, FileAccess.Read))
{
//Choose one of either 1 or 2
//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);
//IExcelDataReader excelReader = ExcelReaderFactory.CreateReader(stream);
//...
//3. DataSet - The result of each spreadsheet will be created in the result.Tables
while (excelReader.Read())
{
List <string> colList = new List <string>();
var colCount = excelReader.FieldCount;
for (int i = 0; i < colCount; i++)
{
if (!excelReader.IsDBNull(i))
{
colList.Add(excelReader[i].ToString());
}
else
{
colList.Add(Empty);
}
}
onRowRead(excelReader.Depth, colList);
}
excelReader.Close();
excelReader.Dispose();
stream.Close();
stream.Dispose();
}
}
public static IEnumerable ExcelFile(string path)
{
LinkedList <object[]> result = new LinkedList <object[]>();
using (FileStream file = new FileStream(path, FileMode.Open))
{
IExcelDataReader reader = ExcelReaderFactory.CreateReader(file);
do
{
int r = 0;
while (reader.Read())
{
for (int c = 0; c < reader.FieldCount; c++)
{
result.AddLast(new object[] { reader.Name, r, c, GetText(reader.GetValue(c)) });
}
r++;
}
} while(reader.NextResult());
reader.Close();
file.Close();
}
return(result);
}
protected override void Process()
{
var config = GetConfiguration();
if (config.AppSettings.Settings["VendorID"] == null)
{
throw new Exception("VendorID not set in config for Five4u ProductImport");
}
vendorID = int.Parse(config.AppSettings.Settings["VendorID"].Value.ToString());
try
{
using (var unit = GetUnitOfWork())
{
var productXlsFilePath = config.AppSettings.Settings["Five4uBasePath"].Value;
DataSet[] datas = new DataSet[0];
using (FileStream stream1 = File.Open(productXlsFilePath, FileMode.Open, FileAccess.Read))
{
//1. Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader1 = ExcelReaderFactory.CreateOpenXmlReader(stream1);
excelReader1.IsFirstRowAsColumnNames = true;
//...
//2. DataSet - The result of each spreadsheet will be created in the result.Tables
DataSet result1 = excelReader1.AsDataSet();
ParseDocuments(unit, result1);
}
}
}
catch (Exception ex)
{
log.AuditError("Error getting Five4u files from Excel", ex);
}
}
private static DataTable ExcelToDataTable(string filename)
{
FileStream stream = File.Open(filename, FileMode.Open, FileAccess.Read);
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
//ExcelReaderFactory.CreateBinaryReader(stream);
DataSet resultset = excelReader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
});
DataTableCollection table = resultset.Tables;
DataTable resultTable = table["Credentials"];
return(resultTable);
}
/// <summary>
/// 根据命令行参数，执行Excel数据导出工作
/// </summary>
/// <param name="options">命令行参数</param>
private static void Run(string excelPath)
{
string file = Path.GetFileNameWithoutExtension(excelPath);
int header = 1;
// 加载Excel文件
using (FileStream excelFile = File.Open(excelPath, FileMode.Open, FileAccess.Read))
{
// Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(excelFile);
// The result of each spreadsheet will be created in the result.Tables
excelReader.IsFirstRowAsColumnNames = true;
DataSet book = excelReader.AsDataSet();
// 数据检测
if (book.Tables.Count < 1)
{
throw new Exception("Excel文件中没有找到Sheet: " + excelPath);
}
// 取得数据
DataTable sheet = book.Tables[0];
if (sheet.Rows.Count <= 0)
{
throw new Exception("Excel Sheet中没有数据: " + excelPath);
}
//-- UTF8编码
Encoding cd = new UTF8Encoding(false);
//-- 导出JSON文件
JsonExporter exporter = new JsonExporter(sheet, header, false);
exporter.SaveToFile("json/" + file + ".json", cd);
}
}
//Excel Select button click event
private void Excel_btn_Click(object sender, EventArgs e)
{
try
{
using (OpenFileDialog openFile = new OpenFileDialog()
{
Filter = "Excel Files|*.xls|*xlsx|*.xlsm"
}) //check if | is needed last?
{
if (openFile.ShowDialog() == DialogResult.OK)
{
Filepath_textbox.Text = openFile.FileName; //Filepath_textbox.Text --- filepath is displayed in textbox
using (var stream = File.Open(openFile.FileName, FileMode.Open, FileAccess.Read))
{
using (IExcelDataReader reader = ExcelReaderFactory.CreateReader(stream))
{
DataSet result = reader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
});
tableCollection = result.Tables;
Sheet_combobox.Items.Clear();
foreach (DataTable table in tableCollection)
{
Sheet_combobox.Items.Add(table.TableName); //add sheet to combobox
}
}
}
}
}
}
catch (Exception) { MessageBox.Show("Try Again", "Excel_btn_Click", MessageBoxButtons.OK, MessageBoxIcon.Error); }
}
// etc.............
/// <summary>
/// 读取excel文件内容
/// </summary>
/// <param name="filePath">文件路径</param>
/// <param name="tableName">表的名字</param>
/// <param name="columnNum">行数</param>
/// <param name="rowNum">列数</param>
/// <returns></returns>
static DataRowCollection ReadExcel(string filePath, string tableName, ref int columnNum, ref int rowNum)
{
FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read, FileShare.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
DataSet result = excelReader.AsDataSet();
//Tables[0] 下标0表示excel文件中第一张表的数据
columnNum = result.Tables[tableName].Columns.Count;
rowNum = result.Tables[tableName].Rows.Count;
//判断有效行数
DataRowCollection tempData = result.Tables[tableName].Rows;
for (int i = StartRow; i < rowNum; i++)
{
if (tempData[i][0].ToString() == "")
{
rowNum = i;
break;
}
}
return(result.Tables[tableName].Rows);
}
private static DataTable ExcelToDataTable(string fileName, string SheetName)
{
// Open file and return as Stream
using (System.IO.FileStream stream = File.Open(fileName, FileMode.Open, FileAccess.Read))
{
using (IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream))
{
excelReader.IsFirstRowAsColumnNames = true;
//Return as dataset
DataSet result = excelReader.AsDataSet();
//Get all the tables
DataTableCollection table = result.Tables;
// store it in data table
DataTable resultTable = table[SheetName];
//excelReader.Dispose();
//excelReader.Close();
// return
return(resultTable);
}
}
}
public void GenerateBlocksFromExcel(IExcelDataReader excelReader, string sheetName)
{
// Convert ExcelReader to DataSet
DataSet result = excelReader.AsDataSet();
DataTable table = result.Tables[sheetName];
// Loop through all Rows
for (int y = 0; y < table.Rows.Count; y++)
{
// Get Row at X index
DataRow row = table.Rows[y];
// Loop through all Items in Row
for (int x = 0; x < row.ItemArray.Length; x++)
{
string value = row.ItemArray.GetValue(x).ToString();
if (value != "")
{
string blockType = value;
// Create Block with Type & Position
CreateBlock(blockType, new Vector2(x * spacing.x, (y * spacing.y) * -1));
}
}
}
}
/// <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;
}
}
/*
static void ReadExcelFiles (DirectoryInfo dir)
{
foreach (var f in dir.GetFiles ("*.xlsx")) {
var nombre = f.Name.ToLowerInvariant ();
if ((nombre.Contains("riesgo") || nombre.Contains("problema")) && !nombre.Contains ("$") ) {
Console.WriteLine ("Leyendo: " + f.FullName);
using (var stream = f.OpenRead ()) {
var reader = ExcelReaderFactory.CreateOpenXmlReader (stream);
Dictionary<String, List<List<String>>> dic;
if (f.Name.ToLowerInvariant ().Contains ("riesgo")) {
dic = _riesgos;
} else {
dic = _problemas;
}
ProcessTable (f.FullName, reader, dic);
}
}
}
}
*/
static string GetResultTableName (IExcelDataReader reader, int i)
{
var fields = reader.GetType ().GetRuntimeFields ();
var workbookField = fields.FirstOrDefault(f => f.Name.ToLowerInvariant().Contains("workbook"));
var workBook = workbookField.GetValue (reader);
var workbookType = workBook.GetType ();
var sheets = (IList)workbookType.GetProperty ("Sheets").GetValue (workBook);
var sheet = sheets [i];
string name = (String)sheet.GetType ().GetProperty ("Name").GetValue (sheet);
//return "";
return name;
}
public uc_autoprocess(
System.Int32 aRows,
System.Int32 aColumns,
TermWindow p1
)
{
this.rRows = aRows;
this.rColumns = aColumns;
this.Parent = p1;
mySB = new System.Text.StringBuilder(this.rRows * this.rColumns);
this.SetSequence();
myMapTxtCaret = new uc_maptxtcaret(this);
stream = File.Open(@"c:\temp\temp.xlsx", FileMode.Open, FileAccess.Read);
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
excelReader.IsFirstRowAsColumnNames = true;
output = excelReader.AsDataSet();
ddtt = output.Tables[0];
numCount = ddtt.Rows.Count;
shouldIncExcelPatientCntr = false;
}
private DataSet ConvertExcelToDataSet(IExcelDataReader parsedExcel)
{
DataSet result = parsedExcel.AsDataSet();
return result;
}
public ExcelReader(string path)
{
_path = path;
reader = getExcelReader();
}
private static List<Schema> ReadSchemas(IExcelDataReader excelReader)
{
var schemas = new List<Schema>();
var excelDs = excelReader.AsDataSet();
var tocDt = excelDs.Tables["TOC"];
var tableNames = tocDt.ValueOf<string>(0);
foreach (var tableName in tableNames)
{
var schemaDt = excelDs.Tables[tableName + "_schema"];
if (schemaDt == null)
{
continue;
}
var schema = new Schema(tableName, schemaDt);
var dt = excelDs.Tables[schema.TableName + "_data"];
schema.DataTable = dt;
schemas.Add(schema);
}
return schemas;
}
private Hash DoCompilerExcelReader(string path, IExcelDataReader excelReader, string compileToFilePath = null)
{
//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();
if (result.Tables.Count <= 0)
throw new InvalidExcelException("No Sheet!");
var renderVars = new RenderTemplateVars();
renderVars.FieldsInternal = new List<RenderFieldVars>();
var sheet1 = result.Tables[0];
var strBuilder = new StringBuilder();
var ignoreColumns = new HashSet<int>();
var ignoreRows = new HashSet<int>();
// 寻找注释行，1,或2行
var hasStatementRow = false;
var statementRow = sheet1.Rows[0].ItemArray;
var regExCheckStatement = new Regex(@"\[(.*)\]");
foreach (var cellVal in statementRow)
{
if ((cellVal is string))
{
var matches = regExCheckStatement.Matches(cellVal.ToString());
if (matches.Count > 0)
{
hasStatementRow = true;
}
}
break;
}
// 获取注释行
var commentRow = hasStatementRow ? sheet1.Rows[1].ItemArray : sheet1.Rows[0].ItemArray;
var commentsOfColumns = new List<string>();
foreach (var cellVal in commentRow)
{
commentsOfColumns.Add(cellVal.ToString());
}
// Header
int colIndex = 0;
foreach (DataColumn column in sheet1.Columns)
{
var colNameStr = column.ColumnName.Trim();
if (!string.IsNullOrEmpty(colNameStr))
{
var isCommentColumn = false;
foreach (var commentStartsWith in _config.CommentColumnStartsWith)
{
if (colNameStr.StartsWith(commentStartsWith))
{
isCommentColumn = true;
break;
}
}
if (isCommentColumn)
{
ignoreColumns.Add(colIndex);
}
else
{
if (colIndex > 0)
strBuilder.Append("\t");
strBuilder.Append(colNameStr);
string typeName = "string";
string defaultVal = "";
if (hasStatementRow)
{
var match = regExCheckStatement.Match(statementRow[colIndex].ToString());
var attrs = match.Groups[1].ToString().Split(':');
// Type
if (attrs.Length > 0)
{
typeName = attrs[0];
}
// Default Value
if (attrs.Length > 1)
{
defaultVal = attrs[1];
}
if (attrs.Length > 2)
{
if (attrs[2] == "pk")
{
renderVars.PrimaryKey = colNameStr;
}
}
}
renderVars.FieldsInternal.Add(new RenderFieldVars
{
Index = colIndex,
Type = typeName,
Name = colNameStr,
DefaultValue = defaultVal,
Comment = commentsOfColumns[colIndex],
});
//codeGentor.Columns2DefaultValus.Add(colNameStr, defaultVal);
}
}
colIndex++;
}
strBuilder.Append("\n");
// Rows
var rowIndex = 1;
foreach (DataRow dRow in sheet1.Rows)
{
if (hasStatementRow)
{
// 有声明行，忽略第2行
if (rowIndex == 2)
{
rowIndex++;
continue;
}
}
else
{
// 无声明行，忽略第1行
if (rowIndex == 1)
{
rowIndex++;
continue;
}
}
colIndex = 0;
foreach (var item in dRow.ItemArray)
{
if (ignoreColumns.Contains(colIndex)) // comment column, ignore
continue;
if (colIndex > 0)
strBuilder.Append("\t");
var cloneItem = item;
// 如果单元格是字符串，换行符改成\\n
if (item is string)
{
var sItme = item as string;
cloneItem = sItme.Replace("\n", "\\n");
}
strBuilder.Append(cloneItem);
colIndex++;
}
strBuilder.Append("\n");
rowIndex++;
}
var fileName = Path.GetFileNameWithoutExtension(path);
string exportPath;
if (!string.IsNullOrEmpty(compileToFilePath))
{
exportPath = compileToFilePath;
}
else
{
// use default
exportPath = string.Format("{0}{1}", fileName, _config.ExportTabExt);
}
File.WriteAllText(exportPath, strBuilder.ToString());
renderVars.ClassName = string.Join("", (from name in fileName.Split('_') select System.Threading.Thread.CurrentThread.CurrentCulture.TextInfo.ToTitleCase(name)).ToArray());
renderVars.TabFilePath = exportPath;
return Hash.FromAnonymousObject(renderVars);
}
/// <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();
}
protected override void BeginProcessing()
{
WriteVerbose(string.Format(
CultureInfo.CurrentCulture,
"Opening file {0} for reading as an Excel open-xml formatted spreadsheet",
this.roleAssignmentFile
)
);
this.excelDataReader = ExcelReaderFactory.CreateOpenXmlReader(
File.Open(this.roleAssignmentFile, FileMode.Open, FileAccess.Read)
);
if (!this.excelDataReader.IsValid)
{
throw new ArgumentException(
string.Format(
CultureInfo.CurrentCulture,
"The specified file {0} is not a valid open-xml formatted Excel file.{1}Error details:{1}{2}",
this.roleAssignmentFile,
Environment.NewLine,
this.excelDataReader.ExceptionMessage
)
);
}
var dataSet = this.excelDataReader.AsDataSet();
var sheet = dataSet.Tables[this.sheetName];
if (sheet == null)
{
throw new ArgumentException(
string.Format(
CultureInfo.CurrentCulture,
"The Excel data file {0} does not contain the specified sheet '{1}'",
this.roleAssignmentFile,
this.sheetName
)
);
}
this.dataReader = dataSet.CreateDataReader(sheet);
if (this.dataReader == null)
{
throw new InvalidOperationException(
string.Format(
CultureInfo.CurrentCulture,
"The specified sheet name '{0}' in the Excel data file '{1}' cannot be opened for reading",
this.sheetName,
this.roleAssignmentFile
)
);
}
using (var permissionCellsReader = dataSet.CreateDataReader(sheet))
{
int rowIndex = -1;
while (permissionCellsReader.Read())
{
++rowIndex;
var permissionUri = permissionCellsReader.GetValue(this.permissionIdsStartCell.ColIndex) as string;
var permissionShortName = permissionCellsReader.GetValue(this.permissionsShortNameColumnIndex) as string;
var permissionTitle = permissionCellsReader.GetValue(this.permissionsTitleColumnIndex) as string;
if (string.IsNullOrWhiteSpace(permissionTitle))
{
permissionTitle = permissionUri;
}
if (string.IsNullOrWhiteSpace(permissionUri)
|| !permissionUri.StartsWith(this.permissionsUriPrefix))
{
continue;
}
if (string.IsNullOrEmpty(permissionShortName))
{
permissionShortName = permissionTitle;
}
if(string.IsNullOrEmpty(permissionShortName))
{
throw new InvalidOperationException(string.Format("Missing permission short name in row {0} (URI '{1}', title '{2}')",
rowIndex + 1,
permissionUri,
permissionTitle
));
}
permissionShortName = permissionShortName
.Replace("æ", "ae")
.Replace("ø", "oe")
.Replace("å", "aa")
.Replace("Æ", "Ae")
.Replace("Ø", "Oe")
.Replace("Å", "Aa")
;
permissionShortName = permissionShortName.Split(" \t\r\n".ToArray(), StringSplitOptions.RemoveEmptyEntries)
.Select(c => c.Substring(0, 1).ToUpper() + c.Substring(1))
.Aggregate(string.Empty, (s, acc) => s + acc);
this.permissionCells.Add(
new Permission(
new GridCoordinate(rowIndex, this.permissionIdsStartCell.ColIndex),
new Uri(permissionUri, UriKind.Absolute),
permissionShortName,
permissionTitle
)
);
}
}
var disctinctPermissions = this.permissionCells.Select(rc => rc.ShortName).Distinct().ToList();
if (disctinctPermissions.Count != this.permissionCells.Count)
{
var duplicates = this.permissionCells
.GroupBy(rc => rc.ShortName)
.Where(g => g.Count() > 1);
var message = duplicates.Aggregate(
"",
(acc, dupe) => string.Format(CultureInfo.CurrentCulture,
"{0}'{1}' (row numbers {2}){3}",
acc,
dupe.Key,
string.Join(",", dupe.Select(d => (d.UriCoordinate.RowIndex + 1).ToString())),
Environment.NewLine
));
throw new InvalidDataException(string.Format(CultureInfo.CurrentCulture,
"Duplicate permissions found. Terminating:{0}{1}",
Environment.NewLine,
message
));
}
var colIndex = this.RoleValuesStartCell.ColIndex - 1;
this.roleCells = sheet
.Rows[this.RoleValuesStartCell.RowIndex]
.ItemArray
.Skip(this.RoleValuesStartCell.ColIndex)
.Where(val =>
{
++colIndex;
var str = val as string;
return !string.IsNullOrWhiteSpace(str);
})
.Select(val => new RoleCell(
new GridCoordinate(this.roleValuesStartCell.RowIndex, colIndex),
(string)val
)
)
.ToList();
var disctinctRoleNames = this.roleCells.Select(rc => rc.Role).Distinct().ToList();
if (disctinctRoleNames.Count != this.roleCells.Count)
{
var duplicates = this.roleCells
.GroupBy(rc => rc.Role)
.Where(g => g.Count() > 1);
var message = duplicates.Aggregate(
"",
(acc, dupe) => string.Format(CultureInfo.CurrentCulture,
"{0}'{1}' (column numbers {2}){3}",
acc,
dupe.Key,
string.Join(",", dupe.Select(d => (d.Coordinate.ColIndex + 1).ToString())),
Environment.NewLine
));
throw new InvalidDataException(string.Format(CultureInfo.CurrentCulture,
"Duplicate role names found. Terminating:{0}{1}",
Environment.NewLine,
message
));
}
}
// Parse results
public static List<string> ParseResults(IExcelDataReader excelReader)
{
var list = new List<string>();
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();
if (result.Tables.Count > 0)
{
var dt = result.Tables[0];
foreach (DataRow r in dt.Rows)
{
list.Add(r[0].ToString());
}
}
// Return list
return list;
}
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();
}
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;
}
