private void Dispose(bool disposing)
{
if (!isDisposed && disposing && initialized)
{
try {
if (dataSet != null)
{
dataSet.Dispose();
}
} finally {
try {
if (excelDataReader != null)
{
excelDataReader.Dispose();
}
} finally {
if (internalStream == excelStream)
{
internalStream = null;
}
if (isStreamOwner && excelStream != null)
{
excelStream.Dispose();
}
if (internalStream != null)
{
internalStream.Dispose();
}
}
}
}
isDisposed = true;
}
public List <Person> GetAll()
{
FileStream stream = new FileStream(@"C:\Users\d.ivanisevic\Documents\Visual Studio 2015\Projects\Learning.Excel\MOCK_DATA.xlsx", FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();
List <Person> persons = new List <Person>();
DataTable table = new DataTable();
table = result.Tables[0];
excelReader.Close();
excelReader.Dispose();
stream.Close(); stream.Dispose();
foreach (DataRow row in table.Rows)
{
Person person = new Person()
{
ID = (double)row.ItemArray[0],
FirstName = (string)row.ItemArray[1],
LastName = (string)row.ItemArray[2],
Email = row.ItemArray[3].ToString(),
Gender = (string)row.ItemArray[4]
};
persons.Add(person);
}
return(persons);
}
public static DataTable ExcelToDataTable(HttpPostedFileBase fileBase, bool firstRowAsColumnNames = true)
{
//Valid
if (!fileBase.FileName.EndsWith(".xls") && !fileBase.FileName.EndsWith(".xlsx"))
{
return(null);
}
Stream stream = fileBase.InputStream;
IExcelDataReader reader = null;
if (fileBase.FileName.EndsWith(".xls"))
{
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else
{
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
reader.IsFirstRowAsColumnNames = firstRowAsColumnNames;
var result = reader.AsDataSet();
reader.Close();
reader.Dispose();
reader = null;
return(result.Tables[0]);
}
public static IEnumerable <busStop> Create(string fileName)
{
List <StringBuilder> rows = new List <StringBuilder>();
FileStream stream = File.Open(fileName, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
DataSet result = excelReader.AsDataSet();
DataTable table = result.Tables[0];
for (int i = dataStart; i < table.Rows.Count; i++)
{
if (table.Rows[i][0].ToString().StartsWith("№") == true)
{
StringBuilder item = new StringBuilder();
for (int j = 0; j < table.Columns.Count; j++)
{
if (table.Rows[i][j].ToString() != null && table.Rows[i][0].ToString().StartsWith("№"))
{
item.Append(table.Rows[i][j].ToString() + "|");
}
}
rows.Add(item);
}
}
IEnumerable <busStop> stops = Parse(rows);
stream.Close();
excelReader.Dispose();
return(stops);
}
/// <summary>
/// Parse xls file as dataset
/// </summary>
/// <param name="xlsStream">Stream with xls file to parse</param>
/// <returns>DataSet</returns>
public DataSet ToDataSet(bool is2007Format, Stream xlsStream, bool asDataSet)
{
IExcelDataReader excelReader = null;
DataSet resultDataSet = null;
try
{
if (is2007Format)
{
excelReader = ExcelReaderFactory.CreateOpenXmlReader(xlsStream);
}
else
{
excelReader = ExcelReaderFactory.CreateBinaryReader(xlsStream);
}
if (asDataSet)
{
//excelReader.IsFirstRowAsColumnNames = true;
resultDataSet = excelReader.AsDataSet(asDataSet);
}
else
{
resultDataSet = excelReader.AsDataSet();
}
}
finally
{
if (excelReader != null)
{
excelReader.Dispose();
}
}
return(resultDataSet);
}
public static DataTable ExcelToDataTable(Stream stream, string fileName, bool firstRowAsColumnNames)
{
DataTable dtExcel = new DataTable();
int pos = fileName.ToLower().LastIndexOf(".");
string strconn = string.Empty;
if (fileName.Substring(pos + 1) == "xls" || fileName.Substring(pos + 1) == "xlsx")
{
IExcelDataReader excelReader = null;
if (fileName.Substring(pos + 1) == "xls")
{
//1. Reading from a binary Excel file ('97-2003 format; *.xls)
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (fileName.Substring(pos + 1) == "xlsx")
{
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
excelReader.IsFirstRowAsColumnNames = firstRowAsColumnNames;
//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.Dispose();
dtExcel = result.Tables[0];
//Data Reader methods
}
return dtExcel;
}
private static List <RegionDeal> GetRegionDeals(string filePath)
{
FileStream stream = System.IO.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;
// var dataSet = excelReader.AsDataSet();
//5. Data Reader methods
var deals = new List <RegionDeal>();
var isFirstRow = true;
while (excelReader.Read())
{
if (isFirstRow)
{
isFirstRow = false;
continue;
}
var value = excelReader.GetDecimal(7);
if (value < 0)
{
value = 0;
}
deals.Add(new RegionDeal {
Region = excelReader.GetString(6), Value = value
});
}
//6. Free resources (IExcelDataReader is IDisposable)
excelReader.Close();
excelReader.Dispose();
// var deals = new List<RegionDeal>();
// foreach (DataRow row in dataSet.Tables[0].Rows)
// {
// var value = row[7] is DBNull ? 0 : Convert.ToDecimal(row[7]);
// deals.Add(new RegionDeal {Region = row[6].ToString(), Value = value});
// }
var sums = deals.GroupBy(d => d.Region)
.Select(x => new RegionDeal {
Value = x.Sum(d => d.Value), Region = x.First().Region
})
.OrderBy(x => x.Region)
.ToList();
return(sums);
}
private static async Task <DataTable> ExcelToDataTable(string fileName, string SheetName)
{
using (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);
}
}
// Open file and return as Stream
}
public static DataTable ExcelToDataTable(string fileName, int number)
{
//open file and returns as Stream
FileStream stream = File.Open(fileName, FileMode.Open, FileAccess.Read);
//Createopenxmlreader via ExcelReaderFactory
IExcelDataReader ExcelDataReader = ExcelReaderFactory.CreateOpenXmlReader(stream); //.xlsx
//Return as DataSet
DataSet result = ExcelDataReader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
});
//Get all the Tables
DataTableCollection table = result.Tables;
//Store it in DataTable
DataTable resultTable = table["Sheet" + number];
stream.Close();
stream.Dispose();
if (ExcelDataReader != null)
{
ExcelDataReader.Close();
ExcelDataReader.Dispose();
}
//return
return(resultTable);
}
/// <summary>
/// Gets a list of worksheets within the Excel workbook.
/// </summary>
/// <returns>A list of sheets name.</returns>
public List <string> GetSheets()
{
List <string> sheetNames = new List <string>();
try
{
FileInfo excelFile = new FileInfo(Path);
IExcelDataReader excelReader = GetExcelDataReader(excelFile);
while (excelReader.Read())
{
if (!sheetNames.Contains(excelReader.Name))
{
sheetNames.Add(excelReader.Name);
}
excelReader.NextResult();
}
excelReader.Dispose();
}
catch
{ }
return(sheetNames);
}
public static DataSet readExcelFile(string fileName)
{
DataSet result = null;
using (FileStream stream = File.Open(fileName, FileMode.Open, FileAccess.Read))
{
//Choose one of either 1 or 2
IExcelDataReader excelReader = null;
if (fileName.ToLower().EndsWith(".xls"))
{
//1. Reading from a binary Excel file ('97-2003 format; *.xls)
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else
{
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
if (excelReader != null)
{
//Choose one of either 3, 4, or 5
//3. DataSet - The result of each spreadsheet will be created in the result.Tables
result = excelReader.AsDataSet();
}
//6. Free resources (IExcelDataReader is IDisposable)
excelReader.Close();
excelReader.Dispose();
}
return(result);
}
public Boolean DuplicateColumnNames()
{
using (ReadAndStreamFile())
{
reader.Read();
{
var ColumnsNames = Enumerable.Range(0, reader.FieldCount).Select(i => reader.GetValue(i)).ToList();
var excel = new ExcelDataReaderFile();
errorDetected = false;
//looping through column names
for (int columnIndexNumber = 0; columnIndexNumber < ColumnsNames.Count; columnIndexNumber++)
{
//looping through rows
for (int columnIndexNum = 0; columnIndexNum < ColumnsNames.Count; columnIndexNum++)
{
var cellAddressA = excel.GetAddress(columnIndexNumber, 0);
var cellAddressB = excel.GetAddress(columnIndexNum, 0);
//so lets say we are talking about column A and Column C, if ColumnA and Column C are not null
if (ColumnsNames[columnIndexNumber] != null && ColumnsNames[columnIndexNum] != null)
{
//so if column A were never put against column C to check if they match
if (ColumnsNames[columnIndexNumber] != ColumnAlreadyMatchedA && ColumnsNames[columnIndexNum] != ColumnAlreadyMatchedB)
{
//check every column against eachother apart from checking it with itself ( say if columnIndexNumber and columnIndexNum are the same then it emans we are trying to match it with itself)
// any column say ColumnsNames[columnIndexNumber] is column A already matched with B, C and D so going backwards doesnt check column B,C,D against column A
if (ColumnsNames[columnIndexNumber] == ColumnsNames[columnIndexNum] && columnIndexNumber != columnIndexNum)
{
// and so Column a is given "ColumnAlreadyMatchedA"name
ColumnAlreadyMatchedA = ColumnsNames[columnIndexNumber];
ColumnAlreadyMatchedB = ColumnsNames[columnIndexNum];
Logger.Error($"[{GetFileName(file)}]{reader.Name}!{cellAddressA} with column name {ColumnsNames[columnIndexNumber]} matches {cellAddressB} with column name {ColumnsNames[columnIndexNum]}");
errorDetected = true;
}
else
{
// errorDetected = false;
}
}
}
}
}
}
reader.Dispose();
reader.Close();
return(errorDetected);
}
}
public void Dispose()
{
stream?.Dispose();
reader?.Dispose();
if (File.Exists(tempLocalFilePath))
{
File.Delete(tempLocalFilePath);
}
}
public void Dispose()
{
Reader.Dispose(PolicyName.Disposable);
TempFileStream.Dispose(PolicyName.Disposable);
if ((!string.IsNullOrEmpty(TempFileName)) && (File.Exists(TempFileName)))
{
File.Delete(TempFileName);
}
}
public Tuple <List <ValidationEmpleado>, DataTable> ExcelToDataTable(HttpPostedFileBase fileBase)
{
var datosValidacion = new Empleados().GetDatosValidacion();
List <ValidationEmpleado> ListaEmpledosValidacion = new List <ValidationEmpleado>();
#region File to Datatable
Stream stream = fileBase.InputStream;
IExcelDataReader reader = null;
if (fileBase.FileName.EndsWith(".xls"))
{
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else
{
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
reader.IsFirstRowAsColumnNames = true;
var result = reader.AsDataSet();
reader.Close();
reader.Dispose();
reader = null;
var dataTable = result.Tables[0];
#endregion
//RECORRE CADA LINEA DEL EXCEL
foreach (DataRow row in dataTable.Rows)
{
//si cada row del datatable tiene los 3 primeros datos
if (row[0] == DBNull.Value && row[1] == DBNull.Value && row[2] == DBNull.Value)
{
row.Delete();
}
else
{
//valida la infroamcion de cada row
var DataValidacion = ValidateRow(row, datosValidacion);
if (DataValidacion != null)
{
//sidevuelve algun error lo guarda en la lista
ListaEmpledosValidacion.Add(
new ValidationEmpleado()
{
Nombre = row[0].ToString() + " " + row[1].ToString() + " " + row[2].ToString(),
NumWar = DataValidacion.Where(x => x.Type == true).Count(),
NumError = DataValidacion.Where(x => x.Type == false).Count(),
data = DataValidacion
}
);
}
}
}
dataTable.AcceptChanges();
//ListaEmpledosValidacion = ListaEmpledosValidacion.Count == 0 ? null : ListaEmpledosValidacion;
return(Tuple.Create(ListaEmpledosValidacion, dataTable));
}
public void Dispose()
{
// Do not change this code. Put cleanup code in 'Dispose(bool disposing)' method
Dispose(true);
excelDataReader.Close();
excelDataReader.Dispose();
dataSetResult.Dispose();
// TODO: uncomment the following line if the finalizer is overridden above.
GC.SuppressFinalize(this);
}
public void Dispose()
{
_excelDataReader?.Dispose();
_excelDataReader = null;
_fileStream?.Dispose();
_fileStream = null;
_data?.Dispose();
_data = null;
}
protected virtual void Dispose(bool disposing)
{
if (disposing)
{
if (excelReader != null)
{
excelReader.Dispose();
excelReader = null;
}
}
}
public Workbook(string filePath)
{
try {
_stream = File.OpenRead(filePath);
ExcelReaderConfiguration config = new();
_reader = ExcelReaderFactory.CreateReader(_stream, config);
} catch {
_stream?.Dispose();
_reader?.Dispose();
throw;
}
}
/*
* /// <summary>
* /// This method is use for
* /// return data in exel file
* /// </summary> ///
* /// <returns></returns>
* public DataTable GetExcelData(string path, string sheet, bool hasHeader = true)
* {
* DataTable _returnDataTable;
* int _totalCols = 0;
* int _totalRows = 0;
* int _startRow = hasHeader ? 2 : 1;
* ExcelRange _workSheetRow = null;
* DataRow _dataRow = null;
* try
* {
* using (ExcelPackage package = new ExcelPackage())
* {
* // Open the Excel file and load it to the ExcelPackage
* using (var stream = File.OpenRead(path))
* {
* package.Load(stream);
* }
* ExcelWorksheet worksheet = package.Workbook.Worksheets[sheet];
* _returnDataTable = new DataTable(worksheet.Name);
* _totalCols = worksheet.Dimension.End.Column;
* _totalRows = worksheet.Dimension.End.Row;
* foreach (var firstRowCell in worksheet.Cells[1, 1, 1, _totalCols])
* {
* _returnDataTable.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
* }
* for (int rowNum = _startRow; rowNum <= _totalRows; rowNum++)
* {
* _workSheetRow = worksheet.Cells[rowNum, 1, rowNum, _totalCols];
* _dataRow = _returnDataTable.NewRow();
* foreach (var cell in _workSheetRow)
* {
* _dataRow[cell.Start.Column - 1] = cell.Text;
* }
* _returnDataTable.Rows.Add(_dataRow);
* }
* }
* _workSheetRow.Dispose();
* return _returnDataTable;
* }
* catch (FileNotFoundException)
* {
* throw new StepErrorException("Cannot found excel file in '" + path + "'");
* }
* catch (IOException)
* {
* throw new StepErrorException("Cannot access excel file in '" + path + "'");
* }
* //Still define Data Type
* }
*/
/// <summary>
/// This method is use for
/// return data in exel file
/// use Lib ExcelReader
/// </summary> ///
/// <returns></returns>
public DataTable GetExcelData(string path, string sheet, bool hasHeader = true)
{
IExcelDataReader _read = null;
try
{
DataTable _returnDataTable = null;
FileStream _stream = File.Open(path, FileMode.Open, FileAccess.Read);
if (path.Split('.')[1].ToString().ToLower().Equals("xlsx"))
{
_read = ExcelReaderFactory.CreateOpenXmlReader(_stream);
}
else if (path.Split('.')[1].ToString().ToLower().Equals("xls"))
{
_read = ExcelReaderFactory.CreateBinaryReader(_stream);
}
else
{
throw new StepErrorException("Excel file not correct in file name extensions");
}
_read.IsFirstRowAsColumnNames = hasHeader;
DataSet result = _read.AsDataSet();
_read.Close();
_read.Dispose();
_stream.Close();
_stream.Dispose();
for (int n = 0; n < result.Tables.Count; n++)
{
if (result.Tables[n].TableName.Equals(sheet))
{
_returnDataTable = result.Tables[n];
break;
}
}
if (_returnDataTable == null)
{
throw new NullReferenceException();
}
return(_returnDataTable);
}
catch (FileNotFoundException)
{
throw new StepErrorException("Cannot found excel file in '" + path + "'");
}
catch (IOException)
{
throw new StepErrorException("Cannot access excel file in '" + path + "'");
}
catch (NullReferenceException)
{
throw new StepErrorException("Cannot find Sheet Name '" + sheet + "'");
}
}
public void StartReadData(string path)
{
FileStream excelStream = File.Open(path, FileMode.Open, FileAccess.Read);
reader = ExcelReaderFactory.CreateReader(excelStream, new ExcelReaderConfiguration {
FallbackEncoding = Encoding.GetEncoding(1252)
});
handleTempleInterface.Handler(reader.AsDataSet(new ExcelDataSetConfiguration()
{
// Gets or sets a value indicating whether to set the DataColumn.DataType
// property in a second pass.
UseColumnDataType = true,
// Gets or sets a callback to obtain configuration options for a DataTable.
ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
{
// Gets or sets a value indicating the prefix of generated column names.
EmptyColumnNamePrefix = "Column",
// Gets or sets a value indicating whether to use a row from the
// data as column names.
UseHeaderRow = false,
// Gets or sets a callback to determine which row is the header row.
// Only called when UseHeaderRow = true.
ReadHeaderRow = (rowReader) =>
{
// F.ex skip the first row and use the 2nd row as column headers:
rowReader.Read();
},
// Gets or sets a callback to determine whether to include the
// current row in the DataTable.
FilterRow = (rowReader) =>
{
return(true);
},
// Gets or sets a callback to determine whether to include the specific
// column in the DataTable. Called once per column after reading the
// headers.
FilterColumn = (rowReader, columnIndex) =>
{
return(true);
}
}
}));
reader.Dispose();
excelStream.Close();
}
public static List <ExcelStateObj> ReadExcel(string path)
{
MemoryStream memory = new MemoryStream(LoadFilebytes(path));
List <ExcelStateObj> list = new List <ExcelStateObj>();
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(memory);
do
{
// sheet name
//Debug.Log(excelReader.Name);
List <RowStateObj> rowList = new List <RowStateObj>();
int rowCount = 0;
int colCount = 0;
while (excelReader.Read())
{
bool isAdd = false;
RowStateObj row = new RowStateObj(excelReader.FieldCount);
colCount = Mathf.Max(excelReader.FieldCount, colCount);
for (int i = 0; i < colCount; i++)
{
CellStateObj cell = new CellStateObj();
row.Cells[i] = cell;
if (i < excelReader.FieldCount)
{
string value = excelReader.IsDBNull(i) ? "" : excelReader.GetString(i);
cell.Value = value;
if (!string.IsNullOrEmpty(value))
{
isAdd = true;
}
}
}
if (isAdd)
{
rowCount++;
rowList.Add(row);
}
}
ExcelStateObj excelStateObj = new ExcelStateObj(rowCount, colCount);
excelStateObj.ExcelName = excelReader.Name;
excelStateObj.Rows = rowList.ToArray();
list.Add(excelStateObj);
}while(excelReader.NextResult());
//DataSet result = excelReader.AsDataSet();
excelReader.Close();
excelReader.Dispose();
memory.Close();
memory.Dispose();
return(list);
}
private void Initialize(string source)
{
try
{
var excelSource = new ExcelSource(source);
InitializeExcelReader(excelSource);
ConfigureWorksheet(excelSource);
ConfigureStartRange(excelSource);
#if DEBUG
Log.Debug($"Reader is initialized with '{source}'");
#endif
}
catch (Exception ex)
{
Log.Error(ex, $"Failed to initialize reader for data source '{Source}'");
_reader?.Dispose();
_reader = null;
AddValidationError($"Failed to initialize reader: '{ex.Message}'");
}
}
private void ButtonLoad_Click(object sender, EventArgs e)
{
using (OpenFileDialog ofd = new OpenFileDialog()
{
Filter = "Excel Workbook|*.xls;*.xlsx", ValidateNames = true
})
{
if (ofd.ShowDialog() == DialogResult.OK)
{
try
{
MessageBox.Show(ofd.FileName);
fileStream = File.Open(ofd.FileName, FileMode.Open, FileAccess.Read); //Dosya Kullanımdaysa hata veriyor..
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
isFileLoaded = true;
LabelFileName.Text = ofd.SafeFileName;
path = ofd.FileName.Replace(ofd.SafeFileName, "");
IExcelDataReader reader = ExcelReaderFactory.CreateReader(fileStream);
result = reader.AsDataSet();
cellTypes = new Dictionary <int, Type[, ]>();
for (int workSheetIndex = 0; workSheetIndex < reader.ResultsCount; workSheetIndex++)
{
Type[,] types = new Type[reader.RowCount, reader.FieldCount];
int rowIndex = 0;
while (reader.Read())
{
for (int columnIndex = 0; columnIndex <= reader.FieldCount - 1; columnIndex++)
{
types[rowIndex, columnIndex] = reader.GetFieldType(columnIndex);
}
rowIndex++;
}
cellTypes.Add(workSheetIndex, types);
reader.NextResult();
}
reader.Close();
reader.Dispose();
reader = null;
}
}
}
/// <summary>
/// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
/// </summary>
/// <param name="disposing">True if the instance needs to be disposed of.</param>
protected virtual void Dispose(
bool disposing)
{
if (_disposed)
{
return;
}
if (disposing)
{
_reader?.Dispose();
_reader = null;
}
_disposed = true;
}
private DataSet GetExcelDataSet(int recordLimit)
{
DataSet dataSet = new DataSet();
FileInfo excelFile = new FileInfo(Path);
IExcelDataReader excelReader = GetExcelDataReader(excelFile);
while (excelReader.Read() && dataSet.Tables.Count == 0)
{
if (excelReader.Name == SheetName)
{
dataSet = excelReader.AsDataSet();
if (firstRowHeaders && dataSet.Tables[SheetName].Rows.Count >= 1)
{
for (int columnIndex = 0; columnIndex < dataSet.Tables[SheetName].Columns.Count; columnIndex++)
{
string columnName = dataSet.Tables[SheetName].Rows[0][columnIndex].ToString();
if (!dataSet.Tables[SheetName].Columns.Contains(columnName))
{
dataSet.Tables[SheetName].Columns[columnIndex].ColumnName = columnName;
dataSet.Tables[SheetName].Columns[columnIndex].Caption = columnName;
}
else
{
throw new ApplicationException(string.Format("Please remove the duplicate '{0}' column from the {1} worksheet", columnName, sheetName));
}
}
dataSet.Tables[SheetName].Rows[0].Delete();
dataSet.AcceptChanges();
}
for (int rowIndex = 0; rowIndex < dataSet.Tables[SheetName].Rows.Count && recordLimit != ALL_RECORDS_LIMIT; rowIndex++)
{
if (rowIndex >= recordLimit)
{
dataSet.Tables[SheetName].Rows[rowIndex].Delete();
dataSet.AcceptChanges();
}
}
}
excelReader.NextResult();
}
excelReader.Dispose();
return(dataSet);
}
private static List <AdvisorDeal> GetIndustryByAdvisor(string filePath, params string[] advisorVariations)
{
FileStream stream = System.IO.File.Open(filePath, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
excelReader.IsFirstRowAsColumnNames = true;
//5. Data Reader methods
var deals = new Dictionary <string, int>();
var isFirstRow = true;
while (excelReader.Read())
{
if (isFirstRow)
{
isFirstRow = false;
continue;
}
var acquirorAdvisor = excelReader.GetString(3) ?? "";
var targetAdvisor = excelReader.GetString(4) ?? "";
foreach (var advisor in advisorVariations)
{
if (acquirorAdvisor.Contains(advisor) || targetAdvisor.Contains(advisor))
{
var industry = excelReader.GetString(8);
if (deals.ContainsKey(industry))
{
deals[industry]++;
}
else
{
deals.Add(industry, 1);
}
}
}
}
excelReader.Close();
excelReader.Dispose();
var advisorDeals = deals.Select(x => new AdvisorDeal {
Deals = x.Value, Industry = x.Key
})
.OrderBy(x => x.Industry)
.ToList();
return(advisorDeals);
}
private List <SlmScr032ExcelData> ReadExcel()
{
IExcelDataReader excelReader = null;
try
{
string ext = System.IO.Path.GetExtension(fuData.PostedFile.FileName);
excelReader = ext == ".xls" ? ExcelReaderFactory.CreateBinaryReader(fuData.FileContent) : ExcelReaderFactory.CreateOpenXmlReader(fuData.FileContent);
excelReader.IsFirstRowAsColumnNames = true;
bool isHeader = true;
var dataList = new List <SlmScr032ExcelData>();
while (excelReader.Read())
{
if (isHeader)
{
ExcelColumnName = excelReader.GetString((int)ExcelColName.PreleadId) != null?excelReader.GetString((int)ExcelColName.PreleadId).Trim() : "";
isHeader = false;
continue;
}
SlmScr032ExcelData data = new SlmScr032ExcelData
{
TeamTelesales = excelReader.GetString((int)ExcelColName.TeamTelesales) != null?excelReader.GetString((int)ExcelColName.TeamTelesales).Trim() : "",
EmpCode = excelReader.GetString((int)ExcelColName.EmpCode) != null?excelReader.GetString((int)ExcelColName.EmpCode).Trim() : "",
PreleadId = excelReader.GetString((int)ExcelColName.PreleadId) != null?excelReader.GetString((int)ExcelColName.PreleadId).Trim() : "",
Lot = excelReader.GetString((int)ExcelColName.Lot) != null?excelReader.GetString((int)ExcelColName.Lot).Trim() : ""
};
dataList.Add(data);
}
return(dataList);
}
catch
{
throw;
}
finally
{
if (excelReader != null)
{
excelReader.Close();
excelReader.Dispose();
}
}
}
public DataTable ExcelToDataTable(HttpPostedFileBase fileBase)
{
Stream stream = fileBase.InputStream;
IExcelDataReader reader = null;
if (fileBase.FileName.EndsWith(".xls"))
reader = ExcelReaderFactory.CreateBinaryReader(stream);
else
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
reader.IsFirstRowAsColumnNames = true;
var result = reader.AsDataSet();
reader.Close();
reader.Dispose();
reader = null;
return result.Tables[0];
}
private void Dispose(bool disposing)
{
if (!_disposedValue)
{
if (disposing)
{
// dispose managed state (managed objects)
_reader.Dispose();
_stream.Dispose();
}
// free unmanaged resources (unmanaged objects) and override finalizer
// set large fields to null
_disposedValue = true;
}
}