public static string ValidateUsername(IDbConnection connection, string username, Int32?existingUserId)
{
username = username.TrimToNull();
if (username == null)
{
throw DataValidation.RequiredError(fld.Username.Name, fld.Username.Title);
}
if (!IsValidUsername(username))
{
throw new ValidationError("InvalidUsername", "Username",
"Usernames should start with letters, only contain letters and numbers!");
}
var existing = GetUser(connection,
new Criteria(fld.Username) == username |
new Criteria(fld.Username) == username.Replace('I', 'İ'));
if (existing != null && existingUserId != existing.UserId)
{
throw new ValidationError("UniqueViolation", "Username",
"A user with same name exists. Please choose another!");
}
return(username);
}
private void txtFinalQty_KeyDown(object sender, KeyEventArgs e)
{
try
{
if (e.KeyCode == Keys.Enter)
{
if (DataValidation.IsNumericNumber(txtFinalQty.Text))
{
objBatchActivity.StopQty = Convert.ToInt32(txtFinalQty.Text);
if (objBatchActivity.MainActivity.MainActivityTitle == "Packing_Secondary")
{
objBatchActivity.BatchActStatus = BatchActivity.Status.SecondaryPacking_Finish;
}
else
{
objBatchActivity.BatchActStatus = BatchActivity.Status.Finished;
}
long x = objBatchActivity_DL.Update(objBatchActivity);
}
else
{
MessageBox.Show(this, "Please Enter a Valid Qty", "Wrong Attempt", MessageBoxButtons.OK);
}
this.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
private void btnBasicAdd_Click(object sender, EventArgs e)
{
FormulaBasicProduct objFormulaBasicProduct = new FormulaBasicProduct();
try
{
if (cmbBasicProducts.Items.Count > 0 && DataValidation.Is3DecimalPointNumber(txtBasicProductQty.Text))
{
objFormulaBasicProduct.BasicProduct = objBasicProductDL.Get(cmbBasicProducts.SelectedValue.ToString());
objFormulaBasicProduct.Formula = objFormula;
objFormulaBasicProduct.FormulaPart = cmbBasicPart.SelectedItem.ToString();
objFormulaBasicProduct.FormulaProductQty = Convert.ToDecimal(txtBasicProductQty.Text);
objFormulaBasicProductDL.Add(objFormulaBasicProduct, CurrentUser.UserEmp);
bindBasicProduct.DataSource = objFormulaBasicProductDL.GetDataView_FormulaID(objFormula.FormulaID);
bindItemList.DataSource = objFormulaDL.GetDataView_Items(objFormula.FormulaID);
}
else
{
MessageBox.Show(this, "Quantity should be a Numaric value", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch (FormatException fex)
{
MessageBox.Show(this, "Quantity should be a Numaric value", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (Exception ex)
{
MessageBox.Show(this, ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
objFormulaBasicProduct = null;
}
}
private void btnFinalize_Click(object sender, EventArgs e)
{
if (DataValidation.Is3DecimalPointNumber(txtDamagedQty.Text) && DataValidation.Is3DecimalPointNumber(txtBatchUsed.Text))
{
try
{
BatchPackingMR obj = new BatchPackingMR();
BatchActivity objBatchActivity = new BatchActivity();
BatchActivity_DL objBatchActivity_DL = new BatchActivity_DL(ConnectionStringClass.GetConnection());
obj = objBatchPackingMR_DL.Get(Convert.ToInt64(cmbMRNO.SelectedValue));
objBatchActivity = objBatchActivity_DL.GetByID(Convert.ToInt32(txtPackingBatch.Text));
if ((objBatchActivity.BatchActStatus == BatchActivity.Status.Finished) | (objBatchActivity.BatchActStatus == BatchActivity.Status.SecondaryPacking_Finish) | (objBatchActivity.BatchActStatus == BatchActivity.Status.Transferred))
{
MessageBox.Show("Activity is Already Finished");
}
else
{
if (obj.StartQty >= Convert.ToDecimal(txtDamagedQty.Text) + Convert.ToDecimal(txtBatchReturned.Text) + Convert.ToDecimal(txtBatchUsed.Text))
{
obj.BatchID = BatchID;
obj.BatchDamaged = Convert.ToDecimal(txtDamagedQty.Text);
obj.BatchReturned = Convert.ToDecimal(txtBatchReturned.Text);
obj.BatchUsed = Convert.ToDecimal(txtBatchUsed.Text);
obj.State = 1;
obj.BatchPackingID = Convert.ToInt64(txtPackingBatch.Text);
objBatchPackingMR_DL.Update(obj, MTN.Status.Initial, MTN.Type.Reject, MTN.ItemType.Material, CurrentUser.EmployeeID);
}
else
{
MessageBox.Show("Invalid Qty");
}
}
Load_MRList();
cmbMRNO_SelectedIndexChanged(sender, e);
txtBatchReturned.Text = "";
txtBatchUsed.Text = "";
txtDamagedQty.Text = "";
txtBatchUsed.Select();
loadSectionData();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
else
{
MessageBox.Show(this, "please enter valid quantity", "MRP System", MessageBoxButtons.OK);
}
}
/// <summary>
/// Incrementally resizes text until it fits within the mealplan
/// </summary>
private void SetMealPlanTextSize()
{
try
{
List <StackPanel> days = new List <StackPanel>();
double totalHeight = 0;
StackPanelMealPlan.UpdateLayout();
foreach (var item in StackPanelMealPlan.Children)
{
if (item.GetType() == typeof(StackPanel))
{
StackPanel sp = (StackPanel)item;
totalHeight += sp.ActualHeight;
days.Add(sp);
//Day
TextBlock tb = sp.Children[0] as TextBlock;
tb.FontSize = TBlockAdminMessage.FontSize + 5;
tb.VerticalAlignment = VerticalAlignment.Center;
tb.UpdateLayout();
//Meal
tb = sp.Children[1] as TextBlock;
tb.FontSize = TBlockAdminMessage.FontSize;
tb.VerticalAlignment = VerticalAlignment.Center;
tb.UpdateLayout();
//Width
while (tb.IsTextTrimmed && tb.FontSize > 10)
{
tb.FontSize--;
tb.UpdateLayout();
}
}
}
while (totalHeight > StackPanelMealPlan.MaxHeight)
{
totalHeight = 0;
//Height
foreach (StackPanel item in days)
{
TextBlock tbDay = (TextBlock)item.Children[0];
TextBlock tbMeal = (TextBlock)item.Children[1];
tbMeal.TextTrimming = TextTrimming.CharacterEllipsis;
if (tbMeal.FontSize > 10 && tbDay.FontSize > 10)
{
tbMeal.FontSize--;
tbDay.FontSize--;
tbMeal.UpdateLayout();
tbDay.UpdateLayout();
totalHeight += item.ActualHeight;
}
}
}
}
catch (Exception error)
{
DataValidation.SaveError(error.ToString());
}
}
private void ConfirmButton_Click(object sender, EventArgs e)
{
string dni = dniTextBox.Text;
string name = nameTextBox.Text;
string surname = lastnameTextBox.Text;
string district = districtTextBox.Text;
string email = emailTextBox.Text;
string phoneNumber = phoneTextBox.Text;
bool flagOK = true;
if (!DataValidation.ValidField(Constants.DniRegex, dni, ref flagOK))
{
label4.ForeColor = Color.Red;
}
if (!DataValidation.ValidField(Constants.NameRegex, name, ref flagOK))
{
label6.ForeColor = Color.Red;
}
if (!DataValidation.ValidField(Constants.SurnameRegex, surname, ref flagOK))
{
label8.ForeColor = Color.Red;
}
if (!DataValidation.ValidField(Constants.PlaceRegex, district, ref flagOK))
{
label5.ForeColor = Color.Red;
}
if (!DataValidation.ValidField(Constants.PhoneRegex, phoneNumber, ref flagOK))
{
label7.ForeColor = Color.Red;
}
if (!DataValidation.ValidField(Constants.EmailRegex, email, ref flagOK))
{
label10.ForeColor = Color.Red;
}
if (flagOK)
{
NaturalClient nc = new NaturalClient();
nc.Dni = dni; nc.Name = name; nc.Surname = surname; nc.Address = district; nc.Email = email; nc.PhoneNumber = phoneNumber;
try {
naturalClientBL.addNaturalClient(nc);
MessageBox.Show("El cliente ha sido registrado", "Registro de cliente nuevo", MessageBoxButtons.OK, MessageBoxIcon.Information);
dniTextBox.Clear();
nameTextBox.Clear();
lastnameTextBox.Clear();
districtTextBox.Clear();
emailTextBox.Clear();
phoneTextBox.Clear();
}
catch (Exception ex) {
MessageBox.Show("¡Existe un cliente con el mismo DNI!", "Error al añadir cliente", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show("Uno o más campos son incorrectos. Revise los campos en rojo.", "Error en registro", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public void DataValidation_ValidateComposite_InValidComposite(long number)
{
//Act
var actual = DataValidation.ValidateComposite(number);
// Assert
Assert.False(actual);
}
public void DataValidation_ValidateInput_LongOverFlow(long number)
{
// Act
var actual = DataValidation.ValidateInput((number + 1).ToString());
// Assert
Assert.False(actual);
}
public void DataValidation_ValidateInput_BadFormat()
{
// Act
var actual = DataValidation.ValidateInput("27.7");
// Assert
Assert.False(actual);
}
public void DataValidation_ValidateInput_NoEntry()
{
// Act
var actual = DataValidation.ValidateInput(null);
// Assert
Assert.False(actual);
}
private void ConfirmButton_Click(object sender, RoutedEventArgs e)
{
//votes(voteId INTEGER PRIMARY KEY, studentId INTEGER, upvotes BOOLEAN, description TEXT, creationDate INTEGER)
string lastname = _lastnameTextBox.Text;
string surname = _surnameTextBox.Text;
int studentId = Database.Get.Student.IdFromName(lastname, surname);
string description = _descriptionTextBox.Text;
DateTime currentDateTime = DateTime.Now;
int currentTimestamp = (int)new DateTimeOffset(currentDateTime).ToUnixTimeSeconds();
string valid = DataValidation.Vote(lastname, surname, description);
if (valid != "valid")
{
error.Foreground = new SolidColorBrush(Colors.Red);
error.Content = valid;
return;
}
error.Foreground = new SolidColorBrush(Colors.Green);
string upvoteType = upvote ? "Upvote" : "Downvote";
error.Content = $"{upvoteType} ajouté avec succès à {currentDateTime:hh:mm:ss}";
if (studentDisplay != null)
{
if (upvote)
{
int currentUpvote = int.Parse(studentDisplay.UpvotesCount);
currentUpvote++;
studentDisplay.UpvotesCount = currentUpvote.ToString();
}
else
{
int currentDownvote = int.Parse(studentDisplay.DownvotesCount);
currentDownvote++;
studentDisplay.DownvotesCount = currentDownvote.ToString();
}
}
else
{
Students.StudentDetails.VotesDetails.VoteDetails vote = new Students.StudentDetails.VotesDetails.VoteDetails()
{
Content = description,
ID = this.studentId,
Date = currentDateTime.ToString("g", GlobalVariable.culture)
};
if (upvote)
{
Students.StudentDetails.VotesDetails.UpvoteList.Add(vote);
}
else
{
Students.StudentDetails.VotesDetails.DownvoteList.Add(vote);
}
}
}
public override void Show()
{
Console.WriteLine("Welcome in ShadeMSG now u can create a free account");
string nick = Input("Nick: ");
Console.WriteLine("It will not be possible to change your password in the future");
string password = Input("Password: "******"Check nick lenght Min:" + minLenghtNick + " Max:" + maxLenghtNick);
}
if (!DataValidation.CheckLenght(password, minLenghtPassword, maxLenghtPassword))
{
validationErrors.Add("Check password lenght Min:" + minLenghtPassword + " Max:" + maxLenghtPassword);
}
if (DataValidation.RegexCheck(nick, "[^A-Za-z0-9_.]"))
{
validationErrors.Add("Nick have illegal characters, use only letters and numbers.");
}
if (DataValidation.RegexCheck(password, "[^A-Za-z0-9_.]"))
{
validationErrors.Add("Password have illegal characters, use only letters and numbers.");
}
if (validationErrors.Count > 0)
{
foreach (string error in validationErrors)
{
Console.WriteLine(error, Color.Tomato);
}
return;
}
Packet reg_packet = new Packet()
{
name = "register", args = new Argument[] {
new Argument("nick", nick),
new Argument("password", password)
}
};
Console.WriteLine("Creating Account..");
client.Send(reg_packet);
responseCatcher = new ResponseCatcher(reg_packet);
responseCatcher.PacketCaught += ResponseCatcher_PacketCaught;
while (!responseCatcher.packetWasCaught)
{
Thread.Sleep(100);
}
}
private void button1_Click_1(object sender, EventArgs e)
{
string companyName = textBox2.Text;
string address = textBox4.Text;
string phoneNumber = textBox5.Text;
string email = textBox6.Text;
bool flagOK = true;
if (!DataValidation.ValidField(Constants.CompanyNameRegex, companyName, ref flagOK))
{
namesCheckBox.ForeColor = Color.Red;
}
if (!DataValidation.ValidField(Constants.PlaceRegex, address, ref flagOK))
{
districtCheckBox.ForeColor = Color.Red;
}
if (!DataValidation.ValidField(Constants.PhoneRegex, phoneNumber, ref flagOK))
{
phoneCheckBox.ForeColor = Color.Red;
}
if (!DataValidation.ValidField(Constants.EmailRegex, email, ref flagOK))
{
emailCheckBox.ForeColor = Color.Red;
}
if (flagOK)
{
DialogResult result;
result = MessageBox.Show("¿Esta Seguro de que desea cambiar el/los campo(s)?", "Cambio de cmapos de cliente", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result == System.Windows.Forms.DialogResult.Yes)
{
if (namesCheckBox.Checked)
{
selectedClient.CompanyName = companyName;
}
if (districtCheckBox.Checked)
{
selectedClient.Address = address;
}
if (phoneCheckBox.Checked)
{
selectedClient.PhoneNumber = phoneNumber;
}
if (emailCheckBox.Checked)
{
selectedClient.Email = email;
}
legalClientBL.updateLegalClient(selectedClient);
MessageBox.Show("Se modifico al cliente", "Modificacion completada", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
}
else
{
MessageBox.Show("Uno o más campos son incorrectos. Revise los campos en rojo.", "Error en registro", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public string this[string columnName]
{
get
{
string message = string.Empty;
switch (columnName)
{
case "Name":
if (!DataValidation.ValidateString(Name))
{
message = "NAME should be only letters and 3-15 symbols";
}
break;
case "Surname":
if (!DataValidation.ValidateString(Surname))
{
message = "SURNAME should be only letters and 3-15 symbols";
}
break;
case "Nickname":
if (!DataValidation.ValidateString(Nickname))
{
message = "NICKNAME should be only letters and 3-15 symbols";
}
break;
case "Number":
if (!DataValidation.ValidateNumber(Number))
{
message = "NUMBER should be XXX-XXX-XXXX format";
}
break;
case "Password":
if (!DataValidation.ValidatePassword(Password))
{
message = "Password should be 3-15 symbols, " +
"uppercase and lowercase letter and number";
}
break;
case "ConfirmPassword":
if (!DataValidation.ValidateConfirmation(Password, ConfirmPassword))
{
message = "Passwords Do Not Match";
}
break;
}
Validated = DataValidation.ValidateString(Name) && DataValidation.ValidateString(Surname) &&
DataValidation.ValidateString(Nickname) && DataValidation.ValidateNumber(Number) &&
DataValidation.ValidatePassword(Password) &&
DataValidation.ValidateConfirmation(Password, ConfirmPassword);
return(message);
}
}
public RequestResult InsertOperationMechanisms(
OperationInfo OperationInfo,
List <Files> Files
)
{
try
{
/// Data Validations
DataValidation.IsEmptyOrDefault2(OperationInfo);
DataValidation.IsEmptyOrDefault2(OperationInfo.CheckedData);
DataValidation.IsEmptyOrDefault2(OperationInfo.ManagersInfo);
DataValidation.IsEmptyOrDefaultList2(Files);
//if (DataValidation.IsEmptyOrDefault(OperationInfo) ||
// DataValidation.IsEmptyOrDefault(OperationInfo.CheckedData) ||
// DataValidation.IsEmptyOrDefault(OperationInfo.ManagersInfo) ||
// DataValidation.IsEmptyOrDefaultList(Files))
// throw new FaultException<ValidationFault>(new ValidationFault());
/// Call Database
using (CooperativeDAL dal = new CooperativeDAL(new ADO()))
{
return(dal.InsertOperationMechanismsDAL(
OperationInfo,
Files
));
}
}
catch (FaultException <ValidationFault> flex)
{
//ValidationFault fault = new ValidationFault
//{
// Result = true,
// Message = "Parameter not correct",
// Description = "Invalid Parameters is Required but have null or empty or 0 value"
//};
//var flex = new FaultException<ValidationFault>(fault, new FaultReason("Invalid Parameters is Required but have null or empty or 0 value"));
_log.Error(flex);
throw flex;
}
catch (Exception ex)
{
ValidationFault fault = new ValidationFault
{
Result = false,
Message = ex.Message,
Description = "Service have an internal error please contact service administartor [email protected]"
};
_log.Error(ex);
throw new FaultException <ValidationFault>(fault);
}
}
public void CheckWeightTest_EquivalentNumber59_ReturnFalse()
{
DataValidation dataValidation = new DataValidation();
bool factResult;
int number = 59;
factResult = dataValidation.CheckWeight(number);
Assert.IsFalse(factResult);
}
public void CheckLenghtPasswordTest_StringLenght35_ReturnFalse()
{
DataValidation dataValidation = new DataValidation();
bool factResult;
string s1 = "abcdefghijklmnopqrstuvwxyzaylhecotl";
factResult = dataValidation.CheckLenghtPassword(s1);
Assert.IsFalse(factResult);
}
private void PortDataReceived(int port)
{
Thread.Sleep(20);
try
{
int len = _mPort.GetBytesToRead();
if (len == 0)
{
return;
}
byte[] by = new byte[len];
_mPort.Read(by, len);
List <byte[]> bylist = DataValidation.Validation(by);
foreach (byte[] item in bylist)
{
if (item == null)
{
continue;
}
ParsingParameter parameter = DataParsing.ParsingContent(item);
if (parameter.FunctionAddress == 50)
{
if (parameter.Command == 1)
{
long number = DataParsing.TemporaryContent(parameter.DataContent);
if (number == 1)
{
if (_currentNumber >= _mNumber.Number)
{
_mNumber.Number = _currentNumber;
if (_mNumber.Mid > 0)
{
DbHelper.Db.Update <ModuleNumber>(_mNumber);
}
else
{
_mNumber.Mid = DbHelper.Db.Insert <ModuleNumber>(_mNumber);
}
}
EnableDownLoadControls(false);
return;
}
else
{
EnableDownLoadControls(true);
return;
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + ex.StackTrace, @"提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
EnableDownLoadControls(true);
}
public void CheckVolumeTest_EquivalentNumber193_ReturnFalse()
{
DataValidation dataValidation = new DataValidation();
bool factResult;
int number = 193;
factResult = dataValidation.CheckVolume(number);
Assert.IsFalse(factResult);
}
public void CheckLenghtPasswordTest_StringLenght2_ReturnFalse()
{
DataValidation dataValidation = new DataValidation();
bool factResult;
string s1 = "ab";
factResult = dataValidation.CheckLenghtPassword(s1);
Assert.IsFalse(factResult);
}
public void CheckWeightTest_EquivalentNumber32_ReturnTrue()
{
DataValidation dataValidation = new DataValidation();
bool factResult;
int number = 32;
factResult = dataValidation.CheckWeight(number);
Assert.IsTrue(factResult);
}
public void CheckVolumeTest_LimitNumber121_ReturnFalse()
{
DataValidation dataValidation = new DataValidation();
bool factResult;
int number = 121;
factResult = dataValidation.CheckVolume(number);
Assert.IsFalse(factResult);
}
public void CheckVolumeTest_EquivalentNumber35_ReturnTrue()
{
DataValidation dataValidation = new DataValidation();
bool factResult;
int number = 35;
factResult = dataValidation.CheckVolume(number);
Assert.IsTrue(factResult);
}
public void SavePersuitData()
{
DataBaseWorker.SavePursuitData(JsonConvert.SerializeObject(this), Program.datosCompartidos.activeUser, LookAndPlayForm.Program.datosCompartidos.startTimeTest);
string path = DataConverter.OldTypeDirectory(Program.datosCompartidos.startTimeTest, Program.datosCompartidos.activeUser);
DataValidation.CheckDirectoryPath(path, true, FileAttributes.Hidden);
File.WriteAllText(path + @"\persuitData.json", JsonConvert.SerializeObject(this));
}
public static string ValidateDisplayName(IDbConnection connection, string displayName, Int32? existingUserId)
{
displayName = displayName.TrimToNull();
if (displayName == null)
throw DataValidation.RequiredError(fld.DisplayName.Name, fld.DisplayName.Title);
return displayName;
}
public void CheckLenghtPasswordTest_StringLenght14_ReturnTrue()
{
DataValidation dataValidation = new DataValidation();
bool factResult;
string s1 = "abcdefghijklmn";
factResult = dataValidation.CheckLenghtPassword(s1);
Assert.IsTrue(factResult);
}
public void CheckWeightTest_LimitNumber0_ReturnFalse()
{
DataValidation dataValidation = new DataValidation();
bool factResult;
int number = 0;
factResult = dataValidation.CheckWeight(number);
Assert.IsFalse(factResult);
}
public string DoWork(
testc c
)
{
DataValidation.IsEmptyOrDefault2(c);
DataValidation.IsEmptyOrDefault2(c);
return("NA");
}
// Обновляет свойства окружности
public override void Update(AutocadObject obj)
{
var circle = (PrimitiveCircle)obj;
this.Radius = DataValidation.ValidRadius(circle.Radius, this.Radius);
this.Center = circle.Center;
this.Height = circle.Height;
OnPropertyChanged("Display");
}
private void btnSave_Click(object sender, EventArgs e)
{
if ((dgvWIP.CurrentRow != null) && DataValidation.IsNumericNumber(txtFinalQty.Text))
{
objBatchActivity_DL.Update_FinalQty_WIP(Convert.ToDecimal(txtFinalQty.Text), Convert.ToInt32(dgvWIP.CurrentRow.Cells["PackingBatchID"].Value));
Load_WIP();
}
}
internal void FromDataValidation(DataValidation dv)
{
this.SetAllNull();
if (dv.Formula1 != null) this.Formula1 = dv.Formula1.Text;
if (dv.Formula2 != null) this.Formula2 = dv.Formula2.Text;
if (dv.Type != null) this.Type = dv.Type.Value;
if (dv.ErrorStyle != null) this.ErrorStyle = dv.ErrorStyle.Value;
if (dv.ImeMode != null) this.ImeMode = dv.ImeMode.Value;
if (dv.Operator != null) this.Operator = dv.Operator.Value;
if (dv.AllowBlank != null) this.AllowBlank = dv.AllowBlank.Value;
if (dv.ShowDropDown != null) this.ShowDropDown = dv.ShowDropDown.Value;
if (dv.ShowInputMessage != null) this.ShowInputMessage = dv.ShowInputMessage.Value;
if (dv.ShowErrorMessage != null) this.ShowErrorMessage = dv.ShowErrorMessage.Value;
if (dv.ErrorTitle != null) this.ErrorTitle = dv.ErrorTitle.Value;
if (dv.Error != null) this.Error = dv.Error.Value;
if (dv.PromptTitle != null) this.PromptTitle = dv.PromptTitle.Value;
if (dv.Prompt != null) this.Prompt = dv.Prompt.Value;
// it has to be not-null because it's a required thing, but you never know...
if (dv.SequenceOfReferences != null)
{
this.SequenceOfReferences = SLTool.TranslateSeqRefToCellPointRange(dv.SequenceOfReferences);
}
}
private static void GenerateWorksheetPartContent(WorksheetPart worksheetPart, XLWorksheet xlWorksheet,
SaveContext context)
{
#region Worksheet
if (worksheetPart.Worksheet == null)
worksheetPart.Worksheet = new Worksheet();
GenerateTables(xlWorksheet, worksheetPart, context);
if (
!worksheetPart.Worksheet.NamespaceDeclarations.Contains(new KeyValuePair<String, String>("r",
"http://schemas.openxmlformats.org/officeDocument/2006/relationships")))
{
worksheetPart.Worksheet.AddNamespaceDeclaration("r",
"http://schemas.openxmlformats.org/officeDocument/2006/relationships");
}
#endregion
var cm = new XLWSContentManager(worksheetPart.Worksheet);
#region SheetProperties
if (worksheetPart.Worksheet.SheetProperties == null)
worksheetPart.Worksheet.SheetProperties = new SheetProperties();
worksheetPart.Worksheet.SheetProperties.TabColor = xlWorksheet.TabColor.HasValue
? GetTabColor(xlWorksheet.TabColor)
: null;
cm.SetElement(XLWSContentManager.XLWSContents.SheetProperties, worksheetPart.Worksheet.SheetProperties);
if (worksheetPart.Worksheet.SheetProperties.OutlineProperties == null)
worksheetPart.Worksheet.SheetProperties.OutlineProperties = new OutlineProperties();
worksheetPart.Worksheet.SheetProperties.OutlineProperties.SummaryBelow =
(xlWorksheet.Outline.SummaryVLocation ==
XLOutlineSummaryVLocation.Bottom);
worksheetPart.Worksheet.SheetProperties.OutlineProperties.SummaryRight =
(xlWorksheet.Outline.SummaryHLocation ==
XLOutlineSummaryHLocation.Right);
if (worksheetPart.Worksheet.SheetProperties.PageSetupProperties == null
&& (xlWorksheet.PageSetup.PagesTall > 0 || xlWorksheet.PageSetup.PagesWide > 0))
worksheetPart.Worksheet.SheetProperties.PageSetupProperties = new PageSetupProperties {FitToPage = true};
#endregion
var maxColumn = 0;
var sheetDimensionReference = "A1";
if (xlWorksheet.Internals.CellsCollection.Count > 0)
{
maxColumn = xlWorksheet.Internals.CellsCollection.MaxColumnUsed;
var maxRow = xlWorksheet.Internals.CellsCollection.MaxRowUsed;
sheetDimensionReference = "A1:" + XLHelper.GetColumnLetterFromNumber(maxColumn) +
maxRow.ToStringLookup();
}
if (xlWorksheet.Internals.ColumnsCollection.Count > 0)
{
var maxColCollection = xlWorksheet.Internals.ColumnsCollection.Keys.Max();
if (maxColCollection > maxColumn)
maxColumn = maxColCollection;
}
#region SheetViews
if (worksheetPart.Worksheet.SheetDimension == null)
worksheetPart.Worksheet.SheetDimension = new SheetDimension {Reference = sheetDimensionReference};
cm.SetElement(XLWSContentManager.XLWSContents.SheetDimension, worksheetPart.Worksheet.SheetDimension);
if (worksheetPart.Worksheet.SheetViews == null)
worksheetPart.Worksheet.SheetViews = new SheetViews();
cm.SetElement(XLWSContentManager.XLWSContents.SheetViews, worksheetPart.Worksheet.SheetViews);
var sheetView = (SheetView)worksheetPart.Worksheet.SheetViews.FirstOrDefault();
if (sheetView == null)
{
sheetView = new SheetView {WorkbookViewId = 0U};
worksheetPart.Worksheet.SheetViews.AppendChild(sheetView);
}
if (xlWorksheet.TabSelected)
sheetView.TabSelected = true;
else
sheetView.TabSelected = null;
if (xlWorksheet.ShowFormulas)
sheetView.ShowFormulas = true;
else
sheetView.ShowFormulas = null;
if (xlWorksheet.ShowGridLines)
sheetView.ShowGridLines = null;
else
sheetView.ShowGridLines = false;
if (xlWorksheet.ShowOutlineSymbols)
sheetView.ShowOutlineSymbols = null;
else
sheetView.ShowOutlineSymbols = false;
if (xlWorksheet.ShowRowColHeaders)
sheetView.ShowRowColHeaders = null;
else
sheetView.ShowRowColHeaders = false;
if (xlWorksheet.ShowRuler)
sheetView.ShowRuler = null;
else
sheetView.ShowRuler = false;
if (xlWorksheet.ShowWhiteSpace)
sheetView.ShowWhiteSpace = null;
else
sheetView.ShowWhiteSpace = false;
if (xlWorksheet.ShowZeros)
sheetView.ShowZeros = null;
else
sheetView.ShowZeros = false;
if (xlWorksheet.RightToLeft)
sheetView.RightToLeft = true;
else
sheetView.RightToLeft = null;
if (xlWorksheet.SheetView.View == XLSheetViewOptions.Normal)
sheetView.View = null;
else
sheetView.View = xlWorksheet.SheetView.View.ToOpenXml();
var pane = sheetView.Elements<Pane>().FirstOrDefault();
if (pane == null)
{
pane = new Pane();
sheetView.AppendChild(pane);
}
pane.State = PaneStateValues.FrozenSplit;
Double hSplit = xlWorksheet.SheetView.SplitColumn;
Double ySplit = xlWorksheet.SheetView.SplitRow;
pane.HorizontalSplit = hSplit;
pane.VerticalSplit = ySplit;
pane.TopLeftCell = XLHelper.GetColumnLetterFromNumber(xlWorksheet.SheetView.SplitColumn + 1)
+ (xlWorksheet.SheetView.SplitRow + 1);
if (hSplit == 0 && ySplit == 0)
sheetView.RemoveAllChildren<Pane>();
if (xlWorksheet.SelectedRanges.Any() || xlWorksheet.ActiveCell != null)
{
sheetView.RemoveAllChildren<Selection>();
var firstSelection = xlWorksheet.SelectedRanges.FirstOrDefault();
var selection = new Selection();
if (xlWorksheet.ActiveCell != null)
selection.ActiveCell = xlWorksheet.ActiveCell.Address.ToStringRelative(false);
else if (firstSelection != null)
selection.ActiveCell = firstSelection.RangeAddress.FirstAddress.ToStringRelative(false);
var seqRef = new List<String> {selection.ActiveCell.Value};
seqRef.AddRange(xlWorksheet.SelectedRanges
.Select(range => range.RangeAddress.ToStringRelative(false)));
selection.SequenceOfReferences = new ListValue<StringValue> {InnerText = String.Join(" ", seqRef.Distinct().ToArray())};
sheetView.Append(selection);
}
#endregion
var maxOutlineColumn = 0;
if (xlWorksheet.ColumnCount() > 0)
maxOutlineColumn = xlWorksheet.GetMaxColumnOutline();
var maxOutlineRow = 0;
if (xlWorksheet.RowCount() > 0)
maxOutlineRow = xlWorksheet.GetMaxRowOutline();
#region SheetFormatProperties
if (worksheetPart.Worksheet.SheetFormatProperties == null)
worksheetPart.Worksheet.SheetFormatProperties = new SheetFormatProperties();
cm.SetElement(XLWSContentManager.XLWSContents.SheetFormatProperties,
worksheetPart.Worksheet.SheetFormatProperties);
worksheetPart.Worksheet.SheetFormatProperties.DefaultRowHeight = xlWorksheet.RowHeight;
if (xlWorksheet.RowHeightChanged)
worksheetPart.Worksheet.SheetFormatProperties.CustomHeight = true;
else
worksheetPart.Worksheet.SheetFormatProperties.CustomHeight = null;
var worksheetColumnWidth = GetColumnWidth(xlWorksheet.ColumnWidth);
if (xlWorksheet.ColumnWidthChanged)
worksheetPart.Worksheet.SheetFormatProperties.DefaultColumnWidth = worksheetColumnWidth;
else
worksheetPart.Worksheet.SheetFormatProperties.DefaultColumnWidth = null;
if (maxOutlineColumn > 0)
worksheetPart.Worksheet.SheetFormatProperties.OutlineLevelColumn = (byte)maxOutlineColumn;
else
worksheetPart.Worksheet.SheetFormatProperties.OutlineLevelColumn = null;
if (maxOutlineRow > 0)
worksheetPart.Worksheet.SheetFormatProperties.OutlineLevelRow = (byte)maxOutlineRow;
else
worksheetPart.Worksheet.SheetFormatProperties.OutlineLevelRow = null;
#endregion
#region Columns
if (xlWorksheet.Internals.CellsCollection.Count == 0 &&
xlWorksheet.Internals.ColumnsCollection.Count == 0
&& xlWorksheet.Style.Equals(DefaultStyle))
worksheetPart.Worksheet.RemoveAllChildren<Columns>();
else
{
if (!worksheetPart.Worksheet.Elements<Columns>().Any())
{
var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.Columns);
worksheetPart.Worksheet.InsertAfter(new Columns(), previousElement);
}
var columns = worksheetPart.Worksheet.Elements<Columns>().First();
cm.SetElement(XLWSContentManager.XLWSContents.Columns, columns);
var sheetColumnsByMin = columns.Elements<Column>().ToDictionary(c => c.Min.Value, c => c);
//Dictionary<UInt32, Column> sheetColumnsByMax = columns.Elements<Column>().ToDictionary(c => c.Max.Value, c => c);
Int32 minInColumnsCollection;
Int32 maxInColumnsCollection;
if (xlWorksheet.Internals.ColumnsCollection.Count > 0)
{
minInColumnsCollection = xlWorksheet.Internals.ColumnsCollection.Keys.Min();
maxInColumnsCollection = xlWorksheet.Internals.ColumnsCollection.Keys.Max();
}
else
{
minInColumnsCollection = 1;
maxInColumnsCollection = 0;
}
var worksheetStyleId = context.SharedStyles[xlWorksheet.GetStyleId()].StyleId;
if (minInColumnsCollection > 1)
{
UInt32Value min = 1;
UInt32Value max = (UInt32)(minInColumnsCollection - 1);
for (var co = min; co <= max; co++)
{
var column = new Column
{
Min = co,
Max = co,
Style = worksheetStyleId,
Width = worksheetColumnWidth,
CustomWidth = true
};
UpdateColumn(column, columns, sheetColumnsByMin); //, sheetColumnsByMax);
}
}
for (var co = minInColumnsCollection; co <= maxInColumnsCollection; co++)
{
UInt32 styleId;
Double columnWidth;
var isHidden = false;
var collapsed = false;
var outlineLevel = 0;
if (xlWorksheet.Internals.ColumnsCollection.ContainsKey(co))
{
styleId = context.SharedStyles[xlWorksheet.Internals.ColumnsCollection[co].GetStyleId()].StyleId;
columnWidth = GetColumnWidth(xlWorksheet.Internals.ColumnsCollection[co].Width);
isHidden = xlWorksheet.Internals.ColumnsCollection[co].IsHidden;
collapsed = xlWorksheet.Internals.ColumnsCollection[co].Collapsed;
outlineLevel = xlWorksheet.Internals.ColumnsCollection[co].OutlineLevel;
}
else
{
styleId = context.SharedStyles[xlWorksheet.GetStyleId()].StyleId;
columnWidth = worksheetColumnWidth;
}
var column = new Column
{
Min = (UInt32)co,
Max = (UInt32)co,
Style = styleId,
Width = columnWidth,
CustomWidth = true
};
if (isHidden)
column.Hidden = true;
if (collapsed)
column.Collapsed = true;
if (outlineLevel > 0)
column.OutlineLevel = (byte)outlineLevel;
UpdateColumn(column, columns, sheetColumnsByMin); //, sheetColumnsByMax);
}
var collection = maxInColumnsCollection;
foreach (
var col in
columns.Elements<Column>().Where(c => c.Min > (UInt32)(collection)).OrderBy(
c => c.Min.Value))
{
col.Style = worksheetStyleId;
col.Width = worksheetColumnWidth;
col.CustomWidth = true;
if ((Int32)col.Max.Value > maxInColumnsCollection)
maxInColumnsCollection = (Int32)col.Max.Value;
}
if (maxInColumnsCollection < XLHelper.MaxColumnNumber && !xlWorksheet.Style.Equals(DefaultStyle))
{
var column = new Column
{
Min = (UInt32)(maxInColumnsCollection + 1),
Max = (UInt32)(XLHelper.MaxColumnNumber),
Style = worksheetStyleId,
Width = worksheetColumnWidth,
CustomWidth = true
};
columns.AppendChild(column);
}
CollapseColumns(columns, sheetColumnsByMin);
if (!columns.Any())
{
worksheetPart.Worksheet.RemoveAllChildren<Columns>();
cm.SetElement(XLWSContentManager.XLWSContents.Columns, null);
}
}
#endregion
#region SheetData
if (!worksheetPart.Worksheet.Elements<SheetData>().Any())
{
var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.SheetData);
worksheetPart.Worksheet.InsertAfter(new SheetData(), previousElement);
}
var sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();
cm.SetElement(XLWSContentManager.XLWSContents.SheetData, sheetData);
var cellsByRow = new Dictionary<Int32, List<IXLCell>>();
foreach (var c in xlWorksheet.Internals.CellsCollection.GetCells())
{
var rowNum = c.Address.RowNumber;
if (!cellsByRow.ContainsKey(rowNum))
cellsByRow.Add(rowNum, new List<IXLCell>());
cellsByRow[rowNum].Add(c);
}
var lastRow = 0;
var sheetDataRows =
sheetData.Elements<Row>().ToDictionary(r => r.RowIndex == null ? ++lastRow : (Int32)r.RowIndex.Value,
r => r);
foreach (
var r in
xlWorksheet.Internals.RowsCollection.Deleted.Where(r => sheetDataRows.ContainsKey(r.Key)))
{
sheetData.RemoveChild(sheetDataRows[r.Key]);
sheetDataRows.Remove(r.Key);
xlWorksheet.Internals.CellsCollection.Deleted.RemoveWhere(d => d.Row == r.Key);
}
var distinctRows = cellsByRow.Keys.Union(xlWorksheet.Internals.RowsCollection.Keys);
var noRows = (sheetData.Elements<Row>().FirstOrDefault() == null);
foreach (var distinctRow in distinctRows.OrderBy(r => r))
{
Row row; // = sheetData.Elements<Row>().FirstOrDefault(r => r.RowIndex.Value == (UInt32)distinctRow);
if (sheetDataRows.ContainsKey(distinctRow))
row = sheetDataRows[distinctRow];
else
{
row = new Row {RowIndex = (UInt32)distinctRow};
if (noRows)
{
sheetData.AppendChild(row);
noRows = false;
}
else
{
if (sheetDataRows.Any(r => r.Key > row.RowIndex.Value))
{
var minRow = sheetDataRows.Where(r => r.Key > (Int32)row.RowIndex.Value).Min(r => r.Key);
var rowBeforeInsert = sheetDataRows[minRow];
sheetData.InsertBefore(row, rowBeforeInsert);
}
else
sheetData.AppendChild(row);
}
}
if (maxColumn > 0)
row.Spans = new ListValue<StringValue> {InnerText = "1:" + maxColumn.ToStringLookup()};
row.Height = null;
row.CustomHeight = null;
row.Hidden = null;
row.StyleIndex = null;
row.CustomFormat = null;
row.Collapsed = null;
if (xlWorksheet.Internals.RowsCollection.ContainsKey(distinctRow))
{
var thisRow = xlWorksheet.Internals.RowsCollection[distinctRow];
if (thisRow.HeightChanged)
{
row.Height = thisRow.Height;
row.CustomHeight = true;
row.CustomFormat = true;
}
if (thisRow.GetStyleId() != xlWorksheet.GetStyleId())
{
row.StyleIndex = context.SharedStyles[thisRow.GetStyleId()].StyleId;
row.CustomFormat = true;
}
if (thisRow.IsHidden)
row.Hidden = true;
if (thisRow.Collapsed)
row.Collapsed = true;
if (thisRow.OutlineLevel > 0)
row.OutlineLevel = (byte)thisRow.OutlineLevel;
}
var lastCell = 0;
var cellsByReference = row.Elements<Cell>().ToDictionary(c => c.CellReference == null
? XLHelper.GetColumnLetterFromNumber(
++lastCell) + distinctRow
: c.CellReference.Value, c => c);
foreach (var c in xlWorksheet.Internals.CellsCollection.Deleted.ToList())
{
var key = XLHelper.GetColumnLetterFromNumber(c.Column) + c.Row.ToStringLookup();
if (!cellsByReference.ContainsKey(key)) continue;
row.RemoveChild(cellsByReference[key]);
xlWorksheet.Internals.CellsCollection.Deleted.Remove(c);
}
if (!cellsByRow.ContainsKey(distinctRow)) continue;
var isNewRow = !row.Elements<Cell>().Any();
var mRows = row.Elements<Cell>().ToDictionary(c => XLHelper.GetColumnNumberFromAddress(c.CellReference.Value), c => c);
foreach (var opCell in cellsByRow[distinctRow]
.OrderBy(c => c.Address.ColumnNumber)
.Select(c => (XLCell)c))
{
var styleId = context.SharedStyles[opCell.GetStyleId()].StyleId;
var dataType = opCell.DataType;
var cellReference = (opCell.Address).GetTrimmedAddress();
Cell cell;
if (cellsByReference.ContainsKey(cellReference))
cell = cellsByReference[cellReference];
else
{
cell = new Cell {CellReference = new StringValue(cellReference)};
if (isNewRow)
row.AppendChild(cell);
else
{
var newColumn = XLHelper.GetColumnNumberFromAddress(cellReference);
Cell cellBeforeInsert = null;
int[] lastCo = {Int32.MaxValue};
foreach (var c in mRows.Where(kp => kp.Key > newColumn).Where(c => lastCo[0] > c.Key))
{
cellBeforeInsert = c.Value;
lastCo[0] = c.Key;
}
if (cellBeforeInsert == null)
row.AppendChild(cell);
else
row.InsertBefore(cell, cellBeforeInsert);
}
}
cell.StyleIndex = styleId;
var formula = opCell.FormulaA1;
if (opCell.HasFormula)
{
if (formula.StartsWith("{"))
{
formula = formula.Substring(1, formula.Length - 2);
var f = new CellFormula {FormulaType = CellFormulaValues.Array};
if (opCell.FormulaReference.FirstAddress.Equals(opCell.Address))
{
f.Text = formula;
f.Reference = opCell.FormulaReference.ToStringRelative();
}
cell.CellFormula = f;
}
else
cell.CellFormula = new CellFormula(formula);
cell.CellValue = null;
}
else
{
cell.CellFormula = null;
cell.DataType = opCell.DataType == XLCellValues.DateTime ? null : GetCellValue(opCell);
var cellValue = new CellValue();
if (dataType == XLCellValues.Text)
{
if (opCell.InnerText.Length == 0)
cell.CellValue = null;
else
{
if (opCell.ShareString)
{
cellValue.Text = opCell.SharedStringId.ToString();
cell.CellValue = cellValue;
}
else
{
var text = opCell.GetString();
var t = new Text(text);
if (text.PreserveSpaces())
t.Space = SpaceProcessingModeValues.Preserve;
cell.InlineString = new InlineString {Text = t};
}
}
}
else if (dataType == XLCellValues.TimeSpan)
{
var timeSpan = opCell.GetTimeSpan();
cellValue.Text =
XLCell.BaseDate.Add(timeSpan).ToOADate().ToString(CultureInfo.InvariantCulture);
cell.CellValue = cellValue;
}
else if (dataType == XLCellValues.DateTime || dataType == XLCellValues.Number)
{
if (!XLHelper.IsNullOrWhiteSpace(opCell.InnerText))
cellValue.Text = Double.Parse(opCell.InnerText).ToString(CultureInfo.InvariantCulture);
cell.CellValue = cellValue;
}
else
{
cellValue.Text = opCell.InnerText;
cell.CellValue = cellValue;
}
}
}
xlWorksheet.Internals.CellsCollection.Deleted.RemoveWhere(d => d.Row == distinctRow);
}
foreach (
var r in
xlWorksheet.Internals.CellsCollection.Deleted.Select(c => c.Row).Distinct().Where(
sheetDataRows.ContainsKey))
{
sheetData.RemoveChild(sheetDataRows[r]);
sheetDataRows.Remove(r);
}
#endregion
#region SheetProtection
if (xlWorksheet.Protection.Protected)
{
if (!worksheetPart.Worksheet.Elements<SheetProtection>().Any())
{
var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.SheetProtection);
worksheetPart.Worksheet.InsertAfter(new SheetProtection(), previousElement);
}
var sheetProtection = worksheetPart.Worksheet.Elements<SheetProtection>().First();
cm.SetElement(XLWSContentManager.XLWSContents.SheetProtection, sheetProtection);
var protection = xlWorksheet.Protection;
sheetProtection.Sheet = protection.Protected;
if (!XLHelper.IsNullOrWhiteSpace(protection.PasswordHash))
sheetProtection.Password = protection.PasswordHash;
sheetProtection.FormatCells = GetBooleanValue(!protection.FormatCells, true);
sheetProtection.FormatColumns = GetBooleanValue(!protection.FormatColumns, true);
sheetProtection.FormatRows = GetBooleanValue(!protection.FormatRows, true);
sheetProtection.InsertColumns = GetBooleanValue(!protection.InsertColumns, true);
sheetProtection.InsertHyperlinks = GetBooleanValue(!protection.InsertHyperlinks, true);
sheetProtection.InsertRows = GetBooleanValue(!protection.InsertRows, true);
sheetProtection.DeleteColumns = GetBooleanValue(!protection.DeleteColumns, true);
sheetProtection.DeleteRows = GetBooleanValue(!protection.DeleteRows, true);
sheetProtection.AutoFilter = GetBooleanValue(!protection.AutoFilter, true);
sheetProtection.PivotTables = GetBooleanValue(!protection.PivotTables, true);
sheetProtection.Sort = GetBooleanValue(!protection.Sort, true);
sheetProtection.SelectLockedCells = GetBooleanValue(!protection.SelectLockedCells, false);
sheetProtection.SelectUnlockedCells = GetBooleanValue(!protection.SelectUnlockedCells, false);
}
else
{
worksheetPart.Worksheet.RemoveAllChildren<SheetProtection>();
cm.SetElement(XLWSContentManager.XLWSContents.SheetProtection, null);
}
#endregion
#region AutoFilter
worksheetPart.Worksheet.RemoveAllChildren<AutoFilter>();
if (xlWorksheet.AutoFilter.Enabled)
{
var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.AutoFilter);
worksheetPart.Worksheet.InsertAfter(new AutoFilter(), previousElement);
var autoFilter = worksheetPart.Worksheet.Elements<AutoFilter>().First();
cm.SetElement(XLWSContentManager.XLWSContents.AutoFilter, autoFilter);
PopulateAutoFilter(xlWorksheet.AutoFilter, autoFilter);
}
else
{
cm.SetElement(XLWSContentManager.XLWSContents.AutoFilter, null);
}
#endregion
#region MergeCells
if ((xlWorksheet).Internals.MergedRanges.Any())
{
if (!worksheetPart.Worksheet.Elements<MergeCells>().Any())
{
var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.MergeCells);
worksheetPart.Worksheet.InsertAfter(new MergeCells(), previousElement);
}
var mergeCells = worksheetPart.Worksheet.Elements<MergeCells>().First();
cm.SetElement(XLWSContentManager.XLWSContents.MergeCells, mergeCells);
mergeCells.RemoveAllChildren<MergeCell>();
foreach (var mergeCell in (xlWorksheet).Internals.MergedRanges.Select(
m => m.RangeAddress.FirstAddress.ToString() + ":" + m.RangeAddress.LastAddress.ToString()).Select(
merged => new MergeCell {Reference = merged}))
mergeCells.AppendChild(mergeCell);
mergeCells.Count = (UInt32)mergeCells.Count();
}
else
{
worksheetPart.Worksheet.RemoveAllChildren<MergeCells>();
cm.SetElement(XLWSContentManager.XLWSContents.MergeCells, null);
}
#endregion
#region Conditional Formatting
if (!xlWorksheet.ConditionalFormats.Any())
{
worksheetPart.Worksheet.RemoveAllChildren<ConditionalFormatting>();
cm.SetElement(XLWSContentManager.XLWSContents.ConditionalFormatting, null);
}
else
{
worksheetPart.Worksheet.RemoveAllChildren<ConditionalFormatting>();
var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.ConditionalFormatting);
//if (!worksheetPart.Worksheet.Elements<ConditionalFormatting>().Any())
//{
// var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.ConditionalFormatting);
// worksheetPart.Worksheet.InsertAfter(new ConditionalFormatting(), previousElement);
//}
//var conditionalFormats = worksheetPart.Worksheet.Elements<ConditionalFormatting>().First();
//cm.SetElement(XLWSContentManager.XLWSContents.ConditionalFormatting, conditionalFormats);
////conditionalFormats.RemoveAllChildren<ConditionalFormat>();
var priority = 0;
foreach (var cf in xlWorksheet.ConditionalFormats)
{
priority++;
var conditionalFormatting = new ConditionalFormatting
{
SequenceOfReferences =
new ListValue<StringValue> {InnerText = cf.Range.RangeAddress.ToStringRelative(false)}
};
conditionalFormatting.Append(XLCFConverters.Convert(cf, priority, context));
worksheetPart.Worksheet.InsertAfter(conditionalFormatting, previousElement);
previousElement = conditionalFormatting;
cm.SetElement(XLWSContentManager.XLWSContents.ConditionalFormatting, conditionalFormatting);
}
}
#endregion
#region DataValidations
if (!xlWorksheet.DataValidations.Any(d => d.IsDirty()))
{
worksheetPart.Worksheet.RemoveAllChildren<DataValidations>();
cm.SetElement(XLWSContentManager.XLWSContents.DataValidations, null);
}
else
{
if (!worksheetPart.Worksheet.Elements<DataValidations>().Any())
{
var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.DataValidations);
worksheetPart.Worksheet.InsertAfter(new DataValidations(), previousElement);
}
var dataValidations = worksheetPart.Worksheet.Elements<DataValidations>().First();
cm.SetElement(XLWSContentManager.XLWSContents.DataValidations, dataValidations);
dataValidations.RemoveAllChildren<DataValidation>();
foreach (var dv in xlWorksheet.DataValidations)
{
var sequence = dv.Ranges.Aggregate(String.Empty, (current, r) => current + (r.RangeAddress + " "));
if (sequence.Length > 0)
sequence = sequence.Substring(0, sequence.Length - 1);
var dataValidation = new DataValidation
{
AllowBlank = dv.IgnoreBlanks,
Formula1 = new Formula1(dv.MinValue),
Formula2 = new Formula2(dv.MaxValue),
Type = dv.AllowedValues.ToOpenXml(),
ShowErrorMessage = dv.ShowErrorMessage,
Prompt = dv.InputMessage,
PromptTitle = dv.InputTitle,
ErrorTitle = dv.ErrorTitle,
Error = dv.ErrorMessage,
ShowDropDown = !dv.InCellDropdown,
ShowInputMessage = dv.ShowInputMessage,
ErrorStyle = dv.ErrorStyle.ToOpenXml(),
Operator = dv.Operator.ToOpenXml(),
SequenceOfReferences =
new ListValue<StringValue> {InnerText = sequence}
};
dataValidations.AppendChild(dataValidation);
}
dataValidations.Count = (UInt32)xlWorksheet.DataValidations.Count();
}
#endregion
#region Hyperlinks
var relToRemove = worksheetPart.HyperlinkRelationships.ToList();
relToRemove.ForEach(worksheetPart.DeleteReferenceRelationship);
if (!xlWorksheet.Hyperlinks.Any())
{
worksheetPart.Worksheet.RemoveAllChildren<Hyperlinks>();
cm.SetElement(XLWSContentManager.XLWSContents.Hyperlinks, null);
}
else
{
if (!worksheetPart.Worksheet.Elements<Hyperlinks>().Any())
{
var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.Hyperlinks);
worksheetPart.Worksheet.InsertAfter(new Hyperlinks(), previousElement);
}
var hyperlinks = worksheetPart.Worksheet.Elements<Hyperlinks>().First();
cm.SetElement(XLWSContentManager.XLWSContents.Hyperlinks, hyperlinks);
hyperlinks.RemoveAllChildren<Hyperlink>();
foreach (var hl in xlWorksheet.Hyperlinks)
{
Hyperlink hyperlink;
if (hl.IsExternal)
{
var rId = context.RelIdGenerator.GetNext(RelType.Workbook);
hyperlink = new Hyperlink {Reference = hl.Cell.Address.ToString(), Id = rId};
worksheetPart.AddHyperlinkRelationship(hl.ExternalAddress, true, rId);
}
else
{
hyperlink = new Hyperlink
{
Reference = hl.Cell.Address.ToString(),
Location = hl.InternalAddress,
Display = hl.Cell.GetFormattedString()
};
}
if (!XLHelper.IsNullOrWhiteSpace(hl.Tooltip))
hyperlink.Tooltip = hl.Tooltip;
hyperlinks.AppendChild(hyperlink);
}
}
#endregion
#region PrintOptions
if (!worksheetPart.Worksheet.Elements<PrintOptions>().Any())
{
var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.PrintOptions);
worksheetPart.Worksheet.InsertAfter(new PrintOptions(), previousElement);
}
var printOptions = worksheetPart.Worksheet.Elements<PrintOptions>().First();
cm.SetElement(XLWSContentManager.XLWSContents.PrintOptions, printOptions);
printOptions.HorizontalCentered = xlWorksheet.PageSetup.CenterHorizontally;
printOptions.VerticalCentered = xlWorksheet.PageSetup.CenterVertically;
printOptions.Headings = xlWorksheet.PageSetup.ShowRowAndColumnHeadings;
printOptions.GridLines = xlWorksheet.PageSetup.ShowGridlines;
#endregion
#region PageMargins
if (!worksheetPart.Worksheet.Elements<PageMargins>().Any())
{
var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.PageMargins);
worksheetPart.Worksheet.InsertAfter(new PageMargins(), previousElement);
}
var pageMargins = worksheetPart.Worksheet.Elements<PageMargins>().First();
cm.SetElement(XLWSContentManager.XLWSContents.PageMargins, pageMargins);
pageMargins.Left = xlWorksheet.PageSetup.Margins.Left;
pageMargins.Right = xlWorksheet.PageSetup.Margins.Right;
pageMargins.Top = xlWorksheet.PageSetup.Margins.Top;
pageMargins.Bottom = xlWorksheet.PageSetup.Margins.Bottom;
pageMargins.Header = xlWorksheet.PageSetup.Margins.Header;
pageMargins.Footer = xlWorksheet.PageSetup.Margins.Footer;
#endregion
#region PageSetup
if (!worksheetPart.Worksheet.Elements<PageSetup>().Any())
{
var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.PageSetup);
worksheetPart.Worksheet.InsertAfter(new PageSetup(), previousElement);
}
var pageSetup = worksheetPart.Worksheet.Elements<PageSetup>().First();
cm.SetElement(XLWSContentManager.XLWSContents.PageSetup, pageSetup);
pageSetup.Orientation = xlWorksheet.PageSetup.PageOrientation.ToOpenXml();
pageSetup.PaperSize = (UInt32)xlWorksheet.PageSetup.PaperSize;
pageSetup.BlackAndWhite = xlWorksheet.PageSetup.BlackAndWhite;
pageSetup.Draft = xlWorksheet.PageSetup.DraftQuality;
pageSetup.PageOrder = xlWorksheet.PageSetup.PageOrder.ToOpenXml();
pageSetup.CellComments = xlWorksheet.PageSetup.ShowComments.ToOpenXml();
pageSetup.Errors = xlWorksheet.PageSetup.PrintErrorValue.ToOpenXml();
if (xlWorksheet.PageSetup.FirstPageNumber > 0)
{
pageSetup.FirstPageNumber = (UInt32)xlWorksheet.PageSetup.FirstPageNumber;
pageSetup.UseFirstPageNumber = true;
}
else
{
pageSetup.FirstPageNumber = null;
pageSetup.UseFirstPageNumber = null;
}
if (xlWorksheet.PageSetup.HorizontalDpi > 0)
pageSetup.HorizontalDpi = (UInt32)xlWorksheet.PageSetup.HorizontalDpi;
else
pageSetup.HorizontalDpi = null;
if (xlWorksheet.PageSetup.VerticalDpi > 0)
pageSetup.VerticalDpi = (UInt32)xlWorksheet.PageSetup.VerticalDpi;
else
pageSetup.VerticalDpi = null;
if (xlWorksheet.PageSetup.Scale > 0)
{
pageSetup.Scale = (UInt32)xlWorksheet.PageSetup.Scale;
pageSetup.FitToWidth = null;
pageSetup.FitToHeight = null;
}
else
{
pageSetup.Scale = null;
if (xlWorksheet.PageSetup.PagesWide > 0)
pageSetup.FitToWidth = (UInt32)xlWorksheet.PageSetup.PagesWide;
else
pageSetup.FitToWidth = 0;
if (xlWorksheet.PageSetup.PagesTall > 0)
pageSetup.FitToHeight = (UInt32)xlWorksheet.PageSetup.PagesTall;
else
pageSetup.FitToHeight = 0;
}
#endregion
#region HeaderFooter
var headerFooter = worksheetPart.Worksheet.Elements<HeaderFooter>().FirstOrDefault();
if (headerFooter == null)
headerFooter = new HeaderFooter();
else
worksheetPart.Worksheet.RemoveAllChildren<HeaderFooter>();
{
var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.HeaderFooter);
worksheetPart.Worksheet.InsertAfter(headerFooter, previousElement);
cm.SetElement(XLWSContentManager.XLWSContents.HeaderFooter, headerFooter);
}
if (((XLHeaderFooter)xlWorksheet.PageSetup.Header).Changed
|| ((XLHeaderFooter)xlWorksheet.PageSetup.Footer).Changed)
{
//var headerFooter = worksheetPart.Worksheet.Elements<HeaderFooter>().First();
headerFooter.RemoveAllChildren();
headerFooter.ScaleWithDoc = xlWorksheet.PageSetup.ScaleHFWithDocument;
headerFooter.AlignWithMargins = xlWorksheet.PageSetup.AlignHFWithMargins;
headerFooter.DifferentFirst = xlWorksheet.PageSetup.DifferentFirstPageOnHF;
headerFooter.DifferentOddEven = xlWorksheet.PageSetup.DifferentOddEvenPagesOnHF;
var oddHeader = new OddHeader(xlWorksheet.PageSetup.Header.GetText(XLHFOccurrence.OddPages));
headerFooter.AppendChild(oddHeader);
var oddFooter = new OddFooter(xlWorksheet.PageSetup.Footer.GetText(XLHFOccurrence.OddPages));
headerFooter.AppendChild(oddFooter);
var evenHeader = new EvenHeader(xlWorksheet.PageSetup.Header.GetText(XLHFOccurrence.EvenPages));
headerFooter.AppendChild(evenHeader);
var evenFooter = new EvenFooter(xlWorksheet.PageSetup.Footer.GetText(XLHFOccurrence.EvenPages));
headerFooter.AppendChild(evenFooter);
var firstHeader = new FirstHeader(xlWorksheet.PageSetup.Header.GetText(XLHFOccurrence.FirstPage));
headerFooter.AppendChild(firstHeader);
var firstFooter = new FirstFooter(xlWorksheet.PageSetup.Footer.GetText(XLHFOccurrence.FirstPage));
headerFooter.AppendChild(firstFooter);
}
#endregion
#region RowBreaks
if (!worksheetPart.Worksheet.Elements<RowBreaks>().Any())
{
var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.RowBreaks);
worksheetPart.Worksheet.InsertAfter(new RowBreaks(), previousElement);
}
var rowBreaks = worksheetPart.Worksheet.Elements<RowBreaks>().First();
var rowBreakCount = xlWorksheet.PageSetup.RowBreaks.Count;
if (rowBreakCount > 0)
{
rowBreaks.Count = (UInt32)rowBreakCount;
rowBreaks.ManualBreakCount = (UInt32)rowBreakCount;
var lastRowNum = (UInt32)xlWorksheet.RangeAddress.LastAddress.RowNumber;
foreach (var break1 in xlWorksheet.PageSetup.RowBreaks.Select(rb => new Break
{
Id = (UInt32)rb,
Max = lastRowNum,
ManualPageBreak = true
}))
rowBreaks.AppendChild(break1);
cm.SetElement(XLWSContentManager.XLWSContents.RowBreaks, rowBreaks);
}
else
{
worksheetPart.Worksheet.RemoveAllChildren<RowBreaks>();
cm.SetElement(XLWSContentManager.XLWSContents.RowBreaks, null);
}
#endregion
#region ColumnBreaks
if (!worksheetPart.Worksheet.Elements<ColumnBreaks>().Any())
{
var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.ColumnBreaks);
worksheetPart.Worksheet.InsertAfter(new ColumnBreaks(), previousElement);
}
var columnBreaks = worksheetPart.Worksheet.Elements<ColumnBreaks>().First();
var columnBreakCount = xlWorksheet.PageSetup.ColumnBreaks.Count;
if (columnBreakCount > 0)
{
columnBreaks.Count = (UInt32)columnBreakCount;
columnBreaks.ManualBreakCount = (UInt32)columnBreakCount;
var maxColumnNumber = (UInt32)xlWorksheet.RangeAddress.LastAddress.ColumnNumber;
foreach (var break1 in xlWorksheet.PageSetup.ColumnBreaks.Select(cb => new Break
{
Id = (UInt32)cb,
Max = maxColumnNumber,
ManualPageBreak = true
}))
columnBreaks.AppendChild(break1);
cm.SetElement(XLWSContentManager.XLWSContents.ColumnBreaks, columnBreaks);
}
else
{
worksheetPart.Worksheet.RemoveAllChildren<ColumnBreaks>();
cm.SetElement(XLWSContentManager.XLWSContents.ColumnBreaks, null);
}
#endregion
#region Drawings
//worksheetPart.Worksheet.RemoveAllChildren<Drawing>();
//{
// OpenXmlElement previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.Drawing);
// worksheetPart.Worksheet.InsertAfter(new Drawing() { Id = String.Format("rId{0}", 1) }, previousElement);
//}
//Drawing drawing = worksheetPart.Worksheet.Elements<Drawing>().First();
//cm.SetElement(XLWSContentManager.XLWSContents.Drawing, drawing);
#endregion
#region Tables
worksheetPart.Worksheet.RemoveAllChildren<TableParts>();
{
var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.TableParts);
worksheetPart.Worksheet.InsertAfter(new TableParts(), previousElement);
}
var tableParts = worksheetPart.Worksheet.Elements<TableParts>().First();
cm.SetElement(XLWSContentManager.XLWSContents.TableParts, tableParts);
tableParts.Count = (UInt32)xlWorksheet.Tables.Count();
foreach (
var tablePart in
from XLTable xlTable in xlWorksheet.Tables select new TablePart {Id = xlTable.RelId})
tableParts.AppendChild(tablePart);
#endregion
#region LegacyDrawing
if (xlWorksheet.LegacyDrawingIsNew)
{
worksheetPart.Worksheet.RemoveAllChildren<LegacyDrawing>();
{
if (!XLHelper.IsNullOrWhiteSpace(xlWorksheet.LegacyDrawingId))
{
var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.LegacyDrawing);
worksheetPart.Worksheet.InsertAfter(new LegacyDrawing {Id = xlWorksheet.LegacyDrawingId},
previousElement);
}
}
}
#endregion
#region LegacyDrawingHeaderFooter
//LegacyDrawingHeaderFooter legacyHeaderFooter = worksheetPart.Worksheet.Elements<LegacyDrawingHeaderFooter>().FirstOrDefault();
//if (legacyHeaderFooter != null)
//{
// worksheetPart.Worksheet.RemoveAllChildren<LegacyDrawingHeaderFooter>();
// {
// var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.LegacyDrawingHeaderFooter);
// worksheetPart.Worksheet.InsertAfter(new LegacyDrawingHeaderFooter { Id = xlWorksheet.LegacyDrawingId },
// previousElement);
// }
//}
#endregion
}
internal DataValidation ToDataValidation()
{
DataValidation dv = new DataValidation();
if (this.Formula1.Length > 0) dv.Formula1 = new Formula1(this.Formula1);
if (this.Formula2.Length > 0) dv.Formula2 = new Formula2(this.Formula2);
if (this.Type != DataValidationValues.None) dv.Type = this.Type;
if (this.ErrorStyle != DataValidationErrorStyleValues.Stop) dv.ErrorStyle = this.ErrorStyle;
if (this.ImeMode != DataValidationImeModeValues.NoControl) dv.ImeMode = this.ImeMode;
if (this.Operator != DataValidationOperatorValues.Between) dv.Operator = this.Operator;
if (this.AllowBlank) dv.AllowBlank = this.AllowBlank;
if (this.ShowDropDown) dv.ShowDropDown = this.ShowDropDown;
if (this.ShowInputMessage) dv.ShowInputMessage = this.ShowInputMessage;
if (this.ShowErrorMessage) dv.ShowErrorMessage = this.ShowErrorMessage;
if (this.ErrorTitle.Length > 0) dv.ErrorTitle = this.ErrorTitle;
if (this.Error.Length > 0) dv.Error = this.Error;
if (this.PromptTitle.Length > 0) dv.PromptTitle = this.PromptTitle;
if (this.Prompt.Length > 0) dv.Prompt = this.Prompt;
dv.SequenceOfReferences = SLTool.TranslateCellPointRangeToSeqRef(this.SequenceOfReferences);
return dv;
}
public virtual IEnumerable<DataValidation> AddDataValidations(IEnumerable<IColumnDefinition> columnDefinitions)
{
var defs = columnDefinitions.ToList();
//Last row of excel
const int lastRow = 1048576;
//=FormOIDSource
const string formula1 = "={0}";
//=INDIRECT("FormOid.Form2")
const string formula2 = "=INDIRECT(\"{0}.\" & OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,{1}))";
List<DataValidation> validations = new List<DataValidation>();
for (int i = 0; i < defs.Count; i++)
{
var def = defs[i];
string columnName = (i + 1).ConvertToColumnName();
//without header: "A1:A1048576", with header : "A2:A1048576"
int firstRow = def.Header == null ? 1 : 2;
string source = def.ColumnSource;
if (string.IsNullOrEmpty(source))
{
continue;
}
string formula = null;
var sourceParts = source.Split('.');
if (sourceParts.Length == 2)
{
//indirect source
//"{Header}.{DependentHeader}"
//"FieldOid.FormOid"
var indirectOnDef = defs.FirstOrDefault(e => e.Header == sourceParts[1]);
if (indirectOnDef != null)
{
var j = defs.IndexOf(indirectOnDef);
var offset = j - i;
formula = string.Format(formula2, def.Header, offset);
}
}
else
{
//direct source
//"{Source}"
//"FormOIDSource"
formula = string.Format(formula1, sourceParts[0]);
}
var validation = new DataValidation
{
Type = DataValidationValues.List,
AllowBlank = true,
ShowInputMessage = true,
ShowErrorMessage = true,
Formula1 = new Formula1(formula),
SequenceOfReferences =
new ListValue<StringValue>()
{
InnerText = string.Format("{0}{1}:{0}{2}", columnName, firstRow, lastRow)
}
};
validations.Add(validation);
}
return validations;
}
