/// <summary>
/// Creates an Excel workbook response. Automatically converts the specified file name to a safe file name.
/// </summary>
public static EwfResponse CreateExcelWorkbookResponse(Func <string> extensionlessFileNameCreator, Func <ExcelFileWriter> workbookCreator)
{
return(Create(
ExcelFileWriter.ContentType,
new EwfResponseBodyCreator(stream => workbookCreator().SaveToStream(stream)),
fileNameCreator: () => ExcelFileWriter.GetSafeFileName(extensionlessFileNameCreator())));
}
/// <summary>
/// The main method
/// </summary>
/// <param name="args">The arguments.</param>
static void Main(string[] args)
{
// Retrieve project path
string projectPath = AppDomain.CurrentDomain.BaseDirectory.Substring(0, AppDomain.CurrentDomain.BaseDirectory.LastIndexOf("bin"));
// Retrieve template document.
var templatePath = $"{projectPath}\\Template\\UserList.xlsx";
var fileAsStream = new MemoryStream(File.ReadAllBytes(templatePath));
// Import file with ExcelFileReader From File info to List of dictionary with header
List <Dictionary <string, object> > dictionaryResultFromFileInfo = ExcelFileReader.ImportFileAsDictionary(new FileInfo(templatePath), "Users", true);
// Import file with ExcelFileReader From File Stream to List of dictionary without header
List <Dictionary <string, object> > dictionaryResultFromFileStream = ExcelFileReader.ImportFileAsDictionary(fileAsStream, "Users");
// Import file with ExcelFileReader From File info to Data table
DataTable dataTableResultFromFileInfo = ExcelFileReader.ImportFileAsDataTable(new FileInfo(templatePath), "Users");
// Import file with ExcelFileReader From File Stream to Data table
DataTable dataTableResultFromFileStream = ExcelFileReader.ImportFileAsDataTable(fileAsStream, "Users");
// Export file with ExcelFileWriter
string tempFilePath = $"{projectPath}\\App_Data\\{Guid.NewGuid()}.xlsx";
var streamExport = ExcelFileWriter.ExportToExcel(dataTableResultFromFileStream, "Users");
FileStream file = new FileStream(tempFilePath, FileMode.Create, FileAccess.Write);
streamExport.WriteTo(file);
file.Close();
streamExport.Close();
Console.Write($"Generated file path : {tempFilePath}");
Console.ReadLine();
}
static void Main(string[] args)
{
var facebookLogin = ConfigurationManager.AppSettings["FacebookLogin"];
var facebookPass = ConfigurationManager.AppSettings["FacebookPass"];
var facebookGroupId = ConfigurationManager.AppSettings["FacebookGroupId"];
var memberProcessingDelay = int.Parse(ConfigurationManager.AppSettings["MemberProcessingDelayInMs"]);
var refreshDelay = 60 * 1000 * int.Parse(ConfigurationManager.AppSettings["RefreshDelayInMin"]);
var googleSpreadsheetId = ConfigurationManager.AppSettings["GoogleSpreadsheetId"];
var excelFilePath = ConfigurationManager.AppSettings["ExcelFilePath"];
var chromeOptions = ConfigurationManager.AppSettings["ChromeOptions"];
var spreadSheetsWriter = new GoogleSpreadSheetsWriter(googleSpreadsheetId);
var excelFileWriter = new ExcelFileWriter(excelFilePath);
var consoleWriter = new ConsoleWriter();
//consoleWriter.Write("FacebookRobot started.");
var facebookRobot = new FacebookRobot(chromeOptions, facebookGroupId);
facebookRobot.FacebookLogin(facebookLogin, facebookPass);
var writers = new IWriter[] { spreadSheetsWriter, excelFileWriter, consoleWriter }; //TODO: if failed writes into spreadsheet multiple times
facebookRobot.ProcessNewMemberRequests(writers, memberProcessingDelay, refreshDelay);
excelFileWriter.Dispose();
facebookRobot.Close();
//consoleWriter.Write("FacebookRobot stopped.");
}
//public virtual V Value { get; set; }
public virtual void FormatValue(IFileWriter exportDestination, T record)
{
ExcelFileWriter writer = (ExcelFileWriter)exportDestination;
//if (null != record && null != this.PropertyGetter)
// writer.RecordBuffer.Cells[writer.CurrentRowNo, this.ColumnNo].Value2 = this.GetTargetValue(record);
//else if (null != this.Value)
// writer.RecordBuffer.Cells[writer.CurrentRowNo, this.ColumnNo].Value2 = this.Value;
}
public override void Insert(ExcelFileWriter writer)
{
if (null != this.Target && null != this.PropertyGetter)
{
this.Value = this.PropertyGetter(this, this.Target);
}
else
{
this.Value = this.SignatureValue;
}
writer.Append(this.Value);
}
public void TestGetSafeFileName()
{
/* First assert that the output's what's expected. */
Console.WriteLine("Making sure safe filename works.");
var writer = new ExcelFileWriter();
const string fileName = "get safe filename testing :" + "_gibberish_here_*$#*&(#@)*(?|\\/@#_end_gibberish";
var safeFileName = ExcelFileWriter.GetSafeFileName(fileName);
Assert.AreEqual(safeFileName, "GetSafeFilenameTesting_Gibberish_here_$#&(#@)(@#_end_gibberish.xlsx");
/* Then actually write it so we're sure the filesystem accepts it too. */
runTest(actualWriter => fileName);
}
public override void Insert(ExcelFileWriter writer)
{
if (null != this.PropertyGetter)
{
this.Value = this.PropertyGetter(this, this.Target);
try
{
writer.Append(this.Value);
}
catch (Exception exc)
{
throw new Exception(this.FieldInfo + ": \"" + this.Value + "\" : " + exc.ToString());
}
}
}
private static void addAutofitTestRows(ExcelFileWriter writer, string fitOrNot)
{
writer.DefaultWorksheet.AddHeaderToWorksheet("This is the default worksheet - hopefully " + fitOrNot);
writer.DefaultWorksheet.AddRowToWorksheet("500", "5000", "5", "50", "500", "5000", "5", "5", "5", "5", "50");
writer.DefaultWorksheet.AddRowToWorksheet("500", "5000", "5", "50", "500", "5000", "5", "5", "5", "5", "50");
writer.DefaultWorksheet.AddRowToWorksheet("500", "5000", "5", "50", "500", "5000", "5", "5", "5", "5", "50");
writer.DefaultWorksheet.AddRowToWorksheet(
"500",
"5000",
"5",
"50",
"500",
"5000",
"5",
"Longer than expected cell value here oh yeah it is. Super long in fact.");
}
public void WriteSimpleFile()
{
var output = new MemoryStream();
var pack = new ExcelFileWriter(output);
pack.AddPage("FooBar", new List <IList>
{
new List <object> {
"1", "2", "3"
},
new List <object> {
1, 2, 3
}
});
pack.Save();
Assert.Equal(4096, output.GetBuffer().Length);
}
internal static ExcelFileWriter CreateExcelFileWriter(MergeRowTree rowTree, IEnumerable <string> fieldNames, bool useMsWordFieldNames)
{
var excelFile = new ExcelFileWriter();
if (rowTree.Rows.Any())
{
foreach (var fieldName in fieldNames)
{
if (rowTree.Rows.First().Values.All(i => i.Name != fieldName))
{
// Use ApplicationException instead of MailMergingException because the field names can easily be validated before this method is called.
throw new ApplicationException("Merge field " + fieldName + " is invalid.");
}
}
var sheet = excelFile.DefaultWorksheet;
sheet.AddHeaderToWorksheet(
fieldNames.Select(fieldName => rowTree.Rows.First().Values.Single(i => i.Name == fieldName))
.Select(mergeValue => useMsWordFieldNames ? mergeValue.MsWordName : mergeValue.Name.CamelToEnglish())
.ToArray());
sheet.FreezeHeaderRow();
foreach (var row in rowTree.Rows)
{
sheet.AddRowToWorksheet(
fieldNames.Select(fieldName => row.Values.Single(i => i.Name == fieldName))
.Select(
mergeValue => {
var mergeValueAsString = mergeValue as MergeValue <string>;
string value = null;
if (mergeValueAsString != null)
{
value = mergeValueAsString.Evaluate(false);
}
if (value == null)
{
// Use ApplicationException instead of MailMergingException because the field names can easily be validated before this method is called.
throw new ApplicationException("Merge field " + mergeValue.Name + " evaluates to an unsupported type.");
}
return(value);
})
.ToArray());
}
}
return(excelFile);
}
public override void Export(ExcelFileWriter writer)
{
if (null == this.Target)
{
throw new Exception("The export instance is null.");
}
this.HeaderMapper.Value = this.DetailMapper.Target = this.FooterMapper.Value = this.Target;
this.HeaderMapper.Insert(writer);
writer.Next();
this.DetailMapper.Export(writer);
this.FooterMapper.Insert(writer);
writer.Next();
}
public bool SaveToExcel(string FileName)
{
try {
using (ExcelFileWriter file = ExcelFileWriter.OpenExcelApplication()) {
if (file == null)
{
throw new NullReferenceException("Failed to open the Excel application.");
}
using (WorkbookWriter book = file.CreateNewWorkbook()) {
if (book == null)
{
throw new NullReferenceException("Failed to create a new Excel workbook.");
}
WorksheetWriter sheet = book.GetActiveWorksheet();
if (sheet == null)
{
throw new NullReferenceException("Failed to find the active worksheet.");
}
sheet.Name = "Data Acquisition";
int col = WorksheetWriter.MinColumn;
int row;
foreach (string cat in orderedCategories)
{
row = WorksheetWriter.MinRow;
sheet[row, col] = cat;
sheet.Bold(row, col);
foreach (object data in allData[cat])
{
sheet[++row, col] = data.ToString();
}
col++;
}
sheet.AutoFitAllColumns();
return(book.Save(FileName + WorkbookWriter.DEFAULT_FILE_EXTENSION));
}
}
} catch (Exception) {
Console.WriteLine("Unable to save data.");
return(false);
}
}
public override void Insert(ExcelFileWriter writer)
{
//by kittikun 2014-10-30
if (null != this.Target && null != this.PropertyGetter)
{
this.Value = this.PropertyGetter(this, this.Target);
}
if (null != this.Value)
{
foreach (ExcelFieldMapper <V> f in this.FieldMappers)
{
f.Target = this.Value;
f.Insert(writer);
}
}
this.Value = null;
}
//public override IFileReader ImportSource
//{
// get
// {
// if (String.IsNullOrEmpty(base.ImportFilePath))
// throw new iSabayaException(Messages.FileFormatFilePathIsNotDefined);
// if (null == base.ImportSource)
// {
// base.ImportSource = new ExcelFileReader(base.ImportFilePath);
// }
// return base.ImportSource;
// }
// protected set { base.ImportSource = value; }
//}
//public override IFileWriter ExportDestination
//{
// get
// {
// if (null == base.ExportDestination)
// {
// if (String.IsNullOrEmpty(base.ExportFilePath))
// throw new iSabayaException("The file path is not defined.");
// base.ExportDestination = new ExcelFileWriter(base.ExportFilePath);
// }
// return base.ExportDestination;
// }
// protected set { base.ExportDestination = value; }
//}
#region ObjectFileMapping implementation
public override void Export(string filePath, T t)
{
ExcelFileWriter writer = new ExcelFileWriter(filePath);
if (null != this.OnExportStartHandler)
{
this.OnExportStartHandler(filePath, t);
}
this.RecordMapping.Target = t;
this.RecordMapping.Export(writer);
if (null != this.OnExportFinishHandler)
{
this.OnExportFinishHandler(filePath, t);
}
writer.Close();
}
/// <summary>
/// Performs an EhModifyDataAndSendFile operation. This is convenient if you want to get the built-in export functionality, but from
/// an external button rather than an action on this table.
/// </summary>
public PostBackAction ExportToExcel()
{
return(new PostBackAction(
new SecondaryResponse(
() => new EwfResponse(
() => caption.Any() ? caption : "Excel export",
() => {
var workbook = new ExcelFileWriter();
foreach (var rowSetup in rowSetups)
{
if (rowSetup.IsHeader)
{
workbook.DefaultWorksheet.AddHeaderToWorksheet(rowSetup.CsvLine.ToArray());
}
else
{
workbook.DefaultWorksheet.AddRowToWorksheet(rowSetup.CsvLine.ToArray());
}
}
return workbook;
}))));
}
private void runTest(Func <ExcelFileWriter, string> code, bool includeDefaultWorksheet = true)
{
var start = DateTime.Now;
var writer = includeDefaultWorksheet ? new ExcelFileWriter() : new ExcelFileWriter(false);
var fileName = code(writer);
var filePath = Path.Combine(outputFolderPath, ExcelFileWriter.GetSafeFileName(timestampPrefix + fileName));
var doneCreating = DateTime.Now;
long size;
using (var f = File.Create(filePath)) {
writer.SaveToStream(f);
size = f.Length;
}
var doneWriting = DateTime.Now;
Console.WriteLine("To view this test file, open '{0}' with Excel.".FormatWith(filePath));
Console.WriteLine(
"Finished creating {3}kb file in {0}ms, written in another {1}ms, for a total of {2}ms.".FormatWith(
(doneCreating - start).TotalMilliseconds,
(doneWriting - doneCreating).TotalMilliseconds,
(doneWriting - start).TotalMilliseconds,
(size / 1000)));
}
public abstract void Export(ExcelFileWriter writer);
public static EwfResponse GetExcelFileResponse(this ExcelFileWriter workbook, string fileNameWithoutExtension)
{
return(new EwfResponse(() => fileNameWithoutExtension, () => workbook));
}
public abstract void Insert(ExcelFileWriter writer);
//public override void ExtractRecords(Context context, String filePath,
// RecordConsumer<T> headerRecordConsumer,
// RecordConsumer<T> detailRecordConsumer,
// RecordConsumer<T> footerRecordConsumer)
//{
// if (null == DetailRecordFormat)
// throw new iSabayaException(Messages.DetailRecordFormatNotDefined);
// //Validate consistency of parameters
// this.InputFilePath = filePath;
// this.RecordBuffer = (Worksheet)this.RecordSource.ActiveSheet;
// if (null == this.RecordBuffer)
// throw new iSabayaException(String.Format("Can't get active sheet of '{0}'" + this.InputFilePath));
// T recordObject;
// //read and process the first line
// if (null != HeaderRecordFormat)
// {
// recordObject = this.HeaderRecordFormat.Extract(context, this.RecordBuffer);
// if (null != headerRecordConsumer)
// if (!headerRecordConsumer(context, recordObject)) return;
// }
// //read and process subsequent records
// this.CurrentRowNo = this.LineNoOfFirstDetailRecord;
// Range endRow = this.RecordBuffer.get_Range("A1", Missing.Value).get_End(XlDirection.xlDown);
// int endRowNo = endRow.Row;
// if (null == this.FooterRecordFormat)
// {
// for (this.CurrentRowNo = this.LineNoOfFirstDetailRecord; this.CurrentRowNo <= endRowNo; ++this.CurrentRowNo)
// {
// recordObject = this.DetailRecordFormat.Extract(context, this.RecordBuffer);
// if (null != detailRecordConsumer)
// if (!detailRecordConsumer(context, recordObject)) return;
// }
// }
// else
// {
// for (this.CurrentRowNo = this.LineNoOfFirstDetailRecord; this.CurrentRowNo < endRowNo; ++this.CurrentRowNo)
// {
// recordObject = this.DetailRecordFormat.Extract(context, this.RecordBuffer);
// if (null != detailRecordConsumer)
// if (!detailRecordConsumer(context, recordObject)) return;
// }
// recordObject = this.FooterRecordFormat.Extract(context, this.RecordBuffer);
// if (null != footerRecordConsumer)
// if (!footerRecordConsumer(context, recordObject)) return;
// }
// this.RecordSource.Close(false, Missing.Value, Missing.Value);
//}
public override IFileWriter InitializeOutput(String filePath)
{
ExcelFileWriter excelFileWriter = new ExcelFileWriter(filePath);
return(excelFileWriter);
}
public ExportControl()
{
_excelFileWriter = new ExcelFileWriter();
InitializeComponent();
}
//public override ExtractStatus Import(ExcelFileReader recordSource)
//{
// ExtractStatus r;
// int count = 0;
// do
// {
// V v;
// if (null == this.SignatureRecordMapper)
// {
// v = new V();
// r = ExtractStatus.Success;
// }
// else
// {
// this.SignatureRecordMapper.Value = null;
// r = this.SignatureRecordMapper.Import(recordSource);
// if (r == ExtractStatus.ValueMismatched && count >= this.MinOccurrence && (this.MaxOccurrence == 0 || count <= this.MaxOccurrence))
// return ExtractStatus.Success;
// if (r != ExtractStatus.Success)
// return r;
// v = this.SignatureRecordMapper.Value;
// }
// if (r == ExtractStatus.Success)
// {
// ++count;
// this.PropertySetter(this, this.Target, v);
// if (null != this.RecordMappers)
// {
// foreach (var m in this.RecordMappers)
// {
// m.Target = v;
// r = m.Import(recordSource);
// if (r != ExtractStatus.Success)
// break;
// }
// }
// }
// } while (this.MaxOccurrence == 0 || count < this.MaxOccurrence);
// return r;
//}
public override void Export(ExcelFileWriter writer)
{
this.Export <T, V>(writer);
}
private static void SelectDynamicReport(ReportArgument rArgument, SearchArgument sArgument, string orderBy, string companyName, string branchName, string foreignKeyName, int id, string UserName)
{
// IEnumerable<T> data;
List <RollBackDistributionCompanyInfo> data;
int count;
if (!String.IsNullOrEmpty(foreignKeyName))
{
if (sArgument != null)
{
data = SelectDynamicSearchByFK(foreignKeyName, id, sArgument, orderBy).ToList <RollBackDistributionCompanyInfo>();
count = SelectSearchByFKCountCached(foreignKeyName, id, sArgument);
}
else
{
data = SelectDynamicByFK(foreignKeyName, id, orderBy).ToList <RollBackDistributionCompanyInfo>();
count = SelectByFKCountCached(foreignKeyName, id);
}
}
else
{
if (sArgument != null)
{
data = SelectSearch(sArgument, orderBy).ToList <RollBackDistributionCompanyInfo>();
count = data.Count;
//count = SelectSearchCountCached(sArgument);
}
else
{
data = Select(orderBy).ToList <RollBackDistributionCompanyInfo>();
count = data.Count;
//count = SelectCountCached();
}
}
int n = count;
int m = rArgument.ReportArguments.Count;
TypeCode[] codes = new TypeCode[m];
PropertyInfo[] infos = new PropertyInfo[m];
bool first = true;
if (rArgument.Format == "PDF")
{
PDFCreators creator = new PDFCreators(rArgument.Orientation, rArgument.MarginLeft, rArgument.MarginRight, rArgument.MarginTop, rArgument.MarginBottom);
creator.SetDocumentHeaderFooter(companyName);
creator.OpenPDF();
creator.SetTitle(rArgument.ReportName);
string[] headers = new string[m];
for (int i = 0; i < m; i++)
{
headers[i] = rArgument.ReportArguments[i].HeaderText;
}
creator.CreateTable(m, true, headers, branchName);
for (int i = 0; i < n; i++)
{
object[] values = new object[m];
for (int j = 0; j < m; j++)
{
if (first)
{
infos[j] = typeof(RollBackDistributionCompanyInfo).GetProperty(rArgument.ReportArguments[j].PropertyField);
if (IsNullableType(infos[j].PropertyType))
{
NullableConverter nc = new NullableConverter(infos[j].PropertyType);
codes[j] = Type.GetTypeCode(nc.UnderlyingType);
}
else
{
codes[j] = Type.GetTypeCode(infos[j].PropertyType);
}
}
values[j] = infos[j].GetValue(data[i], null);
if (codes[j] == TypeCode.DateTime)
{
DateTime date = (DateTime)values[j];
values[j] = date.ToShortDateString();
}
if (codes[j] == TypeCode.Decimal)
{
decimal dec = (decimal)values[j];
values[j] = String.Format("{0:#,0.00}", dec);
}
if (codes[j] == TypeCode.Boolean)
{
bool temp = (bool)values[j];
if (temp == true)
{
values[j] = "Да";
}
else
{
values[j] = "Не";
}
}
}
first = false;
if (creator.AddDataRow(values, m, codes))
{
i--;
}
}
creator.AddTable();
creator.FinishPDF();
}
//Creating Excel document
else if (rArgument.Format == "XLS")
{
ExcelFileWriter <RollBackDistributionCompanyInfo> myExcel = new ExcelFileWriter <RollBackDistributionCompanyInfo>();
string[] headers = new string[m];
for (int i = 0; i < m; i++)
{
headers[i] = rArgument.ReportArguments[i].HeaderText;
}
myExcel.Headers = headers;
int temp_num = ('A') + m - 1;
char lastCol = Convert.ToChar(temp_num);
string finalColumn = lastCol + "1";
myExcel.ColumnCount = m - 1;
myExcel.RowCount = n;
myExcel.ActivateExcel();
myExcel.FillHeaderColumn(headers, "A1", finalColumn);
for (int i = 0; i < n; i++)
{
object[] values = new object[m];
for (int j = 0; j < m; j++)
{
if (first)
{
infos[j] = typeof(RollBackDistributionCompanyInfo).GetProperty(rArgument.ReportArguments[j].PropertyField);
if (IsNullableType(infos[j].PropertyType))
{
NullableConverter nc = new NullableConverter(infos[j].PropertyType);
codes[j] = Type.GetTypeCode(nc.UnderlyingType);
}
else
{
codes[j] = Type.GetTypeCode(infos[j].PropertyType);
}
}
values[j] = infos[j].GetValue(data[i], null);
if (codes[j] == TypeCode.DateTime)
{
DateTime date = (DateTime)values[j];
values[j] = date.ToShortDateString();
}
if (codes[j] == TypeCode.Boolean)
{
bool temp = (bool)values[j];
if (temp == true)
{
values[j] = "Да";
}
else
{
values[j] = "Не";
}
}
}
first = false;
string fColumn = "A" + (i + 2).ToString();
string lColumn = lastCol + (i + 2).ToString();
myExcel.FillRowData_Mine(values, fColumn, lColumn);
}
string fileName = UserName + "_" + rArgument.ReportName + ".xls";
myExcel.SaveExcel(fileName);
myExcel.FinishExcel(fileName);
}
//Create Word document
else if (rArgument.Format == "DOC")
{
WordFileWriter <RollBackDistributionCompanyInfo> myWord = new WordFileWriter <RollBackDistributionCompanyInfo>();
myWord.Orientation = rArgument.Orientation;
myWord.ActivateWord();
//myWord.InsertingText(rArgument.ReportName);
//myWord.InsertingText("Датум на извештај: " + DateTime.Now.ToShortDateString());
string[] headers = new string[m];
for (int i = 0; i < m; i++)
{
headers[i] = rArgument.ReportArguments[i].HeaderText;
}
myWord.Headers = headers;
object[,] tempFillValues = new object[n, m];
CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
CultureInfo oldUICI = System.Threading.Thread.CurrentThread.CurrentUICulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo("mk-MK");
System.Threading.Thread.CurrentThread.CurrentUICulture = new CultureInfo("mk-MK");
for (int i = 0; i < n; i++)
{
object[] values = new object[m];
for (int j = 0; j < m; j++)
{
if (first)
{
infos[j] = typeof(RollBackDistributionCompanyInfo).GetProperty(rArgument.ReportArguments[j].PropertyField);
if (IsNullableType(infos[j].PropertyType))
{
NullableConverter nc = new NullableConverter(infos[j].PropertyType);
codes[j] = Type.GetTypeCode(nc.UnderlyingType);
}
else
{
codes[j] = Type.GetTypeCode(infos[j].PropertyType);
}
}
values[j] = infos[j].GetValue(data[i], null);
if (codes[j] == TypeCode.DateTime)
{
DateTime date = (DateTime)values[j];
values[j] = date.ToShortDateString();
}
if (codes[j] == TypeCode.Boolean)
{
bool temp = (bool)values[j];
if (temp == true)
{
values[j] = "Да";
}
else
{
values[j] = "Не";
}
}
tempFillValues[i, j] = values[j];
}
first = false;
}
System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
System.Threading.Thread.CurrentThread.CurrentUICulture = oldUICI;
myWord.FillValues = tempFillValues;
myWord.CreateTable(n, m);
myWord.InsertFooter();
myWord.InsertHeader(rArgument.BranchName);
string fileName = UserName + "_" + rArgument.ReportName + ".doc";
myWord.SaveDOC(fileName);
myWord.FinishDOC(fileName);
}
}
