public static void ExportToCSV(COREobject core, List <DBItem> Data = null, string Columns = null, string TableName = null)
{
// Init
DBConnection db = core.Entitron;
// Připravíme CSV
List <string> rows = new List <string>();
/// columns
List <string> columns = new List <string>();
if (Columns == null && TableName != null)
{
foreach (DBColumn col in db.Table(TableName).Columns)
{
columns.Add(col.Name);
}
}
else
{
columns = Columns.Split(';').ToList();
}
/// data
if (Data == null && TableName != null)
{
Data = db.Table(TableName).Select().ToList();
}
/// header to csv
List <string> header = new List <string>();
foreach (string column in columns)
{
header.Add($"\"{column}\"");
}
rows.Add(string.Join(";", header));
/// data to csv
foreach (DBItem item in Data)
{
List <string> row = new List <string>();
foreach (string column in columns)
{
row.Add($"\"{item[column].ToString()}\"");
}
rows.Add(string.Join(";", row));
}
string csv = string.Join("\r\n", rows);
core.HttpResponse("export.csv", "application/csv", Encoding.UTF8.GetBytes(csv));
}
public static void DownloadAsFile(COREobject core, string FileName, string Content)
{
Content = Content.Replace("\\n", "\n");
core.HttpResponse(FileName, "application/csv", Encoding.UTF8.GetBytes(Content));
}
public static void RedirectToUrl(COREobject core, string URL)
{
core.HttpResponse(URL);
}
public static void ExportToExcel(COREobject core, string TableName = null, string ViewName = null, List <DBItem> TableData = null, List <dynamic> Filter = null, string Columns = null, Dictionary <string, string> ForeignKeys = null, string OrderBy = null)
{
// Init
DBConnection db = core.Entitron;
DBEntities context = core.Context;
List <DBItem> data = new List <DBItem>();
List <string> rows = new List <string>();
List <string> columns = new List <string>();
List <DbColumn> DBColumns = new List <DbColumn>();
string abc = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
bool isTable = false;
bool isView = false;
List <string> chars = new List <string>();
for (int i = -1; i < abc.Length; i++)
{
for (int j = 0; j < abc.Length; j++)
{
string ch = (i >= 0 ? abc[i].ToString() : "") + abc[j].ToString();
chars.Add(ch);
}
}
string tableName = TableName ?? core.BlockAttribute.ModelTableName;
string viewName = ViewName ?? "";
if (viewName != "")
{
if (TableData == null)
{
TableData = ExportFromView(db, viewName, Filter, OrderBy);
}
columns = GetColumnsFromTable(db, viewName, Columns);
isView = true;
}
else if (!string.IsNullOrEmpty(tableName))
{
data = Entitron.Select(core, TableName: tableName, CondColumn: new string[0], CondOperator: new string[0], CondValue: new string[0]);
columns = GetColumnsFromTable(db, tableName, Columns);
isTable = true;
}
else
{
throw new Exception("Nebylo nalezeno jméno tabulky ani jméno pohledu");
}
// Připravíme XLS
Dictionary <string, Dictionary <int, string> > foreignData = new Dictionary <string, Dictionary <int, string> >();
Dictionary <string, string> foreignColumnNames = new Dictionary <string, string>();
if (ForeignKeys != null)
{
foreach (KeyValuePair <string, string> key in ForeignKeys)
{
foreignData.Add(key.Key, new Dictionary <int, string>());
string[] target = key.Value.Split('.');
string foreignTable = target[0];
string foreignColumn = target[1];
foreach (DBItem fr in db.Table(foreignTable).Select().Where(c => c.Column("id").In(new HashSet <object>(data.Select(i => i[key.Key])))).ToList())
{
foreignData[key.Key].Add((int)fr["id"], (string)fr[foreignColumn]);
}
DbColumn foreignDbColumn = context.DbTables.Include("Columns").Where(t => t.Name == foreignTable).OrderByDescending(t => t.DbSchemeCommitId).First().Columns.Where(c => c.Name == foreignColumn).First();
foreignColumnNames[key.Key] = foreignDbColumn.DisplayName ?? foreignDbColumn.Name;
}
}
if (isTable)
{
DBColumns = context.DbTables.Include("Columns").Where(t => t.Name == tableName).OrderByDescending(t => t.DbSchemeCommitId).First().Columns.ToList();
}
using (MemoryStream stream = new MemoryStream())
{
using (SpreadsheetDocument xls = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook, true))
{
WorkbookPart bookPart = xls.AddWorkbookPart();
bookPart.Workbook = new Workbook();
bookPart.Workbook.AppendChild <Sheets>(new Sheets());
SharedStringTablePart strings = xls.WorkbookPart.AddNewPart <SharedStringTablePart>();
WorksheetPart sheetPart = InsertWorksheet("Data", bookPart);
int c = 0;
if (isTable)
{
foreach (DbColumn col in DBColumns)
{
if (columns.Contains(col.Name))
{
string name;
if (foreignColumnNames.ContainsKey(col.Name))
{
name = foreignColumnNames[col.Name];
}
else
{
name = col.DisplayName ?? col.Name;
}
int i = InsertSharedStringItem(name, strings);
Cell cell = InsertCellInWorksheet(chars[c], 1, sheetPart);
cell.CellValue = new CellValue(i.ToString());
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
c++;
}
}
}
if (isView)
{
foreach (string colName in columns)
{
int i = InsertSharedStringItem(colName, strings);
Cell cell = InsertCellInWorksheet(chars[c], 1, sheetPart);
cell.CellValue = new CellValue(i.ToString());
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
c++;
}
}
uint r = 1;
foreach (DBItem item in data)
{
c = 0;
r++;
if (isTable)
{
foreach (DbColumn col in DBColumns)
{
if (columns.Contains(col.Name))
{
string value;
if (foreignData.ContainsKey(col.Name))
{
if (foreignData[col.Name].ContainsKey((int)item[col.Name]))
{
value = foreignData[col.Name][(int)item[col.Name]];
}
else
{
value = item[col.Name].ToString();
}
}
else
{
value = item[col.Name].ToString();
if (col.Type == "boolean")
{
value = value == "True" ? "Ano" : "Ne";
}
}
int i = InsertSharedStringItem(value, strings);
Cell cell = InsertCellInWorksheet(chars[c], r, sheetPart);
cell.CellValue = new CellValue(i.ToString());
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
c++;
}
}
}
if (isView)
{
foreach (string colName in columns)
{
string value = item[colName].ToString();
int i = InsertSharedStringItem(value, strings);
Cell cell = InsertCellInWorksheet(chars[c], r, sheetPart);
cell.CellValue = new CellValue(i.ToString());
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
c++;
}
}
}
xls.Close();
stream.Seek(0, SeekOrigin.Begin);
core.HttpResponse("export.xlsx", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", stream.ToArray());
}
}
}
