private void GenerateSharedStringTablePartContent(SharedStringTablePart sharedStringTablePart,
SaveContext context)
{
// Call all table headers to make sure their names are filled
var x = 0;
Worksheets.ForEach(w => w.Tables.ForEach(t => x = (t as XLTable).FieldNames.Count));
sharedStringTablePart.SharedStringTable = new SharedStringTable {Count = 0, UniqueCount = 0};
var stringId = 0;
var newStrings = new Dictionary<String, Int32>();
var newRichStrings = new Dictionary<IXLRichText, Int32>();
foreach (
var c in
Worksheets.Cast<XLWorksheet>().SelectMany(
w =>
w.Internals.CellsCollection.GetCells(
c => ((c.DataType == XLCellValues.Text && c.ShareString) || c.HasRichText)
&& (c as XLCell).InnerText.Length > 0
&& XLHelper.IsNullOrWhiteSpace(c.FormulaA1)
)))
{
c.DataType = XLCellValues.Text;
if (c.HasRichText)
{
if (newRichStrings.ContainsKey(c.RichText))
c.SharedStringId = newRichStrings[c.RichText];
else
{
var sharedStringItem = new SharedStringItem();
foreach (var rt in c.RichText.Where(r => !String.IsNullOrEmpty(r.Text)))
{
sharedStringItem.Append(GetRun(rt));
}
if (c.RichText.HasPhonetics)
{
foreach (var p in c.RichText.Phonetics)
{
var phoneticRun = new PhoneticRun
{
BaseTextStartIndex = (UInt32)p.Start,
EndingBaseIndex = (UInt32)p.End
};
var text = new Text {Text = p.Text};
if (p.Text.PreserveSpaces())
text.Space = SpaceProcessingModeValues.Preserve;
phoneticRun.Append(text);
sharedStringItem.Append(phoneticRun);
}
var f = new XLFont(null, c.RichText.Phonetics);
if (!context.SharedFonts.ContainsKey(f))
context.SharedFonts.Add(f, new FontInfo {Font = f});
var phoneticProperties = new PhoneticProperties
{
FontId =
context.SharedFonts[
new XLFont(null, c.RichText.Phonetics)].
FontId
};
if (c.RichText.Phonetics.Alignment != XLPhoneticAlignment.Left)
phoneticProperties.Alignment = c.RichText.Phonetics.Alignment.ToOpenXml();
if (c.RichText.Phonetics.Type != XLPhoneticType.FullWidthKatakana)
phoneticProperties.Type = c.RichText.Phonetics.Type.ToOpenXml();
sharedStringItem.Append(phoneticProperties);
}
sharedStringTablePart.SharedStringTable.Append(sharedStringItem);
sharedStringTablePart.SharedStringTable.Count += 1;
sharedStringTablePart.SharedStringTable.UniqueCount += 1;
newRichStrings.Add(c.RichText, stringId);
c.SharedStringId = stringId;
stringId++;
}
}
else
{
if (newStrings.ContainsKey(c.Value.ToString()))
c.SharedStringId = newStrings[c.Value.ToString()];
else
{
var s = c.Value.ToString();
var sharedStringItem = new SharedStringItem();
var text = new Text {Text = s};
if (!s.Trim().Equals(s))
text.Space = SpaceProcessingModeValues.Preserve;
sharedStringItem.Append(text);
sharedStringTablePart.SharedStringTable.Append(sharedStringItem);
sharedStringTablePart.SharedStringTable.Count += 1;
sharedStringTablePart.SharedStringTable.UniqueCount += 1;
newStrings.Add(c.Value.ToString(), stringId);
c.SharedStringId = stringId;
stringId++;
}
}
}
}
// Generates content of sharedStringTablePart1.
private void GenerateSharedStringTablePart1Content(SharedStringTablePart sharedStringTablePart1)
{
SharedStringTable sharedStringTable1 = new SharedStringTable(){ Count = (UInt32Value)130U, UniqueCount = (UInt32Value)32U };
SharedStringItem sharedStringItem1 = new SharedStringItem();
Text text7 = new Text();
text7.Text = "Date";
PhoneticRun phoneticRun1 = new PhoneticRun(){ BaseTextStartIndex = (UInt32Value)0U, EndingBaseIndex = (UInt32Value)0U };
Text text8 = new Text();
text8.Text = "";
phoneticRun1.Append(text8);
PhoneticProperties phoneticProperties4 = new PhoneticProperties(){ FontId = (UInt32Value)0U, Type = PhoneticValues.Hiragana };
sharedStringItem1.Append(text7);
sharedStringItem1.Append(phoneticRun1);
sharedStringItem1.Append(phoneticProperties4);
SharedStringItem sharedStringItem2 = new SharedStringItem();
Text text9 = new Text();
text9.Text = "Product";
PhoneticRun phoneticRun2 = new PhoneticRun(){ BaseTextStartIndex = (UInt32Value)0U, EndingBaseIndex = (UInt32Value)0U };
Text text10 = new Text();
text10.Text = "";
phoneticRun2.Append(text10);
PhoneticProperties phoneticProperties5 = new PhoneticProperties(){ FontId = (UInt32Value)0U, Type = PhoneticValues.Hiragana };
sharedStringItem2.Append(text9);
sharedStringItem2.Append(phoneticRun2);
sharedStringItem2.Append(phoneticProperties5);
SharedStringItem sharedStringItem3 = new SharedStringItem();
Text text11 = new Text();
text11.Text = "Quantity";
PhoneticRun phoneticRun3 = new PhoneticRun(){ BaseTextStartIndex = (UInt32Value)0U, EndingBaseIndex = (UInt32Value)0U };
Text text12 = new Text();
text12.Text = "";
phoneticRun3.Append(text12);
PhoneticProperties phoneticProperties6 = new PhoneticProperties(){ FontId = (UInt32Value)0U, Type = PhoneticValues.Hiragana };
sharedStringItem3.Append(text11);
sharedStringItem3.Append(phoneticRun3);
sharedStringItem3.Append(phoneticProperties6);
SharedStringItem sharedStringItem4 = new SharedStringItem();
Text text13 = new Text();
text13.Text = "Price";
PhoneticRun phoneticRun4 = new PhoneticRun(){ BaseTextStartIndex = (UInt32Value)0U, EndingBaseIndex = (UInt32Value)0U };
Text text14 = new Text();
text14.Text = "";
phoneticRun4.Append(text14);
PhoneticProperties phoneticProperties7 = new PhoneticProperties(){ FontId = (UInt32Value)0U, Type = PhoneticValues.Hiragana };
sharedStringItem4.Append(text13);
sharedStringItem4.Append(phoneticRun4);
sharedStringItem4.Append(phoneticProperties7);
SharedStringItem sharedStringItem5 = new SharedStringItem();
Text text15 = new Text();
text15.Text = "DeliveryDate";
sharedStringItem5.Append(text15);
SharedStringItem sharedStringItem6 = new SharedStringItem();
Text text16 = new Text();
text16.Text = "Employee";
PhoneticRun phoneticRun5 = new PhoneticRun(){ BaseTextStartIndex = (UInt32Value)0U, EndingBaseIndex = (UInt32Value)0U };
Text text17 = new Text();
text17.Text = "";
phoneticRun5.Append(text17);
PhoneticProperties phoneticProperties8 = new PhoneticProperties(){ FontId = (UInt32Value)0U, Type = PhoneticValues.Hiragana };
sharedStringItem6.Append(text16);
sharedStringItem6.Append(phoneticRun5);
sharedStringItem6.Append(phoneticProperties8);
SharedStringItem sharedStringItem7 = new SharedStringItem();
Text text18 = new Text();
text18.Text = "product_A";
sharedStringItem7.Append(text18);
SharedStringItem sharedStringItem8 = new SharedStringItem();
Text text19 = new Text();
text19.Text = "sales staff G";
sharedStringItem8.Append(text19);
SharedStringItem sharedStringItem9 = new SharedStringItem();
Text text20 = new Text();
text20.Text = "product_D";
sharedStringItem9.Append(text20);
SharedStringItem sharedStringItem10 = new SharedStringItem();
Text text21 = new Text();
text21.Text = "sales staff B";
sharedStringItem10.Append(text21);
SharedStringItem sharedStringItem11 = new SharedStringItem();
Text text22 = new Text();
text22.Text = "sales staff C";
sharedStringItem11.Append(text22);
SharedStringItem sharedStringItem12 = new SharedStringItem();
Text text23 = new Text();
text23.Text = "product_E";
sharedStringItem12.Append(text23);
SharedStringItem sharedStringItem13 = new SharedStringItem();
Text text24 = new Text();
text24.Text = "sales staff E";
sharedStringItem13.Append(text24);
SharedStringItem sharedStringItem14 = new SharedStringItem();
Text text25 = new Text();
text25.Text = "sales staff J";
sharedStringItem14.Append(text25);
SharedStringItem sharedStringItem15 = new SharedStringItem();
Text text26 = new Text();
text26.Text = "product_B";
sharedStringItem15.Append(text26);
SharedStringItem sharedStringItem16 = new SharedStringItem();
Text text27 = new Text();
text27.Text = "sales staff I";
sharedStringItem16.Append(text27);
SharedStringItem sharedStringItem17 = new SharedStringItem();
Text text28 = new Text();
text28.Text = "product_C";
sharedStringItem17.Append(text28);
SharedStringItem sharedStringItem18 = new SharedStringItem();
Text text29 = new Text();
text29.Text = "product_H";
sharedStringItem18.Append(text29);
SharedStringItem sharedStringItem19 = new SharedStringItem();
Text text30 = new Text();
text30.Text = "product_I";
sharedStringItem19.Append(text30);
SharedStringItem sharedStringItem20 = new SharedStringItem();
Text text31 = new Text();
text31.Text = "sales staff H";
sharedStringItem20.Append(text31);
SharedStringItem sharedStringItem21 = new SharedStringItem();
Text text32 = new Text();
text32.Text = "sales staff A";
sharedStringItem21.Append(text32);
SharedStringItem sharedStringItem22 = new SharedStringItem();
Text text33 = new Text();
text33.Text = "sales staff F";
sharedStringItem22.Append(text33);
SharedStringItem sharedStringItem23 = new SharedStringItem();
Text text34 = new Text();
text34.Text = "product_F";
sharedStringItem23.Append(text34);
SharedStringItem sharedStringItem24 = new SharedStringItem();
Text text35 = new Text();
text35.Text = "product_G";
sharedStringItem24.Append(text35);
SharedStringItem sharedStringItem25 = new SharedStringItem();
Text text36 = new Text();
text36.Text = "sales staff D";
sharedStringItem25.Append(text36);
SharedStringItem sharedStringItem26 = new SharedStringItem();
Text text37 = new Text();
text37.Text = "product_J";
sharedStringItem26.Append(text37);
SharedStringItem sharedStringItem27 = new SharedStringItem();
Text text38 = new Text();
text38.Text = "ProductName";
PhoneticRun phoneticRun6 = new PhoneticRun(){ BaseTextStartIndex = (UInt32Value)0U, EndingBaseIndex = (UInt32Value)0U };
Text text39 = new Text();
text39.Text = "";
phoneticRun6.Append(text39);
PhoneticProperties phoneticProperties9 = new PhoneticProperties(){ FontId = (UInt32Value)0U, Type = PhoneticValues.Hiragana };
sharedStringItem27.Append(text38);
sharedStringItem27.Append(phoneticRun6);
sharedStringItem27.Append(phoneticProperties9);
SharedStringItem sharedStringItem28 = new SharedStringItem();
Text text40 = new Text();
text40.Text = "Employee";
sharedStringItem28.Append(text40);
SharedStringItem sharedStringItem29 = new SharedStringItem();
Text text41 = new Text();
text41.Text = "Sum of Quantity";
sharedStringItem29.Append(text41);
SharedStringItem sharedStringItem30 = new SharedStringItem();
Text text42 = new Text();
text42.Text = "Row Labels";
sharedStringItem30.Append(text42);
SharedStringItem sharedStringItem31 = new SharedStringItem();
Text text43 = new Text();
text43.Text = "Grand Total";
sharedStringItem31.Append(text43);
SharedStringItem sharedStringItem32 = new SharedStringItem();
Text text44 = new Text();
text44.Text = "Toral / Price";
sharedStringItem32.Append(text44);
sharedStringTable1.Append(sharedStringItem1);
sharedStringTable1.Append(sharedStringItem2);
sharedStringTable1.Append(sharedStringItem3);
sharedStringTable1.Append(sharedStringItem4);
sharedStringTable1.Append(sharedStringItem5);
sharedStringTable1.Append(sharedStringItem6);
sharedStringTable1.Append(sharedStringItem7);
sharedStringTable1.Append(sharedStringItem8);
sharedStringTable1.Append(sharedStringItem9);
sharedStringTable1.Append(sharedStringItem10);
sharedStringTable1.Append(sharedStringItem11);
sharedStringTable1.Append(sharedStringItem12);
sharedStringTable1.Append(sharedStringItem13);
sharedStringTable1.Append(sharedStringItem14);
sharedStringTable1.Append(sharedStringItem15);
sharedStringTable1.Append(sharedStringItem16);
sharedStringTable1.Append(sharedStringItem17);
sharedStringTable1.Append(sharedStringItem18);
sharedStringTable1.Append(sharedStringItem19);
sharedStringTable1.Append(sharedStringItem20);
sharedStringTable1.Append(sharedStringItem21);
sharedStringTable1.Append(sharedStringItem22);
sharedStringTable1.Append(sharedStringItem23);
sharedStringTable1.Append(sharedStringItem24);
sharedStringTable1.Append(sharedStringItem25);
sharedStringTable1.Append(sharedStringItem26);
sharedStringTable1.Append(sharedStringItem27);
sharedStringTable1.Append(sharedStringItem28);
sharedStringTable1.Append(sharedStringItem29);
sharedStringTable1.Append(sharedStringItem30);
sharedStringTable1.Append(sharedStringItem31);
sharedStringTable1.Append(sharedStringItem32);
sharedStringTablePart1.SharedStringTable = sharedStringTable1;
}
