public void WorksheetBorderTest()
{
MemoryStream stream = SpreadsheetReader.Create();
SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true);
WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);
//Fill a background cell to make sure it is not overwritten
SpreadsheetStyle style = SpreadsheetReader.GetDefaultStyle(doc);
style.SetBackgroundColor("C0C0C0");
//(grey)
writer.SetStyle(style, "B2");
writer.DrawBorder("B2", "D4", "FF0000", BorderStyleValues.Medium);
//Save to the memory stream, and then to a file
SpreadsheetWriter.Save(doc);
SpreadsheetWriter.StreamToFile(string.Format("{0}\\border.xlsx", GetOutputFolder()), stream);
}
///<summary>
///Determines if the two font settings supplied are the same.
///</summary>
protected internal static bool CompareFont(SpreadsheetStyle font1, SpreadsheetStyle font2)
{
if (!font1.Italic.Compare(font2.Italic))
{
return(false);
}
if (!font1.Bold.Compare(font2.Bold))
{
return(false);
}
if (!font1.Underline.Compare(font2.Underline))
{
return(false);
}
if (!font1.Color.Compare(font2.Color))
{
return(false);
}
if (!font1.FontSize.Compare(font2.FontSize))
{
return(false);
}
if (!font1.FontName.Compare(font2.FontName))
{
return(false);
}
if (!font1.FontFamily.Compare(font2.FontFamily))
{
return(false);
}
if (!font1.FontScheme.Compare(font2.FontScheme))
{
return(false);
}
return(true);
}
///<summary>
///Creates or gets an existing number format.
///</summary>
public static UInt32 CreateNumberFormat(SpreadsheetStyle style, WorkbookStylesPart styles)
{
NumberingFormat formatMatch = null;
UInt32 formatIndex = 0; //starts at 164
//Loop through and see if there is a matching border style
if (styles.Stylesheet.NumberingFormats != null)
{
foreach (var formatElement in styles.Stylesheet.NumberingFormats)
{
var format = (NumberingFormat)formatElement;
//If we have a match then use this font
if (SpreadsheetStyle.CompareNumberFormat(format, style))
{
formatMatch = format;
break;
}
formatIndex += Convert.ToUInt32(1);
}
}
//Add the new number format if not found
if (formatMatch == null)
{
NumberingFormat format = style.ToNumberFormat();
format.NumberFormatId = formatIndex + Convert.ToUInt32(164);
if (styles.Stylesheet.NumberingFormats == null)
{
styles.Stylesheet.NumberingFormats = new NumberingFormats();
}
styles.Stylesheet.NumberingFormats.AppendChild <NumberingFormat>(format);
styles.Stylesheet.NumberingFormats.Count = formatIndex + Convert.ToUInt32(1);
}
return(formatIndex + Convert.ToUInt32(164));
}
public void WorksheetMergeCellsTest()
{
MemoryStream stream = SpreadsheetReader.Create();
SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true);
WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);
SpreadsheetStyle style = SpreadsheetReader.GetDefaultStyle(doc);
Cell cell = writer.PasteSharedText("B2", "Merged cells");
style.IsUnderline = true;
writer.SetStyle(style, "B2");
Cell cell2 = writer.FindCell("C2");
cell2.StyleIndex = cell.StyleIndex;
writer.MergeCells("B2", "C2");
//Save to the memory stream, and then to a file
SpreadsheetWriter.Save(doc);
SpreadsheetWriter.StreamToFile(string.Format("{0}\\merge.xlsx", GetOutputFolder()), stream);
}
///<summary>
///Determines if the two format settings supplied are the same.
///</summary>
protected internal static bool CompareNumberFormat(NumberingFormat format1, SpreadsheetStyle format2)
{
return(CompareNumberFormat(new SpreadsheetStyle(format1), format2));
}
///<summary>
///Determines if the two alignment settings supplied are the same.
///</summary>
protected internal static bool CompareAlignment(Alignment alignment1, SpreadsheetStyle alignment2)
{
return(CompareAlignment(new SpreadsheetStyle(alignment1), alignment2));
}
///<summary>
///Determines if the two border settings supplied are the same.
///</summary>
protected internal static bool CompareBorder(Border border1, SpreadsheetStyle border2)
{
return(CompareBorder(new SpreadsheetStyle(border1), border2));
}
///<summary>
///Determines if the two fill settings supplied are the same.
///</summary>
protected internal static bool CompareFill(Fill fill1, SpreadsheetStyle fill2)
{
return(CompareFill(new SpreadsheetStyle(fill1), fill2));
}
///<summary>
///Determines if the two font settings supplied are the same.
///</summary>
protected internal static bool CompareFont(Font font1, SpreadsheetStyle font2)
{
return(CompareFont(new SpreadsheetStyle(font1), font2));
}
