private ExcelDataSetConfiguration createDataSetReadConfig(int headerRow)
{
var tableConfig = new ExcelDataTableConfiguration()
{
// Gets or sets a value indicating whether to use a row from the
// data as column names.
UseHeaderRow = true,
// Gets or sets a callback to determine whether to include the
// current row in the DataTable.
FilterRow = (rowReader) => {
return(rowReader.Depth > headerRow - 2);
},
};
return(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) => { return tableConfig; },
});
}
/// <summary>
/// open excel sheet
/// </summary>
/// <param name="filePath"></param>
private void OpenDataMap(string filePath)
{
var configuredatatable = new ExcelDataTableConfiguration {
UseHeaderRow = true, ReadHeaderRow = rowreader => rowreader.Read()
};
try
{
using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
DataMapDS = reader.AsDataSet(new ExcelDataSetConfiguration {
ConfigureDataTable = (tableReader) => configuredatatable
});
}
}
}
catch (Exception ex)
{
log.Error("ReadDataMap", ex);
ExcelError = true;
}
ExcelError = false;
}
public async Task <Stream> ToCsvStream(Stream sourceStream, bool isLegacyXls = false)
{
try
{
var reader = isLegacyXls
? ExcelReaderFactory.CreateBinaryReader(sourceStream)
: ExcelReaderFactory.CreateOpenXmlReader(sourceStream);
if (reader is null)
{
throw new ArgumentException("Unsupported source stream");
}
var excelDataTableConfiguration =
new ExcelDataTableConfiguration
{
UseHeaderRow = false
};
var excelDataSetConfiguration =
new ExcelDataSetConfiguration
{
ConfigureDataTable = (tableReader) => excelDataTableConfiguration
};
var ds = reader.AsDataSet(excelDataSetConfiguration);
var csvContent = string.Empty;
int rowNumber = 0;
while (rowNumber < ds.Tables[0].Rows.Count)
{
var arr = new List <string>();
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
arr.Add(ds.Tables[0].Rows[rowNumber][i].ToString());
}
rowNumber++;
csvContent += string.Join(",", arr) + "\n";
}
sourceStream.Close();
var destinationStream = new MemoryStream();
var writer = new StreamWriter(destinationStream, Encoding.UTF8);
await writer.WriteAsync(csvContent);
await writer.FlushAsync();
destinationStream.Position = 0;
return(destinationStream);
}
catch (Exception exception)
{
throw new CouldNotConvertException("Unable to convert the given Excel stream to a CSV stream", exception);
}
}
private ExcelDataSetConfiguration CreateDataSetReadConfig(int headerRow)
{
var tableConfig = new ExcelDataTableConfiguration()
{
UseHeaderRow = true,
FilterRow = (rowReader) =>
{
return(rowReader.Depth > headerRow - 1);
},
};
return(new ExcelDataSetConfiguration()
{
UseColumnDataType = true,
ConfigureDataTable = (tableReader) => { return tableConfig; },
});
}
private static DataSet ReadExcelFile(string fullExcelFileName, int headerRowIndex)
{
var columnHeaders = new HashSet <string>();
var dataTableConfiguration = new ExcelDataTableConfiguration()
{
UseHeaderRow = true,
ReadHeaderRow = (rowReader) =>
{
while (rowReader.Depth < headerRowIndex - 1)
{
rowReader.Read();
}
},
FilterColumn = (rowReader, columnIndex) =>
{
var header = rowReader.GetString(columnIndex);
if (!columnHeaders.Contains(header))
{
columnHeaders.Add(header);
return(true);
}
return(false);
},
FilterRow = (rowReader) =>
{
var isRowAfterHeaderRow = rowReader.Depth > headerRowIndex - 2;
var isFirstColumnPopulated = !string.IsNullOrWhiteSpace(rowReader.GetString(0));
return(isRowAfterHeaderRow && isFirstColumnPopulated);
},
};
var dataSetConfiguration = new ExcelDataSetConfiguration()
{
UseColumnDataType = true,
ConfigureDataTable = (tableReader) => dataTableConfiguration,
};
using (var stream = new FileStream(fullExcelFileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
return(reader.AsDataSet(dataSetConfiguration));
}
}
private ExcelDataSetConfiguration CreateDataSetReadConfig()
{
var tableConfig = new ExcelDataTableConfiguration()
{
// Gets or sets a value indicating whether to use a row from the
// data as column names.
UseHeaderRow = false,
};
return(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) => { return tableConfig; },
});
}
/// <summary>
///
/// </summary>
private void GetCitectTagsFromExcel(string filePath)
{
var configuredatatable = new ExcelDataTableConfiguration {
UseHeaderRow = true
};
try
{
using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
CitectTagsDataSet.Add(reader.AsDataSet(new ExcelDataSetConfiguration {
ConfigureDataTable = (tableReader) => configuredatatable
}));
}
}
}
catch (Exception ex)
{
log.Error("GetCitectTagsFromExcel", ex);
}
}
