public void TestReadTextBox()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("WithDrawing.xlsx");
XSSFSheet sheet = wb.GetSheetAt(0) as XSSFSheet;
//the sheet has one relationship and it is XSSFDrawing
List <POIXMLDocumentPart.RelationPart> rels = sheet.RelationParts;
Assert.AreEqual(1, rels.Count);
POIXMLDocumentPart.RelationPart rp = rels[0];
Assert.IsTrue(rp.DocumentPart is XSSFDrawing);
XSSFDrawing drawing = (XSSFDrawing)rp.DocumentPart;
//sheet.CreateDrawingPatriarch() should return the same instance of XSSFDrawing
Assert.AreSame(drawing, sheet.CreateDrawingPatriarch());
String drawingId = rp.Relationship.Id;
//there should be a relation to this Drawing in the worksheet
Assert.IsTrue(sheet.GetCTWorksheet().IsSetDrawing());
Assert.AreEqual(drawingId, sheet.GetCTWorksheet().drawing.id);
List <XSSFShape> shapes = drawing.GetShapes();
Assert.AreEqual(6, shapes.Count);
Assert.IsTrue(shapes[4] is XSSFSimpleShape);
XSSFSimpleShape textbox = (XSSFSimpleShape)shapes[4];
Assert.AreEqual("Sheet with various pictures\n(jpeg, png, wmf, emf and pict)", textbox.Text);
checkRewrite(wb);
wb.Close();
}
public void TestNoColsWithoutWidthWhenGroupingAndCollapsing()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet("test");
sheet.SetColumnWidth(4, 5000);
sheet.SetColumnWidth(5, 5000);
sheet.GroupColumn((short)4, (short)5);
sheet.SetColumnGroupCollapsed(4, true);
CT_Cols cols = sheet.GetCTWorksheet().GetColsArray(0);
//logger.log(POILogger.DEBUG, "test52186_2/cols:" + cols);
wb = XSSFTestDataSamples.WriteOutAndReadBack(wb, "testNoColsWithoutWidthWhenGroupingAndCollapsing");
sheet = (XSSFSheet)wb.GetSheet("test");
for (int i = 4; i <= 5; i++)
{
Assert.AreEqual(5000, sheet.GetColumnWidth(i), "Unexpected width of column " + i);
}
cols = sheet.GetCTWorksheet().GetColsArray(0);
foreach (CT_Col col in cols.GetColList())
{
Assert.IsTrue(col.IsSetWidth(), "Col width attribute is unset: " + col.ToString());
}
}
public void SetTabColor()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sh = wb.CreateSheet() as XSSFSheet;
Assert.IsTrue(sh.GetCTWorksheet().sheetPr == null || !sh.GetCTWorksheet().sheetPr.IsSetTabColor());
sh.SetTabColor(IndexedColors.Red.Index);
Assert.IsTrue(sh.GetCTWorksheet().sheetPr.IsSetTabColor());
Assert.AreEqual(IndexedColors.Red.Index,
sh.GetCTWorksheet().sheetPr.tabColor.indexed);
}
public void TestNew()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet();
//multiple calls of CreateDrawingPatriarch should return the same instance of XSSFDrawing
XSSFDrawing dr1 = (XSSFDrawing)sheet.CreateDrawingPatriarch();
XSSFDrawing dr2 = (XSSFDrawing)sheet.CreateDrawingPatriarch();
Assert.AreSame(dr1, dr2);
List <POIXMLDocumentPart> rels = sheet.GetRelations();
Assert.AreEqual(1, rels.Count);
Assert.IsTrue(rels[0] is XSSFDrawing);
XSSFDrawing drawing = (XSSFDrawing)rels[0];
String drawingId = drawing.GetPackageRelationship().Id;
//there should be a relation to this Drawing in the worksheet
Assert.IsTrue(sheet.GetCTWorksheet().IsSetDrawing());
Assert.AreEqual(drawingId, sheet.GetCTWorksheet().drawing.id);
XSSFClientAnchor anchor = new XSSFClientAnchor();
XSSFConnector c1 = drawing.CreateConnector(anchor);
c1.LineWidth = 3;
c1.LineStyle = SS.UserModel.LineStyle.DashDotSys;
XSSFShapeGroup c2 = drawing.CreateGroup(anchor);
XSSFSimpleShape c3 = drawing.CreateSimpleShape(anchor);
c3.SetText(new XSSFRichTextString("Test String"));
c3.SetFillColor(128, 128, 128);
XSSFTextBox c4 = (XSSFTextBox)drawing.CreateTextbox(anchor);
XSSFRichTextString rt = new XSSFRichTextString("Test String");
rt.ApplyFont(0, 5, wb.CreateFont());
rt.ApplyFont(5, 6, wb.CreateFont());
c4.SetText(rt);
c4.IsNoFill = (true);
}
public void TestMergingOverlappingCols_OVERLAPS_2_MINOR()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet("test");
CT_Cols cols = sheet.GetCTWorksheet().GetColsArray(0);
CT_Col col = cols.AddNewCol();
col.min = (2 + 1);
col.max = (4 + 1);
col.width = (20);
col.customWidth = (true);
sheet.GroupColumn((short)1, (short)3);
cols = sheet.GetCTWorksheet().GetColsArray(0);
//logger.log(POILogger.DEBUG, "testMergingOverlappingCols_OVERLAPS_2_MINOR/cols:" + cols);
Assert.AreEqual(1, cols.GetColArray(0).outlineLevel);
Assert.AreEqual(2, cols.GetColArray(0).min); // 1 based
Assert.AreEqual(2, cols.GetColArray(0).max); // 1 based
Assert.AreEqual(false, cols.GetColArray(0).customWidth);
Assert.AreEqual(1, cols.GetColArray(1).outlineLevel);
Assert.AreEqual(3, cols.GetColArray(1).min); // 1 based
Assert.AreEqual(4, cols.GetColArray(1).max); // 1 based
Assert.AreEqual(true, cols.GetColArray(1).customWidth);
Assert.AreEqual(0, cols.GetColArray(2).outlineLevel);
Assert.AreEqual(5, cols.GetColArray(2).min); // 1 based
Assert.AreEqual(5, cols.GetColArray(2).max); // 1 based
Assert.AreEqual(true, cols.GetColArray(2).customWidth);
Assert.AreEqual(3, cols.sizeOfColArray());
wb = XSSFTestDataSamples.WriteOutAndReadBack(wb, "testMergingOverlappingCols_OVERLAPS_2_MINOR");
sheet = (XSSFSheet)wb.GetSheet("test");
for (int i = 2; i <= 4; i++)
{
Assert.AreEqual(20 * 256, sheet.GetColumnWidth(i), "Unexpected width of column " + i);
}
Assert.AreEqual(sheet.DefaultColumnWidth * 256, sheet.GetColumnWidth(1), "Unexpected width of column " + 1);
}
public void TestRead()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("WithDrawing.xlsx");
XSSFSheet sheet = (XSSFSheet)wb.GetSheetAt(0);
//the sheet has one relationship and it is XSSFDrawing
List <RelationPart> rels = sheet.RelationParts;
Assert.AreEqual(1, rels.Count);
RelationPart rp = rels[0];
Assert.IsTrue(rp.DocumentPart is XSSFDrawing);
XSSFDrawing drawing = (XSSFDrawing)rp.DocumentPart;
//sheet.CreateDrawingPatriarch() should return the same instance of XSSFDrawing
Assert.AreSame(drawing, sheet.CreateDrawingPatriarch());
String drawingId = rp.Relationship.Id;
//there should be a relation to this Drawing in the worksheet
Assert.IsTrue(sheet.GetCTWorksheet().IsSetDrawing());
Assert.AreEqual(drawingId, sheet.GetCTWorksheet().drawing.id);
List <XSSFShape> shapes = drawing.GetShapes();
Assert.AreEqual(6, shapes.Count);
Assert.IsTrue(shapes[(0)] is XSSFPicture);
Assert.IsTrue(shapes[(1)] is XSSFPicture);
Assert.IsTrue(shapes[(2)] is XSSFPicture);
Assert.IsTrue(shapes[(3)] is XSSFPicture);
Assert.IsTrue(shapes[(4)] is XSSFSimpleShape);
Assert.IsTrue(shapes[(5)] is XSSFPicture);
foreach (XSSFShape sh in shapes)
{
Assert.IsNotNull(sh.GetAnchor());
}
checkRewrite(wb);
wb.Close();
}
public void TestRead()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("WithDrawing.xlsx");
XSSFSheet sheet = (XSSFSheet)wb.GetSheetAt(0);
//the sheet has one relationship and it is XSSFDrawing
List <POIXMLDocumentPart> rels = sheet.GetRelations();
Assert.AreEqual(1, rels.Count);
Assert.IsTrue(rels[0] is XSSFDrawing);
XSSFDrawing drawing = (XSSFDrawing)rels[0];
//sheet.CreateDrawingPatriarch() should return the same instance of XSSFDrawing
Assert.AreSame(drawing, sheet.CreateDrawingPatriarch());
String drawingId = drawing.GetPackageRelationship().Id;
//there should be a relation to this Drawing in the worksheet
Assert.IsTrue(sheet.GetCTWorksheet().IsSetDrawing());
Assert.AreEqual(drawingId, sheet.GetCTWorksheet().drawing.id);
}
private void SetWorksheetOutlineLevelRow()
{
var ct = _sh.GetCTWorksheet();
var pr = ct.IsSetSheetFormatPr() ?
ct.sheetFormatPr :
ct.AddNewSheetFormatPr();
if (outlineLevelRow > 0)
{
pr.outlineLevelRow = (byte)outlineLevelRow;
}
}
public void TestSetSaveRead()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet s1 = (XSSFSheet)wb.CreateSheet();
Assert.AreEqual(false, s1.GetCTWorksheet().IsSetPageSetup());
Assert.AreEqual(true, s1.GetCTWorksheet().IsSetPageMargins());
XSSFPrintSetup print = (XSSFPrintSetup)s1.PrintSetup;
Assert.AreEqual(true, s1.GetCTWorksheet().IsSetPageSetup());
Assert.AreEqual(true, s1.GetCTWorksheet().IsSetPageMargins());
print.Copies = ((short)3);
print.Landscape = (true);
Assert.AreEqual(3, print.Copies);
Assert.AreEqual(true, print.Landscape);
XSSFSheet s2 = (XSSFSheet)wb.CreateSheet();
Assert.AreEqual(false, s2.GetCTWorksheet().IsSetPageSetup());
Assert.AreEqual(true, s2.GetCTWorksheet().IsSetPageMargins());
// Round trip and check
XSSFWorkbook wbBack = (XSSFWorkbook)XSSFITestDataProvider.instance.WriteOutAndReadBack(wb);
s1 = (XSSFSheet)wbBack.GetSheetAt(0);
s2 = (XSSFSheet)wbBack.GetSheetAt(1);
Assert.AreEqual(true, s1.GetCTWorksheet().IsSetPageSetup());
Assert.AreEqual(true, s1.GetCTWorksheet().IsSetPageMargins());
Assert.AreEqual(false, s2.GetCTWorksheet().IsSetPageSetup());
Assert.AreEqual(true, s2.GetCTWorksheet().IsSetPageMargins());
print = (XSSFPrintSetup)s1.PrintSetup;
Assert.AreEqual(3, print.Copies);
Assert.AreEqual(true, print.Landscape);
wb.Close();
}
public int AddConditionalFormatting(CellRangeAddress[] regions, IConditionalFormattingRule[] cfRules)
{
if (regions == null)
{
throw new ArgumentException("regions must not be null");
}
foreach (CellRangeAddress range in regions)
{
range.Validate(SpreadsheetVersion.EXCEL2007);
}
if (cfRules == null)
{
throw new ArgumentException("cfRules must not be null");
}
if (cfRules.Length == 0)
{
throw new ArgumentException("cfRules must not be empty");
}
if (cfRules.Length > 3)
{
throw new ArgumentException("Number of rules must not exceed 3");
}
XSSFConditionalFormattingRule[] hfRules;
if (cfRules is XSSFConditionalFormattingRule[])
{
hfRules = (XSSFConditionalFormattingRule[])cfRules;
}
else
{
hfRules = new XSSFConditionalFormattingRule[cfRules.Length];
Array.Copy(cfRules, 0, hfRules, 0, hfRules.Length);
}
CellRangeAddress[] mergeCellRanges = CellRangeUtil.MergeCellRanges(regions);
CT_ConditionalFormatting cf = _sheet.GetCTWorksheet().AddNewConditionalFormatting();
List <String> refs = new List <String>();
foreach (CellRangeAddress a in mergeCellRanges)
{
refs.Add(a.FormatAsString());
}
cf.sqref = (refs);
int priority = 1;
foreach (CT_ConditionalFormatting c in _sheet.GetCTWorksheet().conditionalFormatting)
{
priority += c.sizeOfCfRuleArray();
}
foreach (IConditionalFormattingRule rule in cfRules)
{
XSSFConditionalFormattingRule xRule = (XSSFConditionalFormattingRule)rule;
xRule.GetCTCfRule().priority = (priority++);
cf.AddNewCfRule().Set(xRule.GetCTCfRule());
}
return(_sheet.GetCTWorksheet().SizeOfConditionalFormattingArray() - 1);
}
public int AddConditionalFormatting(CellRangeAddress[] regions, IConditionalFormattingRule[] cfRules)
{
if (regions == null)
{
throw new ArgumentException("regions must not be null");
}
foreach (CellRangeAddress range in regions)
{
range.Validate(SpreadsheetVersion.EXCEL2007);
}
if (cfRules == null)
{
throw new ArgumentException("cfRules must not be null");
}
if (cfRules.Length == 0)
{
throw new ArgumentException("cfRules must not be empty");
}
CellRangeAddress[] mergeCellRanges = CellRangeUtil.MergeCellRanges(regions);
CT_ConditionalFormatting cf = _sheet.GetCTWorksheet().AddNewConditionalFormatting();
string refs = string.Empty;
foreach (CellRangeAddress a in mergeCellRanges)
{
if (refs.Length == 0)
{
refs = a.FormatAsString();
}
else
{
refs += " " + a.FormatAsString();
}
}
cf.sqref = refs;
int priority = 1;
foreach (CT_ConditionalFormatting c in _sheet.GetCTWorksheet().conditionalFormatting)
{
priority += c.sizeOfCfRuleArray();
}
foreach (IConditionalFormattingRule rule in cfRules)
{
XSSFConditionalFormattingRule xRule = (XSSFConditionalFormattingRule)rule;
xRule.GetCTCfRule().priority = (priority++);
cf.AddNewCfRule().Set(xRule.GetCTCfRule());
}
return(_sheet.GetCTWorksheet().SizeOfConditionalFormattingArray() - 1);
}
public void TestBug57423()
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet();
CT_Worksheet wsh = sheet.GetCTWorksheet();
CT_SheetData sheetData = wsh.sheetData;
XSSFRow row1 = (XSSFRow)sheet.CreateRow(0);
row1.CreateCell(0).SetCellValue("a");
XSSFRow row2 = (XSSFRow)sheet.CreateRow(1);
row2.CreateCell(0).SetCellValue("b");
XSSFRow row3 = (XSSFRow)sheet.CreateRow(2);
row3.CreateCell(0).SetCellValue("c");
sheet.ShiftRows(0, 1, 3); //move "a" and "b" 3 rows down
// Before: After:
// A A
// 1 a <empty>
// 2 b <empty>
// 3 c c
// 4 a
// 5 b
List <CT_Row> xrow = sheetData.row;
Assert.AreEqual(3, xrow.Count);
// Rows are sorted: [3, 4, 5]
Assert.AreEqual(3u, xrow[0].r);
Assert.IsTrue(xrow[0].Equals(row3.GetCTRow()));
Assert.AreEqual(4u, xrow[1].r);
Assert.IsTrue(xrow[1].Equals(row1.GetCTRow()));
Assert.AreEqual(5u, xrow[2].r);
Assert.IsTrue(xrow[2].Equals(row2.GetCTRow()));
}
public void TestNoColsWithoutWidthWhenGrouping()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet("test");
sheet.SetColumnWidth(4, 5000);
sheet.SetColumnWidth(5, 5000);
sheet.GroupColumn((short)4, (short)7);
sheet.GroupColumn((short)9, (short)12);
wb = XSSFTestDataSamples.WriteOutAndReadBack(wb, "testNoColsWithoutWidthWhenGrouping");
sheet = (XSSFSheet)wb.GetSheet("test");
CT_Cols cols = sheet.GetCTWorksheet().GetColsArray(0);
//logger.log(POILogger.DEBUG, "test52186/cols:" + cols);
foreach (CT_Col col in cols.GetColList())
{
Assert.IsTrue(col.IsSetWidth(), "Col width attribute is unset: " + col.ToString());
}
}
public void TestGetSetColDefaultStyle()
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet();
CT_Worksheet ctWorksheet = sheet.GetCTWorksheet();
ColumnHelper columnHelper = sheet.GetColumnHelper();
// POI column 3, OOXML column 4
CT_Col col = columnHelper.GetOrCreateColumn1Based(4, false);
Assert.IsNotNull(col);
Assert.IsNotNull(columnHelper.GetColumn(3, false));
columnHelper.SetColDefaultStyle(3, 2);
Assert.AreEqual(2, columnHelper.GetColDefaultStyle(3));
Assert.AreEqual(-1, columnHelper.GetColDefaultStyle(4));
StylesTable stylesTable = workbook.GetStylesSource();
CT_Xf cellXf = new CT_Xf();
cellXf.fontId = (0);
cellXf.fillId = (0);
cellXf.borderId = (0);
cellXf.numFmtId = (0);
cellXf.xfId = (0);
stylesTable.PutCellXf(cellXf);
CT_Col col_2 = ctWorksheet.GetColsArray(0).AddNewCol();
col_2.min = (10);
col_2.max = (12);
col_2.style = (1);
col_2.styleSpecified = true;
Assert.AreEqual(1, columnHelper.GetColDefaultStyle(11));
XSSFCellStyle cellStyle = new XSSFCellStyle(0, 0, stylesTable, null);
columnHelper.SetColDefaultStyle(11, cellStyle);
Assert.AreEqual(0u, col_2.style);
Assert.AreEqual(1, columnHelper.GetColDefaultStyle(10));
}
public void TestNew()
{
XSSFWorkbook wb1 = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb1.CreateSheet();
//multiple calls of CreateDrawingPatriarch should return the same instance of XSSFDrawing
XSSFDrawing dr1 = (XSSFDrawing)sheet.CreateDrawingPatriarch();
XSSFDrawing dr2 = (XSSFDrawing)sheet.CreateDrawingPatriarch();
Assert.AreSame(dr1, dr2);
List <POIXMLDocumentPart.RelationPart> rels = sheet.RelationParts;
Assert.AreEqual(1, rels.Count);
POIXMLDocumentPart.RelationPart rp = rels[0];
Assert.IsTrue(rp.DocumentPart is XSSFDrawing);
XSSFDrawing drawing = (XSSFDrawing)rp.DocumentPart;
String drawingId = rp.Relationship.Id;
//there should be a relation to this Drawing in the worksheet
Assert.IsTrue(sheet.GetCTWorksheet().IsSetDrawing());
Assert.AreEqual(drawingId, sheet.GetCTWorksheet().drawing.id);
//XSSFClientAnchor anchor = new XSSFClientAnchor();
XSSFConnector c1 = drawing.CreateConnector(new XSSFClientAnchor(0, 0, 0, 0, 0, 0, 2, 2));
c1.LineWidth = 2.5;
c1.LineStyle = LineStyle.DashDotSys;
XSSFShapeGroup c2 = drawing.CreateGroup(new XSSFClientAnchor(0, 0, 0, 0, 0, 0, 5, 5));
Assert.IsNotNull(c2);
XSSFSimpleShape c3 = drawing.CreateSimpleShape(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4));
c3.SetText(new XSSFRichTextString("Test String"));
c3.SetFillColor(128, 128, 128);
XSSFTextBox c4 = (XSSFTextBox)drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 4, 4, 5, 6));
XSSFRichTextString rt = new XSSFRichTextString("Test String");
rt.ApplyFont(0, 5, wb1.CreateFont());
rt.ApplyFont(5, 6, wb1.CreateFont());
c4.SetText(rt);
c4.IsNoFill = (true);
Assert.AreEqual(4, drawing.GetCTDrawing().SizeOfTwoCellAnchorArray());
List <XSSFShape> shapes = drawing.GetShapes();
Assert.AreEqual(4, shapes.Count);
Assert.IsTrue(shapes[(0)] is XSSFConnector);
Assert.IsTrue(shapes[(1)] is XSSFShapeGroup);
Assert.IsTrue(shapes[(2)] is XSSFSimpleShape);
Assert.IsTrue(shapes[(3)] is XSSFSimpleShape);
// Save and re-load it
XSSFWorkbook wb2 = XSSFTestDataSamples.WriteOutAndReadBack(wb1) as XSSFWorkbook;
wb1.Close();
sheet = wb2.GetSheetAt(0) as XSSFSheet;
// Check
dr1 = sheet.CreateDrawingPatriarch() as XSSFDrawing;
CT_Drawing ctDrawing = dr1.GetCTDrawing();
// Connector, shapes and text boxes are all two cell anchors
Assert.AreEqual(0, ctDrawing.SizeOfAbsoluteAnchorArray());
Assert.AreEqual(0, ctDrawing.SizeOfOneCellAnchorArray());
Assert.AreEqual(4, ctDrawing.SizeOfTwoCellAnchorArray());
shapes = dr1.GetShapes();
Assert.AreEqual(4, shapes.Count);
Assert.IsTrue(shapes[0] is XSSFConnector);
Assert.IsTrue(shapes[1] is XSSFShapeGroup);
Assert.IsTrue(shapes[2] is XSSFSimpleShape);
Assert.IsTrue(shapes[3] is XSSFSimpleShape); //
// Ensure it got the right namespaces
//String xml = ctDrawing.ToString();
//Assert.IsTrue(xml.Contains("xmlns:xdr=\"http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing\""));
//Assert.IsTrue(xml.Contains("xmlns:a=\"http://schemas.openxmlformats.org/drawingml/2006/main\""));
checkRewrite(wb2);
wb2.Close();
}
public void TestReadTextBoxParagraphs()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("WithTextBox.xlsx");
XSSFSheet sheet = wb.GetSheetAt(0) as XSSFSheet;
//the sheet has one relationship and it is XSSFDrawing
List <POIXMLDocumentPart.RelationPart> rels = sheet.RelationParts;
Assert.AreEqual(1, rels.Count);
POIXMLDocumentPart.RelationPart rp = rels[0];
Assert.IsTrue(rp.DocumentPart is XSSFDrawing);
XSSFDrawing drawing = (XSSFDrawing)rp.DocumentPart;
//sheet.CreateDrawingPatriarch() should return the same instance of XSSFDrawing
Assert.AreSame(drawing, sheet.CreateDrawingPatriarch());
String drawingId = rp.Relationship.Id;
//there should be a relation to this Drawing in the worksheet
Assert.IsTrue(sheet.GetCTWorksheet().IsSetDrawing());
Assert.AreEqual(drawingId, sheet.GetCTWorksheet().drawing.id);
List <XSSFShape> shapes = drawing.GetShapes();
Assert.AreEqual(1, shapes.Count);
Assert.IsTrue(shapes[0] is XSSFSimpleShape);
XSSFSimpleShape textbox = (XSSFSimpleShape)shapes[0];
List <XSSFTextParagraph> paras = textbox.TextParagraphs;
Assert.AreEqual(3, paras.Count);
Assert.AreEqual("Line 2", paras[1].Text); // check content of second paragraph
Assert.AreEqual("Line 1\nLine 2\nLine 3", textbox.Text); // check content of entire textbox
// check attributes of paragraphs
Assert.AreEqual(TextAlign.LEFT, paras[0].TextAlign);
Assert.AreEqual(TextAlign.CENTER, paras[1].TextAlign);
Assert.AreEqual(TextAlign.RIGHT, paras[2].TextAlign);
var clr = paras[0].TextRuns[0].FontColor;
Assert.IsTrue(Arrays.Equals(
new int[] { 255, 0, 0 },
new int[] { clr.R, clr.G, clr.B }));
clr = paras[1].TextRuns[0].FontColor;
Assert.IsTrue(Arrays.Equals(
new int[] { 0, 255, 0 },
new int[] { clr.R, clr.G, clr.B }));
clr = paras[2].TextRuns[0].FontColor;
Assert.IsTrue(Arrays.Equals(
new int[] { 0, 0, 255 },
new int[] { clr.R, clr.G, clr.B }));
checkRewrite(wb);
wb.Close();
}
public void UpdateConditionalFormatting(FormulaShifter Shifter)
{
IWorkbook wb = sheet.Workbook;
int sheetIndex = wb.GetSheetIndex(sheet);
XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(wb);
CT_Worksheet ctWorksheet = sheet.GetCTWorksheet();
List <CT_ConditionalFormatting> conditionalFormattingArray = ctWorksheet.conditionalFormatting;
// iterate backwards due to possible calls to ctWorksheet.removeConditionalFormatting(j)
for (int j = conditionalFormattingArray.Count - 1; j >= 0; j--)
{
CT_ConditionalFormatting cf = conditionalFormattingArray[j];
List <CellRangeAddress> cellRanges = new List <CellRangeAddress>();
String[] regions = cf.sqref.ToString().Split(new char[] { ' ' });
for (int i = 0; i < regions.Length; i++)
{
cellRanges.Add(CellRangeAddress.ValueOf(regions[i]));
}
bool Changed = false;
List <CellRangeAddress> temp = new List <CellRangeAddress>();
for (int i = 0; i < cellRanges.Count; i++)
{
CellRangeAddress craOld = cellRanges[i];
CellRangeAddress craNew = ShiftRange(Shifter, craOld, sheetIndex);
if (craNew == null)
{
Changed = true;
continue;
}
temp.Add(craNew);
if (craNew != craOld)
{
Changed = true;
}
}
if (Changed)
{
int nRanges = temp.Count;
if (nRanges == 0)
{
conditionalFormattingArray.RemoveAt(j);
continue;
}
string refs = string.Empty;
foreach (CellRangeAddress a in temp)
{
if (refs.Length == 0)
{
refs = a.FormatAsString();
}
else
{
refs += " " + a.FormatAsString();
}
}
cf.sqref = refs;
}
foreach (CT_CfRule cfRule in cf.cfRule)
{
List <String> formulas = cfRule.formula;
for (int i = 0; i < formulas.Count; i++)
{
String formula = formulas[i];
Ptg[] ptgs = FormulaParser.Parse(formula, fpb, FormulaType.Cell, sheetIndex);
if (Shifter.AdjustFormula(ptgs, sheetIndex))
{
String ShiftedFmla = FormulaRenderer.ToFormulaString(fpb, ptgs);
formulas[i] = ShiftedFmla;
}
}
}
}
}
public void UpdateConditionalFormatting(FormulaShifter Shifter)
{
IWorkbook wb = sheet.Workbook;
int sheetIndex = wb.GetSheetIndex(sheet);
XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(wb);
List <CT_ConditionalFormatting> cfList = sheet.GetCTWorksheet().conditionalFormatting;
for (int j = 0; cfList != null && j < cfList.Count; j++)
{
CT_ConditionalFormatting cf = cfList[j];
List <CellRangeAddress> cellRanges = new List <CellRangeAddress>();
foreach (Object stRef in cf.sqref)
{
String[] regions = stRef.ToString().Split(new char[] { ' ' });
for (int i = 0; i < regions.Length; i++)
{
cellRanges.Add(CellRangeAddress.ValueOf(regions[i]));
}
}
bool Changed = false;
List <CellRangeAddress> temp = new List <CellRangeAddress>();
for (int i = 0; i < cellRanges.Count; i++)
{
CellRangeAddress craOld = cellRanges[i];
CellRangeAddress craNew = ShiftRange(Shifter, craOld, sheetIndex);
if (craNew == null)
{
Changed = true;
continue;
}
temp.Add(craNew);
if (craNew != craOld)
{
Changed = true;
}
}
if (Changed)
{
int nRanges = temp.Count;
if (nRanges == 0)
{
cfList.RemoveAt(j);
continue;
}
List <String> refs = new List <String>();
foreach (CellRangeAddress a in temp)
{
refs.Add(a.FormatAsString());
}
cf.sqref = (refs);
}
foreach (CT_CfRule cfRule in cf.cfRule)
{
List <String> formulas = cfRule.formula;
for (int i = 0; i < formulas.Count; i++)
{
String formula = formulas[i];
Ptg[] ptgs = FormulaParser.Parse(formula, fpb, FormulaType.CELL, sheetIndex);
if (Shifter.AdjustFormula(ptgs, sheetIndex))
{
String ShiftedFmla = FormulaRenderer.ToFormulaString(fpb, ptgs);
formulas[i] = ShiftedFmla;
}
}
}
}
}