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); } }