static int Main(string[] args)
{
Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
string pass = args.Length > 0 ? args[0] : "123456";
var conf = new ExcelReaderConfiguration {
Password = pass
};
NumberFormatInfo nfi = new NumberFormatInfo
{
NumberDecimalSeparator = "."
};
try
{
ExcelTest(@"..\..\..\secretExcelFile.xlsx", @"..\..\..\wellKnownSecrets.csv", ';', conf, nfi);
}
catch (ExcelDataReader.Exceptions.InvalidPasswordException e)
{
Console.WriteLine(e.Message);
return(1);
}
catch (Exception e2)
{
Console.WriteLine(e2.Message);
return(2);
}
return(0);
}
public ActionResult ConvertToJson([FromQuery] string filePath)
{
FileInfo file = new FileInfo(filePath);
if (!file.Exists)
{
return(NotFound());
}
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
var config = new ExcelReaderConfiguration
{
FallbackEncoding = Encoding.GetEncoding(1252)
};
using (var fileStream = file.OpenRead())
using (var reader = ExcelReaderFactory.CreateBinaryReader(fileStream, config))
{
var dsConfig = new ExcelDataSetConfiguration
{
UseColumnDataType = true,
ConfigureDataTable = tableReader => new ExcelDataTableConfiguration
{
UseHeaderRow = true
}
};
var ds = reader.AsDataSet(dsConfig);
List <object> tables = new List <object>();
foreach (DataTable dataTable in ds.Tables)
{
List <Dictionary <string, object> > rows = new List <Dictionary <string, object> >();
List <string> cols = new List <string>();
foreach (DataColumn col in dataTable.Columns)
{
cols.Add(col.ColumnName);
}
foreach (DataRow dataRow in dataTable.Rows)
{
Dictionary <string, object> row = new Dictionary <string, object>();
foreach (DataColumn dataCol in dataTable.Columns)
{
row.Add(dataCol.ColumnName, Convert.ChangeType(dataRow[dataCol], dataCol.DataType));
}
rows.Add(row);
}
tables.Add(new { dataTable.TableName, Data = new { Columns = cols, Rows = rows } });
}
return(Ok(tables));
}
}
public void Read(string filePath, string password)
{
using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
{
string readerOutput = "";
ExcelReaderConfiguration xlConf = new ExcelReaderConfiguration();
xlConf.Password = password;
xlConf.FallbackEncoding = Encoding.GetEncoding(1252);
xlConf.AutodetectSeparators = new char[] { ',', ';', '\t', '|', '#' };
// Auto-detect format, supports:
// - Binary Excel files (2.0-2003 format; *.xls)
// - OpenXml Excel files (2007 format; *.xlsx)
var reader = ExcelReaderFactory.CreateReader(stream, xlConf);
// 1. Use the reader methods
do
{
while (reader.Read())
{
readerOutput += reader.GetValue(0);
}
Debug.WriteLine(readerOutput);
readerOutput = "";
} while (reader.NextResult());
// 2. Use the AsDataSet extension method
var result = reader.AsDataSet();
Debug.WriteLine("DONE READING EXCEL");
// The result of each spreadsheet is in result.Tables
}
}
private ExcelReaderConfiguration TryPossiblePasswords()
{
foreach (var pw in possiblePasswords)
{
try
{
var config = new ExcelReaderConfiguration()
{
Password = pw
};
using (var stream = File.Open(location.FullName, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream, config))
{
}
}
return(config);
}
catch (InvalidPasswordException)
{
continue;
}
}
return(new ExcelReaderConfiguration());
}
private static DataSet OpenExcel(string filePath, string pwd = null)
{
FileStream stream = new FileStream(filePath, FileMode.Open);
IExcelDataReader reader;
ExcelReaderConfiguration readerConf = null;
if (!String.IsNullOrEmpty(pwd))
{
readerConf = new ExcelReaderConfiguration {
Password = pwd
};
}
reader = ExcelReaderFactory.CreateOpenXmlReader(stream, readerConf);
DataSet result = reader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
});
return(result);
}
private async void ReadFile(string filePath, Worksheet worksheet)
{
try
{
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
do
{
while (reader.Read())
{
}
} while (reader.NextResult());
var conf = new ExcelReaderConfiguration {
Password = "******"
};
var excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream, conf);
var result = reader.AsDataSet();
var workbook = result.Tables;
await IterateWorkBook(workbook, worksheet);
}
}
} catch (Exception ex)
{
Console.WriteLine(ex);
}
}
static void ExcelTest(string xlsxPath, string csvPath, char d, ExcelReaderConfiguration conf, NumberFormatInfo nfi)
{
using (var stream = File.Open(xlsxPath, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream, conf))
{
int wNum = 0;
do
{
int kolNum = reader.FieldCount;
int rowNum = reader.RowCount;
StreamWriter sw = new StreamWriter($"{csvPath[0..(csvPath.Length - 4)]}{++wNum}.csv", false, Encoding.UTF8);
public void CsvWrongEncoding()
{
Assert.Throws(typeof(DecoderFallbackException), () =>
{
var configuration = new ExcelReaderConfiguration()
{
FallbackEncoding = Encoding.UTF8
};
using (var excelReader = ExcelReaderFactory.CreateCsvReader(Configuration.GetTestWorkbook("cp1252.csv"), configuration))
{
}
});
}
private static IExcelDataReader CreateDataReader(string path, FileStream stream)
{
var fileExtension = Path.GetExtension(path);
var config = new ExcelReaderConfiguration
{
// configuration stuff...
};
if (fileExtension == ".csv")
{
return(ExcelReaderFactory.CreateCsvReader(stream, config));
}
else if (fileExtension == ".xlsb")
{
return(ExcelReaderFactory.CreateBinaryReader(stream, config));
}
else
{
return(ExcelReaderFactory.CreateReader(stream, config));
}
}
public void TestNoSeparator(ExcelReaderConfiguration configuration)
{
using (var strm = new MemoryStream())
{
using (var writer = new StreamWriter(strm, Encoding.UTF8))
{
writer.WriteLine("This");
writer.WriteLine("is");
writer.WriteLine("a");
writer.Write("test");
writer.Flush();
using (var excelReader = ExcelReaderFactory.CreateCsvReader(strm, configuration))
{
var ds = excelReader.AsDataSet();
Assert.AreEqual("This", ds.Tables[0].Rows[0][0]);
Assert.AreEqual("is", ds.Tables[0].Rows[1][0]);
Assert.AreEqual("a", ds.Tables[0].Rows[2][0]);
Assert.AreEqual("test", ds.Tables[0].Rows[3][0]);
}
}
}
}
/// <summary>
/// Convert the specified 'file' into JSON with the Excel DataReader, and save it to the specified 'outputPath'.
/// </summary>
/// <param name="options"></param>
public async Task ConvertWithDataReaderAsync(SourceOptions options)
{
var file = new FileInfo(options.File);
if (file == null)
{
throw new ArgumentNullException(nameof(file));
}
if (String.IsNullOrWhiteSpace(options.Output))
{
throw new ArgumentException("Argument cannot be null, emtpy or whitespace.", nameof(options.Output));
}
if (String.IsNullOrWhiteSpace(options.SheetName))
{
throw new ConfigurationException("Configuration 'Converter:{Source}:SheetName' cannot be null, emtpy or whitespace.");
}
_logger.LogInformation($"Reading file '{file}'.");
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
var excelOptions = new ExcelReaderConfiguration()
{
FallbackEncoding = Encoding.GetEncoding(1252)
};
using var reader = ExcelReaderFactory.CreateReader(file.Open(FileMode.Open, FileAccess.Read), excelOptions);
var worksheets = reader.AsDataSet();
var worksheet = worksheets.Tables[options.SheetName];
// Get column names.
var columns = new string[worksheet.Columns.Count];
var firstRowIsHeader = options.FirstRowIsHeaders;
var rows = new List <Dictionary <string, object> >();
try
{
foreach (var wrow in worksheet.Rows)
{
var drow = wrow as DataRow;
if (firstRowIsHeader)
{
columns = drow.ItemArray.Select(i => $"{i}").NotNullOrWhiteSpace().ToArray();
for (var i = 0; i < columns.Length; i++)
{
columns[i] = $"{drow.ItemArray[i]}";
}
firstRowIsHeader = false;
}
else
{
var row = new Dictionary <string, object>();
for (var i = 0; i < columns.Length; i++)
{
var item = drow.ItemArray[i];
var type = item.GetType();
var colOptions = options.Columns.ContainsKey(columns[i]) ? options.Columns[columns[i]] : null;
var name = GetName(colOptions, columns[i]);
var value = await GetValueAsync(colOptions, item, row);
if (colOptions?.Skip != SkipOption.Never)
{
if (colOptions?.Skip == SkipOption.Always)
{
continue;
}
if (colOptions?.Skip == SkipOption.AlreadySet && row.ContainsKey(name))
{
continue;
}
if (colOptions?.Skip == SkipOption.Null && (item.GetType().IsDbNull() || item == null))
{
continue;
}
if (colOptions?.Skip == SkipOption.NullOrEmpty && String.IsNullOrEmpty($"{item}"))
{
continue;
}
if (colOptions?.Skip == SkipOption.NullOrWhitespace && String.IsNullOrWhiteSpace($"{item}"))
{
continue;
}
}
if (colOptions?.OutputTo?.Any() ?? false)
{
for (var oi = 0; oi < colOptions.OutputTo.Length; oi++)
{
var outputCol = colOptions.OutputTo[oi];
if (row.ContainsKey(outputCol))
{
row[outputCol] = SetValue(value, oi);
}
else
{
row.Add(outputCol, SetValue(value, oi));
}
}
}
else
{
row.Add(name, value);
}
}
// Post actions can further transform data after a row has been created.
// This is useful when one row's data relies on others.
if (options.Row?.PostActions?.Any() ?? false)
{
foreach (var action in options.Row.PostActions)
{
if (!row.ContainsKey(action.Column))
{
throw new ConfigurationException($"Row action configuration column '{action.Column}' does not exist.");
}
try
{
row[action.Column] = await ConvertAsync(row[action.Column], row, action.Converter, action.ConverterArgs);
}
catch (Exception ex)
{
_logger.LogError(ex, $"Unable to perform post action '{action.Column}'.");
throw;
}
}
}
rows.Add(row);
}
}
}
catch (Exception ex)
{
_logger.LogError(ex, "Fatal error ended converter");
}
var json = JsonSerializer.Serialize(rows, _serialzerOptions);
ExportJsonFile(json, options.Output);
}
protected abstract IExcelDataReader OpenReader(Stream stream, ExcelReaderConfiguration configuration = null);
/// <inheritdoc />
protected override IExcelDataReader OpenReader(Stream stream, ExcelReaderConfiguration configuration = null)
{
return(ExcelReaderFactory.CreateOpenXmlReader(stream, configuration));
}
private void SetupConfig()
{
ReaderConfig = new ExcelReaderConfiguration()
{
// Gets or sets the encoding to use when the input XLS lacks a CodePage
// record, or when the input CSV lacks a BOM and does not parse as UTF8.
// Default: cp1252 (XLS BIFF2-5 and CSV only)
FallbackEncoding = Encoding.GetEncoding(1252),
//// Gets or sets the password used to open password protected workbooks.
//Password = "******",
// Gets or sets an array of CSV separator candidates. The reader
// autodetects which best fits the input data. Default: , ; TAB | #
// (CSV only)
AutodetectSeparators = new char[] { ',', ';', '\t', '|', '#' },
// Gets or sets a value indicating whether to leave the stream open after
// the IExcelDataReader object is disposed. Default: false
LeaveOpen = false,
// Gets or sets a value indicating the number of rows to analyze for
// encoding, separator and field count in a CSV. When set, this option
// causes the IExcelDataReader.RowCount property to throw an exception.
// Default: 0 - analyzes the entire file (CSV only, has no effect on other
// formats)
AnalyzeInitialCsvRows = 0,
};
ExcelDataSetConfig = 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 determine whether to include the current sheet
// in the DataSet. Called once per sheet before ConfigureDataTable.
FilterSheet = (tableReader, sheetIndex) => 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 = true,
// 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;
var hasData = false;
for (var u = 0; u < rowReader.FieldCount; u++)
{
if (rowReader[u] == null || string.IsNullOrEmpty(rowReader[u].ToString()))
{
continue;
}
else
{
hasData = true;
break;
}
}
return(hasData);
},
// 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);
}
}
};
}
public void ReadData(DocumentsStorage documentStorage)
{
using (var stream = excelFileInfo.OpenRead())
{
var configuration = new ExcelReaderConfiguration()
{
FallbackEncoding = Encoding.Default
};
using (var reader = ExcelReaderFactory.CreateOpenXmlReader(stream, configuration))
{
try
{
do
{
int rowCount = reader.RowCount;
int workingRow = 0;
// + Work with header +
int attributePositionStart = 5;
int fieldCount = reader.FieldCount - attributePositionStart;
documentStorage.Init(fieldCount);
reader.Read();
for (int attributeNumber = 0; attributeNumber < fieldCount; attributeNumber++)
{
documentStorage.SetAttributeName(attributeNumber, reader.GetString(attributeNumber + attributePositionStart));
}
workingRow++;
reader.Read(); reader.Read();
for (int attributeNumber = 0; attributeNumber < fieldCount; attributeNumber++)
{
documentStorage.SetAttributeIdentifier(attributeNumber, reader.GetString(attributeNumber + attributePositionStart));
}
// - Work with header -
int documentCount = 1;
while (reader.Read())
{
workingRow++;
string documentIdentifier = null;
ReadColumn(reader, 0, out object textFromReader, out Type textFromReaderType);
if (textFromReader != null && !string.IsNullOrWhiteSpace(textFromReader.ToString()))
{
documentIdentifier = textFromReader.ToString();
}
Document document = documentStorage.CreateDocument(documentIdentifier);
ReadTextFieldFromExcelReader(reader, document, 1, DocumentsStorage.FilesType.Text);
ReadTextFieldFromExcelReader(reader, document, 2, DocumentsStorage.FilesType.ScanCopy);
ReadTextFieldFromExcelReader(reader, document, 3, DocumentsStorage.FilesType.TextPdf);
ReadTextFieldFromExcelReader(reader, document, 4,
DocumentsStorage.FilesType.Attachments);
bool shouldToBeAddedInDocumentStorage = false;
for (int attributeNumber = 0; attributeNumber < fieldCount; attributeNumber++)
{
object value;
Type valueType;
string attributeId = documentStorage.GetAttributeIdentifier(attributeNumber);
int colPosition = attributeNumber + attributePositionStart;
ReadColumn(reader, colPosition, out value, out valueType);
if (value != null)
{
document.SetAttributeValue(attributeId,
new DocumentAttributeValue(value, valueType));
shouldToBeAddedInDocumentStorage = true;
}
}
try
{
if (shouldToBeAddedInDocumentStorage)
{
documentStorage.AddDocument(document);
}
}
catch (Exception ex)
{
Exception sendException =
new Exception($"Произошла ошибка при добавлении документа. Документ '{document.Identifier}' не был добавлен для обработки. Продолжаем обработку.", ex);
ExceptionOccured?.BeginInvoke(sendException, null, null);
continue;
}
}
} while (reader.NextResult());
}
catch (Exception ex)
{
Exception sendException = new Exception("Произошла ошибка при чтении файда шаблона.", ex);
ExceptionOccured?.BeginInvoke(sendException, null, null);
}
}
}
}
