public void TestReadWrite()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("sample.xlsx");
SharedStringsTable sst1 = wb.GetSharedStringSource();
//Serialize, read back and compare with the original
SharedStringsTable sst2 = ((XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(wb)).GetSharedStringSource();
Assert.AreEqual(sst1.GetCount(), sst2.GetCount());
Assert.AreEqual(sst1.GetUniqueCount(), sst2.GetUniqueCount());
List <CT_Rst> items1 = sst1.GetItems();
List <CT_Rst> items2 = sst2.GetItems();
Assert.AreEqual(items1.Count, items2.Count);
for (int i = 0; i < items1.Count; i++)
{
CT_Rst st1 = items1[i];
CT_Rst st2 = items2[i];
Assert.AreEqual(st1.ToString(), st2.ToString());
}
}
/**
* Create a new CT_Workbook with all values Set to default
*/
private void OnWorkbookCreate()
{
doc = new WorkbookDocument();
workbook = doc.Workbook;
// don't EVER use the 1904 date system
CT_WorkbookPr workbookPr = workbook.AddNewWorkbookPr();
workbookPr.date1904 = (false);
CT_BookViews bvs = workbook.AddNewBookViews();
CT_BookView bv = bvs.AddNewWorkbookView();
bv.activeTab = 0;
workbook.AddNewSheets();
ExtendedProperties expProps = GetProperties().ExtendedProperties;
CT_ExtendedProperties ctExtendedProp= expProps.GetUnderlyingProperties();
ctExtendedProp.Application = DOCUMENT_CREATOR;
ctExtendedProp.DocSecurity = 0;
ctExtendedProp.DocSecuritySpecified = true;
ctExtendedProp.ScaleCrop = false;
ctExtendedProp.ScaleCropSpecified = true;
ctExtendedProp.LinksUpToDate = false;
ctExtendedProp.LinksUpToDateSpecified = true;
ctExtendedProp.HyperlinksChanged = false;
ctExtendedProp.HyperlinksChangedSpecified = true;
ctExtendedProp.SharedDoc = false;
ctExtendedProp.SharedDocSpecified = true;
sharedStringSource = (SharedStringsTable)CreateRelationship(XSSFRelation.SHARED_STRINGS, XSSFFactory.GetInstance());
stylesSource = (StylesTable)CreateRelationship(XSSFRelation.STYLES, XSSFFactory.GetInstance());
namedRanges = new List<XSSFName>();
sheets = new List<XSSFSheet>();
pivotTables = new List<XSSFPivotTable>();
}
internal override void OnDocumentRead()
{
try
{
XmlDocument xmldoc = ConvertStreamToXml(GetPackagePart().GetInputStream());
doc = WorkbookDocument.Parse(xmldoc, NamespaceManager);
this.workbook = doc.Workbook;
Dictionary<String, XSSFSheet> shIdMap = new Dictionary<String, XSSFSheet>();
Dictionary<String, ExternalLinksTable> elIdMap = new Dictionary<String, ExternalLinksTable>();
foreach (POIXMLDocumentPart p in GetRelations())
{
if (p is SharedStringsTable) sharedStringSource = (SharedStringsTable)p;
else if (p is StylesTable) stylesSource = (StylesTable)p;
else if (p is ThemesTable) theme = (ThemesTable)p;
else if (p is CalculationChain) calcChain = (CalculationChain)p;
else if (p is MapInfo) mapInfo = (MapInfo)p;
else if (p is XSSFSheet)
{
shIdMap.Add(p.GetPackageRelationship().Id,(XSSFSheet)p);
}
else if (p is ExternalLinksTable)
{
elIdMap.Add(p.GetPackageRelationship().Id, (ExternalLinksTable)p);
}
}
bool packageReadOnly = (Package.GetPackageAccess() == PackageAccess.READ);
if (stylesSource == null)
{
// Create Styles if it is missing
if (packageReadOnly)
{
stylesSource = new StylesTable();
}
else
{
stylesSource = (StylesTable)CreateRelationship(XSSFRelation.STYLES, XSSFFactory.GetInstance());
}
}
stylesSource.SetTheme(theme);
if (sharedStringSource == null)
{
//Create SST if it is missing
if (packageReadOnly)
{
sharedStringSource = new SharedStringsTable();
}
else
{
sharedStringSource = (SharedStringsTable)CreateRelationship(XSSFRelation.SHARED_STRINGS, XSSFFactory.GetInstance());
}
}
// Load individual sheets. The order of sheets is defined by the order
// of CTSheet elements in the workbook
sheets = new List<XSSFSheet>(shIdMap.Count);
foreach (CT_Sheet ctSheet in this.workbook.sheets.sheet)
{
XSSFSheet sh = null;
if(shIdMap.ContainsKey(ctSheet.id))
sh = shIdMap[ctSheet.id];
if (sh == null)
{
logger.Log(POILogger.WARN, "Sheet with name " + ctSheet.name + " and r:id " + ctSheet.id + " was defined, but didn't exist in package, skipping");
continue;
}
sh.sheet = ctSheet;
sh.OnDocumentRead();
sheets.Add(sh);
}
// Load the external links tables. Their order is defined by the order
// of CTExternalReference elements in the workbook
externalLinks = new List<ExternalLinksTable>(elIdMap.Count);
if (this.workbook.IsSetExternalReferences())
{
foreach (CT_ExternalReference er in this.workbook.externalReferences.externalReference)
{
ExternalLinksTable el = null;
if(elIdMap.ContainsKey(er.id))
el = elIdMap[(er.id)];
if (el == null)
{
logger.Log(POILogger.WARN, "ExternalLinksTable with r:id " + er.id + " was defined, but didn't exist in package, skipping");
continue;
}
externalLinks.Add(el);
}
}
// Process the named ranges
ReprocessNamedRanges();
}
catch (XmlException e)
{
throw new POIXMLException(e);
}
}
public void TestCreateNew()
{
SharedStringsTable sst = new SharedStringsTable();
CT_Rst st;
int idx;
// Check defaults
Assert.IsNotNull(sst.Items);
Assert.AreEqual(0, sst.Items.Count);
Assert.AreEqual(0, sst.Count);
Assert.AreEqual(0, sst.UniqueCount);
st = new CT_Rst();
st.t = ("Hello, World!");
idx = sst.AddEntry(st);
Assert.AreEqual(0, idx);
Assert.AreEqual(1, sst.Count);
Assert.AreEqual(1, sst.UniqueCount);
//add the same entry again
idx = sst.AddEntry(st);
Assert.AreEqual(0, idx);
Assert.AreEqual(2, sst.Count);
Assert.AreEqual(1, sst.UniqueCount);
//and again
idx = sst.AddEntry(st);
Assert.AreEqual(0, idx);
Assert.AreEqual(3, sst.Count);
Assert.AreEqual(1, sst.UniqueCount);
st = new CT_Rst();
st.t = ("Second string");
idx = sst.AddEntry(st);
Assert.AreEqual(1, idx);
Assert.AreEqual(4, sst.Count);
Assert.AreEqual(2, sst.UniqueCount);
//add the same entry again
idx = sst.AddEntry(st);
Assert.AreEqual(1, idx);
Assert.AreEqual(5, sst.Count);
Assert.AreEqual(2, sst.UniqueCount);
st = new CT_Rst();
CT_RElt r = st.AddNewR();
CT_RPrElt pr = r.AddNewRPr();
pr.AddNewColor().SetRgb(new byte[] { (byte)0xFF, 0, 0 }); //red
pr.AddNewI().val = (true); //bold
pr.AddNewB().val = (true); //italic
r.t = ("Second string");
idx = sst.AddEntry(st);
Assert.AreEqual(2, idx);
Assert.AreEqual(6, sst.Count);
Assert.AreEqual(3, sst.UniqueCount);
idx = sst.AddEntry(st);
Assert.AreEqual(2, idx);
Assert.AreEqual(7, sst.Count);
Assert.AreEqual(3, sst.UniqueCount);
//OK. the sst table is Filled, check the contents
Assert.AreEqual(3, sst.Items.Count);
Assert.AreEqual("Hello, World!", new XSSFRichTextString(sst.GetEntryAt(0)).ToString());
Assert.AreEqual("Second string", new XSSFRichTextString(sst.GetEntryAt(1)).ToString());
Assert.AreEqual("Second string", new XSSFRichTextString(sst.GetEntryAt(2)).ToString());
}
internal override void OnDocumentRead()
{
try
{
XmlDocument xmldoc = ConvertStreamToXml(GetPackagePart().GetInputStream());
doc = WorkbookDocument.Parse(xmldoc, NamespaceManager);
this.workbook = doc.Workbook;
Dictionary<String, XSSFSheet> shIdMap = new Dictionary<String, XSSFSheet>();
foreach (POIXMLDocumentPart p in GetRelations())
{
if (p is SharedStringsTable) sharedStringSource = (SharedStringsTable)p;
else if (p is StylesTable) stylesSource = (StylesTable)p;
else if (p is ThemesTable) theme = (ThemesTable)p;
else if (p is CalculationChain) calcChain = (CalculationChain)p;
else if (p is MapInfo) mapInfo = (MapInfo)p;
else if (p is XSSFSheet)
{
shIdMap.Add(p.GetPackageRelationship().Id,(XSSFSheet)p);
}
}
if (null != stylesSource) { stylesSource.SetTheme(theme); }
if (sharedStringSource == null)
{
//Create SST if it is missing
sharedStringSource = (SharedStringsTable)CreateRelationship(XSSFRelation.SHARED_STRINGS, XSSFFactory.GetInstance());
}
// Load individual sheets. The order of sheets is defined by the order of CT_Sheet elements in the workbook
sheets = new List<XSSFSheet>(shIdMap.Count);
foreach (CT_Sheet ctSheet in this.workbook.sheets.sheet)
{
XSSFSheet sh = shIdMap[ctSheet.id];
if (sh == null)
{
logger.Log(POILogger.WARN, "Sheet with name " + ctSheet.name + " and r:id " + ctSheet.id + " was defined, but didn't exist in package, skipping");
continue;
}
sh.sheet = ctSheet;
sh.OnDocumentRead();
sheets.Add(sh);
}
// Process the named ranges
namedRanges = new List<XSSFName>();
if (workbook.IsSetDefinedNames())
{
foreach (CT_DefinedName ctName in workbook.definedNames.definedName)
{
namedRanges.Add(new XSSFName(ctName, this));
}
}
}
catch (XmlException e)
{
throw new POIXMLException(e);
}
}
public void TestCreateNew()
{
SharedStringsTable sst = new SharedStringsTable();
CT_Rst st;
int idx;
// Check defaults
Assert.IsNotNull(sst.Items);
Assert.AreEqual(0, sst.Items.Count);
Assert.AreEqual(0, sst.Count);
Assert.AreEqual(0, sst.UniqueCount);
st = new CT_Rst();
st.t = ("Hello, World!");
idx = sst.AddEntry(st);
Assert.AreEqual(0, idx);
Assert.AreEqual(1, sst.Count);
Assert.AreEqual(1, sst.UniqueCount);
//add the same entry again
idx = sst.AddEntry(st);
Assert.AreEqual(0, idx);
Assert.AreEqual(2, sst.Count);
Assert.AreEqual(1, sst.UniqueCount);
//and again
idx = sst.AddEntry(st);
Assert.AreEqual(0, idx);
Assert.AreEqual(3, sst.Count);
Assert.AreEqual(1, sst.UniqueCount);
st = new CT_Rst();
st.t = ("Second string");
idx = sst.AddEntry(st);
Assert.AreEqual(1, idx);
Assert.AreEqual(4, sst.Count);
Assert.AreEqual(2, sst.UniqueCount);
//add the same entry again
idx = sst.AddEntry(st);
Assert.AreEqual(1, idx);
Assert.AreEqual(5, sst.Count);
Assert.AreEqual(2, sst.UniqueCount);
st = new CT_Rst();
CT_RElt r = st.AddNewR();
CT_RPrElt pr = r.AddNewRPr();
pr.AddNewColor().SetRgb(new byte[] { (byte)0xFF, 0, 0 }); //red
pr.AddNewI().val = (true); //bold
pr.AddNewB().val = (true); //italic
r.t = ("Second string");
idx = sst.AddEntry(st);
Assert.AreEqual(2, idx);
Assert.AreEqual(6, sst.Count);
Assert.AreEqual(3, sst.UniqueCount);
idx = sst.AddEntry(st);
Assert.AreEqual(2, idx);
Assert.AreEqual(7, sst.Count);
Assert.AreEqual(3, sst.UniqueCount);
//OK. the sst table is Filled, check the contents
Assert.AreEqual(3, sst.Items.Count);
Assert.AreEqual("Hello, World!", new XSSFRichTextString(sst.GetEntryAt(0)).ToString());
Assert.AreEqual("Second string", new XSSFRichTextString(sst.GetEntryAt(1)).ToString());
Assert.AreEqual("Second string", new XSSFRichTextString(sst.GetEntryAt(2)).ToString());
}
/**
* Construct a XSSFCell.
*
* @param row the parent row.
* @param cell the xml bean Containing information about the cell.
*/
public XSSFCell(XSSFRow row, CT_Cell cell)
{
_cell = cell;
_row = row;
if (cell.r != null)
{
_cellNum = new CellReference(cell.r).Col;
}
_sharedStringSource = ((XSSFWorkbook)row.Sheet.Workbook).GetSharedStringSource();
_stylesSource = ((XSSFWorkbook)row.Sheet.Workbook).GetStylesSource();
}
/**
* Construct a XSSFCell.
*
* @param row the parent row.
* @param cell the xml bean Containing information about the cell.
*/
public XSSFCell(XSSFRow row, CT_Cell cell)
{
_cell = cell;
_row = row;
if (cell.r != null)
{
_cellNum = new CellReference(cell.r).Col;
}
else
{
int prevNum = row.LastCellNum;
if (prevNum != -1)
{
_cellNum = (row as XSSFRow).GetCell(prevNum - 1, MissingCellPolicy.RETURN_NULL_AND_BLANK).ColumnIndex + 1;
}
}
_sharedStringSource = ((XSSFWorkbook)row.Sheet.Workbook).GetSharedStringSource();
_stylesSource = ((XSSFWorkbook)row.Sheet.Workbook).GetStylesSource();
}