private void UpdateWorksheetContent(WorksheetPart workSheetPart, List<ExcelSheet> templateRows, SharedStringTable sharedStrings, List<ProjectFile> projectFiles, Customer customer, User user)
{
IEnumerable<SheetData> sheets = workSheetPart.Worksheet.ChildElements.OfType<SheetData>();
foreach (var sheetData in sheets)
{
sheetData.RemoveAllChildren<Row>();
List<String> strings = sharedStrings.ChildElements.OfType<SharedStringItem>().Where(shItem => shItem.ChildElements.OfType<Text>().Any()).Select(shItem =>
{
var firstOrDefault = shItem.ChildElements.OfType<Text>().FirstOrDefault();
return firstOrDefault != null ? firstOrDefault.Text : String.Empty;
}).ToList();
//from this point is the content
int linesToAdd = 0;
var templateSheet = templateRows.FirstOrDefault(row => row.SheetData.Equals(sheetData));
if (templateSheet != null)
{
foreach (var projectFile in projectFiles)
{
GenerateWorksheetDataContent(sheetData, templateSheet.Rows, linesToAdd, new List<TokensProvider> {projectFile, customer, user}, strings);
linesToAdd += (templateSheet.Rows.Count + 5);
}
}
}
}
// Creates a SpreadsheetDocument.
public void CreatePackage(string filePath, List<ProjectFile> projectFiles, Customer customer, User user)
{
using(SpreadsheetDocument package = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
{
CreateParts(package, projectFiles);
}
}
public override void GenerateExcelData(List<ProjectFile> files, Customer customer, User user, String fileToSave, String template)
{
StandardLinesExcelHelper xmlHelper = new StandardLinesExcelHelper();
if (String.IsNullOrEmpty(template))
xmlHelper.CreatePackage(fileToSave, files, customer, user);
else
xmlHelper.UpdatePackage(template, fileToSave, files, customer, user);
}
// Updates a SpreadsheetDocument.
public void UpdatePackage(string templateFile, string filePath, List<ProjectFile> projectFiles, Customer customer, User user)
{
File.Copy(templateFile, filePath, true);
Thread.Sleep(100);
using (WordprocessingDocument package = WordprocessingDocument.Open(filePath, true, new OpenSettings() { AutoSave = true }))
{
UpdateParts(package, projectFiles, customer, user);
}
}
private void UpdateParts(SpreadsheetDocument document, Dictionary<WorksheetPart, List<ExcelSheet>> worksheetsWithRows, List<ProjectFile> projectFiles, Customer customer, User user)
{
var workSheets = document.WorkbookPart.WorksheetParts;
foreach (var workSheet in workSheets)
{
UpdateWorksheetContent(workSheet, worksheetsWithRows[workSheet], document.WorkbookPart.SharedStringTablePart.SharedStringTable, projectFiles, customer, user);
}
}
// Updates a SpreadsheetDocument.
public void UpdatePackage(string templateFile, string filePath, List<ProjectFile> projectFiles, Customer customer, User user)
{
File.Copy(templateFile, filePath, true);//.Replace("xlsx", "xltx"));
Thread.Sleep(100);
using (SpreadsheetDocument package = SpreadsheetDocument.Open(filePath, true, new OpenSettings {AutoSave = true}))
{
var worksheetsWithRows = package.WorkbookPart.WorksheetParts.ToDictionary(workSheet => workSheet, GetRowsForWorksheet);
UpdateParts(package, worksheetsWithRows, projectFiles, customer, user);
}
}
public virtual void GenerateClipboardData(List<ProjectFile> projectFiles, Customer customer, User user)
{
Encoding enc = Encoding.UTF8;
const string begin = "Version:0.9\r\nStartHTML:{0:000000}\r\nEndHTML:{1:000000}\r\nStartFragment:{2:000000}\r\nEndFragment:{3:000000}\r\n";
String htmlBegin = "<html>\r\n<head>\r\n"
+ "<meta http-equiv=\"Content-Type\""
+ " content=\"text/html; charset=" + enc.WebName + "\">\r\n"
+ "<title>HTML clipboard</title>\r\n</head>\r\n<body>\r\n"
+ "<!--StartFragment-->";
var sb = new StringBuilder();
sb.AppendLine(GenerateCustomerData(customer));
sb.AppendLine("<br>");
foreach (var porjectFile in projectFiles)
{
sb.AppendLine(porjectFile.FileName.Replace(".sdlxliff", ""));
sb.AppendLine(GenerateHeader(porjectFile));
foreach (var porjectProperty in porjectFile.ProjectProperties)
{
if (porjectProperty.StandardType != ExcludedType)
{
sb.AppendLine(GenerateLine(porjectProperty));
}
}
sb.AppendLine( GenerateTotal(porjectFile.ProjectProperties));
sb.AppendLine("</tbody></TABLE>");
sb.AppendLine("<br>");
}
sb.AppendLine(GenerateUserData(user));
String html = sb.ToString();
const string htmlEnd = "<!--EndFragment-->\r\n</body>\r\n</html>\r\n";
string beginSample = String.Format(begin, 0, 0, 0, 0);
int countBegin = enc.GetByteCount(beginSample);
int countHtmlBegin = enc.GetByteCount(htmlBegin);
int countHtml = enc.GetByteCount(html);
int countHtmlEnd = enc.GetByteCount(htmlEnd);
string htmlTotal = String.Format(
begin
, countBegin
, countBegin + countHtmlBegin + countHtml + countHtmlEnd
, countBegin + countHtmlBegin
, countBegin + countHtmlBegin + countHtml
) + htmlBegin + html + htmlEnd;
DataObject obj = new DataObject();
obj.SetData(DataFormats.Html, new System.IO.MemoryStream(
enc.GetBytes(htmlTotal)));
Clipboard.SetDataObject(obj, true);
}
private void UpdateParts(WordprocessingDocument document, List<ProjectFile> projectFiles, Customer customer,
User user)
{
try
{
OpenXmlElement root = document.MainDocumentPart.Document.Body;
UpdateElementsForRoot(projectFiles, customer, user, root);
}
catch {}
try
{
foreach (FooterPart footerPart in document.MainDocumentPart.FooterParts)
{
OpenXmlElement root = footerPart.Footer;
UpdateElementsForRoot(customer, user, root);
}
}
catch { }
try
{
foreach (HeaderPart headerPart in document.MainDocumentPart.HeaderParts)
{
OpenXmlElement root = headerPart.Header;
UpdateElementsForRoot(customer, user, root);
}
}
catch { }
#region comments part
//try
//{
// if (document.MainDocumentPart.WordprocessingCommentsPart == null) return;
// OpenXmlElement root = document.MainDocumentPart.WordprocessingCommentsPart.Comments;
// UpdateElementsForRoot(projectFiles, customer, user, root);
//}
//catch { }
#endregion
}
private void UpdateElements(OpenXmlElement root, List<OpenXmlElement> elements, Customer customer, User user)
{
UpdateSingleFile(root, elements, new List<TokensProvider>() { customer, user }, false);
}
private string GenerateUserData(User user)
{
if (user == null)
return String.Empty;
UIResources resources = new UIResources(Settings.GetSavedCulture());
var sb = new StringBuilder();
sb.AppendLine("<br>");
sb.AppendLine(resources.GeneratedBy);
if (!String.IsNullOrEmpty(user.Name))
{
sb.AppendLine(String.Format(" {1}: {0}", user.Name, resources.UserName));
sb.AppendLine("<br>");
}
if (!String.IsNullOrEmpty(user.Street))
{
sb.AppendLine(String.Format(" {1}: {0}", user.Street, resources.Street));
sb.AppendLine("<br>");
}
if (!String.IsNullOrEmpty(user.City))
{
sb.AppendLine(String.Format(" {1}: {0}", user.City, resources.City));
sb.AppendLine("<br>");
}
if (!String.IsNullOrEmpty(user.State))
{
sb.AppendLine(String.Format(" {1}: {0}", user.State, resources.State));
sb.AppendLine("<br>");
}
if (!String.IsNullOrEmpty(user.Zip))
{
sb.AppendLine(String.Format(" {1}: {0}", user.Zip, resources.Zip));
sb.AppendLine("<br>");
}
if (!String.IsNullOrEmpty(user.Country))
{
sb.AppendLine(String.Format(" {1}: {0}", user.Country, resources.Country));
sb.AppendLine("<br>");
}
if (!String.IsNullOrEmpty(user.Phone))
{
sb.AppendLine(String.Format(" {1}: {0}", user.Phone, resources.Phone));
sb.AppendLine("<br>");
}
if (!String.IsNullOrEmpty(user.Mobile))
{
sb.AppendLine(String.Format(" {1}: {0}", user.Mobile, resources.Mobile));
sb.AppendLine("<br>");
}
if (!String.IsNullOrEmpty(user.Email))
{
sb.AppendLine(String.Format(" {1}: {0}", user.Email, resources.Email));
sb.AppendLine("<br>");
}
if (!String.IsNullOrEmpty(user.Skype))
{
sb.AppendLine(String.Format(" {1}: {0}", user.Skype, resources.Skype));
sb.AppendLine("<br>");
}
if (!String.IsNullOrEmpty(user.WebAddress))
{
sb.AppendLine(String.Format(" {1}: {0}", user.WebAddress, resources.WebAddress));
sb.AppendLine("<br>");
}
if (!String.IsNullOrEmpty(user.Twitter))
{
sb.AppendLine(String.Format(" {1}: {0}", user.Twitter, resources.Twitter));
sb.AppendLine("<br>");
}
return sb.ToString();
}
public virtual void GenerateWordData(List<ProjectFile> files, Customer customer, User user, string fileToSave, string template)
{
WordHelperBase xmlHelper = new WordHelperBase();
xmlHelper.UpdatePackage(template, fileToSave, files, customer, user);
}
private void UpdateElementsForRoot(IEnumerable<ProjectFile> projectFiles, Customer customer, User user, OpenXmlElement root)
{
List<OpenXmlElement> elements = InitializeRoot(root);
UpdateElements(root, elements, projectFiles, customer, user);
}
private void btnGenerate_Click(object sender, EventArgs e)
{
if (!ckWord.Checked && !ckExcel.Checked && !ckClipboard.Checked)
return;
Settings.SaveRatesState(_rates.State);
ITemplateRates currentSelectedTemplate = _rates.CurrentRatesTemplate;
if (currentSelectedTemplate == null)
{
if (_rates == null || _rates.State == null)
currentSelectedTemplate = new SimpleWordTemplate();
else
{
currentSelectedTemplate = _rates.GetSimpleWordTemplateFromState();
}
}
Project project = new Project(cmbLanguagePair.SelectedValue.ToString(),
currentSelectedTemplate,
cmbReportType.SelectedIndex == 0 ? ReportType.Detailed : ReportType.Summary);
Customer customer = new Customer(
txtCustomerName.SDLText,
txtCustomerStreet.SDLText,
txtCustomerCity.SDLText,
txtCustomerState.SDLText,
txtCustomerZip.SDLText,
txtCustomerCountry.SDLText);
//Settings.SaveCurrentCustomer(customer);
bool emptyUser = String.IsNullOrEmpty(txtUserName.SDLText);
User user = new User()
{
Name = emptyUser ? String.Empty : txtUserName.SDLText,
Street = emptyUser ? String.Empty : txtUserStreet.SDLText,
City = emptyUser ? String.Empty : txtUserCity.SDLText,
State = emptyUser ? String.Empty : txtUserState.SDLText,
Zip = emptyUser ? String.Empty : txtUserZip.SDLText,
Country = emptyUser ? String.Empty : txtUserCountry.SDLText,
Phone = emptyUser ? String.Empty : txtUserPhone.SDLText,
Mobile = emptyUser ? String.Empty : txtUserMobile.SDLText,
Email = emptyUser ? String.Empty : txtUserEmail.SDLText,
Skype = emptyUser ? String.Empty : txtUserSkype.SDLText,
WebAddress = emptyUser ? String.Empty : txtUserWebAdress.SDLText,
Twitter = emptyUser ? String.Empty : txtUserTwitter.SDLText
};
Settings.SaveCurrentUser(user);
UIResources resources = new UIResources(ddlLanguages.SelectedValue.ToString());
if (ckClipboard.Checked)
{
project.GenerateClipboardData(customer, user);
MessageBox.Show(resources.QuoteGeneratedClipboard, resources.Information, MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
if (ckExcel.Checked)
{
saveFileDialog.FileName = String.Empty;
saveFileDialog.DefaultExt = "xlsx";
saveFileDialog.AddExtension = true;
saveFileDialog.Filter = @"Excel Document|*.xlsx";
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
String fileToSave = saveFileDialog.FileName;
String template = ddlExcel.SelectedValue.ToString();
project.GenerateExcelData(customer, user, fileToSave, template);
MessageBox.Show(resources.QuoteGeneratedExcel, resources.Information, MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
}
if (ckWord.Checked)
{
saveFileDialog.FileName = String.Empty;
saveFileDialog.DefaultExt = "docx";
saveFileDialog.AddExtension = true;
saveFileDialog.Filter = @"Word Document|*.docx";
saveFileDialog.SupportMultiDottedExtensions = false;
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
String fileToSave = saveFileDialog.FileName;
String template = ddlWord.SelectedValue.ToString();
project.GenerateWordData(customer, user, fileToSave, template);
MessageBox.Show(resources.QuoteGeneratedWord, resources.Information, MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
}
}
public void GenerateWordData(Customer customer, User user, string fileToSave, string template)
{
_template.GenerateWordData(Files, customer, user, fileToSave, template);
}
public void GenerateExcelData(Customer customer, User user, String fileToSave, String template)
{
_template.GenerateExcelData(Files, customer, user, fileToSave, template);
}
public void GenerateClipboardData(Customer customer, User user)
{
_template.GenerateClipboardData(Files, customer, user);
}
private void UpdateElementsForRoot(Customer customer, User user, OpenXmlElement root)
{
List<OpenXmlElement> elements = InitializeRoot(root, false);
UpdateElements(root, elements, customer, user);
}
private void StudioInQuote_FormClosed(object sender, FormClosedEventArgs e)
{
if (_rates != null && _rates.State != null)
Settings.SaveRatesState(_rates.State);
bool emptyUser = String.IsNullOrEmpty(txtUserName.SDLText);
User user = new User()
{
Name = emptyUser ? String.Empty : txtUserName.SDLText,
Street = emptyUser ? String.Empty : txtUserStreet.SDLText,
City = emptyUser ? String.Empty : txtUserCity.SDLText,
State = emptyUser ? String.Empty : txtUserState.SDLText,
Zip = emptyUser ? String.Empty : txtUserZip.SDLText,
Country = emptyUser ? String.Empty : txtUserCountry.SDLText,
Phone = emptyUser ? String.Empty : txtUserPhone.SDLText,
Mobile = emptyUser ? String.Empty : txtUserMobile.SDLText,
Email = emptyUser ? String.Empty : txtUserEmail.SDLText,
Skype = emptyUser ? String.Empty : txtUserSkype.SDLText,
WebAddress = emptyUser ? String.Empty : txtUserWebAdress.SDLText,
Twitter = emptyUser ? String.Empty : txtUserTwitter.SDLText
};
Settings.SaveCurrentUser(user);
}
private void UpdateElements(OpenXmlElement root, List<OpenXmlElement> elements, IEnumerable<ProjectFile> projectFiles, Customer customer, User user)
{
foreach (var projectFile in projectFiles)
{
UpdateSingleFile(root, elements, new List<TokensProvider>() {customer, user, projectFile});
#region empty lines
var emptyLineParagraph = new Paragraph() { RsidParagraphAddition = "0077013B", RsidRunAdditionDefault = "00684C81", ParagraphId = "5E06053C", TextId = "77777777" };
var emptyLineRun = new Run();
var emptyLineText = new Text() { Text = String.Empty };
emptyLineRun.Append(emptyLineText);
emptyLineParagraph.Append(emptyLineRun);
root.Append(emptyLineParagraph);
root.Append((OpenXmlElement)emptyLineParagraph.Clone());
#endregion
}
}
public virtual void GenerateExcelData(List<ProjectFile> files, Customer customer, User user, String fileToSave, String template)
{
}
