Esempio n. 1
0
        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));
        }
Esempio n. 2
0
        public static void DownloadAsFile(COREobject core, string FileName, string Content)
        {
            Content = Content.Replace("\\n", "\n");

            core.HttpResponse(FileName, "application/csv", Encoding.UTF8.GetBytes(Content));
        }
Esempio n. 3
0
 public static void RedirectToUrl(COREobject core, string URL)
 {
     core.HttpResponse(URL);
 }
Esempio n. 4
0
        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());
                }
            }
        }