public void CleanColumns()
{
this.newCols = new CT_Cols();
List<CT_Cols> colsArray = worksheet.GetColsArray();
if (null != colsArray)
{
int i = 0;
for (i = 0; i < colsArray.Count; i++)
{
CT_Cols cols = colsArray[i];
List<CT_Col> colArray = cols.GetColArray();
for (int y = 0; y < colArray.Count; y++)
{
CT_Col col = colArray[y];
newCols = AddCleanColIntoCols(newCols, col);
}
}
for (int y = i - 1; y >= 0; y--)
{
worksheet.RemoveCols(y);
}
}
worksheet.AddNewCols();
worksheet.SetColsArray(0, newCols);
}
public void TestAddCleanColIntoCols()
{
CT_Worksheet worksheet = new CT_Worksheet();
ColumnHelper helper = new ColumnHelper(worksheet);
CT_Cols cols1 = new CT_Cols();
CT_Col col1 = cols1.AddNewCol();
col1.min = (1);
col1.max = (1);
col1.width = (88);
col1.hidden = (true);
CT_Col col2 = cols1.AddNewCol();
col2.min = (2);
col2.max = (3);
CT_Col col3 = cols1.AddNewCol();
col3.min = (13);
col3.max = (16750);
Assert.AreEqual(3, cols1.sizeOfColArray());
CT_Col col4 = cols1.AddNewCol();
col4.min = (8);
col4.max = (9);
Assert.AreEqual(4, cols1.sizeOfColArray());
CT_Col col5 = new CT_Col();
col5.min = (4);
col5.max = (5);
helper.AddCleanColIntoCols(cols1, col5);
Assert.AreEqual(5, cols1.sizeOfColArray());
CT_Col col6 = new CT_Col();
col6.min = (8);
col6.max = (11);
col6.hidden = (true);
helper.AddCleanColIntoCols(cols1, col6);
Assert.AreEqual(6, cols1.sizeOfColArray());
CT_Col col7 = new CT_Col();
col7.min = (6);
col7.max = (8);
col7.width = (17.0);
helper.AddCleanColIntoCols(cols1, col7);
Assert.AreEqual(8, cols1.sizeOfColArray());
CT_Col col8 = new CT_Col();
col8.min = (20);
col8.max = (30);
helper.AddCleanColIntoCols(cols1, col8);
Assert.AreEqual(10, cols1.sizeOfColArray());
CT_Col col9 = new CT_Col();
col9.min = (25);
col9.max = (27);
helper.AddCleanColIntoCols(cols1, col9);
// TODO - assert something interesting
Assert.AreEqual(12, cols1.col.Count);
Assert.AreEqual(1u, cols1.GetColArray(0).min);
Assert.AreEqual(16750u, cols1.GetColArray(11).max);
}
public CT_Col CloneCol(CT_Cols cols, CT_Col col)
{
CT_Col newCol = cols.AddNewCol();
newCol.min = (uint)(col.min);
newCol.max = (uint)(col.max);
SetColumnAttributes(col, newCol);
return newCol;
}
public static CT_Cols Parse(XmlNode node, XmlNamespaceManager namespaceManager)
{
if (node == null)
return null;
CT_Cols ctObj = new CT_Cols();
ctObj.col = new List<CT_Col>();
foreach (XmlNode childNode in node.ChildNodes)
{
if (childNode.LocalName == "col")
ctObj.col.Add(CT_Col.Parse(childNode, namespaceManager));
}
return ctObj;
}
public void TestSortColumns()
{
//CT_Worksheet worksheet = new CT_Worksheet();
//ColumnHelper helper = new ColumnHelper(worksheet);
CT_Cols cols1 = new CT_Cols();
CT_Col col1 = cols1.AddNewCol();
col1.min = (1);
col1.max = (1);
col1.width = (88);
col1.hidden = (true);
CT_Col col2 = cols1.AddNewCol();
col2.min = (2);
col2.max = (3);
CT_Col col3 = cols1.AddNewCol();
col3.min = (13);
col3.max = (16750);
Assert.AreEqual(3, cols1.sizeOfColArray());
CT_Col col4 = cols1.AddNewCol();
col4.min = (8);
col4.max = (11);
Assert.AreEqual(4, cols1.sizeOfColArray());
CT_Col col5 = cols1.AddNewCol();
col5.min = (4);
col5.max = (5);
Assert.AreEqual(5, cols1.sizeOfColArray());
CT_Col col6 = cols1.AddNewCol();
col6.min = (8);
col6.max = (9);
col6.hidden = (true);
CT_Col col7 = cols1.AddNewCol();
col7.min = (6);
col7.max = (8);
col7.width = (17.0);
CT_Col col8 = cols1.AddNewCol();
col8.min = (25);
col8.max = (27);
CT_Col col9 = cols1.AddNewCol();
col9.min = (20);
col9.max = (30);
Assert.AreEqual(9, cols1.sizeOfColArray());
Assert.AreEqual(20u, cols1.GetColArray(8).min);
Assert.AreEqual(30u, cols1.GetColArray(8).max);
ColumnHelper.SortColumns(cols1);
Assert.AreEqual(9, cols1.sizeOfColArray());
Assert.AreEqual(25u, cols1.GetColArray(8).min);
Assert.AreEqual(27u, cols1.GetColArray(8).max);
}
public CT_Cols AddNewCols()
{
if (null == colsField) { colsField = new List<CT_Cols>(); }
CT_Cols newCols = new CT_Cols();
this.colsField.Add(newCols);
return newCols;
}
//YK: GetXYZArray() array accessors are deprecated in xmlbeans with JDK 1.5 support
public static void SortColumns(CT_Cols newCols)
{
List<CT_Col> colArray = newCols.GetColArray();
colArray.Sort(new CTColComparator());
newCols.SetColArray(colArray);
}
public int GetIndexOfColumn(CT_Cols cols, CT_Col col)
{
for (int i = 0; i < cols.sizeOfColArray(); i++)
{
if (cols.GetColArray(i).min == col.min && cols.GetColArray(i).max == col.max)
{
return i;
}
}
return -1;
}
/**
* Do not leave the width attribute undefined (see #52186).
*/
private void SetColWidthAttribute(CT_Cols ctCols) {
foreach (CT_Col col in ctCols.GetColList())
{
if (!col.IsSetWidth()) {
col.width = (DefaultColumnWidth);
col.customWidth = (false);
}
}
}
private bool columnExists1Based(CT_Cols cols, long index1)
{
for (int i = 0; i < cols.sizeOfColArray(); i++)
{
if (cols.GetColArray(i).min == index1)
{
return true;
}
}
return false;
}
/**
* Does the column at the given 0 based index exist
* in the supplied list of column defInitions?
*/
public bool columnExists(CT_Cols cols, long index)
{
return columnExists1Based(cols, index + 1);
}
/*
* Insert a new CT_Col at position 0 into cols, Setting min=min, max=max and
* copying all the colsWithAttributes array cols attributes into newCol
*/
private CT_Col insertCol(CT_Cols cols, long min, long max,
CT_Col[] colsWithAttributes)
{
if (!columnExists(cols, min, max))
{
CT_Col newCol = cols.InsertNewCol(0);
newCol.min = (uint)(min);
newCol.max = (uint)(max);
foreach (CT_Col col in colsWithAttributes)
{
SetColumnAttributes(col, newCol);
}
return newCol;
}
return null;
}
public CT_Cols AddCleanColIntoCols(CT_Cols cols, CT_Col col)
{
bool colOverlaps = false;
for (int i = 0; i < cols.sizeOfColArray(); i++)
{
CT_Col ithCol = cols.GetColArray(i);
long[] range1 = { ithCol.min, ithCol.max };
long[] range2 = { col.min, col.max };
long[] overlappingRange = NumericRanges.GetOverlappingRange(range1,
range2);
int overlappingType = NumericRanges.GetOverlappingType(range1,
range2);
// different behavior required for each of the 4 different
// overlapping types
if (overlappingType == NumericRanges.OVERLAPS_1_MINOR)
{
ithCol.max = (uint)(overlappingRange[0] - 1);
CT_Col rangeCol = insertCol(cols, overlappingRange[0],
overlappingRange[1], new CT_Col[] { ithCol, col });
i++;
CT_Col newCol = insertCol(cols, (overlappingRange[1] + 1), col
.max, new CT_Col[] { col });
i++;
}
else if (overlappingType == NumericRanges.OVERLAPS_2_MINOR)
{
ithCol.min = (uint)(overlappingRange[1] + 1);
CT_Col rangeCol = insertCol(cols, overlappingRange[0],
overlappingRange[1], new CT_Col[] { ithCol, col });
i++;
CT_Col newCol = insertCol(cols, col.min,
(overlappingRange[0] - 1), new CT_Col[] { col });
i++;
}
else if (overlappingType == NumericRanges.OVERLAPS_2_WRAPS)
{
SetColumnAttributes(col, ithCol);
if (col.min != ithCol.min)
{
CT_Col newColBefore = insertCol(cols, col.min, (ithCol
.min - 1), new CT_Col[] { col });
i++;
}
if (col.max != ithCol.max)
{
CT_Col newColAfter = insertCol(cols, (ithCol.max + 1),
col.max, new CT_Col[] { col });
i++;
}
}
else if (overlappingType == NumericRanges.OVERLAPS_1_WRAPS)
{
if (col.min != ithCol.min)
{
CT_Col newColBefore = insertCol(cols, ithCol.min, (col
.min - 1), new CT_Col[] { ithCol });
i++;
}
if (col.max != ithCol.max)
{
CT_Col newColAfter = insertCol(cols, (col.max + 1),
ithCol.max, new CT_Col[] { ithCol });
i++;
}
ithCol.min = (uint)(overlappingRange[0]);
ithCol.max = (uint)(overlappingRange[1]);
SetColumnAttributes(col, ithCol);
}
if (overlappingType != NumericRanges.NO_OVERLAPS)
{
colOverlaps = true;
}
}
if (!colOverlaps)
{
CT_Col newCol = CloneCol(cols, col);
}
SortColumns(cols);
return cols;
}
public void TestCloneCol()
{
CT_Worksheet worksheet = new CT_Worksheet();
ColumnHelper helper = new ColumnHelper(worksheet);
CT_Cols cols = new CT_Cols();
CT_Col col = new CT_Col();
col.min = (2);
col.max = (8);
col.hidden = (true);
col.width = (13.4);
CT_Col newCol = helper.CloneCol(cols, col);
Assert.AreEqual(2u, newCol.min);
Assert.AreEqual(8u, newCol.max);
Assert.IsTrue(newCol.hidden);
Assert.AreEqual(13.4, newCol.width, 0.0);
}
public void SetColsArray(int index, CT_Cols newCols)
{
if (null == colsField)
{
colsField = new List<CT_Cols>();
}
else
{
colsField.Clear();
}
this.colsField.Insert(index, newCols);
}
public static CT_Worksheet Parse(XmlNode node, XmlNamespaceManager namespaceManager)
{
if (node == null)
{
return(null);
}
CT_Worksheet ctObj = new CT_Worksheet();
ctObj.cols = new List <CT_Cols>();
ctObj.conditionalFormatting = new List <CT_ConditionalFormatting>();
foreach (XmlNode childNode in node.ChildNodes)
{
if (childNode.LocalName == "sheetPr")
{
ctObj.sheetPr = CT_SheetPr.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "dimension")
{
ctObj.dimension = CT_SheetDimension.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "sheetViews")
{
ctObj.sheetViews = CT_SheetViews.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "sheetFormatPr")
{
ctObj.sheetFormatPr = CT_SheetFormatPr.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "sheetData")
{
ctObj.sheetData = CT_SheetData.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "sheetCalcPr")
{
ctObj.sheetCalcPr = CT_SheetCalcPr.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "sheetProtection")
{
ctObj.sheetProtection = CT_SheetProtection.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "protectedRanges")
{
ctObj.protectedRanges = CT_ProtectedRanges.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "scenarios")
{
ctObj.scenarios = CT_Scenarios.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "autoFilter")
{
ctObj.autoFilter = CT_AutoFilter.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "sortState")
{
ctObj.sortState = CT_SortState.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "dataConsolidate")
{
ctObj.dataConsolidate = CT_DataConsolidate.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "customSheetViews")
{
ctObj.customSheetViews = CT_CustomSheetViews.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "mergeCells")
{
ctObj.mergeCells = CT_MergeCells.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "phoneticPr")
{
ctObj.phoneticPr = CT_PhoneticPr.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "dataValidations")
{
ctObj.dataValidations = CT_DataValidations.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "hyperlinks")
{
ctObj.hyperlinks = CT_Hyperlinks.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "printOptions")
{
ctObj.printOptions = CT_PrintOptions.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "pageMargins")
{
ctObj.pageMargins = CT_PageMargins.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "pageSetup")
{
ctObj.pageSetup = CT_PageSetup.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "headerFooter")
{
ctObj.headerFooter = CT_HeaderFooter.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "rowBreaks")
{
ctObj.rowBreaks = CT_PageBreak.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "colBreaks")
{
ctObj.colBreaks = CT_PageBreak.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "customProperties")
{
ctObj.customProperties = CT_CustomProperties.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "cellWatches")
{
ctObj.cellWatches = CT_CellWatches.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "ignoredErrors")
{
ctObj.ignoredErrors = CT_IgnoredErrors.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "smartTags")
{
ctObj.smartTags = CT_CellSmartTags.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "drawing")
{
ctObj.drawing = CT_Drawing.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "legacyDrawing")
{
ctObj.legacyDrawing = CT_LegacyDrawing.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "legacyDrawingHF")
{
ctObj.legacyDrawingHF = CT_LegacyDrawing.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "picture")
{
ctObj.picture = CT_SheetBackgroundPicture.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "oleObjects")
{
ctObj.oleObjects = CT_OleObjects.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "controls")
{
ctObj.controls = CT_Controls.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "webPublishItems")
{
ctObj.webPublishItems = CT_WebPublishItems.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "tableParts")
{
ctObj.tableParts = CT_TableParts.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "extLst")
{
ctObj.extLst = CT_ExtensionList.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "cols")
{
ctObj.cols.Add(CT_Cols.Parse(childNode, namespaceManager));
}
else if (childNode.LocalName == "conditionalFormatting")
{
ctObj.conditionalFormatting.Add(CT_ConditionalFormatting.Parse(childNode, namespaceManager));
}
}
return(ctObj);
}
private bool columnExists(CT_Cols cols, long min, long max)
{
for (int i = 0; i < cols.sizeOfColArray(); i++)
{
if (cols.GetColArray(i).min == min && cols.GetColArray(i).max == max)
{
return true;
}
}
return false;
}
