/// <summary>
/// Removes the sheet from the document
/// using the given sheet unique name
/// </summary>
/// <param name="document">SpreadSheetDocument reference</param>
/// <param name="name">Sheet name</param>
public static void RemoveSheet(this SpreadsheetDocument document, string name)
{
//Check whether the document or the sheet name is a non-valid to work with object value
if (document.IsNotValid() || name.IsNotValid())
{
return;
}
//Search for sheet collection reference
Sheets sheets = document
.WorkbookPart
.Workbook
.GetFirstChild <Sheets>();
//If there is not any existing sheet with the given name, there is nothing to remove.
if (!sheets.Descendants <Sheet>().Any(s => s.Name.Equals(name)))
{
return;
}
Sheet sheet = sheets.Descendants <Sheet>()
.First(s => s.Name.Equals(name));
int index = sheets
.ToList()
.IndexOf(sheet);
sheet.RemoveAllChildren();
sheet.Remove();
}
/// <summary>
/// Gets the sheet data from the document
/// using the given sheet unique name
/// </summary>
/// <param name="document">SpreadSheetDocument reference</param>
/// <param name="name">Sheet name</param>
/// <returns>SheetData reference</returns>
public static SheetData GetSheetData(this SpreadsheetDocument document, string name)
{
//Check whether the document or the sheet name is a non-valid to work with object value
if (document.IsNotValid() || name.IsNotValid())
{
return(null);
}
//Search the sheet by the unique name given
Sheet sheet = document.WorkbookPart.Workbook
.GetFirstChild <Sheets>()
.Descendants <Sheet>()
.Where(s => s.Name.Equals(name))
.FirstOrDefault();
if (sheet == null)
{
return(null);
}
//Get the workspace corresponding to the sheet founded
WorksheetPart workSpace = document.WorkbookPart.GetPartById(sheet.Id) as WorksheetPart;
//return founded sheet data
return(workSpace.Worksheet
.Descendants <SheetData>()
.First());
}
public void AutoAdjustWidth()
{
SpreadsheetDocument document = SpreadSheetExtensions.ExcelDocument();
document.AutoAdjustWidth();
Assert.True(!document.IsNotValid());
}
public void SwapColumns()
{
SpreadsheetDocument document = SpreadSheetExtensions.ExcelDocument();
SheetData sheet = document.GetSheetData(@"Report");
sheet.SwapColumns(0, 1);
Assert.True(!document.IsNotValid());
}
public async Task SetTemplateValues()
{
SpreadsheetDocument document = SpreadSheetExtensions.ExcelDocument();
SheetData sheet = document.GetSheetData(@"Entity");
List <KeyValuePair <string, object> > data = new List <KeyValuePair <string, object> >(3)
{
new KeyValuePair <string, object>(@"{{Name}}", @"Israel"),
new KeyValuePair <string, object>(@"{{Age}}", DateTime.Now.AddYears(-27)),
new KeyValuePair <string, object>(@"{{Email}}", @"*****@*****.**")
};
await sheet.SetTemplateValues(data);
Assert.True(!document.IsNotValid());
}
public async Task ToExcelDocument()
{
Pagination page = new Pagination();
page.Calculate(1);
List <TestingEntity> entities = new List <TestingEntity>()
{
new TestingEntity()
{
}
};
PaginatedCollection <TestingEntity> collection = new PaginatedCollection <TestingEntity>(page, entities);
SpreadsheetDocument excel = await collection.ToExcelDocument();
Assert.True(!excel.IsNotValid());
}
/// <summary>
/// Adds a new Sheet to the document
/// </summary>
/// <param name="document">SpreadSheetDocument reference</param>
/// <param name="name">Sheet name</param>
public static void AddSheet(this SpreadsheetDocument document, string name)
{
//Check whether the document or the sheet name is a non-valid to work with object value
if (document.IsNotValid() || name.IsNotValid())
{
return;
}
//Search for sheet collection reference
Sheets sheets = document
.WorkbookPart
.Workbook
.GetFirstChild <Sheets>();
//If there already exists a sheet by the given name, there is no need to create a new one.
if (sheets.Descendants <Sheet>().Any(s => s.Name.Equals(name)))
{
return;
}
//Get the last sheet pushed into the sheets collection
Sheet last = sheets
.Descendants <Sheet>()
.OrderByDescending(s => s.SheetId)
.FirstOrDefault();
//Set current workspace reference
WorksheetPart workSpace = document.WorkbookPart.AddNewPart <WorksheetPart>();
//Add new worksheet with data
workSpace.Worksheet = new Worksheet(new SheetData());
//Stablish index value
uint index = last == null ? 1 : last.SheetId.Value;
index = last == null ? 1 : index + 1;
//Create the new sheet
Sheet sheet = new Sheet()
{
Id = document.WorkbookPart.GetIdOfPart(workSpace),
SheetId = index,
Name = name
};
//Add new sheet to the collection
sheets.Append(sheet);
}
/// <summary>
/// Adjust the column width to its best fit
/// </summary>
/// <param name="document">SpreadSheetDocument reference</param>
public static void AutoAdjustWidth(this SpreadsheetDocument document)
{
if (document.IsNotValid())
{
return;
}
//Maximum digit with per cell ~96dpi (most common display)
double max = 8;
//Add the columns for each worksheet that has any valid sheet data
document.WorkbookPart.WorksheetParts
.Select(wsp => wsp.Worksheet)
.Where(wsp => wsp.Descendants <SheetData>().Any(sd => sd.Descendants <Row>().Any()))
.ToList()
.ForEach(ws =>
{
//Current SheetData reference
SheetData sd = ws.GetFirstChild <SheetData>();
//Find the widest row
Row row = sd.Descendants <Row>().First();
//Array of lengths (max length per cell per row)
int[] lengths = new int[row.Descendants <Cell>().Count()];
for (int i = 0; i < lengths.Length; i++)
{
lengths[i] = 0;
}
//Find the widest cell in all rows
foreach (Row r in sd.Descendants <Row>())
{
for (int i = 0; i < lengths.Length; i++)
{
Cell cell = r.Descendants <Cell>().ElementAt(i);
int length;
//Take the cell length value from the shared string table if the cell has its value in the shared string table
if (cell.DataType.Equals(CellValues.SharedString) && cell.CellValue.Text.IsNumber())
{
int index = Convert.ToInt32(cell.CellValue.Text);
length = StringPart.SharedStringTable.ElementAt(index).InnerText.Length;
}
else
{
//Take the length from the cell value directly
length = cell.CellValue.Text.Length;
}
//Set the current length for the cell at row if the length at the current position is less than the current
if (lengths[i] < length)
{
lengths[i] = length;
}
}
}
//Get or create the custom columns reference
Columns columns = ws.GetFirstChild <Columns>() ?? new Columns();
//Adjust the columns width
for (uint i = 1; i <= lengths.Length; i++)
{
double width = ((lengths[i - 1] * max + 5) / max * 256) / 256;
columns.Append(new Column()
{
Min = i,
Max = i,
Width = width + 1,
CustomWidth = true
});
}
//Add the columns if they do not exists
if (ws.GetFirstChild <Columns>() == null)
{
ws.InsertBefore(columns, sd);
}
//Save all changes made
ws.Save();
});
document.Save();
}
/// <summary>
/// Loads the default style sheet part
/// </summary>
/// <param name="document">SpreadSheetDocument reference</param>
public static void LoadDefaultStyleSheet(this SpreadsheetDocument document)
{
//Verify document to work with
if (document.IsNotValid())
{
return;
}
//Initialize worksheet stylesheet once
WorkbookStylesPart style = document.WorkbookPart.WorkbookStylesPart ?? document.WorkbookPart.AddNewPart <WorkbookStylesPart>();
if (style.Stylesheet == null)
{
style.Stylesheet = new Stylesheet();
}
//Reserved fills Excel requires by default this two fills
style.Stylesheet.Fills = new Fills();
//Empty cell fill
style.Stylesheet.Fills.AppendChild(new Fill
{
PatternFill = new PatternFill
{
PatternType = PatternValues.None
}
});
//Weird grey cell fill
style.Stylesheet.Fills.AppendChild(new Fill
{
PatternFill = new PatternFill
{
PatternType = PatternValues.Gray125
}
});
style.Stylesheet.Fills.Count = 2;
//Reserved Cell formats Excel requires by default this two formats
style.Stylesheet.CellFormats = new CellFormats();
style.Stylesheet.CellFormats.AppendChild(new CellFormat());
style.Stylesheet.CellFormats.Count = 1;
//Fonts
style.Stylesheet.Fonts = new Fonts();
Font font = new Font();
font.Append(new FontSize()
{
Val = 11D
});
font.Append(new Color()
{
Rgb = SystemColor.Black.ToHexArgb()
});
font.Append(new FontName()
{
Val = "Calibri"
});
font.Append(new FontFamilyNumbering()
{
Val = 2
});
font.Append(new FontScheme()
{
Val = FontSchemeValues.Minor
});
font.Append(new Bold());
style.Stylesheet.Fonts.AppendChild(font);
style.Stylesheet.Fonts.Count = 1;
//Save all reserved formats
style.Stylesheet.Save();
StylePart = style;
document.Save();
}
public void ExcelDocument()
{
SpreadsheetDocument document = SpreadSheetExtensions.ExcelDocument();
Assert.True(!document.IsNotValid());
}
public void IsNotValid()
{
SpreadsheetDocument document = null;
Assert.True(document.IsNotValid());
}