internal bool WriteLanguages(List <MappedLanguage> mappedLanguages, string path)
{
if (string.IsNullOrEmpty(path))
{
throw new Exception(PluginResources.Warning_FilePathNull);
}
if (mappedLanguages == null || mappedLanguages.Count == 0)
{
throw new NullReferenceException(PluginResources.Warning_LanguagesEmpty);
}
File.Delete(path);
var excelDocument = new Excel();
var spreadsheet = excelDocument.CreateWorkbook(path);
excelDocument.AddBasicStyles(spreadsheet);
var worksheet1 = excelDocument.AddWorksheet(spreadsheet, "Languages");
var rowIndex = Convert.ToUInt32(1);
// write the header row
excelDocument.SetCellValue(spreadsheet, worksheet1, 1, rowIndex, "Code");
excelDocument.SetCellValue(spreadsheet, worksheet1, 2, rowIndex, "Language");
excelDocument.SetCellValue(spreadsheet, worksheet1, 3, rowIndex, "Region");
excelDocument.SetCellValue(spreadsheet, worksheet1, 4, rowIndex, "Mapped Code");
excelDocument.SetCellValue(spreadsheet, worksheet1, 5, rowIndex, "Custom Display Name");
// write the language rows
foreach (var langauge in mappedLanguages)
{
rowIndex++;
excelDocument.SetCellValue(spreadsheet, worksheet1, 1, rowIndex, langauge.LanguageCode);
excelDocument.SetCellValue(spreadsheet, worksheet1, 2, rowIndex, langauge.LanguageName);
excelDocument.SetCellValue(spreadsheet, worksheet1, 3, rowIndex, langauge.LanguageRegion);
excelDocument.SetCellValue(spreadsheet, worksheet1, 4, rowIndex, langauge.MappedCode);
excelDocument.SetCellValue(spreadsheet, worksheet1, 5, rowIndex, langauge.CustomDisplayName);
}
// set the column widths
excelDocument.SetColumnWidth(worksheet1, 1, 20);
excelDocument.SetColumnWidth(worksheet1, 2, 25);
excelDocument.SetColumnWidth(worksheet1, 3, 35);
excelDocument.SetColumnWidth(worksheet1, 4, 20);
excelDocument.SetColumnWidth(worksheet1, 5, 35);
// add a table filter
var autoFilter = new AutoFilter {
Reference = "A1:E" + mappedLanguages.Count + 1
};
worksheet1.Append(autoFilter);
worksheet1.Save();
spreadsheet.Close();
return(true);
}
internal bool WriteLanguages(List <MappedLanguage> mappedLanguages, string path)
{
if (string.IsNullOrEmpty(path))
{
throw new Exception("File path cannot be null!");
}
if (mappedLanguages == null || mappedLanguages.Count == 0)
{
throw new NullReferenceException("Languages cannot be empty or null!");
}
File.Delete(path);
var excelDocument = new Excel();
var spreadsheet = excelDocument.CreateWorkbook(path);
excelDocument.AddBasicStyles(spreadsheet);
var worksheet1 = excelDocument.AddWorksheet(spreadsheet, "Languages");
var rowIndex = Convert.ToUInt32(1);
// write the header row
excelDocument.SetCellValue(spreadsheet, worksheet1, 1, rowIndex, "Language");
excelDocument.SetCellValue(spreadsheet, worksheet1, 2, rowIndex, "Region");
excelDocument.SetCellValue(spreadsheet, worksheet1, 3, rowIndex, "Trados Code");
excelDocument.SetCellValue(spreadsheet, worksheet1, 4, rowIndex, "MT Code");
excelDocument.SetCellValue(spreadsheet, worksheet1, 5, rowIndex, "MT Code (locale)");
// write the language rows
foreach (var langauge in mappedLanguages)
{
rowIndex++;
excelDocument.SetCellValue(spreadsheet, worksheet1, 1, rowIndex, langauge.Name);
excelDocument.SetCellValue(spreadsheet, worksheet1, 2, rowIndex, langauge.Region);
excelDocument.SetCellValue(spreadsheet, worksheet1, 3, rowIndex, langauge.TradosCode);
excelDocument.SetCellValue(spreadsheet, worksheet1, 4, rowIndex, langauge.MTCode);
excelDocument.SetCellValue(spreadsheet, worksheet1, 5, rowIndex, langauge.MTCodeLocale);
}
// set the column widths
excelDocument.SetColumnWidth(worksheet1, 1, 30);
excelDocument.SetColumnWidth(worksheet1, 2, 30);
excelDocument.SetColumnWidth(worksheet1, 3, 20);
excelDocument.SetColumnWidth(worksheet1, 4, 20);
excelDocument.SetColumnWidth(worksheet1, 5, 20);
// add a table filter
var autoFilter = new AutoFilter {
Reference = "A1:E" + mappedLanguages.Count + 1
};
worksheet1.Append(autoFilter);
worksheet1.Save();
spreadsheet.Close();
return(true);
}
/// <summary>
/// Serves excel via the OpenXML library for the second Grid in the Index Page
/// </summary>
public FileResult ExportWithOpenXML([DataSourceRequest] DataSourceRequest request)
{
using (var northwind = new SampleEntities())
{
//Get the data representing the current grid state - page, sort and filter
var products = new List <Product>(northwind.Products.ToDataSourceResult(request).Data as IEnumerable <Product>);
using (var stream = new MemoryStream())
{
/* Create the worksheet. */
SpreadsheetDocument spreadsheet = Excel.CreateWorkbook(stream);
Excel.AddBasicStyles(spreadsheet);
Excel.AddAdditionalStyles(spreadsheet);
Excel.AddWorksheet(spreadsheet, "Products");
Worksheet worksheet = spreadsheet.WorkbookPart.WorksheetParts.First().Worksheet;
//create columns and set their widths
Excel.SetColumnHeadingValue(spreadsheet, worksheet, 1, "Product ID", false, false);
Excel.SetColumnWidth(worksheet, 1, 50);
Excel.SetColumnHeadingValue(spreadsheet, worksheet, 2, "Product Name", false, false);
Excel.SetColumnWidth(worksheet, 2, 50);
Excel.SetColumnHeadingValue(spreadsheet, worksheet, 3, "Unit Price", false, false);
Excel.SetColumnWidth(worksheet, 3, 50);
Excel.SetColumnHeadingValue(spreadsheet, worksheet, 4, "Quantity Per Unit", false, false);
Excel.SetColumnWidth(worksheet, 4, 50);
/* Add the data to the worksheet. */
// For each row of data...
for (int idx = 0; idx < products.Count; idx++)
{
// Set the field values in the spreadsheet for the current row.
Excel.SetCellValue(spreadsheet, worksheet, 1, (uint)idx + 2, products[idx].ProductID.ToString(), false, false);
Excel.SetCellValue(spreadsheet, worksheet, 2, (uint)idx + 2, products[idx].ProductName, false, false);
Excel.SetCellValue(spreadsheet, worksheet, 3, (uint)idx + 2, products[idx].UnitPrice.ToString(), false, false);
Excel.SetCellValue(spreadsheet, worksheet, 4, (uint)idx + 2, products[idx].QuantityPerUnit, false, false);
}
/* Save the worksheet and store it in Session using the spreadsheet title. */
worksheet.Save();
spreadsheet.Close();
return(File(stream.ToArray(), //The binary data of the XLS file
"application/vnd.ms-excel", //MIME type of Excel files
"GridExcelExport.xlsx"));
}
}
}
public void create_excel_report(string fileName, List <Activity> activityList, List <XmlDocumentActivity> xmlDasList)
{
var projectsTmp = new List <Project>();
var companyProfilesTmp = new List <CompanyProfile>();
var projectActivitiesTmp = new List <Activity>();
var spreadsheet = Excel.CreateWorkbook(fileName);
if (spreadsheet == null)
{
return;
}
Excel.AddBasicStyles(spreadsheet);
Excel.AddSharedString(spreadsheet, "Shared string");
var worksheet1 = Excel.AddWorksheet(spreadsheet, "Project Activities");
var worksheet2 = Excel.AddWorksheet(spreadsheet, "Document Activities");
var worksheet4 = Excel.AddWorksheet(spreadsheet, "Quality Metrics");
var worksheet3 = Excel.AddWorksheet(spreadsheet, "KeyStroke Data");
#region | Project Activities |
Excel.SetCellValue(spreadsheet, worksheet1, 1, 1, "Client Name", false, false);
Excel.SetCellValue(spreadsheet, worksheet1, 2, 1, "Project Name", false, false);
Excel.SetCellValue(spreadsheet, worksheet1, 3, 1, "Status (Project)", false, false);
Excel.SetCellValue(spreadsheet, worksheet1, 4, 1, "Activity ID", false, false);
Excel.SetCellValue(spreadsheet, worksheet1, 5, 1, "Activity Name", false, false);
Excel.SetCellValue(spreadsheet, worksheet1, 6, 1, "Description", false, false);
Excel.SetCellValue(spreadsheet, worksheet1, 7, 1, "Status (Activity)", false, false);
Excel.SetCellValue(spreadsheet, worksheet1, 8, 1, "Billable", false, false);
Excel.SetCellValue(spreadsheet, worksheet1, 9, 1, "Date Start", false, false);
Excel.SetCellValue(spreadsheet, worksheet1, 10, 1, "Date End", false, false);
Excel.SetCellValue(spreadsheet, worksheet1, 11, 1, "Documents", false, false);
Excel.SetCellValue(spreadsheet, worksheet1, 12, 1, "Language Rate Total", false, false);
Excel.SetCellValue(spreadsheet, worksheet1, 13, 1, "Hourly Rate Total", false, false);
Excel.SetCellValue(spreadsheet, worksheet1, 14, 1, "Activity Total", false, false);
Excel.SetCellValue(spreadsheet, worksheet1, 15, 1, "Currency", false, false);
for (var i = 0; i < activityList.Count; i++)
{
var uIndex = Convert.ToUInt32(i + 2);
var activity = activityList[i];
Project activityProject;
if (projectsTmp.Exists(a => a.Id == activity.ProjectId))
{
activityProject = projectsTmp.Find(a => a.Id == activity.ProjectId);
}
else
{
activityProject = Helper.GetProjectFromId(activity.ProjectId);
projectsTmp.Add(activityProject);
}
CompanyProfile activityCompanyProfile;
if (companyProfilesTmp.Exists(a => a.Id == activity.CompanyProfileId))
{
activityCompanyProfile = companyProfilesTmp.Find(a => a.Id == activity.CompanyProfileId);
}
else
{
activityCompanyProfile = Helper.GetClientFromId(activity.CompanyProfileId);
if (activityCompanyProfile != null)
{
companyProfilesTmp.Add(activityCompanyProfile);
}
}
var pemTotal = activity.LanguageRateChecked ? (decimal)activity.DocumentActivityRates.LanguageRateTotal : 0;
var hrTotal = activity.HourlyRateChecked ? (decimal)activity.DocumentActivityRates.HourlyRateTotal : 0;
var total = decimal.Add(pemTotal, hrTotal);
Excel.SetCellValue(spreadsheet, worksheet1, 1, uIndex, activityCompanyProfile != null ? activityCompanyProfile.Name : string.Empty, false);
Excel.SetCellValue(spreadsheet, worksheet1, 2, uIndex, activityProject.Name, false);
Excel.SetCellValue(spreadsheet, worksheet1, 3, uIndex, activityProject.ProjectStatus, false);
Excel.SetCellValue(spreadsheet, worksheet1, 4, uIndex, activity.Id.ToString(), false);
Excel.SetCellValue(spreadsheet, worksheet1, 5, uIndex, activity.Name, false);
Excel.SetCellValue(spreadsheet, worksheet1, 6, uIndex, activity.Description, false);
Excel.SetCellValue(spreadsheet, worksheet1, 7, uIndex, activity.ActivityStatus.ToString(), false);
Excel.SetCellValue(spreadsheet, worksheet1, 8, uIndex, activity.Billable, null);
if (activity.Started != null)
{
Excel.SetCellValue(spreadsheet, worksheet1, 9, uIndex, Helper.GetStringFromDateTime(activity.Started.Value).Replace("T", " "), false, false);
}
if (activity.Stopped != null)
{
Excel.SetCellValue(spreadsheet, worksheet1, 10, uIndex, Helper.GetStringFromDateTime(activity.Stopped.Value).Replace("T", " "), false, false);
}
Excel.SetCellValue(spreadsheet, worksheet1, 11, uIndex, activity.Activities.Count, null);
Excel.SetCellValue(spreadsheet, worksheet1, 12, uIndex, pemTotal, null);
Excel.SetCellValue(spreadsheet, worksheet1, 13, uIndex, hrTotal, null);
Excel.SetCellValue(spreadsheet, worksheet1, 14, uIndex, total, null);
Excel.SetCellValue(spreadsheet, worksheet1, 15, uIndex, activity.DocumentActivityRates.HourlyRateCurrency, false);
//FormatCode = "yyyy-MM-dd hh:mm:ss",
}
Excel.SetColumnWidth(worksheet1, 1, 20);
Excel.SetColumnWidth(worksheet1, 2, 20);
Excel.SetColumnWidth(worksheet1, 3, 20);
Excel.SetColumnWidth(worksheet1, 5, 30);
Excel.SetColumnWidth(worksheet1, 6, 15);
Excel.SetColumnWidth(worksheet1, 7, 13);
Excel.SetColumnWidth(worksheet1, 8, 12);
Excel.SetColumnWidth(worksheet1, 9, 25);
Excel.SetColumnWidth(worksheet1, 10, 25);
Excel.SetColumnWidth(worksheet1, 11, 12);
Excel.SetColumnWidth(worksheet1, 12, 12);
Excel.SetColumnWidth(worksheet1, 13, 12);
Excel.SetColumnWidth(worksheet1, 14, 12);
Excel.SetColumnWidth(worksheet1, 15, 12);
var autoFilter1 = new AutoFilter {
Reference = "A1:O" + activityList.Count + 1
};
worksheet1.Append(autoFilter1);
#endregion
worksheet1.Save();
#region | Document Activities |
Excel.SetCellValue(spreadsheet, worksheet2, 1, 1, "Project Name", false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 2, 1, "Activity ID", false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 3, 1, "Activity Name", false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 4, 1, "Document Name", false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 5, 1, "Paragraph ID", false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 6, 1, "Segment ID", false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 7, 1, "Source Language", false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 8, 1, "Target Language", false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 9, 1, "Original Confirmation Level", false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 10, 1, "Original Translation Status", false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 11, 1, "Original Origin System", false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 12, 1, "Original Origin Type", false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 13, 1, "Updated Confirmation Level", false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 14, 1, "Updated Translation Status", false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 15, 1, "Updated Origin System", false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 16, 1, "Updated Origin Type", false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 17, 1, "Source (Text)", false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 18, 1, "Target (Text)", false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 19, 1, "Updated (Text)", false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 20, 1, "Start Date", false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 21, 1, "Stop Date", false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 22, 1, "Active Seconds", false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 23, 1, "Active Milliseconds", false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 24, 1, "Word Count", false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 25, 1, "Edit Distance", false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 26, 1, "Edit Distance Relative", false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 27, 1, "PEM %", false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 28, 1, "Comments", false, false);
for (var i = 0; i < xmlDasList.Count; i++)
{
var uIndex = Convert.ToUInt32(i + 2);
var activity = xmlDasList[i];
Project activityProject;
if (projectsTmp.Exists(a => a.Id == activity.ProjectId))
{
activityProject = projectsTmp.Find(a => a.Id == activity.ProjectId);
}
else
{
activityProject = Helper.GetProjectFromId(activity.ProjectId);
projectsTmp.Add(activityProject);
}
Activity projectActivity;
if (activityProject.Activities.Exists(a => a.Id == activity.ActivityId))
{
projectActivity = activityProject.Activities.Find(a => a.Id == activity.ActivityId);
}
else
{
projectActivity = activityProject.Activities.Find(a => a.Id == activity.ActivityId);
projectActivitiesTmp.Add(projectActivity);
}
Excel.SetCellValue(spreadsheet, worksheet2, 1, uIndex, activityProject.Name, false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 2, uIndex, activity.ActivityId.ToString(), false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 3, uIndex, projectActivity.Name, false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 4, uIndex, activity.DocumentName, false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 5, uIndex, activity.ParagraphId, false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 6, uIndex, activity.SegmentId, false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 7, uIndex, activity.SourceLang, false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 8, uIndex, activity.TargetLang, false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 9, uIndex, activity.OriginalConfirmationLevel, false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 10, uIndex, activity.OriginalTranslationStatus, false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 11, uIndex, activity.OriginalOriginSystem, false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 12, uIndex, activity.OriginalOriginType, false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 13, uIndex, activity.UpdatedConfirmationLevel, false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 14, uIndex, activity.UpdatedTranslationStatus, false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 15, uIndex, activity.UpdatedOriginSystem, false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 16, uIndex, activity.UpdatedOriginType, false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 17, uIndex, activity.SourceText, false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 18, uIndex, activity.TargetText, false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 19, uIndex, activity.UpdatedText, false, false);
if (activity.StartDate != null)
{
Excel.SetCellValue(spreadsheet, worksheet2, 20, uIndex, Helper.GetStringFromDateTimeMilli(activity.StartDate.Value).Replace("T", " "), false, false);
}
if (activity.StopDate != null)
{
Excel.SetCellValue(spreadsheet, worksheet2, 21, uIndex, Helper.GetStringFromDateTimeMilli(activity.StopDate.Value).Replace("T", " "), false, false);
}
Excel.SetCellValue(spreadsheet, worksheet2, 22, uIndex, activity.TotalSeconds, null);
Excel.SetCellValue(spreadsheet, worksheet2, 23, uIndex, activity.TotalMiliseconds, null);
Excel.SetCellValue(spreadsheet, worksheet2, 24, uIndex, Convert.ToInt32(activity.WordsSource), null);
Excel.SetCellValue(spreadsheet, worksheet2, 25, uIndex, activity.EditDistance, null);
Excel.SetCellValue(spreadsheet, worksheet2, 26, uIndex, activity.EditDistanceRelative, null);
Excel.SetCellValue(spreadsheet, worksheet2, 27, uIndex, activity.PemPercentage + "%", false, false);
Excel.SetCellValue(spreadsheet, worksheet2, 28, uIndex, activity.CommentsStr, false, false);
}
Excel.SetColumnWidth(worksheet2, 13, 30);
Excel.SetColumnWidth(worksheet2, 14, 30);
Excel.SetColumnWidth(worksheet2, 15, 30);
Excel.SetColumnWidth(worksheet2, 16, 23);
Excel.SetColumnWidth(worksheet2, 17, 30);
Excel.SetColumnWidth(worksheet2, 18, 30);
Excel.SetColumnWidth(worksheet2, 19, 30);
Excel.SetColumnWidth(worksheet2, 20, 23);
Excel.SetColumnWidth(worksheet2, 21, 23);
Excel.SetColumnWidth(worksheet2, 28, 23);
var autoFilter2 = new AutoFilter {
Reference = "A1:AB" + xmlDasList.Count + 1
};
worksheet2.Append(autoFilter2);
#endregion
worksheet2.Save();
#region | Quality Metrics |
Excel.SetCellValue(spreadsheet, worksheet4, 1, 1, "Activity ID", false, false);
Excel.SetCellValue(spreadsheet, worksheet4, 2, 1, "Activity Name", false, false);
Excel.SetCellValue(spreadsheet, worksheet4, 3, 1, "Document ID", false, false);
Excel.SetCellValue(spreadsheet, worksheet4, 4, 1, "Document Name", false, false);
Excel.SetCellValue(spreadsheet, worksheet4, 5, 1, "Record ID", false, false);
Excel.SetCellValue(spreadsheet, worksheet4, 6, 1, "Paragraph ID", false, false);
Excel.SetCellValue(spreadsheet, worksheet4, 7, 1, "Segment ID", false, false);
Excel.SetCellValue(spreadsheet, worksheet4, 8, 1, "QM GUID", false, false);
Excel.SetCellValue(spreadsheet, worksheet4, 9, 1, "QM ID", false, false);
Excel.SetCellValue(spreadsheet, worksheet4, 10, 1, "QM Name", false, false);
Excel.SetCellValue(spreadsheet, worksheet4, 11, 1, "QM Status", false, false);
Excel.SetCellValue(spreadsheet, worksheet4, 12, 1, "QM Severity", false, false);
Excel.SetCellValue(spreadsheet, worksheet4, 13, 1, "QM Weight", false, false);
Excel.SetCellValue(spreadsheet, worksheet4, 14, 1, "QM Content", false, false);
Excel.SetCellValue(spreadsheet, worksheet4, 15, 1, "QM Comment", false, false);
Excel.SetCellValue(spreadsheet, worksheet4, 16, 1, "QM Created", false, false);
Excel.SetCellValue(spreadsheet, worksheet4, 17, 1, "QM Modified", false, false);
Excel.SetCellValue(spreadsheet, worksheet4, 18, 1, "QM User Name", false, false);
var index = 1;
foreach (var activity in xmlDasList)
{
Project activityProject = null;
var activity1 = activity;
if (projectsTmp.Exists(a => a.Id == activity1.ProjectId))
{
activityProject = projectsTmp.Find(a => a.Id == activity.ProjectId);
}
else
{
activityProject = Helper.GetProjectFromId(activity.ProjectId);
projectsTmp.Add(activityProject);
}
Activity projectActivity = null;
if (activityProject.Activities.Exists(a => a.Id == activity.ActivityId))
{
projectActivity = activityProject.Activities.Find(a => a.Id == activity.ActivityId);
}
else
{
projectActivity = activityProject.Activities.Find(a => a.Id == activity.ActivityId);
projectActivitiesTmp.Add(projectActivity);
}
if (activity.QualityMetrics == null)
{
continue;
}
foreach (var t in activity.QualityMetrics)
{
index += 1;
var uIndex = Convert.ToUInt32(index);
var qm = t;
Excel.SetCellValue(spreadsheet, worksheet4, 1, uIndex, activity.ActivityId.ToString(), false, false);
Excel.SetCellValue(spreadsheet, worksheet4, 2, uIndex, projectActivity != null ? projectActivity.Name : string.Empty, false, false);
Excel.SetCellValue(spreadsheet, worksheet4, 3, uIndex, activity.DocumentId, false, false);
Excel.SetCellValue(spreadsheet, worksheet4, 4, uIndex, activity.DocumentName, false, false);
Excel.SetCellValue(spreadsheet, worksheet4, 5, uIndex, activity.RecordId, false, false);
Excel.SetCellValue(spreadsheet, worksheet4, 6, uIndex, activity.ParagraphId, false, false);
Excel.SetCellValue(spreadsheet, worksheet4, 7, uIndex, activity.SegmentId, false, false);
if (qm.Name.Trim() != string.Empty)
{
Excel.SetCellValue(spreadsheet, worksheet4, 8, uIndex, qm.Guid, false, false);
Excel.SetCellValue(spreadsheet, worksheet4, 9, uIndex, qm.Id, false, false);
Excel.SetCellValue(spreadsheet, worksheet4, 10, uIndex, qm.Name, false, false);
Excel.SetCellValue(spreadsheet, worksheet4, 11, uIndex, qm.Status.ToString(), false, false);
Excel.SetCellValue(spreadsheet, worksheet4, 12, uIndex, qm.SeverityName, false, false);
Excel.SetCellValue(spreadsheet, worksheet4, 13, uIndex, qm.SeverityValue.ToString(), false, false);
Excel.SetCellValue(spreadsheet, worksheet4, 14, uIndex, qm.Content, false, false);
Excel.SetCellValue(spreadsheet, worksheet4, 15, uIndex, qm.Comment, false, false);
if (qm.Created != null)
{
Excel.SetCellValue(spreadsheet, worksheet4, 16, uIndex, Helper.GetStringFromDateTimeMilli(qm.Created.Value).Replace("T", " "), false, false);
}
if (qm.Modified != null)
{
Excel.SetCellValue(spreadsheet, worksheet4, 17, uIndex, Helper.GetStringFromDateTimeMilli(qm.Modified.Value).Replace("T", " "), false, false);
}
Excel.SetCellValue(spreadsheet, worksheet4, 18, uIndex, qm.UserName, false, false);
}
else
{
Excel.SetCellValue(spreadsheet, worksheet4, 8, uIndex, string.Empty, false, false);
Excel.SetCellValue(spreadsheet, worksheet4, 9, uIndex, string.Empty, false, false);
Excel.SetCellValue(spreadsheet, worksheet4, 10, uIndex, string.Empty, false, false);
Excel.SetCellValue(spreadsheet, worksheet4, 11, uIndex, string.Empty, false, false);
Excel.SetCellValue(spreadsheet, worksheet4, 12, uIndex, string.Empty, false, false);
Excel.SetCellValue(spreadsheet, worksheet4, 13, uIndex, string.Empty, false, false);
Excel.SetCellValue(spreadsheet, worksheet4, 14, uIndex, string.Empty, false, false);
Excel.SetCellValue(spreadsheet, worksheet4, 15, uIndex, string.Empty, false, false);
Excel.SetCellValue(spreadsheet, worksheet4, 16, uIndex, string.Empty, false, false);
Excel.SetCellValue(spreadsheet, worksheet4, 17, uIndex, string.Empty, false, false);
Excel.SetCellValue(spreadsheet, worksheet4, 18, uIndex, string.Empty, false, false);
}
}
}
Excel.SetColumnWidth(worksheet2, 9, 23);
Excel.SetColumnWidth(worksheet2, 11, 23);
Excel.SetColumnWidth(worksheet2, 13, 23);
Excel.SetColumnWidth(worksheet2, 15, 23);
Excel.SetColumnWidth(worksheet2, 16, 23);
Excel.SetColumnWidth(worksheet2, 17, 23);
var autoFilter4 = new AutoFilter {
Reference = "A1:R" + xmlDasList.Count + 1
};
worksheet4.Append(autoFilter4);
#endregion
worksheet4.Save();
#region | Keystroke data |
Excel.SetCellValue(spreadsheet, worksheet3, 1, 1, "Activity ID", false, false);
Excel.SetCellValue(spreadsheet, worksheet3, 2, 1, "Activity Name", false, false);
Excel.SetCellValue(spreadsheet, worksheet3, 3, 1, "Document ID", false, false);
Excel.SetCellValue(spreadsheet, worksheet3, 4, 1, "Document Name", false, false);
Excel.SetCellValue(spreadsheet, worksheet3, 5, 1, "Record ID", false, false);
Excel.SetCellValue(spreadsheet, worksheet3, 6, 1, "Paragraph ID", false, false);
Excel.SetCellValue(spreadsheet, worksheet3, 7, 1, "Segment ID", false, false);
Excel.SetCellValue(spreadsheet, worksheet3, 8, 1, "Created", false, false);
Excel.SetCellValue(spreadsheet, worksheet3, 9, 1, "SHIFT", false, false);
Excel.SetCellValue(spreadsheet, worksheet3, 10, 1, "ALT", false, false);
Excel.SetCellValue(spreadsheet, worksheet3, 11, 1, "CTRL", false, false);
Excel.SetCellValue(spreadsheet, worksheet3, 12, 1, "Text", false, false);
Excel.SetCellValue(spreadsheet, worksheet3, 13, 1, "Key", false, false);
Excel.SetCellValue(spreadsheet, worksheet3, 14, 1, "Selection", false, false);
Excel.SetCellValue(spreadsheet, worksheet3, 15, 1, "Origin System", false, false);
Excel.SetCellValue(spreadsheet, worksheet3, 16, 1, "Origin Type", false, false);
Excel.SetCellValue(spreadsheet, worksheet3, 17, 1, "Match", false, false);
index = 1;
foreach (var activity in xmlDasList)
{
Project activityProject = null;
var activity1 = activity;
if (projectsTmp.Exists(a => a.Id == activity1.ProjectId))
{
activityProject = projectsTmp.Find(a => a.Id == activity.ProjectId);
}
else
{
activityProject = Helper.GetProjectFromId(activity.ProjectId);
projectsTmp.Add(activityProject);
}
Activity projectActivity = null;
if (activityProject.Activities.Exists(a => a.Id == activity.ActivityId))
{
projectActivity = activityProject.Activities.Find(a => a.Id == activity.ActivityId);
}
else
{
projectActivity = activityProject.Activities.Find(a => a.Id == activity.ActivityId);
projectActivitiesTmp.Add(projectActivity);
}
if (activity.KeyStrokes == null)
{
continue;
}
foreach (var t in activity.KeyStrokes)
{
index += 1;
var uIndex = Convert.ToUInt32(index);
var ks = t;
Excel.SetCellValue(spreadsheet, worksheet3, 1, uIndex, activity.ActivityId.ToString(), false, false);
Excel.SetCellValue(spreadsheet, worksheet3, 2, uIndex, projectActivity != null ? projectActivity.Name : string.Empty, false, false);
Excel.SetCellValue(spreadsheet, worksheet3, 3, uIndex, activity.DocumentId, false, false);
Excel.SetCellValue(spreadsheet, worksheet3, 4, uIndex, activity.DocumentName, false, false);
Excel.SetCellValue(spreadsheet, worksheet3, 5, uIndex, activity.RecordId, false, false);
Excel.SetCellValue(spreadsheet, worksheet3, 6, uIndex, activity.ParagraphId, false, false);
Excel.SetCellValue(spreadsheet, worksheet3, 7, uIndex, activity.SegmentId, false, false);
if (ks.Created != null)
{
Excel.SetCellValue(spreadsheet, worksheet3, 8, uIndex, Helper.GetStringFromDateTimeMilli(ks.Created.Value).Replace("T", " "), false, false);
}
Excel.SetCellValue(spreadsheet, worksheet3, 9, uIndex, ks.Shift.ToString(), false, false);
Excel.SetCellValue(spreadsheet, worksheet3, 10, uIndex, ks.Alt.ToString(), false, false);
Excel.SetCellValue(spreadsheet, worksheet3, 11, uIndex, ks.Ctrl.ToString(), false, false);
Excel.SetCellValue(spreadsheet, worksheet3, 12, uIndex, ks.Text, false, false);
Excel.SetCellValue(spreadsheet, worksheet3, 13, uIndex, ks.Key, false, false);
Excel.SetCellValue(spreadsheet, worksheet3, 14, uIndex, ks.Selection, false, false);
Excel.SetCellValue(spreadsheet, worksheet3, 15, uIndex, ks.OriginType, false, false);
Excel.SetCellValue(spreadsheet, worksheet3, 16, uIndex, ks.OriginSystem, false, false);
Excel.SetCellValue(spreadsheet, worksheet3, 17, uIndex, ks.Match, false, false);
}
}
Excel.SetColumnWidth(worksheet3, 8, 23);
Excel.SetColumnWidth(worksheet3, 12, 25);
Excel.SetColumnWidth(worksheet3, 13, 15);
Excel.SetColumnWidth(worksheet3, 14, 15);
Excel.SetColumnWidth(worksheet3, 15, 15);
Excel.SetColumnWidth(worksheet3, 16, 15);
var autoFilter3 = new AutoFilter {
Reference = "A1:Q" + xmlDasList.Count + 1
};
worksheet3.Append(autoFilter3);
#endregion
worksheet3.Save();
spreadsheet.Close();
}
/// <summary>
/// Create the Excel spreadsheet.
/// </summary>
/// <param name="model">Definition of the columns for the spreadsheet.</param>
/// <param name="data">Grid data.</param>
/// <param name="title">Title of the spreadsheet.</param>
/// <returns></returns>
public JsonResult ExportToExcel(string model, string data, string title)
{
using (System.IO.MemoryStream stream = new System.IO.MemoryStream())
{
/* Create the worksheet. */
SpreadsheetDocument spreadsheet = Excel.CreateWorkbook(stream);
Excel.AddBasicStyles(spreadsheet);
Excel.AddAdditionalStyles(spreadsheet);
Excel.AddWorksheet(spreadsheet, title);
Worksheet worksheet = spreadsheet.WorkbookPart.WorksheetParts.First().Worksheet;
/* Get the information needed for the worksheet */
var modelObject = JsonConvert.DeserializeObject <dynamic>(model);
var dataObject = JsonConvert.DeserializeObject <dynamic>(data);
/* Add the column titles to the worksheet. */
// For each column...
for (int mdx = 0; mdx < modelObject.Count; mdx++)
{
// If the column has a title, use it. Otherwise, use the field name.
Excel.SetColumnHeadingValue(spreadsheet, worksheet, Convert.ToUInt32(mdx + 1),
modelObject[mdx].title == null ? modelObject[mdx].field.ToString() : modelObject[mdx].title.ToString(),
false, false);
// Is there are column width defined?
Excel.SetColumnWidth(worksheet, mdx + 1, modelObject[mdx].width != null
? Convert.ToInt32(modelObject[mdx].width.ToString()) / 4
: 25);
}
/* Add the data to the worksheet. */
// For each row of data...
for (int idx = 0; idx < dataObject.Count; idx++)
{
// For each column...
for (int mdx = 0; mdx < modelObject.Count; mdx++)
{
// Set the field value in the spreadsheet for the current row and column.
Excel.SetCellValue(spreadsheet, worksheet, Convert.ToUInt32(mdx + 1), Convert.ToUInt32(idx + 2),
dataObject[idx][modelObject[mdx].field.ToString()].ToString(),
false, false);
}
}
/* Save the worksheet and store it in Session using the spreadsheet title. */
worksheet.Save();
spreadsheet.Close();
byte[] file = stream.ToArray();
Session[title] = file;
}
return(Json(new { success = true }, JsonRequestBehavior.AllowGet));
}
public ExportToExcelResponse ExportToExcel(ExportToExcelRequest request)
{
try
{
using (var stream = new MemoryStream())
{
// Create the worksheet.
var spreadsheet = Excel.CreateWorkbook(stream);
Excel.AddBasicStyles(spreadsheet);
Excel.AddAdditionalStyles(spreadsheet);
Excel.AddWorksheet(spreadsheet, request.Title);
var worksheet = spreadsheet.WorkbookPart.WorksheetParts.First().Worksheet;
var columns = request.Columns;
var data = request.Data;
/* Add the column titles to the worksheet. */
for (var mdx = 0; mdx < columns.Count; mdx++)
{
// If the column has a title, use it. Otherwise, use the field name.
Excel.SetColumnHeadingValue(spreadsheet, worksheet, Convert.ToUInt32(mdx + 1),
columns[mdx].Title ?? columns[mdx].Field,
false, false);
// Is there are column width defined?
Excel.SetColumnWidth(worksheet, mdx + 1,
columns[mdx].Width != null
? Convert.ToInt32(columns[mdx].Width.Replace("px", string.Empty)) / 4
: 25);
}
/* Add the data to the worksheet. */
// For each row of data...
for (var idx = 0; idx < data.Count; idx++)
{
// For each column...
for (var mdx = 0; mdx < columns.Count; mdx++)
{
// Set the field value in the spreadsheet for the current row and column.
Excel.SetCellValue(spreadsheet, worksheet, Convert.ToUInt32(mdx + 1), Convert.ToUInt32(idx + 2),
data[idx][columns[mdx].Field],
false, false);
}
}
// save the changed
worksheet.Save();
// clean up excel
spreadsheet.Close();
// generate a unique id for the response
var key = Guid.NewGuid().ToString();
// Cache the response (60 seconds should be long enough)
Cache.AddOrUpdate(
new Tuple <string, byte[]>(request.Title, stream.ToArray()), // package the title and data into a tuple.
key, ExportExcelRegion, 1);
// return the response with the url to get the document
return(new ExportToExcelResponse()
{
Expires = DateTime.Now.AddMinutes(1),
Url = Request.RequestUri + "?key=" + key
});
}
}
catch (Exception ex)
{
LogProvider.Get <QueuesController>().Error(ex);
throw;
}
}
private void export(object sender, EventArgs e)
{
if (!_dependencyObjectList.Any())
{
return;
}
try
{
string workbookName = TempFolder.GetPath(null, "xlsx", true);
var spreadsheet = Excel.CreateWorkbook(workbookName);
Excel.AddBasicStyles(spreadsheet);
Excel.AddWorksheet(spreadsheet, "Sheet1");
var worksheet = spreadsheet.WorkbookPart.WorksheetParts.First().Worksheet;
// Excel file ready for the data
//
uint maxDepth = (uint)_dependencyObjectList.Max(o => o.HierarchyLevel) + 1;
// Columns
for (uint i = 1; i <= maxDepth; i++)
{
string text = string.Format("Level {0}", /*this.treeListView1.Columns[0].Text,*/ i);
Excel.SetCellValue(spreadsheet, worksheet, i, 1, text, false, false);
}
for (uint i = maxDepth + 1; i <= (uint)this.treeListView1.Columns.Count + maxDepth - 1; i++)
{
string text = this.treeListView1.Columns[(int)i - (int)maxDepth].Text;
Excel.SetCellValue(spreadsheet, worksheet, i, 1, text, false, false);
}
// data
uint row = 2;
foreach (object obj in this.treeListView1.Objects)
{
MySqlObject sqlObj = (MySqlObject)obj;
Excel.SetCellValue(spreadsheet, worksheet, (uint)sqlObj.HierarchyLevel + 1, row, sqlObj.ObjectName, false, false);
Excel.SetCellValue(spreadsheet, worksheet, (uint)maxDepth + 1, row, sqlObj.ObjectId, null, false);
Excel.SetCellValue(spreadsheet, worksheet, (uint)maxDepth + 2, row, sqlObj.ServerObjectName, false, false);
Excel.SetCellValue(spreadsheet, worksheet, (uint)maxDepth + 3, row, sqlObj.DatabaseName, false, false);
Excel.SetCellValue(spreadsheet, worksheet, (uint)maxDepth + 4, row, sqlObj.SchemaName, false, false);
Excel.SetCellValue(spreadsheet, worksheet, (uint)maxDepth + 5, row, sqlObj.ObjectType, false, false);
Excel.SetCellValue(spreadsheet, worksheet, (uint)maxDepth + 6, row, sqlObj.MaximumUnderlyingLevels, null, false);
Excel.SetCellValue(spreadsheet, worksheet, (uint)maxDepth + 7, row, sqlObj.IsSelfReferencing, false, false);
row++;
IEnumerable <MySqlObject> children = GetChildren(sqlObj);
foreach (MySqlObject child in children)
{
Excel.SetCellValue(spreadsheet, worksheet, (uint)child.HierarchyLevel + 1, row, child.ObjectName, false, false);
Excel.SetCellValue(spreadsheet, worksheet, (uint)maxDepth + 1, row, child.ObjectId, null, false);
Excel.SetCellValue(spreadsheet, worksheet, (uint)maxDepth + 2, row, child.ServerObjectName, false, false);
Excel.SetCellValue(spreadsheet, worksheet, (uint)maxDepth + 3, row, child.DatabaseName, false, false);
Excel.SetCellValue(spreadsheet, worksheet, (uint)maxDepth + 4, row, child.SchemaName, false, false);
Excel.SetCellValue(spreadsheet, worksheet, (uint)maxDepth + 5, row, child.ObjectType, false, false);
Excel.SetCellValue(spreadsheet, worksheet, (uint)maxDepth + 6, row, child.MaximumUnderlyingLevels, null, false);
Excel.SetCellValue(spreadsheet, worksheet, (uint)maxDepth + 7, row, child.IsSelfReferencing, false, false);
row++;
}
}
worksheet.Save();
spreadsheet.Close();
Process.Start(workbookName);
}
catch (Exception ex)
{
}
}
